Rem Rem $Header: rdbms/admin/catqm.sql /st_rdbms_11.2.0/2 2011/05/20 09:12:32 spetride Exp $ Rem Rem catqm.sql Rem Rem Copyright (c) 1900, 2011, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem catqm.sql - CAtalog script for sQl xMl management Rem Rem DESCRIPTION Rem Creates the tables and views needed to run the XDB system Rem Run this script like this: Rem catqm.sql Rem -- XDB_PASSWD: password for XDB user Rem -- TABLESPACE: tablespace for XDB Rem -- TEMP_TABLESPACE: temporary tablespace for XDB Rem -- SECURE_FILES_REPO: if YES and compatibility is at least 11.2, Rem then XDB repository will be stored as secure files; Rem otherwise, old LOBS are used. There is no default value for Rem this parameter, the caller must pass either YES or NO. Rem NOTES Rem Must be run connected as SYS Rem Rem MODIFIED (MM/DD/YY) Rem spetride 05/16/11 - grant select to select_catalog_role Rem badeoti 05/07/10 - disable xdk schema caching for inserts into csx Rem tables during install Rem vmedi 05/06/10 - revert 9144511 changes Rem badeoti 04/23/10 - 9451814: adding prompt for input parameters Rem spetride 12/02/09 - 9144511: disable sch validation for XS Rem badeoti 03/20/09 - remove public synonyms for XDB internal packages Rem yiru 03/06/09 - add XS$NULL into XDB schema list Rem spetride 03/06/09 - 8251841: children col in xdb.xdb$h_index Rem cannot be securefile Rem spetride 02/06/09 - lrg 3573827: install trigger to allow sequences Rem spetride 01/26/09 - 7714185: document user_opt_secfiles Rem spetride 11/15/08 - xdb_installation_trigger: allow triggers Rem spetride 06/24/08 - run catxdpapp Rem spetride 04/29/08 - option: use secure files for xdb$resource Rem sipatel 09/29/08 - bug 7414934. call catxtbix Rem sichandr 09/23/08 - load dbmsxdbrepos Rem badeoti 09/21/08 - 6451792: add object validation to XDB Rem achoi 03/11/08 - register ANONYMOUS as part of XDB Rem sidicula 01/10/08 - Grants to dba, system Rem thbaby 10/27/07 - split prvtxdb to create prvtxdba Rem vkapoor 04/27/07 - lrg 2941734 Rem vkapoor 04/09/07 - bug 5640175 Rem bpwang 10/19/06 - bug 5633032 Rem thbaby 11/02/06 - move dbms_xmlindex package body into prvtxidx Rem pthornto 10/09/06 - move catzxs.sql to EOF Rem vkapoor 07/25/06 - Bug 5371725 Rem rtjoa 05/26/06 - change prvtxdz2.plb location Rem rmurthy 04/21/06 - add prvtxdz2.plb Rem ataracha 06/08/06 - move dbmsxidx before catxidx Rem rmurthy 06/02/06 - call catxdbdl for document links Rem pnath 03/22/06 - add prvtxdbdl.plb Rem pthornto 05/18/06 - add DBMS_XSH_LIB Rem nkhandel 02/20/06 - DOM streaming APIs added Rem smalde 03/09/06 - Add dbms_xmltranslations Rem petam 04/07/06 - separate out the install of ResConfig from ACL Rem abagrawa 03/11/06 - Add xdbready Rem cchui 03/02/06 - move after resconfig package is installed Rem mrafiq 03/06/06 - move catzxs after catxdbpv Rem pnath 02/15/06 - remove link_props from xdb.xdb$d_link Rem rmurthy 02/03/06 - add xdb.d_link table Rem thbaby 02/21/06 - add NFS info into rootinfo Rem rtjoa 02/16/06 - Create a schedule for nfsclient cleanup job Rem pnath 10/13/05 - submit job for nfs client cleanup Rem sidicula 01/18/06 - Adding protocol info into rootinfo Rem taahmed 01/18/06 - Extensible Security Rem mrafiq 09/20/05 - merging changes for upgrade/downgrade Rem thoang 09/22/04 - add dbmsxres.sql & prvtxres.plb Rem ataracha 04/14/04 - add pl/sql dom, xml parser, AND xsl processor Rem nkandalu 07/25/05 - 4494717: set upgrade status if XDB is VALID Rem sidicula 06/25/05 - No need for dbmsxadm as yet Rem rmurthy 03/09/05 - drop function for patching namespace Rem vkapoor 01/13/05 - LRG 1804464 Rem pnath 12/01/04 - prvtxdb.sql needs prvtxmld.sql to be compiled Rem pnath 11/16/04 - delete all objects created in installation Rem rpang 11/18/04 - Add catepg.sql Rem rmurthy 11/11/04 - add dbmsxidx Rem petam 11/11/04 - added execution of xdbinstd.sql Rem najain 07/14/04 - add stateid_restart_sequence Rem pnath 10/22/04 - Make SYS the owner of package dbms_regxdb Rem fge 10/29/04 - call prvtxdr0 Rem attran 08/20/04 - xmlidx Rem rburns 08/17/04 - conditionally run dbmsxdbt Rem rpang 07/16/04 - Renamed epgc to epg Rem fge 07/08/04 - extend xdb$h_link Rem sbalaram 06/10/04 - Add catxlcr - xml schema definitions for LCRs Rem rpang 06/07/04 - Add dbmsepgc.sql and prvtepgc.plb Rem smukkama 02/27/04 - move catxdbtm.sql to after prvtxdb.sql Rem smukkama 01/05/04 - add catxdbtm.sql for compact xml token mgmt Rem attran 02/17/04 - XMLIndex Rem najain 01/27/04 - call prvtxdb0 and prvtxdz0 Rem fge 08/01/03 - xdb$h_link: add secondary index on child_oid Rem sidicula 07/03/03 - prvtxdb to be executed after prvtxdbz Rem fge 05/19/03 - add catxdbeo.sql Rem sidicula 04/16/03 - Revoke powerful privileges from XDB Rem abagrawa 03/09/03 - Separate dbmsxsch and prvtxsch Rem njalali 02/11/03 - setting upgrade state to 1000 Rem smuralid 01/09/03 - add dbmsxdbt Rem sichandr 12/16/02 - invoke pre-condition checks Rem njalali 11/14/02 - making sure 9.2.0.1 -> 9.2.0.2 mig. is noop Rem mkrishna 07/05/02 - dissallow ref cascade for resource and schema tables Rem fge 06/13/02 - rename prvtpidx.sql to prvtxdbp.sql Rem sichandr 04/14/02 - remove index on refcount Rem spannala 03/26/02 - tieing the xdb version to the database version Rem sidicula 02/22/02 - Anonymous login allowed only by HTTP Rem njalali 02/11/02 - removed refcount from H_INDEX Rem rmurthy 02/20/02 - remove owner user Rem fge 01/20/02 - call prvtxdbr.plb Rem fge 01/08/02 - rename prvtxdbpi.sql to prvtpidx.sql Rem spannala 01/13/02 - correcting compilation errors in prvtxreg Rem spannala 01/02/02 - registry Rem sichandr 01/11/02 - catxdbstd.sql becomes catxdbst.sql Rem spannala 01/11/02 - creating all types with fixed toids Rem rmurthy 01/18/02 - add xdbowner role Rem nmontoya 12/18/01 - grant select any table to xdb Rem spannala 12/19/01 - removing connects, creating objects in xdb schema Rem spannala 12/13/01 - beta showstopper cleanup Rem nmontoya 11/29/01 - replace calls of prvt*.sql to prvt*.plb Rem nmontoya 11/14/01 - changing owner ID to GUID Rem nmontoya 11/13/01 - reorder dbmsxdb pkg Rem nagarwal 11/12/01 - change ordering of packages Rem tsingh 11/09/01 - XDB Fake installation and cleanup. Rem nagarwal 11/08/01 - change ordering of catxdbpi.sql Rem najain 11/08/01 - catxdbpi.sql gets loaded before catxdbz.sql Rem nagarwal 11/05/01 - add catxdbpi.sql Rem nle 09/20/01 - add versioning package Rem abagrawa 09/27/01 - Add catxdbc1, catxdbc2 Rem nmontoya 10/12/01 - ADD xdbadmin role Rem nagarwal 09/08/01 - add catxdbpv Rem nmontoya 08/21/01 - ADD pl/sql dom, xml parser, AND xsl processor Rem nmontoya 08/16/01 - grant alter session and dbms_rls execute to xdb Rem nagarwal 08/10/01 - add catxdbr Rem esedlar 08/09/01 - XDB standard packages Rem njalali 07/11/01 - Resource as XMLType Rem spannala 05/18/01 - xmltype_p -> xmltype Rem njalali 05/17/01 - split schema OID in resource into two columns Rem rmurthy 03/09/01 - move schema related setup to catxdbs.sql Rem tsingh 03/01/01 - load xdb.jar Rem njalali 02/15/01 - reinstated the WITH ROWID in the resource table Rem nmontoya 02/14/01 - Add security initialization Rem njalali 02/13/01 - added schema OID to resource table Rem rmurthy 02/02/01 - add support for element ref Rem mkrishna 01/29/01 - remove xmlindex related stuff Rem rmurthy 01/17/01 - changes to allow case-sensitive names Rem rmurthy 12/01/00 - grant create library to xdb Rem esedlar 11/01/00 - Add SQL schema Rem njalali 10/03/00 - removed 'datatype' from resource table Rem esedlar 09/27/00 - Add schema in uniqueness constraints Rem njalali 09/26/00 - removed the 'with rowid' in XDB$RESOURCE. Rem tsingh 09/22/00 - added catxdbdt.sql Rem nmontoya 09/18/00 - Changing default tablespace for xdb schema. Rem esedlar 09/05/00 - Type cache Rem njalali 08/15/00 - changed H_LINK to XDB$H_LINK. Rem tsingh 06/30/00 - Fix tablespace code. Rem tsingh 06/28/00 - sys to system. Rem tsingh 06/20/00 - Resource tables. Rem mkrishna 06/29/00 - add dbmsxidx Rem njalali 04/20/00 - Initial revision Rem njalali 01/00/00 - Created Rem Rem IMPORTANT: For any object type owned by XDB, if changes are made Rem with respect to pre-11.2.0.3 versions, do not issue a new Rem CREATE TYPE DDL part of XDB installation. Instead, use the 11.2.0.3 Rem CREATE TYPE DDL and follow it by required ALTER TYPE statements Rem (essentially, the install and upgrade steps for XDB object types Rem should proceed the same). This is to be done so that every XDB Rem installation has the object type histories at least starting with Rem 11.2.0.3, and it is required by full/database export. prompt prompt prompt Starting Oracle XML DB Installation ... prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt Enter Parameter #1 , password for XDB schema: define xdb_pass = &1 prompt prompt Enter Parameter #2 , tablespace for XDB: define res_tbs = &2 prompt prompt Enter Parameter #3 , temporary tablespace for XDB: define temp_tbs = &3 prompt variable user_opt_secfiles varchar2(30); variable usesecfiles varchar2(3); Rem Determine if secure files will be used for xdb.xdb$resource Rem prompt Enter Parameter #4 , YES/NO prompt ...................If YES and compatibility is at least 11.2, prompt ...................then XDB repository will be stored as secure files. prompt ...................Otherwise, old LOBS are used declare checkcompat boolean := FALSE; begin :usesecfiles := 'NO'; :user_opt_secfiles := '&4'; -- if no option was passed, try using secure files if (nvl(:user_opt_secfiles,'YES') != 'NO') then checkcompat := TRUE; end if; -- secure files can only be used for 11.0.0.0 least compatibility level if (checkcompat) then if ((dbms_metadata_util.get_canonical_vsn(dbms_metadata_util.get_compat_vsn()) >= dbms_metadata_util.get_canonical_vsn('11.0.0.0') )) then :usesecfiles := 'YES'; end if; end if; exception when others then return; end; / prompt prompt Rem Table xdb_installation_tab will store all objects created Rem as a part of XDB installation, where owner is not XDB. Rem Objects already existing in the database and recreated Rem as a part of the installation, will not be added to the table. Rem This table will be used during un-installation of XDB, Rem when all these objects will then be dropped. This table Rem need not contain objects which will be dropped automatically Rem as a result of dropping some other object Rem (eg. all PACKAGE BODY objects will be dropped when corresponding Rem PACKAGE object is dropped). Rem Point to note: Currently only certain object types are Rem inserted into this table. The creation of objects of Rem object type not handled will result in the need to modify Rem the trigger responsible for populating xdb_installation_tab. CREATE TABLE sys.xdb_installation_tab ( Owner VARCHAR2(200), Object_name VARCHAR2(200), Object_type VARCHAR2(200)); Rem Table dropped_xdb_instll_tab will consist of the items that Rem existed prior to XDB installation and were dropped during Rem XDB installation. Creation of an object during the installation Rem that is present in this table will not be added to Rem xdb_installation_tab, as this implies that the object did Rem exist prior to the installation. CREATE TABLE sys.dropped_xdb_instll_tab ( Owner VARCHAR2(200), Object_name VARCHAR2(200), Object_type VARCHAR2(200)); Rem A trigger for every successful drop on the database during Rem XDB installation create or replace trigger sys.dropped_xdb_instll_trigger AFTER DROP ON DATABASE BEGIN insert into dropped_xdb_instll_tab values (dictionary_obj_owner, dictionary_obj_name, dictionary_obj_type); END; / Rem For eXtensible Security required library, cannot do it after the Rem install trigger is added CREATE OR REPLACE LIBRARY DBMS_XSU_LIB TRUSTED AS STATIC; / CREATE OR REPLACE LIBRARY DBMS_XSH_LIB TRUSTED AS STATIC; / Rem A trigger for every object creation during XDB installation Rem The trigger body may need to be modified if other object types Rem are to be handled, which are currently not handled. In addition, Rem catnoqm.sql might need to be modified to handle special Rem cases while dropping objects. create or replace trigger sys.xdb_installation_trigger BEFORE CREATE ON DATABASE DECLARE sql_text varchar2(200); val number; BEGIN if (dictionary_obj_owner != 'XDB') then if (dictionary_obj_type = 'FUNCTION' or dictionary_obj_type = 'INDEX' or dictionary_obj_type = 'PACKAGE' or dictionary_obj_type = 'PACKAGE BODY' or dictionary_obj_type = 'PROCEDURE' or dictionary_obj_type = 'SYNONYM' or dictionary_obj_type = 'TABLE' or dictionary_obj_type = 'TABLESPACE' or dictionary_obj_type = 'TYPE' or dictionary_obj_type = 'VIEW' or dictionary_obj_type = 'USER' or dictionary_obj_type = 'TYPE BODY' or dictionary_obj_type = 'BODY' or dictionary_obj_type = 'TRIGGER' or dictionary_obj_type = 'SEQUENCE' )then if (dictionary_obj_type != 'PACKAGE BODY' ) then sql_text := 'select count(*) from ALL_OBJECTS where owner = :1 and object_name = :2 and object_type = :3'; execute immediate sql_text into val using dictionary_obj_owner, dictionary_obj_name, dictionary_obj_type; if (val = 0) then sql_text := 'select count(*) from dropped_xdb_instll_tab where owner = :1 and object_name = :2 and object_type = :3'; execute immediate sql_text into val using dictionary_obj_owner, dictionary_obj_name, dictionary_obj_type; if (val = 0) then insert into xdb_installation_tab values (dictionary_obj_owner, dictionary_obj_name, dictionary_obj_type); end if; end if; end if; else raise_application_error(-20000, 'Trigger xdb_installation_trigger does not support object creation of type '||dictionary_obj_type); end if; end if; end; / Rem Check for pre-conditions @@catxdbck &xdb_pass &res_tbs &temp_tbs :usesecfiles Rem Create XDB User. create user xdb identified by &xdb_pass account lock password expire default tablespace &res_tbs temporary tablespace &temp_tbs; Rem Invoke Registry. The package is defined later. EXECUTE dbms_registry.loading('XDB', 'Oracle XML Database', 'DBMS_REGXDB.VALIDATEXDB', 'XDB'); Rem Set parent rowid''s for XDB$H_LINK. Used only by upgrade. Rem should go away soon create user anonymous identified by values 'anonymous' default tablespace &res_tbs; EXECUTE dbms_registry.loading('XDB', 'Oracle XML Database', 'DBMS_REGXDB.VALIDATEXDB', 'XDB', dbms_registry.schema_list_t('ANONYMOUS')); grant create session to anonymous; alter user anonymous account lock; grant resource to xdb; grant create session to xdb; grant alter session to xdb; GRANT execute ON dbms_rls TO xdb; grant unlimited tablespace to xdb; grant create library to xdb; grant create public synonym to xdb; grant drop public synonym to xdb; GRANT SELECT ON user$ TO xdb; grant execute on dbms_streams_control_adm to xdb; CREATE role xdbadmin; GRANT xdbadmin TO dba; -- Pseudo user that can be used in ACLs to refer to the resource owner -- create user owner identified by values 'OWNER'; -- Needed for prvtxdbz grant administer database trigger to xdb; -- Needed for catxdbj GRANT javauserpriv TO xdb; -- Needed by catxdbr and catxdbpi grant create view to xdb; grant query rewrite to xdb; grant create operator to xdb; grant create indextype to xdb; -- Needed by catxdbpi -- This is needed because we are selecting from the hierarchially enabled -- table (as a part of the path index trigger) while dropping/truncating it. /* REF CASCADE IS DISSALLOWED FOR SCHEMA AND RESOURCE TABLES */ /* turn off the REF cascade semantics for resource$ */ alter session set events '22830 trace name context forever, level 4'; -- XDB$ROOT_INFO table create table xdb.xdb$root_info (resource_root rowid, rclist raw(2000), ftp_port number(5), ftp_protocol varchar2(4000), http_port number(5), http_protocol varchar2(4000), http_host varchar2(4000), http2_port number(5), http2_protocol varchar2(4000), http2_host varchar2(4000), nfs_port number(5), nfs_protocol varchar2(4000) ); -- XDB$XDB_READY table create table xdb.xdb$xdb_ready (data CLOB ); -- XDB$RCLIST view create view xdb.xdb$rclist_v as (select rclist from xdb.xdb$root_info); -- This is needed for users to be able to query the repository's rclist grant select on xdb.xdb$rclist_v to public; -- XDB$H_INDEX table -- Note: chidren column cannot be securefiles for now variable param_secf varchar2(4000); select value from v$parameter where name='db_securefile'; begin select value into :param_secf from v$parameter where UPPER(name)='DB_SECUREFILE'; end; / alter system set db_securefile='NEVER'; create table xdb.xdb$h_index ( oid raw(16), acl_id raw(16), owner_id raw(16), flags raw(4), children BLOB ) pctfree 99 pctused 1; -- revert declare s varchar2(4100); begin s := 'alter system set db_securefile=''' || :param_secf || ''' '; execute immediate s; end; / CREATE INDEX xdb.xdb$h_index_oid_i ON xdb.xdb$h_index (OID); create type xdb.xdb$link_t OID '00000000000000000000000000020151' AS OBJECT ( parent_oid raw(16), child_oid raw(16), name varchar2(256), flags raw(4), link_sn raw(16), child_acloid raw(16), child_ownerid raw(16), parent_rids raw(2000) ); / create table xdb.xdb$h_link of xdb.xdb$link_t ( constraint xdb_pk_h_link primary key (parent_oid, name) ); create index xdb.xdb_h_link_child_oid on xdb.xdb$h_link(child_oid); -- create document links table create table xdb.xdb$d_link ( source_id raw(16), target_id raw(16), target_path varchar2(4000), flags raw(8) ); create index xdb.xdb$d_link_source_id on xdb.xdb$d_link(source_id); create index xdb.xdb$d_link_target_id on xdb.xdb$d_link(target_id); ------------------------------------------------------------------------------ create sequence xdb.stateid_restart_sequence increment by 1 start with 1 minvalue 1 nocycle / create sequence xdb.clientid_sequence increment by 1 start with 1 minvalue 1 cache 10 nocycle / Rem Create XML schema related types and tables @@catxdbs.sql Rem Add XDB schema for schemas @@catxdbdt.sql Rem Create XML resource schema related types and tables @@catxdbrs.sql :usesecfiles Rem Add XDB schema for resources @@catxdbdr.sql /* turn off the ref cascade event */ alter session set events '22830 trace name context off'; Rem Add the schema registration/compilation module @@dbmsxsch.sql Rem Add the security module @@dbmsxdbz.sql @@dbmsxmlu.sql @@dbmsxmls.sql @@dbmsxmld.sql @@dbmsxres.sql Rem Add definition for various xdb utilities @@dbmsxdb.sql Rem Add definition for various xdb administrative utilities @@dbmsxdba.sql Rem Create Path Index @@catxdbpi -- Load the dbms_xdbutil_int specification @@prvtxdb0.plb -- Load the dbms_xdbz0 specification @@prvtxdz0.plb Rem Implementation of XDB Security modules @@prvtxdbz.plb REM ADD pl/sql dom, xml parser, AND xsl processor @@dbmsxmlp.sql @@dbmsxslp.sql @@prvtxmlstreams.plb @@prvtxmld.plb @@prvtxmlp.plb @@prvtxslp.plb Rem Implementation of DBMS_XDBResource @@prvtxres.plb Rem Implementation of XDB Utilities Package @@prvtxdb.plb Rem Implementation of XDB Admin Package @@prvtxdba.plb Rem Create the Compact XML Token Manager tables @@catxdbtm.sql Rem Create tables for Application users and roles @@catxdbapp.sql Rem Implementation of schema registration/compilation module @@prvtxsch.plb Rem Resource View @@prvtxdr0.plb @@catxdbr.sql Rem Resource view implementaion @@prvtxdbr.plb -- set xdk schema cache event ALTER SESSION SET EVENTS='31150 trace name context forever, level 0x8000'; Rem add xdb_dltrig_pkg - pre-update trigger to invoke document link proc @@prvtxdbdl.plb Rem XDB Path Index Implementation @@prvtxdbp.plb Rem Initialize bootstrap acl @@catxdbz.sql Rem Initialize ResConfig @@catxev Rem Initialize XDB standard packages (Configuration, Servlets, etc.) @@catxdbst.sql Rem Create the Versioning Package @@catxdbvr.sql Rem Create Path View @@catxdbpv Rem Initialize document links support @@catxdbdl.sql Rem Create helper package for xml index @@dbmsxidx Rem Load body of xmlindex helper package (dbms_xmlindex) @@prvtxidx.plb Rem Create the XMLIndex @@catxidx Rem Create the Structured XMLIndex tables @@catxtbix Rem Initialize extensible optimizer @@catxdbeo.sql Rem Initialize XDB configuration management @@catxdbc1 @@catxdbc2 Rem Setup XDB Digest Authentication @@xdbinstd.sql Rem Create Embedded PL/SQL Gateway package and schema objects @@dbmsepg @@prvtepg.plb @@catepg Rem Add the various views to be created on xdb data @@catxdbv Rem Create the DBMS_RESCONFIG package @@dbmsxrc @@prvtxrc.plb Rem Create the DBMS_XEVENT package @@dbmsxev @@prvtxev.plb Rem Create the dbms_xmltranslations package @@dbmsxtr @@prvtxtr.plb -- XDB$REPOS table CREATE TABLE XDB.XDB$REPOS ( obj# NUMBER NOT NULL, flags NUMBER, rootinfo# NUMBER, hindex# NUMBER, hlink# NUMBER, resource# NUMBER, acl# NUMBER, config# NUMBER, dlink# NUMBER, nlocks# NUMBER, stats# NUMBER, checkouts# NUMBER, resconfig# NUMBER, wkspc# NUMBER, vershist# NUMBER, params XMLType ); -- XDB$MOUNTS table CREATE TABLE XDB.XDB$MOUNTS ( dobj# NUMBER, dpath VARCHAR2(4000), sobj# NUMBER, spath VARCHAR2(4000), flags NUMBER ); Rem Create the DBMS_XDBREPOS package @@dbmsxdbrepos @@prvtxdbrepos.plb Rem Create helper package for text index on xdb resource data COLUMN xdb_name NEW_VALUE xdb_file NOPRINT; SELECT dbms_registry.script('CONTEXT','@dbmsxdbt.sql') AS xdb_name FROM DUAL; @&xdb_file Rem Add XML schema definitions for LCRs @@catxlcr Rem Indicate that xdb has been Loaded begin dbms_registry.loaded('XDB', dbms_registry.release_version, 'Oracle XML Database Version ' || dbms_registry.release_version || ' - ' || dbms_registry.release_status); end; / Rem Create a schedule for cleanup of expired nfs clients job Rem Disabling the job for 11gR1. It needs to be reenabled Rem explicitly by customers, or enabled automatically by NFS Rem server in 11gR2. Rem dbms_scheduler.enable('nfsclient_cleanup_job'); DECLARE c number; BEGIN select count(*) into c from ALL_SCHEDULER_JOB_CLASSES where JOB_CLASS_NAME = 'XMLDB_NFS_JOBCLASS'; if c = 0 then dbms_scheduler.create_job_class( job_class_name => 'SYS.XMLDB_NFS_JOBCLASS', logging_level => DBMS_SCHEDULER.LOGGING_FAILED_RUNS); end if; select count(*) into c from ALL_SCHEDULER_JOBS where JOB_NAME = 'XMLDB_NFS_CLEANUP_JOB'; if c = 0 then dbms_scheduler.create_job( job_name => 'SYS.XMLDB_NFS_CLEANUP_JOB' , job_type=>'STORED_PROCEDURE', job_action=>'xdb.dbms_xdbutil_int.cleanup_expired_nfsclients', job_class=>'SYS.XMLDB_NFS_JOBCLASS', repeat_interval=>'Freq=minutely;interval=5'); end if; execute immediate 'delete from noexp$ where name = :1' using 'XMLDB_NFS_JOBCLASS'; execute immediate 'insert into noexp$ (owner, name, obj_type) values(:1, :2, :3)' using 'SYS', 'XMLDB_NFS_JOBCLASS', '68'; end; / Rem Create the registry package and the validation procedure @@dbmsxreg grant execute on dbms_registry to xdb; @@prvtxreg.plb Rem Part 2 of ACL setup @@prvtxdz2.plb revoke administer database trigger from xdb; set serveroutput on set long 10000 Rem at catqm.sql, Invoke Validation for registry. execute sys.dbms_regxdb.validatexdb; Rem Show that no upgrade is needed to bring XDB to a valid 10.1 state. BEGIN IF dbms_registry.status('XDB') = 'VALID' THEN execute immediate 'create table xdb.migr9202status (n integer)'; execute immediate 'insert into xdb.migr9202status values (1000)'; END IF; END; / Rem drop objects created to track object creation during XDB Rem installation drop trigger sys.xdb_installation_trigger; drop trigger sys.dropped_xdb_instll_trigger; drop table dropped_xdb_instll_tab; drop package xdb.xdb$bootstrap; drop package xdb.xdb$bootstrapres; drop function xdb.xdb$getPickledNS; commit; prompt XML DB Installation completed prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt prompt prompt Triggering Extensible Security (XS) Installation ... prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Always keep this at the end. XS Rem Initialize bootstrap extensible security @@catzxs.sql -- Add XS$NULL to XDB schema list BEGIN dbms_registry.update_schema_list('XDB', dbms_registry.schema_list_t('ANONYMOUS', 'XS$NULL')); END; / -- Explicit grants to DBA,System; "any" privileges are no more applicable for -- XDB tables. Listing these specifically since there are certain tables -- for which we dont grant full access by default even to DBA & System. -- (eg, purely-dictionary tables like XDB$SCHEMA, XDB$TTSET etc.) grant all on XDB.XDB$RESOURCE to dba; grant all on XDB.XDB$RESOURCE to system with grant option; grant all on XDB.XDB$H_INDEX to dba; grant all on XDB.XDB$H_INDEX to system with grant option; grant all on XDB.XDB$H_LINK to dba; grant all on XDB.XDB$H_LINK to system with grant option; grant all on XDB.XDB$D_LINK to dba; grant all on XDB.XDB$D_LINK to system with grant option; grant all on XDB.XDB$NLOCKS to dba; grant all on XDB.XDB$NLOCKS to system with grant option; grant all on XDB.XDB$WORKSPACE to dba; grant all on XDB.XDB$WORKSPACE to system with grant option; grant all on XDB.XDB$CHECKOUTS to dba; grant all on XDB.XDB$CHECKOUTS to system with grant option; grant all on XDB.XDB$ACL to dba; grant all on XDB.XDB$ACL to system with grant option; grant all on XDB.XDB$CONFIG to dba; grant all on XDB.XDB$CONFIG to system with grant option; grant all on XDB.XDB$RESCONFIG to dba; grant all on XDB.XDB$RESCONFIG to system with grant option; grant all on XDB.XS$DATA_SECURITY to dba; grant all on XDB.XS$DATA_SECURITY to system with grant option; grant all on XDB.XS$PRINCIPALS to dba; grant all on XDB.XS$PRINCIPALS to system with grant option; grant all on XDB.XS$ROLESETS to dba; grant all on XDB.XS$ROLESETS to system with grant option; grant all on XDB.XS$SECURITYCLASS to dba; grant all on XDB.XS$SECURITYCLASS to system with grant option; declare suf varchar2(26); stmt varchar2(2000); begin select toksuf into suf from xdb.xdb$ttset where flags = 0; stmt := 'grant all on XDB.X$PT' || suf || ' to DBA'; execute immediate stmt; stmt := 'grant all on XDB.X$PT' || suf || ' to SYSTEM WITH GRANT OPTION'; execute immediate stmt; end; / prompt Extensible Security (XS) Installation completed prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt prompt Extensible Security (XS) Installation completed prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt Rem Data Pump has the new requirement that users granted Rem DATAPUMP_FULL_EXP_DATABASE be able to export in FULL mode Rem tables in the XDB schema. The advise is actually to grant Rem SELECT on XDB tables to the SELECT_CATALOG_ROLE, which in Rem turn is granted to DATAPUMP_FULL_EXP_DATABASE, to be in sync Rem with other components to be supported by FULL export. Rem Some XDB tables are actually allowing PUBLIC access, so this Rem grant will be a noop for them, but some do not. Rem If other XDB scripts are run beyond this point (outside of catqm.sql), Rem it is the responsability of the script developer to allow similar Rem grants on any XDB-owned tables that may get created in the script. Rem prompt Granting SELECT on XDB tables to SELECT_CATALOG_ROLE prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ set serveroutput on declare type cur_type is ref cursor; cur cur_type; tname varchar2(100); stmt varchar2(2000); begin open cur for 'select table_name from dba_tables where owner=:1 union ' || 'select table_name from dba_xml_tables where owner=:2 union ' || 'select table_name from dba_object_tables where owner=:3 ' using 'XDB', 'XDB', 'XDB'; loop fetch cur into tname; exit when cur%NOTFOUND; tname := 'XDB."' || tname || '"'; stmt := 'grant select on ' || tname || ' to SELECT_CATALOG_ROLE'; begin execute immediate stmt; exception when OTHERS then if ((SQLCODE != -22812) and (SQLCODE != -30967)) then dbms_output.put_line(stmt); dbms_output.put_line(SQLERRM); end if; end; end loop; end; / set serveroutput off