How to Change the LOB Storage Parameters after Creation
Goal:
Actually the goal behind creating this article is to try to provide a precise specific information about altering the storage parameters of a table that contains one or more LOB columns and provide a life examples on how to do so.
(文章的目的是提供一个精确的具体信息关于修改一个包括一个或者多个LOB字段的表的storage参数的方法)
Solution:
When creating a LOB column (CLOB, NCLOB and BLOB) that automatically creates 2 additional disk segments for this LOB column - a LOBINDEX and a LOBSEGMENT. These can be viewed, along with the LOB attributes, using the dictionary views:
select * from DBA_LOBS
select * from ALL_LOBS
select * from USER_LOBS
After creating the LOB column(LOBSEGMENT and LOBINDEX) you can only change the following Storage Parameters,The other LOB parameters cannot be changes after creation:
TABLESPACE
IN ROW
CHUNK
PCTVERSION
RETENTION
FREEPOOLS
CACHE
Changing the LOB Storage parameters can only be done by using the Alter Table Move command as following:
ALTER TABLE
e.g.
SQL> alter table TEST_LOB_TAB move lob(content) store as (tablespace system);
The above statement will move the whole "content" LOB segment(data and index) to the SYSTEM tablespace.
When creating a table, if you specify a tablespace for the LOB index for a non-partitioned table,then your specification of the tablespace will be ignored and the LOB index will be co-located with the LOB data. Partitioned LOBs do not include the LOB index syntax.(当你为非分区表指定lob index的tbs时,表空间将被忽略,lob index将被和lob data放在同一个表空间内)
And therefore to put the lobindex in a different tablespace you have to use the above mentioned MOVE command to move the whole LOB to another tablespace(所以如何想把lob index放在不同的表空间时,只能通过move命令去移动整个的LOB(index+data)to相应的tbs).
e.g.
SQL> select index_name, index_type, tablespace_name from dba_indexes where table_name like 'TEST_LOB_TAB';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME
------------------------------ --------------------------- ------------------------------
XIE2_TEST_LOB_TAB LOB ! ; USERS
SQL> alter table TEST_LOB_TAB move lob(content) store as (tablespace system);
Table altered.
SQL> select index_name, index_type, tablespace_name from dba_indexes where table_name like 'TEST_LOB_TAB';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME
------------------------------ --------------------------- ------------------------------
XIE2_TEST_LOB_TAB LOB ! ; &! nbsp;&nb sp; SYSTEM
有关LOB字段的表如何move tablespace,试验部分相关操作,记录如下:
1. 新建用户语句:
create user ecc_view identified by "ecc"
default tablespace data01
temporary tablespace TEMP
profile DEFAULT;
2. Grant/Revoke role privileges
grant connect to ecc_view ;
grant resource to ecc_view ;
grant create synonym, create view, create database link to ecc_view (10g中的connect中没有这个权限,需要单独授权)
3. revoke quota on tablespace except data01
REVOKE UNLIMITED TABLESPACE FROM "ecc_view"
ALTER USER "ecc_view" QUOTA UNLIMITED ON "data01"
a. move table to tablespace(非lob字段的对象)
select 'alter table '||segment_name ||' move tablespace data01 ; '
from user_segments a
where a.tablespace_name <> 'data01'
and a.segment_type = 'TABLE'
b. rebuild index
select ' alter index '||index_name||' rebuild tablespace data01;'
from user_ind_columns b, user_segments c
where b.index_name = c.segment_name
and c.tablespace_name <> 'data01'
c. lob segment move tablespace
select ' alter table '|| table_name || ' move lob('||column_name||') store as (tablespace data01); ' from user_lobs a
where a.tablespace_name <> 'data01'
4. check
select * from user_segments a
where a.tablespace_name = 'USERS'
--null
select * from user_objects b
where b.status <> 'VALID'
--null
select * from user_indexes b
where b.status <> 'VALID'
--null
参考文献:
Subject: How to Change the LOB Storage Parameters after Creation
Doc ID: 801338.1 Type: HOWTO
Modified Date : 06-APR-2009 Status: MODERATED
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9252210/viewspace-609840/,如需转载,请注明出处,否则将追究法律责任。
<%=items[i].content%>
<%if(items[i].items.items.length) { %><%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
转载于:http://blog.itpub.net/9252210/viewspace-609840/