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 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