ITPub博客

首页 > 应用开发 > IT综合 > Transporting Tablespaces with Self-Contained

Transporting Tablespaces with Self-Contained

原创 IT综合 作者:foreverlee 时间:2004-12-24 15:45:10 0 删除 编辑

1 Be aware of the following limitations as you plan for transportable tablespace use:

  1:The source and target database must be on the same hardware platform. For example, you can transport tablespaces between Sun Solaris Oracle databases, or you can transport tablespaces between Windows NT Oracle databases. However, you cannot transport a tablespace from a Sun Solaris Oracle database to an Windows NT Oracle database.
  2:The source and target database must use the same character set and national character set.
  3:You cannot transport a tablespace to a target database in which a tablespace with the same name already exists.
  4:Transportable tablespaces do not support: Materialized, views/replication ,Function-based indexes, Scoped REFs
  5:8.0-compatible advanced queues with multiple recipients
SQL> show parameter COMPATIBLE;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
compatible                           string
9.2.0.0.0

2 创建Self-Contained Set of Tablespaces

[@more@]

1 Be aware of the following limitations as you plan for transportable tablespace use:

  1:The source and target database must be on the same hardware platform. For example, you can transport tablespaces between Sun Solaris Oracle databases, or you can transport tablespaces between Windows NT Oracle databases. However, you cannot transport a tablespace from a Sun Solaris Oracle database to an Windows NT Oracle database.
  2:The source and target database must use the same character set and national character set.
  3:You cannot transport a tablespace to a target database in which a tablespace with the same name already exists.
  4:Transportable tablespaces do not support: Materialized, views/replication ,Function-based indexes, Scoped REFs
  5:8.0-compatible advanced queues with multiple recipients
SQL> show parameter COMPATIBLE;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
compatible                           string
9.2.0.0.0

2 创建Self-Contained Set of Tablespaces

SQL> edit
已写入文件 afiedt.buf

  1  create tablespace tsport1
  2  datafile 'E:oracleoradataliyongtemptsport1.dbf' size 5m
  3* autoextend on next 2m
SQL> /

表空间已创建。

SQL> edit
已写入文件 afiedt.buf

  1  create tablespace tsport2
  2  datafile 'E:oracleoradataliyongtemptsport2.dbf' size 5m
  3* autoextend on next 2m
SQL> /

表空间已创建。

SQL> create table tb1 (object_id number,object_name varchar2(10)) tablespace tsp
ort1;

表已创建。

SQL> alter table tb1 add (
  2  constraint tb1_pk1 primary key (object_id));

表已更改。
SQL> desc tb1;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 OBJECT_ID                                 NOT NULL NUMBER
 OBJECT_NAME                                        VARCHAR2(10)

SQL> create table tb2 (object_id number,obj_pro number) tablespace tsport2;

表已创建。

SQL> alter table tb2 add
  2  constraint tb2_fk1 foreign key (object_id)
  3  references tb1(object_id);

表已更改。
SQL> edit
已写入文件 afiedt.buf

  1  begin
  2      for i in 1..1000 loop
  3  insert into tb1 values (i,'Good Job');
  4      end loop;
  5       commit;
  6* end;
SQL> /

PL/SQL 过程已成功完成。
SQL> insert into tb2
  2  select object_id,1 from tb1;

已创建1000行。

SQL> conn / as sysdba;

SQL> EXECUTE dbms_tts.transport_set_check('tsport1,tsport2',true);

PL/SQL 过程已成功完成。

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
--------------------------------------------------------------------------------

Index LIYONG.TB1_PK1 in tablespace SYSTEM enforces primary constriants  of table

 LIYONG.TB1 in tablespace TSPORT1
 
3 开始做Transportable Tablespace
 
SQL> alter tablespace tsport1 read only;

表空间已更改。

SQL> alter tablespace tsport2 read only;

表空间已更改。
D:>EXP TRANSPORT_TABLESPACE=y TABLESPACES=(tsport1,tsport2) TRIGGERS=y CONSTRA
NTS=n GRANTS=n FILE=expdat.dmp

Export: Release 9.2.0.1.0 - Production on 星期五 12月 24 15:04:30 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


用户名:  liyong/xxx as sysdba

连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不会导出表数据(行)
注: 将不导出对表 / 视图 / 序列 / 角色的授权
注: 将不会导出表的约束条件
关于导出可传输的表空间元数据...
用于表空间 TSPORT1...
. 正在导出群集定义
. 正在导出表定义
. . 正在导出表                             TB1
用于表空间 TSPORT2...
. 正在导出群集定义
. 正在导出表定义
. . 正在导出表                             TB2
. 正在导出触发器
. 结束导出可传输的表空间元数据
在没有警告的情况下成功终止导出。

注意:
1Although the Export utility is used, only data dictionary structural information (metadata) for the tablespaces is exported. Hence, this operation goes quickly even for a large tablespace.
2If you are performing TSPITR or transport with a strict containment check, use:
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2)
   TTS_FULL_CHECK=Y FILE=expdat.dmp


4 plug tablespace

由于在本地做实验所以:
SQL> drop tablespace tsport1 including contents cascade constraints;

表空间已丢弃。
SQL> drop tablespace tsport2 including contents cascade constraints;

表空间已丢弃。

D:>IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp DATAFILES=('E:oracleoradataliy
ongtempTSPORT3.DBF','E:oracleoradataliyongtempTSPORT4.DBF') TABLESPACES=(
tsport1,tsport2)

Import: Release 9.2.0.1.0 - Production on 星期五 12月 24 15:21:32 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

用户名:  liyong/xxx as sysdba

连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件
关于导入可传输表空间元数据...
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将SYS的对象导入到 SYS
. 正在将LIYONG的对象导入到 LIYONG
. . 正在导入表                           "TB1"
. . 正在导入表                           "TB2"
成功终止导入,但出现警告。

SQL> conn liyong/xxx
已连接。
SQL> select count(*) from tb2;

  COUNT(*)
----------
      1000

SQL> select count(*) from tb1;

  COUNT(*)
----------
      1000

SQL> alter tablespace tsport1 read write;

表空间已更改。

SQL> alter tablespace tsport2 read write;

表空间已更改。


SQL> select owner,object_name,status from dba_objects where object_name = 'TB1';


OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------

STATUS
-------
LIYONG
TB1
VALID

出现警告应当是由于在imp的时候没有指定
TTS_OWNERS lists all users who own data in the tablespace set.

imp前 确保source DB and target DB的schema相同

出现警告应当是由于在imp的时候没有指定
TTS_OWNERS lists all users who own data in the tablespace set.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/76065/viewspace-785423/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-11-26

  • 博文量
    72
  • 访问量
    1357129