expdp/impdp导入导出后索引约束统计信息情况
发表于：2025-01-20 作者：千家信息网编辑
千家信息网最后更新 2025年01月20日，实验如下：SQL> conn scott/tiger;Connected.SQL> select * from tab;TNAME TABTYPE CLUSTERID-----------------
千家信息网最后更新 2025年01月20日expdp/impdp导入导出后索引约束统计信息情况实验如下：
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ --------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
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
[oracle@wang ~]$
--查看导出文件：
[oracle@wang ~]$ ll test*
-rw-r----- 1 oracle oinstall 122880 Jan 24 01:03 test.dmp
-rw-r--r-- 1 oracle oinstall 1308 Jan 24 01:03 test.log
[oracle@wang ~]$
授权：
[oracle@wang ~]$ chmod u+x test*
[oracle@wang ~]$
[oracle@wang ~]$ ll test*
-rwxr----- 1 oracle oinstall 122880 Jan 24 01:03 test.dmp
-rwxr--r-- 1 oracle oinstall 1308 Jan 24 01:03 test.log
导入实验一：参数exclude不指定，都导入
执行导入：导入到hr用户、test表空间
impdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test
导入报错：ORA-01950: no privileges on tablespace 'TEST'
执行：alter user hr quota unlimited on test;
再次执行，如下：
oracle@testdb ~]$ impdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test
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;
Table dropped.
--执行导入：
[oracle@wang ~]$ impdp \'\/ 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
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;
Table dropped.
--执行导入：
[oracle@wang ~]$ impdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test exclude=index
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;
Table dropped.
--执行导入：
[oracle@wang ~]$ impdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test exclude=statistics
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;
Table dropped.
--执行导入：
[oracle@wang ~]$ impdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test exclude=constraint
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
总结：expdp/impdp完成后，索引，唯一约束，主键还可以生效，索引，约束，统计信息都可以导入，同时注意：expdp/impdp还可以使用并行参数parallel以加快速度！！！！！！！！
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ --------------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
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
[oracle@wang ~]$
--查看导出文件：
[oracle@wang ~]$ ll test*
-rw-r----- 1 oracle oinstall 122880 Jan 24 01:03 test.dmp
-rw-r--r-- 1 oracle oinstall 1308 Jan 24 01:03 test.log
[oracle@wang ~]$
授权：
[oracle@wang ~]$ chmod u+x test*
[oracle@wang ~]$
[oracle@wang ~]$ ll test*
-rwxr----- 1 oracle oinstall 122880 Jan 24 01:03 test.dmp
-rwxr--r-- 1 oracle oinstall 1308 Jan 24 01:03 test.log
导入实验一：参数exclude不指定，都导入
执行导入：导入到hr用户、test表空间
impdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test
导入报错：ORA-01950: no privileges on tablespace 'TEST'
执行：alter user hr quota unlimited on test;
再次执行，如下：
oracle@testdb ~]$ impdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test
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;
Table dropped.
--执行导入：
[oracle@wang ~]$ impdp \'\/ 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
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;
Table dropped.
--执行导入：
[oracle@wang ~]$ impdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test exclude=index
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;
Table dropped.
--执行导入：
[oracle@wang ~]$ impdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test exclude=statistics
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;
Table dropped.
--执行导入：
[oracle@wang ~]$ impdp \'\/ as sysdba\' dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test exclude=constraint
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
总结：expdp/impdp完成后，索引，唯一约束，主键还可以生效，索引，约束，统计信息都可以导入，同时注意：expdp/impdp还可以使用并行参数parallel以加快速度！！！！！！！！
参数
实验
用户
验证
索引
信息
统计
再次
同时
对象
文件
目录
空间
速度
分析
查询
情况
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
军队网络安全龙头
服务器的操作与维护
海南整机销售软件开发
如何将t3数据库拷贝
希望之村能不能自己创建服务器
页面ajax加载数据库
网络安全具有以下哪些主要特性
软件开发项目尖兵颁奖词
网络技术成本
聚类分析数据库
黄浦区信息网络技术服务
软件开发按工时计算
pi数据库 点
加固服务器包含多少配件
恐龙岛无规则的服务器
软件开发时怎么自我介绍
云服务器打开淘宝广告
身边的网络安全故事漫画
网络安全法经典案例
芜湖安卓软件开发定制
网信如何保障网络安全
无限宝服务器错误
谢美意 数据库课程
苏州戴尔网络站点服务器
数据库的触发器
多对多关联数据库表
软件开发技术服务进项怎么提高
sc数据库
云服务器跟轻量云服务器区别
java项目服务器
