- 论坛徽章:
- 0
|
本帖最后由 connectedtoyou 于 2016-07-05 17:51 编辑
数据泵导入导出案例(expdp & impdp)
案例环境:
rhel-server-6.5-x86_64
oracle 11.2.0.4.0
源数据库:
1、首先查看数据泵目录
<pre name="code" class="sql"><pre name="code" class="sql"><pre name="code" class="sql">SQL> set lines 200
SQL> col DIRECTORY_PATH for a80
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS SUBDIR /u01/app/oracle/product/11204/db/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11204/db/demo/schema/order_entry/
SYS LOG_FILE_DIR /u01/app/oracle/product/11204/db/demo/schema/log/
SYS MEDIA_DIR /u01/app/oracle/product/11204/db/demo/schema/product_media/
SYS DATA_FILE_DIR /u01/app/oracle/product/11204/db/demo/schema/sales_history/
SYS XMLDIR /u01/app/oracle/product/11204/db/rdbms/xml
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11204/db/ccr/hosts/localhost.localdomain/state
SYS DATA_PUMP_DIR /u01/app/oracle/admin/orcl/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11204/db/ccr/state
9 rows selected.
SQL>
此处,我们数据泵目录选用DATA_PUMP_DIR ,其对应的目录路径为 /u01/app/oracle/admin/orcl/dpdump/
当然此处也可以新建数据泵的directory,命令如下:
<pre name="code" class="sql">SQL> create directory dump_test as'/dump/test';
Directory created.
2、赋权限(数据泵导入导出时的执行用户 对上面选用的数据泵目录要有读写权限)
<pre name="code" class="sql">SQL> grant read,write on directory DATA_PUMP_DIR to hr;
Grant succeeded.
3、数据泵导出
3.1>按用户导出
a>首先查看需要导出的用户所在的表空间,以及该用户下的表在初始化时占用表空间的大小(换算为MB)此处以HR用户为例:
SQL> SELECT ds.tablespace_name,
2 SUM(ds.initial_extent) / 1024 / 1024 as initial_extent
3 FROM DBA_SEGMENTS ds
4 WHERE ds.owner IN ( --查看账户状态为open的用户HR
5 SELECT du.username
6 FROM DBA_USERS du
7 WHERE du.account_status = 'OPEN'
8 and du.username = 'HR')
9 GROUP BY ds.tablespace_name
10 ORDER BY initial_extent desc;
TABLESPACE_NAME INITIAL_EXTENT
------------------------------ --------------
EXAMPLE 1.5625
USERS .0625
SQL>
b>在表空间数量较多的情况下,可以用以下语句查看表在初始化时占用表空间的总大小:
SQL> SELECT SUM(ds.initial_extent) / 1024 / 1024 as initial_extent
2 FROM DBA_SEGMENTS ds
3 WHERE ds.owner IN ( --查看账户状态为open的用户HR
4 SELECT du.username
5 FROM DBA_USERS du
6 WHERE du.account_status = 'OPEN'
7 and du.username = 'HR');
INITIAL_EXTENT
--------------
1.625
SQL>
c>查看表空间对应的数据文件大小
SQL> set lines 200
SQL> col FILE_NAME for a50
SQL> col TOTAL_SPACE for a20
SQL> select tablespace_name, file_id, file_name,
2 round(bytes/(1024*1024),0)||' MB' total_space,AUTOEXTENSIBLE
3 from dba_data_files
4 order by tablespace_name;
TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE AUT
------------------------------ ---------- -------------------------------------------------- -------------------- ---
EXAMPLE 5 /oradata/orcl/example01.dbf 313 MB YES
SYSAUX 2 /oradata/orcl/sysaux01.dbf 520 MB YES
SYSTEM 1 /oradata/orcl/system01.dbf 750 MB YES
TEST_TBS 6 /oradata/orcl/test_tbs.dbf 30 MB YES
UNDOTBS1 3 /oradata/orcl/undotbs01.dbf 90 MB YES
USERS 4 /oradata/orcl/users01.dbf 5 MB YES
6 rows selected.
SQL>
上面a、b、c三个步骤查到的结果,在目标库规划存储空间大小和建表空间初始大小时作为参考条件。
d.expdp按用户导出(schemas=hr)
$ expdp hr/hr directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=hr.log schemas=hr
结果如下:
Export: Release 11.2.0.4.0 - Production on Sun Jul 3 21:38:06 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/******** directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=hr.log schemas=hr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/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/VIEW/VIEW
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
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/orcl/dpdump/hr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" <strong>successfully completed</strong> at Sun Jul 3 21:39:19 2016 elapsed 0 00:01:09
3.2>按表导出
expdp hr/hr directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=tab.log tables=hr.employees,hr.departments
结果如下:
Export: Release 11.2.0.4.0 - Production on Sun Jul 3 21:51:45 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=tab.log tables=hr.employees,hr.departments
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orcl/dpdump/tab.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jul 3 21:52:05 2016 elapsed 0 00:00:17
3.3>按查询条件导
<pre name="code" class="sql">expdp hr/hr directory=DATA_PUMP_DIR dumpfile=condition.dmp logfile=condition.log tables=hr.employees query=\' WHERE DEPARTMENT_ID\=\10\'
结果如下:
Export: Release 11.2.0.4.0 - Production on Sun Jul 3 22:06:09 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=DATA_PUMP_DIR dumpfile=condition.dmp logfile=condition.log tables=hr.employees query=' WHERE DEPARTMENT_ID=10'
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
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES" 9.320 KB 1 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orcl/dpdump/condition.dmp
Job "HR"."SYS_EXPORT_TABLE_01" <strong>successfully completed</strong> at Sun Jul 3 22:06:25 2016 elapsed 0 00:00:14
3.4>按表空间导出,以example表空间为例
expdp hr/hr directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tablespace.log TABLESPACES=example;
结果如下:
Export: Release 11.2.0.4.0 - Production on Sun Jul 3 22:14:26 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLESPACE_01": hr/******** directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tablespace.log TABLESPACES=example
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
Master table "HR"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLESPACE_01 is:
/u01/app/oracle/admin/orcl/dpdump/tablespace.dmp
Job "HR"."SYS_EXPORT_TABLESPACE_01" <strong>successfully completed </strong>at Sun Jul 3 22:15:04 2016 elapsed 0 00:00:36
3.5>全库导出
按全库导出时,若执行用户仍然使用hr,那么需要给hr赋予另外两个权限
第一个是EXP_FULL_DATABASE权限,否则,在执行全库导出命令时会报如下错误:
ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges
所以先给hr赋EXP_FULL_DATABASE权限
SQL> grant EXP_FULL_DATABASE to hr;
Grant succeeded.
第二个是EXEMPT ACCESS POLICY权限,否则在执行全库导出命令时会报如下错误,此错误可通过查看导出日志得到:
ORA-39181: Only partial table data may be exported due to fine grain access control on "OE"."PURCHAS
进一步根据报错代码得到如下信心:
39181, 0000, "Only partial table data may be exported due to fine grain access control on %s"
// *Cause: An unprivileged user has tried to export a table that has fine
// grain access control. The table owner is subjected to access
// control and may not be able to export all rows in the table.
// Only the rows that can be seen by that user will be exported. In
// order to preserve integrity of the table, the user importing the
// table should have enough privilege to recreate the table with the
// security policies at import time.
// *Action: It is strongly recommended that the database administrator handle
// exporting of this table.
大概意思就是:
由于细粒度访问控制存在,所以只有部分表数据可能被导出
原因:非特权用户试图导出一个具有细粒度的访问控制的表。表的所有者受到访问控制和可能无法导出表中的所有行。
用户只能导出能访问到的行。为了保持表的完整性,用户导入(此处应该写为:导入或者导出)表应该有足够的特权并通过安全策略重新创建表。
哈哈哈,最后,oracle官方强烈建议通过拥有dba权限的用户导出表。
给hr赋EXEMPT ACCESS POLICY权限
SQL> grant EXEMPT ACCESS POLICY to hr;
Grant succeeded.
当然了,直接可以直接通过as sysdba导出:
expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=full.dmp logfile=full.log parallel=2 full=y
此处,为了顺便更深入地理解权限问题,所以下面仍以hr用户为例:
expdp hr/hr directory=DATA_PUMP_DIR dumpfile=full.dmp logfile=full.log parallel=2 full=y
结果如下(太长,刷屏了,仅贴最后信息):
Master table "HR"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/admin/orcl/dpdump/full.dmp
Job "HR"."SYS_EXPORT_FULL_01" <strong>successfully completed </strong>at Sun Jul 3 23:15:50 2016 elapsed 0 00:06:01 |
|