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 ( ); |
(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,'*'); |
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
|
红色部分可以指定表名称的,如果数据库中的分区表不止一个,同时月份又重合,那么通过表名来区分,修改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'; SQL> alter table t truncate partition( p201309); SQL> select object_name,subobject_name,data_object_id from user_objects where object_name='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