|
暂停和继续作业:
- [oracle@station26 ~]$ expdp system/oracle_4U parfile=my.txt
- Export: Release 12.1.0.2.0 - Production on Wed Dec 13 19:53:52 2017
- Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
- UDE-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 2 days
- 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"."MY_JOB": system/******** parfile=my.txt
- Estimate in progress using BLOCKS method...
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 4.172 GB
- 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/PASSWORD_HISTORY
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- ^C
- Export> stop_job
- Are you sure you wish to stop this job ([yes]/no): yes
- [oracle@station26 ~]$ expdp system/oracle_4U attach=my_job
- Export: Release 12.1.0.2.0 - Production on Wed Dec 13 19:54:28 2017
- Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
- UDE-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 2 days
- 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: MY_JOB
- Owner: SYSTEM
- Operation: EXPORT
- Creator Privs: TRUE
- GUID: 60386BD9F3C2356FE0531A00A8C0EF12
- Start Time: Wednesday, 13 December, 2017 19:53:53
- Mode: SCHEMA
- 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/******** parfile=my.txt
- FLASHBACK_TIME 17-DEC-13 07:39:58 PM
- State: STOP PENDING
- Bytes Processed: 0
- Current Parallelism: 1
- Job Error Count: 0
- Dump File: /home/oracle/mydir/my.dmp
- bytes written: 4,096
-
- Worker 1 Status:
- Instance ID: 1
- Instance name: orcl
- Host name: station26.example.com
- Process Name: DW00
- State: EXECUTING
- Object Schema: HR
- Object Name: SCHEDULER_TEST_SEQ
- Object Type: SCHEMA_EXPORT/SEQUENCE/SEQUENCE
- Completed Objects: 4
- Worker Parallelism: 1
- Export>
复制代码
带并行度地(多个目录对象,限制导出文件大小)导出:
- expdp system/oracle_4U directory=mydir dumpfile=mydir:mydir_%U.dmp,mydir2:mydir2_%U.dmp filesize=100M full=y parallel=20
复制代码 impdp可以局部调dumpfile中的内容,有必要时要remap_schema,remap_tablespace, remap_table:
- [oracle@station37 mydir]$ impdp system/oracle_4U directory=mydir dumpfile=my.dmp tables=user4.t_bank_invoice remap_schema=user4:hr
- Import: Release 11.2.0.4.0 - Production on Thu Nov 23 20:34:23 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDI-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
- Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=mydir dumpfile=my.dmp tables=user4.t_bank_invoice remap_schema=user4:hr
- Processing object type SCHEMA_EXPORT/TABLE/TABLE
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- . . imported "HR"."T_BANK_INVOICE" 10.96 KB 10 rows
- Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Thu Nov 23 20:34:26 2017 elapsed 0 00:00:02
复制代码 采样扫描:
- expdp hr/oracle_4U directory=mydir dumpfile=mydir:a.dmp tables=countries,jobs sample=10
复制代码- [oracle@station26 mydir]$ expdp hr/oracle_4U directory=mydir dumpfile=mydir:a.dmp tables=t_big,t_bank_invoice sample=10
- Export: Release 12.1.0.2.0 - Production on Wed Dec 13 20:57:43 2017
- 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 "HR"."SYS_EXPORT_TABLE_04": hr/******** directory=mydir dumpfile=mydir:a.dmp tables=t_big,t_bank_invoice sample=10
- Estimate in progress using BLOCKS method...
- Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 211.8 MB
- Processing object type TABLE_EXPORT/TABLE/TABLE
- Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
- Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
- Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
- . . exported "HR"."T_BIG" 16.82 MB 130305 rows
- . . exported "HR"."T_BANK_INVOICE" 10.34 KB 0 rows
- Master table "HR"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for HR.SYS_EXPORT_TABLE_04 is:
- /home/oracle/mydir/a.dmp
- Job "HR"."SYS_EXPORT_TABLE_04" successfully completed at Wed Dec 13 20:58:11 2017 elapsed 0 00:00:27
- [oracle@station26 mydir]$ expdp hr/oracle_4U directory=mydir dumpfile=mydir:b.dmp tables=t_big,t_bank_invoice sample=hr.t_big:10
- Export: Release 12.1.0.2.0 - Production on Wed Dec 13 20:58:50 2017
- 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 "HR"."SYS_EXPORT_TABLE_04": hr/******** directory=mydir dumpfile=mydir:b.dmp tables=t_big,t_bank_invoice sample=hr.t_big:10
- Estimate in progress using BLOCKS method...
- Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 211.8 MB
- Processing object type TABLE_EXPORT/TABLE/TABLE
- Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
- Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
- Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
- . . exported "HR"."T_BIG" 16.83 MB 130982 rows
- . . exported "HR"."T_BANK_INVOICE" 10.96 KB 10 rows
- Master table "HR"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for HR.SYS_EXPORT_TABLE_04 is:
- /home/oracle/mydir/b.dmp
- Job "HR"."SYS_EXPORT_TABLE_04" successfully completed at Wed Dec 13 20:58:59 2017 elapsed 0 00:00:08
- [oracle@station26 mydir]$ sqlplus /nolog
- SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 13 20:59:07 2017
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> select count(*) from t_big;
- COUNT(*)
- ----------
- 1305388
- SQL>
复制代码 非传送表空间,不会报自包含集错误:
- [oracle@station26 mydir]$ expdp system/oracle_4U directory=mydir dumpfile=mydir:c.dmp tablespaces=tbsutf version=11.2.0.4
- Export: Release 12.1.0.2.0 - Production on Wed Dec 13 21:10:44 2017
- Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
- UDE-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 2 days
- 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"."SYS_EXPORT_TABLESPACE_01": system/******** directory=mydir dumpfile=mydir:c.dmp tablespaces=tbsutf version=11.2.0.4
- Estimate in progress using BLOCKS method...
- Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 64 KB
- Processing object type TABLE_EXPORT/TABLE/TABLE
- . . exported "HR"."TUTF" 5.046 KB 1 rows
- Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
- /home/oracle/mydir/c.dmp
- Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed Dec 13 21:10:56 2017 elapsed 0 00:00:10
- [oracle@station26 mydir]$
复制代码 非传送表空间在目的地是不会建这个表空间的,它是表定义导出了而已:
- [oracle@station37 ~]$ impdp system/oracle_4U directory=mydir dumpfile=c.dmp
- Import: Release 11.2.0.4.0 - Production on Thu Nov 23 20:57:02 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDI-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
- Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=mydir dumpfile=c.dmp
- Processing object type TABLE_EXPORT/TABLE/TABLE
- ORA-39083: Object type TABLE:"HR"."TUTF" failed to create with error:
- ORA-00959: tablespace 'TBSUTF' does not exist
- Failing sql is:
- CREATE TABLE "HR"."TUTF" ("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 "TBSUTF"
- Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
- Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Thu Nov 23 20:57:04 2017 elapsed 0 00:00:01
复制代码- [oracle@station37 ~]$ impdp system/oracle_4U directory=mydir dumpfile=c.dmp remap_tablespace=tbsutf:users
- Import: Release 11.2.0.4.0 - Production on Thu Nov 23 20:59:42 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDI-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
- Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=mydir dumpfile=c.dmp remap_tablespace=tbsutf:users
- Processing object type TABLE_EXPORT/TABLE/TABLE
- Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
- . . imported "HR"."TUTF" 5.046 KB 1 rows
- Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 23 20:59:44 2017 elapsed 0 00:00:01
- [oracle@station37 ~]$
复制代码 关于content=data_only:
- expdp hr/oracle_4U directory=mydir dumpfile=mydir:d.dmp tables=t_big version=11.2.0.4 content=data_only
复制代码 导入:
- SQL> conn user4/oracle_4U
- Connected.
- SQL> select count(*) from t_big;
- COUNT(*)
- ----------
- 1305388
- SQL> exit
- Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- [oracle@station37 ~]$ impdp system/oracle_4U directory=mydir dumpfile=d.dmp remap_schema=hr:user4
- Import: Release 11.2.0.4.0 - Production on Thu Nov 23 21:08:24 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDI-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
- Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=mydir dumpfile=d.dmp remap_schema=hr:user4
- Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
- . . imported "USER4"."T_BIG" 167.9 MB 1305388 rows
- Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 23 21:08:33 2017 elapsed 0 00:00:08
- [oracle@station37 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 23 21:08:40 2017
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
- SQL> conn user4/oracle_4U
- Connected.
- SQL> select count(*) from t_big;
- COUNT(*)
- ----------
- 2610776
- SQL>
复制代码 关于legacy模式:
- [oracle@station37 ~]$ impdp system/oracle_4U directory=mydir dumpfile=c.dmp fromuser=hr touser=user5 remap_tablespace=tbsutf:users
- Import: Release 11.2.0.4.0 - Production on Thu Nov 23 21:30:53 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDI-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Legacy Mode Active due to the following parameters:
- Legacy Mode Parameter: "fromuser=hr" Location: Command Line, Replaced with: "remap_schema"
- Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
- Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=mydir dumpfile=c.dmp remap_schema=hr:user5 remap_tablespace=tbsutf:users
- Processing object type TABLE_EXPORT/TABLE/TABLE
- Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
- . . imported "USER5"."TUTF" 5.046 KB 1 rows
- Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 23 21:30:55 2017 elapsed 0 00:00:01
复制代码
|
|