Oracle Object summary on schema
Oracle Object list user wise
Oracle Object summary on schema
Object summary on user wise set pagesize 10000 wrap off linesize 200 heading on tab on scan on verify off column tab format 9999 heading "tab" column ind format 9999 heading "ind" column syn format 9999 heading "syn" column vew format 9999 heading "vew" column seq format 9999 heading "seq" column prc format 9999 heading "prc" column fun format 9999 heading "fun" column pck format 9999 heading "pck" column trg format 9999 heading "trg" column dep format 9999 heading "dep" spool list_objects_by_user.lst ttitle 'Object count by user' - skip 2 select substr(username,1,10) "user", count(decode(o.type#, 2, o.obj#, '')) tab, count(decode(o.type#, 1, o.obj#, '')) ind, count(decode(o.type#, 5, o.obj#, '')) syn, count(decode(o.type#, 4, o.obj#, '')) vew, count(decode(o.type#, 6, o.obj#, '')) seq, count(decode(o.type#, 7, o.obj#, '')) prc, count(decode(o.type#, 8, o.obj#, '')) fun, count(decode(o.type#, 9, o.obj#, '')) pck, count(decode(o.type#,12, o.obj#, '')) trg, count(decode(o.type#,10, o.obj#, '')) dep from sys.obj$ o, sys.dba_users u where u.user_id = o.owner# (+) and o.type# > 0 group by username order by 1; spool off set feed on echo off termout on pages 24 verify on @obj.sql Object count by user user tab ind syn vew seq prc fun pck trg dep ---------------------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ABC 1 0 0 0 0 0 0 0 0 0 ANUJ 4 2 0 0 0 0 0 0 0 0 ANUJREP 1 1 0 0 0 0 0 0 0 0 ANUJTEST 1 0 0 0 0 0 0 0 0 0 APEX_03020 360 1101 45 125 4 19 12 189 366 0 APEX_04000 426 1362 54 175 3 19 12 234 439 0 APPQOSSYS 2 0 1 0 0 0 0 0 0 0 BI 0 0 8 0 0 0 0 0 0 0 CTXSYS 47 58 0 71 3 2 2 73 0 0 DBSNMP 25 13 1 7 2 1 0 4 0 0 EXFSYS 47 41 0 56 1 10 26 18 5 0 FLOWS_FILE 1 5 5 0 0 0 0 0 1 0 GGATE 12 17 0 0 2 4 4 2 0 0 HR 7 19 0 1 3 2 0 0 2 0 IX 17 17 0 8 2 0 0 0 0 0 MDSYS 123 208 0 86 15 2 107 70 53 0 OE 14 48 6 13 1 0 1 0 4 0 OLAPSYS 126 137 0 307 5 0 1 45 48 0 ORACLE_OCM 0 0 0 0 0 0 0 3 0 0 ORDDATA 73 138 0 25 12 0 0 0 0 0 ORDPLUGINS 0 0 0 0 0 0 0 5 0 0 ORDSYS 5 4 0 5 0 7 32 28 0 0 OUTLN 3 5 0 0 0 1 0 0 0 0 OWBSYS 1 1 0 0 0 0 0 0 0 0 OWBSYS_AUD 0 0 12 0 0 0 0 0 0 0 PERFSTAT 72 72 0 1 1 0 0 1 0 0 PM 3 21 0 0 0 0 0 0 0 0 RMAN 44 96 0 98 1 0 2 2 3 0 SCOTT 15 9 0 0 2 0 3 0 0 0 SH 17 29 0 1 0 0 0 0 0 0 SI_INFORMT 0 0 8 0 0 0 0 0 0 0 SYS 1013 1200 9 3761 136 107 107 628 11 0 SYSTEM 157 232 8 12 20 1 0 1 2 0 TEST_USER 0 0 0 0 0 0 1 0 0 0 VIHAAN 1 0 0 0 0 0 0 0 0 0 WMSYS 45 69 0 110 9 4 4 22 2 0 XDB 81 468 0 5 5 4 7 34 27 5 37 rows selected. ======================================== column name format a13 trunc heading SCHEMA column cl format 99999 heading CLSTR column ta format 99999 heading TABLE column ix format 99999 heading INDEX column se format 99999 heading SEQNC column tr format 99999 heading TRIGR column fn format 99999 heading FUNCT column pr format 99999 heading PROCD column pa format 99999 heading PACKG column vi format 99999 heading VIEWS column sy format 99999 heading SYNYM column ot format 99999 heading OTHER break on report compute sum of cl ta ix se tr fn pr pa vi sy ot on report select u.name, sum(decode(o.type#, 3, objs)) cl, sum(decode(o.type#, 2, objs)) ta, sum(decode(o.type#, 1, objs)) ix, sum(decode(o.type#, 6, objs)) se, sum(decode(o.type#, 12,objs)) tr, sum(decode(o.type#, 8, objs)) fn, sum(decode(o.type#, 7, objs)) pr, sum(decode(o.type#, 9, objs)) pa, sum(decode(o.type#, 4, objs)) vi, sum(decode(o.type#, 5, objs)) sy, sum(decode(o.type#, 1,0, 2,0, 3,0, 4,0, 5,0, 6,0, 7,0, 8,0, 9,0, 12,0, objs)) ot from (select owner#, type#, count(*) objs from sys.obj$ group by owner#, type#) o, sys.user$ u where u.user# = o.owner# group by u.name order by decode(u.name, 'SYS', 1, 'SYSTEM', 2, 'PUBLIC', 3, 4), u.name / SQL> @obj1 object count by user SCHEMA CLSTR TABLE INDEX SEQNC TRIGR FUNCT PROCD PACKG VIEWS SYNYM OTHER ------------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ SYS 10 1013 1200 136 11 107 107 628 3761 9 24507 SYSTEM 157 232 20 2 1 1 12 8 119 PUBLIC 27507 0 ABC 1 0 ANUJ 4 2 0 ANUJREP 1 1 0 ANUJTEST 1 0 APEX_030200 360 1101 4 366 12 19 189 125 45 340 APEX_040000 426 1362 3 439 12 19 234 175 54 420 APPQOSSYS 2 1 0 BI 8 0 CTXSYS 47 58 3 2 2 73 71 112 DBSNMP 25 13 2 1 4 7 1 12 EXFSYS 47 41 1 5 26 10 18 56 108 FLOWS_FILES 1 5 1 5 1 GGATE 12 17 2 4 4 2 2 HR 7 19 3 2 2 1 0 IX 17 17 2 8 14 MDSYS 123 208 15 53 107 2 70 86 939 OE 14 48 1 4 1 13 6 55 OLAPSYS 126 137 5 48 1 45 307 52 ORACLE_OCM 3 5 ORDDATA 73 138 12 25 9 ORDPLUGINS 5 5 ORDSYS 5 4 32 7 28 5 2451 OUTLN 3 5 1 1 OWBSYS 1 1 0 OWBSYS_AUDIT 12 0 PERFSTAT 72 72 1 1 1 1 PM 3 21 20 RMAN 44 96 1 3 2 2 98 6 SCOTT 15 9 2 3 3 SH 17 29 1 261 SI_INFORMTN_S 8 0 TEST_USER 1 0 VIHAAN 1 0 WMSYS 45 69 9 2 4 4 22 110 59 XDB 81 468 5 27 7 4 34 5 558 ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ sum 10 2744 5373 227 963 321 183 1359 4867 27664 30060 38 rows selected. ============= SET PAGES 100 LINESIZE 120 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON COLUMN owner FORMAT a19 HEADING "Owner" COLUMN sum_table FORMAT 999,999 HEADING "Tables" COLUMN sum_index FORMAT 999,999 HEADING "Indexes" COLUMN sum_view FORMAT 999,999 HEADING "Views" COLUMN sum_sequence FORMAT 999,999 HEADING "Sequences" COLUMN sum_synonym FORMAT 999,999 HEADING "Synonyms" COLUMN sum_cluster FORMAT 999,999 HEADING "Clusters" COLUMN sum_procedure FORMAT 999,999 HEADING "Procedures" COLUMN sum_package FORMAT 999,999 HEADING "Packages" COLUMN sum_package_body FORMAT 999,999 HEADING "Pckg Bodies" COLUMN sum_db_link FORMAT 999,999 HEADING "DB Links" COMPUTE SUM OF sum_table ON REPORT COMPUTE SUM OF sum_index ON REPORT COMPUTE SUM OF sum_view ON REPORT COMPUTE SUM OF sum_sequence ON REPORT COMPUTE SUM OF sum_synonym ON REPORT COMPUTE SUM OF sum_cluster ON REPORT COMPUTE SUM OF sum_procedure ON REPORT COMPUTE SUM OF sum_package ON REPORT COMPUTE SUM OF sum_package_body ON REPORT COMPUTE SUM OF sum_db_link ON REPORT spool object.lst SELECT O.owner, Sum( Decode( O.object_type, 'TABLE', 1, 0)) AS "sum_table", Sum( Decode( O.object_type, 'INDEX', 1, 0)) AS "sum_index", Sum( Decode( O.object_type, 'VIEW', 1, 0)) AS "sum_view", Sum( Decode( O.object_type, 'SEQUENCE', 1, 0)) AS "sum_sequence", Sum( Decode( O.object_type, 'SYNONYM', 1, 0)) AS "sum_synonym", Sum( Decode( O.object_type, 'CLUSTER', 1, 0)) AS "sum_cluster", Sum( Decode( O.object_type, 'PROCEDURE', 1,0)) AS "sum_procedure", Sum( Decode( O.object_type, 'PACKAGE', 1, 0)) AS "sum_package", Sum( Decode( O.object_type, 'PACKAGE BODY',1,0)) AS "sum_package_body", Sum( Decode( O.object_type, 'DATABASE LINK', 1,0)) AS "sum_db_link" FROM dba_objects O GROUP BY O.owner / SPOOL OFF CLEAR COLUMNS CLEAR COMPUTES Owner Tables Indexes Views Sequences Synonyms Clusters Procedures Packages Pckg Bodies DB Links ------------------- -------- -------- -------- --------- -------- -------- ---------- -------- ----------- -------- OWBSYS_AUDIT 0 0 0 0 12 0 0 0 0 0 MDSYS 123 114 86 15 0 0 2 70 66 0 RMAN 44 96 98 1 0 0 0 2 2 0 PUBLIC 0 0 0 0 27,507 0 0 0 0 0 OUTLN 3 4 0 0 0 0 1 0 0 0 CTXSYS 47 56 71 3 0 0 2 73 62 0 OLAPSYS 126 135 307 5 0 0 0 45 43 0 FLOWS_FILES 1 4 0 0 5 0 0 0 0 0 OWBSYS 1 1 0 0 0 0 0 0 0 0 HR 7 19 1 3 0 0 2 0 0 0 ANUJTEST 1 0 0 0 0 0 0 0 0 0 SYSTEM 157 210 12 20 8 0 1 1 1 0 ORACLE_OCM 0 0 0 0 0 0 0 3 3 0 EXFSYS 47 39 56 1 0 0 10 18 18 0 SCOTT 17 9 0 2 0 0 0 0 0 0 ABC 1 0 0 0 0 0 0 0 0 0 APEX_030200 360 946 125 4 45 0 19 189 181 0 APEX_040000 426 1,177 175 3 54 0 19 234 227 0 PM 3 4 0 0 0 0 0 0 0 0 OE 14 33 13 1 6 0 0 0 0 0 DBSNMP 25 13 7 2 1 0 1 4 4 0 ORDSYS 5 4 5 0 0 0 7 28 20 0 ORDPLUGINS 0 0 0 0 0 0 0 5 5 0 SH 17 27 1 0 0 0 0 0 0 0 IX 17 14 8 2 0 0 0 0 0 0 APPQOSSYS 2 0 0 0 1 0 0 0 0 0 XDB 81 128 5 5 0 0 4 34 33 0 ORDDATA 73 129 25 12 0 0 0 0 0 0 BI 0 0 0 0 8 0 0 0 0 0 SYS 1,012 1,026 3,761 136 9 10 107 628 602 0 WMSYS 45 61 110 9 0 0 4 22 22 0 SI_INFORMTN_SCHEMA 0 0 0 0 8 0 0 0 0 0 ANUJ 4 2 0 0 0 0 0 0 0 0 GGATE 12 16 0 2 0 0 4 2 1 0 PERFSTAT 72 72 1 1 0 0 0 1 1 0 TEST_USER 0 0 0 0 0 0 0 0 0 0 ANUJREP 1 1 0 0 0 0 0 0 0 0 VIHAAN 1 0 0 0 0 0 0 0 0 0 38 rows selected.
*********************************************************************************************
SET PAGES 50000 LINESIZE 4000 TRIMSPOOL ON ECHO OFF VERIFY OFF FEEDBACK ON COLUMN owner FORMAT a25 HEADING "Owner" COLUMN CLUSTERS FORMAT 999,999 HEADING "Clusters" COLUMN CONSUMER_GROUPS FORMAT 999,999 HEADING "Cons Groups" COLUMN CONTAINERS FORMAT 999,999 HEADING "Containers" COLUMN CONTEXTS FORMAT 999,999 HEADING "Contexts" COLUMN DB_LINKS FORMAT 999,999 HEADING "DB Links" COLUMN DESTINATIONS FORMAT 999,999 HEADING "Destinations" COLUMN DIRECTORIES FORMAT 999,999 HEADING "Directories" COLUMN EDITIONS FORMAT 999,999 HEADING "Editions" COLUMN EVAL_CONTEXTS FORMAT 999,999 HEADING "Eval Contexts" COLUMN FUNCTIONS FORMAT 999,999 HEADING "Functions" COLUMN INDEXES FORMAT 999,999 HEADING "Indexes" COLUMN INDEX_PARTS FORMAT 999,999 HEADING "Index Parts" COLUMN INDEXTYPES FORMAT 999,999 HEADING "Indextypes" COLUMN JAVA_CLASSES FORMAT 999,999 HEADING "Java Classes" COLUMN JAVA_DATA FORMAT 999,999 HEADING "Java Data" COLUMN JAVA_RESOURCES FORMAT 999,999 HEADING "Java Res" COLUMN JAVA_SOURCES FORMAT 999,999 HEADING "Java Sources" COLUMN JOBS FORMAT 999,999 HEADING "Jobs" COLUMN JOB_CLASSES FORMAT 999,999 HEADING "Job Classes" COLUMN LIBRARIES FORMAT 999,999 HEADING "Libraries" COLUMN LOBS FORMAT 999,999 HEADING "Lobs" COLUMN LOB_PARTS FORMAT 999,999 HEADING "Lob Parts" COLUMN LOCKDOWN_PROFS FORMAT 999,999 HEADING "Lockdown Prof" COLUMN OPERATORS FORMAT 999,999 HEADING "Operators" COLUMN PACKAGES FORMAT 999,999 HEADING "Packages" COLUMN PACKAGE_BODIES FORMAT 999,999 HEADING "Pckg Bodies" COLUMN PROCEDURES FORMAT 999,999 HEADING "Procedures" COLUMN PROGRAMS FORMAT 999,999 HEADING "Programs" COLUMN QUEUES FORMAT 999,999 HEADING "Queues" COLUMN RESOURCE_PLANS FORMAT 999,999 HEADING "Res Plans" COLUMN RULES FORMAT 999,999 HEADING "Rules" COLUMN RULE_SETS FORMAT 999,999 HEADING "Rule Sets" COLUMN SCHEDULES FORMAT 999,999 HEADING "Schedules" COLUMN SCHEDULER_GROUPS FORMAT 999,999 HEADING "Sched Groups" COLUMN SEQUENCES FORMAT 999,999 HEADING "Sequences" COLUMN SYNONYMS FORMAT 999,999 HEADING "Synonyms" COLUMN TABLES FORMAT 999,999 HEADING "Tables" COLUMN TABLE_PARTS FORMAT 999,999 HEADING "Table Parts" COLUMN TABLE_SUBPARTS FORMAT 999,999 HEADING "Table Subparts" COLUMN TRIGGERS FORMAT 999,999 HEADING "Triggers" COLUMN TYPES FORMAT 999,999 HEADING "Types" COLUMN TYPE_BODIES FORMAT 999,999 HEADING "Type Bodies" COLUMN UNDEFINED FORMAT 999,999 HEADING "Undefined" COLUMN UNIFIED_AUDITS FORMAT 999,999 HEADING "Unified Audits" COLUMN VIEWS FORMAT 999,999 HEADING "Views" COLUMN WINDOWS FORMAT 999,999 HEADING "Windows" COLUMN XML_SCHEMAS FORMAT 999,999 HEADING "XML Schemas" -- Compute Summary Totals at Report End COMPUTE SUM OF CLUSTERS CONSUMER_GROUPS CONTAINERS CONTEXTS DB_LINKS DESTINATIONS DIRECTORIES EDITIONS EVAL_CONTEXTS FUNCTIONS INDEXES INDEX_PARTS INDEXTYPES JAVA_CLASSES JAVA_DATA JAVA_RESOURCES JAVA_SOURCES JOBS JOB_CLASSES LIBRARIES LOBS LOB_PARTS LOCKDOWN_PROFS OPERATORS PACKAGES PACKAGE_BODIES PROCEDURES PROGRAMS QUEUES RESOURCE_PLANS RULES RULE_SETS SCHEDULES SCHEDULER_GROUPS SEQUENCES SYNONYMS TABLES TABLE_PARTS TABLE_SUBPARTS TRIGGERS TYPES TYPE_BODIES UNDEFINED UNIFIED_AUDITS VIEWS WINDOWS XML_SCHEMAS ON REPORT BREAK ON REPORT SPOOL object_summary.lst SELECT owner, COUNT(CASE WHEN object_type = 'CLUSTER' THEN 1 END) AS "CLUSTERS", COUNT(CASE WHEN object_type = 'CONSUMER GROUP' THEN 1 END) AS "CONSUMER_GROUPS", COUNT(CASE WHEN object_type = 'CONTAINER' THEN 1 END) AS "CONTAINERS", COUNT(CASE WHEN object_type = 'CONTEXT' THEN 1 END) AS "CONTEXTS", COUNT(CASE WHEN object_type = 'DATABASE LINK' THEN 1 END) AS "DB_LINKS", COUNT(CASE WHEN object_type = 'DESTINATION' THEN 1 END) AS "DESTINATIONS", COUNT(CASE WHEN object_type = 'DIRECTORY' THEN 1 END) AS "DIRECTORIES", COUNT(CASE WHEN object_type = 'EDITION' THEN 1 END) AS "EDITIONS", COUNT(CASE WHEN object_type = 'EVALUATION CONTEXT' THEN 1 END) AS "EVAL_CONTEXTS", COUNT(CASE WHEN object_type = 'FUNCTION' THEN 1 END) AS "FUNCTIONS", COUNT(CASE WHEN object_type = 'INDEX' THEN 1 END) AS "INDEXES", COUNT(CASE WHEN object_type = 'INDEX PARTITION' THEN 1 END) AS "INDEX_PARTS", COUNT(CASE WHEN object_type = 'INDEXTYPE' THEN 1 END) AS "INDEXTYPES", COUNT(CASE WHEN object_type = 'JAVA CLASS' THEN 1 END) AS "JAVA_CLASSES", COUNT(CASE WHEN object_type = 'JAVA DATA' THEN 1 END) AS "JAVA_DATA", COUNT(CASE WHEN object_type = 'JAVA RESOURCE' THEN 1 END) AS "JAVA_RESOURCES", COUNT(CASE WHEN object_type = 'JAVA SOURCE' THEN 1 END) AS "JAVA_SOURCES", COUNT(CASE WHEN object_type = 'JOB' THEN 1 END) AS "JOBS", COUNT(CASE WHEN object_type = 'JOB CLASS' THEN 1 END) AS "JOB_CLASSES", COUNT(CASE WHEN object_type = 'LIBRARY' THEN 1 END) AS "LIBRARIES", COUNT(CASE WHEN object_type = 'LOB' THEN 1 END) AS "LOBS", COUNT(CASE WHEN object_type = 'LOB PARTITION' THEN 1 END) AS "LOB_PARTS", COUNT(CASE WHEN object_type = 'LOCKDOWN PROFILE' THEN 1 END) AS "LOCKDOWN_PROFS", COUNT(CASE WHEN object_type = 'OPERATOR' THEN 1 END) AS "OPERATORS", COUNT(CASE WHEN object_type = 'PACKAGE' THEN 1 END) AS "PACKAGES", COUNT(CASE WHEN object_type = 'PACKAGE BODY' THEN 1 END) AS "PACKAGE_BODIES", COUNT(CASE WHEN object_type = 'PROCEDURE' THEN 1 END) AS "PROCEDURES", COUNT(CASE WHEN object_type = 'PROGRAM' THEN 1 END) AS "PROGRAMS", COUNT(CASE WHEN object_type = 'QUEUE' THEN 1 END) AS "QUEUES", COUNT(CASE WHEN object_type = 'RESOURCE PLAN' THEN 1 END) AS "RESOURCE_PLANS", COUNT(CASE WHEN object_type = 'RULE' THEN 1 END) AS "RULES", COUNT(CASE WHEN object_type = 'RULE SET' THEN 1 END) AS "RULE_SETS", COUNT(CASE WHEN object_type = 'SCHEDULE' THEN 1 END) AS "SCHEDULES", COUNT(CASE WHEN object_type = 'SCHEDULER GROUP' THEN 1 END) AS "SCHEDULER_GROUPS", COUNT(CASE WHEN object_type = 'SEQUENCE' THEN 1 END) AS "SEQUENCES", COUNT(CASE WHEN object_type = 'SYNONYM' THEN 1 END) AS "SYNONYMS", COUNT(CASE WHEN object_type = 'TABLE' THEN 1 END) AS "TABLES", COUNT(CASE WHEN object_type = 'TABLE PARTITION' THEN 1 END) AS "TABLE_PARTS", COUNT(CASE WHEN object_type = 'TABLE SUBPARTITION' THEN 1 END) AS "TABLE_SUBPARTS", COUNT(CASE WHEN object_type = 'TRIGGER' THEN 1 END) AS "TRIGGERS", COUNT(CASE WHEN object_type = 'TYPE' THEN 1 END) AS "TYPES", COUNT(CASE WHEN object_type = 'TYPE BODY' THEN 1 END) AS "TYPE_BODIES", COUNT(CASE WHEN object_type = 'UNDEFINED' THEN 1 END) AS "UNDEFINED", COUNT(CASE WHEN object_type = 'UNIFIED AUDIT POLICY' THEN 1 END) AS "UNIFIED_AUDITS", COUNT(CASE WHEN object_type = 'VIEW' THEN 1 END) AS "VIEWS", COUNT(CASE WHEN object_type = 'WINDOW' THEN 1 END) AS "WINDOWS", COUNT(CASE WHEN object_type = 'XML SCHEMA' THEN 1 END) AS "XML_SCHEMAS" FROM dba_objects WHERE owner = 'ANUJ' GROUP BY owner ORDER BY owner; SPOOL OFF CLEAR COLUMNS CLEAR COMPUTES CLEAR BREAKS
*********************************************************************************************
set linesize 300
SET SERVEROUTPUT ON;
DECLARE
v_sql CLOB;
BEGIN
---------------------------------------------------------------------
-- 1. Build dynamic SQL with SUM(DECODE()) for all object types
---------------------------------------------------------------------
SELECT 'SELECT ' ||
LISTAGG(
'SUM(DECODE(object_type, ''' || object_type || ''', 1, 0)) AS sum_' ||
REPLACE(LOWER(object_type), ' ', '_'),
', '
) WITHIN GROUP (ORDER BY object_type)
|| ', COUNT(*) AS total_objects ' ||
'FROM dba_objects WHERE owner = ''ANUJ'''
INTO v_sql
FROM (SELECT DISTINCT object_type
FROM dba_objects
WHERE owner = 'ANUJ');
---------------------------------------------------------------------
-- 2. Print the generated SQL (optional)
---------------------------------------------------------------------
DBMS_OUTPUT.PUT_LINE('Generated SQL:');
DBMS_OUTPUT.PUT_LINE(v_sql);
---------------------------------------------------------------------
-- 3. Execute the dynamic SQL
---------------------------------------------------------------------
EXECUTE IMMEDIATE v_sql;
END;
/
==========
SET LINESIZE 300 PAGESIZE 50 VERIFY OFF FEEDBACK ON
define O='ANUJ'
SELECT
'Object Counts for ' || UPPER('&O') AS "SCHEMA",
SUM(DECODE(object_type, 'TRIGGER', 1, 0)) AS sum_TRIGGER,
-- Count objects that are TABLEs BUT DO NOT start with MLOG$
SUM(CASE WHEN object_type = 'TABLE' AND object_name NOT LIKE 'MLOG$_%' THEN 1 ELSE 0 END) AS sum_TABLE,
-- Count objects that are TABLEs AND start with MLOG$
SUM(CASE WHEN object_type = 'TABLE' AND object_name LIKE 'MLOG$_%' THEN 1 ELSE 0 END) AS sum_MV_LOG,
SUM(DECODE(object_type, 'FUNCTION', 1, 0)) AS sum_FUNCTION,
SUM(DECODE(object_type, 'SEQUENCE', 1, 0)) AS sum_SEQUENCE,
SUM(DECODE(object_type, 'PACKAGE BODY', 1, 0)) AS sum_PACKAGEBODY,
SUM(DECODE(object_type, 'INDEX', 1, 0)) AS sum_INDEX,
SUM(DECODE(object_type, 'LOB', 1, 0)) AS sum_LOB,
SUM(DECODE(object_type, 'JOB', 1, 0)) AS sum_JOB,
SUM(DECODE(object_type, 'MATERIALIZED VIEW', 1, 0)) AS sum_MATERIALIZEDVIEW,
SUM(DECODE(object_type, 'PACKAGE', 1, 0)) AS sum_PACKAGE,
SUM(DECODE(object_type, 'PROCEDURE', 1, 0)) AS sum_PROCEDURE,
SUM(DECODE(object_type, 'VIEW', 1, 0)) AS sum_VIEW,
COUNT(*) AS total_objects
FROM
dba_objects o
WHERE
owner = UPPER('&O')
GROUP BY
'Object Counts for ' || UPPER('&O')
/
SET VERIFY ON
5 comments:
set pagesize 10000 wrap off linesize 200 heading on tab on scan on verify off
column tab format 9999 heading "tab"
column ind format 9999 heading "ind"
column syn format 9999 heading "syn"
column vew format 9999 heading "vew"
column seq format 9999 heading "seq"
column prc format 9999 heading "prc"
column fun format 9999 heading "fun"
column pck format 9999 heading "pck"
column trg format 9999 heading "trg"
column dep format 9999 heading "dep"
ttitle 'Object count by user' -
skip 2
select substr(username,1,10) "user",
count(decode(o.type#, 2, o.obj#, '')) tab,
count(decode(o.type#, 1, o.obj#, '')) ind,
count(decode(o.type#, 5, o.obj#, '')) syn,
count(decode(o.type#, 4, o.obj#, '')) vew,
count(decode(o.type#, 6, o.obj#, '')) seq,
count(decode(o.type#, 7, o.obj#, '')) prc,
count(decode(o.type#, 8, o.obj#, '')) fun,
count(decode(o.type#, 9, o.obj#, '')) pck,
count(decode(o.type#,12, o.obj#, '')) trg,
count(decode(o.type#,10, o.obj#, '')) dep
from sys.obj$ o, sys.dba_users u
where u.user_id = o.owner# (+)
-- and username='ANUJ'
and o.type# > 0
group by username
order by 1;
set feed on echo off termout on pages 24 verify on
set pagesize 300 linesize 150
col owner for a25
col object_name for a30
select o.owner,tablespace_name ,o.object_type, o.status,count(*) from dba_objects o,dba_segments s
where 1=1
-- and STATUS='INVALID'
--and o.OWNER='OWNER'
and s.segment_name = o.object_name
and s.segment_type = o.object_type
and s.owner = o.owner
group by o.owner,o.object_type, o.status ,tablespace_name
order by owner
/
List of Invalid user
SET PAGESIZE 300 LINESIZE 150
col OWNER for a25
col OBJECT_NAME for a30
select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS from dba_objects
where STATUS='INVALID' and OWNER= upper('&user_name') ;
compile all the Object
on Unix:
$sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
sql> @?/rdbms/admin/utlrp.sql
on Windows:
C:\>sqlplus "/ as sysdba" @%ORACLE_HOME%\rdbms\admin\utlrp.sql
Oracle Object Detail ...
http://anuj-singh.blogspot.com/2018_05_17_archive.html
Post a Comment