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