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 查看该表空间的使⽤情况
select substr(a.tablespace_name,1,20) tablespace,
round(al1)/1024/1024, 1) Total,
round(al1)/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(al1)/1024/1024, 1),1) pct_free,
round(sum(a.maxb)/1024/1024, 1) largest,
max(at) 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.log select 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 查看表空间使⽤信息，基本上符合我们的要求了树林的作用
将⽤户的默认表空间设置为我们新建的表空间
ALTER USER ADHOC DEFAULT TABLESPACE ADHOC_DATA_BIGFILE;
附查看某⼀表空间下block的分布情况
select c.segment_name,c.block_id start_block_al 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;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论