OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4, 12.2.0.1
방법 : 오라클 11g, 12c 오브젝트 조회 쿼리(기본계정 제외)
싱글 11g r2 기준으로 ORACLE 기본계정을 제외하고 오브젝트별 카운트를 구하는 쿼리임
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | SQL> col OWNER for 20 SELECT OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE OWNER NOT IN ('SYSTEM','SYS','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS', 'WMSYS','EXFSYS','CTXSYS','XDB','ANONYMOUS','XS$NULL','ORDPLUGINS', 'ORDSYS','ORDDATA','SI_INFORMTN_SCHEMA','MDSYS','OLAPSYS','MDDATA', 'SPATIAL_WFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW', 'APEX_030200','FLOWS_FILES','APEX_PUBLIC_USER','OWBSYS','OWBSYS_AUDIT') AND OBJECT_NAME NOT LIKE 'BIN$%' GROUP BY OWNER, OBJECT_TYPE ORDER BY 1,2 / OWNER OBJECT_TYPE COUNT(*) ------------------------------ ------------------- ---------- PUBLIC SYNONYM 3451 SCOTT FUNCTION 4 SCOTT INDEX 251 SCOTT INDEX PARTITION 64 SCOTT LOB 23 SCOTT PACKAGE 1 SCOTT PACKAGE BODY 1 SCOTT PROCEDURE 1 SCOTT QUEUE 4 SCOTT SEQUENCE 20 SCOTT SYNONYM 8 SCOTT TABLE 164 SCOTT TABLE PARTITION 45 SCOTT TRIGGER 2 SCOTT TYPE 9 SCOTT VIEW 14 TEST PROCEDURE 1 17 rows selected. |
싱글 12g r2 기준으로 ORACLE 기본계정을 제외하고 오브젝트별 카운트를 구하는 쿼리임
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | SQL> col OWNER for 20 SELECT OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE OWNER NOT IN ('SYSTEM','SYS','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS', 'WMSYS','EXFSYS','CTXSYS','XDB','ANONYMOUS','XS$NULL','ORDPLUGINS', 'ORDSYS','ORDDATA','SI_INFORMTN_SCHEMA','MDSYS','OLAPSYS','MDDATA', 'SPATIAL_WFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW', 'APEX_030200','FLOWS_FILES','APEX_PUBLIC_USER','OWBSYS','OWBSYS_AUDIT', 'SYSDG','SYSKM','AUDSYS','SYSRAC','SYSBACKUP','GSMUSER', 'GSMADMIN_INTERNAL','REMOTE_SCHEDULER_AGENT','DBSFWUSER', 'SYS$UMF','GSMCATUSER','GGSYS') AND OBJECT_NAME NOT LIKE 'BIN$%' GROUP BY OWNER, OBJECT_TYPE ORDER BY 1,2 / OWNER OBJECT_TYPE COUNT(*) ------------------------------ ------------------- ---------- PUBLIC SYNONYM 3451 SCOTT FUNCTION 4 SCOTT INDEX 251 SCOTT INDEX PARTITION 64 SCOTT LOB 23 SCOTT PACKAGE 1 SCOTT PACKAGE BODY 1 SCOTT PROCEDURE 1 SCOTT QUEUE 4 SCOTT SEQUENCE 20 SCOTT SYNONYM 8 SCOTT TABLE 164 SCOTT TABLE PARTITION 45 SCOTT TRIGGER 2 SCOTT TYPE 9 SCOTT VIEW 14 TEST PROCEDURE 1 17 rows selected. |
참조 :
'ORACLE > Sql' 카테고리의 다른 글
| 오라클 유저의 인덱스와 코멘트 확인 쿼리 (0) | 2020.01.04 |
|---|---|
| 오라클 temp 할당량 및 실시간 사용량 확인 쿼리 (0) | 2020.01.03 |
| 함수기반 인덱스(FBI, Function-Based Index) 오름차순 내림차순 확인 (0) | 2019.02.19 |
| 부하 테스트용 샘플데이터 생성 dbms_random (0) | 2019.02.18 |
| 리터럴 sql 조회 쿼리 (0) | 2019.02.15 |