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

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:使用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.
此条目发表在 Oracle备份恢复 分类目录。将固定链接加入收藏夹。

使用copy实现long类型转移表空间》有 1 条评论

  1. 惜分飞 说:

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

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

      [引用]  [回复]

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>