温馨提示×

温馨提示×

您好，登录后才能下订单哦！

密码登录×
登录注册×
获取短信验证码
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》
登 录 注册有礼
云服务器 香港服务器 高防服务器
最新更新 网站标签 地图导航
产品

expdp/impdp导入导出后索引约束统计信息情况

发布时间：2020-08-18 15:40:12 来源：ITPUB博客 阅读：388 作者：不一样的天空w 栏目：关系型数据库
实验如下：
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以加快速度！！！！！！！！




向AI问一下细节
推荐阅读：
  1. Java object queries using JXPath
  2. 利用高级Java、算法、三角学、分布计算设计自己的智能机器人 (转)

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。

expdp impdp 信息

猜你喜欢

最新资讯
相关推荐

相关标签

explain regexp expect exploit kodexplorer -exp explode devexpress pexpect expr dllexport export命令 constexpr mongoexport unexpecte expect免交互 expand dims explode() express.js express4.x
AI
助
手
产品服务
地区划分
专题活动
帮助支持
关于我们
售后咨询
7*24小时在线电话：400-100-2938
7*24小时在线 QQ：800811969
关注亿速云

亿速云公众号

手机网站二维码

Copyright © Yisu Cloud Ltd. All Rights Reserved. 2018 版权所有

广州亿速云计算有限公司粤ICP备17096448号-1 粤公网安备 44010402001142号增值电信业务经营许可证编号：B1-20181529