|
数据泵大作业中断:
- [oracle@station90 ~]$ expdp system/oracle_4U@pdb2 attach=myfull
- Export: Release 12.1.0.2.0 - Production on Mon Jun 4 19:42:41 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, Real Application Testing
- and Unified Auditing options
- Job: MYFULL
- Owner: SYSTEM
- Operation: EXPORT
- Creator Privs: TRUE
- GUID: 6DD0639E4BFD2D2EE0535A00A8C0B771
- Start Time: Monday, 04 June, 2018 19:42:42
- Mode: FULL
- Instance: cdb2
- Max Parallelism: 1
- Timezone: -07: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/********@pdb2 directory=dir1 dumpfile=full.dmp full=y job_name=myfull
- 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: cdb2
- Host name: station90.example.com
- Process Name: DW00
- State: UNDEFINED
- Export> status_job
- UDE-00053: unrecognized client command 'status_job'
- Export> status
- Job: MYFULL
- 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: cdb2
- Host name: station90.example.com
- Process Name: DW00
- State: UNDEFINED
- Export> start_job
- Export> status
- Job: MYFULL
- Operation: EXPORT
- Mode: FULL
- State: EXECUTING
- Bytes Processed: 0
- Current Parallelism: 1
- Job Error Count: 0
- Dump File: /home/oracle/dir1/full.dmp
- bytes written: 106,496
-
- Worker 1 Status:
- Instance ID: 1
- Instance name: cdb2
- Host name: station90.example.com
- Process Name: DW00
- State: EXECUTING
- Object Schema: SYS
- Object Name: FGA_LOG$FOR_EXPORT
- Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
- Completed Objects: 7
- Worker Parallelism: 1
- Export> exit
复制代码- select * from cdb_directories;
- select * from cdb_datapump_jobs;
- select * from system.myfull;
复制代码
导入:
- [oracle@station90 ~]$ impdp system/oracle_4U directory=dir1 dumpfile=full.dmp tables=hr.t04209_uname
- Import: Release 12.1.0.2.0 - Production on Mon Jun 4 19:56: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
- Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
- Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=dir1 dumpfile=full.dmp tables=hr.t04209_uname
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
- ORA-39083: Object type TABLE:"HR"."T04209_UNAME" failed to create with error:
- ORA-00959: tablespace 'LDATA' does not exist
- Failing sql is:
- CREATE TABLE "HR"."T04209_UNAME" ("UNAME" VARCHAR2(60 BYTE), "UVALUE" NUMBER(9,0)) 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 "LDATA"
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
- ORA-39112: Dependent object type AUDIT_OBJ skipped, base object type TABLE:"HR"."T04209_UNAME" creation failed
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
- Processing object type DATABASE_EXPORT/STATISTICS/MARKER
- Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at Mon Jun 4 19:56:29 2018 elapsed 0 00:00:20
- [oracle@station90 ~]$ impdp system/oracle_4U directory=dir1 dumpfile=full.dmp tables=hr.t04209_uname remap_tablespace=ldata:users
- Import: Release 12.1.0.2.0 - Production on Mon Jun 4 19:57:21 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=full.dmp tables=hr.t04209_uname remap_tablespace=ldata:users
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
- . . imported "HR"."T04209_UNAME" 1.510 MB 100000 rows
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
- Processing object type DATABASE_EXPORT/STATISTICS/MARKER
- Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jun 4 19:57:46 2018 elapsed 0 00:00:21
- [oracle@station90 ~]$
复制代码
第二个例子:
expparfile.txt:
- schemas=hr
- directory=dir1
- dumpfile=dir1:my1%U.dmp,dir2:my2%U.dmp
- version=11.2
- filesize=1M
- parallel=4
- sample=t04209_uname:90
- job_name=myschema
- exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('PROC2')"
- query='employees:where employee_id<>102'
- flashback_time='2018-06-04:20:15:42'
复制代码- [oracle@station90 ~]$ expdp hr/oracle_4U@pdb2 parfile=/home/oracle/expparfile.txt
- Export: Release 12.1.0.2.0 - Production on Mon Jun 4 20:24:27 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, Real Application Testing
- and Unified Auditing options
- Starting "HR"."MYSCHEMA": hr/********@pdb2 parfile=/home/oracle/expparfile.txt
- Estimate in progress using BLOCKS method...
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 2.049 MB
- . . exported "HR"."T05207_A" 5.031 KB 0 rows
- . . exported "HR"."T05207_M" 5.046 KB 1 rows
- . . exported "HR"."T06303_A" 5.046 KB 1 rows
- . . exported "HR"."T05207_B" 0 KB 0 rows
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- . . exported "HR"."T04209_UNAME" 1.359 MB 89989 rows
- . . exported "HR"."EMPLOYEES" 6.875 KB 2 rows
- Processing object type SCHEMA_EXPORT/TABLE/TABLE
- Processing object type SCHEMA_EXPORT/TABLE/COMMENT
- Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
- Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
- Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
- 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
- Master table "HR"."MYSCHEMA" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for HR.MYSCHEMA is:
- /home/oracle/dir1/my101.dmp
- /home/oracle/dir2/my201.dmp
- /home/oracle/dir1/my102.dmp
- /home/oracle/dir2/my202.dmp
- Job "HR"."MYSCHEMA" successfully completed at Mon Jun 4 20:24:50 2018 elapsed 0 00:00:23
- [oracle@station90 ~]$
复制代码
[oracle@station90 dir2]$ ls
extab2.dat my201.dmp my202.dmp
[oracle@station90 dir2]$ mv my201.dmp my202.dmp /home/oracle/dir1/
impparfile.txt:
- schemas=hr
- directory=dir1
- dumpfile=my1%U.dmp,my2%U.dmp
- parallel=4
- remap_table=employees:emp2
- remap_tablespace=ldata:users
复制代码- [oracle@station90 ~]$ impdp hr/oracle_4U parfile=impparfile.txt
- Import: Release 11.2.0.3.0 - Production on Mon Jun 4 20:30:35 2018
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Master table "HR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
- Starting "HR"."SYS_IMPORT_SCHEMA_01": hr/******** parfile=impparfile.txt
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- Processing object type SCHEMA_EXPORT/TABLE/TABLE
- ORA-39083: Object type TABLE:"HR"."T05207_M" failed to create with error:
- ORA-00959: tablespace 'TBS05207_M' does not exist
- Failing sql is:
- CREATE TABLE "HR"."T05207_M" ("A" NUMBER) 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 "TBS05207_M"
- ORA-39083: Object type TABLE:"HR"."EXTAB" failed to create with error:
- ORA-06564: object DIR2 does not exist
- Failing sql is:
- CREATE TABLE "HR"."EXTAB" ("EMPLOYEE_ID" NUMBER(4,0), "FIRST_NAME" VARCHAR2(20 BYTE), "LAST_NAME" VARCHAR2(25 BYTE), "HIRE_DATE" DATE) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "DIR1" ACCESS PARAMETERS ( records delimited by newline
- badfile dir1:'extab%a_%p.bad'
- logfile dir1:'extab%a_%p.log'
- fields terminated by ','
- missing field
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- . . imported "HR"."T04209_UNAME" 1.359 MB 89989 rows
- . . imported "HR"."EMP2" 6.875 KB 2 rows
- . . imported "HR"."T05207_A" 5.031 KB 0 rows
- . . imported "HR"."T06303_A" 5.046 KB 1 rows
- . . imported "HR"."T05207_B" 0 KB 0 rows
- Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
- Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
- Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
- ORA-39112: Dependent object type INDEX:"HR"."I05207_M" skipped, base object type TABLE:"HR"."T05207_M" creation failed
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
- ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"HR"."I05207_M" creation failed
- Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"HR"."EXTAB" creation failed
- ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"HR"."T05207_M" creation failed
- Job "HR"."SYS_IMPORT_SCHEMA_01" completed with 6 error(s) at 20:30:39
- [oracle@station90 ~]$
复制代码 第三个例子:
netparfile:
- schemas=hr
- directory=dir1
- network_link=mylink
- version=11.2
- parallel=4
- job_name=myschema
- exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('PROC2')"
- query='employees:where employee_id<>102'
- flashback_time='2018-06-04:20:15:42'
- remap_tablespace=ldata:users
复制代码
数据库链:
- SQL> conn hr/oracle_4U
- Connected.
- SQL> create database link mylink connect to hr identified by oracle_4U using 'pdb2';
复制代码- [oracle@station90 ~]$ impdp hr/oracle_4U parfile=netparfile.txt
- Import: Release 11.2.0.3.0 - Production on Mon Jun 4 21:04:45 2018
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Starting "HR"."MYSCHEMA": hr/******** parfile=netparfile.txt
- Estimate in progress using BLOCKS method...
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 2.25 MB
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- Processing object type SCHEMA_EXPORT/TABLE/TABLE
- ORA-39083: Object type TABLE:"HR"."T05207_M" failed to create with error:
- ORA-00959: tablespace 'TBS05207_M' does not exist
- Failing sql is:
- CREATE TABLE "HR"."T05207_M" ("A" NUMBER) 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 "TBS05207_M"
- ORA-39083: Object type TABLE:"HR"."EXTAB" failed to create with error:
- ORA-06564: object DIR2 does not exist
- Failing sql is:
- CREATE TABLE "HR"."EXTAB" ("EMPLOYEE_ID" NUMBER(4,0), "FIRST_NAME" VARCHAR2(20 BYTE), "LAST_NAME" VARCHAR2(25 BYTE), "HIRE_DATE" DATE) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "DIR1" ACCESS PARAMETERS ( records delimited by newline
- badfile dir1:'extab%a_%p.bad'
- logfile dir1:'extab%a_%p.log'
- fields terminated by ','
- missing field
- . . imported "HR"."T04209_UNAME" 100000 rows
- . . imported "HR"."T05207_B" 0 rows
- . . imported "HR"."T05207_A" 0 rows
- . . imported "HR"."T06303_A" 1 rows
- . . imported "HR"."EMPLOYEES" 2 rows
- Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
- Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
- Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
- ORA-39112: Dependent object type INDEX:"HR"."I05207_M" skipped, base object type TABLE:"HR"."T05207_M" creation failed
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
- ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"HR"."I05207_M" creation failed
- Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"HR"."T05207_M" creation failed
- ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"HR"."EXTAB" creation failed
- Job "HR"."MYSCHEMA" completed with 6 error(s) at 21:04:52
- [oracle@station90 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 4 21:04:59 2018
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> select * from t06303_a;
- A
- ----------
- 1
- SQL> select * from employees;
- EMPLOYEE_ID EMAIL SALARY COMMISSION_PCT DEPARTMENT_ID
- ----------- -------------------- ---------- -------------- -------------
- 100 XXX 30000 90
- 101 YYY 14000 90
- SQL> select object_name from user_objects where object_type='PROCEDURE';
- OBJECT_NAME
- --------------------------------------------------------------------------------
- PROC1
- SQL>
复制代码 数据泵外部表的“导出”:
- CREATE TABLE ext_emp_query_results
- ORGANIZATION EXTERNAL
- (
- TYPE ORACLE_DATAPUMP
- DEFAULT DIRECTORY dir1
- LOCATION (dir1:'emp1.dmp',dir2:'emp2.dmp')
- )
- PARALLEL
- AS
- SELECT e.first_name,e.last_name,d.department_name
- FROM employees e, departments d
- WHERE e.department_id = d.department_id AND
- d.department_name in
- ('Marketing', 'Purchasing');
- select * from user_external_tables;
- select * from user_external_locations;
- select * from user_tab_columns tc
- where tc.table_name='EXT_EMP_QUERY_RESULTS'
- order by tc.COLUMN_ID;
复制代码 [oracle@station90 dir2]$ ls -l em*
-rw-r----- 1 oracle asmadmin 12288 6月 4 21:20 emp2.dmp
[oracle@station90 dir2]$ ls -l em*
-rw-r----- 1 oracle asmadmin 12288 6月 4 21:20 emp2.dmp
[oracle@station90 dir2]$ cd ..
[oracle@station90 ~]$ cd dir1
[oracle@station90 dir1]$ ls -l em*
-rw-r----- 1 oracle asmadmin 12288 6月 4 21:20 emp1.dmp
[oracle@station90 dir1]$
数据泵外部表导入:
- CREATE TABLE ext_emp_query_results
- ( first_name varchar2(20) , last_name varchar2(25) , department_name varchar2(30) )
- ORGANIZATION EXTERNAL
- (
- TYPE ORACLE_DATAPUMP
- DEFAULT DIRECTORY dir1
- LOCATION (dir1:'emp1.dmp',dir2:'emp2.dmp')
- )
- PARALLEL ;
- select * from ext_emp_query_results;
复制代码
|
|