/*查下哪些表空间需要收缩*/
select /*+ ordered use_hash(a,b,c) */
a.file_id,a.file_name,a.filesize, b.freesize, (a.filesize-b.freesize) usedsize, c.hwmsize, c.hwmsize - (a.filesize-b.freesize) unsedsize_belowhwm, a.filesize - c.hwmsize canshrinksize from ( select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files ) a, ( select file_id,round(sum(dfs.bytes)/1024/1024) freesize from dba_free_space dfs group by file_id ) b, ( select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c where a.file_id = b.file_id and a.file_id = c.file_id order by unsedsize_belowhwm desc /*查下表空间下对象情况*/
select * from dba_data_files t where t.tablespace_name='DC_DEF'; purge user_recyclebin; create tablespace dc_def_tmp ; alter user dcuser quota unlimited on dc_def_tmp; select count(*) from dba_segments s where s.tablespace_name='DC_DEF' and s.segment_type='TABLE';
select count(*) from dba_segments s where s.tablespace_name='DC_DEF' and s.segment_type='INDEX'; select count(*) from dba_segments s where s.tablespace_name='DC_DEF' and s.segment_type='TABLE PARTITION'; select count(*) from dba_segments s where s.tablespace_name='DC_DEF' and s.segment_type='INDEX PARTITION'; /*生成脚本*/
Select distinct 'alter table '|| SEGMENT_NAME||' move tablespace dc_def_tmp; ' FROM DBA_EXTENTS Where TABLESPACE_NAME='DC_DEF' and segment_type='TABLE' ;
select cname from ( select rownum rm,'alter table '||ds.owner||'.'||ds.segment_name||' move partition '||ds.partition_name||' tablespace DC_DEF_TMP;' cname from dba_segments ds where ds.tablespace_name ='DC_DEF' and ds.segment_type = 'TABLE PARTITION') c; Select distinct 'alter INDEX '|| SEGMENT_NAME||' REBUILD TABLESPACE DC_DEF_TMP; ' FROM DBA_EXTENTS Where TABLESPACE_NAME='DC_DEF' and segment_type='INDEX' ;
select 'alter INDEX '||ds.owner||'.'||ds.segment_name||' rebuild partition '||ds.partition_name||' tablespace dc_def_tmp;'
from dba_segments ds where ds.tablespace_name ='DC_DEF' and ds.segment_type = 'INDEX PARTITION'; /*查找LOB字段*/
select /*+use_hash(ds,dtc)*/
ds.tablespace_name,ds.owner||'.'||ds.segment_name,ds.segment_type, dtc.DATA_TYPE,dtc.COLUMN_NAME from dba_tab_columns dtc , dba_segments ds where dtc.TABLE_NAME = ds.segment_name and dtc.OWNER = ds.owner and ds.tablespace_name ='DC_DEF' and data_type = 'LONG'; select /*+use_hash(ds,dtc)*/
'alter table '||ds.owner||'.'||ds.segment_name||' modify '||dtc.COLUMN_NAME||' clob;' from dba_tab_columns dtc , dba_segments ds where dtc.TABLE_NAME = ds.segment_name and dtc.OWNER = ds.owner and ds.tablespace_name='DC_DEF' and data_type = 'LONG'; /*逆顺序脚本*/
select ds.tablespace_name,'alter table '||ds.owner||'.'||ds.segment_name||' move tablespace '||du.default_tablespace||';'
from dba_segments ds , dba_users du where ds.owner = du.username and ds.owner='DCUSER' and ds.tablespace_name = 'DC_DEF' and ds.segment_type = 'TABLE'; select cname from ( select rownum rm,'alter table '||ds.owner||'.'||ds.segment_name||' move partition '||ds.partition_name||' tablespace '||du.default_tablespace||';' cname from dba_segments ds , dba_users du where ds.owner = du.username and ds.owner='DCUSER' and ds.tablespace_name = 'DC_DEF' and ds.segment_type = 'TABLE PARTITION' ) c; select 'alter INDEX '||ds.owner||'.'||ds.segment_name||' rebuild tablespace '||nvl(dt.tablespace_name,du.default_tablespace)||';'
from dba_segments ds , dba_users du, dba_tablespaces dt where ds.owner = du.username and dt.tablespace_name(+) = du.username||'I' and ds.owner='DCUSER' and ds.tablespace_name = 'DC_DEF' and ds.segment_type = 'INDEX'; select 'alter INDEX '||ds.owner||'.'||ds.segment_name||' rebuild partition '||ds.partition_name||' tablespace ' ||nvl(dt.tablespace_name,du.default_tablespace)||';'
from dba_segments ds , dba_users du, dba_tablespaces dt where ds.owner = du.username and dt.tablespace_name(+) = du.username||'I' and ds.owner='DCUSER' and ds.tablespace_name = 'DC_DEF' and ds.segment_type = 'INDEX PARTITION'; select * from dba_users u where u.username='DCUSER'; select * from dba_data_files f where f.tablespace_name='DC_DEF'; alter database datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\DC_DEF.ORA' resize 200m; select e.block_id,e.segment_name from dba_extents e where e.tablespace_name='DC_DEF'; select max(e.block_id) from dba_extents e where e.tablespace_name='DC_DEF'; select 23305*8/1024 from dual; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ select f.file_name,f.bytes/1024/1024 from dba_data_files f where f.tablespace_name='DC_DEF'; select * from dba_segments s where s.tablespace_name='DC_DEF_TMP'; select i.index_name,i.index_type,i.table_owner,i.table_name,i.status from dba_indexes i where i.tablespace_name='DC_DEF'; select count(*) from dba_segments s where s.tablespace_name='DC_DEF' and s.segment_type='TABLE'; select count(*) from dba_segments s where s.tablespace_name='DC_DEF' and s.segment_type='INDEX'; select count(*) from dba_segments s where s.tablespace_name='DC_DEF' and s.segment_type='TABLE PARTITION'; select count(*) from dba_segments s where s.tablespace_name='DC_DEF' and s.segment_type='INDEX PARTITION'; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/*查询收缩完后空间可resize的大小*/
select /*+ ordered use_hash(a,c) */
'alter database datafile '''||a.file_name||''' resize ' ||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;', a.filesize, c.hwmsize from ( select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files ) a, ( select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c where a.file_id = c.file_id and a.filesize - c.hwmsize > 100 |
|