[20130513]给lob字段命名的问题.txt

[20130513]给lob字段命名的问题.txt

前几天同事建议把一些表移动到其他表空间,主要是增长太快.在测试时忘记移动lob字段,这样效果不大.
我突然想能否lob字段命名一个好名字,这样看到这个字段就知道它属于这个表.

自己在测试环境做一些测试:

1.测试环境:

SQL> @ver

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t( id number,image blob);

SQL> select table_name,column_name,segment_name,tablespace_name,index_name  from dba_lobs where wner=user and table_name='T';

TABLE_NAME COLUMN_NAME          SEGMENT_NAME         TABLESPACE_NAME                INDEX_NAME

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

T          IMAGE                SYS_LOB0000274635C00 USERS                          SYS_IL0000274635C00002$$

                                002$$

--274635标识表T的object_id,2表示第2个字段.

SQL> select object_id,data_object_id from dba_objects where wner=user and object_name='T';

 OBJECT_ID DATA_OBJECT_ID

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

    274635         274635

--很明显如果知道SYS_LOB0000274635C00002$$,要知道是那个表的lob字段,要查询dba_lobs视图.

SQL> select table_name,column_name,segment_name,tablespace_name,index_name  from dba_lobs where SEGMENT_NAME='SYS_LOB0000274635C00002$$';

TABLE_NAME COLUMN_NAME          SEGMENT_NAME         TABLESPACE_NAME                INDEX_NAME

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

T          IMAGE                SYS_LOB0000274635C00 USERS                          SYS_IL0000274635C00002$$

                                002$$

2.如何改名呢?
先试验按照常规方法修改索引:

SQL> alter index "SYS_IL0000274635C00002$$" rename to T_IMAGE_IDX;

alter index "SYS_IL0000274635C00002$$" rename to T_IMAGE_IDX

*

ERROR at line 1:

ORA-22864: cannot ALTER or DROP LOB indexes

--行不通.

--抽取定义:

select dbms_metadata.get_ddl('TABLE','T') from dual;

  CREATE TABLE "SCOTT"."T" 

   (    "ID" NUMBER, 

    "IMAGE" BLOB

   ) SEGMENT CREATION DEFERRED 

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 

 NOCOMPRESS LOGGING

  TABLESPACE "USERS" 

 LOB ("IMAGE") STORE AS BASICFILE (

  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 

  NOCACHE LOGGING ) 

--不知道如何修改.仔细看一些手册,写成

CREATE TABLE "SCOTT"."T" 

   (    "ID" NUMBER, 

    "IMAGE" BLOB

   ) SEGMENT CREATION DEFERRED 

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 

 NOCOMPRESS LOGGING

  TABLESPACE "USERS" 

 LOB ("IMAGE") STORE AS  BASICFILE t_image_lob (

  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 

  NOCACHE LOGGING ) ;

测试看看.通过.

SQL> select table_name,column_name,segment_name,tablespace_name,index_name  from dba_lobs where wner=user and table_name='T';

TABLE_NAME COLUMN_NAME          SEGMENT_NAME         TABLESPACE_NAME                INDEX_NAME

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

T          IMAGE                T_IMAGE_LOB          USERS                          SYS_IL0000274638C00002$$

--建立的段名已经T_IMAGE_LOB.对应的索引依旧不行.再仔细看手册加上一个google,找到如下链接:

http://www.dbaglobe.com/2010/06/lobsegment-defragmentation.html

写成如下:

drop table t purge;

create table t( id number,image blob)

    LOB ( image) STORE AS BASICFILE t_image_lob (

      TABLESPACE users

      ENABLE STORAGE IN ROW

      INDEX t_image_idx ( TABLESPACE users ));

SQL> select table_name,column_name,segment_name,tablespace_name,index_name  from dba_lobs where wner=user and table_name='T';

TABLE_NAME COLUMN_NAME          SEGMENT_NAME         TABLESPACE_NAME                INDEX_NAME

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

T          IMAGE                T_IMAGE_LOB          USERS                          T_IMAGE_IDX

3.顺便测试一下move的情况:

SQL> insert into t values (1,lpad('a',4000,'a'));

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t move tablespace test lob (image) store as( tablespace test );

SQL> select * from dba_extents where wner=user and segment_name in ('T_IMAGE_LOB','T_IMAGE_IDX','T');

OWNER  SEGMENT_NAME   SEGMENT_TYPE       TABLESPACE_NAME   EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

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

SCOTT  T_IMAGE_LOB    LOBSEGMENT         TEST                      0          8        144      65536          8            8

SCOTT  T_IMAGE_IDX    LOBINDEX           TEST                      0          8        152      65536          8            8

SCOTT  T              TABLE              TEST                      0          8        160      65536          8            8

--很明显索引也一起移动了.


--执行如下,效果也一样.

SQL> alter table t move tablespace test lob (image) store as( tablespace test index t_image_idx (tablespace test) );

Table altered.

SQL> select * from dba_extents where wner=user and segment_name in ('T_IMAGE_LOB','T_IMAGE_IDX','T');

OWNER  SEGMENT_NAME  SEGMENT_TYPE       TABLESPACE_NAME   EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

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

SCOTT  T_IMAGE_LOB   LOBSEGMENT         TEST                      0          8        168      65536          8            8

SCOTT  T_IMAGE_IDX   LOBINDEX           TEST                      0          8        176      65536          8            8

SCOTT  T             TABLE              TEST                      0          8        184      65536          8            8