常用sql語句索引在線更新分區(qū)擴(kuò)展信息刪除重復(fù)行
1:索引在線更新：
ALTER INDEX owner.tablename REBUILD ONLINE ；
不能用帶表空間信息。如：This one works fine within PL/SQL:
ALTER INDEX LISADMIN.Isample REBUILD TABLESPACE INDX
ALTER INDEX LISADMIN.Isample REBUILD ONLINE TABLESPACE INDX
2:刪除重復(fù)行，保留最大值的行信息：
delete from 'table'
where (a,b,c) not in (select a,b,max(c) from 'table' group by a,b);
3：分區(qū)參數(shù)信息的更新
CREATE TABLE WDZW1 (
WDBH NUMBER (10) NOT NULL,
WDZW CLOB)
TABLESPACE TESTSPACE PCTFREE 5 PCTUSED 90 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 512 NEXT 512 )
PARTITION BY RANGE (WDBH)
(
PARTITION WDZW11 VALUES LESS THAN (100000)
PCTFREE 5 PCTUSED 90 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 512k NEXT 512k PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 999),
PARTITION WDZW21 VALUES LESS THAN (200000)
PCTFREE 5 PCTUSED 90 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 512k NEXT 512k PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 999）
）；
CREATE UNIQUE INDEX PK_WDZW ON
WDZW1(WDBH)
TABLESPACE TESTSPACE PCTFREE 10 STORAGE(INITIAL 512 NEXT 512 PCTINCREASE 0 ) ；
查看分區(qū)擴(kuò)展信息：
SQL> select INITIAL_EXTENT, NEXT_EXTENT ,MIN_EXTENTS ,MAX_EXTENTS from dba_segments
2 where segment_type = 'LOB PARTITION';
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
-------------- ----------- ----------- -----------
20480 20480 1 249
20480 20480 1 249
修改擴(kuò)展分區(qū)：
alter table wdzw1 modify lob(wdzw) (storage (MAXEXTENTS unlimited))
修改后結(jié)果信息：
SQL> select INITIAL_EXTENT, NEXT_EXTENT ,MIN_EXTENTS ,MAX_EXTENTS from dba_segments
2 where segment_type = 'LOB PARTITION';
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
-------------- ----------- ----------- -----------
20480 20480 1 2147483645
20480 20480 1 2147483645