oracle手动分区改为自动分区,oracle12c在线转换分区表，本地索引，自动按月间隔分区...

SELECT count(*) FROM AAA_DATA;  --AAA话单数据表

SELECT count(*) FROM IPTV_HW_DATA;  --IPTV华为数据表

SELECT count(*) FROM IPTV_ZTE_DATA;

在线转换分区表：

alter table AAA_DATA modify

PARTITION BY RANGE (INSERTTIME)

INTERVAL (NUMTOYMINTERVAL

(1,'MONTH'))

(

PARTITION

AAA_P1 VALUES LESS THAN (TO_DATE('2017-11-01', 'YYYY-MM-DD'))

) online;

create index IDX_AAA_INSERTTIME on AAA_DATA

(INSERTTIME,BTACHID) online nologging local parallel 8;

alter index IDX_AAA_INSERTTIME

noparallel;

alter table IPTV_HW_DATA modify

PARTITION BY RANGE (INSERTTIME)

INTERVAL (NUMTOYMINTERVAL

(1,'MONTH'))

(

PARTITION

HW_P1 VALUES LESS THAN (TO_DATE('2017-11-01', 'YYYY-MM-DD'))

) online;

create index IDX_HW_INSERTTIME on IPTV_HW_DATA

(INSERTTIME,BTACHID) online nologging local parallel 8 ;

alter index IDX_HW_INSERTTIME  noparallel;

alter table IPTV_ZTE_DATA modify

PARTITION BY RANGE (INSERTTIME)

INTERVAL (NUMTOYMINTERVAL

(1,'MONTH'))

(

PARTITION

ZTE_P1 VALUES LESS THAN (TO_DATE('2017-11-01', 'YYYY-MM-DD'))

) online;

create index IDX_ZTE_INSERTTIME on IPTV_ZTE_DATA

(INSERTTIME,BTACHID) online nologging local parallel 8;

alter index IDX_ZTE_INSERTTIME

noparallel;

检查索引情况：

SELECT index_name, column_name, descend,COLUMN_POSITION

FROM user_ind_columns  WHERE

table_name = 'AAA_DATA';

SELECT index_name, column_name, descend,COLUMN_POSITION

FROM user_ind_columns  WHERE

table_name = 'IPTV_HW_DATA';

SELECT index_name, column_name, descend,COLUMN_POSITION

FROM user_ind_columns  WHERE

table_name = 'IPTV_ZTE_DATA';

select table_name,index_name,status,blevel,leaf_blocks from

user_Indexes where table_name

in('AAA_DATA','IPTV_HW_DATA','IPTV_ZTE_DATA');

查看表占用：

select segment_name,sum(bytes)/1024/1024/1024 from

user_segments where segment_name

in('AAA_DATA','IPTV_HW_DATA','IPTV_ZTE_DATA') group by

segment_name;

查看分区数：

select table_name,partition_name from user_tab_partitions

where table_name

in('AAA_DATA','IPTV_HW_DATA','IPTV_ZTE_DATA');

select count(*) from IPTV_ZTE_DATA partition(ZTE_P1);

SET LINESIZE 120

COLUMN table_name FORMAT A20

COLUMN partition_name FORMAT A20

COLUMN high_value FORMAT A50

SELECT table_name,

partition_name,

high_value,

num_rows

FROM  user_tab_partitions

WHERE  table_name

in('AAA_DATA','IPTV_HW_DATA','IPTV_ZTE_DATA')

ORDER BY 1, 2;

压缩分区：

ALTER TABLE IPTV_ZTE_DATA MOVE PARTITION ZTE_P1

COMPRESS FOR QUERY UPDATE INDEXES

ONLINE;

更多分区操作参考：

http://www.cnblogs.com/yy3b2007com/p/7067954.html

http://blog.itpub.net/14359/viewspace-1194730/

查看表空间占用：

$ more tbs.sql

set line 132

set wrap off

select t.*

from (SELECT D.TABLESPACE_NAME,

SPACE "SUM_SPACE(M)",

BLOCKS SUM_BLOCKS,

SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",

ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2)

"USED_RATE(%)",

FREE_SPACE "FREE_SPACE(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

SUM(BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

UNION ALL --if have tempfile

SELECT D.TABLESPACE_NAME,

SPACE "SUM_SPACE(M)",

BLOCKS SUM_BLOCKS,

USED_SPACE "USED_SPACE(M)",

ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2)

"USED_RATE(%)",

SPACE - USED_SPACE "FREE_SPACE(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

SUM(BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE,

ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE

FROM V$SORT_USAGE

GROUP BY TABLESPACE) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t

order by "USED_RATE(%)" desc;

常用定时任务脚本：

[root@gddb]/#more

/gdhome/oracle/cml/idx.sh

#!/bin/sh

su - gdoracle -c "sqlplus /nolog <

set time on

set timing on

conn telecom/telecom

alter session set

workarea_size_policy=manual;

alter session set

sort_area_size=1073741820;

alter session set

sort_area_retained_size=1073741820;

alter session set db_file_multiblock_read_count=128;

create index IDX_T_FIX_DEALER on T_FIX_COMMUNICATION (DEALER)

nologging  local parallel 8 online;

alter index IDX_T_FIX_DEALER

noparallel;

alter index IDX_T_FIX_DEALER  LOGGING;

commit;

exit;

EOF" >> /gdhome/oracle/cml/idx.out

在线建索引异常中断后处理：

DECLARE

isClean BOOLEAN;

BEGIN

isClean := FALSE;

WHILE isClean=FALSE

LOOP

isClean :=

dbms_repair.online_index_clean(dbms_repair.all_index_id,

dbms_repair.lock_wait);

dbms_lock.sleep(5);

END LOOP;

END;