Rem 
Rem $Header: catexp.sql 19-jun-2000.13:36:10 nireland Exp $ expvew.sql 
Rem 
Rem Copyright (c) Oracle Corporation 1987, 2000. All Rights Reserved
Rem NAME
Rem    CATEXP81.SQL - CATalog EXPort/import sql script
Rem  FUNCTION
Rem    Creates internal views for Export/Import utility
Rem  NOTES
Rem    Must be run when connected to SYS or INTERNAL.
Rem
Rem    This file is organized into 3 sections:
Rem     Section 1: Views needed by BOTH export and import
Rem     Section 2: Views required by import ONLY
Rem     Section 3: Views required by export ONLY
Rem
Rem     Import does not currently require any views of its own. The views
Rem     used by both tools are moved to the top so that a user doing an import
Rem     only has to run part of this file. Since there are common views
Rem     a separate file called catimp.sql was not created for now for
Rem     maintenance reasons.
Rem
Rem     No views depend on catalog.sql. This script can be run standalone.
Rem
Rem
Rem  MODIFIED
Rem     nireland   06/07/00  - Add isonline to exu81sto
Rem     nireland   05/22/00  - Don't export constraints for ORDSYS etc.
Rem                            #1308267
Rem     prao       05/11/00  - 1206380:Grant 'analyse any' privilege to
Rem                            imp_full_database role
Rem     bmccarth   05/12/00 -  performance of exu8spsu - bug 1296644
Rem     arithikr   05/10/00 -  Bad merge, restore the file.
Rem     cchui      05/08/00 -  do not export LBACSYS in full export
Rem     jdavison   03/30/00 -  fix previous bad merge for exu8coo
Rem     nireland   03/29/00  - Fix outer join problem with exu8syn
Rem     prakumar   03/28/00 -
Rem     bmccarth   01/14/00 -  icache: check for icache_imp_plsql
Rem     nireland   01/20/00  - tempflags now obsolete
Rem     wfisher    01/17/00 -  Prevent exu8csn from seeing invalid types
Rem     nireland   12/22/99  - Fetch index partition base object #
Rem     bmccarth   11/11/99 -  incorrect merge- remove outer joints from
Rem                            exu816tgr
Rem     bmccarth   11/01/99 -  performance work on exu816tgr
Rem     bmccarth   10/13/99 -  bug 991834 - exu8orfs - remove cdef$ use
Rem     htseng     09/09/99 -  fix primary key missing nologging attribute
Rem     cchui      08/18/99 -  modify exu8coo to check for RLS hidden col
Rem     wfisher    08/17/99 -  Fixing up grant for exu816sqv
Rem     wfisher    08/04/99 -  Support sql versioning for snapshots/ update ver
Rem     wfisher    08/09/99 -  fixing exu81tts and exu81usci
Rem     nireland   07/28/99 -  Need update any table for LOBs. #861310
Rem     thoang     07/22/99  - Not using spare1 and spare2 from col$.          
Rem     emagrath   07/08/99 -  Support enhanced trigger events
Rem     dmwong     07/06/99 - add view to test for trusted oracle
Rem     mjungerm   06/15/99 - add java shared data object type
Rem     wfisher    06/24/99 -  SQL Version support
Rem     cyyip      05/26/99 -  remove special character '&'
Rem     wfisher    05/28/99 -  Speeding up EXUTTS query
Rem     nvishnub   05/04/99 -  Fix bug # 882543 (filter_non_existent_types)
Rem     nireland   04/21/99 -  Remove bogus DROP ROLE. #874826
Rem     lbarton    04/26/99 -  make export sensitive to COLLSTO COMPATIBILITY
Rem     nvishnub   04/19/99 -  Fix views to improve performance.
Rem     nireland   04/09/99 -  Cope with large tables. #867018
Rem     nvishnub   03/24/99 -  E/I support for fast rebuild of domain indexes.
Rem     wfisher    03/26/99 -  Make synonym ordering y2k ready
Rem     wfisher    03/18/99 -  Redo view dependency ordering
Rem     wfisher    03/08/99 -  TS for IOTS comes from index
Rem     wfisher    02/24/99 -  more column statistics
Rem     lbarton    03/12/99 -  change exu81javt
Rem     wfisher    02/15/99 -  Don't export precomputed statistics when associa
Rem     nvishnub   01/07/99 -  Handle indexes due to constraints correctly.
Rem     lbarton    02/22/99 -  filter cartridge-owned objects
Rem     nvishnub   02/25/99 -  Optimize view exu8dim(u).
Rem     wfisher    01/06/99 -  bug 745470: vlen needed for testing overflow
Rem     wfisher    12/21/98 -  Support associations
Rem     tlee       12/10/98 -  change privilege rewrite to query rewrite
Rem     vkarra     11/19/98 -  fix 81ind_base
Rem     masubram   11/17/98  - code review comments                            
Rem     masubram   10/13/98  - add two columns to exu81srt
Rem     mimoy      11/13/98  - Support NEVER REFRESH for snapshot in exu81snap 
Rem     wesmith    11/13/98 -  RepAPI export code review fixes                 
Rem     jingliu    11/12/98  - Fix imp/exp snapshot log related difs
Rem     lbarton    11/09/98 -  new priv: administer database trigger
Rem     wfisher    11/04/98 -  Add new privs to impexp roles
Rem     wesmith    11/02/98 -  Fix view exu81rgsu                              
Rem     jingliu    10/30/98  - Support snapshot log export for 8.1             
Rem     tlee       10/27/98 -  get compress option for piot in exu81usci
Rem     wesmith    10/20/98 -  Modify view exu81snap to nvl() flavor_id
Rem     wesmith    10/15/98 -  Add support for export of RepAPI snapshots
Rem     tlee       10/13/98 -  support adt attribute column on partition keys
Rem     avaradar   10/07/98  - get property value in exu8col                   
Rem     nvishnub   10/20/98 -  Enumerate partitioned constraints indices.
Rem     tlee       10/02/98 -  get subpart storage for transportable tablespace
Rem     avaradar   09/28/98  - compare intcol# in exu8spok,exu8poki,exu8pok
Rem     wfisher    09/08/98 -  Specify default histograms if no histograms exis
Rem     avaradar   09/07/98 -  Hidden column support for snapshots   
Rem     lbarton    09/09/98 -  Filter types owned by cartridges
Rem     tlee       09/01/98 -  transportable tablespace with nested table
Rem     nvishnub   08/26/98 -  Filter special schema objects.
Rem     tlee       08/25/98 -  transportable tablespace with iot
Rem     tlee       08/25/98 -  update defbufpool of default level 
Rem     whuang     08/19/98 -  fake index
Rem     nvishnub   08/05/98 -  E/I of primary key refs.
Rem     nvishnub   07/29/98 -  Filter datetime interval types.
Rem     nireland   07/28/98 -  Correctly identify constraint indices. #686272
Rem     lbarton    08/04/98 -  use new dbms_java names
Rem     tlee       07/22/98 -  remove ts_type from exupds, update exu8lob 
Rem     lbarton    07/13/98 -  lrid downgrade support
Rem     nvishnub   07/13/98 -  Add tablespace_type to storage info.
Rem     lbarton    06/19/98 -  Java longname support
Rem     tlee       06/14/98 -  lob and varray as lob partitioned obj support
Rem     tlee       06/10/98 -  tspitr support of new partitioning 
Rem     amsrivas   06/07/98 -  Bug 536970
Rem     gclaborn   06/08/98 -  Add system procedural object and action support
Rem     nvishnub   06/05/98 -  Put back lob-index storage clause.
Rem     gclaborn   06/04/98 -  Separate new export tables
Rem     asurpur    06/03/98 -  Adding changes to exu8grs
Rem     asurpur    06/02/98 -  Changing view exu8spv to not export some privile
Rem     gclaborn   06/02/98 -  Update operator / indextype support
Rem     lbarton    05/26/98 -  javasnm$ has changed
Rem     nvishnub   05/20/98 -  Fix exu8ink to not to include 0 cols.
Rem     gclaborn   05/08/98 -  Add views on exppkgs$ / expdep$: Subset exu8tab
Rem                            & exu8typ from 81 versions; no secondary obj.
Rem     tlee       05/07/98 -  fix exu8pds and update tabcompart$ changes
Rem     dmwong     05/07/98  - require select_catalog_role for exu81rls
Rem     nvishnub   05/01/98 -  Lob storage for varrays.
Rem     ayalaman   05/01/98 -  Key compression : add preccnt to exu81ind
Rem     nvishnub   04/29/98 -  Nested table enhancements.
Rem     hasun      04/28/98  - Exclude 8.1 snapshots from V8.0.X export        
Rem     wfisher    04/27/98 -  Adding support for flags
Rem     nvishnub   04/21/98 -  Support for bitmapped tablespaces.
Rem     lbarton    04/16/98 -  filter system events from pre8.1 triggers
Rem     wfisher    04/15/98 -  Support Dimensions
Rem     dmwong     04/15/98  - add sys.exu81rls for fine grain access control
Rem     smuthuli   04/13/98 -  bug 487555.roll forward from 805
Rem     tlee       04/13/98 -  rename comppart$->tabcompart$ add indcompart$ 
Rem     gclaborn   04/07/98 -  Fetch implementation type details in exu81doi
Rem     dmwong     04/02/98  - add support for application context             
Rem     ayalaman   03/27/98 -  use 2 bytes of pctthres for guess quality
Rem     sparrokk   03/18/98 -  621964: EXECUTE ANY TYPE for exp/imp roles
Rem     nvishnub   03/13/98 -  Fix view exu8iov to use bitand.
Rem     nvishnub   03/11/98 -  E/I of partitioned IOTs.
Rem     wfisher    03/02/98 -  Save raw analyze statistics at export
Rem     lbarton    02/23/98 -  changes for java
Rem     vkarra     02/12/98 -  single table cluster
Rem     tlee       02/09/98 -  81 partitioned object support
Rem     nireland   02/06/98 -  SYS_NC_ROWINFO$ is 0x200 in sys.col$. #606078
Rem     nvishnub   12/17/97 -  Support for datetime-interval datatypes.
Rem     cfreiwal   02/24/98 -  key compression : add preccnt to exu8uscu
Rem     thoang     12/11/97 -  Updated views to exclude unused columns
Rem     gclaborn   12/19/97 -  Filter 2ndary objects, add oper/indextype sup.
Rem     wfisher    12/02/97 -  Merge from wfisher_catexp804 in 8.0.4
Rem     gclaborn   12/02/97 -  Add Functional / Domain Index support
Rem     wesmith    11/21/97  - Correct grant of snapshot view                  
Rem     wesmith    11/20/97 -  add 8.1 views to support aggregate snapshots
Rem     mdepledg   10/08/97 -  add spare1 to exu8tab
Rem     wfisher    09/19/97 -  Bug 547977 -- support parallel for indexes
Rem     bmoy       09/16/97 -  fix imp8repcat view.
Rem     wfisher    09/16/97 -  Allow export of privs and role grants to public
Rem     wfisher    08/27/97 -  fix exu8opt definition
Rem     wfisher    08/26/97 -  Add exu8opt
Rem     bmoy       07/03/97 -  Fix imp8repcat, add check for user#.
Rem     jpearson   06/20/97 -  bug 498610 - view text and trigger action sizes
Rem     jstenois   06/12/97 -  distributed security domains in export
Rem     wfisher    06/12/97 -  Support trusted links
Rem     jstenois   06/06/97 -  recover from V$OPTIONS changes
Rem     rsarwal    05/29/97 -  Remove Echo
Rem     gdoherty   05/10/97 -  remove blank lines that break sqlplus
Rem     bmoy       05/05/97 -  Fix bug 454318.
Rem     jstenois   04/17/97 -  support trigger with different owner than table
Rem     bmoy       03/27/97 -  Replication support for Imp, added imp8repcat 
Rem     jpearson   04/01/97 -  fix index on nested_table_id col of inner table
Rem     jpearson   03/11/97 -  partitioned cache support
Rem     wfisher    03/25/97 -  adding exu8csn and exu8csnu (scalar nested table
Rem     jstenois   03/21/97 -  get account status from user$
Rem     jpearson   02/13/97 -  fix date specifications
Rem     gdoherty   01/30/97 -  Get dataobj# in exu8lob for PITR
Rem     syeung     11/14/96 -  Snapshot DDL: grant exu8glob to public
Rem     adowning   10/08/96 -  fix snapshot views
Rem     syeung     08/29/96 -  snapshot ddl
Rem     wfisher    11/08/96 -  exu8tab(u) and exu8cset are used by import too
Rem     wfisher    10/31/96 -  SYS_NC_SETID$ -> NESTED_TABLE_ID
Rem     jpearson   10/29/96 -  bitmap indexes on inner nested tables
Rem     gdoherty   10/22/96 -  add dobjid to exu8tbp and exu8ixp
Rem     jpearson   10/22/96 -  export views in dependency order
Rem     syeung     10/07/96 -  bug 374657: merge bitmap index fix from 7.3
Rem     syeung     10/03/96 -  bump up EXPORT_VIEWS_VERSION for 8.0.2
Rem     syeung     09/25/96 -  nchar support
Rem     echong     09/26/96 -  modify exu8ink to handle ADTs
Rem     echong     09/17/96 -  define exu8ink for iots
Rem     jpearson   09/17/96 -  fix exu8tabi modified flag
Rem     jpearson   09/13/96 -  fix exu8lob view
Rem     syeung     09/11/96 -  password management
Rem     ixhu       07/31/96 -  dataobj# and tabno for point-in-time recovery
Rem     jpearson   09/11/96 -  modify ref scope handling
Rem     jpearson   08/30/96 -  fix column comments on extent tables and views
Rem     jpearson   08/28/96 -  fix cache flag in exu8tab and exu8clu
Rem     jpearson   08/21/96 -  nested table column names
Rem     asurpur    07/31/96 -  Granting *_catalog_roles to *_full_database
Rem     jpearson   08/19/96 -  modify nested table support
Rem     wfisher    08/14/96 -  LOB clauses on CREATE TABLE for attributes
Rem     jpearson   08/08/96 -  support REF SCOPE
Rem     jpearson   08/06/96 -  extent views fix for dropped types
Rem     syeung     07/19/96 -  purified
Rem     jpearson   07/29/96 -  handle INSTEAD OF triggers
Rem     jpearson   07/24/96 -  simplify incremental export
Rem     jpearson   07/11/96 -  fix merge errors
Rem     syeung     07/01/96 -  system generated name changed
Rem     jpearson   07/10/96 -  add catalog roles
Rem     syeung     06/24/96 -  clu.spare4->avgchn in exu8clu
Rem     syeung     06/19/96 -  fix snapshot views
Rem     echong     07/08/96 -  define exu8iovu for iots
Rem     echong     06/24/96 -  add iot comments
Rem     echong     06/17/96 -  add defer to exu8con
Rem     jpearson   06/27/96 -  support for librarys and execute any type priv
Rem     wfisher    06/25/96 -  Don't get lob descriptions for lob attributes
Rem     jpearson   06/14/96 -  change views for indices to support objects
Rem     jpearson   06/13/96 -  support constraints on adt columns
Rem     wfisher    06/13/96 -  Add SYS_NC_ to system generated column names
Rem     jpearson   06/12/96 -  fix type body audit information
Rem     jpearson   06/11/96 -  support for objects in incremental export
Rem     jpearson   06/11/96 -  fix type body views
Rem     jpearson   06/10/96 -  add views for types, librarys and directory alia
Rem     wfisher    06/07/96 -  Add in more object views
Rem     wfisher    06/06/96 -  Adding object views
Rem     jpearson   06/05/96 -  modify exugrn[u] for directorys
Rem     asurpur    05/29/96 -  Removing select on private views from select_cat
Rem     wfisher    05/23/96 -  Adding object support for columns
Rem     mmonajje   05/22/96 -  Replace action col name with action#
Rem     wfisher    05/16/96 -  Return property and type information for tables
Rem     echong     05/24/96 -  Modify exu8tab to include iots
Rem     syeung     05/16/96 -  fix merge problem
Rem     asurpur    05/15/96 -  Dictionary Protection: Granting privileges
Rem     syeung     05/14/96 -  modified for replication changes
Rem     ixhu       05/13/96 -  export v7 tables, clusters, p'd tables in Beta1 
Rem     ccchang    05/10/96 -  add nologging support
Rem     ixhu       05/08/96 -  support ts$ online$ and contents$ changes
Rem     asurpur    04/08/96 -  Dictionary Protection Implementation
Rem     ajasuja    05/02/96 -  merge OBJ to BIG
Rem     ixhu       04/11/96 -  AQ support: new expact$ columns
Rem     syeung     04/24/96 -  Add property to exu8uscu
Rem     syeung     04/12/96 -  fix header conflict
Rem     ccchang    03/15/96 -  Support for PTI
Rem     syeung     04/10/96 -  change unique$ to property in exu8ind
Rem     ixhu       04/03/96 -  increment export view version number for 8.0
Rem     ixhu       02/28/96 -  tablespace point-in-time recovery: exu8tsn
Rem     syeung     02/28/96 -  add support deferred constraints and 
Rem                            temporary tables
Rem     ixhu       02/26/96 -  fix exu8fil view
Rem     ixhu       02/24/96 -  fix exu8clu tsno
Rem     atsukerm   02/09/96 -  fix file$ references.
Rem     ixhu       01/16/96 -  ind$ spare8 to type in exu8ind* for bitmap index
Rem     ixhu       01/03/96 -  ts-rel DBA: exu8sto,exu8stou,exu8tne,exu8tab,
Rem                            exu8clu,exu8ind,exu8rsg,exu8uscu,
Rem     achaudhr   10/25/95 -  PTI: fix parallel, cache
Rem                         -  PTI: global replace {imp,exu}7* -> {imp,exu}8*
Rem     aho        11/13/95 -  iot
Rem     achaudhr   10/25/95 -  PTI: fix parallel, cache
Rem     achaudhr   08/22/95 -  PTI: t$.modified -> t$.flags
Rem     gdoherty   08/31/95 -  mergetrans fix_pti_merge_bugs
Rem     achaudhr   07/20/95 -  PTI: t.modified -> t.flags
Rem     ccchang    10/10/95 -  add bitmap to exu7ind view for bitmap index
Rem     ixhu       09/18/95 -  bug 250819 - misspelt RECORD, referential
Rem     ixhu       09/11/95 -  bug 110894 - add exu7ordu to speed up exu7vewu
Rem     bhimatsi   07/11/95 -  merge changes from branch 1.37.720.2
Rem     ssamu      06/15/95 -  change views on tab$
Rem     ixhu       05/25/95 -  add content to exu7tbs
Rem     ixhu       05/15/95 -  bug 274629 - export ts quota even for dropped ts
Rem     lcprice    05/08/95 -  merge changes from branch 1.37.720.1
Rem     ixhu       04/18/95 -  add imp7uec for unlimited extent compatibility 
Rem     jcchou     04/17/95 -  (258186) fix
Rem     ixhu       04/04/95 -  temporary/permanent tablespace in exu7tbs
Rem     ixhu       03/09/95 -  add segcol# in exu7col & exu7colu, exu7cset
Rem     lcprice    04/18/95 -  Fix bug #267737 - default roles processing
Rem     vraghuna   08/19/94 -  move views reqd by both imp and exp to the top
Rem     vraghuna   08/18/94 -  move compatibility checks from sql.bsq
Rem     vraghuna   08/15/94 -  bug 227714 - add exu7cpo
Rem     jloaiza    07/08/94 -  bitand modified column
Rem     vraghuna   06/20/94 -  add support for hash cluster functions
Rem     vraghuna   06/10/94 -  bug 218372 - speed up exu7del
Rem     vraghuna   06/09/94 -  bug 219654 - add NVLs for ||l and cache params
Rem     vraghuna   05/16/94 -  bug 215597 - change exu7snaplu also
Rem     vraghuna   05/12/94 -  bug 215597 - change exu7snapl to exclude slog
Rem     vraghuna   04/24/94 -  bug 211989 - add create roles but not drop
Rem     ltung      03/02/94 -  merge changes from branch 1.15.710.1
Rem     vraghuna   02/09/94 -  add exu7ver for version control
Rem     vraghuna   02/02/94 -  bug 190236 - add outer join to exu7ord in exu7ve
Rem     vraghuna   01/17/94 -  bug 191751 - add support for deferred RPC/RepCat
Rem     vraghuna   01/12/94 -  bug 191750 - add support for refresh groups
Rem     vraghuna   01/11/94 -  bug 191749 - add support for job queues exu7jbq
Rem     vraghuna   01/11/94 -  bug 193733 - use basename in incr trigger views
Rem     vraghuna   01/11/94 -  bug 193732 - fix exu7del for trigs, pkg bodies
Rem     vraghuna   01/10/94 -  bug 192781 - add basetable name for trigger view
Rem     vraghuna   12/30/93 -  bug 192652 - change MM to MI for exu7spr
Rem     vraghuna   12/21/93 -  bug 191879 - grants issued twice
Rem     vraghuna   12/06/93 -  bug 186073 - add read only tablespaces
Rem     vraghuna   08/18/93 -  bug 174029 - moving role creation to sql.bsq
Rem     vraghuna   07/07/93 -  add support for updatable snapshots
Rem     vraghuna   06/17/93 -  bug 166480 - add exu7erc for resource costs
Rem     vraghuna   06/17/93 -  bug 168261 - imp_full_database needs insert priv
Rem     vraghuna   06/17/93 -  bug 166482 - export role passwords
Rem     vraghuna   05/27/93 -  bug 166484 - add audt to exu7spr
Rem     ltung      05/16/93 -  export parallel/cache parameters 
Rem     vraghuna   03/15/93 -  bug 140485 - incrementals on tables with constra
Rem     vraghuna   03/12/93 -  bug 152906 - add tspname to exu7uscu 
Rem     vraghuna   01/27/93 -  bug 146283 - add exu7usc 
Rem     vraghuna   12/18/92 -  bug 143375 - break up exu7col 
Rem     vraghuna   12/02/92 -  bug 139302 - speed up exu7colnn 
Rem     tpystyne   11/07/92 -  use create or replace view 
Rem     vraghuna   10/28/92 -  bug 130560 - add exu7ful 
Rem     vraghuna   10/23/92 -  bug 135594 - remove exu7inv and exu7invu 
Rem     glumpkin   10/20/92 -  Renamed from EXPVEW.SQL 
Rem     vraghuna   10/14/92 -  bug 131957 - add field to exu7sto and exu7stou 
Rem     vraghuna   07/14/92 -  bug 115048 - support for analyze statement 
Rem     cheigham   06/24/92 -  add exu7colnn view 
Rem     cheigham   06/22/92 -  fix cdef$,col$ joins to accommodate changes for 
Rem     jbellemo   06/12/92 -  add mapping for MLSLABEL 
Rem     vraghuna   06/03/92 -  bug 39511 - add exu7grs 
Rem     cheigham   05/27/92 -  speed up exu7vew 
Rem     cheigham   02/13/92 -  add select any to imp_full_database 
Rem     cheigham   02/13/92 -  grant alter any table to imp_full_database 
Rem     cheigham   01/29/92 -  export altered clusters in inc. exports 
Rem     cheigham   01/09/92 -  add more privs to imp_full_database 
Rem     cheigham   11/15/91 -  fix object codes 
Rem     cheigham   11/06/91 -  fix inc. trigger views 
Rem     cheigham   11/02/91 -  merge in hash changes 
Rem     cheigham   10/11/91 -  view names: exu -> exu7
Rem     cheigham   09/27/91 -  add snapshot views 
Rem     sksingh    09/30/91 -  merge changes from branch 1.13.50.1 
Rem     sksingh    09/23/91 -  replace spare1,2,3 with match, refact, enabled
Rem     agupta     09/20/91 -  add support for lists/groups storage params 
Rem     agupta     08/16/91 -  enable|disable constraints 
Rem     agupta     07/30/91 -  7037 - views not created in dependency or
Rem     agupta     07/02/91 -  timestamp syntax for procedures 
Rem     agupta     06/21/91 -  fix errors in exurlg 
Rem     agupta     06/14/91 -  user$ column name changes 
Rem     agupta     05/31/91 -  add userid to tablespace quota view 
Rem     agupta     05/04/91 -  fix unique constraints bug 
Rem     agupta     04/16/91 -  fix auditing views 
Rem     jwijaya    04/12/91 -  remove LINKNAME IS NULL 
Rem     rkooi      04/01/91 -  add 'o.linkname IS NULL' clause 
Rem     Gupta      02/26/90 -  Lots of modifications for V7
Rem     Hong       10/31/88 -  don't export quotas of 0
Rem     Hong       09/21/88 -  allow null precision/scale
Rem     Hong       09/10/88 -  fix outer joins
Rem     Hong       08/10/88 -  get default/temp tablespace in exuusr
Rem     Hong       07/01/88 -  get obj id in some views
Rem     Hong       06/10/88 -  remove userid != 0 from views
Rem     Hong       04/28/88 -  comment$ moved to com$
Rem     Hong       03/24/88 -  add audit field to exu7seq
Rem     Hong       03/07/88 -  deal with initrans, maxtrans
Rem                            add views for constraints, sequence #
Rem     Hong       02/01/88 -  add exuico and exuicou
Rem                            temporary commented out col$.default$
Rem     Hong       02/01/88 -  fix exufil to use v$dbfile directly
Rem     Hong       12/12/87 -  fix exutbs
Rem     Hong       12/07/87 -  handle min extents 
Rem
rem this role allows the grantee to perform full database exports
rem including incremental exports
CREATE ROLE exp_full_database;
grant select any table to exp_full_database;
grant backup any table to exp_full_database;
grant execute any procedure to exp_full_database;
grant execute any type to exp_full_database;
GRANT insert,update,delete
 ON sys.incexp
 TO exp_full_database;
GRANT insert,update,delete
 ON sys.incvid
 TO exp_full_database;
GRANT insert,update,delete
 ON sys.incfil
 TO exp_full_database;
grant exp_full_database to dba;

rem this role allows the grantee to perform full database imports
CREATE ROLE imp_full_database;
grant become user to imp_full_database;
grant create any cluster to imp_full_database;
grant create any index to imp_full_database;
grant create any table to imp_full_database;
grant create any procedure to imp_full_database;
grant create any sequence to imp_full_database;
grant create any snapshot to imp_full_database;
grant create any synonym to imp_full_database;
grant create any trigger to imp_full_database;
grant create any view to imp_full_database;
grant create profile to imp_full_database;
grant create public database link to imp_full_database;
grant create database link to imp_full_database;
grant create public synonym to imp_full_database;
grant create rollback segment to imp_full_database;
grant create role to imp_full_database;
grant create tablespace to imp_full_database;
grant create user to imp_full_database;
grant audit any to imp_full_database;
grant comment any table to imp_full_database;
grant alter any table to imp_full_database;
grant select any table to imp_full_database;
grant execute any procedure to imp_full_database;
grant execute any type to imp_full_database;
grant insert any table to imp_full_database;
grant update any table to imp_full_database;
grant create any directory to imp_full_database;
grant create any type to imp_full_database;
grant create any library to imp_full_database;
grant create any context to imp_full_database;
grant administer database trigger to imp_full_database;
grant create any operator to imp_full_database;
grant create any indextype to imp_full_database;
grant create any dimension to imp_full_database;
grant global query rewrite to imp_full_database;

rem Privileges needed to execute PL/SQL blocks
rem
rem NOTE:  privileges that need to be granted
rem via packages (e.g., MANAGE_ANY_QUEUE) will
rem be granted in the relevant package (e.g., 
rem catqueue.sql) rather than here.  These 
rem privileges are identified in the 
rem SYSTEM_PRIVILEGE_MAP with PROPERTY!=0
rem
grant drop any outline to imp_full_database;

rem Granting the roles access views/packages from the dictionary;
grant select_catalog_role to exp_full_database;
grant select_catalog_role to imp_full_database;
grant execute_catalog_role to imp_full_database;
grant execute_catalog_role to exp_full_database;

rem for import of incremental export files
grant drop any cluster to imp_full_database;
grant drop any index to imp_full_database;
grant drop any table to imp_full_database;
grant drop any procedure to imp_full_database;
grant drop any sequence to imp_full_database;
grant drop any snapshot to imp_full_database;
grant drop any synonym to imp_full_database;
grant drop any trigger to imp_full_database;
grant drop any view to imp_full_database;
grant drop profile to imp_full_database;
grant drop public database link to imp_full_database;
grant drop public synonym to imp_full_database;
grant drop rollback segment to imp_full_database;
grant drop any role to imp_full_database;
grant drop tablespace to imp_full_database;
grant drop user to imp_full_database;
grant drop any directory to imp_full_database;
grant alter any type to imp_full_database;
grant drop any type to imp_full_database;
grant drop any library to imp_full_database;
grant drop any context to imp_full_database;
grant alter any procedure to  imp_full_database;
grant alter any trigger to  imp_full_database;
grant drop any operator to imp_full_database;
grant drop any indextype to imp_full_database;
grant drop any dimension to imp_full_database;
grant analyze any to imp_full_database;

grant imp_full_database to dba;

REM **********  I M P O R T A N T  **********  I M P O R T A N T  **********
rem This first view selects all rows from sys.obj$ that are NOT secondary
rem objects as created by Domain Indexes. Secondary objects are not normally
rem exported because the domain index's CREATE INDEX at import time will create
rem them. However, when doing domain index 'fast rebuild' we do place certain
rem classes of secondary objects (tables & their types, indexes) in the
rem export file.
rem
rem exu81obj should be used as a substitute for obj$ in all top level views
rem of object classes whose secondary objects will NEVER be exported; i.e, are
rem not exported during DI 'fast rebuild'.  Those object classes that can be
rem exp. in DI fast rebuild (tables/indexes) should continue to use obj$ and
rem filtering (or not) of 2ndary objects will be done at run time via a bind
rem var. on the WHERE clauses of the appropriate SELECT statements in exuiss.c.
rem
rem When and if Pt. In Time Recovery ever supports domain indexes, it will have
rem to allow export of secondary objects because these comprise the storage for
rem the index. At that time, all object classes that PITR exports will have to
rem switch from using exu81obj (static filtering) back to obj$ and dynamically
rem filter off secondary objects as appropriate.
REM **********  I M P O R T A N T  **********  I M P O R T A N T  **********

CREATE OR REPLACE view exu81obj AS
        SELECT o$.* from sys.obj$ o$, sys.user$ u$
        WHERE bitand(o$.flags, 16) != 16 AND
        o$.owner# = u$.user# AND
        u$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS', 'LBACSYS')
/
grant select on exu81obj to select_catalog_role
/
REM
REM Get SQL Version information -- this version needs to be hardcoded as 8.1.6
REM when new versions of sql are generated.
REM
CREATE OR REPLACE VIEW exu816maxsqv (version#, sql_version) AS
  select sv.version#, sv.sql_version from sys.sql_version$ sv
  where sv.version#=(select max(sv2.version#) from sys.sql_version$ sv2)
/
grant select on exu816maxsqv to public
/

REM  The following helper view identifies interesting older sql versions in
REM  the database. Only sql versions other than the database sql version are 
REM  identified.  Versions later than the export view's version are down-graded
REM  to the version of the export views being used.

CREATE OR REPLACE view exu816sqv AS
        SELECT sv.* 
        FROM sys.sql_version$ sv 
        WHERE sv.version# < (SELECT m.version# FROM sys.exu816maxsqv m)
/
grant select on exu816sqv to select_catalog_role
/

Rem
Rem  ***************************************************
Rem  Section 1: Views required by BOTH export and import
Rem  ***************************************************
Rem 
rem block size
CREATE OR REPLACE view exu8bsz(blocksize) AS
       SELECT ts$.blocksize
       FROM   sys.ts$ ts$
/
grant select on exu8bsz to public;

rem all users
CREATE OR REPLACE view exu8usr 
                   (name, userid, passwd, defrole, datats, tempts, profile#, 
	            profname, astatus, ext_username) AS 
       SELECT u.name, u.user#, DECODE(u.password, 'N', '', u.password), 
              DECODE(u.defrole, 0, 'N', 1, 'A', 2, 'L', 3, 'E', 'X'), 
	      ts1.name, ts2.name, u.resource$, p.name, u.astatus,
              u.ext_username
       FROM sys.user$ u, sys.ts$ ts1, sys.ts$ ts2, sys.profname$ p
       WHERE u.datats# = ts1.ts# and u.tempts# = ts2.ts# and u.type# = 1 and
             u.resource$ = p.profile# and
             u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS', 'LBACSYS')

/
grant select on exu8usr to select_catalog_role
/
CREATE OR REPLACE view exu8usru AS			     /* current user */
       SELECT * from exu8usr WHERE userid = UID
/
grant select on exu8usru to public;

rem check if user has priv to do a full db export
CREATE OR REPLACE view exu8ful(role) as
select u.name
from x$kzsro, user$ u
where kzsrorol!=uid and kzsrorol!=1 and u.user#=kzsrorol
/
grant select on sys.exu8ful to public
/


Rem Note that tempflags is now deprecated
CREATE OR REPLACE view exu81tabs
                   (objid, dobjid, name, owner, ownerid, tablespace, tsno,
                    fileno, blockno, audit$, comment$, clusterflag, mtime,
	            modified, tabno, pctfree$, pctused$, initrans, maxtrans,
                    degree, instances, cache, tempflags, property, deflog,
                    tsdeflog, roid, recpblk, secondaryobj,
		    rowcnt, blkcnt, avgrlen, tflags)
AS /* Heap tables */
       SELECT o$.obj#, o$.dataobj#, o$.name, u$.name, o$.owner#, ts$.name,
              t$.ts#, t$.file#, 
	      t$.block#, t$.audit$, c$.comment$, NVL(t$.bobj#, 0), o$.mtime,
	      decode(bitand(t$.flags,1),1,1,0),
	      NVL(t$.tab#,0),
              mod(t$.pctfree$, 100), 
              t$.pctused$, t$.initrans, t$.maxtrans, NVL(t$.degree,1), 
              NVL(t$.instances,1), decode(bitand(t$.flags,8), 8, 1, 0),
              mod(trunc(o$.flags/2),2), t$.property,
              decode(bitand(t$.flags,32),32,1,0), ts$.dflogging, o$.oid$,
              t$.spare1, decode(bitand(o$.flags,16),16,1,0),
	      NVL(t$.rowcnt, -1), NVL(t$.blkcnt,-1), NVL(t$.avgrln,-1),t$.flags
       FROM   sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$, sys.com$ c$
       WHERE  t$.obj# = o$.obj# and t$.ts# = ts$.ts# and 
              u$.user# = o$.owner# and o$.obj# = c$.obj#(+) and
	      c$.col#(+) is null and
              bitand(t$.property, 64+512) = 0  /* skip IOT and overflow segs */
UNION ALL  /* Index-only tables */
       SELECT o$.obj#, o$.dataobj#, o$.name, u$.name, o$.owner#, ts$.name,
              i$.ts#, t$.file#, 
	      t$.block#, t$.audit$, c$.comment$, NVL(t$.bobj#, 0), o$.mtime,
	      decode(bitand(t$.flags,1),1,1,0),
	      NVL(t$.tab#,0),
              0, 0, 0, 0, 1, 1, decode(bitand(t$.flags,8), 8, 1, 0),
              mod(trunc(o$.flags/2),2), t$.property,
              decode(bitand(t$.flags,32),32,1,0), ts$.dflogging, o$.oid$,
              t$.spare1, decode(bitand(o$.flags,16),16,1,0),
	      -1, -1, -1,
              t$.flags
       FROM sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$, 
            sys.com$ c$, sys.ind$ i$
       WHERE  t$.obj# = o$.obj# and 
              u$.user# = o$.owner# and 
              o$.obj# = c$.obj#(+) and
	      c$.col#(+) is null and
              bitand(t$.property, 64+512) = 64 and  /* IOT, but not overflow*/
              t$.pctused$ = i$.obj# and /* For IOTs, pctused has index obj# */
	      i$.ts# = ts$.ts#
/
grant select on exu81tabs to select_catalog_role
/
rem current user's tables 
CREATE OR REPLACE view exu81tabsu AS
       SELECT * from exu81tabs WHERE ownerid = uid
/
grant select on exu81tabsu to public;

Rem Now derive exu81tab (filtered for secondaryobjs from above).

CREATE OR REPLACE view exu81tab AS
SELECT * from exu81tabs WHERE secondaryobj=0
/
grant select on exu81tab to select_catalog_role;

rem current user's tables
CREATE OR REPLACE view exu81tabu AS
       SELECT * from exu81tab WHERE ownerid = uid
/
grant select on exu81tabu to public;

rem
rem V8.0 table view, exu8tab must be subseted off exu81tab in order to filter
rem out tables with columns of type UROWID (208).
rem Filter datetime interval types : (178 <= type <= 183)
rem Filter varying width character LOBs : 
rem (type=112 & ((800 < charsetid < 1000) or (charsetid > 2000)))
CREATE OR REPLACE view exu8tab AS
       SELECT * from exu81tab 
       WHERE NOT EXISTS
       (SELECT * FROM sys.col$ c$
        WHERE c$.obj# = objid AND
             (
             c$.type# = 208 OR
             (c$.type# >= 178 AND c$.type# <= 183) OR
             (c$.type#= 112 AND ((c$.charsetid > 800 AND c$.charsetid < 1000) OR
                                   c$.charsetid > 2000))
             )
       )
/
grant select on exu8tab to select_catalog_role
/
CREATE OR REPLACE view exu8tabu AS
       SELECT * from exu8tab WHERE ownerid = uid
/
grant select on exu8tabu to public
/
REM Database Character Set and NCHAR Character Set
CREATE OR REPLACE view exu8cset  (name, value) AS
                SELECT name, value$ from sys.props$
                WHERE name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
/
grant select on exu8cset to public
/

REM Check for Database Options
CREATE OR REPLACE view exu8opt (parameter, value) AS
                SELECT parameter, DECODE(value, 'TRUE', 1, 'FALSE', 0, 2)
                FROM v$option
/
grant select on exu8opt to public
/



Rem
Rem  ****************************************
Rem  Section 2: Views required by import ONLY
Rem  ****************************************
Rem 
REM 

REM build replication procedures view 
CREATE OR REPLACE view imp8repcat (name, type) as
      select name, type#
      from sys.obj$
      where name in ('DBMS_SNAPSHOT_UTL',  'DBMS_REPCAT_MIG')
       and type# = 11 and owner# = 0;
/
grant select on imp8repcat to public;
/


REM Get Unlimited Extent Compatibility Information
CREATE or REPLACE view imp8uec (release) AS
		SELECT release 
		FROM v$compatibility
		WHERE type_id = 'UNLMTEXT'
/
grant select on imp8uec to public
/


rem obtain the TOID of an existing type
CREATE OR REPLACE view imp8ttd
   (tname, towner, toid) AS
   SELECT o$.name, u$.name, o$.oid$
   FROM  sys.obj$ o$, sys.user$ u$
   WHERE o$.type# = 13
     AND o$.owner# = u$.user#
/
grant select on imp8ttd to select_catalog_role;

CREATE OR REPLACE view imp8ttdu
   (tname, towner, toid) AS
   SELECT o$.name, u$.name, o$.oid$
   FROM  sys.obj$ o$, sys.user$ u$
   WHERE o$.type# = 13
     AND o$.owner# = u$.user#
     AND ( o$.owner# = UID    /* owned by current user */
            OR
            /* current user or public role have execute access to type */
            o$.obj# in (select oa.obj# from sys.objauth$ oa
                       where ( oa.obj# = o$.obj#
                              AND oa.privilege# = 12 /* execute */
                              AND (oa.grantee# = UID or oa.grantee# = 1)))
            OR 
            /* current user or public role can execute any type */
            exists (select null from sys.sysauth$ sa
                    where (sa.grantee# = UID or sa.grantee# = 1)
                           AND sa.privilege# = -184 )
         )
/
grant select on imp8ttdu to public;

CREATE OR REPLACE view imp8cdt (ownerid, bad) AS
SELECT co$.owner#, decode(bitand(c$.defer,16),16,1,0)
FROM   sys.cdef$ c$, sys.con$ co$
WHERE  c$.defer is NOT NULL
   AND bitand(c$.defer,16) = 16
   AND c$.con# = co$.con#
/
grant select on imp8cdt to select_catalog_role;

CREATE OR REPLACE view imp8cdtu  AS
SELECT * from imp8cdt
WHERE ownerid = UID
/
grant select on imp8cdtu to public;

CREATE OR REPLACE view imp8con (ctname, tbname, username, intcol) as
      SELECT c.name, o.name, u.name, cc.intcol# 
      FROM sys.obj$ o, sys.user$ u, sys.con$ c, sys.ccol$ cc, cdef$ cd
      WHERE o.obj#=cc.obj# AND c.con#=cc.con# AND o.obj#=cd.obj#
      AND u.user# = c.owner# AND cd.con#=c.con# AND cd.type#=3
      AND cd.defer=8
/
grant select on imp8con to select_catalog_role;

Rem
Rem  ****************************************
Rem  Section 3: Views required by export ONLY
Rem  ****************************************
Rem
Rem IOT overflow segments

Rem Note tempflags now deprecated

CREATE OR REPLACE view exu8iov
                   (objid,dobjid,name, bobjid, owner, ownerid, tablespace, tsno, 
		    fileno,
	            blockno, audit$, comment$, clusterflag, mtime, modified, 
	            pctfree$,
                    pctused$, initrans, maxtrans, degree, instances, cache,
                    tempflags, property, deflog, tsdeflog) AS
       SELECT o$.obj#,o$.dataobj#,o$.name, t$.bobj#, u$.name, o$.owner#, ts$.name, t$.ts#,
	      t$.file#, 
	      t$.block#, t$.audit$, c$.comment$, NVL(t$.bobj#, 0), o$.mtime,
	      decode(bitand(t$.flags,1),1,1,0), mod(t$.pctfree$, 100), 
              t$.pctused$, t$.initrans, t$.maxtrans, NVL(t$.degree,1), 
              NVL(t$.instances,1), decode(bitand(t$.flags,128), 128, 1, 0),
              mod(trunc(o$.flags/2),2), t$.property,
              decode(bitand(t$.flags,32),32,1,0), ts$.dflogging
       FROM sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$, sys.com$ c$
       WHERE  t$.obj# = o$.obj# and t$.ts# = ts$.ts# and 
              u$.user# = o$.owner# and o$.obj# = c$.obj#(+) 
	      and c$.col#(+) is null and bitand(t$.property, 512) = 512
/
grant select on exu8iov to select_catalog_role
/
rem current user's overflow segments
CREATE OR REPLACE view exu8iovu AS
       SELECT * from exu8iov WHERE ownerid = uid
/
grant select on exu8iovu to public
/
rem iot INCLUDING key columns
CREATE OR REPLACE view exu8ink
                   (objid, ownerid, intcolid, name) AS
       SELECT o$.obj#, o$.owner#, c$.intcol#, c$.name
       FROM sys.obj$ o$, sys.ind$ i$, sys.col$ c$
       WHERE  i$.bo# = o$.obj# and c$.obj# = o$.obj# and 
              c$.col# = i$.trunccnt and i$.trunccnt !=0
/
grant select on exu8ink to select_catalog_role
/
rem current user's INCLUDING key columns
CREATE OR REPLACE view exu8inku AS
       SELECT * from exu8ink WHERE ownerid = uid
/
grant select on exu8inku to public
/

rem 8.1 tables for incremental export: modified, altered or new
CREATE OR REPLACE view exu81tabi AS 
       SELECT t.* from exu81tab t,incexp i, incvid v
       WHERE t.name  = i.name(+) AND t.ownerid = i.owner#(+) AND
             NVL(i.type#,2) = 2 AND 
             bitand(t.property, 8192 ) = 0 AND  /* not inner table */ 
             (bitand(t.modified,1) = 1 
              OR t.mtime > i.itime  
              OR NVL(i.expid,9999) > v.expid
              OR   /* determine if it has inner tables that have been
                      changed since last incremental export */
               ( bitand(t.property,4) = 4 /* has inner tables */
                AND EXISTS (SELECT 0 FROM  obj$ o2, tab$ t2
                            WHERE  o2.obj# = t2.obj#
                               AND bitand(t2.property,8192) = 8192
                               AND ( o2.mtime > i.itime
                                     OR bitand(t2.flags,1) = 1 )
                               AND o2.obj# 
                                 IN ( SELECT nt.ntab# from ntab$ nt
                                        START WITH nt.obj# = t.objid
                                        CONNECT BY PRIOR nt.ntab# = nt.obj#
                                    )
                           )
                ) 
            )
/

grant select on exu81tabi to select_catalog_role
/	     
rem 8.1 tables for cumulative export:
rem  modified, last export was inc, altered or new
CREATE OR REPLACE view exu81tabc AS 
       SELECT t.* from exu81tab t,incexp i, incvid v
       WHERE  t.name  = i.name(+) AND t.ownerid = i.owner#(+) AND 
   	      NVL(i.type#,2) = 2 AND 
              bitand(t.property, 8192 ) = 0 AND /* not inner table */
              (bitand(t.modified,1) = 1 
               OR i.itime > NVL(i.ctime, TO_DATE('01-01-1900','DD-MM-YYYY'))
               OR t.mtime > i.itime 
               OR NVL(i.expid,9999) > v.expid
               OR   /* determine if it has inner tables that have been
                      changed since last incremental export */
               ( bitand(t.property,4) = 4 /* has inner tables */
                AND EXISTS (SELECT 0 FROM  obj$ o2, tab$ t2
                            WHERE  o2.obj# = t2.obj#
                               AND bitand(t2.property,8192) = 8192
                               AND ( o2.mtime > i.itime
                                     OR bitand(t2.flags,1) = 1 )
                               AND o2.obj# 
                                 IN ( SELECT nt.ntab# from ntab$ nt
                                        START WITH nt.obj# = t.objid
                                        CONNECT BY PRIOR nt.ntab# = nt.obj#
                                    )
                           )
                )   
              )
/
grant select on exu81tabc to select_catalog_role
/
rem 8.0 tables for incremental export: modified, altered or new
CREATE OR REPLACE view exu8tabi AS 
       SELECT * from exu81tabi
       WHERE NOT EXISTS (SELECT * FROM sys.col$ c$
                         WHERE c$.obj# = objid AND c$.type# = 208)
/

grant select on exu8tabi to select_catalog_role
/	     
rem 8.0 tables for cumulative export: 
rem modified, last export was inc, altered or new
CREATE OR REPLACE view exu8tabc AS 
       SELECT * from exu81tabc
       WHERE NOT EXISTS (SELECT * FROM sys.col$ c$
                         WHERE c$.obj# = objid AND c$.type# = 208)
/
grant select on exu8tabc to select_catalog_role
/

rem  partition description for all non-composite partitioned tables
CREATE OR REPLACE view exu8tbp 
          ( objid, dobjid, bobjid, ownerid, pname, prowcnt, pblkcnt, pavgrlen,
            pflags, partno, hiboundlen, hiboundval,
            tsname, tsno, fileno, blockno,
            pctfree$, pctused$, initrans, maxtrans, deflog, tsdeflog,
            blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, iflags) AS
     SELECT o$.obj#, o$.dataobj#, tp$.bo#, o$.owner#, o$.subname,
            NVL(tp$.rowcnt,-1), NVL(tp$.blkcnt,-1), NVL(tp$.avgrln,-1),
	    tp$.flags, tp$.part#, tp$.hiboundlen, tp$.hiboundval,
            ts$.name, tp$.ts#, tp$.file#, tp$.block#,
            mod(tp$.pctfree$, 100), tp$.pctused$, tp$.initrans, tp$.maxtrans,
            decode(bitand(tp$.flags,4),4,1,0), ts$.dflogging,
            -1, -1, -1, -1, -1, -1, -1
       FROM  sys.obj$ o$, sys.tabpart$ tp$, sys.ts$ ts$
       WHERE o$.type#  = 19
             and tp$.obj# = o$.obj#
             and ts$.ts# = tp$.ts#
/
grant select on exu8tbp to select_catalog_role
/
rem
rem partition description for current user's non_composite partitioned tables
CREATE OR REPLACE view exu8tbpu AS
       SELECT * from exu8tbp WHERE ownerid = uid
/
grant select on exu8tbpu to public;
/
rem
rem  subpartition description for all composite partitioned tables
CREATE OR REPLACE view exu81tbsp
          ( objid, dobjid, pobjid, ownerid, 
            subpartno, subpname, tsname, 
            fileno, blockno,tsno,
            prowcnt, pblkcnt, pavgrlen,
            blevel, leafcnt, distkey, lblkkey, dblkkey, clufac) AS
    SELECT  o.obj#, o.dataobj#, sp.pobj#, o.owner#,
            sp.subpart#, o.subname, ts.name, 
            sp.file#, sp.block#, sp.ts#,
            NVL(sp.rowcnt,-1), NVL(sp.blkcnt,-1), NVL(sp.avgrln,-1),
            -1, -1, -1, -1, -1, -1
      FROM  sys.obj$ o, sys.tabsubpart$ sp, sys.ts$ ts
      WHERE o.type#  = 34
            and sp.obj# = o.obj#
            and ts.ts# = sp.ts#
/
grant select on exu81tbsp to select_catalog_role
/
rem
rem subpartition description for current user's composite partitioned tables
CREATE OR REPLACE view exu81tbspu AS
       SELECT * from exu81tbsp WHERE ownerid = uid
/
grant select on exu81tbspu to public;
/
rem
rem composite partition description for all composite partitioned tables
rem 
CREATE OR REPLACE view exu81tbcp 
          ( objid, dobjid, bobjid, ownerid, compname, 
            partno, hiboundlen, hiboundval, 
            prowcnt, pblkcnt, pavgrlen, tsname, pctfree$,
            pctused$, initrans, maxtrans, 
            iniexts, extsize, minexts, maxexts,
            extpct, flists, freegrp, 
            pcache, deflog, tsdeflog, 
            blevel, leafcnt, distkey, lblkkey, dblkkey, clufac) AS
     SELECT o.obj#, o.dataobj#, cp.bo#, o.owner#, o.subname,
            cp.part#, cp.hiboundlen, cp.hiboundval,
            NVL(cp.rowcnt,-1),  NVL(cp.blkcnt,-1), NVL(cp.avgrln,-1),
            ts.name, mod(cp.defpctfree, 100),
            cp.defpctused, cp.definitrans, cp.defmaxtrans,
            NVL(cp.definiexts,0), NVL(cp.defextsize,0), 
            NVL(cp.defminexts,0), NVL(cp.defmaxexts,0),
            NVL(cp.defextpct,-1), 
            NVL(cp.deflists,0), NVL(cp.defgroups,0),
            decode(cp.defbufpool, 1, 'KEEP', 2, 'RECYCLE', NULL),
            cp.deflogging, ts.dflogging,
            -1, -1, -1, -1, -1, -1
      FROM  sys.obj$ o, sys.tabcompart$ cp, sys.ts$ ts
      WHERE cp.obj# = o.obj# and
            cp.defts#= ts.ts#  (+)
/
grant select on exu81tbcp to select_catalog_role
/
rem
rem partition description for current user's composite partitioned tables
CREATE OR REPLACE view exu81tbcpu AS
       SELECT * from exu81tbcp WHERE ownerid = uid
/
grant select on exu81tbcpu to public;
/
rem
rem get information about a nested table

rem Note tempflags now deprecated

CREATE OR REPLACE view exu8ntb
       ( pobjid, objid, name, dobjid, owner, ownerid, tablespace, tsno, fileno,
	 blockno, audit$, comment$, clusterflag, mtime, modified, 
	 pctfree$,
         pctused$, initrans, maxtrans, degree, instances, cache,
         tempflags, property, deflog, tsdeflog, roid,
         colprop, expname, rowcnt, blkcnt, avgrlen, tflags, ntcolflgs) AS
       SELECT nt$.obj#, o$.obj#,
              o$.name, o$.dataobj#, u$.name, o$.owner#, ts$.name, t$.ts#, t$.file#, 
	      t$.block#, t$.audit$, c$.comment$, NVL(t$.bobj#, 0), o$.mtime,
	      decode(bitand(t$.flags,1),1,1,0), mod(t$.pctfree$, 100), 
              t$.pctused$, t$.initrans, t$.maxtrans, NVL(t$.degree,1), 
              NVL(t$.instances,1), decode(bitand(t$.flags,128), 128, 1, 0),
              mod(trunc(o$.flags/2),2), t$.property,
              decode(bitand(t$.flags,32),32,1,0), ts$.dflogging, o$.oid$,
              cl$.property,
              DECODE( bitand(cl$.property,1), 1, a$.name, cl$.name),
	      NVL(t$.rowcnt, -1), NVL(t$.blkcnt,-1), NVL(t$.avgrln,-1),
              t$.flags, NVL(ct$.flags, 0)
       FROM sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$, sys.com$ c$,
             sys.ntab$ nt$, sys.col$ cl$, sys.attrcol$ a$, sys.coltype$ ct$
       WHERE  t$.obj# = o$.obj# and t$.ts# = ts$.ts# and 
              u$.user# = o$.owner# and o$.obj# = c$.obj#(+) and
	      c$.col#(+) is null and
              nt$.ntab# = o$.obj# and
              cl$.obj# = ct$.obj# (+) and cl$.intcol# = ct$.intcol# (+)  and
              nt$.obj# = cl$.obj# and nt$.intcol# = cl$.intcol# and
              cl$.obj# = a$.obj# (+) and cl$.intcol# = a$.intcol# (+) and
              bitand(cl$.property,32768) != 32768      /* not unused column */
/
grant select on exu8ntb to select_catalog_role
/
CREATE OR REPLACE view exu8ntbu AS
       SELECT * from exu8ntb WHERE ownerid = UID
/
grant select on exu8ntbu to public;

rem get tables that were included in an incremental/cumulative export
rem that have inner nested tables

rem use for inc/cum when record = yes
CREATE OR REPLACE VIEW exu8tntic
   (objid,  ownerid, tname ) AS
SELECT o$.obj#, o$.owner#, o$.name
FROM  sys.obj$ o$, sys.tab$ t$
WHERE (o$.owner#, o$.name) IN
         (SELECT i$.owner#, i$.name  /* tables in this export */
            FROM sys.incexp i$, sys.incvid v$
            WHERE i$.expid > v$.expid and i$.type# = 2 )
 AND t$.obj# = o$.obj#
 AND bitand( t$.property, 4 ) = 4   /* has nested tables */
/
grant select on exu8tntic to select_catalog_role;

rem use for inc when record = no
CREATE OR REPLACE VIEW exu81tnti
  (objid, ownerid, tname) AS
SELECT t$.objid, t$.ownerid, t$.name
FROM  sys.exu81tabi t$              /* tables in this export */
WHERE bitand(t$.property, 4 ) = 4  /* table has nested tables */
/
grant select on exu81tnti to select_catalog_role;

rem use for cum when record = no
CREATE OR REPLACE VIEW exu81tntc
  (objid, ownerid, tname) AS
SELECT t$.objid, t$.ownerid, t$.name
FROM  sys.exu81tabc t$              /* tables in this export */
WHERE bitand(t$.property, 4 ) = 4  /* table has nested tables */
/
grant select on exu81tntc to select_catalog_role;

rem 8.0 use for inc when record = no
CREATE OR REPLACE VIEW exu8tnti
  (objid, ownerid, tname) AS
SELECT t$.objid, t$.ownerid, t$.name
FROM  sys.exu8tabi t$              /* tables in this export */
WHERE bitand(t$.property, 4 ) = 4  /* table has nested tables */
/
grant select on exu8tnti to select_catalog_role;

rem 8.0 use for cum when record = no
CREATE OR REPLACE VIEW exu8tntc
  (objid, ownerid, tname) AS
SELECT t$.objid, t$.ownerid, t$.name
FROM  sys.exu8tabc t$              /* tables in this export */
WHERE bitand(t$.property, 4 ) = 4  /* table has nested tables */
/
grant select on exu8tntc to select_catalog_role;

/
rem partition description for all non_composite partitioned indexes


CREATE OR REPLACE view exu8ixp 
          ( objid, dobjid, bobjid, ownerid, pname, 
            prowcnt, pblkcnt, pavgrlen, 
            pflags, partno, hiboundlen, hiboundval,
            tsname, tsno, fileno, blockno,
            pctfree$, pctused$, initrans, maxtrans, deflog, tsdeflog, 
            blevel, leafcnt, distkey, lblkkey, dblkkey, clufac,iflags) AS
     SELECT o$.obj#, o$.dataobj#, ip$.bo#, o$.owner#, o$.subname,
            NVL(ip$.rowcnt,-1), -1, -1, 
            -1, ip$.part#, ip$.hiboundlen, ip$.hiboundval,
            ts$.name, ip$.ts#, ip$.file#, ip$.block#,
            mod(ip$.pctfree$, 100), 0, ip$.initrans, ip$.maxtrans,
            decode(bitand(ip$.flags,4),4,1,0), ts$.dflogging,
            NVL(ip$.blevel,-1),NVL(ip$.leafcnt,-1),NVL(ip$.distkey,-1),
            NVL(ip$.lblkkey,-1),NVL(ip$.dblkkey,-1),NVL(ip$.clufac,-1),
	    ip$.flags
      FROM  sys.obj$ o$, sys.indpart$ ip$, sys.ts$ ts$
      WHERE o$.type#  = 20
            and ip$.obj# = o$.obj#
            and ts$.ts# = ip$.ts#

/
grant select on exu8ixp to select_catalog_role
/
rem
rem partition description for current user's non_composite partitioned indexes.
rem
CREATE OR REPLACE view exu8ixpu AS
       SELECT * from exu8ixp WHERE ownerid = uid
/
grant select on exu8ixpu to public;
/
rem partition description for all composite partitioned indexes
CREATE OR REPLACE view exu81ixcp
        ( objid, dobjid, bobjid, ownerid, compname, 
          partno, hiboundlen, hiboundval,
          prowcnt, pblkcnt, pavgrlen, 
          tsname,  pctfree$, pctused$,
          initrans, maxtrans,
          iniexts, extsize, minexts, maxexts,
          extpct, flists, freegrp,
          pcache, deflog,tsdeflog,
          blevel, leafcnt, distkey, lblkkey, dblkkey, clufac) AS
   SELECT o.obj#, o.dataobj#, icp.bo#, o.owner#, o.subname,
          icp.part#, icp.hiboundlen, icp.hiboundval,
          NVL(icp.rowcnt,-1), -1, -1,
          ts.name, mod(icp.defpctfree, 100), 0,
          icp.definitrans, icp.defmaxtrans,
          NVL(icp.definiexts,0), NVL(icp.defextsize,0), 
          NVL(icp.defminexts,0), NVL(icp.defmaxexts,0),
          NVL(icp.defextpct,-1),
          NVL(icp.deflists,0), NVL(icp.defgroups,0),
          decode(icp.defbufpool, 1, 'KEEP', 2, 'RECYCLE', NULL),
          icp.deflogging, ts.dflogging,
          NVL(icp.blevel,-1), NVL(icp.leafcnt,-1),NVL(icp.distkey,-1),
          NVL(icp.lblkkey,-1), NVL(icp.dblkkey,-1),NVL(icp.clufac,-1)
    FROM  sys.obj$ o, sys.indcompart$ icp, sys.ts$ ts
    WHERE icp.obj# = o.obj# and
          icp.defts#= ts.ts#  (+)
/
grant select on exu81ixcp to select_catalog_role
/
rem
rem partition description for current user's composite partitioned indexes.
rem
CREATE OR REPLACE view exu81ixcpu AS
       SELECT * from exu81ixcp WHERE ownerid = uid
/
grant select on exu81ixcpu to public;
/
rem subpartition description for all composite partitioned indexes
CREATE OR REPLACE view exu81ixsp 
          ( objid, dobjid, pobjid,ownerid, 
            subpartno, subpname, tsname, 
            fileno, blockno, tsno,
            prowcnt, pblkcnt, pavgrlen,
            blevel, leafcnt, distkey, lblkkey, dblkkey, clufac) AS
     SELECT o.obj#, o.dataobj#, isp.pobj#, o.owner#, 
            isp.subpart#, o.subname,  
            ts.name, 
            isp.file#, isp.block#, isp.ts#,
            NVL(isp.rowcnt,-1), -1, -1,
            NVL(isp.blevel,-1),NVL(isp.leafcnt,-1),NVL(isp.distkey,-1),
            NVL(isp.lblkkey,-1),NVL(isp.dblkkey,-1),NVL(isp.clufac,-1)
      FROM  sys.obj$ o, sys.indsubpart$ isp, sys.ts$ ts
      WHERE o.type#  = 35
            and isp.obj# = o.obj#
            and ts.ts# = isp.ts#
/
grant select on exu81ixsp to select_catalog_role
/
rem
rem subpartition description for current user's composite 
rem partitioned indexes.
rem
CREATE OR REPLACE view exu81ixspu AS
       SELECT * from exu81ixsp WHERE ownerid = uid
/
grant select on exu81ixspu to public;
/

rem partitioning key columns for all partitioned tables. NOTE: property,
rem function, funclen added in anticipation of support for virtual cols. as
rem partitioning keys and to keep table and index part. views in synch.

CREATE OR REPLACE view exu8pok 
        ( objid, ownerid, posno, name, property, function, funclen) AS
   SELECT o$.obj#, o$.owner#, p$.pos#,  
          DECODE(bitand(c$.property,1),1,a$.name, c$.name),
          c$.property, c$.default$,c$.deflength
     FROM sys.obj$ o$, sys.partcol$ p$, sys.col$ c$, sys.attrcol$ a$
    WHERE     o$.obj#   = c$.obj#
          and o$.obj#   = p$.obj#
          and p$.intcol#  = c$.intcol#
          and p$.obj# = a$.obj# (+)
          and p$.intcol# = a$.intcol# (+)
/
grant select on exu8pok to select_catalog_role
/
rem partitioning key columns for current user's partitioned tables
CREATE OR REPLACE view exu8poku AS
       SELECT * from exu8pok WHERE ownerid = uid
/
grant select on exu8poku to public;
/

rem subpartitioning key columns for all composite partitioned (R+H) tables. 
rem NOTE: property, function, funclen added in anticipation of support for 
rem function, funclen added in anticipation of support for virtual cols. as
rem partitioning keys and to keep table and index part. views in synch.

CREATE OR REPLACE view exu81spok 
        ( objid, ownerid, posno, name, property, function, funclen) AS
   SELECT o.obj#, o.owner#, spc.pos#, 
          DECODE(bitand(c.property,1),1,a.name, c.name),
          c.property, c.default$, c.deflength
     FROM sys.obj$ o, sys.subpartcol$ spc, sys.col$ c, sys.attrcol$ a
    WHERE     o.obj#   = c.obj#
          and o.obj#   = spc.obj#
          and spc.intcol#  = c.intcol#
          and spc.obj# = a.obj# (+)
          and spc.intcol# = a.intcol# (+)
/
grant select on exu81spok to select_catalog_role
/
rem subpartitioning key columns for current user's composite 
rem  partitioned tables
CREATE OR REPLACE view exu81spoku AS
       SELECT * from exu81spok WHERE ownerid = uid
/
grant select on exu81spoku to public;

rem partitioning key columns for all partitioned indexes
CREATE OR REPLACE view exu8poki 
        ( objid, ownerid, posno, name, property, function, funclen) AS
   SELECT o.obj#, o.owner#, p.pos#, 
          DECODE(bitand(c.property,1),1,a.name, c.name),
           c.property, c.default$, c.deflength
     FROM sys.obj$ o, sys.partcol$ p, sys.ind$ i, sys.col$ c,
          sys.attrcol$ a
    WHERE     o.obj#  = p.obj#
          and i.obj#  = o.obj#
          and i.bo#   = c.obj#
          and p.intcol#  = c.intcol# 
          and c.obj# = a.obj# (+)
          and c.intcol# = a.intcol# (+)
/
grant select on exu8poki to select_catalog_role
/

rem partitioning key columns for current user's partitioned indexes 
CREATE OR REPLACE view exu8pokiu AS
       SELECT * from exu8poki WHERE ownerid = uid
/
grant select on exu8pokiu to public;
/

rem subpartitioning key columns for all composite partitioned indexes
rem not used in 81
CREATE OR REPLACE view exu81spoki 
        ( objid, ownerid, posno, name, property, function, funclen) AS
   SELECT o.obj#, o.owner#, sp.pos#, c.name, c.property, c.default$,
          c.deflength
     FROM sys.obj$ o, sys.subpartcol$ sp, sys.ind$ i, sys.col$ c
    WHERE     o.obj#      = sp.obj#
          and i.obj#      = o.obj#
          and i.bo#       = c.obj#
          and sp.intcol#  = c.intcol#
/
grant select on exu81spoki to select_catalog_role
/

rem subpartitioning key columns for current user's composite
rem partitioned indexes 
CREATE OR REPLACE view exu81spokiu AS
       SELECT * from exu81spoki WHERE ownerid = uid
/
grant select on exu81spokiu to public;
/
rem table/index level storage definition for all partitioned tables/indexes
rem description on partitioned object
CREATE OR REPLACE view exu8pds
            (objid, type, ownerid,
             ptype, subptype,
             pflag, pcnt, pkcnt,
             pctfree$, pctused$,
             initrans, maxtrans,
             iniexts, extsize,
             minexts, maxexts, extpct,
             flists, freegrp, tsname, deflog, pcache
            ) AS
     SELECT  o$.obj#, o$.type#, o$.owner#,
             po$.parttype, mod(po$.spare2,256),
             NVL(po$.flags,0), po$.partcnt, po$.partkeycols,
             mod(po$.defpctfree, 100), po$.defpctused,
             po$.definitrans, po$.defmaxtrans,
             po$.deftiniexts, po$.defextsize,
             po$.defminexts, po$.defmaxexts, po$.defextpct,
             po$.deflists,
             po$.defgroups,
             ts$.name, po$.deflogging,
             decode(po$.spare1, 1, 'KEEP', 2, 'RECYCLE', NULL)
      FROM  sys.partobj$ po$, sys.obj$ o$, sys.ts$ ts$
      WHERE     po$.defts# = ts$.ts# (+) 
            and po$.obj#   = o$.obj#
/
grant select on exu8pds to select_catalog_role
/
rem table/index level storage definition for current user's 
rem partitioned tables/indexes
CREATE OR REPLACE view exu8pdsu AS
       SELECT * from exu8pds WHERE ownerid = uid
/
grant select on exu8pdsu to public;

rem not null constraints on columns
CREATE OR REPLACE view exu8colnn (tobjid, intcolid, conname, isnull, enabled,
      defer) AS
      SELECT cc$.obj#, cc$.intcol#, con$.name,
	      1, NVL(cd$.enabled, 0), nvl(cd$.defer, 0)
      FROM  sys.con$ con$, sys.cdef$ cd$, sys.ccol$ cc$
      WHERE cc$.con# = cd$.con# and
            cd$.con# = con$.con# and cd$.type# =7
/
grant select on exu8colnn to select_catalog_role
/
CREATE OR REPLACE view exu8col_temp
                   (tobjid, towner, townerid, tname, name, length, precision, 
		    scale, type, isnull, conname, colid, intcolid, segcolid,
		    colprop, comment$, --default$, 
                    dfltlen, enabled, defer, flags, charsetid, charsetform,
                    fsprecision, lfprecision) AS
       SELECT o$.obj#, u$.name, o$.owner#, o$.name, c$.name, c$.length, 
              c$.precision#, c$.scale, c$.type#, NVL(cn.isnull,0), 
              cn.conname, c$.col#, c$.intcol#, c$.segcol#, c$.property,
	      com$.comment$,-- c$.default$, 
	      NVL(c$.deflength, 0), cn.enabled, cn.defer, nvl(o$.flags, 0),
              NVL(c$.charsetid, 0), NVL(c$.charsetform, 0),
              c$.scale, c$.precision#
       FROM sys.col$ c$, sys.obj$ o$, sys.user$ u$, sys.com$ com$,
		sys.exu8colnn cn
       WHERE c$.obj# = o$.obj# and o$.owner# = u$.user# and 
	     c$.obj# = com$.obj#(+) and c$.col# = com$.col#(+) and
	     c$.obj# = cn.tobjid and c$.intcol# = cn.intcolid
  union all
       SELECT o$.obj#, u$.name, o$.owner#, o$.name, c$.name, c$.length, 
              c$.precision#, c$.scale, c$.type#, 0,
              null, c$.col#, c$.intcol#, c$.segcol#, c$.property,
              com$.comment$,-- c$.default$, 
	      NVL(c$.deflength, 0), 0, 0, nvl(o$.flags, 0),
              NVL(c$.charsetid, 0), nvl(c$.charsetform, 0),
              c$.scale, c$.precision#
       FROM sys.col$ c$, sys.obj$ o$, sys.user$ u$, sys.com$ com$
       WHERE c$.obj# = o$.obj# and o$.owner# = u$.user# and 
	     c$.obj# = com$.obj#(+) and c$.col# = com$.col#(+)
             and bitand(c$.property,32768) != 32768     /* not unused column */
             and not exists 
              (select null from sys.exu8colnn cn
	         where c$.obj# = cn.tobjid and c$.intcol# = cn.intcolid)
/
grant select on exu8col_temp to select_catalog_role
/
create or replace view exu8col
   (tobjid, towner, townerid, tname, name, length, precision,
    scale, type, isnull, conname, colid, intcolid, segcolid, comment$,
    default$, dfltlen, enabled, defer, flags,
    colprop, adtname, adtowner, charsetid, charsetform, fsprecision, lfprecision) AS
 select tobjid, towner, townerid, v$.tname, v$.name, v$.length, v$.precision,
                    v$.scale, type, isnull, conname, colid, intcolid, segcolid,
		    comment$, default$,
                    dfltlen, enabled, defer, flags, colprop, '', '', 
                    v$.charsetid, v$.charsetform, v$.fsprecision, v$.lfprecision
   from exu8col_temp v$, sys.col$ c$
    where c$.obj# = v$.tobjid AND
          c$.intcol# = v$.intcolid AND
          (bitand(v$.colprop, 32) != 32	OR  /* not a hidden column */
	   bitand(v$.colprop,1048608) = 1048608 OR /* snapshot hidden column */
           bitand(v$.colprop,4194304) = 4194304) /* RLS hidden column */
/
grant select on exu8col to select_catalog_role
/
CREATE OR REPLACE view exu8colu AS                 /* current user's columns */
       SELECT * from exu8col WHERE townerid = uid
/
grant select on exu8colu to public;

rem
rem view to access columns in tables containing object oriented columns
rem in normal tables
rem
create or replace view exu8coo
   (tobjid, towner, townerid, tname, name, length, precision,
    scale, type, isnull, conname, colid, intcolid, segcolid, comment$,
    default$, dfltlen, enabled, defer, flags,
    colprop, adtname, adtowner, charsetid, charsetform,
    fsprecision, lfprecision) AS
 select tobjid, towner, townerid, v$.tname, v$.name, v$.length, v$.precision,
                    v$.scale, v$.type, v$.isnull, v$.conname,
                    v$.colid, v$.intcolid, v$.segcolid,
		    v$.comment$, default$,
                    v$.dfltlen, v$.enabled, v$.defer, v$.flags,
		    v$.colprop, o$.name, u$.name,
                    v$.charsetid, v$.charsetform, v$.fsprecision, v$.lfprecision
   from sys.exu8col_temp v$, sys.col$ c$, sys.coltype$ ct$, sys.obj$ o$,
        sys.user$ u$
   where 
	(bitand (v$.colprop,32) != 32 OR /* not a hidden column */
         bitand (v$.colprop,4194304) = 4194304) AND /* RLS hidden column */
	v$.tobjid = c$.obj# (+) AND
	v$.intcolid = c$.intcol# (+) AND
	v$.tobjid = ct$.obj# (+) AND
	v$.intcolid = ct$.intcol# (+) AND
	NVL(ct$.toid,HEXTORAW('00')) = o$.oid$ (+) AND
	NVL(o$.owner#,-1) = u$.user# (+) AND
        NVL(o$.type#,13) = 13 
/
grant select on exu8coo to select_catalog_role
/
CREATE OR REPLACE view exu8coou AS                 /* current user's columns */
       SELECT * from exu8coo WHERE townerid = uid
/
grant select on exu8coou to public;


rem
rem view to access columns in extent tables, extent views and
rem inner nested tables
rem
create or replace view exu8coe
   (tobjid, towner, townerid, tname, name, length, precision,
    scale, type, isnull, conname, colid, intcolid, segcolid, comment$,
    default$, dfltlen, enabled, defer, flags,
    colprop, adtname, adtowner, colclass, charsetid, charsetform, 
    fsprecision, lfprecision) AS
 select tobjid, towner, townerid, v$.tname, v$.name, v$.length, v$.precision,
                    v$.scale, v$.type, v$.isnull, v$.conname,
                    v$.colid, v$.intcolid, v$.segcolid,
		    v$.comment$, default$,
                    v$.dfltlen, v$.enabled, v$.defer, v$.flags,
		    v$.colprop, o$.name, u$.name,
		    decode (v$.name, 'SYS_NC_OID$',1, 'NESTED_TABLE_ID',2, 
                            'SYS_NC_ROWINFO$',3, 100),
                    v$.charsetid, v$.charsetform, v$.fsprecision, v$.lfprecision
   from sys.exu8col_temp v$, sys.col$ c$, sys.coltype$ ct$, sys.obj$ o$,
        sys.user$ u$
   where 
	c$.obj# = v$.tobjid and 
	c$.intcol# = v$.intcolid and
	(bitand(v$.colprop,2)  =  2 or		/* SYS_NC_OID$ */
	 bitand(v$.colprop,16) = 16 or		/* NESTED_TABLE_ID */
	 bitand(v$.colprop, 512) =  512) and	/* SYS_NC_ROWINFO$ */
	c$.obj# = ct$.obj# (+) and
	c$.intcol# = ct$.intcol# (+) and
	NVL(ct$.toid,HEXTORAW('00')) = o$.oid$ (+) and
	NVL(o$.owner#,-1) = u$.user# (+) AND
        NVL(o$.type#,-1) != 10 /* bug#882543: filter non-existent types */
/
grant select on exu8coe to select_catalog_role
/
CREATE OR REPLACE view exu8coeu AS                 /* current user's columns */
       SELECT * from exu8coe WHERE townerid = uid
/
grant select on exu8coeu to public;

rem
rem view to access columns in scalar inner nested tables
rem
create or replace view exu8csn
   (tobjid, towner, townerid, tname, name, length, precision,
    scale, type, isnull, conname, colid, intcolid, segcolid, comment$,
    default$, dfltlen, enabled, defer, flags,
    colprop, adtname, adtowner, colclass, charsetid, charsetform, 
    fsprecision, lfprecision) AS
 select tobjid, towner, townerid, v$.tname, v$.name, v$.length, v$.precision,
                    v$.scale, v$.type, v$.isnull, v$.conname,
                    v$.colid, v$.intcolid, v$.segcolid,
		    v$.comment$, default$,
                    v$.dfltlen, v$.enabled, v$.defer, v$.flags,
		    v$.colprop, o$.name, u$.name,
		    decode (v$.name, 'NESTED_TABLE_ID',2, 
                            'COLUMN_VALUE',3, 100),
                    v$.charsetid, v$.charsetform, v$.fsprecision, v$.lfprecision
   from sys.exu8col_temp v$, sys.col$ c$, sys.coltype$ ct$, sys.obj$ o$,
        sys.user$ u$
   where 
	c$.obj# = v$.tobjid and 
	c$.intcol# = v$.intcolid and
	c$.obj# = ct$.obj# (+) and
	c$.intcol# = ct$.intcol# (+) and
	NVL(ct$.toid,HEXTORAW('00')) = o$.oid$ (+) and
        NVL(o$.owner#,-1) = u$.user# (+) AND
        NVL(o$.type#,-1) != 10 /* bug#882543: filter non-existent types */
/
grant select on exu8csn to select_catalog_role
/
CREATE OR REPLACE view exu8csnu AS                 /* current user's columns */
       SELECT * from exu8csn WHERE townerid = uid
/
grant select on exu8csnu to public;

rem view to access column comments for extent table, inner nested tables
rem and extent views

CREATE OR REPLACE view exu8cmt
   (userid, objid, colno, colname, cmnt) AS
SELECT o$.owner#, cm$.obj#, cm$.col#, c$.name, cm$.comment$
FROM  sys.com$ cm$, sys.obj$ o$, sys.col$ c$
WHERE  o$.obj# = cm$.obj#
   AND c$.obj# = cm$.obj# AND c$.intcol# = cm$.col#
/
grant select on exu8cmt to select_catalog_role;

CREATE OR REPLACE view exu8cmtu AS
   SELECT * FROM exu8cmt WHERE userid = uid
/
grant select on exu8cmtu to public;

rem analyze statistics for columns (except for samples)

CREATE OR REPLACE view exu8asc
   (tobjid, pobjid, townerid, colname, intcol, distcount, lowval, hival,
    density, nullcount, avgcln, cflags)
AS
        SELECT c$.obj#, hh$.obj#, o$.owner#, c$.name, hh$.intcol#,
               hh$.distcnt, hh$.lowval, hh$.hival, hh$.density, hh$.null_cnt, 
	       hh$.avgcln, hh$.spare2
        FROM sys.hist_head$ hh$, sys.obj$ o$, sys.obj$ ot$, sys.col$ c$
        WHERE hh$.obj# = o$.obj# and
	      c$.obj# = ot$.obj# and
	      o$.owner# = ot$.owner# and
              hh$.intcol# = c$.intcol#
/
grant select on exu8asc to select_catalog_role;

CREATE OR REPLACE view exu8ascu AS
   SELECT * FROM exu8asc WHERE townerid = uid
/
grant select on exu8ascu to public;

rem histogram samples for analyze statistics

CREATE OR REPLACE view exu8hst
                   (pobjid, townerid, intcol, bucket, endpthash, endptval) AS
        SELECT h$.obj#, o$.owner#, h$.intcol#, h$.bucket,
               h$.endpoint, h$.epvalue
        FROM sys.histgrm$ h$, sys.obj$ o$
        WHERE h$.obj# = o$.obj#
      UNION ALL
        SELECT h$.obj#, o$.owner#, h$.intcol#, 0, h$.minimum, NULL
        FROM sys.hist_head$ h$, sys.obj$ o$
        WHERE h$.obj# = o$.obj# and h$.bucket_cnt = 1
      UNION ALL
        SELECT h$.obj#, o$.owner#, h$.intcol#, 1, h$.maximum, NULL
        FROM sys.hist_head$ h$, sys.obj$ o$
        WHERE h$.obj# = o$.obj# and h$.bucket_cnt = 1
/
grant select on exu8hst to select_catalog_role;

CREATE OR REPLACE view exu8hstu AS
   SELECT * FROM exu8hst WHERE townerid = uid
/
grant select on exu8hstu to public;



rem all columns for index
CREATE OR REPLACE view exu8ico 
                   (tobjid, towner, townerid, tname, name,
		    colid, property, function, funclen ) AS
       SELECT o$.obj#, u$.name, o$.owner#, o$.name,
              DECODE(bitand(c$.property,1),1,a$.name, c$.name),
              i$.pos#, c$.property, c$.default$, c$.deflength
       FROM sys.col$ c$, sys.icol$ i$, sys.obj$ o$, sys.user$ u$,
            sys.attrcol$ a$
       WHERE c$.obj# = i$.bo# and c$.intcol# = i$.intcol# and 
	     i$.obj# = o$.obj# and o$.owner# = u$.user# and
             c$.obj# = a$.obj# (+) and
             c$.intcol# = a$.intcol# (+) 
/
grant select on exu8ico to select_catalog_role
/
CREATE OR REPLACE view exu8icou AS		   /* current user's columns */
       SELECT * from exu8ico WHERE townerid = uid
/
grant select on exu8icou to public;

rem all users' default roles
CREATE OR REPLACE view exu8dfr (name, userid, role, roleid) AS
       SELECT u$.name, u$.user#, u1$.name, u1$.user#
       FROM sys.user$ u$, sys.user$ u1$, sys.defrole$ d$
       WHERE u$.user# = d$.user# and u1$.user# = d$.role#
/
grant select on exu8dfr to select_catalog_role
/
rem all roles
CREATE OR REPLACE view exu8rol (role, password) AS    /* enumerate all roles */
       SELECT name, password
       FROM sys.user$ 
       WHERE type# = 0 and name not in 
            ('CONNECT', 'RESOURCE', 'DBA', 'PUBLIC', '_NEXT_USER', 
	     'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
/
grant select on exu8rol to select_catalog_role
/
rem all role grants
CREATE OR REPLACE view exu8rlg
                   (grantee, granteeid, role, roleid, admin, sequence) AS
	SELECT u1$.name, u1$.user#, u2$.name, u2$.user#, 
	       NVL(g$.option$, 0), g$.sequence#
   	FROM sys.user$ u1$, sys.user$ u2$, sys.sysauth$ g$ 
	WHERE u1$.user# = g$.grantee# AND u2$.user# = g$.privilege# AND 
	      g$.privilege# > 0 AND
              u1$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS', 'LBACSYS')
/
grant select on exu8rlg to select_catalog_role
/
rem all system privs, type is 1 for user, 0 for role
CREATE OR REPLACE view exu8spv (grantee, granteeid, priv, wgo, sequence) AS
       SELECT u1$.name, u1$.user#, m$.name, NVL(a$.option$,0), a$.sequence#
       FROM sys.sysauth$ a$, sys.system_privilege_map m$, sys.user$ u1$
       WHERE a$.grantee# = u1$.user# and a$.privilege# = m$.privilege AND 
             bitand(m$.property, 1) != 1 AND
	     u1$.name not in 
	     ('CONNECT', 'RESOURCE', 'DBA', '_NEXT_USER', 
	     'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
             'ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS', 'LBACSYS')
/
grant select on exu8spv to select_catalog_role
/
rem all grants
CREATE OR REPLACE view exu8grn (objid, grantor, grantorid, grantee, priv, wgo,
                    creatorid, sequence, isdir) AS
       SELECT t$.obj#, ur$.name, t$.grantor#, ue$.name, 
              m$.name, NVL(t$.option$,0), o$.owner#, t$.sequence#,
              DECODE ( (o$.type#), 23, 1, 0 ) /* flag if directory alias */
       FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$,
            sys.table_privilege_map m$, sys.user$ ue$
       WHERE o$.obj# = t$.obj# AND t$.privilege# = m$.privilege AND
             t$.col# IS NULL AND t$.grantor# = ur$.user# AND
             t$.grantee# = ue$.user# AND
             ue$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS', 'LBACSYS')
/
grant select on exu8grn to select_catalog_role
/
rem just SYS's grants
CREATE OR REPLACE view exu8grs (objid, name) AS
       SELECT t$.obj#, o$.name
       FROM sys.objauth$ t$, sys.obj$ o$
       WHERE o$.obj# = t$.obj# 
	AND t$.col# is null
	AND t$.grantor# = 0
        AND o$.type# NOT IN (SELECT type# FROM sys.exppkgobj$)
/
grant select on exu8grs to select_catalog_role
/
rem first level grants
CREATE OR REPLACE view exu8grnu AS
       SELECT * from exu8grn WHERE grantorid = UID AND creatorid = UID
/
grant select on exu8grnu to public;

rem all column grants
CREATE OR REPLACE view exu8cgr 
                   (objid, grantor, grantorid, grantee, creatorid, cname, 
	            priv, sequence, wgo) AS
       SELECT c$.obj#, ur$.name, c$.grantor#, ue$.name, o$.owner#, cl$.name,
              m$.name, c$.sequence#, NVL(c$.option$,0)
       FROM sys.objauth$ c$, sys.obj$ o$, sys.user$ ur$, sys.user$ ue$,
            sys.table_privilege_map m$, sys.col$ cl$
       WHERE c$.grantor# = ur$.user# AND c$.grantee# = ue$.user# AND
             c$.obj# = o$.obj# AND c$.privilege# = m$.privilege AND 
             c$.obj# = cl$.obj# AND c$.col# = cl$.col#
/
grant select on exu8cgr to select_catalog_role
/
rem first level grants
CREATE OR REPLACE view exu8cgru AS
       SELECT * from exu8cgr WHERE grantorid = UID AND creatorid = UID
/
grant select on exu8cgru to public;
/
rem all indexes. This view is used by V8.1 and higher since it retrieves
rem	functional & domain indexes which are unknown in V8.0
rem	exu81ind_base is the base view. exu81ind is derived from base view.
CREATE OR REPLACE view exu81ind_base 
                   (iobjid, idobjid, iname, iowner, iownerid, ispace,
		    itsno, ifileno, iblockno, btname, btobjid, btowner,
		    btownerid, btproperty, btclusterflag, property,
		    cluster$, pctfree$, initrans, maxtrans, blevel,
                    bitmap, deflog, tsdeflog, degree, instances, type,
                    rowcnt, leafcnt, distkey, lblkkey,dblkkey, clufac,
		    preccnt, iflags) AS
       SELECT i$.obj#, i$.dataobj#, i$.name, ui$.name, i$.owner#, ts$.name,
              ind$.ts#, ind$.file#, ind$.block#, t$.name, t$.obj#, ut$.name,
              t$.owner#, NVL(tb$.property,0), NVL(tb$.bobj#,0),
              ind$.property,
              DECODE(t$.type#, 3, 1, 0), ind$.pctfree$, 
	      ind$.initrans, ind$.maxtrans, NVL(ind$.blevel,-1),
              DECODE(ind$.type#, 2, 1, 0),
              decode(bitand(ind$.flags,4),4,1,0), ts$.dflogging,
              NVL(ind$.degree,1), NVL(ind$.instances,1), ind$.type#,
              NVL(ind$.rowcnt,-1), NVL(ind$.leafcnt,-1), NVL(ind$.distkey,-1),
              NVL(ind$.lblkkey,-1), NVL(ind$.dblkkey,-1), NVL(ind$.clufac,-1),
	      NVL(ind$.spare2,0), ind$.flags
       FROM  sys.obj$ t$, sys.obj$ i$, sys.ind$ ind$,
	     sys.user$ ui$, sys.user$ ut$, sys.ts$ ts$, sys.tab$ tb$
       WHERE ind$.bo# = t$.obj# AND ind$.obj# = i$.obj# AND
             ind$.bo# = tb$.obj# (+) AND
             bitand(i$.flags, 4)=0 AND
	     ts$.ts# = ind$.ts# AND i$.owner# = ui$.user# AND
             t$.owner# = ut$.user# AND 
             ind$.type# != 8 AND                          /* skip LOB index */
             ind$.type# != 4 AND                            /* skip IOT top */
             bitand(ind$.flags, 4096) = 0 AND            /* skip fake index */
             (UID = 0 OR (UID = i$.owner# AND UID = t$.owner#) OR exists
             (select * from session_roles where role='SELECT_CATALOG_ROLE'))
/
grant select on exu81ind_base to public;
/

rem exu81ind is derived from base view to eliminate constraints.
rem Allow non-system-defined indices due to constraints.
rem (except when the base table is IOT).
CREATE OR REPLACE view exu81ind AS
       SELECT * from exu81ind_base
       WHERE (bitand(property, 1) = 0 OR                      /* not unique */
             NOT EXISTS 
                (SELECT * FROM sys.con$ c$, sys.cdef$ cd$
                 WHERE c$.owner# = iownerid
                 AND   c$.name = iname
                 AND   c$.con# = cd$.con#                    /* bug #686272 */
                 AND   NVL(cd$.enabled, 0) = iobjid
                 AND   (bitand(cd$.defer,8) = 8)             /* bug #735699 */
                )
             )
/
grant select on exu81ind to public;
/
                               
rem all indexes for V8.0. This view filters out V8.1 and later index types not
rem		supported in V8.0
CREATE OR REPLACE view exu8ind AS SELECT *
       FROM  exu81ind
       WHERE bitand(property, 16) != 16 AND      /* skip functional index */
             type != 9                           /* skip domain index */
/
grant select on exu8ind to select_catalog_role
/
rem current user indexes for V8.0
CREATE OR REPLACE view exu8indu AS SELECT * from exu8ind
/
grant select on exu8indu to public;

rem Additional information required for domain indexes:
rem indextype name & owner, implementation type name & owner, and params
rem "1" connections are for the index itself, "2" connections are for the
rem assoc. indextype, and  "3" for the assoc. implementation type.

CREATE OR REPLACE view exu81doi
          (iobjid, iownerid, iparams, itname, itowner, implname, implowner) AS
        SELECT  i$.obj#, o1$.owner#, i$.spare4,
                o2$.name, u2$.name, o3$.name, u3$.name
          FROM sys.ind$ i$, sys.obj$ o1$, sys.obj$ o2$, sys.obj$ o3$,
               sys.user$ u2$, sys.user$ u3$, sys.indtypes$ it$
         WHERE i$.indmethod# = it$.obj#
           AND i$.obj#       = o1$.obj#
           AND it$.obj#      = o2$.obj#
           AND it$.implobj#  = o3$.obj#
           AND o2$.owner#    = u2$.user#
           AND o3$.owner#    = u3$.user#
/
grant select on exu81doi to select_catalog_role;

rem Domain index info for current user
CREATE OR REPLACE view exu81doiu AS
        SELECT * from exu81doi where iownerid = UID
/
grant select on exu81doiu to public;


rem Obtain all context binding info
CREATE OR REPLACE view exu81ctx
   (ctxname, shmname, pkgname, objno) AS
   SELECT o$.name, c$.schema, c$.package, o$.obj#
   FROM sys.exu81obj o$, sys.context$ c$
   WHERE o$.type# = 44      /* context */
     AND o$.obj# = c$.obj#
/
grant select on sys.exu81ctx to select_catalog_role;

rem TEST FOR TRUSTED ORACLE
CREATE OR REPLACE view exu816tctx
   (COLS) AS
   SELECT COLS FROM SYS.TAB$ t, SYS.OBJ$ o WHERE t.obj# = o.obj#
   AND o.NAME = 'CONTEXT$' AND o.TYPE# = 2 AND o.OWNER# = 0;
/
grant select on sys.exu816tctx to select_catalog_role;



rem dependency order -- only used for ordering views
CREATE OR REPLACE view exu8ord (dlevel, obj#) AS
       SELECT MAX(LEVEL), d.d_obj#
         FROM sys.dependency$ d
         WHERE
           EXISTS (select v.obj# from sys.view$ v where v.obj# = d.d_obj#)
         START WITH
           EXISTS (select v.obj# from sys.view$ v where v.obj# = d.d_obj#)
         CONNECT BY PRIOR d.d_obj# = d.p_obj# 
         GROUP BY d.d_obj#, d.d_owner#
/
grant select on exu8ord to public;

rem current user's dependency order of views
CREATE OR REPLACE view exu8ordu (dlevel, obj#) AS
       SELECT MAX(LEVEL), d.d_obj#
         FROM  sys.dependency$ d
         WHERE d.d_owner# = uid and
           EXISTS (select v.obj# from sys.view$ v where v.obj# = d.d_obj#)
         START WITH
           EXISTS (select v.obj# from sys.view$ v where v.obj# = d.d_obj#)
         CONNECT BY PRIOR d.d_obj# = d.p_obj# 
         GROUP BY d.d_obj#, d.d_owner#
/
grant select on exu8ordu to public;

rem all views 
CREATE OR REPLACE view exu8vew (vobjid,vname, vtext, vowner, vownerid,
		    vaudit, vcomment, vcname, vlevel,
                    property, defer,flags,  oidlen, oidclause,
                    typeowner, typename, vlen, sqlver ) AS
       SELECT
	o$.obj#, o$.name,  v$.text, u$.name, o$.owner#, 
  	      v$.audit$, com$.comment$, c$.name,
	      d$.dlevel, v$.property,
              nvl(cd$.defer, 0), nvl(o$.flags, 0),
	      NVL(vt$.oidtextlength, 0),
	      vt$.oidtext,
              vt$.typeowner, vt$.typename, v$.textlength, sv$.sql_version
       FROM sys.exu81obj o$, sys.view$ v$, sys.user$ u$, sys.cdef$ cd$,
            sys.con$ c$, sys.com$ com$, exu8ord d$, sys.typed_view$ vt$,
            sys.exu816sqv sv$
       WHERE v$.obj# = o$.obj# AND o$.owner# = u$.user# AND
	     o$.obj# = cd$.obj#(+) AND cd$.con# = c$.con#(+) AND 
             o$.obj# = com$.obj#(+) AND com$.col#(+) IS NULL AND
	     o$.obj# = d$.obj#(+) AND v$.obj# = vt$.obj# (+) AND
             o$.spare1 = sv$.version# (+)
/
grant select on exu8vew to select_catalog_role
/
rem views for incremental export: new or last export not valid
rem cannot use union as in exutabi because of long field
CREATE OR REPLACE view exu8vewi AS 
       SELECT vw.* from exu8vew vw, incexp i, incvid v
       WHERE i.name(+) = vw.vname AND i.owner#(+) = vw.vownerid
             AND v.expid < NVL(i.expid, 9999) AND NVL(i.type#, 4) = 4
/
grant select on exu8vewi to select_catalog_role   
/  
rem views for cumulative export: new, last export was inc or not valid
CREATE OR REPLACE view exu8vewc AS 
       SELECT vw.* from exu8vew vw, incexp i, incvid v
       WHERE vw.vname = i.name(+) AND vw.vownerid = i.owner#(+) AND 
             NVL(i.type#,4) = 4 AND
             (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR 
              v.expid < NVL(i.expid, 9999))
/
grant select on exu8vewc to select_catalog_role
/
rem current user's view
CREATE OR REPLACE view exu8vewu (vobjid,vname, vtext, vowner, vownerid,
		  vaudit, vcomment, vcname, vlevel, property, defer, flags,
		    oidlen, oidclause, typeowner, typename, vlen, sqlver ) AS
       SELECT
	o$.obj#, o$.name,  v$.text, u$.name, o$.owner#, 
  	      v$.audit$, com$.comment$, c$.name, d$.dlevel, v$.property,
              nvl(cd$.defer, 0), nvl(o$.flags, 0), 
	      NVL(vt$.oidtextlength, 0), vt$.oidtext,
              vt$.typeowner, vt$.typename, v$.textlength,sv$.sql_version 
       FROM sys.exu81obj o$, sys.view$ v$, sys.user$ u$, sys.cdef$ cd$,
            sys.con$ c$, sys.com$ com$, exu8ordu d$, sys.typed_view$ vt$,
            sys.exu816sqv sv$
       WHERE v$.obj# = o$.obj# AND o$.owner# = u$.user# AND
	     o$.obj# = cd$.obj#(+) AND cd$.con# = c$.con#(+) AND 
             o$.obj# = com$.obj#(+) AND com$.col#(+) IS NULL AND
	     o$.obj# = d$.obj#(+) AND v$.obj# = vt$.obj#(+) AND
             u$.user# = uid AND o$.spare1 = sv$.version# (+)
/
grant select on exu8vewu to public;


rem get dependency info for views that depend on other views
rem *** obsolete in 8.1.6 ***
CREATE OR REPLACE view exu8vdpt (parent, child, powner, cowner ) AS
   SELECT d$.p_obj#, d$.d_obj#, o1$.owner#, o2$.owner#
   FROM   sys.dependency$ d$, sys.obj$ o1$, sys.obj$ o2$,
          sys.view$ v1$, sys.view$ v2$
   WHERE  d$.p_obj# = v1$.obj# AND v1$.obj# = o1$.obj#
      AND d$.d_obj# = v2$.obj# AND v2$.obj# = o2$.obj#
/
grant select on exu8vdpt to select_catalog_role;

rem get dependency info for views that depend on other views
rem for current user
rem note, that even if user does not have privs, we need to 
rem include views of other other users, to get the proper ordering.
rem *** obsolete in 8.1.6 ***
CREATE OR REPLACE view exu8vdptu (parent, child, powner, cowner ) AS
   SELECT *
   FROM   sys.exu8vdpt 
/
grant select on exu8vdptu to public;

rem get all information about a given view
rem *** obsolete in 8.1.6 ***

CREATE OR REPLACE view exu8vinf (vobjid, vname, vtext, vowner, vownerid,
		    vaudit, vcomment, vcname, 
                    property, defer, flags,oidlen, oidclause,
                    typeowner, typename, vlen ) AS
       SELECT
	o$.obj#, o$.name, v$.text, u$.name, o$.owner#, 
  	      v$.audit$, com$.comment$, c$.name,
	      v$.property,
              nvl(cd$.defer, 0), nvl(o$.flags, 0),
	      NVL(vt$.oidtextlength, 0),
	      vt$.oidtext,
              vt$.typeowner, vt$.typename, v$.textlength
       FROM sys.obj$ o$, sys.view$ v$, sys.user$ u$, sys.cdef$ cd$,
            sys.con$ c$, sys.com$ com$, sys.typed_view$ vt$
       WHERE v$.obj# = o$.obj# AND o$.owner# = u$.user# AND
	     o$.obj# = cd$.obj#(+) AND cd$.con# = c$.con#(+) AND 
             o$.obj# = com$.obj#(+) AND com$.col#(+) IS NULL AND
	     v$.obj# = vt$.obj# (+)
/
grant select on exu8vinf to select_catalog_role;

rem views for incremental export: new or last export not valid
rem cannot use union as in exutabi because of long field
rem *** obsolete in 8.1.6 ***
CREATE OR REPLACE view exu8vinfi AS 
       SELECT vw.* from exu8vinf vw, incexp i, incvid v
       WHERE i.name(+) = vw.vname AND i.owner#(+) = vw.vownerid
             AND v.expid < NVL(i.expid, 9999) AND NVL(i.type#, 4) = 4
/
grant select on exu8vinfi to select_catalog_role; 

rem views for cumulative export: new, last export was inc or not valid
rem *** obsolete in 8.1.6 ***
CREATE OR REPLACE view exu8vinfc AS 
       SELECT vw.* from exu8vinf vw, incexp i, incvid v
       WHERE vw.vname = i.name(+) AND vw.vownerid = i.owner#(+) AND 
             NVL(i.type#,4) = 4 AND
             (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR 
              v.expid < NVL(i.expid, 9999))
/
grant select on exu8vinfc to select_catalog_role;

rem current user's view
rem *** obsolete in 8.1.6 ***
CREATE OR REPLACE view exu8vinfu (vobjid, vname, vtext, vowner, vownerid,
		    vaudit, vcomment, vcname, property, defer,
                    flags,oidlen, oidclause, typeowner, typename, vlen ) AS
       SELECT *
       FROM  sys.exu8vinf
       WHERE vownerid = UID
/
grant select on exu8vinfu to public;

rem get all information about views with no dependent views
rem *** obsolete in 8.1.6 ***
CREATE OR REPLACE view exu8vnc (vobjid,vname, vtext, vowner, vownerid,
		    vaudit, vcomment, vcname, 
                    property, defer, flags,oidlen, oidclause,
                    typeowner, typename, vlen ) AS
       SELECT * 
       FROM   sys.exu8vinf vf$ 
       WHERE  NOT EXISTS ( select 0 from sys.exu8vdpt vd$
                              where vd$.parent = vf$.vobjid )
/
grant select on exu8vnc to select_catalog_role;

rem views without dependent views for incremental export:
rem new or last export not valid
rem cannot use union as in exutabi because of long field
rem *** obsolete in 8.1.6 ***
CREATE OR REPLACE view exu8vnci AS 
       SELECT vw.* from exu8vnc vw, incexp i, incvid v
       WHERE i.name(+) = vw.vname AND i.owner#(+) = vw.vownerid
             AND v.expid < NVL(i.expid, 9999) AND NVL(i.type#, 4) = 4
/
grant select on exu8vnci to select_catalog_role; 

rem views without dependent views for cumulative export:
rem  new, last export was inc or not valid
rem *** obsolete in 8.1.6 ***
CREATE OR REPLACE view exu8vncc AS 
       SELECT vw.* from exu8vnc vw, incexp i, incvid v
       WHERE vw.vname = i.name(+) AND vw.vownerid = i.owner#(+) AND 
             NVL(i.type#,4) = 4 AND
             (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR 
              v.expid < NVL(i.expid, 9999))
/
grant select on exu8vncc to select_catalog_role;

rem current user's views without dependent views
rem *** obsolete in 8.1.6 ***
CREATE OR REPLACE view exu8vncu (vobjid,vname, vtext, vowner, vownerid,
		  vaudit, vcomment, vcname, property, defer, flags,
                  oidlen, oidclause, typeowner, typename, vlen) AS
       SELECT *
       FROM  sys.exu8vinfu vf$
       WHERE NOT EXISTS ( select 0 from sys.exu8vdptu vd$
                          where vd$.parent = vf$.vobjid )
/
grant select on exu8vncu to public;


rem all synonyms
CREATE OR REPLACE view exu8syn (synnam, syntab, tabown, tabnode,
                        public$, synown, synownid, syntime) AS
       SELECT o$.name, s$.name, s$.owner, s$.node,
              DECODE(o$.owner#, 1, 1, 0),
              uo$.name, o$.owner#, o$.ctime
       FROM sys.exu81obj o$, sys.syn$ s$, sys.user$ us$, sys.user$ uo$
       WHERE s$.obj# = o$.obj# AND o$.owner# = uo$.user# AND
             s$.owner = us$.name(+) AND
             NVL(s$.owner, 'SYS') not in 
                 ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS', 'LBACSYS')
/
grant select on exu8syn to select_catalog_role
/
rem synonyms for incremental export: new or last export not valid
CREATE OR REPLACE view exu8syni AS 
       SELECT s.* from exu8syn s, incexp i, incvid v
       WHERE s.synnam = i.name(+) AND s.synownid = i.owner#(+) AND 
             NVL(i.type#,5) = 5 AND NVL(i.expid,9999) > v.expid
/	     
grant select on exu8syni to select_catalog_role
/
rem synonyms for cumulative export: new, last export was inc or not valid
CREATE OR REPLACE view exu8sync AS 
       SELECT s.* from exu8syn s, incexp i, incvid v
       WHERE  s.synnam  = i.name(+) AND s.synownid = i.owner#(+) AND 
              NVL(i.type#,5) = 5 AND
	      (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR 
               NVL(i.expid,9999) > v.expid)
/
grant select on exu8sync to select_catalog_role
/
rem user's synnonyms
CREATE OR REPLACE view exu8synu AS
       SELECT * from exu8syn WHERE synownid = UID
/
grant select on exu8synu to public;

rem clustered tables' columns
CREATE OR REPLACE view exu8cco
                   (tname, towner, townerid, cluster$, tcolnam, seq,
                    property ) AS
       SELECT t$.name, u$.name, t$.owner#, c$.name,
              DECODE( bitand( tc$.property, 1 ),1, a$.name, tc$.name ),
              cc$.col#, tc$.property
       FROM sys.obj$ t$, sys.tab$ tab$, sys.obj$ c$,
	    sys.col$ tc$, sys.col$ cc$, sys.user$ u$, sys.attrcol$ a$
       WHERE t$.type# = 2 AND t$.obj# = tab$.obj# AND
  	     tab$.bobj# = cc$.obj# AND tab$.obj# = tc$.obj# AND 
             tab$.bobj# = c$.obj# AND 
             cc$.segcol# = tc$.segcol# AND t$.owner# = u$.user# AND
             tc$.obj#    = a$.obj# (+) AND
             tc$.intcol# = a$.intcol# (+)
/
grant select on exu8cco to select_catalog_role
/
rem current user's clustered tables' columns
CREATE OR REPLACE view exu8ccou AS
       SELECT * from exu8cco WHERE townerid = UID
/
grant select on exu8ccou to public;

rem all clusters
CREATE OR REPLACE view exu8clu 
                   (objid, dobjid, owner, ownerid, name, tblspace, size$,
                    tsno, fileno,
	            blockno, mtime, pctfree$, pctused$, initrans, maxtrans,
		    hashkeys, function, avgchn, degree, instances, cache, 
		    functxt, funclen, single_table, sqlver) AS
       SELECT o$.obj#, o$.dataobj#, u$.name, o$.owner#, o$.name, ts$.name, 
	      NVL(c$.size$, -1), ts$.ts#, c$.file#, c$.block#, o$.mtime, 
              mod(c$.pctfree$, 100), 
              c$.pctused$, c$.initrans, c$.maxtrans, NVL(c$.hashkeys, 0), 
	      NVL(c$.func, 1), NVL(c$.avgchn,-1), 
	      NVL(c$.degree, 1), NVL(c$.instances, 1),
	      decode(bitand(c$.flags,8), 8, 1, 0),
	      cd$.condition, cd$.condlength,
	      decode(bitand(c$.flags,65536), 65536, 1, 0), sv$.sql_version
       FROM sys.obj$ o$, sys.clu$ c$, sys.ts$ ts$, sys.user$ u$,
	    sys.cdef$ cd$, sys.exu816sqv sv$
       WHERE o$.obj# = c$.obj# AND c$.ts# = ts$.ts# AND o$.owner# = u$.user#
	     AND cd$.obj#(+) = c$.obj# AND o$.spare1 = sv$.version# (+) 
/
grant select on exu8clu to select_catalog_role
/
rem clusters for incremental export: new or last export invalid
rem altered cluster is now exported because its tables are also exported
CREATE OR REPLACE view exu8clui AS 
       SELECT c.* from exu8clu c,incexp i, incvid v
       WHERE c.name  = i.name(+) AND c.ownerid = i.owner#(+) AND
	    (c.mtime > i.itime OR NVL(i.expid,9999) > v.expid)
/
grant select on exu8clui to select_catalog_role
/
rem clusters for cumulative export: last export was inc or new
rem altered cluster is now exported because its tables are also exported
CREATE OR REPLACE view exu8cluc AS 
       SELECT c.* from exu8clu c,incexp i, incvid v
       WHERE c.name = i.name(+) AND c.ownerid = i.owner#(+) AND 
             NVL(i.type#,3) = 3 AND
	      (i.itime > NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY'))
		OR c.mtime > i.itime OR NVL(i.expid,9999) > v.expid)
/
grant select on exu8cluc to select_catalog_role
/
rem current user's clusters
CREATE OR REPLACE view exu8cluu AS
       SELECT * from exu8clu WHERE ownerid = UID
/
grant select on exu8cluu to public;

rem all storage parameters
CREATE OR REPLACE view exu8sto (ownerid, tsno, fileno, blockno, iniext, sext,
                    minext, maxext, pctinc, blocks, lists, groups, extents,
                    pcache, ts_type, tsname, isonline) AS
       SELECT s$.user#, s$.ts#, s$.file#, s$.block#, s$.iniexts, s$.extsize, 
              s$.minexts, s$.maxexts, s$.extpct, s$.blocks, 
              decode(s$.lists, NULL, 1, 65535, 1, lists),
              decode(s$.groups, NULL, 1, 65535, 1, groups), extents,
              decode(s$.cachehint, 0,'DEFAULT', 1, 'KEEP', 2, 'RECYCLE'),
              decode(s$.spare1, 1, NVL(ts$.flags, -1), 0, -1, -1), ts$.name,
              decode(ts$.online$, 1, 1, 4, 1, 0)
       FROM sys.seg$ s$, sys.ts$ ts$
       WHERE s$.ts# = ts$.ts#(+)
/ 
grant select on exu8sto to select_catalog_role
/
rem storage parameters for current user's segments
CREATE OR REPLACE view exu8stou AS
       SELECT * from exu8sto WHERE ownerid = UID
/
grant select on exu8stou to public;

rem find out correct size of second extent using uet$
CREATE OR REPLACE view exu8tne (tsno, fileno, blockno, length) AS
	SELECT ts#, segfile#, segblock#, length
	from uet$ where ext# = 1
/
grant select on exu8tne to public;

rem find out correct size of second extent using x$ktfbue (for bitmapped TS)
CREATE OR REPLACE view exu8tneb (tsno, fileno, blockno, length) AS
        SELECT KTFBUESEGTSN, KTFBUESEGFNO, KTFBUESEGBNO, KTFBUEBLKS
        from x$ktfbue where KTFBUEEXTNO = 1
/
grant select on exu8tneb to public;


rem all tablespaces
CREATE OR REPLACE view exu81tbs 
                   (id, owner, name, isonline, content, iniext, sext, pctinc,
		    minext, maxext, minlen, deflog, ext_mgt, alloc_type) AS
       SELECT ts$.ts#, 'SYSTEM', ts$.name, 
	      DECODE(ts$.online$, 1, 'ONLINE', 4, 'ONLINE', 'OFFLINE'),
	      DECODE(ts$.contents$, 0, 'PERMANENT', 1, 'TEMPORARY'),
	      ts$.dflinit, ts$.dflincr, ts$.dflextpct, ts$.dflminext, 
              ts$.dflmaxext, NVL(ts$.dflminlen,0),
              ts$.dflogging, ts$.bitmapped, ts$.flags
       FROM sys.ts$ ts$ 
       WHERE ts$.online$ in (1, 2, 4) and ts$.ts# != 0
/
grant select on exu81tbs to select_catalog_role
/
rem all tablespaces for 8.0
CREATE OR REPLACE view exu8tbs 
                   (id, owner, name, isonline, content, iniext, sext, pctinc,
		    minext, maxext, deflog) AS
       SELECT tbs$.id, tbs$.owner, tbs$.name, tbs$.isonline, tbs$.content,
              tbs$.iniext, tbs$.sext, tbs$.pctinc, tbs$.minext, tbs$.maxext,
              tbs$.deflog
       FROM exu81tbs tbs$ 
       WHERE tbs$.ext_mgt = 0
/
grant select on exu8tbs to select_catalog_role
/

rem tablespace quotas
CREATE OR REPLACE view exu8tsq(tsname, tsid, uname, userid, maxblocks) AS
       SELECT t$.name, q$.ts#, u$.name, u$.user#, q$.maxblocks
       FROM  sys.ts$ t$, sys.tsq$ q$, sys.user$ u$
       WHERE  q$.user# = u$.user# AND q$.ts# = t$.ts# AND q$.maxblocks != 0
	    		AND t$.online$ in (1, 2, 4)
/
grant select on exu8tsq to select_catalog_role
/

rem tablespace names
CREATE OR REPLACE view exu8tsn(tsname, tsid) AS
	SELECT t$.name, t$.ts#
	FROM sys.ts$ t$
/

grant select on exu8tsn to select_catalog_role
/

rem 1. files from user mapped tablespaces
rem 2. bitmapped tablespaces (derived from dba_data_files)
rem 3. bitmapped temporary tablespaces (derived from dba_temp_files)
CREATE OR REPLACE view exu81fil(fname, fsize, maxextend, inc, tsid, bitmap) AS
       SELECT v$.name, f$.blocks, f$.maxextend, f$.inc, f$.ts#, 0
       FROM   sys.file$ f$, sys.v$dbfile v$
       WHERE  f$.file# = v$.file# and f$.spare1 is NULL
union all
select /*+ ordered use_nl(f) use_nl(hc) */
       v$.name,
       decode(hc.ktfbhccval, 0, hc.ktfbhcsz, -1),
       decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
       f$.ts#, ts$.bitmapped
from sys.ts$ ts$, sys.file$ f$, sys.v$dbfile v$, sys.x$ktfbhc hc
where v$.file# = f$.file#
  and f$.spare1 is NOT NULL
  and f$.file# = hc.ktfbhcafno
  and hc.ktfbhctsn(+) = ts$.ts#
union all
select /*+ ordered use_nl(hc) */
       v$.fnnam,
       decode(hc.ktfthccval, 0, hc.ktfthcsz, -1),
       decode(hc.ktfthccval, 0, hc.ktfthcmaxsz, NULL),
       decode(hc.ktfthccval, 0, hc.ktfthcinc, NULL),
       ts$.ts#, ts$.bitmapped
from sys.ts$ ts$, sys.x$kccfn v$, sys.x$ktfthc hc
where v$.fntyp = 7 and v$.fnnam is not null
  and v$.fnfno = hc.ktfthctfno
  and hc.ktfthctsn(+) = ts$.ts#
/
grant select on exu81fil to select_catalog_role
/
rem all files for 8.0
CREATE OR REPLACE view exu8fil(fname, fsize, maxextend, inc, tsid) AS
       SELECT fname, fsize, maxextend, inc, tsid 
       FROM   exu81fil WHERE bitmap = 0
/
grant select on exu8fil to select_catalog_role
/

rem all database links
CREATE OR REPLACE view exu8lnk 
                   (owner, ownerid, name, user$, passwd, host, public$) AS
       SELECT DECODE(l$.owner#, 1, 'SYSTEM', u$.name), l$.owner#, l$.name, 
              l$.userid, l$.password, l$.host, DECODE(l$.owner#, 1, 1, 0)
       FROM sys.user$ u$, sys.link$ l$
       WHERE u$.user# = l$.owner#
/
grant select on exu8lnk to select_catalog_role
/
CREATE OR REPLACE view exu8lnku AS	    /* current user's database links */
       SELECT * from exu8lnk WHERE ownerid = UID
/
grant select on exu8lnku to public;

rem all rollback segments
CREATE OR REPLACE view exu8rsg 
             (owner, name, space$, tsno, fileno , blockno, minext, public$) AS 
       SELECT 'SYSTEM', r$.name, ts$.name, r$.ts#, r$.file#, r$.block#,
	      s$.minexts, DECODE(r$.user#, 1, 1, 0)
       FROM sys.ts$ ts$, sys.undo$ r$, sys.seg$ s$
       WHERE r$.status$ != 1 AND r$.file# = s$.file# AND r$.block# = s$.block#
             AND s$.ts# = ts$.ts# AND r$.ts# = s$.ts#
	     AND r$.us# != 0
/
grant select on exu8rsg to select_catalog_role
/
rem info on deleted objects EXCEPT snapshots, snapshot logs, 
CREATE OR REPLACE view exu8del (owner, name, type, type#) AS 
       SELECT u$.name, i$.name, DECODE(i$.type#, 2, 'TABLE', 3,
              'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
              7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
              12, 'TRIGGER', 11, 'PACKAGE BODY',
              28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
              32, 'INDEXTYPE', 33, 'OPERATOR', 43, 'DIMENSION',
              56, 'JAVA DATA'),
              i$.type#
       FROM sys.incexp i$, sys.user$ u$, sys.obj$ o$
       WHERE i$.owner# = u$.user#
             AND i$.type# NOT IN (99, 98 )
             AND i$.owner# = o$.owner# (+) /* "+ 0" for sort-merge outer jn */
             AND i$.name   = o$.name (+)
             AND i$.type#   = o$.type# (+)
             AND o$.owner# is NULL AND o$.linkname is NULL
/
grant select on exu8del to select_catalog_role
/
rem info on sequence number
CREATE OR REPLACE view exu8seq 
                   (owner, ownerid, name, objid, curval, minval, maxval, 
		    incr, cache, cycle, order$, audt) AS
       SELECT u.name, u.user#, o.name, o.obj#, s.highwater, s.minvalue, 
	      s.maxvalue, s.increment$, s.cache, s.cycle#, s.order$, s.audit$
       FROM sys.exu81obj o, sys.user$ u, sys.seq$ s
       WHERE o.obj# = s.obj# AND o.owner# = u.user#
/
grant select on exu8seq to select_catalog_role
/
CREATE OR REPLACE view exu8sequ AS 
       SELECT * from sys.exu8seq WHERE UID = ownerid
/
grant select on sys.exu8sequ to public;

rem contraints on table
CREATE OR REPLACE view exu8con 
                   (objid, owner, ownerid, tname, type, cname, cno, condition,
                    condlength, enabled, defer, sqlver) AS
       SELECT o.obj#, u.name, c.owner#, o.name, cd.type#, c.name,
              c.con#, cd.condition, cd.condlength, NVL(cd.enabled,0),
              nvl(cd.defer, 0), sv.sql_version
       FROM sys.obj$ o, sys.user$ u, sys.con$ c, sys.cdef$ cd, 
            sys.exu816sqv sv
       WHERE u.user# = c.owner# AND o.obj# = cd.obj# AND cd.con# = c.con# AND
             cd.spare1 = sv.version# (+)
/
grant select on exu8con to select_catalog_role
/
CREATE OR REPLACE view exu8conu AS 
       SELECT * from sys.exu8con WHERE UID = ownerid
/
grant select on sys.exu8conu to public;

rem referential constraints
CREATE OR REPLACE view exu8ref 
                   (objid, owner, ownerid, tname, rowner, rtname, cname, cno,
	            rcno, action, enabled, defer, property,
                    robjid, rownerid, reftype) AS
       SELECT o.obj#, u.name, c.owner#, o.name, ru.name, ro.name, c.name,
	      c.con#, cd.rcon#, NVL(cd.refact,0), NVL(cd.enabled,0), 
              nvl(cd.defer, 0), t.property, cd.robj#, ro.owner#,
              nvl(rf.reftyp, 0)
       FROM sys.user$ u, sys.user$ ru, sys.exu81obj o, sys.obj$ ro, 
            sys.con$ c, sys.cdef$ cd, sys.tab$ t, sys.refcon$ rf
       WHERE u.user# = c.owner# AND o.obj# = cd.obj# AND ro.obj# = cd.robj# AND
             cd.con# = c.con# AND cd.type# = 4 AND ru.user# = ro.owner#
             AND t.obj# = o.obj# AND o.obj# = rf.obj# (+)
             AND u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS', 
                                'LBACSYS')
/
grant select on exu8ref to select_catalog_role
/
CREATE OR REPLACE view exu8refu
  AS SELECT * from sys.exu8ref WHERE UID = ownerid
/
grant select on sys.exu8refu to public;

rem referential constraints for incremental and cumulative export
rem for tables just exported, i.expid will be greater than v.expid
rem as v.expid is incremented only at the end of the incremental export
rem but i.expid is incremented when the table is exported.
rem USED ONLY WHEN RECORD = YES
CREATE OR REPLACE view exu8refic AS
       SELECT * from sys.exu8ref 
       WHERE (ownerid, tname) in 
             (SELECT i.owner#, i.name 
              FROM sys.incexp i, sys.incvid v
              WHERE i.expid > v.expid AND i.type# = 2)
/
grant select on exu8refic to select_catalog_role
/
rem referential constraints for incremental export
rem exutabi will return the correct table name because RECORD = NO
CREATE OR REPLACE view exu81refi AS
       SELECT * from sys.exu8ref
       WHERE (ownerid, tname) in (SELECT ownerid, name from sys.exu81tabi)
/
grant select on exu81refi to select_catalog_role
/
rem referential constraints for cumulative export, assuming
rem exutabc will return the correct table name because RECORD = NO
CREATE OR REPLACE view exu81refc AS
       SELECT * from sys.exu8ref
       WHERE (ownerid, tname) in (SELECT ownerid, name from sys.exu81tabc)
/
grant select on exu81refc to select_catalog_role
/
rem 8.0 referential constraints for incremental export
rem exutabi will return the correct table name because RECORD = NO
CREATE OR REPLACE view exu8refi AS
       SELECT * from sys.exu8ref
       WHERE (ownerid, tname) in (SELECT ownerid, name from sys.exu8tabi)
/
grant select on exu8refi to select_catalog_role
/
rem 8.0 referential constraints for cumulative export, assuming
rem exutabc will return the correct table name because RECORD = NO
CREATE OR REPLACE view exu8refc AS
       SELECT * from sys.exu8ref
       WHERE (ownerid, tname) in (SELECT ownerid, name from sys.exu8tabc)
/
grant select on exu8refc to select_catalog_role
/
rem contraint column list
CREATE OR REPLACE view exu8ccl (ownerid, cno, colname, colno,
       intcol, property ) AS
       SELECT o.owner#, cc.con#,
          DECODE( bitand(c.property,1), 1, at.name, c.name ),
          cc.pos#,cc.intcol#,
          c.property
       FROM sys.obj$ o, sys.col$ c, sys.ccol$ cc, sys.attrcol$ at
       WHERE o.obj# = cc.obj# AND c.obj# = cc.obj# 
          AND cc.intcol# = c.intcol#
          AND c.obj# = at.obj# (+)
          AND c.intcol# = at.intcol# (+)
/
grant select on exu8ccl to select_catalog_role
/
CREATE OR REPLACE view exu8cclu AS
       SELECT * from sys.exu8ccl WHERE UID = ownerid
/
grant select on sys.exu8cclu to public
/
CREATE OR REPLACE view exu8cclo (ownerid, cno, colname, colno,
        property ) AS
	SELECT a.ownerid, a.cno, a.colname, a.colno, a.property
	from sys.exu8ccl a, sys.con$ b , sys.cdef$ c
	WHERE b.owner#=UID
	AND   b.con# = c.con#
	AND   c.rcon# = a.cno
/
grant select on sys.exu8cclo to public
/
rem triggers on tables and views
rem
rem Replace previous outer joins with subqueries
rem

CREATE OR REPLACE view exu816tgr
                   (ownerid, owner, baseobject, definition, whenclause, 
                    action, enabled, tproperty, name,
                    basename, basetype, property, btowner, btownerid,
                    sys_evts, 
 		    sqlver, 
		    actionsize /* Obsolete in 8.0.4 */ ) AS
       SELECT o.owner#, u.name, t.baseobject, t.definition, t.whenclause, 
              t.action#, t.enabled, t.property, o.name, 
              DECODE( bitand(t.property,24), 0,
                        (SELECT o2.name from sys.exu81obj o2
                        where t.baseobject=o2.obj#)
                     , ''),
              DECODE( bitand(t.property,24), 0,
                        (SELECT o2.type# from sys.exu81obj o2
                        where t.baseobject=o2.obj#)
                    , 0),
              NVL((SELECT tb.property from sys.tab$ tb
                  where t.baseobject=tb.obj#), 0),
              NVL((SELECT ut.name from user$ ut, sys.exu81obj o2
                   where t.baseobject=o2.obj# and o2.owner#=ut.user#)
                , ''),
              NVL((SELECT ut.user# from user$ ut, sys.exu81obj o2
                   where t.baseobject=o2.obj# and o2.owner#=ut.user#)
                , 0),
              t.sys_evts,
	      (SELECT sv.sql_version
	         FROM sys.exu816sqv sv
	         WHERE o.spare1 = sv.version#),
	      t.actionsize		/* Obsolete as of 8.0.4 */ 
       FROM sys.exu81obj o, sys.trigger$ t, sys.user$ u
       WHERE o.obj# = t.obj# AND
             u.user# = o.owner# 
/
grant select on exu816tgr to select_catalog_role
/
rem pre-8.1.6 - filter out enhanced system events
CREATE OR REPLACE view exu81tgr 
                   (ownerid, owner, baseobject, definition, whenclause, 
                    action, enabled, tproperty, name,
                    basename, basetype, property, btowner, btownerid,
                    /* Obsolete in 8.0.4 */ actionsize) AS
       SELECT t.ownerid, t.owner, t.baseobject, t.definition, t.whenclause, 
              t.action, t.enabled, t.tproperty, t.name, 
              t.basename, t.basetype, t.property, t.btowner, t.btownerid,
              t.actionsize
       FROM sys.exu816tgr t 
       WHERE bitand(t.sys_evts,255) = t.sys_evts
/
grant select on exu81tgr to select_catalog_role
/
rem pre-8.1 filter out system events, call triggers, Java triggers, etc...
rem         (all but simple table and view triggers)
CREATE OR REPLACE view exu8tgr 
                   (ownerid, owner, baseobject, definition, whenclause, 
                    action, enabled, name,
                    basename, basetype, property, btowner, btownerid,
                    /* Obsolete in 8.0.4 */ actionsize) AS
       SELECT t.ownerid, t.owner, t.baseobject, t.definition, t.whenclause, 
              t.action, t.enabled, t.name,
              t.basename, t.basetype, t.property, t.btowner, t.btownerid,
              t.actionsize
       FROM sys.exu816tgr t 
       WHERE bitand(t.tproperty,1) = t.tproperty
/
grant select on exu8tgr to select_catalog_role
/
CREATE OR REPLACE view exu816tgru AS
       SELECT * FROM sys.exu816tgr WHERE UID = ownerid
/
grant select on sys.exu816tgru to public
/
CREATE OR REPLACE view exu81tgru AS
       SELECT * FROM sys.exu81tgr WHERE UID = ownerid
/
grant select on sys.exu81tgru to public
/
CREATE OR REPLACE view exu8tgru AS
       SELECT * FROM sys.exu8tgr WHERE UID = ownerid
/
grant select on sys.exu8tgru to public
/
rem triggers for incremental and cumulative export for table just 
rem exported.  See comment on exu8refic.
CREATE OR REPLACE view exu816tgric as 
       SELECT * FROM sys.exu816tgr 
       WHERE (ownerid, basename) in
            (SELECT i.owner#, i.name
             FROM sys.incexp i, sys.incvid v
             WHERE i.expid > v.expid AND i.type# IN ( 2, 4 ) )
/
grant select on exu816tgric to select_catalog_role
/
CREATE OR REPLACE view exu81tgric as 
       SELECT * FROM sys.exu81tgr 
       WHERE (ownerid, basename) in
            (SELECT i.owner#, i.name
             FROM sys.incexp i, sys.incvid v
             WHERE i.expid > v.expid AND i.type# IN ( 2, 4 ) )
/
grant select on exu81tgric to select_catalog_role
/
CREATE OR REPLACE view exu8tgric as 
       SELECT * FROM sys.exu8tgr 
       WHERE (ownerid, basename) in
            (SELECT i.owner#, i.name
             FROM sys.incexp i, sys.incvid v
             WHERE i.expid > v.expid AND i.type# IN ( 2, 4 ) )
/
grant select on exu8tgric to select_catalog_role
/
rem triggers for incremental export: record=no
CREATE OR REPLACE view exu816tgri as 
       SELECT * FROM sys.exu816tgr
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu81tabi)
 UNION ALL
       SELECT * FROM sys.exu816tgr
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu8vinfi)
/
grant select on exu816tgri to select_catalog_role
/
CREATE OR REPLACE view exu81tgri as 
       SELECT * FROM sys.exu81tgr
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu81tabi)
 UNION ALL
       SELECT * FROM sys.exu81tgr
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu8vinfi)
/
grant select on exu81tgri to select_catalog_role
/
CREATE OR REPLACE view exu8tgri as 
       SELECT * FROM sys.exu8tgr
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu8tabi)
 UNION ALL
       SELECT * FROM sys.exu8tgr
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu8vinfi)
/
grant select on exu8tgri to select_catalog_role
/
rem triggers for cumulative export: record=no
CREATE OR REPLACE view exu816tgrc as 
       SELECT * FROM sys.exu816tgr 
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu81tabc)
 UNION ALL
       SELECT * FROM sys.exu816tgr
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu8vinfc)
/
grant select on exu816tgrc to select_catalog_role
/
CREATE OR REPLACE view exu81tgrc as 
       SELECT * FROM sys.exu81tgr 
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu81tabc)
 UNION ALL
       SELECT * FROM sys.exu81tgr
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu8vinfc)
/
grant select on exu81tgrc to select_catalog_role
/
CREATE OR REPLACE view exu8tgrc as 
       SELECT * FROM sys.exu8tgr 
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu8tabc)
 UNION ALL
       SELECT * FROM sys.exu8tgr
       WHERE (ownerid, basename) in (SELECT ownerid, name from sys.exu8vinfc)
/
grant select on exu8tgrc to select_catalog_role
/
CREATE OR REPLACE view exu8spr(ownerid, uname, id, name, time, typeid, type,
	 audt, sqlver) AS
   SELECT o.owner#, u.name, o.obj#, o.name, 
	  TO_CHAR(o.mtime, 'YYYY-MM-DD:HH24:MI:SS'), o.type#,
          DECODE(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 
	         9, 'PACKAGE', 11, 'PACKAGE BODY'), p.audit$, 
          sv.sql_version
   FROM sys.exu81obj o, sys.user$ u, sys.procedure$ p, sys.exu816sqv sv
   WHERE o.owner# = u.user#
        AND (o.type#=7 OR o.type#=8 OR o.type#=9 OR o.type#=11)
	AND o.obj# = p.obj#
        AND o.spare1 = sv.version# (+)
/
grant select on exu8spr to select_catalog_role
/
CREATE OR REPLACE view exu8spu(ownerid, uname, id, name, time, typeid, type,
	audt, sqlver) AS
   SELECT * from sys.exu8spr WHERE UID = ownerid
/
grant select on sys.exu8spu to public
/
rem stored procedures for incremental export: modified, altered or new
CREATE OR REPLACE view exu8spri AS
       SELECT s.* from exu8spr s,incexp i, incvid v
       WHERE s.name  = i.name(+) AND s.ownerid = i.owner#(+) AND
             NVL(i.type#,7) IN (7, 8, 9, 11) AND
             NVL(i.expid,9999) > v.expid
/
grant select on exu8spri to select_catalog_role
/
rem stored procedures for incremental export: modified, altered or new
CREATE OR REPLACE view exu8sprc AS
       SELECT s.* from exu8spr s,incexp i, incvid v
       WHERE s.name  = i.name(+) AND s.ownerid = i.owner#(+) AND
             NVL(i.type#,7) IN (7, 8, 9, 11) AND
(NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR
               NVL(i.expid,9999) > v.expid)
/
grant select on exu8sprc to select_catalog_role
/
CREATE OR REPLACE view exu8sps(obj#, line, source) AS
   SELECT obj#,line,source 
   FROM sys.source$ 
/
grant select on exu8sps to select_catalog_role
/
CREATE OR REPLACE view exu8spsu(obj#, line, source) AS
   SELECT o.obj#, s.line, s.source
   FROM sys.source$ s, sys.obj$ o
   WHERE s.obj# = o.obj# and o.owner# = UID
/
grant select on sys.exu8spsu to public
/

rem stored java entities
CREATE OR REPLACE view exu81jav(ownerid, uname, id, shortname, typeid, type)
	AS
   SELECT o.owner#, u.name, o.obj#, o.name, o.type#,
          DECODE(o.type#, 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 
                          30, 'JAVA RESOURCE') 
   FROM sys.exu81obj o, sys.user$ u
   WHERE o.owner# = u.user#
        AND (o.type#=28 OR o.type#=29 OR o.type#=30)
	AND (uid = 0 OR
	     uid = o.owner# OR
	     exists ( select * from session_roles
			 where role='SELECT_CATALOG_ROLE' ))
/
grant select on exu81jav to public
/

REM Get Java entities for incremental export
CREATE OR REPLACE view exu81javi AS
    SELECT j.* FROM sys.exu81jav j, sys.incexp i, sys.incvid v
    WHERE j.shortname = i.name(+) AND
          j.ownerid = i.owner#(+) AND
	  NVL(i.type#,28) IN (28, 29, 30, 31) AND
          v.expid < NVL(i.expid, 9999)
/
grant select on sys.exu81javi to select_catalog_role;

REM Get Java entities for cumulative export
CREATE OR REPLACE view exu81javc AS
    SELECT j.* FROM sys.exu81jav j, sys.incexp i, sys.incvid v
    WHERE j.shortname = i.name(+) AND
          j.ownerid = i.owner#(+) AND
	  NVL(i.type#,28) IN (28, 29, 30, 31) AND
          (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR 
          v.expid < NVL(i.expid, 9999))
/
grant select on sys.exu81javc to select_catalog_role;

rem check whether java class DbmsJava is installed
CREATE OR REPLACE view exu81javt (objid) AS
       SELECT obj#
       FROM sys.obj$
       WHERE name = 'oracle/aurora/rdbms/DbmsJava'
       AND type# = 29
       AND owner# = 0
       AND status = 1
/
grant select on exu81javt to public
/

rem system auditting options
CREATE OR REPLACE view exu8aud (userid, name, action, success, failure) AS
	SELECT a.user#, u.name, m.name, NVL(a.success,0), NVL(a.failure,0)
	FROM sys.audit$ a, sys.user$ u, sys.stmt_audit_option_map m
	WHERE a.user# = u.user# AND a.option# = m.option#
              and bitand(m.property, 1) != 1 
/
grant select on exu8aud to select_catalog_role
/
rem profiles
CREATE OR REPLACE view exu8prf(profile#, name) AS
	SELECT profile#, name 
	FROM sys.profname$
	WHERE profile# != 0
/
grant select on exu8prf to select_catalog_role
/
CREATE OR REPLACE view exu8prr(profile#, resource#, resname, type, limit) AS
	SELECT p.profile#, p.resource#, r.name, p.type#, p.limit#
	FROM sys.profile$ p, sys.resource_map r
	WHERE p.resource# != 9 and p.resource# = r.resource# and
              p.type# = r.type#
/
grant select on exu8prr to select_catalog_role
/
rem password verification function
CREATE OR REPLACE view exu8pvf (funcid, funcname, line, source) AS
        SELECT o.obj#, o.name, s.line, s.source
        FROM obj$ o, source$ s
        WHERE o.type#=8 and o.owner#=0 and o.obj#=s.obj#
/
grant select on exu8pvf to select_catalog_role
/
rem password history
CREATE OR REPLACE view exu8phs (userid, uname, password, password_date) AS
        SELECT h.user#, u.name, h.password, h.password_date
        FROM user_history$ h, user$ u
        WHERE h.user#=u.user#
/
grant select on exu8phs to select_catalog_role
/

rem trusted server links
CREATE OR REPLACE view exu8tsl (function, dbname, type) AS
	SELECT DECODE(tl.dbname,  '+*', 'allow_all', '-*', 'deny_all',
				  fdef.function),
               DECODE(tl.dbname,  '+*', '', '-*', '', '('''||tl.dbname||''')'),
               DECODE(tl.dbname,  '+*', 0, '-*', 0, 1)
	FROM
	  sys.trusted_list$ tl, 
	  (select decode (dbname, '+*','deny_server ', '-*','allow_server ')
		  function
               from sys.trusted_list$ where dbname like '%*') fdef
/
grant select on exu8tsl to select_catalog_role
/
	
rem new snapshot views for v81
CREATE OR REPLACE view exu81snap
( OWNER, OWNERID, NAME, TABLE_NAME, MASTER_VIEW, 
  MASTER_LINK, MTIME, CAN_USE_LOG, ERROR, TYPE, QUERY,
  FLAG, ROWID_SNAP, PRIMKEY_SNAP, UPDATE_SNAP, UPDATE_TRIG, UPDATE_LOG, 
  MASTABS, MASVER, LOB_VECTOR, SNAPSHOT, SNAPID, INSTSITE, FLAVOR_ID,
  FILE_VER, SQL_VER)
as
SELECT s.sowner, u.user#, s.vname, s.tname, s.mview, s.mlink, s.mtime,
       decode(s.can_use_log, null, 'NO', 'YES'), nvl(s.error#, 0), 
       decode(s.auto_fast, 
              'C', 'COMPLETE', 
              'F', 'FAST', 
              '?', 'FORCE', 
              null, 'FORCE', 
              'N', 'NEVER', 'ERROR'), 
       s.query_txt, nvl(s.flag, 0),
       /* have a flag for each snapshot types: rowid, primary key and updatable
        * for compatibility purpose
        */
       decode(bitand(nvl(s.flag, 0), 16), 16, 1, 0),
       decode(bitand(nvl(s.flag, 0), 32), 32, 1, 0),
       decode(bitand(nvl(s.flag, 0), 2), 2, 1, 0),
       s.ustrg, s.uslog, s.tables, nvl(s.master_version, 0),
       rawtohex(s.lobmaskvec), s.snapshot, nvl(s.snapid, 0),
       s.instsite, nvl(s.flavor_id, 0), 3, sv.sql_version
from sys.snap$ s, sys.user$ u, sys.exu816sqv sv
WHERE u.name = s.sowner
AND  /* s.sql_version */0 = sv.version# (+)
AND  bitand(nvl(s.flag, 0), 16) +    /* supported snapshots:          rowid */
     bitand(nvl(s.flag, 0), 32) +                            /* primary key */
     bitand(nvl(s.flag, 0), 256) +                               /* complex */
     bitand(nvl(s.flag, 0), 4096) > 0                          /* aggregate */

/
grant select on exu81snap to select_catalog_role
/
CREATE OR REPLACE view exu81snapu AS
       SELECT * from exu81snap WHERE ownerid = uid
/
grant SELECT on sys.exu81snapu to public
/

rem snapshots for incremental export: modified, altered or new
CREATE OR REPLACE view exu81snapi AS
        SELECT s.* from exu81snap s,incexp i, incvid v
       WHERE s.name  = i.name(+) AND s.ownerid = i.owner#(+) AND
             /* Since snapshot also creates a view with the same name,
              * we need to check for both type view(4) and snapshot(99). 
              * Note: there will be duplicate entry in incexp for this
              */
             NVL(i.type#,99) in (4, 99) AND
             (s.mtime > i.itime OR NVL(i.expid,9999) > v.expid)
/
grant select on exu81snapi to select_catalog_role
/
rem snapshots for cumulative export: new, last export was inc or not valid
CREATE OR REPLACE view exu81snapc AS
       SELECT s.* from exu81snap s, incexp i, incvid v
       WHERE  s.name  = i.name(+) AND s.ownerid = i.owner#(+) AND
              NVL(i.type#,99) = 99 AND
              (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR
               NVL(i.expid,9999) > v.expid)
/
grant select on exu81snapc to select_catalog_role
/
CREATE OR REPLACE view exu81srt
( SOWNER, SOWNERID, VNAME, MASTER_OWNER, MASTER, TABNUM, REFRESH_TIME,
  MASTER_FLAG, MASTER_OBJNUM, LOADERTIME, INSTSITE, LASTSUCCESS,
  FCMASKVEC, EJMASKVEC)
AS
SELECT srt.sowner, u.user#, srt.vname, srt.mowner, srt.master, srt.tablenum,
       srt.snaptime, srt.masflag, srt.masobj#, srt.loadertime, 
       srt.instsite, srt.lastsuccess,
       rawtohex(srt.fcmaskvec), rawtohex(srt.ejmaskvec)
FROM sys.snap_reftime$ srt, user$ u
WHERE u.name = srt.sowner;
/
grant select on exu81srt to select_catalog_role
/
CREATE OR REPLACE view exu81srtu AS
       SELECT * from exu81srt WHERE sownerid = uid
/
grant SELECT on sys.exu81srtu to public
/

CREATE OR REPLACE view exu81scm
( SOWNER, SOWNERID, VNAME, TABNUM, SNACOL, MASCOL, MASPOS, ROLE,
  INSTSITE, SNAPOS )
AS
SELECT sc.sowner, u.user#, sc.vname, sc.tabnum, sc.snacol, sc.mascol,
       nvl(sc.maspos, 0), nvl(sc.colrole, 0),
       instsite, sc.snapos
       FROM sys.snap_colmap$ sc, user$ u
       where u.name = sc.sowner;
/
grant select on exu81scm to select_catalog_role
/

CREATE OR REPLACE view exu81scmu AS
       SELECT * from exu81scm WHERE sownerid = uid
/
grant SELECT on sys.exu81scmu to public
/

rem snapshots
rem 
rem NOTE:
rem * In V8.1, flag was changed from UB2 to UB4. Thus when exporting
rem   to V8.0.X, the value of flag must be forced into a UB2
rem * Do not include MAVs or MJVs (KKZFJVS|KKZFAV1|KKZFAGG)
rem * Do not export to V8.0.X if the base table name is not SNAP$_*
CREATE OR REPLACE view exu8snap
( OWNER, OWNERID, NAME, TABLE_NAME, MASTER_VIEW, 
  MASTER_LINK, MTIME, CAN_USE_LOG, ERROR, TYPE, QUERY,
  FLAG, ROWID_SNAP, PRIMKEY_SNAP, UPDATE_SNAP, UPDATE_TRIG, UPDATE_LOG, 
  MASTABS, MASVER, LOB_VECTOR, SNAPSHOT, SNAPID, FILE_VER)
as
SELECT 
  owner, ownerid, name, table_name, master_view,
  master_link, mtime, can_use_log, error, type, query, MOD(flag,65536) , 
  rowid_snap, primkey_snap, update_snap, update_trig, update_log,
  mastabs, masver, lob_vector, snapshot, snapid, 2
  FROM exu81snap
 WHERE bitand(flag, 28672) = 0                /* Do not include MAVs or MJVs */
   AND instsite = 0                       /* Do not include RepAPI snapshots */
   AND table_name LIKE 'SNAP$_%'
/
grant select on exu8snap to select_catalog_role
/
CREATE OR REPLACE view exu8snapu AS
       SELECT * from exu8snap WHERE ownerid = uid
/
grant SELECT on sys.exu8snapu to public
/

rem snapshots for incremental export: modified, altered or new
CREATE OR REPLACE view exu8snapi AS
       SELECT s.* from exu8snap s,incexp i, incvid v
       WHERE s.name  = i.name(+) AND s.ownerid = i.owner#(+) AND
             /* Since snapshot also creates a view with the same name,
              * we need to check for both type view(4) and snapshot(99). 
              * Note: there will be duplicate entry in incexp for this
              */
             NVL(i.type#,99) in (4, 99) AND
             (s.mtime > i.itime OR NVL(i.expid,9999) > v.expid)
/
grant select on exu8snapi to select_catalog_role
/
rem snapshots for cumulative export: new, last export was inc or not valid
CREATE OR REPLACE view exu8snapc AS
       SELECT s.* from exu8snap s, incexp i, incvid v
       WHERE  s.name  = i.name(+) AND s.ownerid = i.owner#(+) AND
              NVL(i.type#,99) = 99 AND
              (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR
               NVL(i.expid,9999) > v.expid)
/
grant select on exu8snapc to select_catalog_role
/
rem snapshot column mapping for each master table
CREATE OR REPLACE view exu8scm
( SOWNER, SOWNERID, VNAME, TABNUM, SNACOL, MASCOL, MASPOS, ROLE )
AS
SELECT sowner, sownerid, vname, tabnum, snacol, mascol, maspos, role
     FROM exu81scm
     WHERE instsite = 0
/
grant select on exu8scm to select_catalog_role
/
CREATE OR REPLACE view exu8scmu AS
       SELECT * from exu8scm WHERE sownerid = uid
/
grant SELECT on sys.exu8scmu to public
/
rem snapshot refresh time for each master table used by snapshot
CREATE OR REPLACE view exu8srt
( SOWNER, SOWNERID, VNAME, MASTER_OWNER, MASTER, TABNUM, REFRESH_TIME)
AS
SELECT sowner, sownerid, vname, master_owner, master, tabnum, refresh_time
       FROM exu81srt
       WHERE instsite = 0                 /* Do not include RepAPI snapshots */
/
grant select on exu8srt to select_catalog_role
/
CREATE OR REPLACE view exu8srtu AS
       SELECT * from exu8srt WHERE sownerid = uid
/
grant SELECT on sys.exu8srtu to public
/
rem new snapshot log views for v8.1
CREATE OR REPLACE view exu81snapl
( LOG_OWNER, LOG_OWNERID, MASTER, LOG_TABLE, LOG_TRIGGER, FLAG,
  YOUNGEST, OLDEST, OLDEST_PK, MTIME,
  ROWID_SNAPL, PRIMKEY_SNAPL, FILE_VER, TEMP_LOG)
as
SELECT m.mowner, u.user#, m.master, m.log, m.trig, nvl(m.flag, 0),
       m.youngest, m.oldest, m.oldest_pk, m.mtime,
  /* have a flag for each snapshot log types: rowid, primary key
   * for compatibility purpose
   */
decode(bitand(nvl(m.flag, 0), 1), 1, 1, 0), 
decode(bitand(nvl(m.flag, 0), 2), 2, 1, 0), 3, m.temp_log
FROM sys.mlog$ m, sys.user$ u
WHERE m.mowner = u.name
/
grant select on exu81snapl to select_catalog_role
/
CREATE OR REPLACE view exu81snaplu AS
SELECT * FROM exu81snapl WHERE log_ownerid = uid
/
grant SELECT on sys.exu81snaplu to public;
/
rem snapshot logs for incremental export: modified, altered or new
CREATE OR REPLACE view exu81snapli AS
        SELECT s.* from exu81snapl s,incexp i, incvid v
       WHERE s.master  = i.name(+) AND s.log_ownerid = i.owner#(+) AND
             /* snapshot log also creates a table with the same name */
             NVL(i.type#,98) in (2, 98) AND
             (s.mtime > i.itime OR NVL(i.expid,9999) > v.expid)
/
grant select on exu81snapli to select_catalog_role
/
rem snapshot logs for cumulative export: new, last export was inc or not valid
CREATE OR REPLACE view exu81snaplc AS
       SELECT s.* from exu81snapl s, incexp i, incvid v
       WHERE s.master  = i.name(+) AND s.log_ownerid = i.owner#(+) AND
              NVL(i.type#,98) = 98 AND
              (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR
               NVL(i.expid,9999) > v.expid)
/
grant select on exu81snaplc to select_catalog_role
/
CREATE OR REPLACE view exu81slfc 
   (mowner, mownerid, master, colname, oldest, flag) AS
       SELECT mr.mowner, u.user#, mr.master, mr.colname, mr.oldest, 
              nvl(mr.flag, 0)
       FROM sys.mlog_refcol$ mr, user$ u
       WHERE u.name = mr.mowner
/
grant select on exu81slfc to select_catalog_role
/
CREATE OR REPLACE view exu81slfcu AS
       SELECT * from exu81slfc WHERE mownerid = uid
/
grant SELECT on sys.exu81slfcu to public
/
rem snapshot log views for v8.0
CREATE OR REPLACE view exu8snapl
( LOG_OWNER, LOG_OWNERID, MASTER, LOG_TABLE, LOG_TRIGGER, FLAG,
  YOUNGEST, OLDEST, OLDEST_PK, MTIME,
  ROWID_SNAPL, PRIMKEY_SNAPL, FILE_VER)
as
SELECT log_owner, log_ownerid, master, log_table, log_trigger, 
       /* Clear the bit (0x0040) indicating that a temporary log was created */
       decode(bitand(flag, 64), 64, flag-64, flag), 
       youngest, oldest, oldest_pk, mtime, rowid_snapl, primkey_snapl, 2
FROM exu81snapl
/
grant select on exu8snapl to select_catalog_role
/
CREATE OR REPLACE view exu8snaplu
as
SELECT * from exu8snapl WHERE log_ownerid = uid
/
grant SELECT on sys.exu8snaplu to public;

rem snapshot logs for incremental export: modified, altered or new
CREATE OR REPLACE view exu8snapli AS
        SELECT s.* from exu8snapl s,incexp i, incvid v
       WHERE s.master  = i.name(+) AND s.log_ownerid = i.owner#(+) AND
             /* snapshot log also creates a table with the same name */
             NVL(i.type#,98) in (2, 98) AND
             (s.mtime > i.itime OR NVL(i.expid,9999) > v.expid)
/
grant select on exu8snapli to select_catalog_role
/
rem snapshot logs for cumulative export: new, last export was inc or not valid
CREATE OR REPLACE view exu8snaplc AS
       SELECT s.* from exu8snapl s, incexp i, incvid v
       WHERE s.master  = i.name(+) AND s.log_ownerid = i.owner#(+) AND
              NVL(i.type#,98) = 98 AND
              (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR
               NVL(i.expid,9999) > v.expid)
/
grant select on exu8snaplc to select_catalog_role
/
rem info on deleted snapshots -- they aren't in obj$
CREATE OR REPLACE view exu8delsnap (owner, name, type) as
       SELECT u$.name, i$.name, 'SNAPSHOT'
       from sys.incexp i$, sys.user$ u$
       WHERE i$.owner# = u$.user# and
	     i$.type# = 99 and
             (u$.name, i$.name)
             NOT IN (SELECT s$.sowner, s$.vname
                        from sys.snap$ s$
                        where s$.instsite = 0)
/
grant select on exu8delsnap to select_catalog_role
/
rem info on deleted snapshot logs -- they aren't in obj$
CREATE OR REPLACE view exu8delsnapl (owner, name, type) as
       SELECT u$.name, i$.name, 'SNAPSHOT LOG'
       from sys.incexp i$, sys.user$ u$
       WHERE i$.owner# = u$.user# and
	     i$.type# = 98 and
             (u$.name, i$.name)
             NOT IN (SELECT m$.mowner, m$.master
 			from sys.mlog$ m$)
/
grant select on exu8delsnapl to select_catalog_role
/
rem 
CREATE OR REPLACE view exu8slog 
   (mowner, mownerid, master, snapid, snaptime) AS
       SELECT sl.mowner, u.user#, sl.master, nvl(sl.snapid, 0), sl.snaptime
       FROM sys.slog$ sl, user$ u
       where u.name = sl.mowner
/
grant select on exu8slog to select_catalog_role
/
CREATE OR REPLACE view exu8slogu AS
       SELECT * from exu8slog WHERE mownerid = uid
/
grant SELECT on sys.exu8slogu to public
/
CREATE OR REPLACE view exu8slfc 
   (mowner, mownerid, master, colname, oldest, flag) AS
       SELECT mowner, mownerid, master, colname, oldest, 0 
       FROM exu81slfc e81
       /* in v8.0 primary keys are not logged as filter columns */
       WHERE bitand(e81.flag, 2) != 2
/
grant select on exu8slfc to select_catalog_role
/
CREATE OR REPLACE view exu8slfcu AS
       SELECT * from exu8slfc WHERE mownerid = uid
/
grant SELECT on sys.exu8slfcu to public
/
CREATE OR REPLACE view exu8glob (global_name) AS
       SELECT value$ from sys.props$ WHERE name = 'GLOBAL_DB_NAME'
/
grant select on exu8glob to public
/
rem info on analyzed objects (obsolete in 8.1)
CREATE OR REPLACE view exu8anal(id,rowcnt) as
       select obj#, SIGN(NVL(rowcnt, -1)) from sys.tab$;
grant select on exu8anal to public
/
grant select on exu8anal to select_catalog_role
/

rem  Indexes for which optimizer statistics cannot be easily imported
CREATE OR REPLACE VIEW exu81nos (tobjid,towner) AS
        /* Indexes for table must be exportable */
        SELECT  t$.obj#, t$.owner#
        FROM    sys.obj$ t$, sys.obj$ i$, sys.ind$ ind$
        WHERE   ind$.bo# = t$.obj# AND ind$.obj# = i$.obj# AND
	        ind$.blevel != -1 AND
		(bitand(i$.flags, 4)=1 OR	/* System generated name */
		 ind$.type# = 8 OR		/* LOB index */
		 (bitand(ind$.property, 1) = 1  /* Unique index */
		  AND EXISTS (SELECT * from sys.con$ c$, sys.cdef$ cd$
		     WHERE c$.owner# = i$.owner#
                     AND   c$.name = i$.name
                     AND   c$.con# = cd$.con#         /* bug #686272 */
                     AND   NVL(cd$.enabled, 0) = ind$.obj#))) AND
                (UID IN (t$.owner#, 0) or 
                 exists ( select * from session_roles 
                          where role='SELECT_CATALOG_ROLE' ))
UNION ALL  /* Table cannot have associations */
        SELECT  t$.obj#, t$.owner#
        FROM    sys.obj$ t$, sys.association$ a$
        WHERE   t$.obj# = a$.obj# AND
                t$.type# = 2 AND                         /* Table */
                (UID IN (t$.owner#, 0) or 
                 exists ( select * from session_roles 
                          where role='SELECT_CATALOG_ROLE' ))
UNION ALL /* Type used in table cannot have associations */
        SELECT  t$.obj#, t$.owner#
        FROM    sys.obj$ t$, sys.obj$ tt$, sys.coltype$ ct$, 
                sys.association$ a$
        WHERE   t$.obj# = ct$.obj# AND
		ct$.toid = tt$.oid$ AND
                tt$.obj# = a$.obj# AND
                (UID IN (t$.owner#, 0) or 
                 exists ( select * from session_roles 
                          where role='SELECT_CATALOG_ROLE' ))
/
grant select on exu81nos to public
/

rem
rem Associations
rem
CREATE OR REPLACE view exu81assoc 
    (objowner, objowner#, objtype, objname, objcol, statsschema, statsname,
     selectivity, cpu_cost, io_cost, net_cost) AS
    SELECT ou$.name, oo$.owner#, a$.property,
           oo$.name, NVL(c$.name, ''), su$.name, NVL(so$.name, ''),
           NVL(a$.default_selectivity, 0), NVL(a$.default_cpu_cost,0),
           NVL(a$.default_io_cost, 0), NVL(a$.default_net_cost, 0)
    FROM sys.association$ a$, sys.exu81obj oo$, sys.user$ ou$,
	 sys.col$ c$, sys.obj$ so$, sys.user$ su$
    WHERE 
          a$.obj# = oo$.obj# and
          oo$.owner# = ou$.user# and
          a$.intcol# = c$.intcol# (+) and
          a$.obj# = c$.obj# (+) and
          a$.statstype# = so$.obj# (+) and
          so$.owner# = su$.user# (+) and 
          (uid = 0 or
           uid = oo$.owner# or
           exists (select * from session_roles 
                            where role='SELECT_CATALOG_ROLE'))
/

grant select on exu81assoc to public;

rem add a view to determine storage clause for unique constraint
rem need for it to be user level because two different users can have the
rem same index name 
rem Fields ipctfree, initr, imaxtr, and ipctthres are only used
rem for iots, not for regular tables.
CREATE OR REPLACE view exu8uscu 
 (iobjid, idobjid, iname, itsno, ifileno, iblockno, ibobjid, ipctfree, iinitr,
  imaxtr, ipctthres, tspname, property, preccnt, deflog, ipbobjid) as
  SELECT o$.obj#, o$.dataobj#, o$.name, i$.ts#, i$.file#, i$.block#, i$.bo#,
         i$.pctfree$, i$.initrans, i$.maxtrans, mod(i$.pctthres$,256), t$.name,
         i$.property, i$.spare2, decode(bitand(i$.flags,4),4,1,0), 0
  FROM   sys.obj$ o$, sys.ind$ i$, sys.file$ f$, sys.ts$ t$
  WHERE  o$.obj# = i$.obj# and f$.relfile#=i$.file# and f$.ts#=i$.ts#
         and f$.ts# = t$.ts#
union all
  SELECT o$.obj#, o$.dataobj#, o$.name, ip$.ts#, ip$.file#, ip$.block#,
         ind$.bo#, ip$.pctfree$, ip$.initrans, ip$.maxtrans,
         mod(ip$.pctthres$,256), ts$.name, ind$.property, ip$.spare2, 
         decode(bitand(ind$.flags,4),4,1,0), ip$.bo#
  FROM   sys.obj$ o$, sys.indpart$ ip$, sys.ts$ ts$, sys.ind$ ind$, sys.tab$ t$
  WHERE  ip$.obj# = o$.obj# and ts$.ts# = ip$.ts# and ip$.bo# = ind$.obj# and
         o$.type# = 20 and /* Index partitions */
         ind$.bo# = t$.obj# and bitand(t$.property, 64) = 0 /* Non-IOTs */
/
grant select on sys.exu8uscu to public; 

rem For partitioned iots only: Get top level index's characteristics
CREATE OR REPLACE view exu81usci
 (ipctthres, ipreccnt, iobjid, tobjid, ovfobjid, ownerid) AS
  SELECT mod(i$.pctthres$,256), NVL(i$.spare2,0), i$.obj#, 
  i$.bo#, NVL(t$.bobj#,0), o$.owner#
  FROM sys.ind$ i$, sys.obj$ o$, sys.tab$ t$
  WHERE i$.bo# = t$.obj# and t$.obj# = o$.obj# and i$.type# = 4
/
grant select on exu81usci to select_catalog_role
/
CREATE OR REPLACE view exu81usciu AS
  SELECT * from exu81usci WHERE ownerid = uid
/
grant select on exu81usciu to public;

rem referential constraints
CREATE OR REPLACE view exu8rif 
                   (objid, owner, ownerid, tname, rowner, rtname, cname, cno,
	            rcno, action, enabled, robjid, defer, property) AS
       SELECT o.obj#, u.name, c.owner#, o.name, ru.name, ro.name, c.name,
	      c.con#, cd.rcon#, NVL(cd.refact,0), NVL(cd.enabled,0),
	      cd.robj#, nvl(cd.defer, 0), t.property
       FROM sys.user$ u, sys.user$ ru, sys.obj$ o, sys.obj$ ro, 
            sys.con$ c, sys.cdef$ cd, sys.tab$ t
       WHERE u.user# = c.owner# AND o.obj# = cd.obj# AND ro.obj# = cd.robj# AND
             cd.con# = c.con# AND cd.type# = 4 AND ru.user# = ro.owner#
             AND t.obj# = o.obj#
/
grant select on exu8rif to select_catalog_role
/
CREATE OR REPLACE view exu8erc
		(resource_name, unit_cost) as
		SELECT m.name, c.cost 
		FROM sys.resource_cost$ c, sys.resource_map m 
		WHERE c.resource# = m.resource# and
			m.type# = 0 AND c.resource# in (2, 4, 7, 8);
/
grant select on exu8erc to select_catalog_role
/

REM
REM determine OID index for a table
REM
CREATE OR REPLACE view exu8oid
               (tobjid,intcol,iname,idefer,ownerid,
                pctfree$,initrans,maxtrans,itsno,ifile,iblock,itsname) AS
       SELECT cc$.obj#,cc$.intcol#,co$.name,cd$.defer,co$.owner#,
              i$.pctfree$,i$.initrans,i$.maxtrans,i$.ts#,i$.file#,i$.block#,
              ts$.name
       FROM   sys.ccol$ cc$, sys.con$ co$, sys.cdef$ cd$, sys.obj$ o$,
              sys.ind$ i$, sys.ts$ ts$  
       WHERE  cc$.con# = co$.con# AND
              cc$.con# = cd$.con# AND
	      cd$.type# = 3 AND
              co$.owner# = o$.owner# AND 
              co$.name = o$.name AND
              o$.obj# = i$.obj# AND
              i$.ts# = ts$.ts#
/
grant select on exu8oid to select_catalog_role
/
CREATE OR REPLACE view exu8oidu AS
       SELECT * from exu8oid WHERE ownerid = UID
/
grant select on exu8oidu to public
/

REM
REM determine LOB index and LOB storage for a table
CREATE OR REPLACE VIEW exu8lob
               (tobjid,ownerid,cname,
                sname, ssgflag, stsname, stsno, sfile, sblock,
                sdobjid, schunking, 
                svpool,sflags,
                iname, isgflag, itsname, itsno, ifile, iblock, idobjid,
                iinitrans, imaxtrans,
                sproperty, coltype, coltypflg) AS
       SELECT l$.obj#,so$.owner#,
	      DECODE(bitand(c$.property,1), 0, '"'||c$.name||'"', 1, ac$.name),
              so$.name,so$.flags,sts$.name,sts$.ts#,l$.file#,l$.block#,
              so$.dataobj#,
              l$.chunk,l$.pctversion$,l$.flags,
              io$.name,io$.flags,its$.name,its$.ts#,i$.file#,i$.block#,
              io$.dataobj#,
              i$.initrans,i$.maxtrans, l$.property, 
              NVL(c$.type#, 0), NVL(ct$.flags, 0)
       FROM   sys.lob$ l$, sys.obj$ so$, sys.col$ c$, sys.attrcol$ ac$,
              sys.seg$ ss$, sys.ts$ sts$, sys.ind$ i$, sys.obj$ io$,
              sys.ts$ its$, sys.coltype$ ct$
       WHERE  l$.lobj# = so$.obj# AND
              l$.obj# = c$.obj# AND
	      l$.intcol# = c$.intcol# AND
              l$.obj# = ac$.obj#(+) AND
	      l$.intcol# = ac$.intcol#(+) AND
	      l$.file# = ss$.file# AND
              l$.block# = ss$.block# AND
	      ss$.ts# = sts$.ts# AND
              l$.ind# = i$.obj# AND
	      l$.ind# = io$.obj# AND
              i$.ts# = its$.ts# AND
	      c$.obj# = ct$.obj# (+) AND
	      c$.intcol# = ct$.intcol# (+) AND
              bitand(c$.property,32768) != 32768     /* not unused column */
/
grant select on exu8lob to select_catalog_role
/
rem lob indexes for current user
CREATE OR REPLACE view exu8lobu AS
       SELECT * from exu8lob WHERE ownerid = UID
/
grant select on exu8lobu to public
/
rem table level attributes definition for LOB columns
rem in partitioned tables
CREATE OR REPLACE view exu81plb
           (tobjid, ownerid, cname, lobname, tsname,
           sgflags, property, chunk, versionp, flags,
           iniexts, extsize, minexts, maxexts, extpct,
           flists, freegrp, pcache, coltype, coltypflg) AS
    SELECT o.obj#, o.owner#, c.name, lo.name, ts.name,
           lo.flags, plob.defpro, plob.defchunk, plob.defpctver$,plob.defflags,
           NVL(plob.definiexts, 0), NVL(plob.defextsize, 0),
           NVL(plob.defminexts, 0), NVL(plob.defmaxexts, 0),
           NVL(plob.defextpct, -1),
           NVL(plob.deflists, 0), NVL(plob.defgroups, 0),
           decode(plob.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE',NULL),
           NVL(c.type#, 0), NVL(ct.flags, 0)
     FROM  sys.partlob$ plob, sys.obj$ o, sys.lob$ l,
           sys.col$ c, sys.coltype$ ct, sys.obj$ lo, ts$ ts
     WHERE o.obj# = c.obj# and
           c.obj#  = l.obj# and
           c.intcol# = l.intcol# and
           l.lobj# = lo.obj# and
           l.lobj# = plob.lobj#  and
           plob.defts# = ts.ts# (+) and
           c.obj# = ct.obj# (+) and
           c.intcol# = ct.intcol# (+) and
           bitand(c.property,32768) != 32768
/
grant select on exu81plb to select_catalog_role
/
rem table level default attributes for current
rem user's LOB columns in partitioned tables
CREATE OR REPLACE view exu81plbu AS
       SELECT * from exu81plb WHERE ownerid = uid
/
grant select on exu81plbu to public
/
REM
REM determine attributes of noncomposite partitions of LOB columns
REM
CREATE OR REPLACE VIEW exu81lbp
             (pobjid,  ownerid, cname, lobpname, tsname, sgflags, chunk,
              versionp, flags, property, tsno, fileno, blockno, dobjid,
              iname, isgflag, itsname, itsno, ifile, iblock, idobjid, 
              iinitrans, imaxtrans, coltype, coltypflg) AS
       SELECT po.obj#, o.owner#, c.name, lpo.subname, ts.name, lpo.flags,
              lf.chunk, lf.pctversion$, lf.fragflags, lf.fragpro,
              lf.ts#, lf.file#, lf.block#, lpo.dataobj#, lipo.name,
              lipo.flags, its.name, its.ts#, ip.file#, ip.block#, lipo.dataobj#,
              ip.initrans ,ip.maxtrans, NVL(c.type#, 0), NVL(ct.flags, 0)
       FROM   sys.obj$ o, sys.col$ c, sys.obj$ lpo, sys.lob$ l,
              sys.partobj$ pobj, sys.ts$ ts, sys.obj$ lipo,
              sys.lobfrag$ lf, sys.indpart$ ip, sys.obj$ po,
              sys.ts$ its, sys.coltype$ ct
       WHERE  pobj.obj# = o.obj# AND
              mod(pobj.spare2, 256) = 0 AND
              o.obj# = c.obj# AND
              c.obj# = l.obj# AND
              c.intcol# = l.intcol# AND
              l.lobj# = lf.parentobj# AND
              lf.fragobj# = lpo.obj# AND
              lf.tabfragobj# = po.obj# AND
              lf.indfragobj# = lipo.obj# AND
              lf.indfragobj# = ip.obj# AND
              ip.ts# = its.ts# AND
              lf.ts# = ts.ts# AND
              c.obj# = ct.obj# (+) AND
              c.intcol# = ct.intcol# (+) AND
              bitand(c.property,32768) != 32768
/
grant select on exu81lbp to select_catalog_role
/
CREATE OR REPLACE view exu81lbpu AS
       SELECT * from exu81lbp WHERE ownerid = UID
/
grant select on exu81lbpu to public
/
 
REM
REM determine LOB attributes for a composite partition
REM
CREATE OR REPLACE VIEW exu81lbcp
             (pobjid, ownerid, cname, lobcpname,
              tsname, sgflags, chunk, versionp,
              flags, property, iniexts, extsize,
              minexts, maxexts, extpct, flists,
              fgroups, pcache, coltype, coltypflg) AS 
       SELECT po.obj#, o.owner#, c.name, lpo.subname,
              ts.name, lpo.flags, lcp.defchunk,lcp.defpctver$,
              lcp.defflags, lcp.defpro,
              NVL(lcp.definiexts, 0), NVL(lcp.defextsize, 0),
              NVL(lcp.defminexts, 0), NVL(lcp.defmaxexts, 0),
              NVL(lcp.defextpct, -1),
              NVL(lcp.deflists, 0),
              NVL(lcp.defgroups, 0),
              decode(lcp.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
              NVL(c.type#, 0), NVL(ct.flags, 0)
       FROM   sys.obj$ o, sys.lob$ l, sys.col$ c,
              sys.obj$ lo, sys.obj$ lpo,
              sys.lobcomppart$ lcp, sys.obj$ po,
              sys.ts$ ts,  sys.partobj$ pobj,
              sys.coltype$ ct
       WHERE  pobj.obj# = o.obj# AND
              mod(pobj.spare2, 256) != 0 AND
              o.obj# = c.obj# AND
              c.obj# = l.obj# AND
              l.intcol# = c.intcol# AND
              l.lobj# = lo.obj# AND
              l.lobj# = lcp.lobj# AND
              lcp.tabpartobj# = po.obj# AND
              lcp.partobj# = lpo.obj# AND
              lcp.defts# = ts.ts# (+) AND
              c.obj# = ct.obj# (+) AND
              c.intcol# = ct.intcol# (+) AND
              bitand(c.property,32768) != 32768
/
grant select on exu81lbcp to select_catalog_role
/
CREATE OR REPLACE view exu81lbcpu AS
       SELECT * from exu81lbcp WHERE ownerid = UID
/
grant select on exu81lbcpu to public
/
REM
REM determine LOB storage for subpartition of LOB columns
REM
CREATE OR REPLACE VIEW exu81lbsp
             (pobjid, ownerid, cname, lobspname, tsname, sgflags,
              tsno, fileno, blockno, dobjid,
              iname, isgflag, itsname, itsno, ifile, iblock, idobjid,
              iinitrans, imaxtrans, coltype, coltypflg) AS
       SELECT spo.obj#, o.owner#, c.name,
              lspo.subname, ts.name, lspo.flags, 
              ts.ts#, lf.file#, lf.block#, lspo.dataobj#,
              lispo.name, lispo.flags, its.name, its.ts#,
              isp.file#, isp.block#, lispo.dataobj#, isp.initrans, isp.maxtrans,
              NVL(c.type#, 0), NVL(ct.flags, 0)
       FROM   sys.partobj$ pobj, sys.obj$ o, sys.col$ c,
              sys.lob$ l, sys.coltype$ ct,
              sys.lobcomppart$ lcp, sys.obj$ spo, 
              sys.lobfrag$ lf, sys.obj$ lspo,
              sys.obj$ lispo, sys.indsubpart$ isp, sys.ts$ its,
              sys.ts$ ts  
       WHERE  pobj.obj# = o.obj# AND
              mod(pobj.spare2, 256) !=0 AND
              o.obj# = c.obj# AND
              c.obj# = l.obj# AND
              c.intcol# = l.intcol# AND
              l.lobj# = lcp.lobj# AND
              lf.parentobj# = lcp.partobj# AND
              lf.fragobj# = lspo.obj# AND
              lf.tabfragobj# = spo.obj# AND
              lf.indfragobj# = lispo.obj# AND
              lf.indfragobj# = isp.obj# AND
              isp.ts# = its.ts# AND
              lf.ts# = ts.ts# AND
              c.obj# = ct.obj# (+) AND
              c.intcol# = ct.intcol# (+) AND
              bitand(c.property,32768) != 32768  
/
grant select on exu81lbsp to select_catalog_role
/
CREATE OR REPLACE view exu81lbspu AS
       SELECT * from exu81lbsp WHERE ownerid = UID
/
grant select on exu81lbspu to public
/

REM
REM Job Queues
REM
CREATE OR REPLACE view exu8jbq
	        (job, ownerid, owner) as
		SELECT j$.job, u$.user#, j$.powner
		FROM sys.job$ j$, sys.user$ u$
		WHERE j$.powner = u$.name
/
grant select on exu8jbq to select_catalog_role
/
CREATE OR REPLACE view exu8jbqu 
		(job, ownerid, owner) as
		SELECT * FROM sys.exu8jbq 
		WHERE uid = ownerid
/
grant select on exu8jbqu to public
/
REM
REM Refresh Groups
REM
CREATE OR REPLACE view exu81rgs 
		(refgroup, ownerid, owner, instsite) as
		SELECT nvl(r$.refgroup, 0), u$.user#, r$.owner, r$.instsite
		FROM sys.rgroup$ r$, sys.user$ u$
		WHERE r$.owner = u$.name
/
grant select on exu81rgs to select_catalog_role
/
CREATE OR REPLACE view exu81rgsu as
		SELECT * from sys.exu81rgs
		WHERE uid = ownerid
/
grant select on exu81rgsu to public
/

CREATE OR REPLACE view exu8rgs
                (refgroup, ownerid, owner) as
                SELECT refgroup, ownerid, owner
                FROM exu81rgs
                WHERE instsite = 0        /* Do not include RepAPI refgroups */
/
grant select on exu8rgs to select_catalog_role
/
CREATE OR REPLACE view exu8rgsu as
                SELECT * from sys.exu8rgs
                WHERE uid = ownerid
/
grant select on exu8rgsu to public
/
REM
REM Refresh Group Children
REM
CREATE OR REPLACE view exu81rgc
		(owner, ownerid, child, type, refgroup, instsite) as
		SELECT rc$.owner, u$.user#, rc$.name, rc$.type#, 
                       nvl(rc$.refgroup, 0), rc$.instsite
		FROM sys.rgchild$ rc$, sys.user$ u$
		WHERE rc$.owner = u$.name
/
grant select on exu81rgc to select_catalog_role
/

CREATE OR REPLACE view exu81rgcu as
                SELECT * from sys.exu81rgc
                WHERE uid = ownerid
/
grant select on exu81rgcu to public
/

CREATE OR REPLACE view exu8rgc
                (owner, ownerid, child, type, refgroup) as
                SELECT owner, ownerid, child, type, refgroup
                FROM exu81rgc
                WHERE instsite = 0        /* Do not include RepAPI snapshots */
/
grant select on exu8rgc to select_catalog_role
/

CREATE OR REPLACE view exu8rgcu as
		SELECT * from sys.exu8rgc
		WHERE uid = ownerid
/
grant select on exu8rgcu to public
/
REM
REM PoSTtables actions 
REM
/
CREATE OR REPLACE view exu8pst
		(owner, ownerid, tname, tobjid, callorder,
		 callarg, objtype, usrarg, property) as
		SELECT a$.owner, u$.user#, a$.name, o$.obj#, a$.callorder,
			a$.callarg, a$.obj_type, a$.user_arg,
                        t$.property
		FROM sys.expact$ a$, sys.user$ u$, sys.obj$ o$, sys.tab$ t$
		WHERE u$.name = a$.owner and o$.owner# = u$.user#
		      and o$.name = a$.name and t$.obj# = o$.obj# 
/
grant select on exu8pst to select_catalog_role
/
CREATE OR REPLACE view exu8pstu
		(owner, ownerid, tname, tobjid, callorder, callarg,
		 objtype, usrarg, property ) as
		SELECT * from sys.exu8pst
	        where ownerid = uid
/
grant select on exu8pstu to public
/

REM PoSTtables actions incremental/cumulative with record = Y
CREATE OR REPLACE view exu8pstic
		(owner, ownerid, tname, tobjid, callorder, callarg,
		 objtype, usrarg, property ) as
		SELECT * from sys.exu8pst
		WHERE (ownerid, tname) in
	        (SELECT i.owner#, i.name
                 FROM sys.incexp i, sys.incvid v
                 WHERE i.expid > v.expid AND i.type# = 2)
/
grant select on exu8pstic to select_catalog_role
/
REM PoSTtables actions for incremental export : record = N
CREATE OR REPLACE view exu81psti as
		SELECT * from sys.exu8pst
		WHERE (ownerid, tname) in 
		(SELECT ownerid, name from sys.exu81tabi)
/
grant select on exu81psti to select_catalog_role
/
REM PoSTtables actions for cumulative  export : record = N
CREATE OR REPLACE view exu81pstc as
		SELECT * from sys.exu8pst
		WHERE (ownerid, tname) in 
		(SELECT ownerid, name from sys.exu81tabc)
/
grant select on exu81pstc to select_catalog_role
/
REM 8.0 PoSTtables actions for incremental export : record = N
CREATE OR REPLACE view exu8psti as
		SELECT * from sys.exu8pst
		WHERE (ownerid, tname) in 
		(SELECT ownerid, name from sys.exu8tabi)
/
grant select on exu8psti to select_catalog_role
/
REM 8.0 PoSTtables actions for cumulative  export : record = N
CREATE OR REPLACE view exu8pstc as
		SELECT * from sys.exu8pst
		WHERE (ownerid, tname) in 
		(SELECT ownerid, name from sys.exu8tabc)
/
grant select on exu8pstc to select_catalog_role
/
REM Version Control
CREATE OR REPLACE view exu8ver (version) AS
	        SELECT TO_NUMBER(value$) from sys.props$
		WHERE name = 'EXPORT_VIEWS_VERSION'
/
grant select on exu8ver to public
/

REM Check for Procedural and Replication Options (Obsolete in 8.0.4)
CREATE OR REPLACE view exu8cpo (parameter, value) AS
		SELECT parameter, DECODE(value, 'TRUE', 1, 'FALSE', 0, 2) 
		FROM v$option
		WHERE parameter in ('procedural', 'replication')
/
grant select on exu8cpo to public
/

REM Check for non-exportable objects for all users
CREATE OR REPLACE view exu8nxp (ownerid, owner, name, type) AS
		SELECT u$.user#, n$.owner, n$.name, n$.obj_type
		FROM sys.noexp$ n$, sys.user$ u$
		WHERE n$.owner = u$.name
/
grant select on exu8nxp to select_catalog_role
/
REM Check for non-exportable objects for current user
CREATE OR REPLACE view exu8nxpu (owner, name, type) AS
		SELECT owner, name, type
		FROM sys.exu8nxp
		WHERE ownerid = UID
/
grant select on exu8nxpu to public
/

rem obtain types of top level columns of a table
CREATE OR REPLACE view exu8typt
   (tname, towner, ownerid, toid, mtime,typobjno,tabobjno,
    audit$, sqlver  ) AS
       SELECT o.name, u.name, o.owner#, t.toid,
              TO_CHAR(o.mtime,  'YYYY-MM-DD:HH24:MI:SS'),
              o.obj#, c.obj#, tm.audit$, sv.sql_version
       FROM   sys.coltype$ c, sys.user$ u, sys.obj$ o, sys.type$ t,
              sys.type_misc$ tm, sys.exu816sqv sv
       WHERE  t.toid  = c.toid
         AND  o.oid$  = c.toid
         AND  u.user# = o.owner#
         AND  o.obj#  = tm.obj#
         AND  bitand( t.properties, 2128 ) = 0 /* skip system gen'd types*/
         AND  NVL(o.type#,-1) !=10
         AND  o.spare1 = sv.version# (+)
/
grant select on sys.exu8typt to select_catalog_role;

CREATE OR REPLACE view exu8typtu
   (tname, towner, ownerid, toid, mtime,typobjno,tabobjno, 
    audit$, sqlver ) AS
       SELECT o.name, u.name, o.owner#, t.toid,
              TO_CHAR(o.mtime,  'YYYY-MM-DD:HH24:MI:SS'),
              o.obj#, c.obj#, tm.audit$, sv.sql_version
       FROM   sys.coltype$ c, sys.user$ u, sys.obj$ o, sys.type$ t,
              sys.type_misc$ tm, sys.exu816sqv sv
       WHERE  t.toid  = c.toid
         AND  o.oid$  = c.toid
         AND  u.user# = o.owner#
         AND  o.obj#  = tm.obj#
         AND  bitand( t.properties, 2128) = 0 /* skip system gen'd types*/
         AND  NVL(o.type#,-1) !=10
         AND ( o.owner# = UID    /* owned by current user */
               OR
               /* current user or public role have execute access to type */
               o.obj# in (select oa.obj# from sys.objauth$ oa
                          where ( oa.obj# = o.obj#
                                 AND oa.privilege# = 12 /* execute */
                                 AND (oa.grantee# = UID or oa.grantee# = 1)))
               OR 
               /* current user or public role can execute any type */
               exists (select null from sys.sysauth$ sa
                       where (sa.grantee# = UID or sa.grantee# = 1)
                              AND sa.privilege# = -184 )
             )
         AND o.spare1 = sv.version# (+)
/
grant select on exu8typtu to public;

rem obtain parent types, given a child type
CREATE OR REPLACE VIEW exu8fpt
    (tname, towner, ownerid, toid, mtime, objno, dobjno,
     audit$, sqlver ) AS
    SELECT o.name, u.name, o.owner#, t.toid,
           TO_CHAR(o.mtime,  'YYYY-MM-DD:HH24:MI:SS'), 
           o.obj#, d.d_obj#, tm.audit$, sv.sql_version
    FROM sys.obj$ o, sys.user$ u, sys.type$ t, sys.dependency$ d,
         sys.type_misc$ tm, sys.exu816sqv sv
    WHERE  o.obj# = d.p_obj#
      AND  o.type# = 13
      AND  o.oid$ = t.toid
      AND  o.owner# = u.user#
      AND  o.obj# = tm.obj#
      AND  bitand( t.properties, 2128) = 0 /* skip system gen'd types*/
      AND  o.spare1 = sv.version# (+)
/
grant select on sys.exu8fpt to select_catalog_role;

CREATE OR REPLACE VIEW exu8fptu
    (tname, towner, ownerid, toid, mtime, objno, dobjno,
     audit$, sqlver ) AS
    SELECT o.name, u.name, o.owner#, t.toid,
           TO_CHAR(o.mtime,  'YYYY-MM-DD:HH24:MI:SS'), 
           o.obj#, d.d_obj#, tm.audit$, sv.sql_version
    FROM sys.obj$ o, sys.user$ u, sys.type$ t, sys.dependency$ d,
         sys.type_misc$ tm, sys.exu816sqv sv
    WHERE  o.obj# = d.p_obj#
      AND  o.type# = 13
      AND  o.oid$ = t.toid
      AND  o.owner# = u.user#
      AND  o.obj# = tm.obj#
      AND  bitand( t.properties, 2128) = 0 /* skip system gen'd types*/
      AND ( o.owner# = UID    /* owned by current user */
            OR
            /* current user or public role have execute access to type */
            o.obj# in (select oa.obj# from sys.objauth$ oa
                       where ( oa.obj# = o.obj#
                              AND oa.privilege# = 12 /* execute */
                              AND (oa.grantee# = UID or oa.grantee# = 1)))
            OR 
            /* current user or public role can execute any type */
            exists (select null from sys.sysauth$ sa
                    where (sa.grantee# = UID or sa.grantee# = 1)
                           AND sa.privilege# = -184 )
          )
      AND  o.spare1 = sv.version# (+)
/
grant select on sys.exu8fptu to public;


rem obtain type body object number and audit, from type name and schema name
CREATE OR REPLACE view exu8typb
    (tname, towner, townerid, objno, sqlver ) AS
    SELECT o.name, u.name, o.owner#, o.obj#, sv.sql_version
    FROM sys.obj$ o, sys.user$ u, sys.exu816sqv sv
    WHERE o.type# = 14
      AND u.user# = o.owner# 
      AND o.spare1 = sv.version# (+)
/
grant select on sys.exu8typb to select_catalog_role;

CREATE OR REPLACE view exu8typbu AS
    SELECT * 
    FROM exu8typb 
    WHERE townerid = UID
/
grant select on sys.exu8typbu to public;

rem obtain info on all types
CREATE OR REPLACE view exu81typ
    (tname, towner, ownerid, toid, mtime, objno, audit$,
     secondaryobj, sqlver) AS
    SELECT o.name, u.name, o.owner#, t.toid, 
           TO_CHAR(o.mtime, 'YYYY-MM-DD:HH24:MI:SS'), 
           o.obj#, tm.audit$,
           decode(bitand(o.flags,16),16,1,0), sv.sql_version
    FROM sys.obj$ o, sys.user$ u, sys.type$ t, sys.type_misc$ tm,
         sys.exu816sqv sv
    WHERE  o.type# = 13
      AND  o.oid$ = t.toid
      AND  u.user# = o.owner#
      AND  tm.obj# = o.obj#
      AND  bitand( t.properties, 2128) = 0 /* skip system gen'd types*/
      AND  u.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS', 'LBACSYS')
      AND  o.spare1 = sv.version# (+)
/
grant select on sys.exu81typ to select_catalog_role;

rem obtain info on types for current user
CREATE OR REPLACE view exu81typu AS
    SELECT * 
    FROM exu81typ
    WHERE ownerid = UID
/
grant select on sys.exu81typu to public
/
rem
rem V8.0 type view, exu8typ must be subseted from exu81typ in order to filter
rem out types marked 'secondary object'.
rem
CREATE OR REPLACE view exu8typ AS
	SELECT * FROM exu81typ WHERE secondaryobj = 0
/
grant select on sys.exu8typ to select_catalog_role
/
rem obtain info on types for current user
CREATE OR REPLACE view exu8typu AS
    SELECT * FROM exu8typ WHERE ownerid = UID
/
grant select on sys.exu8typu to public
/
rem obtain all directory aliases
CREATE OR REPLACE view exu8dir
   (dirname, pathname, objno, audit$ ) AS
   SELECT o$.name, d$.os_path, o$.obj#, d$.audit$
   FROM sys.exu81obj o$, sys.dir$ d$
   WHERE o$.type# = 23      /* directory aliases */
     AND o$.obj# = d$.obj#
/
grant select on exu8dir to select_catalog_role;

rem obtain all foreign function library names
CREATE OR REPLACE view exu8lib
   (lowner, libname, ownerid,  filename, audit$, mtime,
    objno, isstatic, istrusted ) AS
   SELECT u$.name, o$.name, o$.owner#,
          lb$.filespec,
          lb$.audit$,
          TO_CHAR( o$.mtime, 'YYYY-MM-DD:HH24:MI:SS'),
          o$.obj#,
          DECODE( bitand (lb$.property, 1), 1, 1, 0),
          DECODE( bitand( lb$.property, 2), 2, 1, 0)
   FROM sys.exu81obj o$, sys.user$ u$, sys.library$ lb$
   WHERE o$.type#  = 22   /* library name */
     AND o$.owner# = u$.user#
     AND o$.obj#   = lb$.obj#
/
grant select on exu8lib to select_catalog_role;

rem obtain foreign function library names for user
CREATE OR REPLACE view exu8libu AS
   SELECT *
   FROM exu8lib
   WHERE ownerid = UID
/
grant select on exu8libu to public;

rem obtain REF SCOPE and WITH ROWID information for REF attributes and
rem columns
CREATE OR REPLACE view exu8rfs
  (objno, ownerid, property, colname, reftyp, soid)
AS SELECT o$.obj#, o$.owner#, c$.property,
   DECODE(bitand( c$.property,1),1,a$.name, c$.name),
   rf$.reftyp, rf$.stabid
FROM sys.refcon$ rf$, sys.obj$ o$, sys.col$ c$, sys.attrcol$ a$
WHERE rf$.obj# = o$.obj# AND
      rf$.obj# = c$.obj# AND rf$.intcol# = c$.intcol# AND
      rf$.obj# = a$.obj# (+) AND rf$.intcol# = a$.intcol# (+)
      AND rf$.reftyp != 0
      AND bitand(c$.property,32768) != 32768     /* not unused column */
/
grant select on exu8rfs to select_catalog_role;

CREATE OR REPLACE view exu8rfsu AS
  SELECT *
  FROM exu8rfs
  WHERE ownerid = UID
/
grant select on exu8rfsu to public;

REM
REM Initial view included the sys.cdef$ table which broke export/import
REM cycle when SCOPE IS syntax was used for table constraints.
REM 
CREATE OR REPLACE view exu8orfs
  (objno, ownerid, property, colname, reftyp, soid, robjid, rname)
AS SELECT o$.obj#, o$.owner#, c$.property,
   DECODE(bitand( c$.property,1),1,a$.name, c$.name),
   rf$.reftyp, rf$.stabid, ro$.obj#, ro$.name
FROM sys.refcon$ rf$, sys.obj$ o$, sys.col$ c$, sys.attrcol$ a$,
     sys.obj$ ro$
WHERE rf$.obj# = o$.obj# AND
      rf$.obj# = c$.obj# AND rf$.intcol# = c$.intcol# AND
      rf$.obj# = a$.obj# (+) AND rf$.intcol# = a$.intcol# (+)
      AND rf$.reftyp != 0
      AND bitand(c$.property,32768) != 32768     /* not unused column */
      AND rf$.stabid = ro$.oid$
      AND (UID IN (o$.owner#, 0) or
          exists ( select * from session_roles
                   where role='SELECT_CATALOG_ROLE' ))
/
grant select on exu8orfs to public;

rem obtain parent table info for an inner nested table
CREATE OR REPLACE view exu8pnt
   (pobjno, pname, pownerid, cobjno)
AS SELECT nt$.obj#, o$.name, o$.owner#, nt$.ntab#
FROM sys.obj$ o$, sys.ntab$ nt$
WHERE nt$.obj# = o$.obj#
/
grant select on exu8pnt to select_catalog_role;

CREATE OR REPLACE view exu8pntu AS
   SELECT * 
   FROM exu8pnt
   WHERE pownerid = UID
/
grant select on exu8pntu to public;
 
rem bitmap, functional and domain indexes are included if the base table
rem is included or if the index is defined on a table, and that table
rem references a table that is included in the export.

rem Note that the following 6 views do not handle references to inner
rem nested tables, since references to and from inner nested tables
rem are not currently supported by SQL.  Should SQL allow such references
rem in the future, these views will need to be modified.

rem incremental export for bitmap,functional and domain indices
rem (used only when record = yes)
CREATE OR REPLACE view exu81indic AS
   SELECT * from sys.exu81ind
   WHERE ( bitmap = 1 OR             	/* select bitmap, */
           bitand(property,16) = 16 OR  /* functional, */
           type = 9)                    /* and domain indexes */
         AND (iownerid, btname) in
         (  (SELECT i.owner#, i.name
             FROM sys.incexp i, sys.incvid v
             WHERE i.expid > v.expid AND i.type# = 2)
         UNION
            (SELECT r.ownerid, r.tname
             FROM sys.incexp ii, sys.incvid vv, sys.exu8ref r
             WHERE  /* references a table included in the export */
                   ( r.rtname = ii.name AND r.rownerid = ii.owner#
                     AND ii.expid > vv.expid AND ii.type# = 2 )
            )
         )
/
grant select on exu81indic to select_catalog_role;

rem incremental export for bitmap indices in 8.0 (used only when record = yes)
CREATE OR REPLACE view exu8indic AS
   SELECT * from sys.exu81indic
   WHERE bitand(property,16) != 16 AND /* Get bitmap but not functional, */
         type != 9                     /* nor domain indexes */
/
grant select on exu8indic to select_catalog_role;

rem incremental export for bitmap, functional and domain indexes
rem note: exutabi will return the correct table name because record = no
CREATE OR REPLACE view exu81indi AS
   SELECT * from sys.exu81ind
   WHERE ( bitmap = 1 OR             	/* select bitmap, */
           bitand(property,16) = 16 OR  /* functional, */
           type = 9)                    /* and domain indexes */
         AND (iownerid, btname) in
         (   (SELECT ownerid, name from sys.exu81tabi)
         UNION
             (SELECT r.ownerid, r.tname
              FROM sys.exu81tabi ii, sys.exu8ref r
              WHERE ( r.robjid = ii.objid ) /* table included in inc */
             )
         )
/
grant select on exu81indi to select_catalog_role;

rem incremental export for V8.0 bitmap indexes
CREATE OR REPLACE view exu8indi AS
   SELECT * from sys.exu81indi
   WHERE bitand(property,16) != 16 AND /* Get bitmap but not functional, */
         type != 9                     /* nor domain indexes */
/
grant select on exu8indi to select_catalog_role;

rem cumulative export for bitmap, functional and domain indexes
rem note: assume exutabc will return correct table name because record = no
CREATE OR REPLACE view exu81indc AS
   SELECT * from sys.exu81ind
   WHERE ( bitmap = 1 OR             	/* select bitmap, */
           bitand(property,16) = 16 OR  /* functional, */
           type = 9)                    /* and domain indexes */
         AND (iownerid, btname) in
         (   (SELECT ownerid, name from sys.exu81tabc)
         UNION
             (SELECT r.ownerid, r.tname
              FROM sys.exu81tabc cc, sys.exu8ref r
              WHERE ( r.robjid = cc.objid ) /*  table included in cum */
             )
         )
/
grant select on exu81indc to select_catalog_role;

rem cumulative export for V8.0 bitmap indexes
CREATE OR REPLACE view exu8indc AS
   SELECT * from sys.exu81indc
   WHERE bitand(property,16) != 16 AND /* Get bitmap but not functional, */
         type != 9                     /* nor domain indexes */
/
grant select on exu8indc to select_catalog_role;

REM Operators
CREATE OR REPLACE view exu81opr
	(name, objid, owner, ownerid, sqlver) AS
	SELECT o.name, o.obj#, u.name, o.owner#, sv.sql_version
	FROM   sys.exu81obj o, sys.user$ u, sys.operator$ op, 
               sys.exu816sqv sv
	WHERE  o.obj# = op.obj#
	  AND  o.owner# = u.user#
          AND  o.spare1 = sv.version# (+)
/
grant select on sys.exu81opr to select_catalog_role;

REM Get operators for current user
CREATE OR REPLACE view exu81opru AS
    SELECT * 
    FROM exu81opr
    WHERE ownerid = UID
/
grant select on sys.exu81opru to public;

REM Get Operators for incremental export.
CREATE OR REPLACE VIEW exu81opri AS
    SELECT op.* FROM sys.exu81opr op, sys.incexp i, sys.incvid v
    WHERE op.name = i.name(+) AND
          op.ownerid = i.owner#(+) AND
	  NVL(i.type#,33) = 33 AND
          v.expid < NVL(i.expid, 9999)
/
grant select on sys.exu81opri to select_catalog_role;

REM Get Operators for cumulative export.
CREATE OR REPLACE VIEW exu81oprc AS
    SELECT op.* FROM sys.exu81opr op, sys.incexp i, sys.incvid v
    WHERE op.name = i.name(+) AND
          op.ownerid = i.owner#(+) AND
	  NVL(i.type#,33) = 33 AND
          (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR 
          v.expid < NVL(i.expid, 9999))
/
grant select on sys.exu81oprc to select_catalog_role;

REM Indextypes
CREATE OR REPLACE view exu81ity
	(name, objid, owner, ownerid) AS
	SELECT o.name, o.obj#, u.name, o.owner#
	FROM   sys.exu81obj o, sys.user$ u, sys.indtypes$ i
	WHERE  o.obj# = i.obj#
	  AND  o.owner# = u.user#
/
grant select on sys.exu81ity to select_catalog_role;

REM Get Indextypes for current user
CREATE OR REPLACE view exu81ityu AS
    SELECT * 
    FROM exu81ity
    WHERE ownerid = UID
/
grant select on sys.exu81ityu to public;

REM Get Indextypes for incremental export.
CREATE OR REPLACE VIEW exu81ityi AS
    SELECT it.* FROM sys.exu81ity it, sys.incexp i, sys.incvid v
    WHERE it.name = i.name(+) AND
          it.ownerid = i.owner#(+) AND
	  NVL(i.type#,32) = 32 AND
          v.expid < NVL(i.expid, 9999)
/
grant select on sys.exu81ityi to select_catalog_role;

REM Get Indextypes for cumulative export.
CREATE OR REPLACE VIEW exu81ityc AS
    SELECT it.* FROM sys.exu81ity it, sys.incexp i, sys.incvid v
    WHERE it.name = i.name(+) AND
          it.ownerid = i.owner#(+) AND
	  NVL(i.type#,32) = 32 AND
          (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR 
          v.expid < NVL(i.expid, 9999))
/
grant select on sys.exu81ityc to select_catalog_role;

Rem Get Row Level Security policies 
CREATE OR REPLACE view exu81rls
       (objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled) AS
       select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname,
                decode(bitand(r.stmt_type,1), 0,'', 'SELECT,')
       ||       decode(bitand(r.stmt_type,2), 0,'', 'INSERT,')
       ||       decode(bitand(r.stmt_type,4), 0,'', 'UPDATE,')
       ||       decode(bitand(r.stmt_type,8), 0,'', 'DELETE,'),
                r.check_opt, r.enable_flag
       from user$ u, obj$ o, rls$ r
       where u.user# = o.owner#
       and r.obj# = o.obj#
       and (uid = 0  or
            uid = o.owner# or 
            exists ( select * from session_roles where role='SELECT_CATALOG_ROLE')
           )
/
grant select on sys.exu81rls to public;
/


REM Get all Dimensions
CREATE OR REPLACE VIEW exu8dim (ownerid, owner, dimname, dimtext)
       AS
       SELECT o.owner#, u.name, o.name, dm.dimtext
       FROM sys.obj$ o, sys.user$ u, sys.dim$ dm
       WHERE u.user# = o.owner#
       AND dm.obj# = o.obj#
       AND (uid = 0 or uid = o.owner# or 
           exists (select * from session_roles where role='SELECT_CATALOG_ROLE')
           )
/
grant select on sys.exu8dim to public;

REM Get Dimensions for incremental export.
CREATE OR REPLACE VIEW exu8dimi AS
    SELECT dm.* FROM sys.exu8dim dm, sys.incexp i, sys.incvid v
    WHERE dm.dimname = i.name(+) AND
          dm.ownerid = i.owner#(+) AND
	  NVL(i.type#,43) = 43 AND
          v.expid < NVL(i.expid, 9999)
/
grant select on sys.exu8dimi to select_catalog_role;

REM Get Dimensions for Cumulative export.
CREATE OR REPLACE VIEW exu8dimc AS
    SELECT dm.* FROM sys.exu8dim dm, sys.incexp i, sys.incvid v
    WHERE dm.dimname = i.name(+) AND
          dm.ownerid = i.owner#(+) AND
	  NVL(i.type#,43) = 43 AND
          (NVL(i.ctime,TO_DATE('01-01-1900','DD-MM-YYYY')) < i.itime OR 
          v.expid < NVL(i.expid, 9999))
/
grant select on sys.exu8dimc to select_catalog_role;

REM All procedural objects. If the user has SELECT_CATALOG_ROLE, can see all
REM objects... otherwise, just his own.

CREATE OR REPLACE view exu81procobj
	(name, objid, owner, ownerid, type#, class,
	 prepost, level#, package, pkg_schema) AS
	SELECT o.name, o.obj#, u.name, o.owner#, o.type#, p.class,
	       p.prepost, p.level#, p.package, p.schema
	  FROM sys.exu81obj o, sys.user$ u, sys.exppkgobj$ p
	 WHERE p.type#=o.type#
	   AND o.owner#=u.user#
           AND (UID IN (o.owner#, 0) or 
               exists ( select * from session_roles 
                 where role='SELECT_CATALOG_ROLE' ))
/
grant select on sys.exu81procobj to public
/
REM All instance class procedural objects: These are objects dependent upon a
REM parent object.

CREATE OR REPLACE view exu81procobjinstance
	(name, objid, owner, ownerid, type#, class, prepost, level#, package,
         pkg_schema, par_name, par_objid, par_property) AS
	SELECT o.name, o.objid, o.owner, o.ownerid, o.type#, o.class,o.prepost,
               o.level#, o.package, o.pkg_schema, op.name, d.p_obj#, t.property
	 FROM sys.exu81procobj o, sys.expdepobj$ d, sys.exu81obj op, sys.tab$ t
	 WHERE o.class=3
	   AND d.d_obj#=o.objid
	   AND d.p_obj#=op.obj#
	   AND d.p_obj#=t.obj#
/
grant select on sys.exu81procobjinstance to public
/
REM Packages providing procedural object support.

CREATE OR REPLACE view exu81objectpkg
	(package, pkg_schema, class, type#, level#) AS
	SELECT p.package, p.schema, p.class, p.type#, p.level#
	  FROM sys.exppkgobj$ p
/
grant select on sys.exu81objectpkg to public
/
REM Packages providing procedural actions

CREATE OR REPLACE view exu81actionpkg
	(package, pkg_schema, class, level#) AS
	SELECT p.package, p.schema, p.class, p.level#
	  FROM sys.exppkgact$ p
/
grant select on sys.exu81actionpkg to public
/
REM Objects that have procedural actions associated with them. Users with
REM 'SELECT_CATALOG_ROLE or can see all objects; otherwise, just their own.

CREATE OR REPLACE view exu81actionobj
	(name, objid, owner, ownerid, property, type#, level#,
         package, pkg_schema) AS
	SELECT o.name, d.obj#, u.name, o.owner#, t.property, o.type#,
	       p.level#, p.package, p.schema
	  FROM sys.exu81obj o, sys.user$ u, sys.exppkgact$ p, sys.tab$ t,
               sys.expdepact$ d
	 WHERE d.obj#=o.obj#
           AND o.owner#=u.user#
	   AND d.package=p.package
	   AND d.schema=p.schema
	   AND p.class=3
           AND d.obj#=t.obj#
           AND (UID IN (o.owner#, 0) or 
               exists ( select * from session_roles 
                 where role='SELECT_CATALOG_ROLE' ))
/
grant select on sys.exu81actionobj to public
/

REM Get Collection Storage Compatibility Information
CREATE or REPLACE view exu81csc (release) AS
		SELECT release 
		FROM v$compatibility
		WHERE type_id = 'COLLSTO'
/
grant select on exu81csc to public
/

REM Enumerate tables in tablespace
REM Include tables that are either stored in the tablespace or whose 1st
REM (sub)partition is in the tablespace.  If a table is partitioned, but
REM its first partition is not stored in one of the PITR/TTS tablespaces,
REM it will not be returned through this query.  This will effectively drop
REM the partition when the TTS tablespaces are plugged back in.  
REM If a secondary partition's tablespace is not in the TTS list, it will
REM be accounted for by the PL/SQL procedure that determines whether a TTS
REM is consistent.
REM
CREATE OR REPLACE VIEW exu81tts (dobjid, name, ownerid, tabno, 
                                 clusterflag, tsname) 
    AS /* Unpartitioned tables */
	SELECT t$.dataobj#, o$.name, o$.owner#,
               NVL(t$.tab#,0), NVL(t$.bobj#, 0), ts$.name
        FROM sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$
	WHERE t$.obj# = o$.obj# and t$.ts# = ts$.ts#
              and bitand(t$.property, 64+512+8192) = 0 /*Not nested or IOT*/
    UNION ALL /* 1st Partition is simple */
	SELECT 0, o$.name, o$.owner#,
               NVL(t$.tab#,0), NVL(t$.bobj#, 0), ts$.name
        FROM sys.tab$ t$, sys.obj$ o$, sys.tabpart$ tp$, sys.ts$ ts$
	WHERE t$.obj# = o$.obj# and 
              t$.obj# = tp$.bo# and tp$.part# = 1 and tp$.ts# = ts$.ts#
              and bitand(t$.property, 64+512+8192) = 0
    UNION ALL  /* 1st Partition is subpartitioned */
	SELECT 0, o$.name, o$.owner#,
               NVL(t$.tab#,0), NVL(t$.bobj#, 0), ts$.name
        FROM sys.tab$ t$, sys.obj$ o$, sys.tabcompart$ tcp$, 
             sys.tabsubpart$ tsp$, sys.ts$ ts$
	WHERE t$.obj# = o$.obj# and 
              t$.obj# = tcp$.bo# and tcp$.part# = 1 and
              tcp$.obj# = tsp$.pobj# and tsp$.subpart# = 1 and 
              tsp$.ts# = ts$.ts#
              and bitand(t$.property, 64+512+8192) = 0
    UNION ALL /* Unpartitioned IOTs */
        SELECT i$.dataobj#, o$.name, o$.owner#,
               NVL(t$.tab#,0), NVL(t$.bobj#, 0), ts$.name
        FROM sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.ind$ i$
        WHERE t$.obj# = o$.obj# and 
	      t$.pctused$ = i$.obj# and /* For IOTs, pctused has index obj# */
              i$.ts# = ts$.ts# and
              bitand(t$.property, 64+512+8192) = 64
    UNION ALL /* 1st Partition of IOT */
	SELECT 0, o$.name, o$.owner#,
               NVL(t$.tab#,0), NVL(t$.bobj#, 0), ts$.name
        FROM sys.tab$ t$, sys.obj$ o$, sys.indpart$ ip$, sys.ts$ ts$
	WHERE t$.obj# = o$.obj# and 
	      t$.pctused$ = ip$.bo# and ip$.part# = 1 and ip$.ts# = ts$.ts#
              and bitand(t$.property, 64+512+8192) = 64
/
grant select on exu81tts to select_catalog_role
/


REM Determine icache plsql mode
REM YES or NO, if not present, default to NO
REM
CREATE OR REPLACE view exu8icplsql (value) AS
                SELECT value$ from sys.props$
                WHERE name = 'ICACHE_IMP_PLSQL'
/
grant select on exu8icplsql to public
/
REM
REM Add versioning support for export
REM This will get bumped up as the views evolve. The insert is needed
REM for upgrades from 7.0 or new databases. The update is needed for 
REM databases that have older compatibility.
REM These are the releases when the compatibility was bumped:
REM    7.0.* - no compatibility - assume zero
REM    7.1.3 - set to one
REM    7.2.1 - set to two
REM    7.2.3 - set to three
REM    8.0.1 - set to four
REM    8.0.2 - set to five
REM    8.0.3 - set to six
REM    8.0.4 - set to seven
REM    8.1.6 - set to eight
REM
insert into props$
select 'EXPORT_VIEWS_VERSION', '8', 'Export views revision #' from dual
where not exists
  (select 'x' from props$ where name = 'EXPORT_VIEWS_VERSION')
/
update props$ set value$ = 8 where name = 'EXPORT_VIEWS_VERSION'
/
commit
/
