空间收缩脚本

/*查下哪些表空间需要收缩*/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