expdp/impdp导入导出后索引约束统计信息情况
发表于：2025-11-07 作者：千家信息网编辑
千家信息网最后更新 2025年11月07日，实验如下：SQL> conn scott/tiger;Connected.SQL> select * from tab;TNAME TABTYPE CLUSTERID-----------------
千家信息网最后更新 2025年11月07日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安全错误
数据库的锁怎样保障安全
悠宝网络安全
电商网站数据库
网络安全融入大学思政
云服务器最好管理系统
简幻欢怎么启动服务器
早期的线性软件开发模型称为
图书馆数据库怎么改名字
巨杉数据库是什么公司
ube 数据库
数据库主键数据结构
2020年网络安全演练
三明定制软件开发
移动app软件开发好学吗
抓好网络安全宣传周
江苏省政务服务网数据库
畅指网络技术
无线网络技术导论期末试卷
饥荒服务器可以买吗
java多人修改数据库
明日之后第四季的服务器
网络技术十大股东
软件开发赚钱
打电话问你做不做软件开发
pi数据库哪里下载
观看护苗网络安全读后感家长
数据库大致可分为
连接服务器管理口
巴南区提供软件开发服务标志
文件在数据库中
武汉叁开网络技术有限公司
