12c之前查询前几张大表
col OLD_SEGMENT_NAME format a30
col segment_name format a30
select * from (
SELECT b.owner,
b.segment_name,
b.segment_type,
trunc(sum(b.bytes)/1024/1024/1024) size_G
FROM dba_segments b
group by b.owner,b.segment_name,b.segment_type
order by sum(b.bytes) desc)
where rownum<=10;
-- 显示lob的原表
select * from (
SELECT b.owner,
b.segment_name,
case when b.segment_type in ('LOBSEGMENT','LOBINDEX') then
(select nb.TABLE_NAME from DBA_LOBS nb where nb.SEGMENT_NAME=b.segment_name )
else b.segment_name end old_segment_name,
b.segment_type,
trunc(sum(b.bytes)/1024/1024/1024) size_G
FROM dba_segments b
group by b.owner,b.segment_name,b.segment_type
order by sum(b.bytes) desc)
where rownum<=10;
SELECT a.owner,
a.segment_name,
a.partition_name,
a.segment_type,
a.tablespace_name,
trunc(a.bytes/1024/1024/1024) size_G,
a.extents
FROM (SELECT b.owner,
b.segment_name,
b.partition_name,
b.segment_type,
b.tablespace_name,
b.bytes,
b.extents
FROM dba_segments b
WHERE b.owner NOT IN ('SYS','SYSTEM','PUBLIC','MDSYS','TSMSYS','DMSYS','DBSNMP','SCOTT','LHR','LHR2','DB_MONITOR','OUTLN','MGMT_VIEW','FLOWS_FILES','ORDSYS','EXFSYS','WMSYS','APPQOSSYS','APEX_030200','APEX_050000','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','HR','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','APEX_040200','DVSYS','LBACSYS','GSMADMIN_INTERNAL','AUDSYS','OJVMSYS','SYS$UMF','GGSYS','DBSFWUSER','DVF','GSMCATUSER','SYSBACKUP','REMOTE_SCHEDULER_AGENT','GSMUSER','SYSRAC','SYSKM','SYSDG','PDBADMIN','WKSYS','GSMROOTUSER','CSMIG','WKPROXY','WK_TEST','SI_INFORMATN_SCHEMA')
ORDER BY b.bytes desc) a
WHERE ROWNUM <= 10;
12c之前按照表空间查询前几张大表
col owner format a15
col segment_name format a30
col partition_name format a20
col tablespace_name format a10
SELECT a.owner,
a.segment_name,
a.partition_name,
a.segment_type,
a.tablespace_name,
round(a.bytes/1024/1024,2) size_m,
(bytes) segments_size,
a.extents
FROM (SELECT b.owner,
b.segment_name,
b.partition_name,
b.segment_type,
b.tablespace_name,
b.bytes,
b.extents,
DENSE_RANK() over(partition by b.tablespace_name ORDER BY b.bytes desc) rank_order
FROM dba_segments b
WHERE b.BYTES > 10
AND b.tablespace_name NOT LIKE 'UNDO%'
AND b.segment_name not in (SELECT nr.object_name FROM dba_recyclebin nr) ) a
WHERE rank_order <= 3
ORDER BY a.tablespace_name, a.bytes desc, a.owner;
例如：
SYS@lhrdb1> col owner format a15
SYS@lhrdb1> col segment_name format a30
SYS@lhrdb1> col partition_name format a20
SYS@lhrdb1> col tablespace_name format a10
SYS@lhrdb1> SELECT a.owner,
2 a.segment_name,
3 a.partition_name,
4 a.segment_type,
5 a.tablespace_name,
6 round(a.bytes/1024/1024,2) size_m,
7 (bytes) segments_size,
8 a.extents
9 FROM (SELECT b.owner,
10 b.segment_name,
11 b.partition_name,
12 b.segment_type,
13 b.tablespace_name,
14 b.bytes,
15 b.extents,
16 DENSE_RANK() over(partition by b.tablespace_name ORDER BY b.bytes desc) rank_order
17 FROM dba_segments b
18 WHERE b.BYTES > 10
19 AND b.tablespace_name NOT LIKE 'UNDO%'
20 AND b.segment_name not in (SELECT nr.object_name FROM dba_recyclebin nr) ) a
21 WHERE rank_order <= 3
22 ORDER BY a.tablespace_name, a.bytes desc, a.owner;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE SIZE_M SEGMENTS_SIZE EXTENTS
--------------- ------------------------------ -------------------- ------------------------------------ ---------- ---------- ------------- ----------
SYS SYS_LOB0000007350C00005$$ LOBSEGMENT SYSAUX 88.19 92471296 69
MDSYS SYS_LOB0000067470C00006$$ LOBSEGMENT SYSAUX 50.19 52625408 52
SYS SYS_LOB0000069706C00004$$ LOBSEGMENT SYSAUX 17.19 18022400 19
SYS IDL_UB1$ TABLE SYSTEM 336 352321536 113
SYS SOURCE$ TABLE SYSTEM 36 37748736 51
SYS C_TOID_VERSION# CLUSTER SYSTEM 34 35651584 49
6 rows selected.12c查询前几张大表
SELECT CON_ID,a.owner,
a.segment_name,
a.partition_name,
a.segment_type,
a.tablespace_name,
round(a.bytes/1024/1024,2) size_m,
(bytes) segments_size,
a.extents
FROM (SELECT b.CON_ID, b.owner,
b.segment_name,
b.partition_name,
b.segment_type,
b.tablespace_name,
b.bytes,
b.extents,
DENSE_RANK() over(partition by b.tablespace_name ORDER BY b.bytes desc) rank_order
FROM cdb_segments b
WHERE b.BYTES > 10*1024*1024
AND b.tablespace_name NOT LIKE 'UNDO%'
AND b.owner NOT IN ('SYS','SYSTEM','PUBLIC','MDSYS','DBSNMP','SCOTT','LHR','LHR2','DB_MONITOR','OUTLN','MGMT_VIEW','FLOWS_FILES','ORDSYS','EXFSYS','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','HR','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','APEX_040200','DVSYS','LBACSYS','GSMADMIN_INTERNAL','AUDSYS','OJVMSYS','SYS$UMF','GGSYS','DBSFWUSER','DVF','GSMCATUSER','SYSBACKUP','REMOTE_SCHEDULER_AGENT','GSMUSER','SYSRAC','SYSKM','SYSDG')
AND b.segment_name not in (SELECT nr.object_name FROM cdb_recyclebin nr) ) a
WHERE rank_order <= 3
ORDER BY CON_ID,a.tablespace_name, a.bytes desc, a.owner;
