使用copy实现long类型转移表空间

在一次8.1.6的数据库恢复过程中,发现一个表空间的数据文件损坏,在转移该表空间相关表时,遇到让人郁闷的long类型.不能使用ctas和move来实现转移,最后通过古老的copy来实现该项工作.
模拟LONG类型表

SQL> create table chf.t_long (id number,name long) tablespace ts_xifenfei;
Table created.
SQL> insert into chf.t_long  select object_id,object_name from dba_objects where rownum<10;
9 rows created.
SQL> commit;
Commit complete.
SQL> desc chf.t_long
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 ID                                                 NUMBER
 NAME                                               LONG

测试ctas和move

SQL> create table chf.t_long_bak
  2  as
  3  select * from chf.t_long;
select * from chf.t_long
       *
ERROR at line 3:
ORA-00997: illegal use of LONG datatype
SQL> alter table chf.t_long move tablespace users;
alter table chf.t_long move tablespace users
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

使用copy实现LONG表跟换表空间

SQL> SET LONG 1000
SQL> select dbms_metadata.get_ddl('TABLE','T_LONG','CHF') from dual;
DBMS_METADATA.GET_DDL('TABLE','T_LONG','CHF')
--------------------------------------------------------------------------------
  CREATE TABLE "CHF"."T_LONG"
   (    "ID" NUMBER,
        "NAME" LONG
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_XIFENFEI"
SQL>   CREATE TABLE "CHF"."T_LONG_BAK"
  2     (    "ID" NUMBER,
  3          "NAME" LONG
  4     ) SEGMENT CREATION IMMEDIATE
  5    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  6   NOCOMPRESS LOGGING
  7    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  8    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  9    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 10    TABLESPACE "USERS";
Table created.
SQL> copy from chf/xifenfei@ora11g_d INSERT chf.t_long_bak using select * from chf.t_long;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
   9 rows selected from chf@ora11g_d.
   9 rows inserted into CHF.T_LONG_BAK.
   9 rows committed into CHF.T_LONG_BAK at DEFAULT HOST connection.
SQL> alter table t_long rename to t_long_old;
Table altered.
SQL> alter table t_long_bak rename to t_long;
Table altered.
SQL> select tablespace_name,table_name from dba_tables where table_name like 'T_LONG%';
TABLESPACE_NAME TABLE_NAME
--------------- ---------------
TS_XIFENFEI     T_LONG_OLD
USERS           T_LONG
SQL> DROP TABLE T_LONG_OLD PURGE;
Table dropped.

One thought on “使用copy实现long类型转移表空间”

LOB类型移动表空间
LOB类型在建立含有lob字段的表时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据（segment_type=LOBSEGMENT）[当存储字节数不超过4k,默认不创建],另一个用来存放索引（segment_type=LOBINDEX）.默认它们会存储在和表一起的表空间.我们对表MOVE时，LOG类型字段和该字段的索引不会跟着MOVE，必须要单独来进行MOVE

--模拟lob表
SQL&gt; create table chf.t_lob(id number,name clob) tablespace ts_xifenfei;
Table created.
SQL&gt; insert into chf.t_lob  select object_id,object_name from dba_objects where rownum&lt;10;
9 rows created.
SQL&gt; commit;
Commit complete.
SQL&gt; desc chf.t_lob
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               CLOB
--查询含有segment数目(这里未出现LOBSEGMENT)
SQL&gt; COL SEGMENT_NAME FOR A30
SQL&gt; select segment_name,SEGMENT_TYPE from dba_segments where (SEGMENT_NAME,TABLESPACE_NAME) IN
  2  (SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME='T_LOB' AND
  3   TABLESPACE_NAME='TS_XIFENFEI')
  4  UNION ALL
  5  select segment_name,SEGMENT_TYPE from dba_segments where (SEGMENT_NAME,TABLESPACE_NAME) IN
  6  (SELECT INDEX_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE TABLE_NAME='T_LOB' AND
  7   TABLESPACE_NAME='TS_XIFENFEI');
SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------------
T_LOB                          TABLE
SYS_IL0000076349C00002$$       LOBINDEX
--move table
SQL&gt; ALTER TABLE CHF.T_LOB MOVE TABLESPACE USERS;
Table altered.
SQL&gt; SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME='T_LOB';
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T_LOB                          USERS
SQL&gt; SELECT INDEX_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE TABLE_NAME='T_LOB';
INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SYS_IL0000076349C00002$$       TS_XIFENFEI
--move lob列
SQL&gt; alter table CHF.T_LOB move lob(NAME) store as (tablespace users);
Table altered.
SQL&gt; SELECT INDEX_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE TABLE_NAME='T_LOB';
INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SYS_IL0000076349C00002$$       USERS

使用copy实现long类型转移表空间

One thought on “使用copy实现long类型转移表空间”

发表评论 取消回复

发表评论取消回复