By testing for a while, I believe that in the traditional schema mode exp job, the tables are exported in the alphabetical order of their name; whereas in the data pump exp job, the tables are exported in the ascending order of their creation time.
Belwo are more detailed steps and observations.
First I created ten tables with sigle letter table name in random order in a 10g database, i.e.
testsrv1:/home/oracle/denis [testdb1] $ grep "create table" expord.sql
create table c
create table b
create table j
create table k
create table m
create table l
create table p
create table d
create table v
create table t
Then I did several tests using exp/imp or datapump exp/imp:
Test 1 schema mode export
I found that during the export using schema mode, the tables are exported in alphabetical order. I issued the following command:
exp testusr rows=n log=exp1.log owner=testusr file=exp1.dmp
In the exp1.log, it can be seen that the table exporting order is as follows:
. about to export TESTUSR's tables via Conventional Path ...
. . exporting table B
. . exporting table C
. . exporting table D
. . exporting table J
. . exporting table K
. . exporting table L
. . exporting table M
. . exporting table P
. . exporting table T
. . exporting table TEST
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table V
If I use the above exp dump file to do an imp and generate index file, I can observe that the 'create table' ddl statments in the indexfile is in the same order as the exporting table order, shown as follows:
imp testusr ignore=y full=y file=exp1.dmp log=imp1.log indexfile=imp1_index.sql
testsrv1:/home/oracle/denis [testdb1] $ grep "CREATE TABLE" imp1_index.sql
REM CREATE TABLE "TESTUSR"."B" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."C" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."D" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."J" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."K" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."L" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."M" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."P" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."T" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."TEST" ("N" NUMBER) PCTFREE 10 PCTUSED 40
REM CREATE TABLE "TESTUSR"."V" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
Test 2 table mode export
In this test, I created the following parfile for table-mode export:
testsrv1:/home/oracle/denis [testdb1] $ cat exp.par
rows=n
tables=(
K
,B
,P
,D
,T
,J
,L
,M
,C
)
We can see that the table exporting order is same as in the parfile:
testsrv1:/home/oracle/denis [testdb1] $ exp testusr log=exp2.log parfile=exp.par file=exp2.dmp
Export: Release 10.2.0.1.0 - Production on Wed Apr 8 06:40:41 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table K
. . exporting table B
. . exporting table P
. . exporting table D
. . exporting table T
. . exporting table J
. . exporting table L
. . exporting table M
. . exporting table C
Export terminated successfully without warnings.
Again, the 'create table' ddl statement order in the indexfile is same as the table exporting order.
imp testusr ignore=y full=y file=exp2.dmp log=imp2.log indexfile=imp2_index.sql
testsrv1:/home/oracle/denis [testdb1] $ grep "CREATE TABLE" imp2_index.sql
REM CREATE TABLE "TESTUSR"."K" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."B" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."P" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."D" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."T" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."J" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."L" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."M" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
REM CREATE TABLE "TESTUSR"."C" ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
Test 3 datapump export
In this test, I issued the following command to do schema-mode export using datapump:
expdp testusr directory=dir1 dumpfile=expdp1.dmp schemas=testusr logfile=expdp1.log content=metadata_only
However, no information indicated in which order the tables were exported in the logfile:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "TESTUSR"."SYS_EXPORT_SCHEMA_01": testusr/******** directory=dir1 dumpfile=expdp1.dmp schemas=testusr/******** logfile=expdp1.log 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/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "TESTUSR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSR.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/denis/expdp1.dmp
Job "TESTUSR"."SYS_EXPORT_SCHEMA_01" successfully completed at 06:59:52
Similarly, we can generate ddl statements by specifying sqlfile option in the impdp session:
impdp testusr directory=dir1 dumpfile=expdp1.dmp logfile=impdp1.log sqlfile=impdp1_sql.sql
Checked the create table order in the generated impdp1_sql.sql, we could see the order was acutally same as the order I created them.
testsrv1:/home/oracle/denis [testdb1] $ grep "CREATE TABLE" impdp1_sql.sql
CREATE TABLE "TESTUSR"."TEST"
CREATE TABLE "TESTUSR"."C"
CREATE TABLE "TESTUSR"."B"
CREATE TABLE "TESTUSR"."J"
CREATE TABLE "TESTUSR"."K"
CREATE TABLE "TESTUSR"."M"
CREATE TABLE "TESTUSR"."L"
CREATE TABLE "TESTUSR"."P"
CREATE TABLE "TESTUSR"."D"
CREATE TABLE "TESTUSR"."V"
CREATE TABLE "TESTUSR"."T"
I modified the table structure of table B and did the above expdp/impdp again, and the create table order did not change. I dropped the table B and re-created it, and repeated the steps again. Now the 'create table b' moved to the last position. So what I can say is most likely , the order of the table export using data pump with schema mode is by the table creation time.
Note: table test is an old table which exists before I perform the test.
No comments:
Post a Comment