一个sql查询各个owner下的一些object数目
select owner, object_type, count(*)
from dba_objects
where owner not in ('SYSTEM','SYS','DBSNMP','OUTLN','WMSYS','SCOTT')
group by owner,object_type
显示如下:
OWNER OBJECT_TYPE COUNT(*)
CJ7 LOB 25
CJ7 INDEX 197
CJ7 TABLE 178
CJ7 PACKAGE 4
CJ7 FUNCTION 7
CJ7 SEQUENCE 3
CJ7 PROCEDURE 16
DSD LOB 26
DSD VIEW 4
.....
现在要将它显示成下面这个形式:
OWNER LOB VIEW INDEX TABLE PACKAGE FUNCTION SEQUEBCE PROCEDURE
CJ7 25 0 197 178 4 7 3 16
DSD 26 4
-------------------------------------------------------------------------------
select owner,max(decode(object_type,'LOB',cn,null)) LOB1,
max(decode(object_type,'VIEW',cn,null)) VIEW1,
max(decode(object_type,'INDEX',cn,null))INDEX1,
max(decode(object_type,'TABLE',cn,null)) TABLE1,
max(decode(object_type,'PACKAGE',cn,null)) PACKAGE1,
max(decode(object_type,'FUNCTION',cn,null)) FUNCTION1,
max(decode(object_type,'SEQUENCE',cn,null)) SEQUENCE1,
max(decode(object_type,'PROCEDURE',cn,null)) PROCEDURE1
from
(select owner, object_type, count(*) cnfrom dba_objects group by owner,object_type
)
where owner not in ('SYSTEM','SYS','DBSNMP','OUTLN','WMSYS','SCOTT')
GROUP BY owner
执行结果:
1 BI
2 CTXSYS 1 54 46 37 71 5 3 3
3 DMSYS 2 71 39 43 37 12 10 1
4 EXFSYS 32 13 15 8 17 1 6
5 FENGJIN 1 3
6 FENGJIN_LINK 1 3 8
7 HR 1 19 7 3 2
8 IX 3 6 12 14 2
9 MDSYS 17 31 31 35 39 51 6 1
10 OE 15 14 28 13 1 1
11 OLAPSYS 2 296 129 126 47 1 5
12 ORDPLUGINS 19
13 ORDSYS 5 4 4 17 34 9
14 PM 17 3 3
15 PUBLIC
16 RMAN 30 81 34 2 1 1
17 SH 2 1 27 17
18 SI_INFORMTN_SCHEMA
19 SYSMAN 28 118 376 326 66 8 5 2
20 TEST1 5
21 WKSYS 73 74 53 21 3 25 5
22 WK_TEST 2 6 22 13 3
23 XDB 332 2 40 31 20 4 2 5