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', 1, 0)) "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', 1, 0)) "index", sum(decode(object_type, 'SYNONYM', 1, 0)) "synonym", sum(decode(object_type, 'SEQUENCE', 1, 0)) "sequence", sum(decode(object_type, 'VIEW', 1, 0)) "view", sum(decode(object_type, 'CLUSTER', 1, 0)) "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 |
참조 :
'ORACLE > Sql' 카테고리의 다른 글
dba_jobs 로 실패한 job 확인 (0) | 2018.12.06 |
---|---|
오라클 세션수 및 프로세스수 변경하기 (4) | 2018.12.06 |
oracle awr 스냅샷 snapshot 확인 및 주기 변경 (0) | 2018.11.16 |
oracle 지난달, 다음달 구하기 (0) | 2018.11.08 |
Library Cache Lock , Pin 조회 테스트+킬구문포함 (0) | 2018.10.26 |