查看哪些用户下的数据，存放在表空间里面?
SQL> select owner,segment_name from dba_segments 2 where tablespace_name = 'TAB_U2'; OWNER SEGMENT_NAME ------------------------------ ------------------------------ U2 EMP1 U2 T U2 M U2 EMP U2 DEPT U2 LOG U2 LOG_LW U2 MYCLUSTER U2 MYC_IDX U2 SYS_C0012949 U2 SYS_C0012950 11 rows selected.
SQL> select name from v$database;--查看数据库名称 NAME --------- DATACENT
/*
实验目的: 采用可传输表空间方式，将数据库中的某个表空间进行移植
*/
DATACENTER-TAB_U2-U2
ARCERZHANG
1、确认平台是否支持
在目标数据库中执行
SELECT d.name,i.version,d.platform_name,endian_format FROM v$transportable_platform tp,v$database d,v$instance i WHERE tp.platform_name = d.platform_name;
SQL> / NAME VERSION PLATFORM_NAME ENDIAN_FORMAT --------- ----------------- ---------------------------------------- ---------------------------------------- ARCERZHA 11.2.0.1.0 Microsoft Windows IA (32-bit) Little
在源数据库中执行
SQL> l 1 SELECT d.name,i.version,d.platform_name,endian_format 2 FROM v$transportable_platform tp,v$database d,v$instance i 3* WHERE tp.platform_name = d.platform_name SQL> col platform_name format a40 SQL> / NAME VERSION PLATFORM_NAME ENDIAN_FORMAT --------- ----------------- ---------------------------------------- -------------- DATACENT 11.2.0.1.0 Linux x86 64-bit Little
查看字符编码
SQL> select * from v$transportable_platform order by 1; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- ---------------------------------------- -------------- 1 Solaris[tm] OE (32-bit) Big 2 Solaris[tm] OE (64-bit) Big 3 HP-UX (64-bit) Big 4 HP-UX IA (64-bit) Big 5 HP Tru64 UNIX Little 6 AIX-Based Systems (64-bit) Big 7 Microsoft Windows IA (32-bit) Little 8 Microsoft Windows IA (64-bit) Little 9 IBM zSeries Based Linux Big 10 Linux IA (32-bit) Little 11 Linux IA (64-bit) Little PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- ---------------------------------------- -------------- 12 Microsoft Windows x86 64-bit Little 13 Linux x86 64-bit Little 15 HP Open VMS Little 16 Apple Mac OS Big 17 Solaris Operating System (x86) Little 18 IBM Power Based Linux Big 19 HP IA Open VMS Little 20 Solaris Operating System (x86-64) Little 21 Apple Mac OS (x86-64) Little 20 rows selected.
2、选择自包含的表空间
有这样一种表，在当前表空间中，其主表在其他表空间当中.像这样的表就不属于自包含的对象.对于这样的对象，在进行表空间移植的后，读取这样的非自包含对象时，就会出问题.比如说分区表.
--检查是否存在非自包含对象 SQL> exec dbms_tts.transport_set_check('TAB_U2',TRUE); PL/SQL procedure successfully completed. --全部为自包含对象，可移植 SQL> select * from transport_set_violations; no rows selected
3、read only tablespace
SQL> alter tablespace tab_u2 read only; Tablespace altered.
使用EXPDP命令，结合目录对象TEST_DIR导出表空间数据
SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ -------------------------------------------------- SYS TEST_DIR /RealData/oracle/backup/data_pump SYS SUBDIR /RealData/oracle/demo/schema/order_entry//2002/Sep SYS SS_OE_XMLDIR /RealData/oracle/demo/schema/order_entry/ SYS LOG_FILE_DIR /RealData/oracle/demo/schema/log/ SYS DATA_FILE_DIR /RealData/oracle/demo/schema/sales_history/ SYS XMLDIR /ade/b/2125410156/oracle/rdbms/xml SYS MEDIA_DIR /RealData/oracle/demo/schema/product_media/ SYS DATA_PUMP_DIR /RealData/admin/DATACENTER/dpdump/ SYS ORACLE_OCM_CONFIG_DIR /RealData/oracle/ccr/state 9 rows selected.
4、导出元数据
[oracle@arcerzhang ~]$ expdp dumpfile=tab_u2.dmp directory=test_dir transport_tablespace=tab_u2; Export: Release 11.2.0.1.0 - Production on Sun Jun 9 16:23:03 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, Replaced with: "transport_tablespaces=" Legacy Mode has set reuse_dumpfiles=true parameter. FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA dumpfile=tab_u2.dmp directory=test_dir reuse_dumpfiles=true Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/ROLE_GRANT Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /RealData/oracle/backup/data_pump/tab_u2.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:23:26
5、
例行检查
SQL> set serverout on SQL> declare 2 db_ready boolean; 3 begin 4 db_ready := dbms_tdb.check_db('Microsoft Windows IA (32-bit)',2); 5 if (db_ready) then 6 dbms_output.put_line('True'); 7 else 8 dbms_output.put_line('False'); 9 end if; 10 end; 11 / Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and retry. False PL/SQL procedure successfully completed.
因为我们只是转换表空间，而不是转换整个数据，故：此错误可以忽略.
6、使用RMAN命令，进行转换
[oracle@arcerzhang data_pump]$ rman target / nocatalog Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jun 9 16:47:53 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: DATACENT (DBID=298836028) using target database control file instead of recovery catalog RMAN> convert tablespace tab_u2 2> to platform 'Microsoft Windows IA (32-bit)' 3> db_file_name_convert '/RealData/oradata/DATACENTER/','/RealData/oracle/backup/'; Starting conversion at source at 09-JUN-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=199 device type=DISK channel ORA_DISK_1: starting datafile conversion input datafile file number=00006 name=/RealData/oradata/DATACENTER/TAB_U2_01.dbf converted datafile=/RealData/oracle/backupTAB_U2_01.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25 Finished conversion at source at 09-JUN-13
7、ftp上传数据文件及元数据文件
在目标数据库中创建用户及目录对象
SQL> show user; USER is "SYS" SQL> create user U2 identified by U2; User created. SQL> create directory test_dir as 'D:\app\MaryHu\oradata\backup'; Directory created.
8、导入元数据