檢查表的數據量
查詢Schema為'EHEALTH','EHC','HHCARE','HAMSTC'中包含LOB類型的表的數據量
select 'select count(*) from '||owner||'.'||table_name||';'from dba_tab_columns where data_type like '%LOB%' and owner in ('EHEALTH','EHC','HHCARE','HAMSTC') order by owner
查詢BLOB的大小:
select a.owner,
a.segment_name,
decode (a.partition_name,
null, a.segment_name,
a.segment_name || ':' || a.partition_name) objectname,
segment_type objecttype,
nvl ((a.bytes / 1024 / 1024)||'M', 0) "SIZE",
b.table_name,
b.column_name,
a.tablespace_name
from (select * from dba_segments ) a,
(select * from sys.dba_lobs ) b
where (a.segment_name=b.segment_name or a.segment_name=b.index_name) --and b.table_name = 'DBS_SO_ATTACHMENT'
and a.owner not in ('CTXSYS','DBSNMP','DMSYS','EXFSYS','LBACSYS','MDSYS',
'OLAPSYS','ORDSYS','OUTLN','TSMSYS','SPOT','SYS','SYSTEM','SYSMAN','WMSYS','XDB')
order by a.owner,b.table_name,b.column_name
select 'select count(*) from '||owner||'.'||table_name||';'from dba_tab_columns where data_type like '%LOB%' and owner in ('EHEALTH','EHC','HHCARE','HAMSTC') order by owner
查詢BLOB的大小:
select a.owner,
a.segment_name,
decode (a.partition_name,
null, a.segment_name,
a.segment_name || ':' || a.partition_name) objectname,
segment_type objecttype,
nvl ((a.bytes / 1024 / 1024)||'M', 0) "SIZE",
b.table_name,
b.column_name,
a.tablespace_name
from (select * from dba_segments ) a,
(select * from sys.dba_lobs ) b
where (a.segment_name=b.segment_name or a.segment_name=b.index_name) --and b.table_name = 'DBS_SO_ATTACHMENT'
and a.owner not in ('CTXSYS','DBSNMP','DMSYS','EXFSYS','LBACSYS','MDSYS',
'OLAPSYS','ORDSYS','OUTLN','TSMSYS','SPOT','SYS','SYSTEM','SYSMAN','WMSYS','XDB')
order by a.owner,b.table_name,b.column_name
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25198367/viewspace-704253/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
檢查所有失效的物件
下一篇：
檢查包含LOB類型的表
请登录后发表评论
登录
全部评论
