分区表/分区索引查看，在线重建分区索引
1.查看分区索引/普通索引2.查看分区使用空间3.重建分区索引/主键4.增加/删除分区5.创建分区表。分区字段值不能修改。6.非分区表重建成分区表1.查看分区索引/普通索引
--1.查看所有普通索引select *from user_indexes a,user_tables bwhere a.table_name = b.table_name;--2.查看所有分区索引select A.STATUS,C.STATUS,a.TABLEspace_name,A.*from user_ind_partitions a,user_tab_partitions b,user_indexes cwhere a.index_name = c.index_nameand c.table_name = b.table_nameand b.partition_name = a.partition_name --AND C.TABLE_NAME='SPC_RESULT_T' ORDER BY C.TABLE_NAME,C.INDEX_NAME,a.PARTITION_NAME;--3.查看索引对应的字段（LISTAGG用来拼接多个字段的联合索引）select a.table_name,b.index_name 索引名称, LISTAGG(b.column_name,',') WITHIN GROUP (ORDER BY b.column_name) 字段 from user_indexes a ,user_ind_columns b where a.table_name=b.table_name and a.index_name = b.index_name GROUP BY a.table_name,b.index_name;--4.查看分区表中有没有非分区索引SELECT c.STATUS,c.PARTITIONED,c.* FROM user_indexes c WHERE table_name IN (SELECT DISTINCT table_name FROM user_tab_partitions) AND PARTITIONED='NO' ORDER BY TABLE_NAME ,INDEX_NAME;
2.查看分区使用空间
--4.查看各分区使用多少空间select t.BYTES/(1024*1024*1024),t.* from user_segments twhere tablespace_name = 'T10SPCSYS_DATA'--AND SEGMENT_NAME='UI_TASK_EXECRECORD_T';ORDER BY t.BYTES/(1024*1024*1024) desc;--5.查看账号空间使用率SELECTfre.* ,use.* ,fre.free_g + use.use_g all_g,ROUND(use.use_g*100 /(fre.free_g + use.use_g), 2)|| '%' AS P_usedFROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) FREE_gFROMSYS.user_FREE_SPACEGROUP BYTABLESPACE_NAME ) fre,(SELECT tablespace_name,SUM(BYTES)/(1024 * 1024 * 1024) use_gFROMuser_segmentsGROUP BYtablespace_name ) useWHEREuse.TABLESPACE_NAME = fre.TABLESPACE_NAME;
3.重建分区索引/主键
分区主键/唯一索引必须包含分区字段。先件唯一分区索引，在应用主键上。
--一.--1.drop主键：alter table SPC_ID_T drop constraint SYS_C0012462 ;--2.drop主键时会把对应的索引也drop--drop index SYS_C0012462 ;--3.在线重建分区索引create index SP_IDT_VALUEID_IDX on SP_ID_T (VALUE_ID) LOCAL tablespace k10SPSYS_INDEX online;--二.--重建分区主键：alter table SPC_RESULT_T drop constraint SPC_RESULT_PK ;--drop index SPC_RESULT_PK ;--分区主键/唯一索引必须包含分区字段，如（SPC_TIME是分区字段），先件唯一分区索引，在应用主键上：create unique index SPC_RESULT_PK on SPC_RESULT_T (VALUE_ID,SPC_TIME) LOCAL tablespace T10SPCSYS_INDEX online;alter table SPC_RESULT_T add constraint SPC_RESULT_PK primary key (VALUE_ID,SPC_TIME) using INDEX localSPC_RESULT_PK ;
4.增加/删除分区
ALTER TABLE SPC_RV_RECIVE_T ADD PARTITION "PART_10" VALUES LESS THAN (TO_DATE(' -10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))；ALTER TABLE SPC_RV_RECIVE_T DROP PARTITION PART_10;
5.创建分区表。分区字段值不能修改。
CREATE TABLE "RV_LISTENER_LOG_T" ("SITE" VARCHAR2(32) NOT NULL ENABLE, "SHOP" VARCHAR2(32) NOT NULL ENABLE, "SOURCE" VARCHAR2(32) NOT NULL ENABLE, "PID" VARCHAR2(32), "ERROR_TYPE" VARCHAR2(64), "ERROR_INFO" VARCHAR2(4000), "ERROR_MSG" CLOB, "CLASS_NAME" VARCHAR2(4000), "FUNCTION_NAME" VARCHAR2(4000), "LINE_NUM" NUMBER, "IP" VARCHAR2(32), "UPDATE_TIME" DATE DEFAULT SYSDATE NOT NULL ENABLE)TABLESPACE "M10SPCSYS_DATA" PARTITION BY RANGE ("UPDATE_TIME") (PARTITION "PART_02" VALUES LESS THAN (TO_DATE(' -02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "M10SPCSYS_DATA" , PARTITION "PART_03" VALUES LESS THAN (TO_DATE(' -03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "M10SPCSYS_DATA" , PARTITION "PART_04" VALUES LESS THAN (TO_DATE(' -04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "M10SPCSYS_DATA" , PARTITION "PART_05" VALUES LESS THAN (TO_DATE(' -05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "M10SPCSYS_DATA" , PARTITION "PART_06" VALUES LESS THAN (TO_DATE(' -06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "M10SPCSYS_DATA" , PARTITION "PART_07" VALUES LESS THAN (TO_DATE(' -07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "M10SPCSYS_DATA" , PARTITION "PART_08" VALUES LESS THAN (TO_DATE(' -08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "M10SPCSYS_DATA" )
6.非分区表重建成分区表
--1.新建分区表CREATE TABLE "T10SPCSYS"."SPC_ALARM_RULE_T_A" ("VALUE_ID" NUMBER NOT NULL ENABLE, "SPEC_RULE1" NVARCHAR2(100), "SPEC_RULE2" NVARCHAR2(100), "CONTROL_RULE1" NVARCHAR2(100), "CONTROL_RULE2" NVARCHAR2(100), "CONTROL_RULE3" NVARCHAR2(100), "WARN_RULE1" NVARCHAR2(100), "WARN_RULE2" NVARCHAR2(100), "SPEC_OUTVALUES" VARCHAR2(4000), "CONTROL_OUTVALUES" VARCHAR2(4000), "WARN_OUTVALUES" VARCHAR2(4000), "CREATE_TIME" DATE, "ALARM_RULE_TYPE" VARCHAR2(100), "SPEC_HOLD2" VARCHAR2(100), "CONTROL_HOLD1" VARCHAR2(100), "CONTROL_HOLD2" VARCHAR2(100), "CONTROL_HOLD3" VARCHAR2(100), "WARN_HOLD1" VARCHAR2(100), "WARN_HOLD2" VARCHAR2(100), "SPEC_HOLD1" VARCHAR2(100), PRIMARY KEY ("VALUE_ID"))PARTITION BY RANGE ("CREATE_TIME") (PARTITION "PART_03" VALUES LESS THAN (TO_DATE(' -03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "T10SPCSYS_DATA" ,PARTITION "PART_04" VALUES LESS THAN (TO_DATE(' -04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "T10SPCSYS_DATA" ,PARTITION "PART_05" VALUES LESS THAN (TO_DATE(' -05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "T10SPCSYS_DATA" , PARTITION "PART_06" VALUES LESS THAN (TO_DATE(' -06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "T10SPCSYS_DATA" , PARTITION "PART_07" VALUES LESS THAN (TO_DATE(' -07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "T10SPCSYS_DATA" , PARTITION "PART_08" VALUES LESS THAN (TO_DATE(' -08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "T10SPCSYS_DATA" , PARTITION "PART_09" VALUES LESS THAN (TO_DATE(' -09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "T10SPCSYS_DATA" , PARTITION "PART_10" VALUES LESS THAN (TO_DATE(' -10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "T10SPCSYS_DATA" , PARTITION "PART_11" VALUES LESS THAN (TO_DATE(' -11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "T10SPCSYS_DATA" ,PARTITION "PART_12" VALUES LESS THAN (TO_DATE(' -12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "T10SPCSYS_DATA" ) ;--2.建索引create index VALUE_ID_INDEX on SPC_ALARM_RULE_T_A(VALUE_ID) local TABLESPACE "T10SPCSYS_DATA" ;--3.将旧表的数据插入到新分区表insert into SPC_ALARM_RULE_T_A select * from SPC_ALARM_RULE_T;--4.重命名表RENAME "SPC_ALARM_RULE_T" TO "SPC_ALARM_RULE_T_BK";RENAME "SPC_ALARM_RULE_T_A" TO "SPC_ALARM_RULE_T";
7.把分区表的非分区索引重建成local索引，sql拼接生成
--注意，如果有一个索引对应多个字段的索引，需要结合listagg拼接SELECT'alter table ' || a.TABLE_NAME || ' drop constraint ' || b.index_name || ' ;' AS drop_cons,'drop index ' || b.index_name || ' ;' AS drop_INDEX,'create index ' || REPLACE(a.TABLE_NAME, '_T', '') || '_' || REPLACE(b.column_name, '_', '')|| '_IDX on ' || a.TABLE_NAME || '(' || b.column_name || ')' || 'LOCAL tablespace T10SPCSYS_INDEX online;' AS CREATE_indxFROMuser_indexes a ,user_ind_columns bWHEREa.table_name = b.table_nameAND a.index_name = b.index_nameAND b.INDEX_NAME IN (SELECT c.index_nameFROMuser_indexes cWHEREtable_name IN (SELECT DISTINCT table_nameFROMuser_tab_partitions)AND PARTITIONED = 'NO') ;
