【oracle分区：分区表/分区索引查看，在线重建分区索引】

csdnbkyhm

已于 2022-11-02 19:28:59 修改

阅读量2.7k

点赞数 1

分类专栏： DB 文章标签： oracle sql 数据库

于 2022-11-02 19:06:15 首次发布

本文链接：https://blog.csdn.net/csdnbkyhm/article/details/127656523

版权

DB 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

分区表/分区索引查看，在线重建分区索引

1.查看分区索引/普通索引

--1.查看所有普通索引
select *
from user_indexes a,user_tables b
where 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 c
where a.index_name = c.index_name
and c.table_name = b.table_name
and 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 t
where tablespace_name = 'T10SPCSYS_DATA'
--AND SEGMENT_NAME='UI_TASK_EXECRECORD_T';
ORDER BY t.BYTES/(1024*1024*1024) desc;

--5.查看账号空间使用率
SELECT
	fre.* ,
	use.* ,
	fre.free_g + use.use_g all_g,
	ROUND(use.use_g*100 /(fre.free_g + use.use_g), 2)|| '%' AS P_used
FROM
	(
		SELECT TABLESPACE_NAME,
		ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) FREE_g
	FROM
		SYS.user_FREE_SPACE
	GROUP BY
		TABLESPACE_NAME ) fre,
	(
		SELECT tablespace_name,
		SUM(BYTES)/(1024 * 1024 * 1024) use_g
	FROM
		user_segments
	GROUP BY
		tablespace_name ) use
WHERE
	use.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 local
SPC_RESULT_PK ;

4.增加/删除分区

ALTER TABLE SPC_RV_RECIVE_T ADD PARTITION "PART_202210"  VALUES LESS THAN (TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))；

ALTER TABLE SPC_RV_RECIVE_T DROP PARTITION PART_202210;

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_202202"  VALUES LESS THAN (TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))   TABLESPACE "M10SPCSYS_DATA" , 
    PARTITION "PART_202203"  VALUES LESS THAN (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE "M10SPCSYS_DATA" , 
	 PARTITION "PART_202204"  VALUES LESS THAN (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))   TABLESPACE "M10SPCSYS_DATA" , 
	 PARTITION "PART_202205"  VALUES LESS THAN (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE "M10SPCSYS_DATA" , 
	  PARTITION "PART_202206"  VALUES LESS THAN (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE "M10SPCSYS_DATA" , 
	  PARTITION "PART_202207"  VALUES LESS THAN (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE "M10SPCSYS_DATA" , 
	   PARTITION "PART_202208"  VALUES LESS THAN (TO_DATE(' 2022-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_202203"  VALUES LESS THAN (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))   TABLESPACE "T10SPCSYS_DATA" ,
PARTITION "PART_202204"  VALUES LESS THAN (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))   TABLESPACE "T10SPCSYS_DATA" ,
PARTITION "PART_202205"  VALUES LESS THAN (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))   TABLESPACE "T10SPCSYS_DATA" , 
PARTITION "PART_202206"  VALUES LESS THAN (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE "T10SPCSYS_DATA" , 
PARTITION "PART_202207"  VALUES LESS THAN (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))   TABLESPACE "T10SPCSYS_DATA" , 
PARTITION "PART_202208"  VALUES LESS THAN (TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE "T10SPCSYS_DATA" , 
PARTITION "PART_202209"  VALUES LESS THAN (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE "T10SPCSYS_DATA" , 
PARTITION "PART_202210"  VALUES LESS THAN (TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE "T10SPCSYS_DATA" , 
PARTITION "PART_202211"  VALUES LESS THAN (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  TABLESPACE "T10SPCSYS_DATA" ,
PARTITION "PART_202212"  VALUES LESS THAN (TO_DATE(' 2022-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_indx
FROM
	user_indexes a ,
	user_ind_columns b
WHERE
	a.table_name = b.table_name
	AND a.index_name = b.index_name
	AND b.INDEX_NAME IN (
		SELECT c.index_name
	FROM
		user_indexes c
	WHERE
		table_name IN (
			SELECT DISTINCT table_name
		FROM
			user_tab_partitions)
		AND PARTITIONED = 'NO') ;