1、建立索引表
04:33:59 SQL> create table sales_info(
04:34:11 2 id number(6) constraint pk_sale primary key,
04:34:55 3 customer_name varchar2(30) ,slaes_amount number(10,2),
04:35:26 4 sales_date date,remark varchar2(2000)
04:35:56 5 ) organization index tablespace users01
04:36:28 6 pctthreshold 20 including remark
04:36:46 7 overflow tablespace users02;
Table created.
定义索引表时，主键约束和ORGANIZATION index 选项必须指定。
PCTTHRESHOLD :用于指定数据块中为键列和部分非键列数据所预留空间的百分比；如果数据块剩余空间低于PCTTHRESHOLD 设置，Oracle会将其他数据存放到溢出段。
INCLUDING column ：用于指定数据被存放到溢出段的起始列。
OVERFLOW TABLESPACE：用于指定溢出段所在的表空间。
2、修改索引表
1）移动索引表
04:49:41 SQL> alter table sales_info move tablespace users01;
Table altered.
2）增加溢出段
04:51:02 SQL> alter table sales_info add overflow tablespace users02;
alter table sales_info add overflow tablespace users02
*
ERROR at line 1:
ORA-25197: an overflow segment already exists for the indexed-organized table
04:51:04 SQL> alter table test add overflow tablespace users02;
alter table test add overflow tablespace users02
*
ERROR at line 1:
ORA-28658: This operation is supported only for Index-Organized tables
3、修改其他选项
04:52:34 SQL> alter table sales_info
04:52:37 2 initrans 4 pctthreshold 15 including remark
04:52:58 3 overflow initrans 6;
Table altered.
4）转换索引表为普通表
04:54:09 SQL> create table sales_info_new as select * from sales_info;
Table created.
移动一个带有lob字段的表到另外一个表空间
很多人都知道在建立含有lob字段的表时，Oracle会自动为lob字段建立两个单独的segment,一个用来存放数据，另一个用来存放索引，并且它们都会存储在对应表指定的表空间中。但是当我们用alter table tb_name move tablespace tbs_name;来对表做空间迁移时只能移动非lob字段以外的数据，而如果需要同时移动lob相关字段的数据，就必需用如下的含有特殊参数据的语句来完成。
alter table tb_name move tablespace tbs_name
lob (col_lob1,col_lob2) store as(tablesapce tbs_name);
下面是一个具体的示例：
Connected to Oracle8i Enterprise Edition Release 8.1.7.0.0
Connected as scott
SQL> desc test_blob;
Name Type Nullable Default Comments
--------- ------------ -------- ------- --------
FILE_NAME VARCHAR2(25) Y
FILE_BIN BLOB Y
FILE_BIN2 BLOB Y
SQL>
SQL> select t.segment_name, t.segment_type, t.tablespace_name
2 from sys.user_segments t
3 where t.segment_name like 'SYS_%'
4 /
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------ - ---------------- --------------
SYS_IL0000025842C00003$$ LOBINDEX SYSTEM
SYS_IL0000025842C00002$$ LOBINDEX SYSTEM
SYS_LOB0000025842C00002$$ LOBSEGMENT SYSTEM
SYS_LOB0000025842C00003$$ LOBSEGMENT SYSTEM
6 rows selected
SQL> alter table test_blob move tablespace tools;
Table altered
SQL>
SQL> select t.segment_name, t.segment_type, t.tablespace_name
2 from sys.user_segments t
3 where t.segment_name like 'SYS_%'
4 /
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------- ---------------- --------------
SYS_IL0000025842C00003$$ LOBINDEX SYSTEM
SYS_IL0000025842C00002$$ LOBINDEX SYSTEM
SYS_LOB0000025842C00002$$ LOBSEGMENT SYSTEM
SYS_LOB0000025842C00003$$ LOBSEGMENT SYSTEM
6 rows selected
SQL> select t.segment_name, t.segment_type, t.tablespace_name
2 from sys.user_segments t
3 where t.segment_name like 'TEST_BLOB';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------ ------------- -----------------
TEST_BLOB TABLE TOOLS
SQL> alter table test_blob move tablespace SYSTEM;
Table altered
SQL> ALTER TABLE test_blob MOVE
2 TABLESPACE tools
3 LOB (FILE_BIN,FILE_BIN2) STORE AS
4 (TABLESPACE tools);
Table altered
SQL> select t.segment_name, t.segment_type, t.tablespace_name
2 from sys.user_segments t
3 where t.segment_name like 'SYS_%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
--------------------------- ------------------ ------------------------
SYS_IL0000025842C00003$$ LOBINDEX TOOLS
SYS_IL0000025842C00002$$ LOBINDEX TOOLS
SYS_LOB0000025842C00002$$ LOBSEGMENT TOOLS
SYS_LOB0000025842C00003$$ LOBSEGMENT TOOLS
6 rows selected
CUUG
更多oracle视频教程请点击：http://crm2.qq.com/page/portalpage/wpa.php?uin=800060152&f=1&ty=1&aty=0&a=&from=6
