合 Oracle查询前几张大表
👉 本文共约206个字，系统预计阅读时间或需1分钟。
12c之前查询前几张大表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | 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之前按照表空间查询前几张大表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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; |
例如：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
很实用