[Oracle Script] check object count by user
SELECT u.username,
SUM(DECODE(o.object_type, 'INDEX', 1)) "INDEX",
SUM(DECODE(o.object_type, 'TABLE', 1)) "TABLE",
SUM(DECODE(o.object_type, 'CLUSTER', 1)) "CLUSTER",
SUM(DECODE(o.object_type, 'VIEW', 1)) "VIEW",
SUM(DECODE(o.object_type, 'SYNONYM', 1)) "SYNONYM",
SUM(DECODE(o.object_type, 'SEQUENCE', 1)) "SEQUENCE",
SUM(DECODE(o.object_type, 'PROCEDURE', 1)) "PROCEDURE",
SUM(DECODE(o.object_type, 'FUNCTION', 1)) "FUNCTION",
SUM(DECODE(o.object_type, 'PACKAGE', 1)) "PACKAGE",
SUM(DECODE(o.object_type, 'PACKAGE BODY', 1)) "PACKAGE BODY",
SUM(DECODE(o.object_type, 'TRIGGER', 1)) "TRIGGER",
SUM(DECODE(o.object_type, 'TYPE', 1)) "TYPE",
SUM(DECODE(o.object_type, 'TYPE BODY', 1)) "TYPE BODY",
SUM(DECODE(o.object_type, 'TABLE PARTITION', 1)) "TABLE PARTITION",
SUM(DECODE(o.object_type, 'INDEX PARTITION', 1)) "INDEX PARTITION",
SUM(DECODE(o.object_type, 'LOB', 1)) "LOB",
SUM(DECODE(o.object_type, 'LIBRARY', 1)) "LIBRARY",
SUM(DECODE(o.object_type, 'DIRECTORY', 1)) "DIRECTORY",
SUM(DECODE(o.object_type, 'JAVA SOURCE', 1)) "JAVA SOURCE",
SUM(DECODE(o.object_type, 'JAVA CLASS', 1)) "JAVA CLASS",
SUM(DECODE(o.object_type, 'JAVA RESOURCE', 1)) "JAVA RESOURCE",
SUM(DECODE(o.object_type, 'INDEXTYPE', 1)) "INDEXTYPE",
SUM(DECODE(o.object_type, 'OPERATOR', 1)) "OPERATOR",
SUM(DECODE(o.object_type, 'RESOURCE PLAN', 1)) "RESOURCE PLAN",
SUM(DECODE(o.object_type, 'CONSUMER GROUP', 1)) "CONSUMER GROUP",
SUM(DECODE(o.object_type, 'JAVA DATA', 1)) "JAVA DATA"
FROM dba_objects o, dba_users u
WHERE o.owner = u.username
GROUP BY u.username
SUM(DECODE(o.object_type, 'INDEX', 1)) "INDEX",
SUM(DECODE(o.object_type, 'TABLE', 1)) "TABLE",
SUM(DECODE(o.object_type, 'CLUSTER', 1)) "CLUSTER",
SUM(DECODE(o.object_type, 'VIEW', 1)) "VIEW",
SUM(DECODE(o.object_type, 'SYNONYM', 1)) "SYNONYM",
SUM(DECODE(o.object_type, 'SEQUENCE', 1)) "SEQUENCE",
SUM(DECODE(o.object_type, 'PROCEDURE', 1)) "PROCEDURE",
SUM(DECODE(o.object_type, 'FUNCTION', 1)) "FUNCTION",
SUM(DECODE(o.object_type, 'PACKAGE', 1)) "PACKAGE",
SUM(DECODE(o.object_type, 'PACKAGE BODY', 1)) "PACKAGE BODY",
SUM(DECODE(o.object_type, 'TRIGGER', 1)) "TRIGGER",
SUM(DECODE(o.object_type, 'TYPE', 1)) "TYPE",
SUM(DECODE(o.object_type, 'TYPE BODY', 1)) "TYPE BODY",
SUM(DECODE(o.object_type, 'TABLE PARTITION', 1)) "TABLE PARTITION",
SUM(DECODE(o.object_type, 'INDEX PARTITION', 1)) "INDEX PARTITION",
SUM(DECODE(o.object_type, 'LOB', 1)) "LOB",
SUM(DECODE(o.object_type, 'LIBRARY', 1)) "LIBRARY",
SUM(DECODE(o.object_type, 'DIRECTORY', 1)) "DIRECTORY",
SUM(DECODE(o.object_type, 'JAVA SOURCE', 1)) "JAVA SOURCE",
SUM(DECODE(o.object_type, 'JAVA CLASS', 1)) "JAVA CLASS",
SUM(DECODE(o.object_type, 'JAVA RESOURCE', 1)) "JAVA RESOURCE",
SUM(DECODE(o.object_type, 'INDEXTYPE', 1)) "INDEXTYPE",
SUM(DECODE(o.object_type, 'OPERATOR', 1)) "OPERATOR",
SUM(DECODE(o.object_type, 'RESOURCE PLAN', 1)) "RESOURCE PLAN",
SUM(DECODE(o.object_type, 'CONSUMER GROUP', 1)) "CONSUMER GROUP",
SUM(DECODE(o.object_type, 'JAVA DATA', 1)) "JAVA DATA"
FROM dba_objects o, dba_users u
WHERE o.owner = u.username
GROUP BY u.username
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-2143139/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [Oracle Script] check userOracle
- [Oracle Script] check latchOracle
- [Oracle Script] check active sessionOracleSession
- [Oracle Script] check lock infoOracle
- [Oracle Script] check Literal SQLOracleSQL
- [Oracle Script] check tablespace usage infoOracle
- [Oracle Script] check temp tablespace usageOracle
- [Oracle Script] check File I/OOracle
- check ftp success scriptFTP
- Script to Show System and Object Privs for a User (Doc ID 1019508.6)Object
- script of check repair ASM DISKGROUPAIASM
- RAC User Equivalence Check FailedUIAI
- PRVF-4007 : User equivalence check failed for user "grid"UIAI
- ora-600 [rwoirw: check ret val] with count distinct and order by
- SCRIPT: Script to Generate object privilege GRANTS (Doc ID 1020176.6)Object
- oracle scriptOracle
- How to check why identical SQL Statements have high version countIDESQL
- [Oracle Script] LockOracle
- Oracle Database ScriptOracleDatabase
- oracle user$Oracle
- [Oracle Script] Top sqlOracleSQL
- Oracle kill session scriptOracleSession
- ORACLE常用Script(轉)Oracle
- ORACLE常用Script (轉)Oracle
- Script to Check for Foreign Key Locking Issues [ID 1019527.6]
- Check the transaction running in oracleOracle
- [Oracle Script] Log switch statusOracle
- [Oracle Script] latch holderOracle
- [Oracle Script] Temporary Sort UsageOracle
- [Oracle Script] Rollback Segment UsageOracle
- ORACLE WITH CHECK OPTION子句詳解Oracle
- Oracle OCP(28):USEROracle
- oracle user privsOracle
- 【oracle】user_tablesOracle
- [Oracle Script] select db parameterOracle
- [Oracle Script] ASM Disks Performance metricOracleASMORM
- [Oracle Script] Undo Usage Per statusOracle
- [Oracle Script] Undo Usage Per sessionOracleSession