如何在一个查询中对多个表，大小和表空间进行计数(*) [英] How to count(*) of multiple tables, size and tablespace in one query

问题描述

我有2个工作查询，输出正确. 第一个查询给出表名和计数的列表， 第二个查询提供了表名，大小(以MB为单位)和表空间名的列表.

I have 2 working queries with correct output. First query gives list of table names and count, second query gives list of table names, size in MB and tablespace name.

如何将它们组合成一个查询?

How to combine them to one query ?

查询1:

select table_name
,to_number(extractvalue(xmltype(
dbms_xmlgen.getxml(
'select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables 
where table_name like 'AUD_2012080%'
order by table_name desc;

查询2:

select segment_name,blocks*8192/1024/1024 as MB,tablespace_name 
from dba_segments 
where segment_name like 'AUD_2012080%' 
order by segment_name desc;

输出1:

TABLE_NAME                          COUNT
------------------------------ ----------
AUD_20120809                        27967 
AUD_20120808                        28269 
AUD_20120807                        29354 
AUD_20120806                        27155 
AUD_20120805                        18042 
AUD_20120804                        19033 
AUD_20120803                        24642 
AUD_20120802                        26760 
AUD_20120801                        25776 

输出2:

SEGMENT_NAME                 MB TABLESPACE_NAME              
-------------------- ---------- ------------------------------
AUD_20120809                 35 WSS_BIG_I                      
AUD_20120808                 35 WSS_BIG_I                      
AUD_20120807                 36 WSS_BIG_I                      
AUD_20120806                 34 WSS_BIG_I                      
AUD_20120805                 24 WSS_BIG_I                      
AUD_20120804                 24 WSS_BIG_I                      
AUD_20120803                 29 WSS_BIG_I                      
AUD_20120802                 32 WSS_BIG_I                      
AUD_20120801                 30 WSS_BIG_I  

我正在寻找的结果是:

Table_name    count   owner    MB   Tablespace_name
AUD_20120801  25776   auditor  30   wss_big_i 
...
...  

推荐答案

该段通常像表一样命名:

As the segment is usually named like the table:

SELECT ut.table_name,
       to_number(extractvalue(xmltype (dbms_xmlgen.getxml ('select count(*) c from ' ||ut.table_name)),'/ROWSET/ROW/C')) row_count,
       db.blocks*8192/1024/1024 as MB,
       db.tablespace_name 
FROM user_tables ut
  join dba_segments db on db.segment_name = ut.table_name
WHERE ut.table_name LIKE 'AUD_2012080%'
ORDER BY ut.table_name DESC

这篇关于如何在一个查询中对多个表，大小和表空间进行计数(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！