impdp数据泵导入使用table_exists_action=SKIP存在的问题及如何接着导入后续的索引等信息
impdp数据泵导入使用table_exists_action=SKIP存在的问题及如何接着导入后续的索引等信息
小麦苗DBA宝典
发布于 2023-04-27 13:19:51
发布于 2023-04-27 13:19:51
引言
我们在使用数据泵导入数据库时，可能存在这种情况：
数据泵已经跑了8个小时了，而且表的数据都已经入库了，就剩后边的索引、约束和触发器等，那难道我还得重新从头接着导入吗？答案不是的，我们有办法，就是结合“table_exists_action=SKIP content=metadata_only sqlfile=index.sql”这几个参数来解决。
实验1：使用skip选项
lhr用户下t1和t3表，t1表含有1个索引：
[root@lhr ~]# docker start lhrora11204
lhrora11204
[root@lhr ~]# docker exec -it lhrora11204 bash
[root@lhrora11204 /]#
[root@lhrora11204 /]#
[root@lhrora11204 /]# su - oracle
[oracle@lhrora11204 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 10 01:30:51 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@LHR11G> startup
ORACLE instance started.
Total System Global Area 325685248 bytes
Fixed Size 2252944 bytes
Variable Size 188747632 bytes
Database Buffers 130023424 bytes
Redo Buffers 4661248 bytes
Database mounted.
Database opened.
SYS@LHR11G>
SYS@LHR11G>
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G>
LHR@LHR11G>
LHR@LHR11G> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
T1 TABLE
T3 TABLE
LHR@LHR11G> create index idx_1 on t1(owner);
Index created.
LHR@LHR11G> select count(*) from user_indexes;
COUNT(*)
----------
1
LHR@LHR11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$
ECTORY=D[oracle@lhrora11204 ~]$ expdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp schemas=lhr
Export: Release 11.2.0.4.0 - Production on Sat Dec 10 01:37:06 2022
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 "LHR"."SYS_EXPORT_SCHEMA_01": lhr/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp schemas=lhr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 20 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "LHR"."T1" 8.431 MB 86960 rows
. . exported "LHR"."T3" 8.431 MB 86962 rows
Master table "LHR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for LHR.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/LHR11G/dpdump/a.dmp
Job "LHR"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 10 01:37:41 2022 elapsed 0 00:00:23
[oracle@lhrora11204 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 10 01:44:00 2022
Copyright (c) 1982, 2013, Oracle. 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
SYS@LHR11G> alter user system identified by lhr;
User altered.
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G> drop table t1;
Table dropped.
LHR@LHR11G> create table t1 as select * from dba_objects;
Table created.
LHR@LHR11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 10 01:44:51 2022
Copyright (c) 1982, 2013, Oracle. 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
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G> select count(*) from user_indexes;
COUNT(*)
----------
0
LHR@LHR11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$ impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp table_exists_action=SKIP schemas=lhr
Import: Release 11.2.0.4.0 - Production on Sat Dec 10 01:45:13 2022
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 "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp table_exists_action=SKIP schemas=lhr
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LHR" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "LHR"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Table "LHR"."T3" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Sat Dec 10 01:45:15 2022 elapsed 0 00:00:02
[oracle@lhrora11204 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 10 01:45:27 2022
Copyright (c) 1982, 2013, Oracle. 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
SYS@LHR11G> select count(*) from user_indexes;
COUNT(*)
----------
1251
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G> select count(*) from user_indexes;
COUNT(*)
----------
0
LHR@LHR11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lhrora11204 ~]$ impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp table_exists_action=replace schemas=lhr
Import: Release 11.2.0.4.0 - Production on Sat Dec 10 01:46:23 2022
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 "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp table_exists_action=replace schemas=lhr
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LHR" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "LHR"."T1" 8.431 MB 86960 rows
. . imported "LHR"."T3" 8.431 MB 86962 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Sat Dec 10 01:46:27 2022 elapsed 0 00:00:03
[oracle@lhrora11204 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 10 01:46:35 2022
Copyright (c) 1982, 2013, Oracle. 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
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G> select count(*) from user_indexes;
COUNT(*)
----------
1
LHR@LHR11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$ expdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr content=metadata_only
Export: Release 11.2.0.4.0 - Production on Sat Dec 10 01:57:59 2022
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr content=metadata_only
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/LHR11G/dpdump/b.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 10 01:58:06 2022 elapsed 0 00:00:07
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 10 01:58:45 2022
Copyright (c) 1982, 2013, Oracle. 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
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G>
LHR@LHR11G>
LHR@LHR11G> drop table t1;
Table dropped.
LHR@LHR11G> create table t1 as select * from dba_objects;
Table created.
LHR@LHR11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lhrora11204 ~]$
[oracle@lhrora11204 ~]$
hr DIRECTORY=DATA_PUMP_DIR dumpfil[oracle@lhrora11204 ~]$ impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp table_exists_action=SKIPschemas=lhr
Import: Release 11.2.0.4.0 - Production on Sat Dec 10 02:00:18 2022
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 "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp table_exists_action=SKIP schemas=lhr
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LHR" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "LHR"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Table "LHR"."T3" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Sat Dec 10 02:00:21 2022 elapsed 0 00:00:02
[oracle@lhrora11204 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 10 02:00:26 2022
Copyright (c) 1982, 2013, Oracle. 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
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G> select count(*) from user_indexes;
COUNT(*)
----------
0
LHR@LHR11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lhrora11204 dpdump]$ impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr content=metadata_only table_exists_action=append
Import: Release 11.2.0.4.0 - Production on Sat Dec 10 03:01:49 2022
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 "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr content=metadata_only table_exists_action=append
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LHR" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "LHR"."T1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "LHR"."T3" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Sat Dec 10 03:01:53 2022 elapsed 0 00:00:03
[oracle@lhrora11204 dpdump]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 10 03:02:30 2022
Copyright (c) 1982, 2013, Oracle. 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
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G> select count(*) from user_indexes;
COUNT(*)
----------
0
LHR@LHR11G>
[oracle@lhrora11204 ~]$ impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr sqlfile=index.sql
Import: Release 11.2.0.4.0 - Production on Sat Dec 10 02:02:24 2022
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 "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr sqlfile=index.sql
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Sat Dec 10 02:02:26 2022 elapsed 0 00:00:01
[oracle@lhrora11204 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 10 02:02:32 2022
Copyright (c) 1982, 2013, Oracle. 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
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G> select count(*) from user_indexes;
COUNT(*)
----------
0
LHR@LHR11G>
LHR@LHR11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lhrora11204 ~]$ cd /u01/app/oracle/admin/LHR11G/dpdump/
[oracle@lhrora11204 dpdump]$ ll
total 17732
-rw-r----- 1 oracle oinstall 17907712 Dec 10 01:37 a.dmp
-rw-r----- 1 oracle oinstall 221184 Dec 10 01:58 b.dmp
-rw-r----- 1 oracle oinstall 116 Jul 24 2020 dp.log
-rw-r--r-- 1 oracle oinstall 1317 Dec 10 01:58 export.log
-rw-r--r-- 1 oracle oinstall 1146 Dec 10 02:02 import.log
-rw-r--r-- 1 oracle oinstall 15946 Dec 10 02:02 index.sql
[oracle@lhrora11204 dpdump]$ more index.sql
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
CREATE USER "LHR" IDENTIFIED BY VALUES 'S:072AA6DEB5DA3D824222DE075DEA77DB5107CD66984BE2C2551A719546E8;157AE4BCFD41976D'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "LHR";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
GRANT "DBA" TO "LHR";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "LHR" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT LHR
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'LHR11G', inst_scn=>'1336883');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYSTEM
CREATE TABLE "LHR"."T1"
( "OWNER" VARCHAR2(30 BYTE),
"OBJECT_NAME" VARCHAR2(128 BYTE),
"SUBOBJECT_NAME" VARCHAR2(30 BYTE),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19 BYTE),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19 BYTE),
"STATUS" VARCHAR2(7 BYTE),
"TEMPORARY" VARCHAR2(1 BYTE),
"GENERATED" VARCHAR2(1 BYTE),
"SECONDARY" VARCHAR2(1 BYTE),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
CREATE TABLE "LHR"."T3"
( "OWNER" VARCHAR2(30 BYTE),
"OBJECT_NAME" VARCHAR2(128 BYTE),
"SUBOBJECT_NAME" VARCHAR2(30 BYTE),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19 BYTE),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19 BYTE),
"STATUS" VARCHAR2(7 BYTE),
"TEMPORARY" VARCHAR2(1 BYTE),
"GENERATED" VARCHAR2(1 BYTE),
"SECONDARY" VARCHAR2(1 BYTE),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- CONNECT LHR
CREATE INDEX "LHR"."IDX_1" ON "LHR"."T1" ("OWNER")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "LHR"."IDX_1" NOPARALLEL;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- CONNECT SYSTEM
DECLARE I_N VARCHAR2(60);
I_O VARCHAR2(60);
NV VARCHAR2(1);
c DBMS_METADATA.T_VAR_COLL;
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALU
ES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'IDX_1';
i_o := 'LHR';
EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,86960,204,30,6,77,2337,1,86960,NV,NV,TO_DATE('2022-12-10 01:32:35',df),NV;
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
DECLARE
c varchar2(60);
nv varchar2(1);
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
s varchar2(60) := 'LHR';
t varchar2(60) := 'T1';
p varchar2(1);
sp varchar2(1);
stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,
cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,2,t,p,sp,s,
86960,1270,98,86960,0,NULL,NULL,NULL,
TO_DATE('2022-12-03 06:00:07',df));
c := 'OWNER';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
30,.0333333333333333,30,86960,0,3.39127382666026E+35,4.58306556462839E+35,6,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'415045585F303330323030','584442',nv,2,nv;
c := 'OBJECT_NAME';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
52172,.0000191673694702139,52172,86960,0,2.45035608287067E+35,6.29634626559793E+35,25,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'2F31303030333233645F44656C6567617465496E766F636174696F6E4861','794362437253756253616D706C6
96E67547970653232395F54',nv,2,nv;
c := 'SUBOBJECT_NAME';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
141,.00709219858156028,141,513,86447,1.88673574325567E+35,4.53398699964451E+35,2,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'2456534E5F31','575248245F5741495453545F323030373934373535315F30',nv,2,nv;
c := 'OBJECT_ID';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
86960,.0000114995400183993,86960,86958,2,2,92227,5,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'C103','C30A171C',nv,2,nv;
END;
/
DECLARE
c varchar2(60);
nv varchar2(1);
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
s varchar2(60) := 'LHR';
t varchar2(60) := 'T1';
p varchar2(1);
sp varchar2(1);
stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,
cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
NULL;
c := 'DATA_OBJECT_ID';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
9073,.000110217127741651,9073,9145,77815,0,92227,2,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'80','C30A171C',nv,2,nv;
c := 'OBJECT_TYPE';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
46,.0217391304347826,46,86960,0,3.49432112834658E+35,4.58489900435076E+35,9,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'434C5553544552','584D4C20534348454D41',nv,2,nv;
c := 'CREATED';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
1059,.000944287063267233,1059,86960,0,2456529.48443287,2459917.05729167,8,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'787108180C2624','787A0C0302171F',nv,2,nv;
c := 'LAST_DDL_TIME';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
1143,.000874890638670166,1143,86958,2,2452549.52903935,2459917.05729167,8,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'78660A010D2A32','787A0C0302171F',nv,2,nv;
c := 'TIMESTAMP';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
1205,.000829875518672199,1205,86958,2,2.55583174330788E+35,2.60592375524727E+35,20,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'313939302D30382D32363A31313A32353A3030','323032322D31322D30333A30313A32323A3330',nv,2,nv;
END;
/
DECLARE
c varchar2(60);
nv varchar2(1);
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
s varchar2(60) := 'LHR';
t varchar2(60) := 'T1';
p varchar2(1);
sp varchar2(1);
stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,
cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
NULL;
c := 'STATUS';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
1,1,1,86960,0,4.47861930473196E+35,4.47861930473196E+35,6,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'56414C4944','56414C4944',nv,2,nv;
c := 'TEMPORARY';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
2,.5,2,86960,0,4.04999154965717E+35,4.62114420409600E+35,2,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'4E','59',nv,2,nv;
c := 'GENERATED';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
2,.5,2,86960,0,4.04999154965717E+35,4.62114420409600E+35,2,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'4E','59',nv,2,nv;
c := 'SECONDARY';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
2,.5,2,86960,0,4.04999154965717E+35,4.62114420409600E+35,2,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'4E','59',nv,2,nv;
c := 'NAMESPACE';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
21,.0476190476190476,21,86958,2,1,64,3,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),'C102','C141',nv,2,nv;
END;
/
DECLARE
c varchar2(60);
nv varchar2(1);
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
s varchar2(60) := 'LHR';
t varchar2(60) := 'T1';
p varchar2(1);
sp varchar2(1);
stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,
cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
NULL;
c := 'EDITION_NAME';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
0,0,0,nv,86960,nv,nv,0,0,nv,nv,
TO_DATE('2022-12-03 06:00:07',df),nv,nv,nv,2,nv;
DBMS_STATS.IMPORT_TABLE_STATS('"LHR"','"T1"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
DECLARE
c varchar2(60);
nv varchar2(1);
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
s varchar2(60) := 'LHR';
t varchar2(60) := 'T3';
p varchar2(1);
sp varchar2(1);
stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,
cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,2,t,p,sp,s,
86962,1270,98,86962,0,NULL,NULL,NULL,
TO_DATE('2022-12-03 06:00:08',df));
c := 'OWNER';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
31,.032258064516129,31,86962,0,3.39127382666026E+35,4.58306556462839E+35,6,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'415045585F303330323030','584442',nv,2,nv;
c := 'OBJECT_NAME';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
52172,.0000191673694702139,52172,86962,0,2.45035608287067E+35,6.29634626559793E+35,25,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'2F31303030333233645F44656C6567617465496E766F636174696F6E4861','794362437253756253616D706C6
96E67547970653232395F54',nv,2,nv;
c := 'SUBOBJECT_NAME';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
141,.00709219858156028,141,513,86449,1.88673574325567E+35,4.53398699964451E+35,2,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'2456534E5F31','575248245F5741495453545F323030373934373535315F30',nv,2,nv;
c := 'OBJECT_ID';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
86962,.0000114992755456406,86962,86960,2,2,92229,5,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'C103','C30A171E',nv,2,nv;
END;
/
DECLARE
c varchar2(60);
nv varchar2(1);
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
s varchar2(60) := 'LHR';
t varchar2(60) := 'T3';
p varchar2(1);
sp varchar2(1);
stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,
cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
NULL;
c := 'DATA_OBJECT_ID';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
9075,.000110192837465565,9075,9147,77815,0,92229,2,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'80','C30A171E',nv,2,nv;
c := 'OBJECT_TYPE';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
46,.0217391304347826,46,86962,0,3.49432112834658E+35,4.58489900435076E+35,9,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'434C5553544552','584D4C20534348454D41',nv,2,nv;
c := 'CREATED';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
1061,.000942507068803016,1061,86962,0,2456529.48443287,2459917.05780093,8,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'787108180C2624','787A0C0302180F',nv,2,nv;
c := 'LAST_DDL_TIME';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
1145,.000873362445414847,1145,86960,2,2452549.52903935,2459917.05780093,8,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'78660A010D2A32','787A0C0302180F',nv,2,nv;
c := 'TIMESTAMP';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
1207,.000828500414250207,1207,86960,2,2.55583174330788E+35,2.60592375524727E+35,20,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'313939302D30382D32363A31313A32353A3030','323032322D31322D30333A30313A32333A3134',nv,2,nv;
END;
/
DECLARE
c varchar2(60);
nv varchar2(1);
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
s varchar2(60) := 'LHR';
t varchar2(60) := 'T3';
p varchar2(1);
sp varchar2(1);
stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,
cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
NULL;
c := 'STATUS';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
1,1,1,86962,0,4.47861930473196E+35,4.47861930473196E+35,6,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'56414C4944','56414C4944',nv,2,nv;
c := 'TEMPORARY';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
2,.5,2,86962,0,4.04999154965717E+35,4.62114420409600E+35,2,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'4E','59',nv,2,nv;
c := 'GENERATED';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
2,.5,2,86962,0,4.04999154965717E+35,4.62114420409600E+35,2,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'4E','59',nv,2,nv;
c := 'SECONDARY';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
2,.5,2,86962,0,4.04999154965717E+35,4.62114420409600E+35,2,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'4E','59',nv,2,nv;
c := 'NAMESPACE';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
21,.0476190476190476,21,86960,2,1,64,3,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),'C102','C141',nv,2,nv;
END;
/
DECLARE
c varchar2(60);
nv varchar2(1);
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
s varchar2(60) := 'LHR';
t varchar2(60) := 'T3';
p varchar2(1);
sp varchar2(1);
stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,
cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
NULL;
c := 'EDITION_NAME';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
0,0,0,nv,86962,nv,nv,0,0,nv,nv,
TO_DATE('2022-12-03 06:00:08',df),nv,nv,nv,2,nv;
DBMS_STATS.IMPORT_TABLE_STATS('"LHR"','"T3"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
[oracle@lhrora11204 dpdump]$
[oracle@lhrora11204 dpdump]$
[oracle@lhrora11204 dpdump]$
[oracle@lhrora11204 dpdump]$ more index.sql
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
CREATE USER "LHR" IDENTIFIED BY VALUES 'S:072AA6DEB5DA3D824222DE075DEA77DB5107CD66984BE2C2551A719546E8;157AE4BCFD41976D'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "LHR";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
GRANT "DBA" TO "LHR";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "LHR" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT LHR
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'LHR11G', inst_scn=>'1336883');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYSTEM
CREATE TABLE "LHR"."T1"
( "OWNER" VARCHAR2(30 BYTE),
"OBJECT_NAME" VARCHAR2(128 BYTE),
"SUBOBJECT_NAME" VARCHAR2(30 BYTE),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19 BYTE),
[oracle@lhrora11204 dpdump]$
[oracle@lhrora11204 dpdump]$
[oracle@lhrora11204 dpdump]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 10 02:05:27 2022
Copyright (c) 1982, 2013, Oracle. 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
SYS@LHR11G> @index.sql
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
CREATE USER "LHR" IDENTIFIED BY VALUES 'S:072AA6DEB5DA3D824222DE075DEA77DB5107CD66984BE2C2551A719546E8;157AE4BCFD41976D'
*
ERROR at line 1:
ORA-01920: user name 'LHR' conflicts with another user or role name
Grant succeeded.
Grant succeeded.
User altered.
PL/SQL procedure successfully completed.
CREATE TABLE "LHR"."T1"
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
CREATE TABLE "LHR"."T3"
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Index created.
Index altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G>
LHR@LHR11G> select count(*) from user_indexes;
COUNT(*)
----------
1
LHR@LHR11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lhrora11204 dpdump]$ more index.sql | grep drop
[oracle@lhrora11204 dpdump]$
[oracle@lhrora11204 dpdump]$
[oracle@lhrora11204 dpdump]$ more index.sql | grep drop -i
[oracle@lhrora11204 dpdump]$ more index.sql | grep create -i
CREATE USER "LHR" IDENTIFIED BY VALUES 'S:072AA6DEB5DA3D824222DE075DEA77DB5107CD66984BE2C2551A719546E8;157AE4BCFD41976D'
CREATE TABLE "LHR"."T1"
"CREATED" DATE,
CREATE TABLE "LHR"."T3"
"CREATED" DATE,
CREATE INDEX "LHR"."IDX_1" ON "LHR"."T1" ("OWNER")
c := 'CREATED';
c := 'CREATED';
实验2：导入一半后强制停止数据库
先导出sh用户的数据：
[oracle@lhrora11204 dpdump]$ expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=SYSTEM.dmp SCHEMAS=SH
Export: Release 11.2.0.4.0 - Production on Mon Dec 12 15:56:00 2022
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_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=SYSTEM.dmp SCHEMAS=SH
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 273.8 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/DIMENSION
. . exported "SH"."CUSTOMERS" 9.853 MB 55500 rows
. . exported "SH"."COSTS":"COSTS_Q1_1998" 139.5 KB 4411 rows
. . exported "SH"."COSTS":"COSTS_Q1_1999" 183.5 KB 5884 rows
. . exported "SH"."COSTS":"COSTS_Q1_2000" 120.6 KB 3772 rows
. . exported "SH"."COSTS":"COSTS_Q1_2001" 227.8 KB 7328 rows
. . exported "SH"."COSTS":"COSTS_Q2_1998" 79.52 KB 2397 rows
. . exported "SH"."COSTS":"COSTS_Q2_1999" 132.5 KB 4179 rows
. . exported "SH"."COSTS":"COSTS_Q2_2000" 119.0 KB 3715 rows
. . exported "SH"."COSTS":"COSTS_Q2_2001" 184.5 KB 5882 rows
. . exported "SH"."COSTS":"COSTS_Q3_1998" 131.1 KB 4129 rows
. . exported "SH"."COSTS":"COSTS_Q3_1999" 137.3 KB 4336 rows
. . exported "SH"."COSTS":"COSTS_Q3_2000" 151.4 KB 4798 rows
. . exported "SH"."COSTS":"COSTS_Q3_2001" 234.4 KB 7545 rows
. . exported "SH"."COSTS":"COSTS_Q4_1998" 144.7 KB 4577 rows
. . exported "SH"."COSTS":"COSTS_Q4_1999" 159.0 KB 5060 rows
. . exported "SH"."COSTS":"COSTS_Q4_2000" 160.2 KB 5088 rows
. . exported "SH"."COSTS":"COSTS_Q4_2001" 278.4 KB 9011 rows
. . exported "SH"."SALES":"SALES_Q1_1998" 1.412 MB 43687 rows
. . exported "SH"."SALES":"SALES_Q1_1999" 2.071 MB 64186 rows
. . exported "SH"."SALES":"SALES_Q1_2000" 2.012 MB 62197 rows
. . exported "SH"."SALES":"SALES_Q1_2001" 1.965 MB 60608 rows
. . exported "SH"."SALES":"SALES_Q2_1998" 1.160 MB 35758 rows
. . exported "SH"."SALES":"SALES_Q2_1999" 1.754 MB 54233 rows
. . exported "SH"."SALES":"SALES_Q2_2000" 1.802 MB 55515 rows
. . exported "SH"."SALES":"SALES_Q2_2001" 2.051 MB 63292 rows
. . exported "SH"."SALES":"SALES_Q3_1998" 1.633 MB 50515 rows
. . exported "SH"."SALES":"SALES_Q3_1999" 2.166 MB 67138 rows
. . exported "SH"."SALES":"SALES_Q3_2000" 1.909 MB 58950 rows
. . exported "SH"."SALES":"SALES_Q3_2001" 2.130 MB 65769 rows
. . exported "SH"."SALES":"SALES_Q4_1998" 1.581 MB 48874 rows
. . exported "SH"."SALES":"SALES_Q4_1999" 2.014 MB 62388 rows
. . exported "SH"."SALES":"SALES_Q4_2000" 1.814 MB 55984 rows
. . exported "SH"."SALES":"SALES_Q4_2001" 2.257 MB 69749 rows
. . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS" 697.3 KB 4500 rows
. . exported "SH"."FWEEK_PSCAT_SALES_MV" 419.8 KB 11266 rows
. . exported "SH"."PROMOTIONS" 58.89 KB 503 rows
. . exported "SH"."TIMES" 380.8 KB 1826 rows
. . exported "SH"."CAL_MONTH_SALES_MV" 6.312 KB 48 rows
. . exported "SH"."CHANNELS" 7.25 KB 5 rows
. . exported "SH"."COUNTRIES" 10.20 KB 23 rows
. . exported "SH"."PRODUCTS" 26.17 KB 72 rows
. . exported "SH"."COSTS":"COSTS_1995" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_1996" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_H1_1997" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_H2_1997" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2003" 0 KB 0 rows
. . exported "SH"."DIMENSION_EXCEPTIONS" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_1995" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_1996" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_H1_1997" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_H2_1997" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q1_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q1_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q2_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q2_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q3_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q3_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q4_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q4_2003" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/LHR11G/dpdump/SYSTEM.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Dec 12 15:56:34 2022 elapsed 0 00:00:31
然后再导入sh用户的数据到lhr用户，在导入一半的时候将数据库重启：
[oracle@lhrora11204 dpdump]$ impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=SYSTEM.dmp FULL=Y REMAP_SCHEMA=SH:LHR
Import: Release 11.2.0.4.0 - Production on Mon Dec 12 15:57:32 2022
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_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=SYSTEM.dmp FULL=Y REMAP_SCHEMA=SH:LHR
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LHR" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "LHR"."CUSTOMERS" 9.853 MB 55500 rows
. . imported "LHR"."COSTS":"COSTS_Q1_1998" 139.5 KB 4411 rows
. . imported "LHR"."COSTS":"COSTS_Q1_1999" 183.5 KB 5884 rows
. . imported "LHR"."COSTS":"COSTS_Q1_2000" 120.6 KB 3772 rows
. . imported "LHR"."COSTS":"COSTS_Q1_2001" 227.8 KB 7328 rows
. . imported "LHR"."COSTS":"COSTS_Q2_1998" 79.52 KB 2397 rows
. . imported "LHR"."COSTS":"COSTS_Q2_1999" 132.5 KB 4179 rows
. . imported "LHR"."COSTS":"COSTS_Q2_2000" 119.0 KB 3715 rows
. . imported "LHR"."COSTS":"COSTS_Q2_2001" 184.5 KB 5882 rows
. . imported "LHR"."COSTS":"COSTS_Q3_1998" 131.1 KB 4129 rows
. . imported "LHR"."COSTS":"COSTS_Q3_1999" 137.3 KB 4336 rows
. . imported "LHR"."COSTS":"COSTS_Q3_2000" 151.4 KB 4798 rows
. . imported "LHR"."COSTS":"COSTS_Q3_2001" 234.4 KB 7545 rows
UDI-03113: operation generated ORACLE error 3113
ORA-03113: end-of-file on communication channel
Process ID: 1330
Session ID: 190 Serial number: 17
UDI-03114: operation generated ORACLE error 3114
ORA-03114: not connected to ORACLE
在导入一半的时候，强制重启数据库，此时已经导入了2个表。
在强制重启完数据库后，进入数据库查看：
SYS@LHR11G> conn lhr/lhr
Connected.
LHR@LHR11G> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
CAL_MONTH_SALES_MV TABLE
CHANNELS TABLE
COSTS TABLE
COUNTRIES TABLE
CUSTOMERS TABLE
DIMENSION_EXCEPTIONS TABLE
FWEEK_PSCAT_SALES_MV TABLE
PRODUCTS TABLE
PROMOTIONS TABLE
SALES TABLE
SALES_TRANSACTIONS_EXT TABLE
SUPPLEMENTARY_DEMOGRAPHICS TABLE
TIMES TABLE
13 rows selected.
LHR@LHR11G> conn sh/sh
Connected.
SH@LHR11G> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
CAL_MONTH_SALES_MV TABLE
CHANNELS TABLE
COSTS TABLE
COUNTRIES TABLE
CUSTOMERS TABLE
DIMENSION_EXCEPTIONS TABLE
FWEEK_PSCAT_SALES_MV TABLE
PRODUCTS TABLE
PROMOTIONS TABLE
SALES TABLE
SALES_TRANSACTIONS_EXT TABLE
SUPPLEMENTARY_DEMOGRAPHICS TABLE
TIMES TABLE
13 rows selected.
可以看到，LHR用户的表都已经创建完成了。由此可以推断出，尽管数据没有导入完成，但是在执行完“SCHEMA_EXPORT/TABLE/TABLE” 这个步骤后，其实数据库中的所有表都已经创建完成了。整个impdp导入过程是先创建所有表，然后再插入数据，而不是边建表边插入数据。
总结
1、使用参数table_exists_action=SKIP时需要特别注意，若表已存在，那么表上的索引不会被导入，所以可能导致索引丢失的问题。类似的提示：ORA-39151: Table "LHR"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
2、参数table_exists_action的默认值为SKIP，
TABLE_EXISTS_ACTION 默认为：SKIP 作用：定义了如果要导入的表已经存在，impdp的动作 值及其含义： SKIP:不管已经存在的表，直接跳过，会导致表上的索引、触发器、约束等缺失问题，也可能会导致数据丢失 APPEND:保持现有数据不变，导入源数据 TRUNCATE:删掉现有数据，导入源数据 REPLACE:删掉现有表，并重建，导入源数据
3、若需要重新做导入操作，而不导入之前已经导入的数据，那么可以使用如下的办法：
-- 导出导入
expdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp schemas=lhr
impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp schemas=lhr
-- 若报错需要从头开始导入（重复入库，比较慢）
impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp schemas=lhr table_exists_action=REPLACE
-- 若报错重复导入跳过之前已经导入的表
impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp table_exists_action=SKIP schemas=lhr
-- 最后再把元数据导出导入一下（必须）
expdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr content=metadata_only
impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr sqlfile=ddl.sql
@ddl.sql
-- 或者直接导入索引、约束和触发器
impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr INCLUDE=INDEX,CONSTRAINT,TRIGGER sqlfile=ddl.sql
@ddl.sql
注意： 1、最后的
sqlfile=ddl.sql也不能加 EXCLUDE=TABLE，否则也会排除掉索引 2、也可以只导最后的索引和约束。 3、在跑脚本建索引时需要注意临时表空间的使用率。
4、在执行完“SCHEMA_EXPORT/TABLE/TABLE” 这个步骤后，其实数据库中的所有表都已经创建完成了。整个impdp导入过程是先创建所有表，然后再插入数据，而不是边建表边插入数据。
5、最后需要特别注意的一点，使用方法3的前提条件是，必须保证所有的表和数据都已经导入完成了，否则会造成数据丢失，这个问题非常严峻。所以，若时间允许，我们强烈建议使用replace或truncate选项进行重新导入。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2022-12-14，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读