Oracle EBS 大表数据清理方法总结

--Reducing the Oracle E-Business Suite Data Footprint(Doc ID 752322.1)
set lines 160 pages 50000
col table_name for a30
col column_name for a30
col segment_name for a30
col segment_type for a15
col tablespace_name for a15
SELECT *
FROM (
	SELECT A.TABLE_NAME,
	A.COLUMN_NAME,
	B.SEGMENT_NAME,
	B.SEGMENT_TYPE,
	B.TABLESPACE_NAME,
	B.BYTES/1024/1024/1024 as SIZE_GB,
	B.BLOCKS,
	B.EXTENTS
	FROM DBA_LOBS A, DBA_SEGMENTS B
	WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
	ORDER BY B.BYTES DESC
)
WHERE ROWNUM <= 20;

set lines 160 pages 50000
col owner for a15
col segment_name for a30
col segment_type for a15
col tablespace_name for a15
SELECT *
FROM (
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024/1024 as SIZE_GB from dba_segments order by bytes desc
)
WHERE ROWNUM <= 20;

Oracle 大表数据删除或清理方法总结

Oracle表空间占用过大