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

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') ;

最后

以上就是无语香烟为你收集整理的【oracle分区：分区表/分区索引查看，在线重建分区索引】的全部内容，希望文章能够帮你解决【oracle分区：分区表/分区索引查看，在线重建分区索引】所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。