rem 
rem $Header: catalog.sql 7010300.2 94/04/04 02:35:41 snataraj Generic<base> $ catalog.sql
rem 
Rem Copyright (c) 1988 by Oracle Corporation
Rem
Rem NAME
Rem   CATALOG.SQL
Rem FUNCTION
Rem   Creates data dictionary views.
Rem NOTES
Rem   Must be run when connected to SYS or INTERNAL.
Rem
Rem   Do not create DBA synonyms in this file, they should be
Rem   added to the file dba_syn.sql
Rem
Rem Please make sure that the following 3 lines are at the end of this file:
Rem ---------------------------------------------------------------------------
Rem THIS IS THE END OF THIS FILE - IF I AM NOT HERE THEN RCS HAS TRUNCATED FILE
Rem ---------------------------------------------------------------------------
Rem MODIFIED
Rem     thayes     03/02/94 -  Add compatibility views
Rem     wmaimone   03/02/94 -  add view and public synonym for v$sess_io
Rem     ltung      02/20/94 -  yet another parallel/cache semantic change
Rem     ltung      01/23/94 -  add v$pq_sysstat
Rem     ltung      01/19/94 -  add v$pq_sesstat and v$pq_slave
Rem     ltung      01/15/94 -  new parallel/cache/partitions semantics
Rem     agupta     01/05/94 -  192948 - change units for *_extents in *_segment
Rem     jcohen     01/04/94 - #(192450) add v$option table
Rem     jcohen     12/20/93 - #(191673) fix number fmt for user_tables,cluster
Rem     jbellemo   11/09/93 -  #170173: change uid to userenv('schemaid')
Rem     gdoherty   11/01/93 -  add call to catsvrmg for Server Manager
Rem     gdoherty   10/20/93 -  add v$nls_valid_values
Rem     hkodaval   10/14/93 -  merge changes from branch 1.151.312.7
Rem     wbridge    07/02/93 -  add v$controlfile fixed table
Rem     ltung      06/25/93 -  merge changes from branch 1.151.312.4
Rem     jcohen     06/22/93 - #(165117) new view product_component_version
Rem     vraghuna   06/17/93 -  bug 166480 - move resource_map into sql.bsq
Rem     ltung      05/28/93 -  parallel/cache in table/cluster views
Rem     wmaimone   05/20/93 -  merge changes from branch 1.151.312.3 
Rem     wmaimone   05/20/93 -  merge changes from branch 1.151.312.1 
Rem     jcohen     05/18/93 - #(163749) passwords visible in SYS.DBA_DB_LINKS
Rem     wmaimone   05/18/93 -  fix width of all_indexes 
Rem     ltung      05/14/93 - #(157449) add v$dblink 
Rem     hkodaval   04/30/93 -  Bug 162360: free lists/groups should show > 0 
Rem                             in views user_segments and dba_segments
Rem     rnakhwa    04/12/93 -  merge changes from branch 1.151.312.2 
Rem     wbridge    04/02/93 -  read-only tablespaces 
Rem     agupta     01/10/93 -  141957 - remove divide by 0 window 
Rem     wmaimone   05/07/93 -  #(161964) use system privs for all_* 
Rem     rnakhwa    04/12/93 -  Embedded comments are not allowd within SQL stat
Rem     wmaimone   04/02/93 -  #(158143) grant select on nls_parameters 
Rem     wmaimone   04/02/93 -  #(158143) grant select on nls_parameters 
Rem     ksriniva   11/30/92 -  add synonyms for v$session_event, v$system_event
Rem     tpystyne   11/27/92 -  add nls_* views 
Rem     ghallmar   11/20/92 -  fix DBA_2PC_PENDING.GLOBAL_TRAN_ID 
Rem     amendels   11/19/92 -  fix 139681, 140003: modify *_constraints 
Rem     ksriniva   11/13/92 -  add public synonym for v$session_wait 
Rem     pritto     11/09/92 -  add synonym for V$MTS 
Rem     tpystyne   11/06/92 -  use create or replace view 
Rem     jklein     09/29/92 -  histogram support 
Rem     vraghuna   10/29/92 -  bug 130560 - move map tables in sql.bsq 
Rem     jloaiza    10/28/92 -  add v$db_object_cache and v$open_cursor 
Rem     glumpkin   10/20/92 -  Adjust for new .sql filenames 
Rem     ltan       10/20/92 -  rename DBA_ROLLBACK_SEGS status 
Rem     mmoore     10/15/92 - #(134232) show more privs in all_tab_privs
Rem     mmoore     10/15/92 - #(133927) speed up table_privileges view 
Rem     dsdaniel   10/13/92 -  bug 112376 112374 125947 alter/create profile 
Rem     amendels   10/08/92 -  132726: fix *_constraints to show DELETE CASCADE
Rem     mmoore     10/08/92 - #(132956) remove _next_objects from dba_objects
Rem     jwijaya    10/07/92 -  add v$*cursor_cache 
Rem     ltan       10/07/92 -  fix undefined status for dba_rollback_segs
Rem     mmoore     10/02/92 -  fix role_privs views 
Rem     ltan       09/11/92 -  decode new status for rollback segment 
Rem     jbellemo   09/24/92 -  merge changes from branch 1.124.311.2 
Rem     jbellemo   09/18/92 -  #126685: show datatype 106 as MLSLABEL in *_TAB_
Rem     mmoore     09/23/92 -  fix comment on dba_role_privs 
Rem     aho        09/23/92 -  change view text to upper case & make shorter
Rem     pritto     09/04/92 -  rename dispatcher view synonyms 
Rem     jwijaya    09/09/92 -  add v$fixed_table 
Rem     aho        08/31/92 -  merge forward status column in *_indexes from v6
Rem                         -  bug 126268
Rem     mmoore     08/28/92 - #(124859) add default role information to role vi
Rem     mmoore     08/10/92 - #(121120) remove create index from system_priv_ma
Rem     rjenkins   07/24/92 -  removing drop & alter snapshot 
Rem     hrizvi     07/16/92 -  add v$license
Rem     mmoore     07/13/92 - #(104081) change alter resource priv name -> add 
Rem     agupta     06/26/92 -  115032 - add lists,groups to *_segments 
Rem     wbridge    06/25/92 -  fixed tables for file headers 
Rem     jwijaya    06/25/92 -  MODIFIED -> LAST_DDL_TIME per marketing 
Rem     epeeler    06/23/92 -  accomodate new type 7 in cdef$ 
Rem     jwijaya    06/15/92 -  v$plsarea is obsolete 
Rem     jbellemo   06/12/92 -  add mapping for MLSLABEL to *_TAB_COLUMNS 
Rem     jwijaya    06/04/92 -  fix a typo 
Rem     mmoore     06/04/92 - #(112281) add execute to table_privs 
Rem     agupta     06/01/92 -  111558 - user_tablespaces view wrong 
Rem     mmoore     06/01/92 - #(111110) fix dba_role_privs
Rem     rlim       05/29/92 -  #110883 - add missing views in dictionary 
Rem     jwijaya    05/26/92 -  fix bug 110884 - don't grant on v$sga
Rem     jwijaya    05/19/92 -  add v$type_size 
Rem     rlim       05/15/92 -  fix bug 101589 - correct spelling mistakes 
Rem     epeeler    05/06/92 -  fix NULL columns - bug 103146 
Rem     mmoore     05/01/92 - #(107592) fix all_views to look at enabled roles 
Rem     jwijaya    04/23/92 -  status for _NEXT_OBJECT is N/A
Rem     agupta     04/16/92 -  add columns to dba_segments 
Rem     mmoore     04/13/92 -  merge changes from branch 1.101.300.1 
Rem     mmoore     03/03/92 -  change grant view names
Rem     rnakhwa    03/10/92 -  + synonyms 4 views-v$thread, v$datafile, v$log
Rem     thayes     03/24/92 -  Define v$rollname in catalog.sql instead of kqfv
Rem     wmaimone   02/24/92 -  add v$mls_parameters 
Rem     mmoore     02/19/92 -  remove more v$osroles 
Rem     mmoore     02/19/92 -  remove v$enabledroles and v$osroles 
Rem     jwijaya    02/06/92 -  add v$librarycache 
Rem     mmoore     01/31/92 -  fix the user_free_space view 
Rem     rkooi      01/23/92 -  drop global naming views before creating them 
Rem     rkooi      01/23/92 -  use @@ command for subscripts 
Rem     rkooi      01/18/92 -  add synonym 
Rem     rkooi      01/18/92 -  add object_sizes views 
Rem     rkooi      01/10/92 -  fix up trigger views 
Rem     ajasuja    12/31/91 -  fix dba_audit_trail view 
Rem     ajasuja    12/30/91 -  audit EXISTS 
Rem     amendels   12/23/91 -  simplify *_clusters as clu$.hashkeys cannot be n
Rem     amendels   12/23/91 -  fix *_clusters views for hashing 
Rem     agupta     12/23/91 -  89036 - dba_ts_quotas 
Rem     rkooi      12/15/91 -  change 'triggering_statement' to 'trigger_body' 
Rem     ajasuja    11/27/91 -  add system privilege auditing 
Rem     amendels   11/26/91 -  modify user/dba_clusters for hash cluster 
Rem     ghallmar   11/08/91 -  add GLOBAL_NAME view 
Rem     rjenkins   11/07/91 -  commenting snapshots 
Rem     ltan       12/02/91 -  add inst# to undo$ 
Rem     mroberts   10/30/91 -  apply error view changes (for views) to IMRG 
Rem     rkooi      10/20/91 -  add public_dependency, fix priv checking
Rem                            on all_objects
Rem     smcadams   10/19/91 -  tweak audit_action table 
Rem                            add execute obj audit option to audit views
Rem                            add new_owner to dba_audit_trail
Rem     mroberts   10/14/91 -  add v$nls_parameters view 
Rem     mroberts   10/11/91 -  put VIEW changes in the mainline 
Rem     jcleland   10/11/91 -  add mac privileges to sys_priv_map 
Rem     epeeler    10/10/91 -  add enabled status columns to constraint views 
Rem     cheigham   10/03/91 -  remove extra ;'s 
Rem     mmoore     09/18/91 - #(74112) add dba_roles view to show all roles 
Rem     agupta     09/03/91 -  add sequence# to tabauth$ 
Rem     mmoore     09/03/91 -  change trigger view column names again 
Rem     ghallmar   08/12/91 -  global naming 
Rem     amendels   08/29/91 -  fix dict_columns: 'ALL$' -> 'ALL%'
Rem     rlim       08/22/91 -  add comments regarding dba synonyms 
Rem     mmoore     08/17/91 - #77458  change trigger views 
Rem     mmoore     08/01/91 -  merge changes from branch 1.59.100.1 
Rem     mmoore     08/01/91 -  move column_privileges back  
Rem     rlim       07/31/91 -  added remarks column to syscatalog & catalog
Rem     rlim       07/30/91 -  moved dba synonyms to dba_synonyms.sql 
Rem     mmoore     07/22/91 - #65139  fix bug in user_tablespaces 
Rem     jwijaya    07/14/91 -  remove unnecessary LINKNAME IS NULL
Rem   mmoore     07/08/91 - change trigger view column names 
Rem   amendels   07/02/91 - remove change to *_constraints.constraint_type
Rem   mmoore     06/28/91 - move table_privileges back in 
Rem   ltan       06/24/91 - bug 65188,add comment on DBA_ROLLBACK_SEGS.BLOCK_ID
Rem   mmoore     06/24/91 - move table and column_privileges to catalog6
Rem   ghallmar   06/11/91 -         new improved 2PC views 
Rem   amendels   06/10/91 - move obsolete sql2 views to catalog6.sql;
Rem                       - remove decodes for type 97;
Rem                       - union -> union all;
Rem                       - improve *_constraints.constraint_type (66063)
Rem   mmoore     06/10/91 - add grantable column to privilege views
Rem   smcadams   06/09/91 - add actions to audit_actions
Rem   mmoore     06/03/91 - change user$ column names 
Rem   agupta     06/07/91 - syntax error in exp_objects view 
Rem   rkooi      10/22/91 - deleted lots of comments (co truncate bug)
Rem   Grayson    03/21/88 - Creation

remark
remark	FAMILY "FIXED (VIRTUAL) VIEWS"
remark               

create or replace view v_$controlfile as select * from v$controlfile;
drop public synonym v$controlfile;
create public synonym v$controlfile for v_$controlfile;

create or replace view v_$datafile as select * from v$datafile;
drop public synonym v$datafile;
create public synonym v$datafile for v_$datafile;

create or replace view v_$log as select * from v$log;
drop public synonym v$log;
create public synonym v$log for v_$log;

create or replace view v_$thread as select * from v$thread;
drop public synonym v$thread;
create public synonym v$thread for v_$thread;

create or replace view v_$process as select * from v$process;
drop public synonym v$process;
create public synonym v$process for v_$process;

create or replace view v_$bgprocess as select * from v$bgprocess;
drop public synonym v$bgprocess;
create public synonym v$bgprocess for v_$bgprocess;

create or replace view v_$session as select * from v$session;
drop public synonym v$session;
create public synonym v$session for v_$session;

create or replace view v_$license as select * from v$license;
drop public synonym v$license;
create public synonym v$license for v_$license;

create or replace view v_$transaction as select * from v$transaction;
drop public synonym v$transaction;
create public synonym v$transaction for v_$transaction;

create or replace view v_$latch as select * from v$latch;
drop public synonym v$latch;
create public synonym v$latch for v_$latch;

create or replace view v_$latchname as select * from v$latchname;
drop public synonym v$latchname;
create public synonym v$latchname for v_$latchname;

create or replace view v_$latchholder as select * from v$latchholder;
drop public synonym v$latchholder;
create public synonym v$latchholder for v_$latchholder;

create or replace view v_$resource as select * from v$resource;
drop public synonym v$resource;
create public synonym v$resource for v_$resource;

create or replace view v_$_lock as select * from v$_lock;
drop public synonym v$_lock;
create public synonym v$_lock for v_$_lock;

create or replace view v_$lock as select * from v$lock;
drop public synonym v$lock;
create public synonym v$lock for v_$lock;

create or replace view v_$sesstat as select * from v$sesstat;
drop public synonym v$sesstat;
create public synonym v$sesstat for v_$sesstat;

create or replace view v_$sysstat as select * from v$sysstat;
drop public synonym v$sysstat;
create public synonym v$sysstat for v_$sysstat;

create or replace view v_$statname as select * from v$statname;
drop public synonym v$statname;
create public synonym v$statname for v_$statname;

create or replace view v_$access as select * from v$access;
drop public synonym v$access;
create public synonym v$access for v_$access;

create or replace view v_$dbfile as select * from v$dbfile;
drop public synonym v$dbfile;
create public synonym v$dbfile for v_$dbfile;

create or replace view v_$filestat as select * from v$filestat;
drop public synonym v$filestat;
create public synonym v$filestat for v_$filestat;

create or replace view v_$logfile as select * from v$logfile;
drop public synonym v$logfile;
create public synonym v$logfile for v_$logfile;

create or replace view v_$rollname as select x$kturd.kturdusn usn,undo$.name
   from x$kturd, undo$
   where x$kturd.kturdusn=undo$.us# and x$kturd.kturdsiz!=0;
drop public synonym v$rollname;
create public synonym v$rollname for v_$rollname;

create or replace view v_$rollstat as select * from v$rollstat;
drop public synonym v$rollstat;
create public synonym v$rollstat for v_$rollstat;

create or replace view v_$sga as select * from v$sga;
drop public synonym v$sga;
create public synonym v$sga for v_$sga;

create or replace view v_$parameter as select * from v$parameter;
drop public synonym v$parameter;
create public synonym v$parameter for v_$parameter;

create or replace view v_$rowcache as select * from v$rowcache;
drop public synonym v$rowcache;
create public synonym v$rowcache for v_$rowcache;

create or replace view v_$enabledprivs as select * from v$enabledprivs;
drop public synonym v$enabledprivs;
create public synonym v$enabledprivs for v_$enabledprivs;

create or replace view v_$nls_parameters as select * from v$nls_parameters;
drop public synonym v$nls_parameters;
create public synonym v$nls_parameters for v_$nls_parameters;
grant select on v_$nls_parameters to public;

create or replace view v_$nls_valid_values as
select * from v$nls_valid_values;
drop public synonym v$nls_valid_values;
create public synonym v$nls_valid_values for v_$nls_valid_values;
grant select on v_$nls_valid_values to public;

create or replace view v_$mls_parameters as select * from v$parameter
where name like 'mls%';
drop public synonym v$mls_parameters;
create public synonym v$mls_parameters for v_$mls_parameters;
grant select on v_$mls_parameters to public;

create or replace view v_$librarycache as select * from v$librarycache;
drop public synonym v$librarycache;
create public synonym v$librarycache for v_$librarycache;

create or replace view v_$type_size as select * from v$type_size;
drop public synonym v$type_size;
create public synonym v$type_size for v_$type_size;

create or replace view v_$archive as select * from v$archive;
drop public synonym v$archive;
create public synonym v$archive for v_$archive;

create or replace view v_$circuit as select * from v$circuit;
drop public synonym v$circuit;
create public synonym v$circuit for v_$circuit;

create or replace view v_$database as select * from v$database;
drop public synonym v$database;
create public synonym v$database for v_$database;

create or replace view v_$dispatcher as select * from v$dispatcher;
drop public synonym v$dispatcher;
create public synonym v$dispatcher for v_$dispatcher;

create or replace view v_$loghist as select * from v$loghist;
drop public synonym v$loghist;
create public synonym v$loghist for v_$loghist;

REM create or replace view v_$plsarea as select * from v$plsarea;
drop public synonym v$plsarea;
REM create public synonym v$plsarea for v_$plsarea;

create or replace view v_$sqlarea as select * from v$sqlarea;
drop public synonym v$sqlarea;
create public synonym v$sqlarea for v_$sqlarea;

create or replace view v_$db_object_cache as select * from v$db_object_cache;
drop public synonym v$db_object_cache;
create public synonym v$db_object_cache for v_$db_object_cache;

create or replace view v_$open_cursor as select * from v$open_cursor;
drop public synonym v$open_cursor;
create public synonym v$open_cursor for v_$open_cursor;

create or replace view v_$option as select * from v$option;
drop public synonym v$option;
create public synonym v$option for v_$option;
grant select on v_$option to public;

create or replace view v_$version as select * from v$version;
drop public synonym v$version;
create public synonym v$version for v_$version;
grant select on v_$version to public;

create or replace view v_$pq_sesstat as select * from v$pq_sesstat;
drop public synonym v$pq_sesstat;
create public synonym v$pq_sesstat for v_$pq_sesstat;
grant select on v_$pq_sesstat to public;

create or replace view v_$pq_sysstat as select * from v$pq_sysstat;
drop public synonym v$pq_sysstat;
create public synonym v$pq_sysstat for v_$pq_sysstat;

create or replace view v_$pq_slave as select * from v$pq_slave;
drop public synonym v$pq_slave;
create public synonym v$pq_slave for v_$pq_slave;

create or replace view v_$queue as select * from v$queue;
drop public synonym v$queue;
create public synonym v$queue for v_$queue;

create or replace view v_$mts as select * from v$mts;
drop public synonym v$mts;
create public synonym v$mts for v_$mts;

create or replace view v_$dblink as select * from v$dblink;
drop public synonym v$dblink;
create public synonym v$dblink for v_$dblink;

create or replace view v_$reqdist as select * from v$reqdist;
drop public synonym v$reqdist;
create public synonym v$reqdist for v_$reqdist;

create or replace view v_$sgastat as select * from v$sgastat;
drop public synonym v$sgastat;
create public synonym v$sgastat for v_$sgastat;

create or replace view v_$waitstat as select * from v$waitstat;
drop public synonym v$waitstat;
create public synonym v$waitstat for v_$waitstat;

create or replace view v_$shared_server as select * from v$shared_server;
drop public synonym v$shared_server;
create public synonym v$shared_server for v_$shared_server;

create or replace view v_$timer as select * from v$timer;
drop public synonym v$timer;
create public synonym v$timer for v_$timer;

create or replace view v_$recover_file as select * from v$recover_file;
drop public synonym v$recover_file;
create public synonym v$recover_file for v_$recover_file;

create or replace view v_$backup as select * from v$backup;
drop public synonym v$backup;
create public synonym v$backup for v_$backup;

create or replace view v_$log_history as select * from v$log_history;
drop public synonym v$log_history;
create public synonym v$log_history for v_$log_history;

create or replace view v_$recovery_log as select * from v$recovery_log;
drop public synonym v$recovery_log;
create public synonym v$recovery_log for v_$recovery_log;

create or replace view v_$fixed_table as select * from v$fixed_table;
drop public synonym v$fixed_table;
create public synonym v$fixed_table for v_$fixed_table;

create or replace view v_$session_cursor_cache as 
  select * from v$session_cursor_cache;
drop public synonym v$session_cursor_cache;
create public synonym v$session_cursor_cache for v_$session_cursor_cache;

create or replace view v_$session_wait as 
  select * from v$session_wait;
drop public synonym v$session_wait;
create public synonym v$session_wait for v_$session_wait;

create or replace view v_$session_event as 
  select * from v$session_event;
drop public synonym v$session_event;
create public synonym v$session_event for v_$session_event;

create or replace view v_$system_event as 
  select * from v$system_event;
drop public synonym v$system_event;
create public synonym v$system_event for v_$system_event;

create or replace view v_$system_cursor_cache as 
  select * from v$system_cursor_cache;
drop public synonym v$system_cursor_cache;
create public synonym v$system_cursor_cache for v_$system_cursor_cache;

create or replace view v_$sess_io as
  select * from v$sess_io;
drop public synonym v$sess_io;
create public synonym v$sess_io for v_$sess_io;

create or replace view v_$compatibility as
  select * from v$compatibility;
drop public synonym v$compatibility;
create public synonym v$compatibility for v_$compatibility;

create or replace view v_$compatseg as
  select * from v$compatseg;
drop public synonym v$compatseg;
create public synonym v$compatseg for v_$compatseg;


drop synonym system.syscatalog;
create synonym system.syscatalog for syscatalog;
rem
rem The catalog view returns almost all tables accessible to the user
rem except tables in SYS and SYSTEM ("dictionary tables").
rem 
create or replace view catalog (tname, creator, tabletype, remarks) as
  select tname, creator, tabletype, remarks
  from	syscatalog_
  where creatorid not in (0,2)
/
grant select on catalog to public with grant option;
drop synonym system.catalog;
create synonym system.catalog for catalog;

create or replace view tab (tname, tabtype, clusterid) as
   select o.name,
      decode(o.type, 2, 'TABLE', 3, 'CLUSTER', 
             4, 'VIEW', 5, 'SYNONYM'), t.tab#
  from  sys.tab$ t, sys.obj$ o
  where o.owner# = userenv('SCHEMAID')
  and o.type >=2
  and o.type <=5
  and o.linkname is null
  and o.obj# = t.obj# (+)
/
grant select on tab to public with grant option;
drop synonym system.tab;
create synonym system.tab for tab;
drop public synonym tab;
create public synonym tab for tab;
create or replace view col 
  (tname, colno, cname, coltype, width, scale, precision, nulls, defaultval) as
  select t.name, c.col#, c.name, 
	 decode(c.type#, 1, 'VARCHAR2',
                         2, decode(c.scale, null,
                                   decode(c.precision, null, 'NUMBER', 'FLOAT'),
                                  'NUMBER'),
                         8, 'LONG', 9, 'VARCHAR',
                         12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                         69, 'ROWID',96,'CHAR', 105, 'MLSLABEL',
                         106, 'MLSLABEL', 'UNDEFINED'),
         c.length, c.scale, c.precision,
	 decode(sign(c.null$),-1,'NOT NULL - DISABLED', 0, 'NULL', 
	'NOT NULL'), c.default$
  from  sys.col$ c, sys.obj$ t
  where t.obj# = c.obj#
  and   t.type in (2, 3, 4)
  and   t.owner# = userenv('SCHEMAID')
/
grant select on col to public with grant option;
drop synonym system.col;
create synonym system.col for col;
drop public synonym col;
create public synonym col for col;
create or replace view syssegobj 
    (obj#, file#, block#, type, pctfree$, pctused$) as
  select obj#, file#, block#, 'TABLE', pctfree$, pctused$ from sys.tab$
  union all
  select obj#, file#, block#, 'CLUSTER', pctfree$, pctused$ from sys.clu$
  union all
  select obj#, file#, block#, 'INDEX', to_number(null), to_number(null)
         from sys.ind$
/
grant select on syssegobj to public with grant option;
create or replace view tabquotas (tname, type, objno, nextext, maxext, pinc,
		       pfree, pused) as
  select t.name, so.type, t.obj#, s.extsize, s.maxexts, s.extpct, so.pctfree$,
	 so.pctused$
  from  sys.seg$ s, sys.obj$ t, syssegobj so
  where t.owner# = userenv('SCHEMAID')
  and   t.obj# = so.obj#
  and   so.file# = s.file# 
  and   so.block# = s.block#
/
grant select on tabquotas to public with grant option;
drop synonym system.tabquotas;
create synonym system.tabquotas for tabquotas;

create or replace view sysfiles (tsname, fname, blocks) as
  select ts.name, dbf.name, f.blocks
  from  sys.ts$ ts, sys.file$ f, sys.v$dbfile dbf
  where ts.ts# = f.ts#(+) and dbf.file# = f.file# and f.status$ = 2
/
grant select on sysfiles to public with grant option;
drop synonym system.sysfiles;
create synonym system.sysfiles for sysfiles;
create or replace view synonyms 
    (sname, syntype, creator, tname, database, tabtype) as
  select s.name, 
	 decode(s.owner#,1,'PUBLIC','PRIVATE'), t.owner, t.name, 'LOCAL', 
	 decode(ot.type, 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW',
			 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
                         8, 'FUNCTION', 9, 'PACKAGE', 'UNDEFINED')
  from  sys.obj$ s, sys.obj$ ot, sys.syn$ t, sys.user$ u
  where s.obj# = t.obj# 
    and ot.linkname is null
    and s.type = 5
    and ot.name = t.name
    and t.owner = u.name
    and ot.owner# = u.user#
    and s.owner# in (1,userenv('SCHEMAID'))
    and t.node is null
union all
  select s.name, decode(s.owner#, 1, 'PUBLIC', 'PRIVATE'),
	 t.owner, t.name, t.node, 'REMOTE'
  from  sys.obj$ s, sys.syn$ t
  where s.obj# = t.obj#
    and s.type = 5
    and s.owner# in (1, userenv('SCHEMAID'))
    and t.node is not null
/  
grant select on synonyms to public with grant option;
create or replace view publicsyn (sname, creator, tname, database, tabtype) as
  select sname, creator, tname, database, tabtype
  from  synonyms
  where syntype = 'PUBLIC'
/
grant select on publicsyn to public with grant option;
drop synonym system.publicsyn;
create synonym system.publicsyn for publicsyn;

rem 
rem V6 views required for other Oracle products
rem

create or replace view TABLE_PRIVILEGES
      (GRANTEE, OWNER, TABLE_NAME, GRANTOR,
       SELECT_PRIV, INSERT_PRIV, DELETE_PRIV, 
       UPDATE_PRIV, REFERENCES_PRIV, ALTER_PRIV, INDEX_PRIV,
       CREATED)
as
select ue.name, u.name, o.name, ur.name,
    decode(substr(lpad(sum(power(10, privilege#*2) + 
      decode(option$, null, 0, power(10, privilege#*2 + 1))), 26, '0'), 7, 2), 
      '00', 'N', '01', 'Y', '11', 'G', 'N'),
     decode(substr(lpad(sum(decode(col#, null, power(10, privilege#*2) + 
       decode(option$, null, 0, power(10, privilege#*2 + 1)), 0)), 26, '0'), 
              13, 2), '01', 'A', '11', 'G', 
         decode(substr(lpad(sum(decode(col#, null, 0, power(10, privilege#))),
                12, '0'), 6, 1), '0', 'N', 'S')),
    decode(substr(lpad(sum(power(10, privilege#*2) + 
      decode(option$, null, 0, power(10, privilege#*2 + 1))), 26, '0'), 19, 2), 
      '00', 'N', '01', 'Y', '11', 'G', 'N'),
    decode(substr(lpad(sum(decode(col#, null, power(10, privilege#*2) + 
      decode(option$, null, 0, power(10, privilege#*2 + 1)), 0)), 26, '0'), 
             5, 2),'01', 'A', '11', 'G', 
          decode(substr(lpad(sum(decode(col#, null, 0, power(10, privilege#))),
                 12, '0'), 2, 1), '0', 'N', 'S')),
    decode(substr(lpad(sum(decode(col#, null, power(10, privilege#*2) + 
      decode(option$, null, 0, power(10, privilege#*2 + 1)), 0)), 26, '0'), 
             3, 2), '01', 'A', '11', 'G', 
          decode(substr(lpad(sum(decode(col#, null, 0, power(10, privilege#))),
                 12, '0'), 1, 1), '0', 'N', 'S')),
   decode(substr(lpad(sum(power(10, privilege#*2) + 
      decode(option$, null, 0, power(10, privilege#*2 + 1))), 26, '0'), 25, 2), 
      '00', 'N', '01', 'Y', '11', 'G', 'N'), 
    decode(substr(lpad(sum(power(10, privilege#*2) + 
      decode(option$, null, 0, power(10, privilege#*2 + 1))), 26, '0'), 15, 2), 
      '00', 'N', '01', 'Y', '11', 'G', 'N'), min(null)
from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and u.user# = o.owner#
  and (oa.grantor# = userenv('SCHEMAID') or
       oa.grantee# in (select kzsrorol from x$kzsro) or
       o.owner# = userenv('SCHEMAID'))
  group by u.name, o.name, ur.name, ue.name
/
comment on table TABLE_PRIVILEGES is
'Grants on objects for which the user is the grantor, grantee, owner, 
 or an enabled role or PUBLIC is the grantee'
/
comment on column TABLE_PRIVILEGES.GRANTEE is
'Name of the user to whom access was granted'
/
comment on column TABLE_PRIVILEGES.OWNER is
'Owner of the object'
/
comment on column TABLE_PRIVILEGES.TABLE_NAME is
'Name of the object'
/
comment on column TABLE_PRIVILEGES.GRANTOR is
'Name of the user who performed the grant'
/
comment on column TABLE_PRIVILEGES.SELECT_PRIV is
'Permission to SELECT from the object?'
/
comment on column TABLE_PRIVILEGES.INSERT_PRIV is
'Permission to INSERT into the object?'
/
comment on column TABLE_PRIVILEGES.DELETE_PRIV is
'Permission to DELETE from the object?'
/
comment on column TABLE_PRIVILEGES.UPDATE_PRIV is
'Permission to UPDATE the object?'
/
comment on column TABLE_PRIVILEGES.REFERENCES_PRIV is
'Permission to make REFERENCES to the object?'
/
comment on column TABLE_PRIVILEGES.ALTER_PRIV is
'Permission to ALTER the object?'
/
comment on column TABLE_PRIVILEGES.INDEX_PRIV is
'Permission to create/drop an INDEX on the object?'
/
comment on column TABLE_PRIVILEGES.CREATED is
'Timestamp for the grant'
/
drop public synonym TABLE_PRIVILEGES
/
create public synonym TABLE_PRIVILEGES for TABLE_PRIVILEGES
/
grant select on TABLE_PRIVILEGES to PUBLIC
/
create or replace view COLUMN_PRIVILEGES
      (GRANTEE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTOR,
       INSERT_PRIV, UPDATE_PRIV, REFERENCES_PRIV,
       CREATED)
as
select ue.name, u.name, o.name, c.name, ur.name,
    decode(substr(lpad(sum(power(10, privilege#*2) + 
      decode(option$, null, 0, power(10, privilege#*2 + 1))), 26, '0'), 13, 2), 
      '00', 'N', '01', 'Y', '11', 'G', 'N'),
    decode(substr(lpad(sum(power(10, privilege#*2) + 
      decode(option$, null, 0, power(10, privilege#*2 + 1))), 26, '0'), 5, 2), 
      '00', 'N', '01', 'Y', '11', 'G', 'N'),
    decode(substr(lpad(sum(power(10, privilege#*2) + 
      decode(option$, null, 0, power(10, privilege#*2 + 1))), 26, '0'), 3, 2), 
      '00', 'N', '01', 'Y', '11', 'G', 'N'), min(null)
from sys.objauth$ oa, sys.col$ c,sys.obj$ o, sys.user$ u, sys.user$ ur, 
     sys.user$ ue
where oa.col# is not null
  and oa.obj# = c.obj#
  and oa.col# = c.col#
  and oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and u.user# = o.owner#
  and (oa.grantor# = userenv('SCHEMAID') or
       oa.grantee# in (select kzsrorol from x$kzsro) or
       o.owner# = userenv('SCHEMAID'))
  group by u.name, o.name, c.name, ur.name, ue.name
/
comment on table COLUMN_PRIVILEGES is
'Grants on columns for which the user is the grantor, grantee, owner, or 
 an enabled role or PUBLIC is the grantee'
/
comment on column COLUMN_PRIVILEGES.GRANTEE is
'Name of the user to whom access was granted'
/
comment on column COLUMN_PRIVILEGES.OWNER is
'Username of the owner of the object'
/
comment on column COLUMN_PRIVILEGES.TABLE_NAME is
'Name of the object'               
/
comment on column COLUMN_PRIVILEGES.COLUMN_NAME is
'Name of the column'
/
comment on column COLUMN_PRIVILEGES.GRANTOR is
'Name of the user who performed the grant'
/
comment on column COLUMN_PRIVILEGES.INSERT_PRIV is
'Permission to INSERT into the column?'
/
comment on column COLUMN_PRIVILEGES.UPDATE_PRIV is
'Permission to UPDATE the column?'
/
comment on column COLUMN_PRIVILEGES.REFERENCES_PRIV is
'Permission to make REFERENCES to the column?'
/
comment on column COLUMN_PRIVILEGES.CREATED is
'Timestamp for the grant'
/
drop public synonym COLUMN_PRIVILEGES
/
create public synonym COLUMN_PRIVILEGES for COLUMN_PRIVILEGES
/
grant select on COLUMN_PRIVILEGES to PUBLIC
/

rem	**********************************************************************
rem		DBA TWO PHASE COMMIT DECISION / DAMAGE ASSESSMENT TABLES
rem	**********************************************************************
rem 	PSS1$: used to add user name column to pending_sub_sessions$
create or replace view pss1$ as
select	pss.*, u.name owner_name
from 	sys.pending_sub_sessions$ pss, sys.user$ u
where   pss.link_owner = u.user#;

rem 	PS1$: used to add user name column to pending_sessions$
create or replace view ps1$ as
select	ps.*, u.name db_user
from 	sys.pending_sessions$ ps, sys.user$ u
where   ps.db_userid = u.user#;

rem 	DBA_2PC_PENDING
rem	use this view to find info about pending (i.e. incomplete) distributed
rem	transactions at this DB.  Use os_user and db_userid to help track down
rem	a responsible party.  Use DBA_2PC_NEIGHBORS to find the commit point.
rem	Or take the advice, if offered.

create or replace view DBA_2PC_PENDING
    (local_tran_id, global_tran_id, state, mixed,
     advice, tran_comment, fail_time, force_time,
     retry_time, os_user, os_terminal, host, db_user, commit#) as
select  local_tran_id,
        nvl(global_oracle_id, global_tran_fmt||'.'||global_foreign_id),
	state, decode(status,'D','yes','no'), heuristic_dflt, tran_comment,
        fail_time, heuristic_time, reco_time,
        top_os_user, top_os_terminal, top_os_host, top_db_user, global_commit#
from	sys.pending_trans$;

comment on table DBA_2PC_PENDING is
  'info about distributed transactions awaiting recovery';
comment on column DBA_2PC_PENDING.local_tran_id is
  'string of form: n.n.n, n a number';
comment on column DBA_2PC_PENDING.global_tran_id is
  'globally unique transaction id';
comment on column DBA_2PC_PENDING.state is
  'collecting, prepared, committed, forced commit, or forced rollback';
comment on column DBA_2PC_PENDING.mixed is
  'yes => part of the transaction committed and part rolled back (commit or rollback with the FORCE option was used)';
comment on column DBA_2PC_PENDING.advice is
  'C for commit, R for rollback, else null';
comment on column DBA_2PC_PENDING.tran_comment is
  'text for "commit work comment <text>"';
comment on column DBA_2PC_PENDING.fail_time is
  'value of SYSDATE when the row was inserted (tx or system recovery)';
comment on column DBA_2PC_PENDING.force_time is
 'time of manual force decision (null if not forced locally)';
comment on column DBA_2PC_PENDING.retry_time is
 'time automatic recovery (RECO) last tried to recover the transaction';
comment on column DBA_2PC_PENDING.os_user is
  'operating system specific name for the end-user';
comment on column DBA_2PC_PENDING.os_terminal is
  'operating system specific name for the end-user terminal';
comment on column DBA_2PC_PENDING.host is
  'name of the host machine for the end-user';
comment on column DBA_2PC_PENDING.db_user is
  'Oracle user name of the end-user at the topmost database';
comment on column DBA_2PC_PENDING.commit# is
  'global commit number for committed transactions';

rem	DBA_2PC_NEIGHBORS: use this view to obtain info about incoming and
rem	  outgoing connections for a particular transaction.  It is suggested
rem 	  that it be queried using:
rem	    select * from dba_2pc_neighbors where local_tran_id = <id>
rem	     order by sess#, "IN_OUT";
rem	  This will group sessions, with outgoing connections following the
rem	  incoming connection for each session.
rem   columns:
rem	IN_OUT: 'in' for incoming connections, 'out' for outgoing
rem 	DATABASE: if 'in', the name of the client database, else name of 
rem	  outgoing db link
rem	DBUSER_OWNER: if 'in', name of local user, else owner of db link
rem	INTERFACE: 'C' hold commit, else 'N'.  For incoming links, 'C'
rem 	  means that we or a DB at the other end of one of our outgoing links
rem       is the commit point (and must not forget until told by the client).
rem	  For outgoing links, 'C' means that the child at the other end is the
rem	  commit point, and will know whether the tran should commit or abort.
rem	  If we are indoubt and do not find a 'C' on an outgoing link, then
rem	  the top level user/DB, or the client, should be able to locate the
rem	  commit point.
rem	DBID: the database id at the other end of the connection
rem	SESS#: session number at this database of the connection.  Sessions are
rem 	  numbered consecutively from 1; there is always at least 1 session, 
rem	  and exactly 1 incoming connection per session.
rem	BRANCH_ID: transaction branch.  An incoming branch is a two byte
rem	  hexadecimal number.  The first byte is the session_id of the
rem	  remote parent session.  The second byte is the branch_id of the
rem	  remote parent session.  If the remote parent session is not Oracle,
rem	  the branch_id can be up to 64 bytes.

create or replace view DBA_2PC_NEIGHBORS(local_tran_id, in_out, database,
                               dbuser_owner, interface, dbid, 
			       sess#, branch) as
select  local_tran_id, 'in', parent_db, db_user, interface, parent_dbid,
        session_id, rawtohex(branch_id)
from	sys.ps1$
union all
select  local_tran_id, 'out', dblink, owner_name, interface, dbid, 
        session_id, to_char(sub_session_id)
from	sys.pss1$;

comment on table DBA_2PC_NEIGHBORS is
  'information about incoming and outgoing connections for pending transactions';
comment on column DBA_2PC_NEIGHBORS.in_out is
  '"in" for incoming connections, "out" for outgoing';
comment on column DBA_2PC_NEIGHBORS.database is
  'in: client database name; out: outgoing db link';
comment on column DBA_2PC_NEIGHBORS.dbuser_owner is
  'in: name of local user; out: owner of db link';
comment on column DBA_2PC_NEIGHBORS.interface is
  '"C" for request commit, else "N" for prepare or request readonly commit';
comment on column DBA_2PC_NEIGHBORS.dbid is
  'the database id at the other end of the connection';
comment on column DBA_2PC_NEIGHBORS.sess# is
  'session number at this database of the connection';
comment on column DBA_2PC_NEIGHBORS.branch is
  'transaction branch ID at this database of the connection';
/

Rem	GLOBAL DATABASE NAME

create or replace view GLOBAL_NAME ( GLOBAL_NAME ) as
       select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'
/
comment on table GLOBAL_NAME is 'global database name'
/
comment on column GLOBAL_NAME.GLOBAL_NAME is 'global database name'
/
grant select on GLOBAL_NAME to public with grant option
/
drop public synonym GLOBAL_NAME
/
create public synonym GLOBAL_NAME for GLOBAL_NAME
/

Rem     PRODUCT COMPONENT VERSION
create or replace view product_component_version(product,version,status) as
(select
substr(banner,1, instr(banner,'Version')-1),
substr(banner, instr(banner,'Version')+8,
instr(banner,' - ')-(instr(banner,'Version')+8)),
substr(banner,instr(banner,' - ')+3)
from v$version
where instr(banner,'Version') > 0
and
((instr(banner,'Version') <   instr(banner,'Release')) or
instr(banner,'Release') = 0))
union
(select
substr(banner,1, instr(banner,'Release')-1),
substr(banner, instr(banner,'Release')+8,
instr(banner,' - ')-(instr(banner,'Release')+8)),
substr(banner,instr(banner,' - ')+3)
from v$version
where instr(banner,'Release') > 0
and
instr(banner,'Release') <   instr(banner,' - '))
/
comment on table product_component_version is 
'version and status information for component products'
/
comment on column product_component_version.product is 
'product name';
/
comment on column product_component_version.version is 
'version number'
/
comment on column product_component_version.status is 
'status of release'
/
grant select on product_component_version to public with grant option
/
drop public synonym product_component_version
/
create public synonym product_component_version for product_component_version
/

Rem Auditing views
@@cataudit

Rem Import/export views
@@catexp

Rem Loader views
@@catldr

Rem Server Manager views
@@catsvrmg

Rem ---------------------------------------------------------------------------
Rem THIS IS THE END OF THIS FILE - IF I AM NOT HERE THEN RCS HAS TRUNCATED FILE
Rem ---------------------------------------------------------------------------
