[20170623]传输表空间补充测试.txt
--//昨天测试了使用dblink+传输表空间,链接如下:http://blog.itpub.net/267265/viewspace-2141115/
--//今天补充测试看看加参数SQLFILE生成的脚本是什么内容.
impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf
REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss logfile=impdp.log SQLFILE=sss.sql
--//注:前面的测试参考前面的链接
$ impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf
REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss logfile=impdp.log SQLFILE=sss.sql
Import: Release 11.2.0.4.0 - Production on Fri Jun 23 08:53:59 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_SQL_FILE_TRANSPORTABLE_01": scott/a** network_link=l_dbcn1 transport_tablespaces=users
transport_datafiles=/home/oracle/backup/sugar01.dbf REMAP_TABLESPACE=USERS:SUAGR REMAP_SCHEMA=scott:sss
logfile=impdp.log SQLFILE=sss.sql
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SCOTT"."SYS_SQL_FILE_TRANSPORTABLE_01" successfully completed at Fri Jun 23 08:54:23 2017 elapsed 0 00:00:22
--//说明我前面的测试没有使用DIRECTORY参数,缺省使用DIRECTORY_NAME=DATA_PUMP_DIR.
SCOTT@book> column DIRECTORY_PATH format a40
SCOTT@book> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------ -------------- ----------------------------------------
SYS DATA_PUMP_DIR /u01/app/oracle/admin/book/dpdump/
$ cd /u01/app/oracle/admin/book/dpdump
--//我仅仅摘录其中一段:
-- new object type path: TRANSPORTABLE_EXPORT/TABLE
CREATE TABLE "SSS"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14 BYTE),
"LOC" VARCHAR2(13 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(SEG_FILE 4 SEG_BLOCK 130 OBJNO_REUSE 87106
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SUAGR" ;
BEGIN
SYS.DBMS_PLUGTSP.PATCHDICTIONARY('SSS','DEPT',736,'unused','unused','unused','unused',3);
END;
/
--//很明显这样的语句单独无法执行.有点像sys.bootstrap$的sql_text的内容.
--//不知道是否可以直接执行脚本sss.sql来实现.测试看看.
SYS@book> drop tablespace sugar including contents;
Tablespace dropped.
$ ls -l /home/oracle/backup/sugar01.dbf
-rw-r----- 1 oracle oinstall 5251072 2017-06-23 09:17:55 /home/oracle/backup/sugar01.dbf
--//文件还在.
--//不行报许多错误.重新引入看看是否可以打开读写.
SYS@dbcn1> alter tablespace users read only;
Tablespace altered.
$ cp /u01/app/oracle/oradata/dbcn1/datafile/users.307.862160493 /home/oracle/backup/sugar01.dbf
/bin/cp: overwrite `/home/oracle/backup/sugar01.dbf'? y
SYS@dbcn1> alter tablespace users read write ;
Tablespace altered.
$ impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf
REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss logfile=impdp.log
Import: Release 11.2.0.4.0 - Production on Fri Jun 23 09:22:02 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01": scott/a* network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss logfile=impdp.log
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is
ORA-06512: at "SYS.DBMS_METADATA", line 7784
ORA-29335: tablespace 'USERS' is not read only
---//已经提示这样不行.
Job "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Fri Jun 23 09:22:06 2017 elapsed 0 00:00:02
SYS@dbcn1> alter tablespace users read only;
Tablespace altered.
$ impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf
REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss logfile=impdp.log
Import: Release 11.2.0.4.0 - Production on Fri Jun 23 09:28:08 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01": scott/a** network_link=l_dbcn1 transport_tablespaces=users
transport_datafiles=/home/oracle/backup/sugar01.dbf REMAP_TABLESPACE=USERS:SUGAR REMAP_SCHEMA=scott:sss
logfile=impdp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Jun 23 09:28:18 2017 elapsed 0 00:00:09
--//总结:
1.生成的脚本有点像bootstrap$.但是无法单独执行.
2.要设置成read only模式.
