oracle删除索引释放空间,oracle 索引迁移，释放磁盘空间[通俗易懂]

2022-10-03 14:59:28 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

索引文件迁移步骤：

准备工作：

1)备份GBOS用户表索引：通过plsqlDevelop工具将GBOS用户表索引全部导出，以做备份。

1.查看索引表空间 具有那些数据文件

select file_id,file_name,tablespace_name,bytes/1024/1024 M,blocks from dba_data_files

where TABLESPACE_NAME=’USERINDEX’order by 1;

FILE_ID FILE_NAME TABLESPACE M BLOCKS

——— ————————————————– ———- ———- ———-

19 D:ORACLEORADATAINNETDBUSERINDEX01.DBF USERINDEX 10240 1310720

20 D:ORACLEORADATAINNETDBUSERINDEX02.DBF USERINDEX 10240 1310720

21 D:ORACLEORADATAINNETDBUSERINDEX03.DBF USERINDEX 10240 1310720

22 D:ORACLEORADATAINNETDBUSERINDEX04.DBF USERINDEX 10240 1310720

23 D:ORACLEORADATAINNETDBUSERINDEX05.DBF USERINDEX 10240 1310720

39 D:ORACLEORADATAINNETDBUSERINDEX06.DBF USERINDEX 6500 832000

40 D:ORACLEORADATAINNETDBUSERINDEX07.DBF USERINDEX 6500 832000

41 D:ORACLEORADATAINNETDBUSERINDEX08.DBF USERINDEX 6500 832000

42 D:ORACLEORADATAINNETDBUSERINDEX09.DBF USERINDEX 6300 806400

43 D:ORACLEORADATAINNETDBUSERINDEX10.DBF USERINDEX 6300 806400

62 D:ORACLEORADATAINNETDBUSERINDEX11.DBF USERINDEX 1400 179200

63 D:ORACLEORADATAINNETDBUSERINDEX12.DBF USERINDEX 1400 179200

2.创建新的索引表空间，添加索引数据文件

CREATE SMALLFILE TABLESPACE “INDEXTBS” DATAFILE ‘D:ORACLEORADATAINNETDBINDEXTBSINDEX001.DBF’

SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ALTER TABLESPACE “INDEXTBS” ADD DATAFILE ‘D:ORACLEORADATAINNETDBINDEXTBSINDEX003.DBF’

SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 4000M ;

3.查找属于gbos用户的表索引，(除去clob类型的索引,clob索引存放在user表空间)

(分区索引存放在dba_segments表里，普通索引信息放在dba_indexes表里)

SELECT ‘alter index gbos.’||index_name||’ rebuild tablespace INDEXTBS’

FROM dba_INDEXES T where t.table_owner=’GBOS’

and index_name not like ‘%SYS_%’

and t.tablespace_name=’USERINDEX’

SELECT ‘alter index gbos.’||index_name||’ rebuild tablespace INDEXTBS’

FROM dba_INDEXES T where t.tablespace_name=’USERINDEX’ and t.table_owner=’GBOS’

普通索引迁移

alter index gbos.INDEX_T_B_FAULT_LIST_STATUS rebuild tablespace INDEXTBS;

alter index gbos.INDEX_T_B_FAULT_L_OCCUR_TIME rebuild tablespace INDEXTBS;

alter index gbos.INDEX_T_B_FAULT_L_TERMINALID rebuild tablespace INDEXTBS;

alter index gbos.IDX_CAR_INFO_COL rebuild tablespace INDEXTBS;

alter index gbos.IX_T_O_OPEN_DOOR_REC_CLCT_DATE rebuild tablespace INDEXTBS;

alter index gbos.IX_T_O_OPEN_DOOR_REC_TERM_ID rebuild tablespace INDEXTBS;

alter index gbos.IX_T_O_OPEN_DOOR_REC_TICK rebuild tablespace INDEXTBS;

分区索引迁移

Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P61 tablespace INDEXTBS;

Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P62 tablespace INDEXTBS;

Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P63 tablespace INDEXTBS;

Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P64 tablespace INDEXTBS;

Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P65 tablespace INDEXTBS;

Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P66 tablespace INDEXTBS;

Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P67 tablespace INDEXTBS;

Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P68 tablespace INDEXTBS;

Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P69 tablespace INDEXTBS;

Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P70 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P41 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P42 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P43 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P44 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P45 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P46 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P47 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P48 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P49 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P50 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P51 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P52 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P53 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P54 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P55 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P56 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P57 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P58 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P59 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P60 tablespace INDEXTBS;

4.执行以上语句将索引从USERINDEX空间迁移到INDEXTBS空间。

5.将索引空间数据文件脱机(非归档模式下 需要drop)

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX01.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX02.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX03.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX04.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX05.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX06.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX07.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX08.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX09.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX10.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX11.DBF’ offline;

alter database datafile ‘D:ORACLEORADATAINNETDBUSERINDEX12.DBF’ offline;

6.删除USERINDEX表空间及相关联文件，再观察磁盘是否是否空间

drop tablespace USERINDEX

including contents and datafiles cascade constraints;

7.编译oracle相关服务是否有存储错误，用gbos账号登陆 是否能正常查询表，

启动相关服务。

注意：分别查看表索引对应的数据文件

select TABLE_NAME,SEGMENT_NAME,TABLESPACE_NAME,INDEX_NAME from dba_lobs where OWNER=’GBOS’;

select header_file,segment_name,tablespace_name,header_block from dba_segments

where segment_name=’SYS_C0036848′;

select INDEX_NAME,TABLE_NAME from dba_indexes where owner=’GBOS’ ;

select header_file,segment_name,tablespace_name,header_block from dba_segments

where segment_name=’T_O_MENUSET_PK’;

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/197353.html原文链接：https://javaforall.cn

oracle 数据库 sql html

0 人点赞