带有clob字段的自动分区表

SQL> select * From v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

问题：
1、lob对象的表空间是否可以分开放
2、lob对象为什么要打开行迁移
3、lob对象换表空间
建立含有lob字段的表时,oracle会自动为lob字段建立两个单独的segment,一个用来存放lob数据，另一个用来存放lob索引，
并且它们都会存储在对应表指定的表空间中。但是当我们用alter table tb_name move tablespace tbs_name;
 对表做表空间之间迁移时只能迁移非lob字段以外的segment，而如果要在移动表数据同时移动lob相关字段，
就必需用如下的含有特殊参数据的文句来完成：
alter table tb_name move tablespace tbs_name lob (column_lob1,column_lob2) store as(tablespace tbs_name);
4、分区表跟换分区名称的问题

SQL> create tablespace test  datafile '/u01/app/oracle/oradata/test01.dbf' size 200M;
Tablespace created.
SQL> create tablespace test_lob  datafile '/u01/app/oracle/oradata/testlob01.dbf' size 200M;
Tablespace created.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'

  如果日期格式不一致可能后面实验会报错

1.1 创建分区表

实验 一、验证lob对象是可以和表中的其他数据分开存放

1、创建一个按月自动分区，带有lob字段的，分区表

create table t ( dt date, x int, y varchar2(30), z clob ) partition by range(dt) interval(numtoyminterval(1,'MONTH')) store in (test) (partition T20130512 values less than (to_date('2013-05-12 14:45:13','YYYY-MM-DD HH24:MI:SS'))TABLESPACE TEST lob(z) store as (tablespace test_lob ) );

（1）interval：自动创建下个月的分区

根据年月 INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
根据月  INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
根据天  INTERVAL(NUMTODSINTERVAL(1,'DAY'))

（2）指定自动创建分区表的表空间

 interval（numtoyminterval （1,'month'）） store in（tablespace1,tablespace2,…,tablespacen）

使用命令：alter table t set store in（p1,p2）此命令只能针对新插入的数据

alter table t   set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));
alter table t set INTERVAL();

(3)指定lob对象存储的标空间

lob(z) store as (tablespace test_lob )

(4)给分区表重命名

alter table <table_name> rename partition <partition_name> to <new_partition_name>; 

插入数据

declare      l_data long := rpad('*',32000,'*');     begin             for i in 1 .. 10             loop                     insert into t (dt,x,y,z) values (add_months( to_date('2013-06-12 16:51:50','YYYY-MM-DD HH24:MI:SS'),i), i, i, l_data );            end loop;     end;

select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME from user_tab_partitions where  table_name='T'
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                               PARTITION_POSIT
------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------
T                              T20130512                      TO_DATE(' 2013-05-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T                              P201308                        TO_DATE(' 2013-08-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T                              P201309                        TO_DATE(' 2013-09-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T                              P201310                        TO_DATE(' 2013-10-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T                              P201311                        TO_DATE(' 2013-11-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T                              P201312                        TO_DATE(' 2013-12-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T                              P201401                        TO_DATE(' 2014-01-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T                              P201402                        TO_DATE(' 2014-02-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T                              P201403                        TO_DATE(' 2014-03-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T                              P201404                        TO_DATE(' 2014-04-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
T                              P201405                        TO_DATE(' 2014-05-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


已选择11行。


SQL>  select segment_name,partition_name,segment_type,tablespace_name from user_segments ;
SEGMENT_NAME                                                                      PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------ ------------------ ---------
T                                                                                                       SYS_P133               TABLE PARTITION    TEST
T                                                                                                       SYS_P136               TABLE PARTITION    TEST
T                                                                                                       SYS_P139               TABLE PARTITION    TEST
T                                                                                                       SYS_P142               TABLE PARTITION    TEST
T                                                                                                       T20130512              TABLE PARTITION    TEST
T                                                                                                         SYS_P115               TABLE PARTITION    TEST
T                                                                                                        SYS_P118               TABLE PARTITION    TEST
T                                                                                                           SYS_P121               TABLE PARTITION    TEST
T                                                                                                                SYS_P124               TABLE PARTITION    TEST
T                                                                                                             SYS_P127               TABLE PARTITION    TEST
T                                                                                                             SYS_P130              TABLE PARTITION    TEST
SYS_IL0000088919C00004$$                                                          SYS_IL_P114            INDEX PARTITION    TEST_LOB
SYS_IL0000088919C00004$$                                                          SYS_IL_P117            INDEX PARTITION    TEST
SYS_IL0000088919C00004$$                                                          SYS_IL_P120            INDEX PARTITION    TEST
SYS_IL0000088919C00004$$                                                          SYS_IL_P123            INDEX PARTITION    TEST
SYS_IL0000088919C00004$$                                                          SYS_IL_P126            INDEX PARTITION    TEST
SYS_IL0000088919C00004$$                                                          SYS_IL_P129            INDEX PARTITION    TEST
SYS_IL0000088919C00004$$                                                          SYS_IL_P132            INDEX PARTITION    TEST
SYS_IL0000088919C00004$$                                                          SYS_IL_P135            INDEX PARTITION    TEST
SYS_IL0000088919C00004$$                                                          SYS_IL_P138            INDEX PARTITION    TEST
SYS_IL0000088919C00004$$                                                          SYS_IL_P141            INDEX PARTITION    TEST
SYS_IL0000088919C00004$$                                                          SYS_IL_P144           INDEX PARTITION    TEST
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P113           LOB PARTITION      TEST_LOB
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P116           LOB PARTITION      TEST
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P119           LOB PARTITION      TEST
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P122           LOB PARTITION      TEST
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P125           LOB PARTITION      TEST
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P128           LOB PARTITION      TEST
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P131           LOB PARTITION      TEST
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P134           LOB PARTITION      TEST
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P137           LOB PARTITION      TEST
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P140           LOB PARTITION      TEST
SYS_LOB0000088919C00004$$                                                         SYS_LOB_P143          LOB PARTITION      TEST


已选择33行。

 

从此处看以看出：

 1、lob对象和分区表中的数据，是可以分开在两个表空间中的，如果不指定lob的表空间，那么lob对象是会和表空间的数据存放在同一个表空间中的。

2、lob对象是和表中的数据是分开在表空间中存放的，是一个单独的对象

3、从上面我们可以看出，lob对象也像分区表一样，也是一段一段分区  LOB PARTITION，有  INDEX PARTITION

SQL>  select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME from user_tab_partitions where  table_name='T'
  2  ;


TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                               PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------ ------------------------------
T                              T20130512                      TO_DATE(' 2013-05-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  1 TEST
T                              SYS_P115                       TO_DATE(' 2013-08-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  2 TEST
T                              SYS_P118                       TO_DATE(' 2013-09-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  3 TEST
T                              SYS_P121                       TO_DATE(' 2013-10-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  4 TEST
T                              SYS_P124                       TO_DATE(' 2013-11-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  5 TEST
T                              SYS_P127                       TO_DATE(' 2013-12-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  6 TEST
T                              SYS_P130                       TO_DATE(' 2014-01-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  7 TEST
T                              SYS_P133                       TO_DATE(' 2014-02-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  8 TEST
T                              SYS_P136                       TO_DATE(' 2014-03-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  9 TEST
T                              SYS_P139                       TO_DATE(' 2014-04-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                 10 TEST
T                              SYS_P142                       TO_DATE(' 2014-05-12 14:45:13', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                 11 TEST


已选择11行。

（5）给表分区表重命名：

declare   v_sql varchar(400);   v_table_name user_tab_partitions.table_name%type;   v_partition_name user_tab_partitions.partition_name%type;   v_high_value varchar(200);   v_tmp_partition_name user_tab_partitions.partition_name%type;   cursor cur is     select        table_name ,        partition_name ,        high_value       from user_tab_partitions       where partition_name like 'SYS%' ;  begin   open cur;    loop      fetch cur into v_table_name,v_partition_name,v_high_value;      exit when cur%notfound;      v_tmp_partition_name := substr(v_high_value,11,10);      v_tmp_partition_name := to_char( to_date(v_tmp_partition_name,'yyyy-mm-dd')-1 , 'yyyymm');      v_sql := 'alter table '||v_table_name||' rename partition '       ||v_partition_name        ||' to P'||v_tmp_partition_name;      dbms_output.put_line( v_sql );      execute immediate v_sql;    end loop;    close cur;  end;

 红色部分可以指定表名称的，如果数据库中的分区表不止一个，同时月份又重合，那么通过表名来区分，修改sql语句的红色部分

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,BLOCKS from user_tab_partitions where table_name='T';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                    BLOCKS
------------------------------ ------------------------------ ------------------------------ ----------
T                              T20130512                      TEST
T                              P201308                        TEST
T                              P201309                        TEST
T                              P201310                        TEST
T                              P201311                        TEST
T                              P201312                        TEST
T                              P201401                        TEST
T                              P201402                        TEST
T                              P201403                        TEST
T                              P201404                        TEST
T                              P201405                        TEST


已选择11行。

SQL> SELECT tablespace_name, table_name, column_name FROM   user_lobs WHERE  table_name   = 'T';
TABLESPACE_NAME       TABLE_NAME     COLUMN_NAM
------------------------------ ------------------------------ ----------
USERS       T      Z

 这个只是在user表空间的定义，实际的lob对象是存储在 test，test_lob表空间中，并没有存储在user表空间

SQL> select object_name,subobject_name,data_object_id from user_objects where object_name='T'; OBJECT_NAME SUBOBJECT_NAME DATA_OBJECT_ID ---------------------------------------------------------------------------- ------------------------------ -------------- T        T20130512 73664 T        P201308 73672 T          P201309 73669 T SQL> alter table t truncate  partition( p201309); Table truncated. SQL> select object_name,subobject_name,data_object_id from user_objects where object_name='T'; OBJECT_NAME         SUBOBJECT_NAME                  DATA_OBJECT_ID -------------------------------- ------------------------------ -------------- T                                             T20130512                          73664 T                                             P201308                              73672 T                                             P201309                              73801 T

  上述可以发现，truncate一个分区表后，lob对象也是包括在内被 truncate，那个分区的data_object_id变化了，

 

实验二： 为什么lob对象要打开行迁移

实验创建两张表t1、t2

SQL> create table t1   2  (   3  dt date,   4  x int,   5  y varchar2(30),   6  z clob   7  )   8  partition by range(dt)   9  interval(numtoyminterval(1,'MONTH')) store in (test)  10  (partition T20130512 values less than (to_date('2013-05-12 14:45:13','YYYY-MM-DD HH24:MI:SS'))TABLESPACE TEST  11  lob(z) store as (tablespace test_lob )  12  ); SQL> create table t2   2  (   3  dt date,   4  x int,   5  y varchar2(30),   6  z clob   7  )   8  partition by range(dt)   9  interval(numtoyminterval(1,'MONTH')) store in (test)  10  (partition T20130512 values less than (to_date('2013-05-12 14:45:13','YYYY-MM-DD HH24:MI:SS'))TABLESPACE TEST  11  lob(z) store as (tablespace test_lob ENABLE STORAGE IN ROW)  12  );

 t1表的lob对象没有打开行迁移，t2表lob对象打开了行迁移

插入数据：

 

遗留问题：

（1）分区表自动分区时，如果让lob对象单独指定表空间，如何指定，store in只能指定自动分区表数据的标空间，不能指定lob对象的表空间

（2） truncate 分区后，data_object_id变化了，那么数据被删除了吗，还是只是更新了数据字典，实际上数据并没有删除

参考

http://www.cnblogs.com/killkill/archive/2011/01/06/1928971.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:891347700346567659

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#SQLRF01402

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm#VLDBG1109