Oracle Data Dump 实验小结
Oracle Data Dump 实验小结
------------------------- ------------Data dump 测试-------------------------------------
1>表
--原来数据库
SQL> select name from v$database;
NAME
---------
OFS2
--创建测试表
SQL> conn hr/ank88ank
Connected.
SQL> create table test (id number,name varchar(10) );
Table created.
SQL> insert into test values(2,'k');
1 row created.
SQL> insert into test values(3,'n');
1 row created.
SQL> commit;
Commit complete.
--创建目录对象
SQL> create directory dumptest as '/u02/test' ;
Directory created.
SQL> grant read,write on directory dumptest to hr;
Grant succeeded.
--导出表
SQL> !
[Oracle@node2 bdump]$ expdp hr/hellojin directory=dumptest dumpfile=tab.dmp tables=test logfile=exp.log;
Export: Release 10.2.0.1.0 - Production on Wednesday, 08 December, 2010 3:25:10
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=dumptest dumpfile=tab.dmp tables=test logfile=exp.log
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"."TEST" 5.226 KB 2 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u02/test/tab.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 03:26:06
[oracle@node2 bdump]$ cd /u02
[oracle@node2 test]$ pwd
/u02/test
[oracle@node2 test]$ ls
exp.log tab.dmp
[oracle@node2 ~]$
[oracle@node2 ~]$ export ORACLE_SID=OFS2
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 8 03:37:26 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
--删除OFS2数据库，hr用户的test表的内容，
SQL> conn hr/ank88ank
Connected.
SQL> select * from test;
ID NAME
---------- ----------
2 k
3 n
SQL> delete test ;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
0
--做导入部分
--数据来源，OFS2数据库，hr用户test表-->OFS2数据库，hr用户test表
[oracle@node2 test]$ export ORACLE_SID=OFS2
[oracle@node2 test]$ impdp hr/hellojin directory=dumptest dumpfile=tab.dmp tables=test;
Import: Release 10.2.0.1.0 - Production on Wednesday, 08 December, 2010 3:41:15
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Conected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "HR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/******** directory=dumptest dumpfile=tab.dmp tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "HR"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 03:41:25
--将要导入的数据库已经存在表，未设置特别导入参数，因此保错，做如下处理
--删除OFS2数据库的hr用户的test表
SQL> delete test ;
2 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL> select count(*) from test;
COUNT(*)
----------
0
--重新执行导入操作
[oracle@node2 test]$ impdp hr/hellojin directory=dumptest dumpfile=tab.dmp tables=test;
Import: Release 10.2.0.1.0 - Production on Wednesday, 08 December, 2010 3:41:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "HR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/******** directory=dumptest dumpfile=tab.dmp tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."TEST" 5.226 KB 2 rows
Job "HR"."SYS_IMPORT_TABLE_01" successfully completed at 03:43:20
[oracle@node2 test]$
--检查
SQL> select * from test;
ID NAME
---------- ----------
2 k
3 n
SQL>
--数据来源，OFS2数据库，hr.test表-->OFS2数据库，scott.test表
--数据来源，OFS2数据库，hr.test表-->OASM数据库，scott.test表，同理
--不同的方案导入，加入参数REMAP_SCHEM
[oracle@node2 ~]$ export ORACLE_SID=OASM
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 8 03:46:38 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
--目录对象
SQL> grant read,write on directory dumptest to scott;
Grant succeeded.
--用户相关操作
SQL> alter user scott account unlock;
User altered.
SQL> alter user scott identified by hellojin;
User altered.
SQL> !
--执行导入
[oracle@node2 ~]$ impdp scott/hellojin DIRECTORY=dumptest DUMPFILE=tab.dmp tables=hr.test REMAP_SCHEMA=hr:scott logfile=exp2.log;
--不同方案需要加入remap_schema参数
Import: Release 10.2.0.1.0 - Production on Wednesday, 08 December, 2010 4:17:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** DIRECTORY=dumptest DUMPFILE=tab.dmp tables=hr.test REMAP_SCHEMA=hr:scott logfile=exp2.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TEST" 5.226 KB 2 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 04:18:01
[oracle@node2 ~]$ exit
exit
--检查
SQL> conn scott/hellojin
Connected.
SQL> select * from test;
ID NAME
---------- ----------
2 k
3 n
SQL>
|
评论暂时关闭