可传输表空间的导出使用transport_tablespaces参数来表明，在可传输表空间模式中，只有表的元数据（及他们依赖的对象）和一系列表空间被卸载，这可以让表空间数据文件被拷贝到别的数据上使用import导入。要求有exp_full_database权限。
可传输表空间要求是自包含的。可传输表空间一旦停止了就不能重新开始，并且并行度不能超过1.目标数据的版本要和源库的版本相同或是更高。
使用可传输表空间必须要用8i或更高版本的企业版数据库，为了导入表空间到一个不同的平台上，数据库必须要有compatibility设置10.0或更高。
传输的表空间可以使字典管理的或是本地管理的表空间，从9i开始传输的表空间不在要求和目标库有相同的块大小。可传输表空间的速度很快，因为数据文件只是拷贝到目标库中，你使用exp/imp导入的只是表空间的元数据。可传输表空间支持data pump和exp/imp
可传输表空间应用的场景：
1在数据仓库中导入导出
2在多个数据库上拷贝只读表空间
3归档历史数据
4point-int-time-recovery
关于跨平台的传输表空间
从10g开始，传输表空间可以跨平台，并不是所有的平台都支持，可以查看v$transportable_platform视图来查看平台的支持，下面的查询显示了支持跨平台表空间的支持：
SQL> COLUMN PLATFORM_NAME FORMAT A32
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
17 Solaris Operating System (x86) Little
源和目标数据库要是偶不同的endianness，那么需要做格外的动作来转换，如果他们有相同的endianness，那么就好像是相同的平台不需要格外的设置。
可传输表空间的限制
1源和目标表空间要有相同的字符集和国家字符集
2目标库不能有要传输表空间的名字的表空间
3在10.2开始你可以传输包含xml格式的表空间，但是必须要使用exp/imp，不能用数据泵，当使用exp的时候，确保constraints和triggers参数设置成Y
下面的查询查看表空间中是否包含xml格式
select distinct p.tablespace_name from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t where t.table_name=x.table_name and t.tablespace_name=p.tablespace_name and x.owner=u.username
包含xml类型的表空间有下面的限制
1目标表空间必须要有xml db安装
2关联xml类型表的schema不能有循环依赖
3关联xml类型表的schema不能使xml db的标准schema
其他的限制
不能传输system表空间。binary_float和binary_double类型的可传输表空间要使用数据泵，不能使用exp。
步骤：
1如果是不同平台查看v$transportable_platform视图
2检查表空间是否是自包含
3生成传输表空间的集合
4传输表空间集合
5导入表空间集合
查看是否是自包含，可以使用下面的方法来检查
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);
执行完后，查看transport_set_violations视图，如果是自包含的，那么这个视图就是空的。下面的例子给了2个不是自包含的情况
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; VIOLATIONS --------------------------------------------------------------------------- Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and table JIM.DEPT in tablespace OTHER Partitioned table JIM.SALES is partially contained in the transportable set
3生成传输集合
a设置表空间只读
b使用expdp或是exp导出
EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = sales_1,sales_2
可以使用transport_full_check参数来加强包含检查的限制
可以使用rman 来转换endian格式如下：
RMAN> CONVERT DATAFILE 2> '/hq/finance/work/tru/tbs_31.f', 3> '/hq/finance/work/tru/tbs_32.f', 4> '/hq/finance/work/tru/tbs_41.f' 5> TO PLATFORM="Solaris[tm] OE (32-bit)" 6> FROM PLATFORM="HP TRu64 UNIX" 7> DB_FILE_NAME_CONVERT= 8> "/hq/finance/work/tru/", "/hq/finance/dbs/tru" 9> PARALLELISM=5;
导入传输集合
-
IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES= /salesdb/sales_101.dbf, /salesdb/sales_201.dbf REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)
In this example we specify the following:
-
The
DUMPFILEparameter specifies the exported file containing the metadata for the tablespaces to be imported. -
The
DIRECTORYparameter specifies the directory object that identifies the location of the dump file. -
The
TRANSPORT_DATAFILESparameter identifies all of the datafiles containing the tablespaces to be imported. -
The
REMAP_SCHEMAparameter changes the ownership of database objects. If you do not specifyREMAP_SCHEMA, all database objects (such as tables and indexes) are created in the same user schema as in the source database, and those users must already exist in the target database. If they do not exist, then the import utility returns an error. In this example, objects in the tablespace set owned bydcranneyin the source database will be owned bysmithin the target database after the tablespace set is imported. Similarly, objects owned byjfeein the source database will be owned bywilliamsin the target database. In this case, the target database is not required to have usersdcranneyandjfee, but must have userssmithandwilliams.
-
表空间相关的视图
| Description | |
|---|---|
V$TABLESPACE | Name and number of all tablespaces from the control file. |
DBA_TABLESPACES, USER_TABLESPACES | Descriptions of all (or user accessible) tablespaces. |
DBA_TABLESPACE_GROUPS | Displays the tablespace groups and the tablespaces that belong to them. |
DBA_SEGMENTS, USER_SEGMENTS | Information about segments within all (or user accessible) tablespaces. |
DBA_EXTENTS, USER_EXTENTS | Information about data extents within all (or user accessible) tablespaces. |
DBA_FREE_SPACE, USER_FREE_SPACE | Information about free extents within all (or user accessible) tablespaces. |
V$DATAFILE | Information about all datafiles, including tablespace number of owning tablespace. |
V$TEMPFILE | Information about all tempfiles, including tablespace number of owning tablespace. |
DBA_DATA_FILES | Shows files (datafiles) belonging to tablespaces. |
DBA_TEMP_FILES | Shows files (tempfiles) belonging to temporary tablespaces. |
V$TEMP_EXTENT_MAP | Information for all extents in all locally managed temporary tablespaces. |
V$TEMP_EXTENT_POOL | For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance. |
V$TEMP_SPACE_HEADER | Shows space used/free for each tempfile. |
DBA_USERS | Default and temporary tablespaces for all users. |
DBA_TS_QUOTAS | Lists tablespace quotas for all users. |
V$SORT_SEGMENT | Information about every sort segment in a given instance. The view is only updated when the tablespace is of theTEMPORARY type. |
V$TEMPSEG_USAGE | Describes temporary (sort) segment usage by user for temporary or permanent tablespaces. |
6468
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
