home *** CD-ROM | disk | FTP | other *** search
/ PC World Komputer 1996 February / PCWK0296.iso / po7_win / db / rdbms71 / catldr.sql < prev    next >
Text File  |  1994-08-07  |  10KB  |  280 lines
  1. Rem Copyright (c) 1990 by Oracle Corporation
  2. Rem NAME
  3. Rem    catldr.sql
  4. Rem  FUNCTION
  5. Rem    Views for the direct path of the loader
  6. Rem  NOTES
  7. Rem  MODIFIED
  8. Rem     ksudarsh   02/09/94 -  look for bo# in ind_info and indcol_info views
  9. Rem     ksudarsh   02/04/94 -  fix authorizations
  10. Rem     jbellemo   11/29/93 -  #170173: change uid to userenv schemaid
  11. Rem     ksudarsh   11/02/92 -  pdl changes 
  12. Rem     tpystyne   11/22/92 -  use create or replace view 
  13. Rem     glumpkin   10/25/92 -  Renamed from ULVIEW.SQL 
  14. Rem     cheigham   04/28/92 -  users should see info only on tables on which th
  15. Rem     cheigham   10/26/91 -  Creation 
  16. Rem     cheigham   10/07/91 -  add lists, groups to tab,ind views
  17. Rem     cheigham   09/30/91 -  merge changes from branch 1.3.50.2 
  18. Rem     cheigham   09/23/91 -  fix cdef$ column reference 
  19. Rem     cheigham   08/27/91 -  add ts# to loader_tab_info: 
  20. Rem     cheigham   04/11/91 -         expand loader_constraint_info 
  21. Rem   Heigham    09/26/90 - fix v7 LOADER_TRIGGER_INFO def
  22. Rem   Heigham    07/16/90 - remove duplicate grant
  23. Rem   Heigham    06/28/90 - add v$parameters grant
  24. Rem   Heigham    01/22/90 - Creation
  25. Rem
  26. rem 
  27. rem $Header: catldr.sql 7010300.1 94/02/24 18:24:44 snataraj Generic<base> $ ulview.sql 
  28. rem 
  29.  
  30. create or replace view LOADER_COL_INFO
  31. (TABNAME, OWNER, COLNAME, SEGCOL, TYPE, LENGTH, PRECISION, SCALE, NONULL,
  32. OFFSET)
  33. as
  34. select o.name, u.name, c.name, c.segcol#, c.type#, c.length, c.precision,
  35. c.scale, c.null$, c.offset
  36. from sys.col$ c, sys.obj$ o, sys.user$ u
  37. where o.obj# = c.obj#
  38. and o.owner# = u.user#
  39.  and (o.owner# = userenv('schemaid')
  40.        or o.obj# in
  41.             (select oa.obj#
  42.              from sys.objauth$ oa
  43.              where grantee# in ( select kzsrorol
  44.                                  from x$kzsro
  45.                                )
  46.         )
  47.        or /* user has system privileges */
  48.           exists (select null from v$enabledprivs
  49.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  50.                                         -47 /* SELECT ANY TABLE */,
  51.                                         -48 /* INSERT ANY TABLE */,
  52.                                         -49 /* UPDATE ANY TABLE */,
  53.                                         -50 /* DELETE ANY TABLE */)
  54.                   )
  55.       )
  56. /
  57. drop public synonym LOADER_COL_INFO
  58. /
  59. create public synonym LOADER_COL_INFO for LOADER_COL_INFO
  60. /
  61. grant select on LOADER_COL_INFO to public
  62. /
  63. create or replace view LOADER_TAB_INFO
  64. (NAME, FILENO, BLOCKNO, NUMCOLS, OWNER, OBJECTNO, TABLESPACENO, LISTS, GROUPS)
  65. as
  66. select o.name, t.file#, t.block#, t.cols, u.name, t.obj#, t.ts#, s.lists,
  67. s.groups
  68. from sys.tab$ t, sys.obj$ o, sys.user$ u, sys.seg$ s
  69. where t.obj# = o.obj#
  70. and o.owner# = u.user#
  71. and t.file# = s.file#
  72. and t.block# = s.block#
  73.  and (o.owner# = userenv('schemaid')
  74.        or o.obj# in
  75.             (select oa.obj#
  76.              from sys.objauth$ oa
  77.              where grantee# in ( select kzsrorol
  78.                                  from x$kzsro
  79.                                )
  80.         )
  81.        or /* user has system privileges */
  82.           exists (select null from v$enabledprivs
  83.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  84.                                         -47 /* SELECT ANY TABLE */,
  85.                                         -48 /* INSERT ANY TABLE */,
  86.                                         -49 /* UPDATE ANY TABLE */,
  87.                                         -50 /* DELETE ANY TABLE */)
  88.                   )
  89.       )
  90. /
  91. drop public synonym LOADER_TAB_INFO
  92. /
  93. create public synonym LOADER_TAB_INFO for LOADER_TAB_INFO
  94. /
  95. grant select on LOADER_TAB_INFO to PUBLIC
  96. /
  97. create or replace view LOADER_IND_INFO
  98. (NAME, OWNER_NAME, TABLESPACENO, PCTFRE, FILENO, BLOCKNO, NUMCOLS, OWNERNO, 
  99. UNIQUENESS, OBJECTNO, LISTS, GROUPS)
  100. as 
  101. select o.name, u.name, i.ts#, i.pctfree$, i.file#, i.block#, i.cols, o.owner#,
  102. i.unique$, i.obj#, s.lists, s.groups
  103. from sys.ind$ i, sys.obj$ o, sys.user$ u, sys.seg$ s
  104. where i.obj# = o.obj#
  105. and o.owner# = u.user#
  106. and i.file# = s.file#
  107. and i.block# = s.block#
  108.  and (o.owner# = userenv('schemaid')
  109.        or i.bo# in
  110.             (select oa.obj#
  111.              from sys.objauth$ oa
  112.              where grantee# in ( select kzsrorol
  113.                                  from x$kzsro
  114.                                )
  115.         )
  116.        or /* user has system privileges */
  117.           exists (select null from v$enabledprivs
  118.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  119.                                         -47 /* SELECT ANY TABLE */,
  120.                                         -48 /* INSERT ANY TABLE */,
  121.                                         -49 /* UPDATE ANY TABLE */,
  122.                                         -50 /* DELETE ANY TABLE */)
  123.                   )
  124.       )
  125. /
  126. drop public synonym LOADER_IND_INFO
  127. /
  128. create public synonym LOADER_IND_INFO for LOADER_IND_INFO
  129. /
  130. grant select on LOADER_IND_INFO to PUBLIC
  131. /
  132. create or replace view LOADER_INDCOL_INFO
  133. (INDEX_NAME, INDEX_OWNER, POSITION, SEGCOL)
  134. as 
  135. select idx.name, io.name, ic.pos#, ic.segcol#
  136. from sys.user$ io, sys.obj$ idx, sys.icol$ ic
  137. where idx.obj# = ic.obj#
  138. and  idx.owner# = io.user# 
  139. and (idx.owner# = userenv('schemaid')
  140.        or ic.bo# in
  141.             (select oa.obj#
  142.              from sys.objauth$ oa
  143.              where grantee# in ( select kzsrorol
  144.                                  from x$kzsro
  145.                                )
  146.         )
  147.        or /* user has system privileges */
  148.           exists (select null from v$enabledprivs
  149.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  150.                                         -47 /* SELECT ANY TABLE */,
  151.                                         -48 /* INSERT ANY TABLE */,
  152.                                         -49 /* UPDATE ANY TABLE */,
  153.                                         -50 /* DELETE ANY TABLE */)
  154.                   )
  155.     )
  156. /
  157. drop public synonym LOADER_INDCOL_INFO
  158. /
  159. create public synonym LOADER_INDCOL_INFO for LOADER_INDCOL_INFO
  160. /
  161. grant select on LOADER_INDCOL_INFO to PUBLIC
  162. /
  163. create or replace view LOADER_PARAM_INFO
  164. (BLOCKSZ, SERIALIZABLE)
  165. as 
  166. select v1.value, v2.value from v$parameter v1, v$parameter v2
  167. where v1.name = 'db_block_size' and  v2.name = 'serializable'
  168. /
  169. drop public synonym LOADER_PARAM_INFO
  170. /
  171. create public synonym LOADER_PARAM_INFO for LOADER_PARAM_INFO
  172. /
  173. grant select on LOADER_PARAM_INFO to PUBLIC
  174. /
  175. remark
  176. remark VIEWS FOR FIXED TABLES OF STATISTICS
  177. remark
  178. remark CONTROL BLOCK STATS 
  179. remark
  180. create or replace view v_$loadcstat as select * from v$loadcstat;
  181. drop public synonym v$loadcstat;
  182. create public synonym v$loadcstat for v_$loadcstat;
  183. grant select on v_$loadcstat to public;
  184. remark
  185. remark TABLE STATS 
  186. remark
  187. create or replace view v_$loadtstat as select * from v$loadtstat;
  188. drop public synonym v$loadtstat;
  189. create public synonym v$loadtstat for v_$loadtstat;
  190. grant select on v_$loadtstat to public;
  191. remark
  192. remark VIEWS FOR V7
  193. create or replace view LOADER_CONSTRAINT_INFO
  194. (OWNER, CONSTRAINT_NAME, TYPE, TABLE_NAME, ENABLED, NOTNULL, NUMCOLS)
  195. as
  196.    select u.name, con.name, cd.type,
  197.    o.name, cd.enabled, col.null$, cd.cols
  198.    from sys.con$ con, sys.user$ u, sys.cdef$ cd, sys.obj$ o,
  199.    sys.ccol$ cco, sys.col$ col
  200.    where con.owner# = u.user#
  201.    and con.con# = cd.con#
  202.    and cd.obj# = o.obj#
  203.    and cco.con# = con.con#
  204.    and col.obj# = cco.obj#
  205.    and col.col# = cco.col#
  206.    and (con.owner# = userenv('schemaid')
  207.        or o.obj# in
  208.             (select oa.obj#
  209.              from sys.objauth$ oa
  210.              where grantee# in ( select kzsrorol
  211.                                  from x$kzsro
  212.                                )
  213.         )
  214.        or /* user has system privileges */
  215.           exists (select null from v$enabledprivs
  216.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  217.                                         -47 /* SELECT ANY TABLE */,
  218.                                         -48 /* INSERT ANY TABLE */,
  219.                                         -49 /* UPDATE ANY TABLE */,
  220.                                         -50 /* DELETE ANY TABLE */)
  221.                   )
  222.       )
  223. /
  224. drop public synonym LOADER_CONSTRAINT_INFO
  225. /
  226. create public synonym LOADER_CONSTRAINT_INFO for LOADER_CONSTRAINT_INFO
  227. /
  228. grant select on LOADER_CONSTRAINT_INFO to PUBLIC
  229. /
  230. create or replace view LOADER_TRIGGER_INFO
  231. (OWNER, TRIGGER_NAME, TABLE_NAME, ENABLED)
  232. as
  233.    select u.name, o1.name, o.name, t.enabled
  234.    from sys.obj$ o, sys.obj$ o1, sys.user$ u, sys.trigger$ t
  235.    where t.baseobject = o.obj#
  236.    and o.owner# = u.user#
  237.    and t.obj# = o1.obj#
  238.  and (o.owner# = userenv('schemaid')
  239.        or o.obj# in
  240.             (select oa.obj#
  241.              from sys.objauth$ oa
  242.              where grantee# in ( select kzsrorol
  243.                                  from x$kzsro
  244.                                )
  245.         )
  246.        or /* user has system privileges */
  247.           exists (select null from v$enabledprivs
  248.                   where priv_number in (-45 /* LOCK   ANY TABLE */,
  249.                                         -47 /* SELECT ANY TABLE */,
  250.                                         -48 /* INSERT ANY TABLE */,
  251.                                         -49 /* UPDATE ANY TABLE */,
  252.                                         -50 /* DELETE ANY TABLE */)
  253.                   )
  254.      )
  255. /
  256. drop public synonym LOADER_TRIGGER_INFO
  257. /
  258. create public synonym LOADER_TRIGGER_INFO for LOADER_TRIGGER_INFO
  259. /
  260. grant select on LOADER_TRIGGER_INFO to PUBLIC
  261. /
  262. remark
  263. remark VIEWS for Parallel Data Loader
  264. remark
  265. drop view LOADER_FILE_TS
  266. /
  267. create view LOADER_FILE_TS
  268. (TABLESPACENO, FILENAME, FILENO)
  269. as
  270.    select file$.ts#, v$dbfile.name, file$.file# 
  271.    from file$, v$dbfile
  272.    where file$.file# = v$dbfile.file#
  273. /
  274. drop public synonym LOADER_FILE_TS
  275. /
  276. create public synonym LOADER_FILE_TS for LOADER_FILE_TS
  277. /
  278. grant select on LOADER_FILE_TS to public
  279. /
  280.