프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux6.8(64bit)


DB 환경 : Oracle Database 11.2.0.4


쿼리 : 유저별 Object 조회쿼리

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
set line 200
set long 2000
set pagesize 2000
SELECT owner ow,
sum(decode(object_type, 'TABLE'10)) "table",
sum(case when object_type = 'FUNCTION' then 1 end) Func,
sum(case when object_type = 'PROCEDURE' then 1 end)  Proc,
sum(case when object_type = 'TRIGGER' then 1 end) Trig,
sum(decode(object_type, 'INDEX'10)) "index",
sum(decode(object_type, 'SYNONYM'10)) "synonym",
sum(decode(object_type, 'SEQUENCE'10)) "sequence",
sum(decode(object_type, 'VIEW'10)) "view",
sum(decode(object_type, 'CLUSTER'10)) "cluster"
FROM dba_objects
GROUP BY owner
HAVING owner not in ('SYS''SYSTEM''SCOTT''DBSNMP''OUTLN''WKPROXY''WMSYS''ORDSYS''ORDPLUGINS''MDSYS''CTXSYS''XDB''ANONYMOUS''OWNER''WKSYS''ODM_MTR''ODM''OLAPSYS''HR''OE''PM''SH''QS_ADM''QS''QS_WS''QS_ES''QS_OS''QS_CBADM''QS_CB''QS_CS''PERFSTAT''EXFSYS')
ORDER BY 1;



결과값 : 

1
2
3
4
5
6
7
8
9
OW          table       FUNC     PROC        TRIG      index    synonym     sequence    view    cluster
------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
APPQOSSYS          4 (null)       (null)     (null)          0         1        0       0          0
ISYOU              2 (null)       (null)     (null)          0         0        0       0          0
LOGNER              4 (null)       (null)     (null)          0         0        0       0          0
MGAUGE5           0 (null)       (null)     (null)          0         0        0       0          0
ORACLE_OCM          0 (null)       (null)     (null)          0         0        0       0          0
PUBLIC              0 (null)       (null)     (null)          0      3474        0       0          0
JSH              5 (null)       (null)     (null)          2         0        0       0          0



참조 :