SECTION5数据库管理.doc

传输表空间 表级级空间级库级同的级别有不同的参数辅助使用 使用传输表空间技术将库中对应”OLTP”空间迁移到库中 Administrator’s guied - 8managing tablespaces -Transporting tablespaces between databases 参books- sql reference -create directory 答： 使用/home/oracle/scripts目录下的，备份文件进行初始化始化实验环境 输的表空间是建空间应的数据文件是 /u01/app/oracle/oradata/prod/disk1/oltp_01.dbf export ORACLE_SID=PROD sqlplus / as sysdba create user oltp_user identified by oltp_user default tablespace oltp; grant connect,resource to oltp_user; conn oltp_user/oltp_user create table t(x int) tablespace oltp; create index i_t on t(x) tablespace oltp; insert into t values(1); commit; conn /as sysdba SELECT tablespace_name, segment_name, segment_type FROM dba_segments WHERE tablespace_name =OLTP; 检查是否存在违反约问题 Exec sys.dbms_tts.transport_set_check(OLTP,true); Select * from sys.transport_set_violations; 表空间为只读 Atablespace OLTP read only; Exit; 使用EXP或EXPDP的transport_tablespace=y参数导出 份方法 Exp userid=\/as sysdba \ transport_tablespace=y tablespace=oltp file=exp_oltp.dmp 份方法 S/ as sysdba Create directory dir_home as /home/oracle; Grant read,write on directory dir_home to public; Exit; Expdp system/oracle directory=dir_home dump_file=expdp_oltp.dmp transport_tablespaces=oltp transport_full_check=y transport_full_check=y是两种校验传输和引的表空间都校验校验自己贝数据文件 S/ as sysdba Select name ,ts# from v$tablespace; Select name from v$datafile where ts# in(11); !cp /u01/app/oracle/oradata/PROD/disk1/oltp01.dbf /u01/app/oracle/oradata/EMREP/oltp01.dbf Exit 实现传输表空间导入 入前准备工作 建待导入用户 Export ORACLE_SID=EMREP Sqlplus / as sysdba Create user oltp_user identified by oltp_user; Grant connect,resource to oltp_user; 清理环境 Dtablespace oltp including contents and datafiles; 使用imp导入方法 Imp userid=\ / as sysdba \ FILE=/home/oracle/exp_oltp.dmp fromuser=OLTP_user touser=oltp_user transport_tablespace=y tablespaces=OLTP DATAFILES=/u01/app/oracle/ORADATA/emrep/oltp_01.dbf 使用impdp导入方法 dtablespace oltp including contents and datafiles; !cp /u01/app/oracle/oradata/PROD/disk1/oltp_01.dbf /u01/app/oracle/oradata/EMRE