Oracle comment report
Oracle comment
set serveroutput on size 1000000
spool docdb.txt
DECLARE
l_unixdb_owner VARCHAR2(20) := UPPER('&1');
CURSOR get_tc IS
SELECT * FROM dba_tab_comments
WHERE comments IS NOT NULL
AND owner=l_unixdb_owner
AND table_name not like '%JN' -- omit Designer journal tables
ORDER BY table_name;
CURSOR get_cc (p_owner dba_tables.owner%TYPE
,p_table_name dba_tables.table_name%TYPE) IS
SELECT dcc.comments
, dtc.column_name
, DECODE(dtc.nullable,'Y','(Optional)','N','(Mandatory)') nullable
FROM dba_col_comments dcc, dba_tab_columns dtc
WHERE dcc.owner = dtc.owner
AND dcc.table_name = dtc.table_name
AND dcc.column_name = dtc.column_name
AND dcc.owner=p_owner AND dcc.table_name= p_table_name
AND dcc.comments IS NOT NULL
ORDER BY dtc.column_name
;
BEGIN
FOR tab_rec IN get_tc LOOP
dbms_output.put_line('.');
dbms_output.put_line('------------------- Start Of '
||tab_rec.table_type
||' '
||tab_rec.table_name
|| ' ---------------------');
dbms_output.put_line('.');
dbms_output.put_line(tab_Rec.table_type||' Description');
dbms_output.put_line('-----------------');
dbms_output.put_line(tab_rec.comments);
FOR col_rec IN get_cc (tab_rec.owner, tab_rec.table_name) LOOP
IF get_cc%ROWCOUNT = 1 THEN
dbms_output.put_line('.');
dbms_output.put_line('Column Descriptions');
dbms_output.put_line('-------------------');
END IF;
dbms_output.put_line (col_rec.column_name
||' '||col_rec.nullable);
dbms_output.put_line (col_rec.comments);
END LOOP;
dbms_output.put_line('.');
dbms_output.put_line('------------------- End Of Object ---------------------'
);
END LOOP;
END;
/
@comment SYSTEM
------------------- End Of Object ---------------------
.
------------------- Start Of TABLE REPCAT$_TEMPLATE_TYPES ---------------------
.
TABLE Description
-----------------
Internal table for maintaining types of templates.
.
Column Descriptions
-------------------
FLAGS (Optional)
Bitmap flags controlling each type of template.
SPARE1 (Optional)
Reserved for future expansion.
TEMPLATE_DESCRIPTION (Optional)
Description of the template type.
TEMPLATE_TYPE_ID (Mandatory)
Internal primary key of the template types table.
.
------------------- End Of Object ---------------------
Tuesday 18 October 2011
Oracle Object summary on schema
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.
Subscribe to:
Posts (Atom)
