活动第43/44次（2018-07-28星期六下午）
select* from dba_directories;create directory dir1 as '/home/oracle/dir1';
create directory dir2 as '/home/oracle/dir2';
grant read,write on directory dir1 to hr;
grant read,write on directory dir2 to hr;
select* from dba_tab_privs tp where tp.grantee='HR';
GRANTEEOWNERTABLE_NAMEGRANTORPRIVILEGEGRANTABLEHIERARCHYCOMMONTYPE
1HRSYSDBMS_STATSSYSEXECUTENONONOPACKAGE
2HRSYSDIR1SYSREADNONONODIRECTORY
3HRSYSDIR1SYSWRITENONONODIRECTORY
4HRSYSDIR2SYSREADNONONODIRECTORY
5HRSYSDIR2SYSWRITENONONODIRECTORY
CREATE TABLE hr.extab_employees
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
hire_date DATE)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER DEFAULT DIRECTORY dir1
ACCESS PARAMETERS
( records delimited by newline
badfile dir1:'empxt%a_%p.bad'
logfile dir1:'empxt%a_%p.log'
fields terminated by ','
missing field values are null
( employee_id, first_name, last_name,
hire_date char date_format date mask "dd-mon-yyyy"))
LOCATION (dir1:'empxt1.dat', dir2:'empxt2.dat') )
PARALLELREJECT LIMIT UNLIMITED;
select * from dba_tables t where t.owner='HR';
OWNERTABLE_NAMETABLESPACE_NAMECLUSTER_NAMEIOT_NAMESTATUSPCT_FREEPCT_USEDINI_TRANSMAX_TRANSINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASEFREELISTSFREELIST_GROUPSLOGGINGBACKED_UPNUM_ROWSBLOCKSEMPTY_BLOCKSAVG_SPACECHAIN_CNTAVG_ROW_LENAVG_SPACE_FREELIST_BLOCKSNUM_FREELIST_BLOCKSDEGREEINSTANCESCACHETABLE_LOCKSAMPLE_SIZELAST_ANALYZEDPARTITIONEDIOT_TYPETEMPORARYSECONDARYNESTEDBUFFER_POOLFLASH_CACHECELL_FLASH_CACHEROW_MOVEMENTGLOBAL_STATSUSER_STATSDURATIONSKIP_CORRUPTMONITORINGCLUSTER_OWNERDEPENDENCIESCOMPRESSIONCOMPRESS_FORDROPPEDREAD_ONLYSEGMENT_CREATEDRESULT_CACHECLUSTERINGACTIVITY_TRACKINGDML_TIMESTAMPHAS_IDENTITYCONTAINER_DATAINMEMORYINMEMORY_PRIORITYINMEMORY_DISTRIBUTEINMEMORY_COMPRESSIONINMEMORY_DUPLICATE
1HRSSMMSSMM VALID10401255 YESN00000000 1 1 NENABLED07/15/2018 9:10:56 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONONODEFAULTNO NONODISABLED
2HRSSMAUSERS VALID10 1255 YESN00000000 1 1 NENABLED07/15/2018 9:10:57 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONONODEFAULTNO NONODISABLED
3HRT05207_ASSMM VALID80205255 YESN00000000 1 1 NENABLED07/15/2018 9:10:57 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONONODEFAULTNO NONODISABLED
4HREMPLOYEESEXAMPLE VALID10 125565536104857612147483645 NON10750006900 1 1 NENABLED1077/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
5HREXTAB_EMPLOYEES没有表空间
VALID0000 YESN DEFAULT DEFAULT NENABLED NO NNNODEFAULTDEFAULTDEFAULTDISABLEDNONO DISABLEDNO DISABLEDDISABLEDBASICNONOYESDEFAULTNO NONODISABLED
6HRJOBSEXAMPLE VALID10 125565536104857612147483645 NON1950003300 1 1 NENABLED197/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
7HRT04209_UNAMETBSUNIFORM VALID10 1255524288524288121474836450 YESN1000002470001200 1 1 NENABLED1000007/15/2018 9:10:58 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
8HRREGIONSEXAMPLE VALID10 125565536104857612147483645 NON450001400 1 1 NENABLED47/7/2014 6:56:26 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
9HRT_BANK_INVOICEUSERS VALID10 125565536104857612147483645 YESN 1 1 NENABLED NO NNNODEFAULTDEFAULTDEFAULTDISABLEDNONO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
10HRJOB_HISTORYEXAMPLE VALID10 125565536104857612147483645 NON1050003100 1 1 NENABLED107/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
11HRT05207_BUSERS VALID10 125510485760104857612147483645 YESN01006000000 1 1 NENABLED07/15/2018 9:10:58 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
12HRCOUNTRIES VALID0000 N25 00150 1 1 NENABLED257/7/2014 6:56:24 AMNOIOTNNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLEDBASICNONOYESDEFAULTNO NONODISABLED
13HRLOCATIONSEXAMPLE VALID10 125565536104857612147483645 NON2350004900 1 1 NENABLED237/7/2014 6:56:26 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
14HRDEPARTMENTSEXAMPLE VALID10 125565536104857612147483645 NON2750002100 1 1 NENABLED277/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
select* from dba_external_tables et
where et.owner='HR' ;
由于控制文件和外部表都是识别行和列，所以应该可以从控制文件推出外部表：
sqlldrhr/oracle_4Ucontrol=ccb.ctl external_table=GENERATE_ONLY log=ccb.sql
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Jul 28 10:01:43 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Path used: External Table
看看ccb.sql:
drop table hr.T_BANK_INVOICE;
CREATE TABLE hr.T_BANK_INVOICE
(
"ACCOUNT_NUMBER" VARCHAR2(255),
"SUB_NUMBER" VARCHAR2(255),
"TRANSAC_DATE" DATE,
"TRANSAC_ADDRESS" VARCHAR2(600),
"OUT_AMOUNT" NUMBER(20,2),
"IN_AMOUNT" NUMBER(20,2),
"BALANCE" NUMBER(20,2),
"OPPOSITE_NUMBER" NUMBER,
"OPPOSITE_USERNAME" VARCHAR2(100),
"CURRENCY" VARCHAR2(100),
"TRANSAC_COMMENT" VARCHAR2(600)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY dir1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE dir1:'ccb.bad'
LOGFILE dir1:'ccb%a_%p.log'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"ACCOUNT_NUMBER" CHAR(255)
TERMINATED BY ",",
"SUB_NUMBER" CHAR(255)
TERMINATED BY ",",
"TRANSAC_DATE" (1:8) CHAR(8)
DATE_FORMAT DATE MASK "YYYYMMDD",
"TRANSAC_ADDRESS" (10) CHAR(255)
TERMINATED BY ",",
"OUT_AMOUNT" CHAR(255)
TERMINATED BY ",",
"IN_AMOUNT" CHAR(255)
TERMINATED BY ",",
"BALANCE" CHAR(255)
TERMINATED BY ",",
"OPPOSITE_NUMBER" CHAR(255)
TERMINATED BY ",",
"OPPOSITE_USERNAME" CHAR(255)
TERMINATED BY ",",
"CURRENCY" CHAR(255)
TERMINATED BY ",",
"TRANSAC_COMMENT" CHAR(255)
TERMINATED BY ","
)
)
location
(
'ccb.dat'
)
)REJECT LIMIT UNLIMITED;外部表都是只读的：
工行网银的控制文件：
LOAD DATA
characterset ZHS16GBK
INFILE 'icbc.dat'
DISCARDFILE 'icbc.dis'
append
INTO table t_bank_invoice
when (5) ='^'
FIELDS TERMINATED BY '^'
TRAILING NULLCOLS
(
account_number "1402029101000999999",
sub_number "00000",
transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
transac_address TERMINATED BY WHITESPACE
"substr(:transac_address,2)",
transac_comment TERMINATED BY WHITESPACE
"substr(:transac_comment,2)",
currency TERMINATED BY WHITESPACE
"substr(:currency,2)",
remit TERMINATED BY WHITESPACE
"substr(:remit,2)",
in_amount TERMINATED BY WHITESPACE
"TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
out_amount TERMINATED BY WHITESPACE
"TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
balance TERMINATED BY WHITESPACE
"TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
all_comment TERMINATED BY WHITESPACE
"substr(:all_comment,2)"
)
$ sqlldrhr/oracle_4Ucontrol=icbc.ctl external_table=GENERATE_ONLY log=icbc.sql
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Jul 28 10:29:02 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Path used: External Table
icbc.sql的内容：
drop table hr.T_BANK_INVOICE;
CREATE TABLEhr.T_BANK_INVOICE
(
"TRANSAC_DATE" DATE,
"TRANSAC_ADDRESS" VARCHAR2(600),
"TRANSAC_COMMENT" VARCHAR2(600),
"CURRENCY" VARCHAR2(255),
"REMIT" VARCHAR2(255),
"IN_AMOUNT" VARCHAR2(255),
"OUT_AMOUNT" VARCHAR2(255),
"BALANCE" VARCHAR2(255),
"ALL_COMMENT" VARCHAR2(2000)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY dir1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
LOAD WHEN ((5: 5) = "^")
BADFILE dir1:'icbc.bad'
DISCARDFILE dir1:'icbc.dis'
LOGFILE dir1:'icbc%a_%p.log'
READSIZE 1048576
FIELDS TERMINATED BY "^" LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"TRANSAC_DATE" (6:15) CHAR(10)
DATE_FORMAT DATE MASK "YYYY-MM-DD" ,
"TRANSAC_ADDRESS" char(255)
TERMINATED BY "^",
"TRANSAC_COMMENT" (48) CHAR(255)
TERMINATED BY "^",
"CURRENCY" CHAR(255)
TERMINATED BY "^",
"REMIT" CHAR(255)
TERMINATED BY "^",
"IN_AMOUNT" CHAR(255)
TERMINATED BY "^",
"OUT_AMOUNT" CHAR(255)
TERMINATED BY "^",
"BALANCE" CHAR(255)
TERMINATED BY "^",
"ALL_COMMENT" CHAR(255)
TERMINATED BY "^"
)
)
location
(
'icbc.dat'
)
)REJECT LIMIT UNLIMITED;
--------------------------------------二进制进出
$ expdp system/oracle_4Udirectory=dir1 dumpfile=full.dmpfull=y job_name=fulljob
Export: Release 12.1.0.2.0 - Production on Sat Jul 28 11:29:07 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."FULLJOB":system/******** directory=dir1 dumpfile=full.dmp full=y job_name=fulljob
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 318.7 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
这时，按了ctl+c
Export> help
------------------------------------------------------------------------------
The following commands are valid while in interactive mode.
Note: abbreviations are allowed.
ADD_FILE
Add dumpfile to dumpfile set.
CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT
Quit client session and leave job running.
FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.
HELP
Summarize interactive commands.
KILL_JOB
Detach and delete job.
PARALLEL
Change the number of active workers for current job.
REUSE_DUMPFILES
Overwrite destination dump file if it exists .
START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.
STATUS
Frequency (secs) job status is to be monitored where
the default will show new status when available.
STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.
Export> STOP_JOB
Are you sure you wish to stop this job (/no): yes
$ expdp system/oracle_4Uattach=fulljob
Export: Release 12.1.0.2.0 - Production on Sat Jul 28 11:30:38 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Job: FULLJOB
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: 7207D4B3F9CF3D4AE0535A00A8C00270
Start Time: Saturday, 28 July, 2018 11:30:40
Mode: FULL
Instance: orcl
Max Parallelism: 1
Timezone: +00:00
Timezone version: 18
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** directory=dir1 dumpfile=full.dmp full=y job_name=fulljob
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /home/oracle/dir1/full.dmp
bytes written: 4,096
Worker 1 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW00
State: UNDEFINED
Export> help
------------------------------------------------------------------------------
The following commands are valid while in interactive mode.
Note: abbreviations are allowed.
ADD_FILE
Add dumpfile to dumpfile set.
CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT
Quit client session and leave job running.
FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.
HELP
Summarize interactive commands.
KILL_JOB
Detach and delete job.
PARALLEL
Change the number of active workers for current job.
REUSE_DUMPFILES
Overwrite destination dump file if it exists .
START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.
STATUS
Frequency (secs) job status is to be monitored where
the default will show new status when available.
STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.
Export> status
Job: FULLJOB
Operation: EXPORT
Mode: FULL
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /home/oracle/dir1/full.dmp
bytes written: 4,096
Worker 1 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW00
State: UNDEFINED
Export> start_job
Export>
select* from dba_datapump_jobsdj
where dj.job_name='FULLJOB';
select* from system.fulljob;
作业完成主表进了dumpfile，找不到了，这是正常的：
导入的时候，主表先进来：
$ impdp user1/oracle_4U directory=dir1 dumpfile=full.dmptables=hr.employees remap_schema=hr:user1
Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:39:28 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "USER1.SYS_IMPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-01031: insufficient privileges
grant read,write on directory dir1 to user1;
alter user user1 account unlock;
grant resource to user1;
alter user user1 quota unlimited on example;
$ impdp user1/oracle_4U directory=dir1 dumpfile=full.dmptables=hr.employees remap_schema=hr:user1
Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:44:24 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "USER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "USER1"."SYS_IMPORT_TABLE_01":user1/******** directory=dir1 dumpfile=full.dmp tables=hr.employees remap_schema=hr:user1
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "USER1"."EMPLOYEES" 17.07 KB 107 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"USER1"."EMP_DEPT_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "USER1"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "USER1"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT:"USER1"."EMP_JOB_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "USER1"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "USER1"."JOBS" ("JOB_ID") ENABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
ORA-39083: Object type TRIGGER failed to create with error:
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges
Failing sql is:
CREATE NONEDITIONABLE TRIGGER trg05211
after update of salary on hr.employees
referencing new as new old as old
for each row
begin
if :old.salary != :new.salary
then
insert into t05211_valuevalues( to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '||
sys_context('usere
ORA-39082: Object type TRIGGER:"USER1"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"USER1"."UPDATE_JOB_HISTORY" created with compilation warnings
Job "USER1"."SYS_IMPORT_TABLE_01" completed with 5 error(s) at Sat Jul 28 11:44:47 2018 elapsed 0 00:00:22
$ impdp user1/oracle_4U directory=dir1 dumpfile=full.dmptables=hr.departments remap_schema=hr:user1remap_tablespace=example:users
Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:46:39 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "USER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "USER1"."SYS_IMPORT_TABLE_01":user1/******** directory=dir1 dumpfile=full.dmp tables=hr.departments remap_schema=hr:user1 remap_tablespace=example:users
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "USER1"."DEPARTMENTS" 7.125 KB 27 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"USER1"."DEPT_LOC_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "USER1"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "USER1"."LOCATIONS" ("LOCATION_ID") ENABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Job "USER1"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Sat Jul 28 11:47:02 2018 elapsed 0 00:00:21
$
select* from dba_tables t where t.table_namein ('DEPARTMENTS',
'EMPLOYEES')
;
OWNERTABLE_NAMETABLESPACE_NAMECLUSTER_NAMEIOT_NAMESTATUSPCT_FREEPCT_USEDINI_TRANSMAX_TRANSINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASEFREELISTSFREELIST_GROUPSLOGGINGBACKED_UPNUM_ROWSBLOCKSEMPTY_BLOCKSAVG_SPACECHAIN_CNTAVG_ROW_LENAVG_SPACE_FREELIST_BLOCKSNUM_FREELIST_BLOCKSDEGREEINSTANCESCACHETABLE_LOCKSAMPLE_SIZELAST_ANALYZEDPARTITIONEDIOT_TYPETEMPORARYSECONDARYNESTEDBUFFER_POOLFLASH_CACHECELL_FLASH_CACHEROW_MOVEMENTGLOBAL_STATSUSER_STATSDURATIONSKIP_CORRUPTMONITORINGCLUSTER_OWNERDEPENDENCIESCOMPRESSIONCOMPRESS_FORDROPPEDREAD_ONLYSEGMENT_CREATEDRESULT_CACHECLUSTERINGACTIVITY_TRACKINGDML_TIMESTAMPHAS_IDENTITYCONTAINER_DATAINMEMORYINMEMORY_PRIORITYINMEMORY_DISTRIBUTEINMEMORY_COMPRESSIONINMEMORY_DUPLICATE
1HRDEPARTMENTSEXAMPLE VALID10 125565536104857612147483645 NON2750002100 1 1 NENABLED277/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
2HREMPLOYEESEXAMPLE VALID10 125565536104857612147483645 NON10750006900 1 1 NENABLED1077/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
3USER1EMPLOYEESEXAMPLE VALID10 125565536104857612147483645 NON10750006900 1 1 NENABLED1077/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
4USER1DEPARTMENTSUSERS VALID10 125565536104857612147483645 NON2750002100 1 1 NENABLED277/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
$ expdp system/oracle_4U directory=dir1 dumpfile=dir1:'sh_hr_%U.dmp',dir2:'sh_hr_%U.dmp'schemas=sh,hr job_name=sh_hr1jobparallel=4
Export: Release 12.1.0.2.0 - Production on Sat Jul 28 11:52:35 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SH_HR1JOB":system/******** directory=dir1 dumpfile=dir1:sh_hr_%U.dmp,dir2:sh_hr_%U.dmp schemas=sh,hr job_name=sh_hr1job parallel=4
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 287.3 MB
. . exported "HR"."T05207_B" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/USER
. . exported "SH"."COSTS":"COSTS_Q1_1999" 183.7 KB 5884 rows
. . exported "SH"."COSTS":"COSTS_Q1_1998" 139.6 KB 4411 rows
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
. . exported "SH"."CUSTOMERS" 10.27 MB 55500 rows
. . exported "SH"."COSTS":"COSTS_Q1_2001" 228.0 KB 7328 rows
. . exported "SH"."COSTS":"COSTS_Q1_2000" 120.7 KB 3772 rows
Processing object type SCHEMA_EXPORT/ROLE_GRANT
. . exported "SH"."COSTS":"COSTS_Q2_1998" 79.68 KB 2397 rows
. . exported "SH"."COSTS":"COSTS_Q2_1999" 132.7 KB 4179 rows
. . exported "SH"."COSTS":"COSTS_Q2_2000" 119.1 KB 3715 rows
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
. . exported "SH"."COSTS":"COSTS_Q2_2001" 184.7 KB 5882 rows
. . exported "SH"."COSTS":"COSTS_Q3_1998" 131.3 KB 4129 rows
. . exported "SH"."COSTS":"COSTS_Q3_2000" 151.6 KB 4798 rows
. . exported "SH"."COSTS":"COSTS_Q3_1999" 137.5 KB 4336 rows
. . exported "SH"."COSTS":"COSTS_Q4_1999" 159.2 KB 5060 rows
. . exported "SH"."COSTS":"COSTS_Q4_1998" 144.8 KB 4577 rows
. . exported "SH"."COSTS":"COSTS_Q3_2001" 234.6 KB 7545 rows
. . exported "SH"."COSTS":"COSTS_Q4_2000" 160.4 KB 5088 rows
. . exported "SH"."COSTS":"COSTS_Q4_2001" 278.5 KB 9011 rows
. . exported "SH"."SALES":"SALES_Q1_2000" 2.012 MB 62197 rows
. . exported "SH"."SALES":"SALES_Q1_1998" 1.413 MB 43687 rows
. . exported "SH"."SALES":"SALES_Q1_1999" 2.071 MB 64186 rows
. . exported "SH"."SALES":"SALES_Q2_1998" 1.160 MB 35758 rows
. . exported "SH"."SALES":"SALES_Q1_2001" 1.965 MB 60608 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
. . 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_Q3_1998" 1.634 MB 50515 rows
. . exported "SH"."SALES":"SALES_Q2_2001" 2.051 MB 63292 rows
. . exported "SH"."SALES":"SALES_Q3_1999" 2.166 MB 67138 rows
. . exported "SH"."SALES":"SALES_Q3_2000" 1.910 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.6 KB 4500 rows
. . exported "HR"."T04209_UNAME" 1.510 MB100000 rows
. . exported "SH"."FWEEK_PSCAT_SALES_MV" 419.9 KB 11266 rows
. . exported "SH"."PROMOTIONS" 59.17 KB 503 rows
. . exported "SH"."TIMES" 381.7 KB 1826 rows
. . exported "HR"."COUNTRIES" 6.460 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."EMPLOYEES" 17.07 KB 107 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."LOCATIONS" 8.437 KB 23 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
. . exported "SH"."CAL_MONTH_SALES_MV" 6.382 KB 48 rows
. . exported "SH"."CHANNELS" 7.414 KB 5 rows
. . exported "SH"."COUNTRIES" 10.46 KB 23 rows
. . exported "SH"."DIMENSION_EXCEPTIONS" 0 KB 0 rows
. . exported "SH"."PRODUCTS" 26.71 KB 72 rows
. . exported "HR"."SSMA" 0 KB 0 rows
. . exported "HR"."SSMM" 0 KB 0 rows
. . exported "HR"."T05207_A" 0 KB 0 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"."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
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
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/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/FGA_POLICY
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
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/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/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/DIMENSION
Master table "SYSTEM"."SH_HR1JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SH_HR1JOB is:
/home/oracle/dir1/sh_hr_01.dmp
/home/oracle/dir2/sh_hr_01.dmp
/home/oracle/dir1/sh_hr_02.dmp
/home/oracle/dir2/sh_hr_02.dmp
Job "SYSTEM"."SH_HR1JOB" successfully completed at Sat Jul 28 11:54:04 2018 elapsed 0 00:01:27
$
$ impdp system/oracle_4U directory=dir1 dumpfile=dir1:'sh_hr_%U.dmp',dir2:'sh_hr_%U.dmp'tables=sh.salesparallel=4remap_schema=sh:user1
Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:55:36 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":system/******** directory=dir1 dumpfile=dir1:sh_hr_%U.dmp,dir2:sh_hr_%U.dmp tables=sh.sales parallel=4 remap_schema=sh:user1
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39171: Job is experiencing a resumable wait.
Resumable error: ORA-01658: unable to create INITIAL extent for segment in tablespace EXAMPLE
Resumable stmt: CREATE TABLE "USER1"."SALES" ("PROD_ID" NUMBER NOT NULL ENABLE, "CUST_ID" NUMBER NOT NULL ENABLE, "TIME_ID" DATE NOT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE, "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE, "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESSNOLOGGINGSTORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE"PARTITION BY RANGE ("TIME_ID")(PARTITION "SALES_1995"VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGINGSTORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE" , PARTITION "SALES_1996"VALUES LESS THAN (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAX
Resumable stmt status: SUSPENDED
Resumable stmt start: 07/28/18 11:55:40 stmt suspend: 07/28/18 11:55:42
ORA-39171: Job is experiencing a resumable wait.
Resumable error: ORA-01658: unable to create INITIAL extent for segment in tablespace EXAMPLE
Resumable stmt: CREATE TABLE "USER1"."SALES" ("PROD_ID" NUMBER NOT NULL ENABLE, "CUST_ID" NUMBER NOT NULL ENABLE, "TIME_ID" DATE NOT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE, "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE, "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESSNOLOGGINGSTORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE"PARTITION BY RANGE ("TIME_ID")(PARTITION "SALES_1995"VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGINGSTORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE" , PARTITION "SALES_1996"VALUES LESS THAN (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAX
Resumable stmt status: SUSPENDED
Resumable stmt start: 07/28/18 11:55:40 stmt suspend: 07/28/18 12:02:45
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USER1"."SALES":"SALES_Q1_1998" 1.413 MB 43687 rows
. . imported "USER1"."SALES":"SALES_Q1_1999" 2.071 MB 64186 rows
. . imported "USER1"."SALES":"SALES_Q1_2000" 2.012 MB 62197 rows
. . imported "USER1"."SALES":"SALES_Q1_2001" 1.965 MB 60608 rows
. . imported "USER1"."SALES":"SALES_Q2_1998" 1.160 MB 35758 rows
. . imported "USER1"."SALES":"SALES_Q2_1999" 1.754 MB 54233 rows
. . imported "USER1"."SALES":"SALES_Q4_2001" 2.257 MB 69749 rows
. . imported "USER1"."SALES":"SALES_1995" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_1996" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_Q3_2000" 1.910 MB 58950 rows
. . imported "USER1"."SALES":"SALES_H1_1997" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_H2_1997" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_Q1_2002" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_Q1_2003" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_Q2_2002" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_Q2_2003" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_Q3_2002" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_Q3_2003" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_Q4_2002" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_Q4_2003" 0 KB 0 rows
. . imported "USER1"."SALES":"SALES_Q2_2000" 1.802 MB 55515 rows
. . imported "USER1"."SALES":"SALES_Q3_2001" 2.130 MB 65769 rows
. . imported "USER1"."SALES":"SALES_Q2_2001" 2.051 MB 63292 rows
. . imported "USER1"."SALES":"SALES_Q4_1998" 1.581 MB 48874 rows
. . imported "USER1"."SALES":"SALES_Q3_1998" 1.634 MB 50515 rows
. . imported "USER1"."SALES":"SALES_Q4_1999" 2.014 MB 62388 rows
. . imported "USER1"."SALES":"SALES_Q3_1999" 2.166 MB 67138 rows
. . imported "USER1"."SALES":"SALES_Q4_2000" 1.814 MB 55984 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_PROMO_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_PROMO_FK" FOREIGN KEY ("PROMO_ID") REFERENCES "USER1"."PROMOTIONS" ("PROMO_ID") ENABLE NOVALIDATE
ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_CUSTOMER_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_CUSTOMER_FK" FOREIGN KEY ("CUST_ID") REFERENCES "USER1"."CUSTOMERS" ("CUST_ID") ENABLE NOVALIDATE
ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_PRODUCT_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_PRODUCT_FK" FOREIGN KEY ("PROD_ID") REFERENCES "USER1"."PRODUCTS" ("PROD_ID") ENABLE NOVALIDATE
ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_TIME_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_TIME_FK" FOREIGN KEY ("TIME_ID") REFERENCES "USER1"."TIMES" ("TIME_ID") ENABLE NOVALIDATE
ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_CHANNEL_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_CHANNEL_FK" FOREIGN KEY ("CHANNEL_ID") REFERENCES "USER1"."CHANNELS" ("CHANNEL_ID") ENABLE NOVALIDATE
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/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 7 error(s) at Sat Jul 28 12:06:42 2018 elapsed 0 00:11:04
$
如果不用parfile就很难把格式写清楚，
$ expdp user1/oracle_4U directory=dir1dumpfile=user1.dmp schemas=user1 job_name=user1job exclude=table: "in ('sales')", function: "in ('FUNC2')" query='employees:where department_id=10'
LRM-00101: unknown parameter name 'department_id'
$ expdp user1/oracle_4U directory=dir1dumpfile=user1.dmp schemas=user1 job_name=user1job exclude=table: "in ('sales')", function: "in ('FUNC2')" query='employees:where department_id=10'
parfile：
userid=user1/oracle_4U
directory=dir1
dumpfile=user1.dmp
schemas=user1
job_name=user1job
exclude=table:" in ('SALES')",function:" in ('FUNC2')"
query="employees:where department_id=10"
$ expdp parfile=/home/oracle/parfile.ora
Export: Release 12.1.0.2.0 - Production on Sat Jul 28 15:02:19 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "USER1"."USER1JOB":user1/******** parfile=/home/oracle/parfile.ora
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
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/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USER1"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "USER1"."EMPLOYEES" 9.609 KB 1 rows
Master table "USER1"."USER1JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for USER1.USER1JOB is:
/home/oracle/dir1/user1.dmp
Job "USER1"."USER1JOB" successfully completed at Sat Jul 28 15:02:55 2018 elapsed 0 00:00:35
$
$ impdp system/oracle_4U directory=dir1 dumpfile=user1.dmp schemas=user1 remap_schema=user1:user2
select* from dba_objects where owner='USER2';
OWNEROBJECT_NAMESUBOBJECT_NAMEOBJECT_IDDATA_OBJECT_IDOBJECT_TYPECREATEDLAST_DDL_TIMETIMESTAMPSTATUSTEMPORARYGENERATEDSECONDARYNAMESPACEEDITION_NAMESHARINGEDITIONABLEORACLE_MAINTAINED
1USER2UPDATE_JOB_HISTORY 94097 TRIGGER7/28/2018 3:11:43 PM7/28/2018 3:11:57 PM2018-07-28:15:11:57INVALIDNNN3 NONEYN
2USER2SECURE_EMPLOYEES 94096 TRIGGER7/28/2018 3:11:43 PM7/28/2018 3:11:57 PM2018-07-28:15:11:57INVALIDNNN3 NONEYN
3USER2EMP_EMAIL_UK 9409594095INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
4USER2DEPT_LOCATION_IX 9409494094INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
5USER2DEPT_ID_PK 9409394093INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
6USER2EMP_NAME_IX 9409294092INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
7USER2EMP_MANAGER_IX 9409194091INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
8USER2EMP_JOB_IX 9409094090INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
9USER2EMP_DEPARTMENT_IX 9408994089INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
10USER2EMP_EMP_ID_PK 9408894088INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
11USER2FUNC1 94087 FUNCTION7/28/2018 3:11:42 PM7/28/2018 3:11:42 PM2018-07-28:14:53:30VALIDNNN1 NONEYN
12USER2DEPARTMENTS 9408694086TABLE7/28/2018 3:11:42 PM7/28/2018 3:11:43 PM2018-07-28:15:11:42VALIDNNN1 NONE N
13USER2EMPLOYEES 9408594085TABLE7/28/2018 3:11:42 PM7/28/2018 3:11:43 PM2018-07-28:15:11:42VALIDNNN1 NONE N
最后带网络链：
parfile：
userid=user2/oracle_4U
directory=dirwin
network_link=dblink1
version=11.2.0
job_name=user1job
exclude=table:" in ('SALES')",function:" in ('FUNC2')"
query="user1.employees:where department_id=10"
flashback_time='2018-07-28:15:22:29'
remap_schema=user1:user2
在win目的地这边：
impdp parfile=c:\data\parfile.ora
-------------------------------------------------------------------
datapump外部表：
CREATE TABLE ext_emp_query_results
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir1
LOCATION (dir1:'emp1.exp',dir2:'emp2.exp')
)
PARALLEL
AS
SELECT e.first_name,e.last_name,d.department_name
FROM employees e, departments d
WHEREe.department_id = d.department_id AND
d.department_name in
('Marketing', 'Purchasing');
$ ls -l *exp
-rw-r----- 1 oracle oinstall 122887月 28 15:48 emp2.exp
$ ls -l ../dir1/*exp
-rw-r----- 1 oracle oinstall 122887月 28 15:48 ../dir1/emp1.exp
$
$ stringsemp1.exp
"HR"."U"
x86_64/Linux 2.4.xx
AL32UTF8
12.01.00.02.00
001:001:000001:000001
i<?xml version="1.0"?>
<ROWSET>
<ROW>
<STRMTABLE_T>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>0 </VERS_MINOR>
<VERS_DPAPI>3</VERS_DPAPI>
<ENDIANNESS>0</ENDIANNESS>
<CHARSET>AL32UTF8</CHARSET>
<NCHARSET>AL16UTF16</NCHARSET>
<DBTIMEZONE>+00:00</DBTIMEZONE>
<OWNER_NAME>HR</OWNER_NAME>
<NAME>EXT_EMP_QUERY_RESULTS</NAME>
<COL_LIST>
<COL_LIST_ITEM>
<COL_NUM>1</COL_NUM>
<NAME>FIRST_NAME</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>20</LENGTH>
<PRECISION_NUM>0</PRECISION_NUM>
<SCALE>0</SCALE>
<CHARSETID>873</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CHARLENGTH>20</CHARLENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<COL_NUM>2</COL_NUM>
<NAME>LAST_NAME</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>25</LENGTH>
<PRECISION_NUM>0</PRECISION_NUM>
<SCALE>0</SCALE>
<CHARSETID>873</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CHARLENGTH>25</CHARLENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<COL_NUM>3</COL_NUM>
<NAME>DEPARTMENT_NAME</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>30</LENGTH>
<PRECISION_NUM>0</PRECISION_NUM>
<SCALE>0</SCALE>
<CHARSETID>873</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CHARLENGTH>30</CHARLENGTH>
</COL_LIST_ITEM>
</COL_LIST>
</STRMTABLE_T>
</ROW>
</ROWSET>
$
CREATE TABLE ext_emp_query_results
(first_namevarchar2(20), last_namevarchar2(25), department_name varchar2(30))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir1
LOCATION (dir1:'emp1.exp',dir2:'emp2.exp')
)
PARALLEL
;
select* from ext_emp_query_results;
FIRST_NAMELAST_NAMEDEPARTMENT_NAME
1ShelliBaidaPurchasing
2KarenColmenaresPurchasing
3PatFayMarketing
4MichaelHartsteinMarketing
5GuyHimuroPurchasing
6AlexanderKhooPurchasing
7DenRaphaelyPurchasing
8SigalTobiasPurchasing
页:
[1]