• transport tablespace将一个表空间下的数据移到另一个表空间


    http://blog.csdn.net/macliukaijie/article/details/8308643

    1、创建两个表空间

    SQL> create tablespace test1 datafile '/opt/test1.dbf' size 10m;

    Tablespace created

    SQL> create tablespace test2 datafile '/opt/test2.dbf' size 10m;

    Tablespace created

    2、创建两个用户

    SQL> create user test1 identified by test1 default tablespace test1;

    User created

    SQL> create user test2 identified by test2 default tablespace test2;

    User created

    3.给权限

    SQL> grant connect,resource to test1;

    Grant succeeded

    SQL> grant connect,resource to test2;

    Grant succeeded

    4.在test1表空间里建表

    conn test1/test1
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
    Connected as test1

    SQL> create table t(i number) tablespace test1;

    Table created

    SQL> begin 
      2  for i in 1..10000 loop
      3  insert into t values (i);
      4  end loop;
      5  commit;
      6  end ;
      7  
      8  /

    PL/SQL procedure successfully completed

    5.把表空间设置成只读模式

    SQL> alter tablespace test1 read only;

    Tablespace altered

    6、使用transport tablespace导出数据

    SQL> host exp transport_tablespace=y tablespaces=test1 file=/opt/test1.dmp

    Export: Release 10.2.0.1.0 - Production on Mon Dec 17 04:44:11 2012

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.


    Username: sys   
    Password:

    EXP-00056: ORACLE error 28009 encountered
    ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
    Username: sys as sysdba 
    Password:

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    Export done in US7ASCII character set and AL16UTF16 NCHAR character set
    server uses WE8ISO8859P1 character set (possible charset conversion)
    Note: table data (rows) will not be exported
    About to export transportable tablespace metadata...
    For tablespace TEST1 ...
    . exporting cluster definitions
    . exporting table definitions
    . . exporting table                              T
    . exporting referential integrity constraints
    . exporting triggers
    . end transportable tablespace metadata export
    Export terminated successfully without warnings.

    7.将数据移到test2用户下

    SQL> host imp transport_tablespace=y file=/opt/test1.dmp fromuser=test1 touser=test2 datafiles='/opt/test1.dbf';

    Import: Release 10.2.0.1.0 - Production on Mon Dec 17 04:50:24 2012

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

    Username: sys as sysdba
    Password:

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options

    Export file created by EXPORT:V10.02.01 via conventional path
    About to import transportable tablespace(s) metadata...
    import done in US7ASCII character set and AL16UTF16 NCHAR character set
    import server uses WE8ISO8859P1 character set (possible charset conversion)
    . importing TEST1's objects into TEST2
    . . importing table                            "T"
    Import terminated successfully without warnings.

    8.查询test2用户下的数据

    SQL> select count(*) from t;

      COUNT(*)
    ----------
         10000

    9、完成

    -----------------------未验证------------------

    ------------------另------------------------------------------------------------------------

    ORACLE数据库设置表空间为只读/写模式  

       了保证表空间数据完整性,比如:进行数据库备份与还原操作、历史数据的完整性保护等情况下,可以将表空间设置到只读模式。只读表空间中的表不能进行INSERT、UPDATE、DELETE等操作,但是可以删除数据库对象。

    需要dba权限的用户登录才可以进行一下操作:

        SQL> select tablespace_name,status from dba_tablespaces;

     

        SQL> alter tablespace demo read only;

     

     SQL> select tablespace_name,status from dba_tablespaces;

     

       SQL> alter tablespace demo read write;

     

       SQL> select tablespace_name,status from dba_tablespaces;

  • 相关阅读:
    [办公自动化]Excel表格求和始终显示#VALUE!
    Excel中如何对IP地址排序(linux 中呢?用sort)
    勇士通关——记多年前的面试
    [听听音乐]路,李知蹊
    [办公自动化]PPT演示时加个动画倒计时
    [网络安全]burp 转换ascii编码
    [学习笔记]掌控安全的课程学习笔记:20191228
    [生活随笔]起死回生的九阳破壁机
    [IT新应用]如何拯救死机的苹果手机(iPhone X)
    八月份课堂--Python爬虫(Spider)基础
  • 原文地址:https://www.cnblogs.com/lteal/p/3816311.html
Copyright © 2020-2023  润新知