菜单

数据库的备份和恢复,导出和导入数据

2020年2月10日 - 数据网络

  Data pump export/import(hereinafter referred to as Export/Import for
ease of
readingState of Qatar是意气风发种将元数据和多少导出到系统文件集/从系统文件集导入数据的服务端工具;导出的公文能够活动到其余服务器上,但只好使用impdp导入;使用前要先创建目录(directory卡塔尔(قطر‎和授权:

–**********************数据泵本事——oracle
10G之后现身

conn / as sysdbaCREATE DIRECTORY PUMP_DIR AS '/u01/backup';

–创立目录

创设后就可以查询出目录消息,富含目录名称、全数者、目录地点:

create directory dump_dir as ‘e:/backup’;

SQL> column directory_path format a50SQL>select * from dba_directories where directory_name='PUMP_DIR';OWNER             DIRECTORY_NAME         DIRECTORY_PATH------------------------------ ------------------------------ --------------------------------------------------SYS              PUMP_DIR            /u01/backup

–查询数据库全数有目录

删除本来就有的目录

select * from dba_directories;

drop directory pump_dir;

–删除数据库目录

向一些客户授权使用目录,能够授权read或write权限,或然五个权力同期赋予:

drop directory dump_dir;

grant read,write on directory pump_dir to frdc;

drop directory dump_file;

收回权限

–成立表空间

revoke read,write on directory pump_dir from frdc;

create tablespace tbs_test

1导出多少

datafile ‘e:/t.dbf’

1.1大旨注解

EXPDP USERID='sywu/sywu' job_name=export_tb parallel=3 tables=(tb01,tb02,tb03) dumpfile=pump_dir:dw_20150602.dmp logfile=pump_dir:exptb.log version='11.2.0.1.0' exclude=''

userid 代表数据库连接音信,能够是as
sysdba权限,非sysdba权限顾客能够回顾;
job_name 表示导出职分名,未钦赐暗中同意格式为: SYS_EXPORT_TABLE _*;
parallel 代表并行数,默以为1;
tables
表示导出的表名,能够内定全部者owner.tab,导出分区表的某部分区owner.tab:part01;
dumpfile
代表导出的dump文件名,格式目录名:dump文件名,注:某个版本也许不宽容报错(ORA-39145卡塔尔,能够把目录独立出来用directory参数钦定(directory=pump_dir);
version
表示导入目标地数据库版本,在非同生龙活虎版本数据库之间导出导入数据那些相比关键;
exclude
表示导入解除的靶子,DATABASE_EXPORT_OBJECTS表中著录全数的(数据库等级卡塔尔国消释形式,SCHEMA_EXPORT_OBJECTS表中记录schema等级消释对象情势,TABLE_EXPORT_OBJECTS表中记录table品级解除对象情势;

size 10M

1.2带条件导出数据

有的时候只想导出特定的数额,比方id=10或id
in(10,20,30卡塔尔,这种情况下得以用标准限定导出数据(注意字符转义卡塔尔国

expdp userid='ops$sywu/sywu' tables=tb01 query=tb01:\"where object_id in\(10,20,30\)\" dumpfile=query_tab.dump directory=pump_dir Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:09:24 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "OPS$SYWU"."SYS_EXPORT_TABLE_01": userid=ops$sywu/******** tables=tb01 query=tb01:"where object_id in(10,20,30)" dumpfile=query_tab.dump directory=pump_dir Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 88 MBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "OPS$SYWU"."TB01"              25.70 KB   192 rowsMaster table "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for OPS$SYWU.SYS_EXPORT_TABLE_01 is: /u01/backup/query_tab.dumpJob "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully completed at 17:09:32

抑或经过rownum约束行的方法导出数据(注意字符转义State of Qatar

 expdp userid='ops$sywu/sywu' tables=tb01 query=tb01:\"where rownum\<10\" dumpfile=query_tab.dump directory=pump_dir Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:14:28 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "OPS$SYWU"."SYS_EXPORT_TABLE_01": userid=ops$sywu/******** tables=tb01 query=tb01:"where rownum<10" dumpfile=query_tab.dump directory=pump_dir Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 88 MBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "OPS$SYWU"."TB01"              11.41 KB    9 rowsMaster table "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for OPS$SYWU.SYS_EXPORT_TABLE_01 is: /u01/backup/query_tab.dumpJob "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully completed at 17:14:36

稍许意况下,为了满意测量试验要求,我们可能必要整库导出或任何schema下的数码导出,但又无需具备数据,so
雷同能够行使范围行数的艺术界定全部表数据行导出多少

expdp userid='ops$sywu/sywu' SCHEMAS='ops$sywu' query=\"where rownum\<10\" dumpfile=schema_sywu.dump directory=pump_dirExport: Release 11.2.0.3.0 - Production on Tue Jun 2 17:18:34 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "OPS$SYWU"."SYS_EXPORT_SCHEMA_01": userid=ops$sywu/******** SCHEMAS=ops$sywu query="where rownum<10" dumpfile=schema_sywu.dump directory=pump_dir Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 104 MBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing 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/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "OPS$SYWU"."TB01"              11.41 KB    9 rows. . exported "OPS$SYWU"."TB02"              6.015 KB    9 rows. . exported "OPS$SYWU"."T_EXCE"             5.820 KB    2 rowsMaster table "OPS$SYWU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for OPS$SYWU.SYS_EXPORT_SCHEMA_01 is: /u01/backup/schema_sywu.dumpJob "OPS$SYWU"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:18:55

有一点意况下,导出的多少须求经过互联网或透过介绍人传输到其余目标地,思谋媒介的朗朗上口和互联网状态,每回只好限量传输,为了完成这些目标能够将数据导出到三个文件,然后再分批或独自发送;比方评估的数码大小是80M,小编想把公文导出为4个公文,各样文件的高低为20M,so
作者像那样导出数据:

expdp userid='ops$sywu/sywu' job_name=exporttb_bysize SCHEMAS='ops$sywu' filesize=20971520 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.logExport: Release 11.2.0.3.0 - Production on Tue Jun 2 18:03:03 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "OPS$SYWU"."EXPORTTB_BYSIZE": userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=20971520 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 104 MBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing 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/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "OPS$SYWU"."TB01"              74.64 MB 844416 rows. . exported "OPS$SYWU"."TB02"              452.3 KB  13448 rows. . exported "OPS$SYWU"."T_EXCE"             5.820 KB    2 rowsMaster table "OPS$SYWU"."EXPORTTB_BYSIZE" successfully loaded/unloaded******************************************************************************Dump file set for OPS$SYWU.EXPORTTB_BYSIZE is: /u01/backup/size_each_sywu.dump /u01/backup/file02.dmp /u01/backup/file03.dmp /u01/backup/file04.dmpJob "OPS$SYWU"."EXPORTTB_BYSIZE" successfully completed at 18:03:24

filesize 表示各样文件的尺寸,单位为:bytes
dumpfile
代表导出的4个dump文件名,若是实在导出数据大小大于钦赐的文本数量(dumpfile卡塔尔(قطر‎乘以各类文件大小(filesizeState of Qatar之合,则导出甘休并报错
ORA-39095: Dump file space has been exhausted: Unable to allocate 4096
bytesJob “OPS$SYWU”.”SYS_EXPORT_SCHEMA_07″ stopped due to fatal error
at 17:57:31
有个地点要在意:在导出数据时,数据库会在导数客户下基于job_name名称成立一张表,譬如job_name=exporttb_bysize,在导数进度中数据库创立一张名EXPORTTB_BYSIZE的表,该表记录了导数的新闻,导数成功做到后活动删除,如果导数未得逞,比方空间欠缺或实际尺寸大于内定大小,则job甘休,该表不会自动删除。
为了表达难点,作者将filesize改为每一种文件10m仿照错误产生,最终观察情况,然后化解错误;

 expdp userid='ops$sywu/sywu' job_name=exporttb_bysize SCHEMAS='ops$sywu' filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.logExport: Release 11.2.0.3.0 - Production on Tue Jun 2 18:21:07 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "OPS$SYWU"."EXPORTTB_BYSIZE": userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 104 MBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing 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/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytesJob "OPS$SYWU"."EXPORTTB_BYSIZE" stopped due to fatal error at 18:21:26

钦命文件大小乘以文件个数小于实际数据量大小,导出报错停止,仅仅是终止;

ls -lh /u01/backup-rw-r--r--. 1 oracle asmadmin 1.8K Jun 2 17:18 export.log-rw-r--r--. 1 oracle asmadmin 1.5K Jun 2 18:21 exportt01.log-rw-r-----. 1 oracle asmadmin 10M Jun 2 18:21 file02.dmp-rw-r-----. 1 oracle asmadmin 10M Jun 2 18:21 file03.dmp-rw-r-----. 1 oracle asmadmin 10M Jun 2 18:21 file04.dmp-rw-r-----. 1 oracle asmadmin 10M Jun 2 18:21 size_each_sywu.dump

那个时候翻开数据库,发掘一张于job_name名称相似的表EXPORTTB_BYSIZE,表中著录了导数时的音信;查询job的景观为:NOT
RUNING;

select * from tab;TNAME             TABTYPE CLUSTERID------------------------------ ------- ----------EXPORTTB_BYSIZE        TABLETB01              TABLETB02              TABLET_EXCE             TABLEselect job_name,state from dba_datapump_jobs;JOB_NAME            STATE------------------------------ ------------------------------EXPORTTB_BYSIZE        NOT RUNNING

一而再再而三产生导数能够经过增多dump文件或使用系统暗中认可的文本

expdp userid='ops$sywu/sywu' attach=EXPORTTB_BYSIZEExport: Release 11.2.0.3.0 - Production on Tue Jun 2 22:28:37 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsJob: EXPORTTB_BYSIZE Owner: OPS$SYWU             Operation: EXPORT              Creator Privs: FALSE              GUID: 178798A215814641E053FE1813ACD41C Start Time: Tuesday, 02 June, 2015 22:28:38 Mode: SCHEMA              Instance: sydb Max Parallelism: 1 EXPORT Job Parameters: Parameter Name   Parameter Value:   CLIENT_COMMAND    userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log  State: IDLING              Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /u01/backup/size_each_sywu.dump  size: 10,485,760  bytes written: 10,485,760 Dump File: /u01/backup/file02.dmp  size: 10,485,760  bytes written: 10,485,760 Dump File: /u01/backup/file03.dmp  size: 10,485,760  bytes written: 10,485,760 Dump File: /u01/backup/file04.dmp  size: 10,485,760  bytes written: 10,485,760Worker 1 Status: Process Name: DW00 State: UNDEFINED            Object Schema: OPS$SYWU Object Name: TB01 Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA Completed Objects: 1 Total Objects: 3 Completed Bytes: 41,844,736 Percent Done: 45 Worker Parallelism: 1Export> add_file=file05.dmpExport> start_job

开始职务后,导数继续实行

select job_name,state from dba_datapump_jobs;JOB_NAME            STATE------------------------------ ------------------------------EXPORTTB_BYSIZE        EXECUTINGselect job_name,state from dba_datapump_jobs;JOB_NAME            STATE------------------------------ ------------------------------EXPORTTB_BYSIZE        COMPLETING

在添加dump file(add_fileState of Qatar时,增添的文书最后大小决定于增加了略略dump
file,在上例中仅加多了叁个file05.dmp,so
剩余的数据量将整个归结到该公文中;如若未添Gavin件oracle会自个儿分配和开创七个dump文件;

[oracle@sywu backup]$ ls -ltrh-rw-r-----. 1 oracle asmadmin 10M Jun 2 22:30 file02.dmp-rw-r-----. 1 oracle asmadmin 10M Jun 2 22:30 file03.dmp-rw-r-----. 1 oracle asmadmin 10M Jun 2 22:30 file04.dmp-rw-r-----. 1 oracle asmadmin 10M Jun 2 22:30 size_each_sywu.dump-rw-r-----. 1 oracle asmadmin 36M Jun 2 22:30 file05.dmp-rw-r--r--. 1 oracle asmadmin 2.4K Jun 2 22:30 exportt01.log[oracle@sywu backup]$ cat exportt01.log Export: Release 11.2.0.3.0 - Production on Tue Jun 2 18:42:17 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "OPS$SYWU"."EXPORTTB_BYSIZE": userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 104 MBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing 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/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytesJob "OPS$SYWU"."EXPORTTB_BYSIZE" stopped due to fatal error at 18:42:36Job EXPORTTB_BYSIZE has been reopened at Tuesday, 02 June, 2015 22:28 Restarting "OPS$SYWU"."EXPORTTB_BYSIZE": userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log . . exported "OPS$SYWU"."TB01"              74.64 MB 844416 rows. . exported "OPS$SYWU"."TB02"              452.3 KB  13448 rows. . exported "OPS$SYWU"."T_EXCE"             5.820 KB    2 rowsMaster table "OPS$SYWU"."EXPORTTB_BYSIZE" successfully loaded/unloaded******************************************************************************Dump file set for OPS$SYWU.EXPORTTB_BYSIZE is: /u01/backup/size_each_sywu.dump /u01/backup/file02.dmp /u01/backup/file03.dmp /u01/backup/file04.dmp /u01/backup/file05.dmpJob "OPS$SYWU"."EXPORTTB_BYSIZE" completed with 1 error(s) at 22:30:38

autoextend on;

1.3导出某些schema下的数码

  对于导出schema下的数量,提出利用sysdba顾客导出,因为随着系统的纷纭和意况不一致,有些时候信任的包、对象、存款和储蓄进度或然是归于另一个客商的,导出时被导出schema未必全部完全的权杖,同期还要思虑导入的指标地境遇;当然整个没有相对,完全决计于应用;

expdp userid='sys/oracle as sysdba' job_name=export_schema SCHEMAS='ops$sywu' directory=pump_dir dumpfile=schema_sywu.dmp logfile=schema_sywu.log

–查询表空间

1.4整库导出

  整库导出必需利用sysdba权限;

expdp userid='sys/oracle as sysdba' job_name=export_schema FULL=Y directory=pump_dir dumpfile=full_dbexport.dmp logfile=full_dbexport.log

select * from dba_tablespaces;

1.5查询导数job状态

column username format a10 column opname format a20 column units format a10 select  round(sofar/totalwork*100,2) percent_completed,  t.sid,t.SERIAL#,t.USERNAME,t.OPNAME,t.TOTALWORK,t.UNITS,t.START_TIME,t.MESSAGE from  v$session_longops t where sofar <> totalwork order by target,sid;PERCENT_COMPLETED    SID  SERIAL# USERNAME  OPNAME        TOTALWORK UNITS   START_TIME   MESSAGE----------------- ---------- ---------- ---------- -------------------- ---------- ---------- -------------- ---------------------------------------------------------------------------      70.88    128   17213 FRDC    Table Scan       2533381 Blocks   02-6月 -15   Table Scan: FRDC.H_BASE_MAIN05_T: 1795601 out of 2533381 Blocks done      12.58    128   17213 FRDC    Table Scan       2533381 Blocks   02-6月 -15   Table Scan: FRDC.H_BASE_MAIN05_T: 318705 out of 2533381 Blocks done      70.74    128   17213 FRDC    Table Scan       2533381 Blocks   02-6月 -15   Table Scan: FRDC.H_BASE_MAIN05_T: 1792046 out of 2533381 Blocks done      29.06     4   57701 FRDC    Index Fast Full Scan    2127 Blocks   13-5月 -15   Index Fast Full Scan: FRDC.O_BASE_DW: 618 out of 2127 Blocks done        0     5    280 SYS    Rowid Range Scan   4294967296 Blocks   02-6月 -15   Rowid Range Scan: FRDC.O_BASE_DW_T: 22055 out of 0 Blocks done        0    427   54951 FRDC    EXPORT_TB         7900 MB     02-6月 -15   EXPORT_TB: EXPORT : 0 out of 7900 MB done

–创造测验顾客

2数码导入

create user tests identified by tests;

2.1基本语法

$ impdp userid='uname/pwd' directory=pump_dir dumpfile=file01.dmp job_name='' log_file='' remap_schema='scott:sywu' remap_table='tb01:tb02' remap_tablespace='tbs01:ts01' tables='' schemas='' table_exists_action='' tablespaces='' exclude='' sqlfile=''

地点有的参数这里就不重复了,因为dump格局导出数据利用了remap_schema
表示重新定义schema,将旧的schema定义为新的schema,格式old:new,old:new….;
remap_table 表示重新定义表名,格式oldtbname:newtbname,….;
remap_tablespace 代表重新定义表空间名,格式old:new….;
tables 代表要导入的表名,假如未钦命则导入dumpfile里的全数表;
schemas
表示要导入的schema对象,假若未钦赐而且操功能户有权力则导入dumpfile里的持有schema;
table_exists_action
代表当导入表时若是表已经存在时的操作,参数值可感觉:append(追加State of Qatar、replace(替换数据库中留存的卡塔尔、truncate(删除已经存在的数量,导入dumpfile里面的数目卡塔尔(قطر‎、skip(不做别的操作卡塔尔;
tablespaces 表示导入的表空间名;
exclude
表示导入肃清的对象,DATABASE_EXPORT_OBJECTS表中著录全体的(数据库等级State of Qatar消亡情势,SCHEMA_EXPORT_OBJECTS表中记录schema级别杀相对象情势,TABLE_EXPORT_OBJECTS表中记录table等级肃清对象方式;
sqlfile 表示不导入数据仅生成导数ddl语句记录在该公文中;

–顾客授权

2.2导入数据

导入数据时要确认导入的客户是或不是留存,是或不是有权力访问表空间、是不是具备读写directory的权力;

$ impdp userid='ops$sywu/sywu' schemas='ops$sywu' directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmpImport: Release 11.2.0.3.0 - Production on Sat Jun 6 13:39:43 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "OPS$SYWU"."IMPORT_OPSSYWU" successfully loaded/unloadedStarting "OPS$SYWU"."IMPORT_OPSSYWU": userid=ops$sywu/******** schemas=ops$sywu directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmp Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "OPS$SYWU"."TB01"              74.64 MB 844416 rows. . imported "OPS$SYWU"."TB02"              452.3 KB  13448 rows. . imported "OPS$SYWU"."T_EXCE"             5.820 KB    2 rowsProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "OPS$SYWU"."IMPORT_OPSSYWU" successfully completed at 13:39:55

导入数据时重定义schema和tablespaces,重定义schema建议利用sysdba权限导入数据:

create tablespace tbs02 datafile '/u01/oradata/sydb/tbs02.dbf' size 10m autoextend on uniform size 4m;create user sywu identified by sywu default tablespace tbs02 quota unlimited on tbs02;grant create session,resource to sywu;grant read,write on directory pump_dir to sywu;exit;$ impdp userid="'sys/oracle as sysdba'" remap_schema='ops$sywu:sywu' remap_tablespace=tbs01:tbs02 directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmpImport: Release 11.2.0.3.0 - Production on Sat Jun 6 13:51:48 2015Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYS"."IMPORT_OPSSYWU" successfully loaded/unloadedStarting "SYS"."IMPORT_OPSSYWU": userid="sys/******** AS SYSDBA" remap_schema=ops$sywu:sywu remap_tablespace=tbs01:tbs02 directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmp Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "SYWU"."TB01"                74.64 MB 844416 rows. . imported "SYWU"."TB02"                452.3 KB  13448 rows. . imported "SYWU"."T_EXCE"               5.820 KB    2 rowsProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SYS"."IMPORT_OPSSYWU" successfully completed at 13:52:08

此外还会有限量行数的操作,操作看似上边导出操作;
The end(2015-06-06)

 

grant connect,resource to tests;

revoke imp_full_database from scott;

 

–付与顾客操作dump_dir目录的权杖

grant read ,write on directory dump_dir to tests;

grant read, write on directory dump_dir to scott;

———————————备份(导出)————–

–导出全方位数据库

expdp help=y –帮忙文件

–导出

expdp  system/accp@orcl directory=dump_dir  dumpfile=full.bak full=y 

–expdp导出

–directory   钦定目录

–dumpfile  钦点备份的文件名

–full      完整备份

–导出表空间

–查询表空间

select * from scott.

select  * from dba_tablespaces;

 –导出整个表空间 tablespaces表空间

expdp system/accp@orcl directory=dump_dir dumpfile=tablespacefull.bak
tablespaces

 

–导出内定表空间 tablespaces=表空间表

expdp system/accp@orcl directory=dump_dir dumpfile=ts_test.bak
tablespaces=tbs_test

–删除表空间

drop tablespace tbs_test;

–导出客商用SCHEMAS

–导出scott用户

expdp scott/tiger@orcl directory=dump_dir dumpfile=scottschma.bak 
schemas=scott

 

–导出表

expdp scott/tiger@orcl directory=dump_dir dumpfile=scotttabs.bak 
tables=emp,dept,bonus,salgrade

–使用管理员

expdp system/accp@orcl directory=dump_dir dumpfile=scotttbales.bak
tables=scott.emp,scott.dept,scott.bonus,scott.salgrade

–导出二个表

expdp scott/tiger@orcl directory=dump_dir dumpfile=scottemp.bak
  tables=emp

——————————恢复(导入)—————

–impdd导入关键字

 

–导入emp表,scottemp.bak文件

impdp scott/tiger@orcl directory=dump_dir dumpfile=scottemp.bak
tables=emp

 

–导入scott下的有着表 scotttabs.bak

impdp scott/tiger@orcl directory=dump_dir dumpfile=scotttabs.bak
tables=emp,dept,bonus,salgrade

–将scott下的兼具g表恢复生机到tests客户下,scott

–首先利用dba为scott权限imp_full_database

–导入数据权限

grant imp_full_database to scott;

–导出权力

grant exp_full_database to scott;

impdp scott/tiger@orcl directory=dump_dir dumpfile=SCOTTTABS.BAK
tables=emp,dept,bonus,salgrade remap_schema=scott:tests

–将scott下的有所g表复苏到tests客商下,使用项理 员

impdp system/accp@orcl directory=dump_dir dumpfile=SCOTTTABS.BAK
tables=scott.emp,scott.dept,scott.bonus,scott.salgrade
remap_schema=scott:tests

 

–导入scott用户,用SCOTT用户

impdp scott/tiger@orcl directory=dump_dir dumpfile=scottschma.bak
schemas=scott

–导入scott顾客,使用管理员

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图