Oracle收缩表空间

前几天做了一个表空间收缩的case,今天把大概步骤记录一下,

查看该表空间的物理文件信息

select u.USERNAME,u.ACCOUNT_STATUS,f.TABLESPACE_NAME,f.FILE_ID,f.FILE_NAME,(f.BYTES/1024/1024/1024) FILE_SIZE(G) from dba_users u, dba_data_files f where u.DEFAULT_TABLESPACE=f.TABLESPACE_NAME and f.TABLESPACE_NAME='ADHOC_BIGFILE';

查看该表空间的使用情况

select substr(a.tablespace_name,1,20) tablespace,
round(sum(a.total1)/1024/1024, 1) Total,
round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1)
used,
round(sum(a.sum1)/1024/1024, 1) free,
round(round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1),1) pct_free,
round(sum(a.maxb)/1024/1024, 1) largest,
max(a.cnt) fragments
from
(select tablespace_name, 0 total1, sum(bytes) sum1,
max(bytes) MAXB,
count(bytes) cnt
from dba_free_space
group by tablespace_name
union
select tablespace_name, sum(bytes) total1, 0, 0, 0
from dba_data_files
group by tablespace_name
) a
group by a.tablespace_name;

该表空间实际物理文件大概1.4T,但是实际上由于空间碎片化严重,实际上并没有这么多的使用,研发给了一个删除列表,我们删除一些表数据之后再次查看该表空间使用信息

可以看到即使空闲空间增大了,但是TOTAL大小并没有因此减小

我们再看通过resize可以将数据文件减至多大

select a.TABLESPACE_NAME,a.FILE_ID,a.FILE_NAME,(a.BYTES/1024/1024) as "FILE_SIZE(MB)",b.tb_size as "TABLESPACE_SIZE(MB)",((a.BYTES/1024/1024)-b.tb_size) as "RELEASE_SIZE" from dba_data_files a,
(select TABLESPACE_NAME,round(max(BLOCK_ID)*16384/1024/1024) tb_size from dba_extents group by TABLESPACE_NAME) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME order by a.FILE_ID;

可以看到仅仅通过resize只能将物理文件减少了22408M大小,不符合我们碎片整理的要求,我们通过move表的形式来将表空间缩小.

先创建一个新的表空间

CREATE BIGFILE TABLESPACE ADHOC_DATA_BIGFILE DATAFILE 
  '/db02/oradata/FINMART/adhoc_data_bigfile01.dbf' SIZE 500M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER USER ADHOC QUOTA UNLIMITED ON ADHOC_DATA_BIGFILE;

将数据段move至新的表空间,创建一个sql文件,用@调用即可

生成move数据段的语句

select 'alter table ' || owner || '.' || segment_name || ' move tablespace ADHOC_DATA_BIGFILE;' sqltext from dba_segments where tablespace_name = 'ADHOC_BIGFILE' and segment_type='TABLE';

set pagesize 9999 linesize 200
set echo on
set time on
set timing on
set feedback on
spool mv_adhoc_data_tbs.logselect count(*) from dba_tables where TABLESPACE_NAME='ADHOC_BIGFILE'and STATUS='VALID';
alter table ADHOC.USAGE_BYMONTH_SCOUT_FEB17_P move tablespace adhoc_data_bigfile;
alter table ADHOC.TEMP_SUBS_PROFILE_JAN17 move tablespace adhoc_data_bigfile;
alter table ADHOC.USAGE_BYMONTH_JAN17 move tablespace adhoc_data_bigfile;
alter table ADHOC.ENDING_NON_SCOUT_FEB17_P move tablespace adhoc_data_bigfile;
alter table ADHOC.ENDING_SCOUT_FEB17_P move tablespace adhoc_data_bigfile;
alter table ADHOC.SCOUT_USAGE_SUMMARY_201701 move tablespace adhoc_data_bigfile;
alter table ADHOC.SCOUT_PAID_USER_201701 move tablespace adhoc_data_bigfile;
alter table ADHOC.TEMP_SUBS_PROFILE_NON_SCOUT move tablespace adhoc_data_bigfile;
alter table ADHOC.SUBS_PROFILE_NON_SCOUT move tablespace adhoc_data_bigfile;
alter table ADHOC.TEMP_SUBS_PROFILE_SCOUT move tablespace adhoc_data_bigfile;
alter table ADHOC.TN_RECEIPT_PTN_201703 move tablespace adhoc_data_bigfile;
alter table ADHOC.ACTIVE_USER move tablespace adhoc_data_bigfile;
alter table ADHOC.ACTIVE_USER_FINANCE move tablespace adhoc_data_bigfile;
alter table ADHOC.USAGE_BYMONTH_MAY17 move tablespace adhoc_data_bigfile;
alter table ADHOC.ENDING_NON_SCOUT_PREV_1706 move tablespace adhoc_data_bigfile;
alter table ADHOC.ENDING_SCOUT_PREV_1706 move tablespace adhoc_data_bigfile;select count(*) from dba_tables where TABLESPACE_NAME='ADHOC_DATA_BIGFILE'and STATUS='VALID';
spool off
exit;

将索引段move至新的表空间,创建一个sql文件,用@调用即可

生成move索引段的语句

select 'alter index ' || owner || '.' || segment_name || ' rebuild tablespace ADHOC_DATA_BIGFILE;' sqltext from dba_segments where tablespace_name = 'ADHOC_BIGFILE' and segment_type='INDEX';

set pagesize 9999 linesize 200
set echo on
set time on
set timing on
set feedback on
spool mv_adhoc_index_tbs.log
select count(*) from dba_indexes where TABLESPACE_NAME='ADHOC_BIGFILE' and INDEX_TYPE='NORMAL';
alter index ADHOC.IDX_ENDING_PTN rebuild tablespace adhoc_data_bigfile;
alter index ADHOC.IDX_ENDING_PTN2 rebuild tablespace adhoc_data_bigfile;
alter index ADHOC.SYS_C0062583 rebuild tablespace adhoc_data_bigfile;
alter index ADHOC.IDX_PRDCD_SOC rebuild tablespace adhoc_data_bigfile;
alter index ADHOC.IDX_PD_2 rebuild tablespace adhoc_data_bigfile;
alter index ADHOC.IDX_SM_2 rebuild tablespace adhoc_data_bigfile;
alter index ADHOC.IDX_RPT_2 rebuild tablespace adhoc_data_bigfile;
alter index ADHOC.IDX_SM_3 rebuild tablespace adhoc_data_bigfile;
alter index ADHOC.IDX_RPT_3 rebuild tablespace adhoc_data_bigfile;
alter index ADHOC.SYS_C0062450 rebuild tablespace adhoc_data_bigfile;
alter index ADHOC.SYS_C0062452 rebuild tablespace adhoc_data_bigfile;
select count(*) from dba_indexes where TABLESPACE_NAME='ADHOC_DATA_BIGFILE' and INDEX_TYPE='NORMAL' and STATUS='VALID';
spool off
exit;

将lob段move至新的表空间,创建一个sql文件,用@调用即可

生成move lob段的语句

select 'alter table ' || owner || '.' || table_name || ' move lob(' || column_name || ') store as(tablespace adhoc_data_bigfile);' sqltext from dba_lobs where tablespace_name = 'ADHOC_BIGFILE';

set pagesize 9999 linesize 200
set echo on
set time on
set timing on
set feedback on
spool mv_adhoc_lob_tbs.log
select count(*) from dba_indexes where TABLESPACE_NAME='ADHOC_BIGFILE' and INDEX_TYPE='LOB';
alter table ADHOC.DJANGO_ADMIN_LOG move lob(OBJECT_ID) store as(tablespace adhoc_data_bigfile);
alter table ADHOC.DJANGO_ADMIN_LOG move lob(CHANGE_MESSAGE) store as(tablespace adhoc_data_bigfile);
alter table ADHOC.DJANGO_SESSION move lob(SESSION_DATA) store as(tablespace adhoc_data_bigfile);
select count(*) from dba_indexes where TABLESPACE_NAME='ADHOC_DATA_BIGFILE' and INDEX_TYPE='LOB' and STATUS='VALID';
spool off
exit;

以上操作执行完成后,查看该表的物理文件信息

select u.USERNAME,u.ACCOUNT_STATUS,f.TABLESPACE_NAME,f.FILE_ID,f.FILE_NAME,(f.BYTES/1024/1024/1024) FILE_SIZE from dba_users u, dba_data_files f where u.DEFAULT_TABLESPACE=f.TABLESPACE_NAME and f.TABLESPACE_NAME='ADHOC_DATA_BIGFILE';

查看表空间使用信息,基本上符合我们的要求了

 

将用户的默认表空间设置为我们新建的表空间

ALTER USER ADHOC DEFAULT TABLESPACE ADHOC_DATA_BIGFILE;

 附查看某一表空间下block的分布情况

select c.segment_name,c.block_id start_block_id,d.total segment_length,(c.block_id + d.total) end_block_id from
(select segment_name,block_id from dba_extents where tablespace_name='REPMART_DATA_BIGFILE' and extent_id=0) c,
(select segment_name,sum(blocks) total from dba_extents where tablespace_name='REPMART_DATA_BIGFILE' group by segment_name) d
where c.segment_name=d.segment_name
order by c.block_id desc;