SQL> SQL> create table test (id number,name varchar2(10));
Table created.
SQL> alter table test add constraint pk_id primary key(id);
Table altered.
SQL> create index idx_name on test(name);
Index created.
SQL> insert into test values(1,'wagn');
1 row created.
SQL> insert into test values(2,'xue');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME ---------- -------------------- 1 wagn 2 xue
--分析表：
--查詢： SQL> set lines 200 SQL> col owner for a10 SQL> col SEGMENT_NAME for a25 SQL> col TABLESPACE_NAME for a25 SQL> col PARTITION_NAME for a25 SQL> select OWNER, SEGMENT_NAME,TABLESPACE_NAME,PARTITION_NAME, BYTES / 1024 / 1024 size_m from dba_segments where segment_name='TEST';
OWNER SEGMENT_NAME TABLESPACE_NAME PARTITION_NAME SIZE_M ---------- ------------------------- ------------------------- ------------------------- ---------- SCOTT TEST USERS .0625 SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED from dba_tables where table_name ='TEST';
OWNER TABLE_NAME TABLESPACE_NAME STATUS LAST_ANALYZED PAR ---------- ------------------------- ------------------------- -------- ------------------- --- SCOTT TEST USERS VALID 2018-01-24 00:58:46 NO
SQL> col index_name for a25 SQL> col TABLE_OWNER for a15 SQL> col TABLE_NAME for a25 SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME,status from dba_indexes where table_name='TEST';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME STATUS ---------- ------------------------- --------------- ------------------------- ------------------------- -------- SCOTT IDX_NAME SCOTT TEST USERS VALID SCOTT PK_ID SCOTT TEST USERS VALID
--創建目錄對象： SQL> create directory dir as '/home/oracle';
Directory created.
SQL>
--執行導出： expdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=test.log TABLES=scott.test
[oracle@testdb ~]$ expdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=test.log TABLES=scott.test
Export: Release 11.2.0.4.0 - Production on Wed Jan 24 01:03:04 2018
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 "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=test.dmp directory=dir LOGFILE=test.log TABLES=scott.test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TEST" 5.414 KB 2 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 24 01:03:15 2018 elapsed 0 00:00:10
Import: Release 11.2.0.4.0 - Production on Wed Jan 24 01:10:50 2018
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
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."TEST" 5.414 KB 2 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 24 01:10:54 2018 elapsed 0 00:00:03
[oracle@testdb ~]$
--驗證： SQL> select OWNER, SEGMENT_NAME,TABLESPACE_NAME,PARTITION_NAME, BYTES / 1024 / 1024 size_m from dba_segments where segment_name='TEST';
OWNER SEGMENT_NAME TABLESPACE_NAME PARTITION_NAME SIZE_M ---------- ------------------------- ------------------------- ------------------------- ---------- SCOTT TEST USERS .0625 HR TEST TEST .0625
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED from dba_tables where table_name ='TEST';
OWNER TABLE_NAME TABLESPACE_NAME STATUS LAST_ANALYZED PAR ---------- ------------------------- ------------------------- -------- ------------------- --- SCOTT TEST USERS VALID 2018-01-24 00:58:46 NO HR TEST TEST VALID 2018-01-24 00:58:46 NO
SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME,status from dba_indexes where table_name='TEST';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME STATUS ---------- ------------------------- --------------- ------------------------- ------------------------- -------- HR PK_ID HR TEST TEST VALID HR IDX_NAME HR TEST TEST VALID SCOTT IDX_NAME SCOTT TEST USERS VALID SCOTT PK_ID SCOTT TEST USERS VALID
導入實驗二：指定參數exclude=index，statistics,constraint
--hr用戶下刪除表test: SQL> conn hr/hr;
Connected.
SQL> drop table test purge;
Import: Release 11.2.0.4.0 - Production on Wed Jan 24 01:19:47 2018
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
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test exclude=index,statistics,constraint
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."TEST" 5.414 KB 2 rows Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 24 01:19:50 2018 elapsed 0 00:00:02
[oracle@wang ~]$
--驗證：
SQL> select OWNER, SEGMENT_NAME,TABLESPACE_NAME,PARTITION_NAME, BYTES / 1024 / 1024 size_m from dba_segments where segment_name='TEST';
OWNER SEGMENT_NAME TABLESPACE_NAME PARTITION_NAME SIZE_M ---------- ------------------------- ------------------------- ------------------------- ---------- SCOTT TEST USERS .0625 HR TEST TEST .0625
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED from dba_tables where table_name ='TEST';
OWNER TABLE_NAME TABLESPACE_NAME STATUS LAST_ANALYZED PAR ---------- ------------------------- ------------------------- -------- ------------------- --- SCOTT TEST USERS VALID 2018-01-24 00:58:46 NO HR TEST TEST VALID NO
SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME,status from dba_indexes where table_name='TEST';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME STATUS ---------- ------------------------- --------------- ------------------------- ------------------------- -------- SCOTT IDX_NAME SCOTT TEST USERS VALID SCOTT PK_ID SCOTT TEST USERS VALID
發現索引和約束都沒有在hr的test表中都沒有，且hr下的test表統計信息也沒有收集！
導入實驗三：指定參數exclude=index
--hr用戶下刪除表test: SQL> conn hr/hr;
Connected.
SQL> drop table test purge;
Import: Release 11.2.0.4.0 - Production on Wed Jan 24 01:24:54 2018
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
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test exclude=index
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."TEST" 5.414 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 24 01:24:56 2018 elapsed 0 00:00:02
[oracle@wang ~]$
--驗證：
SQL> select OWNER, SEGMENT_NAME,TABLESPACE_NAME,PARTITION_NAME, BYTES / 1024 / 1024 size_m from dba_segments where segment_name='TEST';
OWNER SEGMENT_NAME TABLESPACE_NAME PARTITION_NAME SIZE_M ---------- ------------------------- ------------------------- ------------------------- ---------- SCOTT TEST USERS .0625 HR TEST TEST .0625
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED from dba_tables where table_name ='TEST';
OWNER TABLE_NAME TABLESPACE_NAME STATUS LAST_ANALYZED PAR ---------- ------------------------- ------------------------- -------- ------------------- --- SCOTT TEST USERS VALID 2018-01-24 00:58:46 NO HR TEST TEST VALID 2018-01-24 00:58:46 NO
SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME,status from dba_indexes where table_name='TEST';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME STATUS ---------- ------------------------- --------------- ------------------------- ------------------------- -------- HR PK_ID HR TEST TEST VALID SCOTT IDX_NAME SCOTT TEST USERS VALID SCOTT PK_ID SCOTT TEST USERS VALID
導入實驗四：指定參數exclude=statistics
--hr用戶下刪除表test: SQL> conn hr/hr;
Connected.
SQL> drop table test purge;
Import: Release 11.2.0.4.0 - Production on Wed Jan 24 01:29:26 2018
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
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test exclude=statistics
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."TEST" 5.414 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 24 01:29:29 2018 elapsed 0 00:00:02
[oracle@wang ~]$
--驗證：
SQL> select OWNER, SEGMENT_NAME,TABLESPACE_NAME,PARTITION_NAME, BYTES / 1024 / 1024 size_m from dba_segments where segment_name='TEST';
OWNER SEGMENT_NAME TABLESPACE_NAME PARTITION_NAME SIZE_M ---------- ------------------------- ------------------------- ------------------------- ---------- SCOTT TEST USERS .0625 HR TEST TEST .0625
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED from dba_tables where table_name ='TEST';
OWNER TABLE_NAME TABLESPACE_NAME STATUS LAST_ANALYZED PAR ---------- ------------------------- ------------------------- -------- ------------------- --- SCOTT TEST USERS VALID 2018-01-24 00:58:46 NO HR TEST TEST VALID NO
SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME,status from dba_indexes where table_name='TEST';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME STATUS ---------- ------------------------- --------------- ------------------------- ------------------------- -------- HR PK_ID HR TEST TEST VALID HR IDX_NAME HR TEST TEST VALID SCOTT IDX_NAME SCOTT TEST USERS VALID SCOTT PK_ID SCOTT TEST USERS VALID
導入實驗五：指定參數exclude=constraint
--hr用戶下刪除表test: SQL> conn hr/hr;
Connected.
SQL> drop table test purge;
Import: Release 11.2.0.4.0 - Production on Wed Jan 24 01:32:12 2018
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
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test exclude=constraint
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."TEST" 5.414 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 24 01:32:15 2018 elapsed 0 00:00:02
[oracle@wang ~]$
--驗證：
SQL> select OWNER, SEGMENT_NAME,TABLESPACE_NAME,PARTITION_NAME, BYTES / 1024 / 1024 size_m from dba_segments where segment_name='TEST';
OWNER SEGMENT_NAME TABLESPACE_NAME PARTITION_NAME SIZE_M ---------- ------------------------- ------------------------- ------------------------- ---------- SCOTT TEST USERS .0625 HR TEST TEST .0625
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED from dba_tables where table_name ='TEST';
OWNER TABLE_NAME TABLESPACE_NAME STATUS LAST_ANALYZED PAR ---------- ------------------------- ------------------------- -------- ------------------- --- SCOTT TEST USERS VALID 2018-01-24 00:58:46 NO HR TEST TEST VALID 2018-01-24 00:58:46 NO
SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME,status from dba_indexes where table_name='TEST';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME STATUS ---------- ------------------------- --------------- ------------------------- ------------------------- -------- HR PK_ID HR TEST TEST VALID HR IDX_NAME HR TEST TEST VALID SCOTT IDX_NAME SCOTT TEST USERS VALID SCOTT PK_ID SCOTT TEST USERS VALID
