-- _obview6.sql
-- Copyright Robert Perrine
-- OBJECT VIEWER, Take 6.
-- Sixth attempt to build a tool that will aid programmers
-- and not upset lan, system and database administrators.
-- HISTORY
-- 05Mar2005 R.Perrine Prevent select of wrapped text and ignore table sizing
-- 07Jan2001 R.Perrine Prefix view with column list.
-- 29Jul2000 R.Perrine Add Public role.
-- 23Jul2000 R.Perrine Add sequences.
-- 22Jul2000 R.Perrine Add grants.
-- 26Jun2000 R.Perrine Add triggers.
-- 19Jun2000 R.Perrine Add second parameter.
-- 16Jun2000 R.Perrine Added source.
-- 18Mar2000 R.Perrine Added constraints.
-- 22Jan2000 R.Perrine Updated this script to fit into the
-- selection table mechanism.
-- 07Jan2000 R.Perrine Initial code for this iteration.
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 1. Initialize the environment.
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
WHENEVER SQLERROR CONTINUE ROLLBACK
set feedback off
set heading off
set linesize 200
set pagesize 0
set termout on
set timing off
set underline off
set verify off
set serveroutput on size 1000000
clear breaks
clear computes
clear columns
ttitle off
column t_owner noprint
column t_object noprint
column t_sequence noprint
column t_child noprint
column t_position noprint
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 2. Copyright notification
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Welcome to Object Viewer 6, by Robert E. Perrine.
prompt .
prompt This SQL script expects you to pass two parameters on the command line.
prompt Parameter 1 is the name of a schema.
prompt Parameter 2 is the name of an object within that schema.
prompt You can use the Oracle wildcards to designate a range.
prompt For example AP% INV% will select all the objects beginning with the
prompt letters INV in any schema that begins with the letters AP.
prompt .
prompt Object Viewer 6 will then generate a script named
prompt _gen_obview6_extract.sql.
prompt _gen_obview6_extract.sql can be quite large, surpassing 10mb
prompt or even 100mb. Please plan accordingly.
prompt .
prompt When _gen_obview6_extract.sql is executed, it will generate a series of
prompt html pages. There will be one contents page for each schema and
prompt separate pages for each visible table and each view in the schema.
prompt The total size of all the html pages will be considerably smaller
prompt that the _gen_obview6_extract.sql script.
prompt (So small that I often download the results into my PDA.)
prompt .
prompt When the run is finished the _gen_obview6_extract.sql script is deleted
prompt to conserve disk space. The html pages that are left will provide a handy
prompt on-line reference manual for these database objects. To make best
prompt use of those pages, put them someplace where they can be shared.
prompt .
prompt .
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 3. Get the schema selection criteria.
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Parameter 1 is the schema name (wildcards accepted).
prompt Parameter 2 is the object name (wildcards accepted).
define v_schema='&1'
define v_pick='&2'
undefine 1
undefine 2
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 4. Define privilege level.
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
variable v_dba_roles CHAR(1)
variable v_dba_role_privs CHAR(1)
variable v_dba_sys_privs CHAR(1)
variable v_dba_users CHAR(1)
-- Set defaults
begin
:v_dba_roles := 'N';
:v_dba_role_privs := 'N';
:v_dba_syS_privs := 'N';
:v_dba_users := 'N';
end;
/
-- Decode is just to convert EXISTS into a char
-- When privileges are insufficient, these will error, not decode
begin
select decode(count(*),0,'N','Y')
into :v_dba_roles
from sys.dba_roles;
end;
/
begin
select decode(count(*),0,'N','Y')
into :v_dba_role_privs
from sys.dba_role_privs;
end;
/
begin
select decode(count(*),0,'N','Y')
into :v_dba_sys_privs
from sys.dba_sys_privs;
end;
/
begin
select decode(count(*),0,'N','Y')
into :v_dba_users
from sys.dba_users;
end;
/
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 5. Create a run_script to generate the separate schema index pages.
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
set termout off
spool _gen_obview6_extract.sql
prompt prompt Object Viewer 6. Copyright Robert E. Perrine.
prompt set arraysize 3
prompt set linesize 200
prompt set long 32000
prompt set maxdata 60000
prompt set termout off
prompt set verify off
prompt set wrap on
prompt column longtext format a500
-- TOC HEADER
select distinct col.owner t_owner,
'A' t_sequence,
'spool '||replace(col.owner,'$','-')||'.html'
||chr(10)||'prompt <HTML><TITLE>Schema '
||replace(col.owner,'$','-')||' Index (obview6)</TITLE>'
||chr(10)||'prompt <H3>Index Page for Schema '
||replace(col.owner,'$','-')||'</H3>'
||chr(10)||'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK><B>'
from sys.all_tab_columns col
where col.owner like '&v_schema'
and col.table_name like '&v_pick'
union
select distinct src.owner t_owner,
'A' t_sequence,
'spool '||replace(src.owner,'$','-')||'.html'
||chr(10)||'prompt <HTML><TITLE>Schema '
||replace(src.owner,'$','-')||' Index (obview6)</TITLE>'
||chr(10)||'prompt <H3>Index Page for Schema '
||replace(src.owner,'$','-')||'</H3>'
||chr(10)||'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK><B>'
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
and not exists
( select 1
from sys.all_tab_columns col
where col.owner = src.owner
and col.table_name like '&v_pick')
union
select distinct seq.sequence_owner t_owner,
'A' t_sequence,
'spool '||replace(seq.sequence_owner,'$','-')||'.html'
||chr(10)||'prompt <HTML><TITLE>Schema '
||replace(seq.sequence_owner,'$','-')||' Index (obview6)</TITLE>'
||chr(10)||'prompt <H3>Index Page for Schema '
||replace(seq.sequence_owner,'$','-')||'</H3>'
||chr(10)||'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK><B>'
from sys.all_sequences seq
where seq.sequence_owner like '&v_schema'
and seq.sequence_name like '&v_pick'
and not exists
( select 1
from sys.all_tab_columns col
where col.owner = seq.sequence_owner
and col.table_name like '&v_pick')
and not exists
( select 1
from sys.all_source src
where src.owner = seq.sequence_owner
and src.name like '&v_pick')
union
select distinct usr.username t_owner,
'A' t_sequence,
'spool '||replace(usr.username,'$','-')||'.html'
||chr(10)||'prompt <HTML><TITLE>Schema '
||replace(usr.username,'$','-')||' Index (obview6)</TITLE>'
||chr(10)||'prompt <H3>Index Page for Schema '
||replace(usr.username,'$','-')||'</H3>'
||chr(10)||'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK><B>'
from sys.all_users usr
where usr.username like '&v_schema'
and not exists
( select 1
from sys.all_tab_columns col
where col.owner = usr.username
and col.table_name like '&v_pick')
and not exists
( select 1
from sys.all_source src
where src.owner = usr.username
and src.name like '&v_pick')
and not exists
( select 1
from sys.all_sequences seq
where seq.sequence_owner = usr.username
and seq.sequence_name like '&v_pick')
union
select 'PUBLIC' t_owner,
'A' t_sequence,
'spool PUBLIC.html'
||chr(10)||'prompt <HTML><TITLE>ROLE PUBLIC Index (obview6)</TITLE>'
||chr(10)||'prompt <H3>Index Page for ROLE PUBLIC </H3>'
||chr(10)||'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK><B>'
from dual
where '&v_schema' like 'PUBLIC'
union
-- TOC LINKS
select tab.owner t_owner,
'B' t_sequence,
'prompt <A HREF="'
||replace(tab.owner,'$','-')||'-'
||replace(tab.table_name,'$','-')||'.html">'
||replace(tab.table_name,'$','-')||' (table)'||'</A><BR>'
from sys.all_tables tab
where tab.owner like '&v_schema'
and tab.table_name like '&v_pick'
union
select vw.owner t_owner,
'B' t_sequence,
'prompt <A HREF="'
||replace(vw.owner,'$','-')||'-'
||replace(vw.view_name,'$','-')||'.html">'
||replace(vw.view_name,'$','-')||' (view)'||'</A><BR>'
from sys.all_views vw
where vw.owner like '&v_schema'
and vw.view_name like '&v_pick'
union
select distinct src.owner t_owner,
'B' t_sequence,
'prompt <A HREF="'
||replace(src.owner,'$','-')||'-'
||replace(src.name,'$','-')||'.html">'
||replace(src.name,'$','-')||' ('
||lower(decode(src.type,'PACKAGE BODY','PACKAGE',src.type))||')</A><BR>'
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
union
select distinct seq.sequence_owner t_owner,
'B' t_sequence,
'prompt <A HREF="'
||replace(seq.sequence_owner,'$','-')||'-'
||replace(seq.sequence_name,'$','-')||'.html">'
||replace(seq.sequence_name,'$','-')||' ('
||'sequence'||')</A><BR>'
from sys.all_sequences seq
where seq.sequence_owner like '&v_schema'
and seq.sequence_name like '&v_pick'
union
select distinct trg.owner t_owner,
'B' t_sequence,
'prompt <A HREF="'
||replace(trg.owner,'$','-')||'-'
||replace(trg.trigger_name,'$','-')||'.html">'
||replace(trg.trigger_name,'$','-')||' (trigger)</A><BR>'
from sys.all_triggers trg
where trg.owner like '&v_schema'
and ( trg.trigger_name like '&v_pick'
or trg.table_name like '&v_pick' )
-- ADD CREATE USER SYNTAX
union
select distinct usr.username t_owner,
'C01' t_sequence,
'prompt </b><HR><pre>'
||chr(10)||'prompt CREATE USER '
||replace(usr.username,'$','-')
||' identified by password'
from sys.all_users usr
where usr.username like '&v_schema'
union
select distinct usr.username t_owner,
'C03' t_sequence,
'declare'
||chr(10)||'stxt varchar2(200);'
||chr(10)||'begin'
||chr(10)||'select ''default tablespace ''||default_tablespace '
||chr(10)||'||chr(10)'
||'||'' temporary tablespace ''||temporary_tablespace '
||chr(10)||'||chr(10)'
||'||'' profile ''||profile into stxt from sys.dba_users '
||chr(10)||'where username = '''||usr.username||''';'
||chr(10)||'dbms_output.put_line(stxt);'
||chr(10)||'exception'
||chr(10)||'when others then'
||chr(10)||'null;'
||chr(10)||'end;'
||chr(10)||'/'
||chr(10)||'prompt /'
from sys.all_users usr
where usr.username like '&v_schema'
and :v_dba_users = 'Y'
-- ADD SYNONYMS
union
select distinct syn.owner t_owner,
'C04' t_sequence,
'prompt </pre><HR><P>'
||chr(10)||'prompt <B>SYNONYMS FOR '
||replace(syn.owner,'$','-')||'</B><pre>'
from sys.all_synonyms syn
where syn.owner like '&v_schema'
and ( syn.owner like 'PUBLIC'
or exists
( select 1
from sys.all_users usr
where usr.username = syn.owner))
union
select syn.owner t_owner,
'C04'||syn.synonym_name t_sequence,
'prompt CREATE'||decode(syn.owner,'PUBLIC',' PUBLIC ',' ')
||'SYNONYM'||decode(syn.owner,'PUBLIC',' ',' '||syn.owner||'.')
||syn.synonym_name
||chr(10)||'prompt FOR '
||replace(syn.table_owner,'$','-')||'.'
||replace(syn.table_name,'$','-')
||decode(nvl(syn.db_link,' '),' ','','@'||syn.db_link)
||chr(10)||'prompt /'
from sys.all_synonyms syn
where syn.owner like '&v_schema'
and ( syn.owner like 'PUBLIC'
or exists
( select 1
from sys.all_users usr
where usr.username = syn.owner))
-- GRANTS BANNER
union
select distinct tpriv.grantee t_owner,
'C05A' t_sequence,
'prompt </pre><HR><P>'
||chr(10)||'prompt <B>GRANTS TO '
||replace(tpriv.grantee,'$','-')||'</B><pre>'
from sys.all_tab_privs tpriv
where tpriv.grantee like '&v_schema'
and ( tpriv.grantee like 'PUBLIC'
or exists
( select 1
from sys.all_users usr
where usr.username = tpriv.grantee))
union
select distinct cpriv.grantee t_owner,
'C05B' t_sequence,
'prompt </pre><HR><P>'
||chr(10)||'prompt <B>GRANTS TO '
||replace(cpriv.grantee,'$','-')||'</B><pre>'
from sys.all_col_privs cpriv
where cpriv.grantee like '&v_schema'
and not exists
( select 1
from sys.all_tab_privs tpriv
where tpriv.grantee = cpriv.grantee )
and ( cpriv.grantee like 'PUBLIC'
or exists
( select 1
from sys.all_users usr
where usr.username = cpriv.grantee))
-- ADD GRANTS RECEIVED
union
select tpriv.grantee t_owner,
'C05C'||rpad(tpriv.table_schema,30,' ')||rpad(tpriv.table_name,30,' ')
||tpriv.grantee t_sequence,
'prompt grant '||tpriv.privilege||' on '
||replace(tpriv.table_schema,'$','-')||'.'
||replace(tpriv.table_name,'$','-')||' to '
||replace(tpriv.grantee,'$','-')
||decode(tpriv.grantable,'YES',' with grant option',' ')
||chr(10)||'prompt /'
from sys.all_tab_privs tpriv
where tpriv.grantee like '&v_schema'
and ( tpriv.grantee like 'PUBLIC'
or exists
( select 1
from sys.all_users usr
where usr.username = tpriv.grantee))
union
select cpriv.grantee t_owner,
'C05D'||rpad(cpriv.table_schema,30,' ')||rpad(cpriv.table_name,30,' ')
||cpriv.grantee t_sequence,
'prompt grant '||cpriv.privilege
||' ('||replace(cpriv.column_name,'$','-')||') on '
||replace(cpriv.table_schema,'$','-')||'.'
||replace(cpriv.table_name,'$','-')||' to '
||replace(cpriv.grantee,'$','-')
||decode(cpriv.grantable,'YES',' with grant option',' ')
||chr(10)||'prompt /'
from sys.all_col_privs cpriv
where cpriv.grantee like '&v_schema'
and ( cpriv.grantee like 'PUBLIC'
or exists
( select 1
from sys.all_users usr
where usr.username = cpriv.grantee))
-- ROLE PRIVILEGE BANNER
union
select distinct usr.username t_owner,
'C05E1' t_sequence,
'select distinct ''</pre><HR><P>'''
||chr(10)||'||chr(10)||''<B>ROLE PRIVILEGES FOR '
||replace(usr.username,'$','-')||' </B><pre>'''
||chr(10)||'from sys.dba_role_privs r'
||' where r.grantee = '''||replace(usr.username,'$','-')||''';'
from sys.all_users usr
where usr.username like '&v_schema'
and :v_dba_role_privs = 'Y'
union
select 'PUBLIC' t_owner,
'C05E2' t_sequence,
'select ''</pre><HR><P>'''
||chr(10)||'||chr(10)||''<B>ROLE PRIVILEGES FOR PUBLIC </B><pre>'
||chr(10)||'from sys.dba_role_privs r'
||' where r.grantee = ''PUBLIC'';'
from dual
where '&v_schema' like 'PUBLIC'
and :v_dba_role_privs = 'Y'
-- ROLE PRIVILEGES
union
select usr.username t_owner,
'C05E3' t_sequence,
'select ''grant ''||r.granted_role||'' to ''||r.grantee'
||chr(10)
||'||decode(r.admin_option,''YES'','' with admin option'','' '')'
||'||chr(10)||''/'''
||chr(10)||'from sys.dba_role_privs r'
||chr(10)||'where r.grantee = '''||usr.username||''''
||chr(10)||'order by 1;'
from sys.all_users usr
where usr.username like '&v_schema'
and :v_dba_role_privs = 'Y'
union
select 'PUBLIC' t_owner,
'C05E4' t_sequence,
'select ''grant ''||r.granted_role||'' to ''||r.grantee'
||chr(10)
||'||decode(r.admin_option,''YES'','' with admin option'','' '')'
||'||chr(10)||''/'''
||chr(10)||'from sys.dba_role_privs r'
||chr(10)||'where r.grantee = ''PUBLIC'''
||chr(10)||'order by 1;'
from dual
where '&v_schema' like 'PUBLIC'
and :v_dba_role_privs = 'Y'
-- SYSTEM PRIVILEGE BANNER
union
select distinct usr.username t_owner,
'C05G1' t_sequence,
'select distinct ''</pre><HR><P>'''
||chr(10)||'||chr(10)||''<B>SYSTEM PRIVILEGES FOR '
||replace(usr.username,'$','-')||' </B><pre>'''
||chr(10)||'from sys.dba_sys_privs r'
||' where r.grantee = '''||replace(usr.username,'$','-')||''';'
from sys.all_users usr
where usr.username like '&v_schema'
and :v_dba_sys_privs = 'Y'
union
select 'PUBLIC' t_owner,
'C05G2' t_sequence,
'select ''</pre><HR><P>'''
||chr(10)||'||chr(10)||''<B>SYSTEM PRIVILEGES FOR PUBLIC </B><pre>'
||chr(10)||'from sys.dba_sys_privs r'
||' where r.grantee = ''PUBLIC'';'
from dual
where '&v_schema' like 'PUBLIC'
and :v_dba_sys_privs = 'Y'
-- SYSTEM PRIVILEGES
union
select usr.username t_owner,
'C05G3' t_sequence,
'select ''grant ''||r.privilege||'' to ''||r.grantee'
||chr(10)
||'||decode(r.admin_option,''YES'','' with admin option'','' '')'
||'||chr(10)||''/'''
||chr(10)||'from sys.dba_sys_privs r'
||chr(10)||'where r.grantee = '''||usr.username||''''
||chr(10)||'order by 1;'
from sys.all_users usr
where usr.username like '&v_schema'
and :v_dba_sys_privs = 'Y'
union
select 'PUBLIC' t_owner,
'C05G4' t_sequence,
'select ''grant ''||r.privilege||'' to ''||r.grantee'
||chr(10)
||'||decode(r.admin_option,''YES'','' with admin option'','' '')'
||'||chr(10)||''/'''
||chr(10)||'from sys.dba_sys_privs r'
||chr(10)||'where r.grantee = ''PUBLIC'''
||chr(10)||'order by 1;'
from dual
where '&v_schema' like 'PUBLIC'
and :v_dba_sys_privs = 'Y'
-- TOC COPYRIGHT
union
select distinct col.owner t_owner,
'Z' t_sequence,
'prompt </P><HR><P></FONT><FONT SIZE=-2>Last updated by obview6 on '
||to_char(sysdate,'dd-Mon-yyyy')||'.<br>'
||chr(10)||'prompt Object Viewer 6. Copyright Robert E. Perrine.'
||chr(10)||'prompt </B></FONT></P></BODY></HTML>'
||chr(10)||'spool off'
||chr(10)||'host ed '
||replace(col.owner,'$','-')||'.html'||' <'||'_trim.ctl '
||'>'||'/dev/null'
from sys.all_tab_columns col
where col.owner like '&v_schema'
and col.table_name like '&v_pick'
union
select distinct src.owner t_owner,
'Z' t_sequence,
'prompt </P><HR><P></FONT><FONT SIZE=-2>Last updated by obview6 on '
||to_char(sysdate,'dd-Mon-yyyy')||'.<br>'
||chr(10)||'prompt Object Viewer 6. Copyright Robert E. Perrine.'
||chr(10)||'prompt </B></FONT></P></BODY></HTML>'
||chr(10)||'spool off'
||chr(10)||'host ed '
||replace(src.owner,'$','-')||'.html'||' <'||'_trim.ctl '
||'>'||'/dev/null'
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
and not exists
( select 1
from sys.all_tab_columns col
where col.owner = src.owner
and col.table_name like '&v_pick')
union
select distinct seq.sequence_owner t_owner,
'Z' t_sequence,
'prompt </P><HR><P></FONT><FONT SIZE=-2>Last updated by obview6 on '
||to_char(sysdate,'dd-Mon-yyyy')||'.<br>'
||chr(10)||'prompt Object Viewer 6. Copyright Robert E. Perrine.'
||chr(10)||'prompt </B></FONT></P></BODY></HTML>'
||chr(10)||'spool off'
||chr(10)||'host ed '
||replace(seq.sequence_owner,'$','-')||'.html'||' <'||'_trim.ctl '
||'>'||'/dev/null'
from sys.all_sequences seq
where seq.sequence_owner like '&v_schema'
and seq.sequence_name like '&v_pick'
and not exists
( select 1
from sys.all_tab_columns col
where col.owner = seq.sequence_owner
and col.table_name like '&v_pick')
and not exists
( select 1
from sys.all_source src
where src.owner = seq.sequence_owner
and src.name like '&v_pick')
union
select distinct usr.username t_owner,
'Z' t_sequence,
'prompt </P><HR><P></FONT><FONT SIZE=-2>Last updated by obview6 on '
||to_char(sysdate,'dd-Mon-yyyy')||'.<br>'
||chr(10)||'prompt Object Viewer 6. Copyright Robert E. Perrine.'
||chr(10)||'prompt </B></FONT></P></BODY></HTML>'
||chr(10)||'spool off'
||chr(10)||'host ed '
||replace(usr.username,'$','-')||'.html'||' <'||'_trim.ctl '
||'>'||'/dev/null'
from sys.all_users usr
where usr.username like '&v_schema'
and not exists
( select 1
from sys.all_tab_columns col
where col.owner = usr.username
and col.table_name like '&v_pick')
and not exists
( select 1
from sys.all_source src
where src.owner = usr.username
and src.name like '&v_pick')
and not exists
( select 1
from sys.all_sequences seq
where seq.sequence_owner = usr.username
and seq.sequence_name like '&v_pick')
union
select 'PUBLIC' t_owner,
'Z' t_sequence,
'prompt </P><HR><P></FONT><FONT SIZE=-2>Last updated by obview6 on '
||to_char(sysdate,'dd-Mon-yyyy')||'.<br>'
||chr(10)||'prompt Object Viewer 6. Copyright Robert E. Perrine.'
||chr(10)||'prompt </B></FONT></P></BODY></HTML>'
||chr(10)||'spool off'
||chr(10)||'host ed PUBLIC.html'||' <'||'_trim.ctl '
||'>'||'/dev/null'
from dual
where '&v_schema' like 'PUBLIC'
order by 1, 2, 3
/
spool off
@_gen_obview6_extract.sql
host rm -f _gen_obview6_extract.sql
set termout off
spool _gen_obview6_extract.sql
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 6. Add code to the run_script to generate pages for each object.
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- TABLE BANNER.
select tab.owner t_owner,
tab.table_name t_object,
'01' t_sequence,
'A' t_child,
'A' t_position,
'spool '
||replace(tab.owner,'$','-')||'-'
||replace(tab.table_name,'$','-')||'.html'
||chr(10)||'prompt <HTML><TITLE>Table '
||replace(tab.owner,'$','-')||'.'
||replace(tab.table_name,'$','-')
||' (obview6)</TITLE>'
||chr(10)||'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK>'
||chr(10)||'prompt <B>TABLE '
||replace(tab.owner,'$','-')||'.'
||replace(tab.table_name,'$','-')||'</B><pre>'
||'Create TABLE '||replace(tab.owner,'$','-')||'.'
||replace(tab.table_name,'$','-')
from sys.all_tables tab
where tab.owner like '&v_schema'
and tab.table_name like '&v_pick'
-- VIEW BANNER.
union
select vw.owner t_owner,
vw.view_name t_object,
'01' t_sequence,
'A' t_child,
'A' t_position,
'spool '
||replace(vw.owner,'$','-')||'-'
||replace(vw.view_name,'$','-')||'.html'
||chr(10)||'prompt <HTML><TITLE>View '
||replace(vw.owner,'$','-')||'.'
||replace(vw.view_name,'$','-')
||' (obview6)</TITLE>'
||chr(10)||'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK>'
||chr(10)||'prompt <B>VIEW '
||replace(vw.owner,'$','-')||'.'
||replace(vw.view_name,'$','-')||'</B><pre>'
||'DESCRIBE '||replace(vw.owner,'$','-')||'.'
||replace(vw.view_name,'$','-')
from sys.all_views vw
where vw.owner like '&v_schema'
and vw.view_name like '&v_pick'
-- TABLE OR VIEW COLUMN LIST.
union
select col.owner t_owner,
col.table_name t_object,
'02' t_sequence,
'A' t_child,
to_char(col.column_id,'99990') t_position,
'prompt ( '
||rpad(replace(col.column_name,'$','-'),33,' ')||' '
||rpad(decode(substr(col.data_type,1,9),
'CHAR','char('||col.data_length||')',
'RAW','raw('||col.data_length||')',
'NUMBER','number'
||decode(nvl(col.data_precision,0),0,' ',
'('||col.data_precision||decode(nvl(col.data_scale,0),0,')',','
||col.data_scale||')')
),
'DATE','date',
'LONG','long',
'VARCHAR2','varchar2('||nvl(col.data_length,0)||')',
'ROWID','rowid',
'TIMESTAMP',col.data_type),
40,' ')
||decode(col.nullable, 'Y',' ',' NOT NULL')
from sys.all_tab_columns col
where col.owner like '&v_schema'
and col.table_name like '&v_pick'
and col.column_id = 1
union
select col.owner t_owner,
col.table_name t_object,
'02' t_sequence,
'A' t_child,
to_char(col.column_id,'99990') t_position,
'prompt , '
||rpad(replace(col.column_name,'$','-'),33,' ')||' '
||rpad(decode(substr(col.data_type,1,9),
'CHAR','char('||col.data_length||')',
'RAW','raw('||col.data_length||')',
'NUMBER','number'
||decode(nvl(col.data_precision,0),0,' ',
'('||col.data_precision||decode(nvl(col.data_scale,0),0,')',','
||col.data_scale||')')
),
'DATE','date',
'LONG','long',
'VARCHAR2','varchar2('||nvl(col.data_length,0)||')',
'ROWID','rowid',
'TIMESTAMP',col.data_type),
40,' ')
||decode(col.nullable, 'Y',' ',' NOT NULL')
from sys.all_tab_columns col
where col.owner like '&v_schema'
and col.table_name like '&v_pick'
and col.column_id > 1
-- TABLE STORAGE.
union
select tab.owner t_owner,
tab.table_name t_object,
'03' t_sequence,
'A' t_child,
'Z' t_position,
'prompt )'||chr(10)
-- ||'prompt pctfree '||nvl(tab.pct_free, 0)
-- ||' pctused '||tab.pct_used
-- ||' initrans '||tab.ini_trans
-- ||' maxtrans '||tab.max_trans
||chr(10)||'prompt '
||'tablespace '||rtrim(tab.tablespace_name)
-- ||chr(10)||'prompt '
-- ||'storage (initial '||(tab.initial_extent / 1024)||'k '
-- ||'next '||(tab.next_extent / 1024)||'k '
-- ||'minextents '||tab.min_extents||' '
-- ||'maxextents '||decode(tab.max_extents,2147483645,'UNLIMITED',
-- to_char(tab.max_extents))||' '
-- ||'pctincrease '||tab.pct_increase||')'
||chr(10)||' prompt /</pre>'
from sys.all_tables tab
where tab.owner like '&v_schema'
and tab.table_name like '&v_pick'
-- LINK TO TRIGGER
union
select distinct trg.owner t_owner,
trg.table_name t_object,
'04' t_sequence,
'A' t_child,
'Z' t_position,
'prompt Link to TRIGGER <A HREF="'
||replace(trg.owner,'$','-')||'-'
||replace(trg.trigger_name,'$','-')||'.html">'
||replace(trg.trigger_name,'$','-')||'</A><BR>'
from sys.all_triggers trg
where trg.owner like '&v_schema'
and ( trg.trigger_name like '&v_pick'
or trg.table_name like '&v_pick' )
-- VIEW CREATION SYNTAX
union
select vw.owner t_owner,
vw.view_name t_object,
'05' t_sequence,
'A' t_child,
'A' t_position,
'prompt </pre>CREATE OR REPLACE VIEW '
||replace(vw.owner,'$','-')||'.'
||replace(vw.view_name,'$','-')
from sys.all_views vw
where vw.owner like '&v_schema'
and vw.view_name like '&v_pick'
union
select vw.owner t_owner,
vw.view_name t_object,
'05' t_sequence,
'B' t_child,
to_char(col.column_id,'99990') t_position,
'prompt '||decode(col.column_id,1,'( ',', ')
||rtrim(replace(col.column_name,'$','-'))
from sys.all_tab_columns col, sys.all_views vw
where vw.owner like '&v_schema'
and vw.view_name like '&v_pick'
and col.owner = vw.owner
and col.table_name = vw.view_name
union
select vw.owner t_owner,
vw.view_name t_object,
'05' t_sequence,
'C' t_child,
'A' t_position,
'prompt ) AS '
||chr(10)||'set linesize 500'
||chr(10)||'select text longtext from sys.all_views '
||chr(10)||'where owner = '
||''''||replace(vw.owner,'$','-')||''''
||' and view_name = '
||''''||replace(vw.view_name,'$','-')||''''||';'
||chr(10)||'set linesize 200'
||chr(10)||'prompt <br>'||'/'||'<pre>'
from sys.all_views vw
where vw.owner like '&v_schema'
and vw.view_name like '&v_pick'
-- INDEX BANNER
union
select distinct ind.table_owner t_owner,
ind.table_name t_object,
'10' t_sequence,
'A' t_child,
'A' t_position,
'prompt <HR><P>'
||chr(10)||'prompt <B>INDEXES ON '
||replace(ind.owner,'$','-')||'.'
||replace(ind.table_name,'$','-')||'</B><pre>'
from sys.all_indexes ind
where ind.table_owner like '&v_schema'
and ind.table_name like '&v_pick'
-- INDEX NAME
union
select ind.table_owner t_owner,
ind.table_name t_object,
'11' t_sequence,
ind.index_name t_child,
'0000' t_position,
'prompt '||chr(10)||'prompt Create '
||decode(ind.uniqueness,'UNIQUE','UNIQUE ',' ')
||'INDEX '||replace(ind.index_name,'$','-')
||' ON '||replace(ind.table_name,'$','-')
from sys.all_indexes ind
where ind.table_owner like '&v_schema'
and ind.table_name like '&v_pick'
-- INDEX COLUMNS.
union
select col.table_owner t_owner,
col.table_name t_object,
'11' t_sequence,
col.index_name t_child,
ltrim(to_char(col.column_position,'0000')) t_position,
'prompt ( '||replace(col.column_name,'$','-')
from sys.all_ind_columns col
where col.table_owner like '&v_schema'
and col.table_name like '&v_pick'
and col.column_position = 1
union
select col.table_owner t_owner,
col.table_name t_object,
'11' t_sequence,
col.index_name t_child,
ltrim(to_char(col.column_position,'0000')) t_position,
'prompt , '||replace(col.column_name,'$','-')
from sys.all_ind_columns col
where col.table_owner like '&v_schema'
and col.table_name like '&v_pick'
and col.column_position > 1
-- INDEX STORAGE.
union
select ind.table_owner t_owner,
ind.table_name t_object,
'11' t_sequence,
ind.index_name t_child,
'9999' t_position,
'prompt )'||chr(10)
-- ||'prompt pctfree '||nvl(ind.pct_free, 0)
-- ||' initrans '||ind.ini_trans
-- ||' maxtrans '||ind.max_trans
||chr(10)||'prompt '
||'tablespace '||rtrim(replace(ind.tablespace_name,'$','-'))
-- ||chr(10)||'prompt '
-- ||'storage (initial '||(ind.initial_extent / 1024)||'k '
-- ||'next '||(ind.next_extent / 1024)||'k '
-- ||'minextents '||ind.min_extents||' '
-- ||'maxextents '||decode(ind.max_extents,2147483645,'UNLIMITED',
-- to_char(ind.max_extents))||' '
-- ||'pctincrease '||ind.pct_increase||')'
||chr(10)||' prompt /'
from sys.all_indexes ind
where ind.table_owner like '&v_schema'
and ind.table_name like '&v_pick'
-- CONSTRAINT BANNER
union
select distinct con.owner t_owner,
con.table_name t_object,
'20' t_sequence,
'A' t_child,
'A' t_position,
'prompt </pre><HR><P>'
||chr(10)||'prompt <B>CONSTRAINTS ON '
||replace(con.owner,'$','-')||'.'
||replace(con.table_name,'$','-')
||'</B><pre>'
from sys.all_constraints con
where con.owner like '&v_schema'
and con.table_name like '&v_pick'
-- CONSTRAINT NAME.
union
select con.owner t_owner,
con.table_name t_object,
'21' t_sequence,
con.constraint_name t_child,
'0000' t_position,
'prompt '
||chr(10)||'prompt ALTER TABLE '
||replace(con.table_name,'$','-')||' ADD CONSTRAINT '
||replace(con.constraint_name,'$','-')||' '
||decode(con.constraint_type,'P','PRIMARY KEY','R','FOREIGN KEY',
'U','UNIQUE','C','CHECK (','V','VIEW','')
||decode(con.constraint_type,'V',' (view is read only)',' ')
from sys.all_constraints con
where con.owner like '&v_schema'
and con.table_name like '&v_pick'
-- CHECK CONSTRAINTS
-- NOTE: Assuming this longtext will not actually be longer than linesize
union
select con.owner t_owner,
con.table_name t_object,
'21' t_sequence,
con.constraint_name t_child,
'0001' t_position,
'set linesize 500'
||chr(10)||'select search_condition longtext from sys.all_constraints '
||chr(10)||'where owner = '
||''''||replace(con.owner,'$','-')||''''
||' and constraint_name = '
||''''||replace(con.constraint_name,'$','-')||''''||';'
||chr(10)||'set linesize 200'
||chr(10)||'prompt )'
||chr(10)||'prompt /'
from sys.all_constraints con
where con.owner like '&v_schema'
and con.table_name like '&v_pick'
and con.constraint_type = 'C'
-- CONSTRAINT COLUMNS.
union
select con.owner t_owner,
con.table_name t_object,
'21' t_sequence,
con.constraint_name t_child,
ltrim(to_char(col.position,'0000')) t_position,
'prompt ( '||replace(col.column_name,'$','-')
from sys.all_cons_columns col, sys.all_constraints con
where col.owner like '&v_schema'
and con.owner = col.owner
and con.table_name like '&v_pick'
and con.constraint_name = col.constraint_name
and con.constraint_type <> 'C'
and col.position = 1
union
select con.owner t_owner,
con.table_name t_object,
'21' t_sequence,
con.constraint_name t_child,
ltrim(to_char(col.position,'0000')) t_position,
'prompt , '||replace(col.column_name,'$','-')
||decode(con.constraint_type,'C',' NOT NULL',null)
from sys.all_cons_columns col, sys.all_constraints con
where col.owner like '&v_schema'
and con.owner = col.owner
and con.table_name like '&v_pick'
and con.constraint_name = col.constraint_name
and con.constraint_type <> 'C'
and col.position > 1
-- REFERENCED CONSTRAINT NAME
union
select con.owner t_owner,
con.table_name t_object,
'21' t_sequence,
con.constraint_name t_child,
'8000' t_position,
'prompt ) REFERENCES '||replace(col.owner,'$','-')||'.'
||replace(col.table_name,'$','-')
from sys.all_cons_columns col, sys.all_constraints con
where con.owner like '&v_schema'
and con.table_name like '&v_pick'
and con.constraint_type = 'R'
and col.owner = con.r_owner
and col.constraint_name = con.r_constraint_name
-- REFERENCED CONSTRAINT COLUMNS.
union
select con.owner t_owner,
con.table_name t_object,
'21' t_sequence,
con.constraint_name t_child,
'8'||ltrim(to_char(col.position,'000')) t_position,
'prompt ( '||replace(col.column_name,'$','-')
from sys.all_cons_columns col, sys.all_constraints con
where con.owner like '&v_schema'
and con.table_name like '&v_pick'
and con.constraint_type = 'R'
and col.owner = con.r_owner
and col.constraint_name = con.r_constraint_name
and col.position = 1
union
select con.owner t_owner,
con.table_name t_object,
'21' t_sequence,
con.constraint_name t_child,
'8'||ltrim(to_char(col.position,'000')) t_position,
'prompt , '||replace(col.column_name,'$','-')
from sys.all_cons_columns col, sys.all_constraints con
where con.owner like '&v_schema'
and con.table_name like '&v_pick'
and con.constraint_type = 'R'
and col.owner = con.r_owner
and col.constraint_name = con.r_constraint_name
and col.position > 1
-- CONSTRAINT TRAILER
union
select con.owner t_owner,
con.table_name t_object,
'21' t_sequence,
con.constraint_name t_child,
'9001' t_position,
'prompt )'
from sys.all_cons_columns col, sys.all_constraints con
where col.owner like '&v_schema'
and con.owner = col.owner
and con.table_name like '&v_pick'
and con.constraint_name = col.constraint_name
and con.constraint_type <> 'C'
and col.position = 1
union
select con.owner t_owner,
con.table_name t_object,
'21' t_sequence,
con.constraint_name t_child,
'9999' t_position,
'prompt /'
from sys.all_constraints con
where con.owner like '&v_schema'
and con.table_name like '&v_pick'
and con.constraint_type <> 'C'
-- GRANTS BANNER
union
select distinct priv.table_schema t_owner,
priv.table_name t_object,
'31' t_sequence,
'A' t_child,
'A' t_position,
'prompt </pre><HR><P>'
||chr(10)||'prompt <B>GRANTS ON '
||replace(priv.table_schema,'$','-')||'.'
||replace(priv.table_name,'$','-')
||'</B><pre>'
from sys.all_tab_privs priv, sys.all_objects obj
where priv.table_schema like '&v_schema'
and priv.table_name like '&v_pick'
and obj.owner = priv.table_schema
and obj.object_name = priv.table_name
and obj.object_type in ('TABLE','VIEW')
-- GRANTS ON TABLES
union
select priv.table_schema t_owner,
priv.table_name t_object,
'32' t_sequence,
priv.grantee t_child,
priv.privilege||'01' t_position,
'prompt GRANT '||priv.privilege||' on '
||replace(priv.table_schema,'$','-')||'.'
||replace(priv.table_name,'$','-')||' to '
||replace(priv.grantee,'$','-')
||decode(priv.grantable,'YES',chr(10)||'prompt WITH ADMIN OPTION',' ')
||chr(10)||'prompt /'
from sys.all_tab_privs priv, sys.all_objects obj
where priv.table_schema like '&v_schema'
and priv.table_name like '&v_pick'
and obj.owner = priv.table_schema
and obj.object_name = priv.table_name
and obj.object_type in ('TABLE','VIEW')
-- GRANTS ON COLUMNS
union
select priv.table_schema t_owner,
priv.table_name t_object,
'33' t_sequence,
priv.grantee t_child,
priv.privilege||'01' t_position,
'prompt GRANT '||priv.privilege
||' on ('
||replace(priv.column_name,'$','-')||') '
||replace(priv.table_schema,'$','-')||'.'
||replace(priv.table_name,'$','-')
||chr(10)||'to '
||replace(priv.grantee,'$','-')
||decode(priv.grantable,'YES','prompt WITH ADMIN OPTION',' ')
||chr(10)||'prompt /'
from sys.all_col_privs priv
where priv.table_schema like '&v_schema'
and priv.table_name like '&v_pick'
-- SYNONYMS BANNER
union
select distinct syn.table_owner t_owner,
syn.table_name t_object,
'41' t_sequence,
'A' t_child,
'A' t_position,
'prompt </pre><HR><P>'
||chr(10)||'prompt <B>SYNONYMS FOR '
||replace(syn.table_owner,'$','-')||'.'
||replace(syn.table_name,'$','-')
||'</B><pre>'
from sys.all_synonyms syn, sys.all_objects obj
where syn.table_owner like '&v_schema'
and syn.table_name like '&v_pick'
and obj.owner = syn.table_owner
and obj.object_name = syn.table_name
and obj.object_type in ('TABLE','VIEW')
-- SYNONYMS FOR TABLES
union
select syn.table_owner t_owner,
syn.table_name t_object,
'42' t_sequence,
syn.owner t_child,
'B' t_position,
'prompt CREATE'||decode(syn.owner,'PUBLIC',' PUBLIC ',' ')
||'SYNONYM'||decode(syn.owner,'PUBLIC',' ',' '||syn.owner||'.')
||syn.synonym_name
||chr(10)||'prompt FOR '
||replace(syn.table_owner,'$','-')||'.'
||replace(syn.table_name,'$','-')
||decode(nvl(syn.db_link,' '),' ','','@'||syn.db_link)
||chr(10)||'prompt /'
from sys.all_synonyms syn, sys.all_objects obj
where syn.table_owner like '&v_schema'
and syn.table_name like '&v_pick'
and obj.owner = syn.table_owner
and obj.object_name = syn.table_name
and obj.object_type in ('TABLE','VIEW')
-- COPYRIGHT
union
select distinct col.owner t_owner,
col.table_name t_object,
'99' t_sequence,
'A' t_child,
'Y' t_position,
'prompt </pre><HR><P></B>'
||'</FONT><FONT SIZE=-2>Last updated by obview6 on '
||to_char(sysdate,'dd-Mon-yyyy')||'.'
||chr(10)||'prompt </FONT></P></BODY></HTML>'
||chr(10)||'spool off'
from sys.all_tab_columns col
where col.owner like '&v_schema'
and col.table_name like '&v_pick'
union
select distinct col.owner t_owner,
col.table_name t_object,
'99' t_sequence,
'A' t_child,
'Z' t_position,
'host ed '||col.owner||'-'||col.table_name||'.html'||' <'||'_trim.ctl '
||'>'||'/dev/null'
from sys.all_tab_columns col
where col.owner like '&v_schema'
and col.table_name like '&v_pick'
order by 1, 2, 3, 4, 5
/
spool off
@_gen_obview6_extract.sql
host rm -f _gen_obview6_extract.sql
set termout off
spool _gen_obview6_extract.sql
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 7. Extract the source code.
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- SOURCE TITLE
prompt set linesize 400
select distinct
src.owner t_owner,
src.name t_sequence,
0 t_child,
'A' t_position,
'SPOOL '
||replace(src.owner,'$','-')||'-'
||replace(src.name,'$','-')||'.html'
||chr(10)||'prompt <HTML><TITLE>'
||decode(src.type,'PACKAGE BODY','PACKAGE',src.type)||' '
||replace(src.owner,'$','-')||'.'
||replace(src.name,'$','-')
||' (obview6)</TITLE>'
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
union
-- SOURCE HEADING
select distinct
src.owner t_owner,
src.name t_sequence,
decode(src.type,'PACKAGE',1,'PACKAGE BODY',2,'FUNCTION',3,
'PROCEDURE',4, 5) t_child,
'B' t_position,
'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK>'
||chr(10)||'prompt <B>'||src.type||' '
||replace(src.owner,'$','-')||'.'
||replace(src.name,'$','-')||'</B><BR>'
||chr(10)||'PROMPT <pre>Create or Replace '
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
union
-- SOURCE CODE
select distinct
src.owner t_owner,
src.name t_sequence,
decode(src.type,'PACKAGE',1,'PACKAGE BODY',2,'FUNCTION',3,
'PROCEDURE',4, 5) t_child,
'C' t_position,
'select rtrim(replace(text,chr(13),'' '')) from sys.all_source s1'
||' where owner like '''||replace(src.owner,'$','%')||''''
||' and name like '''||replace(src.name,'$','%')||''''
||chr(10)
||' and type = '''||src.type||''''
||' order by line;'
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
and exists
( select 1 from sys.all_source s2
where s2.owner = src.owner
and s2.name = src.name
and s2.type = src.type
and s2.line = 1
and lower(s2.text) not like '%wrapped%'
)
union
-- WHEN WRAPPED
select distinct
src.owner t_owner,
src.name t_sequence,
decode(src.type,'PACKAGE',1,'PACKAGE BODY',2,'FUNCTION',3,
'PROCEDURE',4, 5) t_child,
'D' t_position,
'PROMPT ' || rtrim(src.type) || ' ' || rtrim(src.name) || ' (Wrapped)'
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
and exists
( select 1 from sys.all_source s2
where s2.owner = src.owner
and s2.name = src.name
and s2.type = src.type
and s2.line = 1
and lower(s2.text) like '%wrapped%'
)
union
-- SOURCE TRAILER
select distinct
src.owner t_owner,
src.name t_sequence,
decode(src.type,'PACKAGE',1,'PACKAGE BODY',2,'FUNCTION',3,
'PROCEDURE',4, 5) t_child,
'E' t_position,
'PROMPT /'
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
and exists
( select 1 from sys.all_source s2
where s2.owner = src.owner
and s2.name = src.name
and s2.type = src.type
and s2.line = 1
and lower(s2.text) not like '%wrapped%'
)
union
select distinct
src.owner t_owner,
src.name t_sequence,
decode(src.type,'PACKAGE',1,'PACKAGE BODY',2,'FUNCTION',3,
'PROCEDURE',4, 5) t_child,
'F' t_position,
'PROMPT show errors</pre>'
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
-- GRANTS BANNER
union
select distinct priv.table_schema t_owner,
priv.table_name t_sequence,
decode(obj.object_type,'PACKAGE',2,'FUNCTION',3,
'PROCEDURE',4, 5) t_child,
'G' t_position,
'prompt </pre><HR><P>'
||chr(10)||'prompt <B>GRANTS ON '
||replace(priv.table_schema,'$','-')||'.'
||replace(priv.table_name,'$','-')
||'</B><pre>'
from sys.all_tab_privs priv, sys.all_objects obj
where priv.table_schema like '&v_schema'
and priv.table_name like '&v_pick'
and obj.owner = priv.table_schema
and obj.object_name = priv.table_name
and obj.object_type in ('FUNCTION','PACKAGE','PROCEDURE')
-- GRANTS ON SOURCE CODE
union
select priv.table_schema t_owner,
priv.table_name t_sequence,
decode(obj.object_type,'PACKAGE',2,'FUNCTION',3,
'PROCEDURE',4, 5) t_child,
'H'||rpad(priv.grantee,32,' ')||priv.privilege t_position,
'prompt GRANT '||priv.privilege||' on '
||replace(priv.table_schema,'$','-')||'.'
||replace(priv.table_name,'$','-')||' to '
||replace(priv.grantee,'$','-')
||decode(priv.grantable,'YES',chr(10)||'prompt WITH ADMIN OPTION',' ')
||chr(10)||'prompt /'
from sys.all_tab_privs priv, sys.all_objects obj
where priv.table_schema like '&v_schema'
and priv.table_name like '&v_pick'
and obj.owner = priv.table_schema
and obj.object_name = priv.table_name
and obj.object_type in ('FUNCTION','PACKAGE','PROCEDURE')
-- SYNONYMS BANNER
union
select distinct syn.table_owner t_owner,
syn.table_name t_sequence,
decode(obj.object_type,'PACKAGE',2,'FUNCTION',3,
'PROCEDURE',4, 5) t_child,
'I' t_position,
'prompt </pre><HR><P>'
||chr(10)||'prompt <B>SYNONYMS FOR '
||replace(syn.table_owner,'$','-')||'.'
||replace(syn.table_name,'$','-')
||'</B><pre>'
from sys.all_synonyms syn, sys.all_objects obj
where syn.table_owner like '&v_schema'
and syn.table_name like '&v_pick'
and obj.owner = syn.table_owner
and obj.object_name = syn.table_name
and obj.object_type in ('FUNCTION','PACKAGE','PROCEDURE')
-- SYNONYMS FOR SOURCE CODE
union
select syn.table_owner t_owner,
syn.table_name t_sequence,
decode(obj.object_type,'PACKAGE',2,'FUNCTION',3,
'PROCEDURE',4, 5) t_child,
'J'||syn.owner t_position,
'prompt CREATE'||decode(syn.owner,'PUBLIC',' PUBLIC ',' ')
||'SYNONYM'||decode(syn.owner,'PUBLIC',' ',' '||syn.owner||'.')
||syn.synonym_name
||chr(10)||'prompt FOR '
||replace(syn.table_owner,'$','-')||'.'
||replace(syn.table_name,'$','-')
||decode(nvl(syn.db_link,' '),' ','','@'||syn.db_link)
||chr(10)||'prompt /'
from sys.all_synonyms syn, sys.all_objects obj
where syn.table_owner like '&v_schema'
and syn.table_name like '&v_pick'
and obj.owner = syn.table_owner
and obj.object_name = syn.table_name
and obj.object_type in ('FUNCTION','PACKAGE','PROCEDURE')
-- COPYRIGHT
union
select distinct
src.owner t_owner,
src.name t_sequence,
decode(src.type,'PACKAGE',2,'PACKAGE BODY',2,'FUNCTION',3,
'PROCEDURE',4, 5) t_child,
'Y' t_position,
'prompt </pre><HR><P></B>'
||'</FONT><FONT SIZE=-2>Last updated by obview6 on '
||to_char(sysdate,'dd-Mon-yyyy')||'.'
||chr(10)||'prompt </FONT></P></BODY></HTML>'
||chr(10)||'spool off'
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
union
select distinct
src.owner t_owner,
src.name t_sequence,
6 t_child,
'Z' t_position,
'host ed '
||replace(src.owner,'$','-')||'-'
||replace(src.name,'$','-')||'.html'||' <'||'_trim.ctl'||' >/dev/null'
from sys.all_source src
where src.owner like '&v_schema'
and src.name like '&v_pick'
order by 1, 2
/
spool off
@_gen_obview6_extract.sql
host rm -f _gen_obview6_extract.sql
set termout off
spool _gen_obview6_extract.sql
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 8. Triggers take special handling
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- TRIGGER TITLE
prompt set linesize 400
select distinct
trg.owner t_owner,
trg.trigger_name t_sequence,
0 t_child,
0 t_position,
'SPOOL '
||replace(trg.owner,'$','-')||'-'
||replace(trg.trigger_name,'$','-')||'.html'
||chr(10)||'prompt <HTML><TITLE>TRIGGER '
||replace(trg.owner,'$','-')||'.'
||replace(trg.trigger_name,'$','-')
||' (obview6)</TITLE>'
from sys.all_triggers trg
where trg.owner like '&v_schema'
and ( trg.trigger_name like '&v_pick'
or trg.table_name like '&v_pick' )
union
-- TRIGGER HEADING
select distinct
trg.owner t_owner,
trg.trigger_name t_sequence,
1 t_child,
0 t_position,
'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK>'
||chr(10)||'prompt <B>TRIGGER '
||replace(trg.owner,'$','-')||'.'
||replace(trg.trigger_name,'$','-')||'</B><BR>'
||chr(10)||'PROMPT <pre>Create or Replace TRIGGER '
from sys.all_triggers trg
where trg.owner like '&v_schema'
and ( trg.trigger_name like '&v_pick'
or trg.table_name like '&v_pick' )
union
-- TRIGGER CLAUSE
select distinct
trg.owner t_owner,
trg.trigger_name t_sequence,
2 t_child,
0 t_position,
'select rtrim(t.description)||chr(10)||rtrim(t.when_clause)'
||' from sys.all_triggers t'||chr(10)
||'where owner like '''||replace(trg.owner,'$','%')||''''
||' and trigger_name like '''||replace(trg.trigger_name,'$','%')||''''
||';'
from sys.all_triggers trg
where trg.owner like '&v_schema'
and ( trg.trigger_name like '&v_pick'
or trg.table_name like '&v_pick' )
union
-- TRIGGER BODY
select distinct
trg.owner t_owner,
trg.trigger_name t_sequence,
2 t_child,
1 t_position,
'select t.trigger_body'
||' from sys.all_triggers t'||chr(10)
||'where owner like '''||replace(trg.owner,'$','%')||''''
||' and trigger_name like '''||replace(trg.trigger_name,'$','%')||''''
||';'
from sys.all_triggers trg
where trg.owner like '&v_schema'
and ( trg.trigger_name like '&v_pick'
or trg.table_name like '&v_pick' )
-- COPYRIGHT
union
select distinct
trg.owner t_owner,
trg.trigger_name t_sequence,
3 t_child,
0 t_position,
'prompt '||'/'||chr(10)
||'prompt </pre><HR><P></B>'
||'</FONT><FONT SIZE=-2>Last updated by obview6 on '
||to_char(sysdate,'dd-Mon-yyyy')||'.'
||chr(10)||'prompt </FONT></P></BODY></HTML>'
||chr(10)||'spool off'
from sys.all_triggers trg
where trg.owner like '&v_schema'
and ( trg.trigger_name like '&v_pick'
or trg.table_name like '&v_pick' )
union
select distinct
trg.owner t_owner,
trg.trigger_name t_sequence,
3 t_child,
1 t_position,
'host ed '
||replace(trg.owner,'$','-')||'-'
||replace(trg.trigger_name,'$','-')||'.html'||' <'||'_trim.ctl'
||' >/dev/null'
from sys.all_triggers trg
where trg.owner like '&v_schema'
and ( trg.trigger_name like '&v_pick'
or trg.table_name like '&v_pick' )
order by 1, 2
/
spool off
@_gen_obview6_extract.sql
host rm -f _gen_obview6_extract.sql
set termout off
spool _gen_obview6_extract.sql
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 9. Extract the sequences
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- SEQUENCE TITLE
prompt set linesize 200
select seq.sequence_owner t_owner,
seq.sequence_name t_sequence,
'A' t_child,
'SPOOL '
||replace(seq.sequence_owner,'$','-')||'-'
||replace(seq.sequence_name,'$','-')||'.html'
||chr(10)||'prompt <HTML><TITLE>'
||'SEQUENCE'||' '
||replace(seq.sequence_owner,'$','-')||'.'
||replace(seq.sequence_name,'$','-')
||' (obview6)</TITLE>'
from sys.all_sequences seq
where seq.sequence_owner like '&v_schema'
and seq.sequence_name like '&v_pick'
union
-- SEQUENCE HEADING
select seq.sequence_owner t_owner,
seq.sequence_name t_sequence,
'B' t_child,
'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK>'
||chr(10)||'prompt <B>SEQUENCE '
||replace(seq.sequence_owner,'$','-')||'.'
||replace(seq.sequence_name,'$','-')||'</B><BR>'
from sys.all_sequences seq
where seq.sequence_owner like '&v_schema'
and seq.sequence_name like '&v_pick'
union
-- SEQUENCE SYNTAX
select seq.sequence_owner t_owner,
seq.sequence_name t_sequence,
'C' t_child,
'PROMPT <pre>Create SEQUENCE '
||replace(seq.sequence_owner,'$','-')||'.'
||replace(seq.sequence_name,'$','-')
||chr(10)||'prompt '
||' start with '||nvl(seq.last_number,nvl(seq.min_value,1))
||' increment by '||nvl(seq.increment_by,1)
||chr(10)||'prompt '
||decode(nvl(seq.min_value,-13),-13,' nominvalue ',
' minvalue '||seq.min_value)||' '
||decode(nvl(seq.max_value,-13),-13,'nomaxvalue',
decode(trunc(length(to_number(seq.max_value)) / 8),0,' maxvalue '
||seq.max_value,'nomaxvalue'))
||chr(10)||'prompt '
||decode(nvl(seq.cycle_flag,'N'),'N',' nocycle ','Y',' cycle ')
||decode(nvl(seq.cache_size,0),0,' nocache', ' cache '
||seq.cache_size)
||chr(10)||'prompt /'
from sys.all_sequences seq
where seq.sequence_owner like '&v_schema'
and seq.sequence_name like '&v_pick'
-- GRANTS BANNER
union
select distinct priv.table_schema t_owner,
priv.table_name t_sequence,
'D' t_child,
'prompt </pre><HR><P>'
||chr(10)||'prompt <B>GRANTS ON '
||replace(priv.table_schema,'$','-')||'.'
||replace(priv.table_name,'$','-')
||'</B><pre>'
from sys.all_tab_privs priv, sys.all_objects obj
where priv.table_schema like '&v_schema'
and priv.table_name like '&v_pick'
and obj.owner = priv.table_schema
and obj.object_name = priv.table_name
and obj.object_type = 'SEQUENCE'
-- GRANTS ON SEQUENCES
union
select priv.table_schema t_owner,
priv.table_name t_sequence,
'G'||rpad(priv.grantee,32,' ')||priv.privilege t_child,
'prompt GRANT '||priv.privilege||' on '
||replace(priv.table_schema,'$','-')||'.'
||replace(priv.table_name,'$','-')||' to '
||replace(priv.grantee,'$','-')
||decode(priv.grantable,'YES',chr(10)||'prompt WITH ADMIN OPTION',' ')
||chr(10)||'prompt /'
from sys.all_tab_privs priv, sys.all_objects obj
where priv.table_schema like '&v_schema'
and priv.table_name like '&v_pick'
and obj.owner = priv.table_schema
and obj.object_name = priv.table_name
and obj.object_type = 'SEQUENCE'
-- SYNONYMS BANNER
union
select distinct syn.table_owner t_owner,
syn.table_name t_sequence,
'H' t_child,
'prompt </pre><HR><P>'
||chr(10)||'prompt <B>SYNONYMS ON '
||replace(syn.table_owner,'$','-')||'.'
||replace(syn.table_name,'$','-')
||'</B><pre>'
from sys.all_synonyms syn, sys.all_objects obj
where syn.table_owner like '&v_schema'
and syn.table_name like '&v_pick'
and obj.owner = syn.table_owner
and obj.object_name = syn.table_name
and obj.object_type = 'SEQUENCE'
-- SYNONYMS FOR SEQUENCES
union
select syn.table_owner t_owner,
syn.table_name t_sequence,
'I'||syn.owner t_child,
'prompt CREATE'||decode(syn.owner,'PUBLIC',' PUBLIC ',' ')
||'SYNONYM'||decode(syn.owner,'PUBLIC',' ',' '||syn.owner||'.')
||syn.synonym_name
||chr(10)||'prompt FOR '
||replace(syn.table_owner,'$','-')||'.'
||replace(syn.table_name,'$','-')
||decode(nvl(syn.db_link,' '),' ','','@'||syn.db_link)
||chr(10)||'prompt /'
from sys.all_synonyms syn, sys.all_objects obj
where syn.table_owner like '&v_schema'
and syn.table_name like '&v_pick'
and obj.owner = syn.table_owner
and obj.object_name = syn.table_name
and obj.object_type = 'SEQUENCE'
-- COPYRIGHT
union
select distinct
seq.sequence_owner t_owner,
seq.sequence_name t_sequence,
'Y' t_child,
'prompt </pre><HR><P></B>'
||'</FONT><FONT SIZE=-2>Last updated by obview6 on '
||to_char(sysdate,'dd-Mon-yyyy')||'.'
||chr(10)||'prompt </FONT></P></BODY></HTML>'
||chr(10)||'spool off'
from sys.all_sequences seq
where seq.sequence_owner like '&v_schema'
and seq.sequence_name like '&v_pick'
union
select distinct
seq.sequence_owner t_owner,
seq.sequence_name t_sequence,
'Z' t_child,
'host ed '
||replace(seq.sequence_owner,'$','-')||'-'
||replace(seq.sequence_name,'$','-')||'.html'||' <'
||'_trim.ctl'||' >/dev/null'
from sys.all_sequences seq
where seq.sequence_owner like '&v_schema'
and seq.sequence_name like '&v_pick'
order by 1, 2
/
spool off
@_gen_obview6_extract.sql
host rm -f _gen_obview6_extract.sql
set termout off
spool _gen_obview6_extract.sql
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 10. Roles require a procedural wrapper
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt WHENEVER SQLERROR CONTINUE ROLLBACK
declare
i_role_handle INTEGER;
s_role_select VARCHAR2(500);
s_role_name VARCHAR2(30);
--
l_count NUMBER;
l_ignore NUMBER;
begin
-- does user have privileges to see roles
if :v_dba_roles = 'Y' then
-- use a dynamic cursor so privilege problem is trapped
begin
s_role_select := 'SELECT replace(r.role,''$'',''-'') '
||'FROM sys.dba_roles r '
||'WHERE r.role like ''&v_schema''';
i_role_handle := dbms_sql.open_cursor;
dbms_sql.parse(i_role_handle, s_role_select, dbms_sql.v7);
dbms_sql.define_column(i_role_handle, 1, s_role_name, 30);
l_ignore := dbms_sql.execute(i_role_handle);
--
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- loop through all the roles
-- fetch roles
while dbms_sql.fetch_rows(i_role_handle) > 0 loop
dbms_sql.column_value(i_role_handle, 1, s_role_name);
--
-- HEADER
dbms_output.put_line(
'spool '||s_role_name||'.html'
||chr(10)||'prompt <HTML><TITLE>Role '||s_role_name
||' Index (obview6)</TITLE>'
||chr(10)||'prompt <H3>Index Page for Role '||s_role_name||'</H3>');
dbms_output.put_line(
'prompt <BODY BGCOLOR=WHITE>'
||chr(10)||'prompt <P><FONT SIZE=-1 COLOR=BLACK><pre>');
dbms_output.put_line(
'prompt create ROLE '||s_role_name
||chr(10)||'prompt /');
--
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- synonyms header
l_count := 0;
select count(*)
into l_count
from sys.all_synonyms syn
where syn.owner = s_role_name;
if SQL%FOUND and l_count > 0 then
dbms_output.put_line('prompt </pre><HR><B>SYNONYMS for '
||s_role_name||'</B><pre>');
--
-- synonym syntax
dbms_output.put_line('select ''CREATE SYNONYM '||s_role_name
||'.''||syn.synonym_name'
||chr(10)||'||'' for '''
||chr(10)||'||replace(syn.table_owner,''$'',''-'')||''.'''
||'||replace(syn.table_name,''$'',''-'')'
);
dbms_output.put_line(
'||decode(nvl(syn.db_link,'' ''),'' '','''',''@''||syn.db_link)'
||chr(10)||'||chr(10)||''/'''
||chr(10)||'from sys.all_synonyms syn'
||chr(10)||'where syn.owner = '''||s_role_name||''';');
end if;
--
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- grants header
l_count := 0;
select count(*)
into l_count
from sys.all_tab_privs tpriv
where tpriv.grantee = s_role_name;
if l_count < 1 then
select count(*)
into l_count
from sys.all_col_privs cpriv
where cpriv.grantee = s_role_name;
end if;
if SQL%FOUND and l_count > 0 then
dbms_output.put_line('prompt </pre><HR><B>GRANTS TO '
||s_role_name||'</B><pre>');
--
-- table grants
dbms_output.put_line('select ''GRANT ''||t.privilege||'' on '''
||chr(10)||'||replace(t.table_schema,''$'',''-'')||''.'''
||'||replace(t.table_name,''$'',''-'')'
);
dbms_output.put_line(
'||'' to '||s_role_name||''''
||'||decode(t.grantable,''YES'','' with grant option'','' '')'
||chr(10)||'||chr(10)||''/''');
dbms_output.put_line(
'from sys.all_tab_privs t'
||chr(10)||'where t.grantee = '''||s_role_name||'''');
-- column grants
dbms_output.put_line('union select ''GRANT ''||c.privilege'
||chr(10)||'||'' (''||replace(c.column_name,''$'',''-'')||'') on '''
||chr(10)||'||replace(c.table_schema,''$'',''-'')||''.'''
||'||replace(c.table_name,''$'',''-'')'
);
dbms_output.put_line(
'||'' to '||s_role_name||''''
||'||decode(c.grantable,''YES'','' with grant option'','' '')'
||chr(10)||'||chr(10)||''/''');
dbms_output.put_line(
'from sys.all_col_privs c'
||chr(10)||'where c.grantee = '''||s_role_name||''';');
end if;
--
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- role privileges
if :v_dba_role_privs = 'Y' then
dbms_output.put_line(
'select distinct ''</pre><HR><P>'''
||chr(10)||'||chr(10)||''<B>ROLE PRIVILEGES FOR '
||s_role_name||' </B><pre>'''
||chr(10)||'from sys.dba_role_privs r'
||' where r.grantee = '''||s_role_name||''';');
--
dbms_output.put_line('select ''GRANT ''||r.granted_role'
||'||'' to '||s_role_name||''''
||chr(10)
||'||decode(r.admin_option,''YES'','' with admin option'','' '')'
||chr(10)||'||chr(10)||''/''');
dbms_output.put_line(
'from sys.dba_role_privs r'
||chr(10)||'where r.grantee = '''||s_role_name||''';');
end if;
--
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- system privileges
if :v_dba_sys_privs = 'Y' then
dbms_output.put_line(
'select distinct ''</pre><HR><P>'''
||chr(10)||'||chr(10)||''<B>SYSTEM PRIVILEGES FOR '
||s_role_name||' </B><pre>'''
||chr(10)||'from sys.dba_sys_privs r'
||' where r.grantee = '''||s_role_name||''';');
--
dbms_output.put_line('select ''GRANT ''||r.privilege'
||'||'' to '||s_role_name||''''
||chr(10)
||'||decode(r.admin_option,''YES'','' with admin option'','' '')'
||chr(10)||'||chr(10)||''/''');
dbms_output.put_line(
'from sys.dba_sys_privs r'
||chr(10)||'where r.grantee = '''||s_role_name||''';');
end if;
--
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- copyright
dbms_output.put_line(
'prompt </P><HR><P></FONT><FONT SIZE=-2>Last updated by obview6 on '
||to_char(sysdate,'dd-Mon-yyyy')||'.<br>');
dbms_output.put_line(
'prompt Object Viewer 6. Copyright Robert E. Perrine.'
||chr(10)||'prompt </B></FONT></P></BODY></HTML>');
dbms_output.put_line('spool off'
||chr(10)||'host ed '||s_role_name||'.html'||' <'||'_trim.ctl');
end loop;
--
-- release resources
dbms_sql.close_cursor(i_role_handle);
exception
when others then
dbms_output.put_line('-- '||SQLERRM);
end;
end if;
end;
/
spool off
host ed _gen_obview6_extract.sql <_trim.ctl
@_gen_obview6_extract.sql
set termout off
host rm -f _gen_obview6_extract.sql
set termout on
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- 11. Final COPYRIGHT
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt .
prompt Object Viewer 6. Copyright Robert E. Perrine.
prompt If you find this tool useful, please send an e-mail
prompt to robert@RobertPerrine.biz.
prompt .
prompt obview6.sql has completed.
prompt Done.
-- end _obview6.sql
|