Expdp/impdp Index constraint Statistics after Import and Export 01/17 Update SLTechnology News&Howtos

Expdp/impdp Index constraint Statistics after Import and Export

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

The experiments are as follows:

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 recording Wagn')

1 row created.

SQL > insert into test values (2)

1 row created.

SQL > commit

Commit complete.

SQL > select * from test

ID NAME

1 wagn

2 xue

-- Analytical table:

-- query:

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

-- create a directory object:

SQL > create directory dir as'/ home/oracle'

Directory created.

SQL >

-- perform export:

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

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 ~] $

-- View the export file:

[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 ~] $

Authorization:

[oracle@wang] $chmod upright 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

Import experiment 1: parameter exclude is not specified, all are imported

Perform import: import to hr user, test tablespace

Impdp\'/ as sysdba\ 'dumpfile=test.dmp directory=dir LOGFILE=impdp.log remap_schema=scott:hr REMAP_TABLESPACE=USERS:test tables=scott.test

Import error report: ORA-01950: no privileges on tablespace 'TEST'

Execute: alter user hr quota unlimited on test

Execute it again, as follows:

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

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 ~] $

-- verify:

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

Import experiment 2: specify the parameter exclude=index,statistics,constraint

-- delete table test under hr user:

SQL > conn hr/hr

Connected.

SQL > drop table test purge

Table dropped.

-- perform import:

[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

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 ~] $

-- verify:

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

It is found that neither indexes nor constraints are in the test table of hr, and the statistics of the test table under hr are not collected!

Import Lab 3: specify the parameter exclude=index

-- delete table test under hr user:

SQL > conn hr/hr

Connected.

SQL > drop table test purge

Table dropped.

-- perform import:

[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

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 ~] $

-- verify:

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

Import Lab 4: specify the parameter exclude=statistics

-- delete table test under hr user:

SQL > conn hr/hr

Connected.

SQL > drop table test purge

Table dropped.

-- perform import:

[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

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 ~] $

-- verify:

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

Import Lab 5: specify the parameter exclude=constraint

-- delete table test under hr user:

SQL > conn hr/hr

Connected.

SQL > drop table test purge

Table dropped.

-- perform import:

[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

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 ~] $

-- verify:

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

Summary: after the completion of expdp/impdp, indexes, unique constraints, primary keys can also take effect, indexes, constraints, statistics can be imported, while note: expdp/impdp can also use the parallel parameter parallel to speed up!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.