分类： Oracle
2010-12-09 16:27:21
------------------------- ------------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>
2>对象
--导出OFS2，hr对象
[oracle@node2 test]$ expdp hr/ank88ank DIRECTORY=dumptest DUMPFILE=schemahr.dmp SCHEMAS=hr logfile=exphrs.log;
Export: Release 10.2.0.1.0 - Production on Wednesday, 08 December, 2010 4:27:32
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_SCHEMA_01": hr/******** DIRECTORY=dumptest DUMPFILE=schemahr.dmp SCHEMAS=hr logfile=exphrs.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 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/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/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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.085 KB 25 rows
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.76 KB 107 rows
. . exported "HR"."JOBS" 6.609 KB 19 rows
. . exported "HR"."JOB_HISTORY" 6.585 KB 10 rows
. . exported "HR"."LOCATIONS" 7.710 KB 23 rows
. . exported "HR"."REGIONS" 5.296 KB 4 rows
. . exported "HR"."TEST" 5.226 KB 2 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/u02/test/schemahr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 04:28:31
--导入到不同的对象
--创建用户
SQL> create user hrin identified by hellojin
User created.
--相关权限
SQL>grant read, write on directory dumptest to hrin;
Grant succeeded.
Connected.
SQL> grant create session to hrin;
Grant succeeded.
SQL> grant connect to hrin;
Grant succeeded.
SQL> grant resource to hrin;
Grant succeeded.
--测试用户
SQL> conn hrin/hellojin
Connected.
SQL> create table one(id number);
Table created.
SQL>！
--导入如果是相同schema，impdp hr/ank88ank DIRECTORY=dumptest
DUMPFILE=schemahr.dmp SCHEMAS=hr logfile=exphrsi.log;
[oracle@node2 test]$ impdp hrin/hellojin DIRECTORY=dumptest DUMPFILE=schemahr.dmp SCHEMAS=hr REMAP_SCHEMA=hr:hrin logfile=expschemi.log;
Import: Release 10.2.0.1.0 - Production on Wednesday, 08 December, 2010 4:39:47
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 "HRIN"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "HRIN"."SYS_IMPORT_SCHEMA_01": hrin/******** DIRECTORY=dumptest DUMPFILE=schemahr.dmp SCHEMAS=hr REMAP_SCHEMA=hr:hrin logfile=expschemi.log
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/TABLE_DATA
. . imported "HRIN"."COUNTRIES" 6.085 KB 25 rows
. . imported "HRIN"."DEPARTMENTS" 6.632 KB 27 rows
. . imported "HRIN"."EMPLOYEES" 15.76 KB 107 rows
. . imported "HRIN"."JOBS" 6.609 KB 19 rows
. . imported "HRIN"."JOB_HISTORY" 6.585 KB 10 rows
. . imported "HRIN"."LOCATIONS" 7.710 KB 23 rows
. . imported "HRIN"."REGIONS" 5.296 KB 4 rows
. . imported "HRIN"."TEST" 5.226 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31685: Object type VIEW:"HRIN"."EMP_DETAILS_VIEW" failed due to insufficient privileges. Failing sql is:
CREATE FORCE VIEW "HRIN"."EMP_DETAILS_VIEW" ("EMPLOYEE_ID", "JOB_ID", "MANAGER_ID", "DEPARTMENT_ID", "LOCATION_ID", "COUNTRY_ID", "FIRST_NAME", "LAST_NAME", "SALARY", "COMMISSION_PCT", "DEPARTMENT_NAME", "JOB_TITLE", "CITY", "STATE_PROVINCE", "COUNTRY_NAME", "REGION_NAME") AS SELECT
e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.country_id,
e.first_name,
e
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
Job "HRIN"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 04:40:07
--测试是否成功导入
[oracle@node2 test]$ exit
exit
SQL> conn hrin/hellojin
Connected.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
ONE TABLE
DEPARTMENTS_SEQ SEQUENCE
EMPLOYEES_SEQ SEQUENCE
LOCATIONS_SEQ SEQUENCE
COUNTRIES TABLE
REGIONS TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
TABLE_NAME TABLE_TYPE
------------------------------ -----------
TEST TABLE
12 rows selected.
SQL> select * from test;
ID NAME
---------- ----------
2 k
3 n
SQL>
3>表空间
--创建表空间
U:sys@OFS2_SQL> create tablespace test_io datafile '/u02/test.dbf' size 5m;
Tablespace created.
--检查表空间
U:sys@OFS2_SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 TEST_IO YES NO YES
7 rows selected.
--建表放测试数据
SQL> conn hr/hellojin
Connected.
SQL> create table hr_t(id number) tablespace test_io;
Table created.
SQL> insert into hr_t values(1);
1 row created.
SQL> insert into hr_t values(2);
1 row created.
SQL> insert into hr_t values(3);
1 row created.
SQL> commit;
Commit complete.
1
2
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -------------------------------------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
LOCATIONS_SEQ SEQUENCE
DEPARTMENTS_SEQ SEQUENCE
EMPLOYEES_SEQ SEQUENCE
EMP_DETAILS_VIEW VIEW
TABLE_NAME TABLE_TYPE
BIN$luMXuelVIDfgQKjAghNYhA==$0 TABLE
TEST TABLE
HR_T TABLE
14 rows selected.
SQL>
--导出
[oracle@node2 ~]$
[oracle@node2 ~]$ expdp hr/hellojin DIRECTORY=dumptest DUMPFILE=ts_testhr.dmp
TABLESPACES=test_io logfile=exp.log;
Export: Release 10.2.0.1.0 - Production on Wednesday, 08 December, 2010 20:24:5
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_TABLESPACE_01": hr/******** DIRECTORY=dumptest DUMPFILE=ts_testhr.dmp TABLESPACES=test_io 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"."HR_T" 4.929 KB 3 rows
Master table "HR"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLESPACE_01 is:
/u02/test/ts_testhr.dmp
Job "HR"."SYS_EXPORT_TABLESPACE_01" successfully completed at 20:25:09
[oracle@node2 ~]$ !
--删除数据，做导入测试准备
SQL> drop tablespace test_io including contents;
Tablespace dropped.
SQL> select * from hr.hr_t;
select * from hr.hr_t *
ERROR at line 1:
ORA-00942: table or view does not exist
--数据已经销毁
--重新创建表空间。导入的时候表空间要存在。否则报错
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'TEST_IO' does not exist
SQL> create tablespace test_io datafile '/u02/test.dbf' size 5m reuse;
Tablespace created.
SQL> !
--开始导入
[oracle@node2 ~]$ impdp hr/hellojin DIRECTORY=dumptest DUMPFILE=ts_testhr.dmp
TABLESPACES=test_io logfile=expi.log;
Import: Release 10.2.0.1.0 - Production on Wednesday, 08 December, 2010 20:30:55
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_TABLESPACE_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLESPACE_01": hr/******** DIRECTORY=dumptest DUMPFILE=ts_testhr.dmp TABLESPACES=test_io logfile=expi.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."HR_T" 4.929 KB 3 rows
Job "HR"."SYS_IMPORT_TABLESPACE_01" successfully completed at 20:31:00
--做测试，是否导入成功
[oracle@node2 ~]$ exit
exit
SQL> select * from hr.hr_t;
ID
----------
1
2
3
SQL>
5>数据库
--导出
[oracle@node2 ~]$ expdp system/hellojin DIRECTORY=dumptest DUMPFILE=fulldb.dmp FULL=Y logfile=dbexp.log;
--导入
[oracle@node2 ~]$ impdp system/hellojin DIRECTORY=dumptest DUMPFILE=fulldb.dmp FULL=y logfile=dbexpi.log;
chinaunix网友2011-06-05 01:59:42
大连法律咨询在线 http://www.fabowang.com 大连律师在线咨询 http://www.fabowang.com 大连法律顾问网 http://www.fabowang.com 大连律师咨询 http://www.fabowang.com
chinaunix网友2010-12-23 16:15:15
SELECT a.tablespace_name, ROUND (a.total_size) "total_size(MB)", ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)", ROUND (b.free_size, 3) "free_size(MB)", ROUND (b.free_size / total_size * 100, 2) || '%' free_rate FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size FROM dba_data_files GROUP BY tablespace_name) a, ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size
