Space SQL

-- tablespace size
select segment_name,sum(bytes) sumbytes
from dba_extents
where tablespace_name=''
group by segment_name
order by sumbytes desc;

-- shink simulation
select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest,
ceil( blocks*8192/1024/1024) currsize,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+) and file_name like '%%';

-- find high hwm objects
select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_id = ) --用你的DATAFILE代替
order by block_id desc
)
where rownum <= 5

-- allow modify system bootstroop objects
alter system set EVENT="38003 trace name context forever, level 10" 2 SCOPE=SPFILE;