rem 
rem $Header: catldr.sql 06-oct-98.14:55:56 vnimani Exp $ ulview.sql 
rem
Rem Copyright (c) 1990, 1995, 1996, 1997, 1998 by Oracle Corporation
Rem NAME
Rem    catldr.sql
Rem  FUNCTION
Rem    Views for the direct path of the loader
Rem  NOTES
Rem    This script must be run while connected as SYS or INTERNAL.
Rem  MODIFIED
Rem     vnimani    08/31/98  - add view to check the type of the ref           
Rem     nlau       04/28/98 -  Bug #359063: re-write LOADER_CONSTRAINT_INFO
Rem     rjenkins   12/03/97 -  using ENABLE NOVALIDATE constraints
Rem     abrumm     03/23/97 -  Bug #462556: fix FILE= for partitioned tables
Rem     abrumm     08/19/96 -  ldr_bmx_mrg: delete LOADER_IND*_INFO views
Rem     mcoyle     08/26/96 -  add gv$ public synonyms
Rem     mmonajje   05/22/96 -  Replace precision col name with precision#
Rem     abrumm     02/26/96 -  TSRDBA: LOADER_FILE_TS returns rel fileno
Rem     jhealy     01/04/96 -  partition support: add partition stats view
Rem     jhealy     01/16/96 -  remove bitmap index spare8 query
Rem     jhealy     11/07/95 -  bitmap index support phase 1
Rem     skaluska   10/04/95 -  Rename unique$ to property
Rem     wmaimone   05/06/94 -  #184921 run as sys/internal
Rem     ksudarsh   04/07/94 -  update loader_constraints_info
Rem     ksudarsh   02/06/94 -  merge changes from branch 1.3.710.2
Rem     ksudarsh   02/04/94 -  fix authorizations
Rem     jbellemo   12/17/93 -  merge changes from branch 1.3.710.1
Rem     jbellemo   11/29/93 -  #170173: change uid to userenv schemaid
Rem     ksudarsh   11/02/92 -  pdl changes 
Rem     tpystyne   11/22/92 -  use create or replace view 
Rem     glumpkin   10/25/92 -  Renamed from ULVIEW.SQL 
Rem     cheigham   04/28/92 -  users should see info only on tables on which th
Rem     cheigham   10/26/91 -  Creation 
Rem     cheigham   10/07/91 -  add lists, groups to tab,ind views
Rem     cheigham   09/30/91 -  merge changes from branch 1.3.50.2 
Rem     cheigham   09/23/91 -  fix cdef$ column reference 
Rem     cheigham   08/27/91 -  add ts# to loader_tab_info: 
Rem     cheigham   04/11/91 -         expand loader_constraint_info 
Rem   Heigham    09/26/90 - fix v7 LOADER_TRIGGER_INFO def
Rem   Heigham    07/16/90 - remove duplicate grant
Rem   Heigham    06/28/90 - add v$parameters grant
Rem   Heigham    01/22/90 - Creation
Rem
rem 

create or replace view LOADER_TAB_INFO
(NAME, NUMCOLS, OWNER, OBJECTNO, TABLESPACENO, PARTITIONED)
as
select o.name, t.cols, u.name, t.obj#, t.ts#,
       decode(bitand(t.property, 32), 32, 'YES', 'NO')
from sys.tab$ t, sys.obj$ o, sys.user$ u
where t.obj# = o.obj#
and o.owner# = u.user#
 and (o.owner# = userenv('schemaid')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
	    )
       or /* user has system privileges */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK   ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
      )
/
drop public synonym LOADER_TAB_INFO
/
create public synonym LOADER_TAB_INFO for LOADER_TAB_INFO
/
grant select on LOADER_TAB_INFO to PUBLIC
/

create or replace view LOADER_PART_INFO
(PNAME, OBJECTNO, BASEOBJECTNO, TABLESPACENO)
as
select o.subname, tp.obj#, tp.bo#, tp.ts#
from sys.obj$ o, sys.tabpart$ tp, sys.tab$ t
where tp.bo#  = t.obj#
and   tp.obj# = o.obj#
/
drop public synonym LOADER_PART_INFO
/
create public synonym LOADER_PART_INFO for LOADER_PART_INFO
/
grant select on LOADER_PART_INFO to PUBLIC
/

create or replace view LOADER_PARAM_INFO
(BLOCKSZ, SERIALIZABLE)
as 
select v1.value, v2.value from v$parameter v1, v$parameter v2
where v1.name = 'db_block_size' and  v2.name = 'serializable'
/
drop public synonym LOADER_PARAM_INFO
/
create public synonym LOADER_PARAM_INFO for LOADER_PARAM_INFO
/
grant select on LOADER_PARAM_INFO to PUBLIC
/
remark
remark VIEWS FOR FIXED TABLES OF STATISTICS
remark
remark CONTROL BLOCK STATS 
remark
create or replace view gv_$loadcstat as select * from gv$loadcstat;
drop public synonym gv$loadcstat;
create public synonym gv$loadcstat for gv_$loadcstat;
grant select on gv_$loadcstat to public;
remark
remark TABLE STATS 
remark
create or replace view gv_$loadtstat as select * from gv$loadtstat;
drop public synonym gv$loadtstat;
create public synonym gv$loadtstat for gv_$loadtstat;
grant select on gv_$loadtstat to public;
remark
remark PARTITION STATS 
remark
create or replace view gv_$loadpstat as select * from gv$loadpstat;
drop public synonym gv$loadpstat;
create public synonym gv$loadpstat for gv_$loadpstat;
grant select on gv_$loadpstat to public;
remark
remark Now the same for v$ views
remark
create or replace view v_$loadcstat as select * from v$loadcstat;
drop public synonym v$loadcstat;
create public synonym v$loadcstat for v_$loadcstat;
grant select on v_$loadcstat to public;
remark
remark TABLE STATS 
remark
create or replace view v_$loadtstat as select * from v$loadtstat;
drop public synonym v$loadtstat;
create public synonym v$loadtstat for v_$loadtstat;
grant select on v_$loadtstat to public;
remark
remark PARTITION STATS 
remark
create or replace view v_$loadpstat as select * from v$loadpstat;
drop public synonym v$loadpstat;
create public synonym v$loadpstat for v_$loadpstat;
grant select on v_$loadpstat to public;
remark
remark VIEWS FOR V7
create or replace view LOADER_CONSTRAINT_INFO
(OWNER, CONSTRAINT_NAME, CONSTRAINT_NUMBER, TYPE, TABLE_NAME, ENABLED,DEFER)
as
   select /*+ ordered index (cd i_cdef2) */
       u.name, con.name, cd.con#, cd.type#,o.name, cd.enabled,nvl(cd.defer,0)
   from sys.user$ u, sys.obj$ o, sys.cdef$ cd, sys.con$ con
   where   o.owner# = u.user#
   and    cd.obj#   = o.obj#
   and   con.con#   = cd.con#
/
drop public synonym LOADER_CONSTRAINT_INFO
/
create public synonym LOADER_CONSTRAINT_INFO for LOADER_CONSTRAINT_INFO
/
grant select on LOADER_CONSTRAINT_INFO to PUBLIC
/
create or replace view LOADER_TRIGGER_INFO
(OWNER, TRIGGER_NAME, TABLE_NAME, ENABLED)
as
   select u.name, o1.name, o.name, t.enabled
   from sys.obj$ o, sys.obj$ o1, sys.user$ u, sys.trigger$ t
   where t.baseobject = o.obj#
   and o.owner# = u.user#
   and t.obj# = o1.obj#
 and (o.owner# = userenv('schemaid')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
	    )
       or /* user has system privileges */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK   ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
     )
/
drop public synonym LOADER_TRIGGER_INFO
/
create public synonym LOADER_TRIGGER_INFO for LOADER_TRIGGER_INFO
/
grant select on LOADER_TRIGGER_INFO to PUBLIC
/
remark
remark VIEWS for Parallel Data Loader
remark  Note: FILENO is now a relative file number.
remark
drop view LOADER_FILE_TS
/
create view LOADER_FILE_TS
(TABLESPACENO, FILENAME, FILENO)
as
   select file$.ts#, v$dbfile.name, file$.relfile# 
   from file$, v$dbfile
   where file$.file# = v$dbfile.file#
/
drop public synonym LOADER_FILE_TS
/
create public synonym LOADER_FILE_TS for LOADER_FILE_TS
/
grant select on LOADER_FILE_TS to public
/

remark ==================================================================
remark
remark VIEW for checking ref type (pk or system based)
remark __________________________________________________________________
create or replace view LOADER_REF_INFO 
(OWNER, TABLE_NAME, OBJECT_ID_TYPE)
as 
	select owner, table_name, object_id_type
	from all_object_tables
/
drop public synonym LOADER_REF_INFO
/
create public synonym LOADER_REF_INFO for LOADER_REF_INFO
/
grant select on LOADER_REF_INFO to public
/
