OCP培训笔记精华_datapump.docx

Mar18: DataPumpKEY WORDS:DATA PUMP ; EXPORT expdp ,4 modules by user/tablespace/tables/full;IMPORT impdp , 4 modules either;DIRECTORY create direacory ,grant * on directorySQL show userUSER is SYSSQL create directory dir_18 as /ocm/logicalbak/;dump1, create a directoryDirectory created.SQL grant read,write on directory dir_18 to public;dump2,grant the right to this directoryGrant succeeded.SQL exitDisconnected from Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsoracledb $ expdp help=yexpdp help from system,exit the SQLsExport: Release .0 - Production on Sun Mar 18 10:00:05 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.The Data Pump export utility provides a mechanism for transferring data objectsbetween Oracle databases. The utility is invoked with the following command: Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmpYou can control how Export runs by entering the expdp command followedby various parameters. To specify parameters, you use keywords: Format: expdp KEYWORD=value or KEYWORD=(value1,value2,.,valueN) Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott or TABLES=(T1:P1,T1:P2), if T1 is partitioned tableUSERID must be the first parameter on the command line.-The available keywords and their descriptions follow. Default values are listed within square brackets.ATTACHAttach to an existing job.For example, ATTACH=job_name.COMPRESSIONReduce the size of a dump file.Valid keyword values are: ALL, DATA_ONLY, METADATA_ONLY and NONE.CONTENTSpecifies data to unload.Valid keyword values are: ALL, DATA_ONLY and METADATA_ONLY.DATA_OPTIONSData layer option flags.Valid keyword values are: XML_CLOBS.DIRECTORYDirectory object to be used for dump and log files.DUMPFILESpecify list of destination dump file names expdat.dmp.For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.ENCRYPTIONEncrypt part or all of a dump file.Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.ENCRYPTION_ALGORITHMSpecify how encryption should be done.Valid keyword values are: AES128, AES192 and AES256.ENCRYPTION_MODEMethod of generating encryption key.Valid keyword values are: DUAL, PASSWORD and TRANSPARENT.ENCRYPTION_PASSWORDPassword key for creating encrypted data within a dump file.ESTIMATECalculate job estimates.Valid keyword values are: BLOCKS and STATISTICS.ESTIMATE_ONLYCalculate job estimates without performing the export.EXCLUDEExclude specific object types.For example, EXCLUDE=SCHEMA:=HR.FILESIZESpecify the size of each dump file in units of bytes.FLASHBACK_SCNSCN used to reset session snapshot.FLASHBACK_TIMETime used to find the closest corresponding SCN value.FULLExport entire database N.HELPDisplay Help messages N.INCLUDEInclude specific object types.For example, INCLUDE=TABLE_DATA.JOB_NAMEName of export job to create.LOGFILESpecify log file name export.log.NETWORK_LINKName of remote database link to the source system.NOLOGFILEDo not write log file N.PARALLELChange the number of active workers for current job.PARFILESpecify parameter file name.QUERYPredicate clause used to export a subset of a table.For example, QUERY=employees:WHERE department_id 10.REMAP_DATASpecify a data conversion function.For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.REUSE_DUMPFILESOverwrite destination dump file if it exists N.SAMPLEPercentage of data to be exported. SCHEMASList of schemas to export login schema.SOURCE_EDITIONEdition to be used for extracting metadata.STATUSFrequency (secs) job status is to be monitored wherethe default 0 will show new status when available.TABLESIdentifies a list of tables to export.For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.TABLESPACESIdentifies a list of tablespaces to export.TRANSPORTABLESpecify whether transportable method can be used.Valid keyword values are: ALWAYS and NEVER.TRANSPORT_FULL_CHECKVerify storage segments of all tables N.TRANSPORT_TABLESPACESList of tablespaces from which metadata will be unloaded.VERSIONVersion of objects to export.Valid keyword values are: COMPATIBLE, LATEST or any valid database version.-The following commands are valid while in interactive mode.Note: abbreviations are allowed.ADD_FILEAdd dumpfile to dumpfile set.CONTINUE_CLIENTReturn to logging mode. Job will be restarted if idle.EXIT_CLIENTQuit client session and leave job running.FILESIZEDefault filesize (bytes) for subsequent ADD_FILE commands.HELPSummarize interactive commands.KILL_JOBDetach and delete job.PARALLELChange the number of active workers for current job.REUSE_DUMPFILESOverwrite destination dump file if it exists N. START_JOBStart or resume current job.Valid keyword values are: SKIP_CURRENT.STATUSFrequency (secs) job status is to be monitored wherethe default 0 will show new status when available.STOP_JOBOrderly shutdown of job execution and exits the client.Valid keyword values are: IMMEDIATE.oracledb $ expdp mode :tables=table_name there are 4 modules to expdp:by full,by schema,by tablespace,by tables,SQL show parameter db_nameNAME TYPE VALUE- - -db_name string etcSQL /oracle/product/db_1/rdbms/admin/utlsampl.sqlthe scripts to create the USER SCOTT,(be sure the path is correct)Disconnected from Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsoracledb $ rlwrap sqlplus / as sysdbaSQL*Plus: Release .0 Production on Sun Mar 18 10:03:52 2012Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL conn scott/tigerConnected.SQL select * from tab;TNAME TABTYPE CLUSTERID- - -BONUS TABLEDEPT TABLEEMP TABLESALGRADE TABLESQL select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM- - - - - - - DEPTNO- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM- - - - - - - DEPTNO- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM- - - - - - - DEPTNO- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM- - - - - - - DEPTNO- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM- - - - - - - DEPTNO- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL exitDisconnected from Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsoracledb $ expdp scott/tiger tables=emp directory=dir_18 dumpfile=emp.dmp logfile=emp.logdump3, export by table,expdp user mode directory dumpfile logfile;1,2,3,4,5 Export: Release .0 - Production on Sun Mar 18 10:05:38 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting SCOTT.SYS_EXPORT_TABLE_01: scott/* tables=emp directory=dir_18 dumpfile=emp.dmp logfile=emp.log Estimate in progress using BLOCKS method.Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported SCOTT.EMP 8.570 KB 14 rowsMaster table SCOTT.SYS_EXPORT_TABLE_01 successfully loaded/unloaded*Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /ocm/logicalbak/emp.dmpJob SCOTT.SYS_EXPORT_TABLE_01 successfully completed at 10:05:58oracledb $ cd /ocm/logicalbak/oracledb logicalbak$ llchange the directory ,find the dump file was createdtotal 132-rw-r- 1 oracle oinstall 118784 Mar 18 10:05 emp.dmp-rw-r-r- 1 oracle oinstall 1332 Mar 18 10:05 emp.logoracledb logicalbak$ more emp.emp.: No such file or directoryoracledb logicalbak$ more emp.dmpemp.dmp is a binary files,the uses cant view it by more command.G讪U:層b眣 刕L蓤4VAoracledb logicalbak$ more emp.log ; Export: Release .0 - Production on Sun Mar 18 10:05:38 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.; Connected to: Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting SCOTT.SYS_EXPORT_TABLE_01: scott/* tables=emp directory=dir_18 dumpfile=emp.dmp logfile=emp.log Estimate in progress using BLOCKS method.Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported SCOTT.EMP 8.570 KB 14 rowsMaster table SCOTT.SYS_EXPORT_TABLE_01 successfully loaded/unloaded*Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /ocm/logicalbak/emp.dmpJob SCOTT.SYS_EXPORT_TABLE_01 successfully completed at 10:05:58 oracledb logicalbak$ expdp tables=emp directory=dir_18 dumpfile=emp_sys.dmp logfile=emp.logdump4,export by table,stillExport: Release .0 - Production on Sun Mar 18 10:07:16 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Username: sys as sysdbaPassword: Connected to: Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting SYS.SYS_EXPORT_TABLE_01: sys/* AS SYSDBA tables=emp directory=dir_18 dumpfile=emp_sys.dmp logfile=emp.log Estimate in progress using BLOCKS method.Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 0 KBORA-39166: Object SYS.EMP was not found.emp belongs to SCOTT,the current user is SYS,log on as SCOTT or type scott.emp,as the default user is SYSORA-31655: no data or metadata objects selected for jobJob SYS.SYS_EXPORT_TABLE_01 completed with 2 error(s) at 10:07:24oracledb logicalbak$ expdp tables=scott.emp directory=dir_18 dumpfile=emp_sys.dmp logfile=emp.logExport: Release .0 - Production on Sun Mar 18 10:07:39 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Username: sys as sysdbaPassword: Connected to: Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting SYS.SYS_EXPORT_TABLE_01: sys/* AS SYSDBA tables=scott.emp directory=dir_18 dumpfile=emp_sys.dmp logfile=emp.log Estimate in progress using BLOCKS method.Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported SCOTT.EMP 8.570 KB 14 rowsMaster table SYS.SYS_EXPORT_TABLE_01 successfully loaded/unloaded*Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /ocm/logicalbak/emp_sys.dmpJob SYS.SYS_EXPORT_TABLE_01 successfully completed at 10:07:51oracledb logicalbak$ llview the files ,new dmp was been createdtotal 256-rw-r- 1 oracle oinstall 118784 Mar 18 10:05 emp.dmp-rw-r-r- 1 oracle oinstall 1346 Mar 18 10:07 emp.log-rw-r- 1 oracle oinstall 118784 Mar 18 10:07 emp_sys.dmporacledb logicalbak$ expdp schemas=scott directory=dir_18 dumpfile=scott.dmp dump5.export by schemasexpdp scott/tiger schemas=scott directory=dir_18 dumpfile=scott.dmp logfile= scott.logExport: Release .0 - Production on Sun Mar 18 10:09:05 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Username: scottPassword: Connected to: Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting SCOTT.SYS_EXPORT_SCHEMA_01: scott/* schemas=scott directory=dir_18 dumpfile=scott.dmp Estimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 192 KBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported SCOTT.DEPT 5.937 KB 4 rows. . exported SCOTT.EMP 8.570 KB 14 rows. . exported SCOTT.SALGRADE 5.867 KB 5 rows. . exported SCOTT.BONUS 0 KB 0 rowsMaster table SCOTT.SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded*Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: /ocm/logicalbak/scott.dmpJob SCOTT.SYS_EXPORT_SCHEMA_01 successfully completed at 10:09:51oracledb logicalbak$ expdp tablespaces=users directory=dir_18 dumpfile=users.dmp dump6,export by tablespaceexpdp tablespace=users directory =dir_18 dumpfile=use.dmp logfile=use.log(or nolog)Export: Release .0 - Production on Sun Mar 18 10:10:22 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Username: sys as sysdbaPassword: Connected to: Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting SYS.SYS_EXPORT_TABL