expdp/impdp导入导出后索引约束统计信息情况
发表于：2024-09-22 作者：千家信息网编辑
千家信息网最后更新 2024年09月22日，实验如下：SQL> conn scott/tiger;Connected.SQL> select * from tab;TNAME TABTYPE CLUSTERID-----------------
千家信息网最后更新 2024年09月22日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安全错误
数据库的锁怎样保障安全
曙光服务器怎么换内存条
怎么新建gdb 数据库
db2实例数据库
佳能相机 软件开发
服务器rsa公钥统一管理
勤哲 示例数据库
城市活动断层探测数据库建设
GGBOOK下载软件开发
博图wincc数据库在哪里
梦幻西游秒空服务器值多少钱
服务器怎么查硬盘序列号
苏州国兴互联网络科技
票的宝安全服务器接入地址
数据库维护技术要求
普宁电力系统软件开发
数据库小总结
网络技术公司内外部成本
服务器本地网络只有百兆
软件开发+专注+效率
包河区资金管理软件开发公司
数据库关闭文件格式表示
提示安全性数据库矛盾
深圳小学的网络安全课在哪看
四川壹鱼科技互联网有限公司
广州凌峰网络技术有限公司
太原市鑫思创软件开发
数据库登录权限怎么设置
智慧物联网络安全高峰论坛
山东信威网络技术有限公司
网络工程和网络安全专业相同吗
