oracle 交换表空间

设置文件输出路径：

SQL> alter systemset db_create_file_dest='/home/oracle';

 

System altered

 

SQL> showparameter db_create_file_dest;

 

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

db_create_file_dest                  string      /home/oracle

 

创建两个表空间用来存储范围分区表

SQL> createtablespace ts_range_30000 datafile size 50M autoextend on;

 

Tablespacecreated

 

SQL> createtablespace ts_range_50000 datafile size 50M autoextend on;

 

Tablespacecreated

 

创建分区表:

CREATE TABLEt_rangetest

(object_idNUMBER(5),

object_nameVARCHAR2(50),

create_dateDATE,

OBJECT_TYPEVARCHAR2(20))

PARTITION BYRANGE(object_id)

(

PARTITIONobject_id_30000 VALUES LESS THAN (30000) TABLESPACE ts_range_30000,

PARTITIONobject_id_50000 VALUES LESS THAN (MAXVALUE) TABLESPACE ts_range_50000

)

 

插入数据：

INSERT INTOt_rangetest

SELECTobject_id,object_name,created,object_type from Dba_Objects;

 

 

创建索引分区：

createindex IND_T_RANGETEST onT_RANGETEST(object_id)

local

(

PARTITIONobject_id_30000 tablespace ts_range_30000 ,

PARTITION object_id_50000 tablespace ts_range_50000 )

 

确定索引分区和表未分区位于同一个表空间

 

检查能不能以表空间传递的方式导出：

SQL> execdbms_tts.transport_set_check('ts_range_30000', TRUE);

 

PL/SQLprocedure successfully completed

 

SQL> SELECT* FROM transport_set_violations;

 

VIOLATIONS

--------------------------------------------------------------------------------

DefaultPartition (Table) Tablespace SYSTEM for T_RANGETEST not contained in tra

Sys ownedobject  IND_T_RANGETEST in tablespaceTS_RANGE_30000 not allowed in pl

Sys ownedobject  T_RANGETEST in tablespaceTS_RANGE_30000 not allowed in plugga

Partitionedtable SYS.T_RANGETEST is partially contained in the transportable se

 

发现system表空间没有包含在传递的表空间中，表和索引都不允许传递

按照谭大师书中的方法解决

 

创建临时表和临时表的索引：

SQL> createtable tmp_ts_range_30000 as select * from T_RANGETEST where 1=2;

 

Table created

 

SQL> createindex IND_TMP_ts_range_30000 on tmp_ts_range_30000(object_id);

 

Index created

 

要交换的分区共包含29553条记录

SQL> altertable T_RANGETEST exchange partition object_id_30000 with tabletmp_ts_range_30000 including indexes with validation;

 

Table altered

交换完毕

发现临时表被交换到了ts_range_30000这个分区上

此时

SQL> execdbms_tts.transport_set_check('ts_range_30000', TRUE);

 

PL/SQLprocedure successfully completed.

 

SQL>  SELECT * FROM transport_set_violations;

 

VIOLATIONS

--------------------------------------------------------------------------------

Sys ownedobject  IND_TMP_TS_RANGE_30000 intablespace TS_RANGE_30000 not allowe

d in pluggableset

 

Sys ownedobject  TMP_TS_RANGE_30000 in tablespaceTS_RANGE_30000 not allowed in

 pluggable set

 

还是不行上网查了下 原来systen表空间不能这么做，晕菜

 

没办法用普通用户重复上面的工作

发现终于ok了可以交换了

SQL> altertablespace ts_range_30000 read only;

 

Tablespacealtered

SQL> createor replace directory export as '/home/oracle';

 

Directorycreated

expdp system/oracle DIRECTORY=exportDUMPFILE=tts.dmp TRANSPORT_TABLESPACES= TS_RANGE_30000 TRANSPORT_FULL_CHECK=yLOGFILE=tts.log

导出成功

 

拷贝tts.dmp和表空间文件到目标计算机上

创建分区表：

CREATE TABLE t_rangetest

(object_id NUMBER(5),

object_name VARCHAR2(50),

create_date DATE,

OBJECT_TYPE VARCHAR2(20))

PARTITION BY RANGE(object_id)

(

PARTITION object_id_30000 VALUES LESS THAN(30000) ,

PARTITION object_id_50000 VALUES LESS THAN(MAXVALUE) 

)

创建索引

createindex IND_T_RANGETEST onT_RANGETEST(object_id)

local

(

PARTITIONobject_id_30000 ,

PARTITIONobject_id_50000  )

 

可见导入成功了第一次报错：

ORA-39123: Data Pump transportabletablespace job aborted

ORA-19721: Cannot find datafile withabsolute file number 8 in tablespace TS_RANGE_30000

发现自己把数据文件拷错了重新拷下导入即可

 

查看导入的表和索引：

最后将导入的临时表交换到分区表中

 

alter table t_rangetest exchange partitionobject_id_30000 with table tmp_ts_range_30000

 including indexes with validation;

 

最后查询

临时表中已经没记录了

分区表正常了

分区表的信息也是ok的