select owner,table_name,object_type, count(1) num_obj
from (
select e.owner, nvl(nvl(t.table_name,l.table_name),i.table_name) table_name,
case
when t.table_name is not null then 'TABLE'
when l.table_name is not null then 'LOB'
when i.table_name is not null then 'INDEX'
else
null
end object_type
from dba_extents e,
dba_tables t,
dba_lobs l,
dba_indexes i
where e.owner = t.owner(+)
and e.segment_name = t.table_name(+)
and e.owner = l.owner(+)
and e.segment_name = l.segment_name(+)
and e.owner = i.owner(+)
and e.segment_name = i.index_name(+)
and e.block_id > 1966080
and e.file_id in (data_file_id_list of the tablespace)
)
group by owner, table_name, object_type
;
上述SQL根据提供的数据文件ID查询占用BLOCK大于15GB位置的对象。
2. 收缩表和Lob字段
收缩表
alter table table_name enable row movement;
alter table table_name shrink space compact;
alter table table_name shrink space;
收缩Lob字段
alter table table_name modify lob (lob_column_name) (shrink space cascade);
3. 查询每个数据文件可回收的空间
SELECT a.tablespace_name,
a.file_id,
a.file_name,
CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) smallest,
CEIL(blocks * blksize / 1024 / 1024) currsize,
CEIL(blocks * blksize / 1024 / 1024) -
CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) savings
--,'alter database datafile ''' || file_name || ''' resize ' ||
--CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) || 'm;' cmd
FROM DBA_DATA_FILES a,
(SELECT file_id, MAX(block_id + blocks - 1) hwm
FROM DBA_EXTENTS
GROUP BY file_id) b,
(SELECT TO_NUMBER(value) blksize
FROM V$PARAMETER
WHERE name = 'db_block_size')
WHERE a.file_id = b.file_id(+)
AND CEIL(blocks * blksize / 1024 / 1024) -
CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) > 0
AND TABLESPACE_NAME = 'USERS'
ORDER BY 1, 2 desc;