rem 
rem $Header: dbmsutil.sql 15-oct-98.19:15:24 rsujitha Exp $ 
rem 
Rem  Copyright (c) 1991, 1995, 1996, 1997, 1998, 1999 by Oracle Corporation 
Rem    NAME
Rem      dbmsutil.sql - packages of various utility procedures
Rem    DESCRIPTION
Rem      This file contains various packages:
Rem         dbms_transaction	  - transaction commands
Rem         dbms_session	  - alter session commands
Rem         dbms_ddl		  - ddl commands
Rem         dbms_utility	  - helpful utilities
Rem         dbms_application_info - application information registration
Rem         dbms_system           - database system level commands (moved to
Rem                                 prvtutil.sql for more obscurity)
Rem         dbms_space            - segment space analysis
Rem         dbms_rowid            - rowid creation and interpretation
Rem         dbms_pclxutil         - intra-partition parallelism for creating 
Rem                                 partition-wise local index.
Rem    RETURNS
Rem 
Rem    NOTES
Rem      The procedural option is needed to use these facilities.
Rem
Rem      All of the packages below run with the privileges of calling user,
Rem      rather than the package owner ('sys').
Rem
Rem      Procedure 'dbms_ddl.alter_table_referenceable',
Rem      'dbms_ddl.alter_table_not_referenceable',
Rem      'dbms_ddl.alter_compile' and 'dbms_ddl.analyze_object'
Rem      commit the current transaction, perform the operation, and 
Rem      then commit again.
Rem 
Rem      The dbms_utility package is run-as-caller (psdicd.c) only for
Rem      its name_resolve, compile_schema and analyze_schema
Rem      procedures.  This package is not run-as-caller
Rem      w.r.t. SQL (psdpgi.c) so that the SQL works correctly (runs as
Rem      SYS).  The privileges are checked via dbms_ddl.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem     ssamu      01/04/99 -  fix comment
Rem     akalra     10/30/98  - add parameter to switch_current_consumer_group  
Rem     rsujitha   10/15/98 -  Add dbms_pclxutil package
Rem     mramache   07/27/98 -  update free_unused_user_memory description
Rem     akalra     06/01/98  - Add switch_current_consumer_group for Res. Mgr.
Rem     bnnguyen   06/01/98 -  bug617734
Rem     kquinn     01/02/98 -  603979: Improve analyze_object validation
Rem     ansriniv   04/13/98 -  keep functionality for types
Rem     dmwong     04/02/98  - add set_context and list_context for app ctx    
Rem     nlewis     01/15/98 -  remove mlslabels
Rem     jingliu    12/08/97  - add procedures for job affinity                 
Rem     bnnguyen   09/04/97 -  Fix for bug512637
Rem     amozes     09/09/97 -  allow analyze of a single partition
Rem     najain     07/25/97 -  505485: compiling triggers in compile_schema
Rem     rherwadk   06/28/97 -  document get_parameter_value() ICD
Rem     asgoel     11/20/96 -  Add another plsql table for order_user_objects
Rem     rherwadk   11/07/96 -  move query parameter by name icd to dbms_utility
Rem     ssamu      08/21/96 -  change analyze_part_object
Rem     sgsmith    08/21/96 -  add analyze_part_object
Rem     mmonajje   09/16/96 -  Fixing bug 244014; Adding RESTRICT_REFERENCES pr
Rem     jwijaya    08/16/96 -  add dbms_ddl.alter_table_referenceable
Rem     atsukerm   08/02/96 -  change DBMS_ROWID for DBA unification.
Rem     atsukerm   05/20/96 -  add exceptions to DBMS_ROWID package.
Rem     asgoel     07/22/96 -  Added index_table_type table
Rem     ajasuja    04/25/96 -  merge
Rem     boki       04/22/96 -  merge OBJ into BIG
Rem     gpongrac   01/31/96 -  move dbms_application_info to separate file
Rem     atsukerm   03/07/96 -  add ROWID migration function to DBMS_ROWID.
Rem     atsukerm   02/29/96 -  space support for partitions.
Rem     boki       03/27/96 -  merge OBJ_960326
Rem     boki       03/13/96 -  merge from big 0228
Rem     hasun      01/29/96 -  Add procedure db_version()
Rem     ssamu      12/21/95 -  fix another typo
Rem     ssamu      12/21/95 -  fix typo
Rem     sjain      12/20/95 -  Move dbms_system to prvtutil.
Rem     atsukerm   11/15/95 -  new ROWID format - restricted/extended only
Rem     atsukerm   10/24/95 -  new ROWID format - add DBMS_ROWID package.
Rem     ssamu      10/19/95 -  add pragma for data_block_address_file
Rem     rtaranto   08/04/95 -  merge changes from branch 1.50.720.2
Rem     rtaranto   07/12/95 -  Add dbms_utility.get_hash_value
Rem     hrizvi     04/03/95 -  merge changes from branch 1.50.725.2
Rem     jarnett    01/03/95 -  add procedure purge_lost_db_entry
Rem     bhirano    12/23/94 -  merge changes from branch 1.50.720.1
Rem     hjakobss   10/12/94 -  analyze_schema support for histograms
Rem     hrizvi     02/03/95 -  add dist_txn_sync to dbms_system
Rem     bhirano    12/23/94 -  merge changes from branch 1.41.710.6
Rem     jloaiza    09/06/94 -  dbms_registration -> dbms_application_info
Rem     atsukerm   06/20/94 -  adding DBMS_SPACE package
Rem     jloaiza    06/08/94 -  change name to dbms_registration
Rem     jloaiza    04/07/94 -  add dbms_application
Rem     dsdaniel   04/07/94 -  merge changes from branch 1.41.710.4
Rem     wmaimone   04/07/94 -  merge changes from branch 1.41.710.5
Rem     adowning   03/29/94 -  merge changes from branch 1.41.710.3
Rem     wmaimone   02/07/94 -  add set close_cached_open_cursors to dbms_sessio
Rem     dsdaniel   02/04/94 -  dbms_util.port_string icd
Rem     adowning   02/02/94 -  split file into public / private binary files
Rem     rjenkins   11/17/93 -  merge changes from branch 1.41.710.2
Rem     rjenkins   10/20/93 -  merge changes from branch 1.41.710.1
Rem     rjenkins   10/28/93 -  make comma_to_table more consistent
Rem     rjenkins   10/12/93 -  adding comma_to_table
Rem     rjenkins   09/03/93 -  adding name_parse
Rem     hjakobss   07/15/93 -  bug 170473
Rem     hjakobss   07/13/93 -  bug 169577
Rem     dsdaniel   03/12/93 -  local_tid, step_id functions for replication  
Rem     mmoore     01/11/93 -  merge changes from branch 1.37.312.1 
Rem     mmoore     01/05/93 - #(145287) add another exception for discrete mode
Rem     mmoore     12/11/92 -  disable set_role in stored procs 
Rem     rkooi      11/24/92 -  fixes per Peter 
Rem     rkooi      11/21/92 -  get rid of error argument to name_resolve 
Rem     tpystyne   11/20/92 -  fix compile_all and analyze_schema 
Rem     rkooi      11/16/92 -  fix set_label 
Rem     rkooi      11/16/92 -  fix comments 
Rem     rkooi      11/13/92 -  add name_res procedure 
Rem     tpystyne   11/07/92 -  make analyze parameters optional 
Rem     mmoore     11/04/92 -  add new analyze options 
Rem     ghallmar   11/03/92 -  add dbms_transaction.purge_mixed 
Rem     rkooi      10/30/92 -  get rid of caller_id and unique_stmt_id 
Rem     rkooi      10/26/92 -  owner -> schema for SQL2 
Rem     rkooi      10/25/92 -  bug 135880 
Rem     mmoore     10/13/92 - #(131686) change messages 2074,4092,0034 
Rem     rkooi      10/02/92 -  compile_all fix 
Rem     mmoore     10/02/92 -  change pls_integer to binary_integer 
Rem     tpystyne   10/01/92 -  fix Bob's mistakes 
Rem     tpystyne   09/28/92 -  disallow commit/rollback force in rpc and trigge
Rem     mmoore     09/25/92 - #(130566) don't allow set_nls or set_role in trig
Rem     tpystyne   09/23/92 -  rename analyze to analyze_object 
Rem     rkooi      08/24/92 -  handle delimited id's in alter_compile 
Rem     tpystyne   08/06/92 -  add analyze_schema 
Rem     epeeler    07/29/92 -  add function to get time 
Rem     rkooi      06/25/92 -  workaround pl/sql bug with 'in' in SQL
Rem     rkooi      06/03/92 -  add 'get unique session id' 
Rem     jcohen     05/28/92 -  add = to alter session set label 
Rem     jloaiza    05/12/92 -  add discrete 
Rem     rkooi      04/22/92 -  put in checks for execute_sql for triggs, stored
Rem     mmoore     04/14/92 -  move begin_oltp to package transaction 
Rem     rkooi      04/06/92 -  merge changes from branch 1.4.300.1 
Rem     rkooi      04/01/92 -  Creation - split/recombined from other files
Rem     mroberts   02/21/92 -  call alter_compile, not sql_ddl 
Rem     rkooi      02/06/92 -  testing 
Rem     rkooi      02/03/92 -  compilation errors 
Rem     rkooi      01/16/92 -  Creation 

REM ********************************************************************
REM THESE PACKAGES MUST NOT BE MODIFIED BY THE CUSTOMER.  DOING SO
REM COULD CAUSE INTERNAL ERRORS AND SECURITY VIOLATIONS IN THE
REM RDBMS.  SPECIFICALLY, THE PSD* AND EXECUTE_SQL ROUTINES MUST NOT BE
REM CALLED DIRECTLY BY ANY CLIENT AND MUST REMAIN PRIVATE TO THE PACKAGE BODY.
REM ********************************************************************

create or replace package dbms_transaction is

  ------------
  --  OVERVIEW
  --
  --  This package provides access to SQL transaction statements from
  --  stored procedures.
  --  It also provids functions for monitoring transaction activities
  --  (transaction ids and ordering of steps of transactions )

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure read_only;
  --  Equivalent to SQL "SET TRANSACTION READ ONLY"
  procedure read_write;
  --  Equivalent to SQL "SET TRANSACTION READ WRITE"
  procedure advise_rollback;
  --  Equivalent to SQL "ALTER SESSION ADVISE ROLLBACK"
  procedure advise_nothing;
  --  Equivalent to SQL "ALTER SESSION ADVISE NOTHING"
  procedure advise_commit;
  --  Equivalent to SQL "ALTER SESSION ADVISE COMMIT"
  procedure use_rollback_segment(rb_name varchar2);
  --  Equivalent to SQL "SET TRANSACTION USE ROLLBACK SEGMENT <rb_seg_name>"
  --  Input arguments:
  --    rb_name
  --      Name of rollback segment to use.
  procedure commit_comment(cmnt varchar2);
  --  Equivalent to SQL "COMMIT COMMENT <text>"
  --  Input arguments:
  --    cmnt
  --      Comment to assoicate with this comment.
  procedure commit_force(xid varchar2, scn varchar2 default null);
  --  Equivalent to SQL "COMMIT FORCE <text>, <number>"
  --  Input arguments:
  --    xid
  --      Local or global transaction id.
  --    scn
  --      System change number.
  procedure commit;
    pragma interface (C, commit);                          -- 1 (see psdicd.c)
  --  Equivalent to SQL "COMMIT".  Here for completeness.  This is
  --    already implemented as part of PL/SQL.
  procedure savepoint(savept varchar2);
    pragma interface (C, savepoint);			   -- 2 (see psdicd.c)
  --  Equivalent to SQL "SAVEPOINT <savepoint_name>".  Here for
  --    completeness. This is already implemented as part of PL/SQL.
  --  Input arguments:
  --    savept
  --      Savepoint identifier.
  procedure rollback;
    pragma interface (C, rollback);			   -- 3 (see psdicd.c)
  --  Equivalent to SQL "ROLLBACK".  Here for completeness. This is 
  --    already implemented as part of PL/SQL.
  procedure rollback_savepoint(savept varchar2);
    pragma interface (C, rollback_savepoint);		   -- 4 (see psdicd.c)
  --  Equivalent to SQL "ROLLBACK TO SAVEPOINT <savepoint_name>".  Here for
  --    completeness. This is already implemented as part of PL/SQL.
  --  Input arguments:
  --    savept
  --      Savepoint identifier.
  procedure rollback_force(xid varchar2);
  --  Equivalent to SQL "ROLLBACK FORCE <text>"
  --  Input arguments:
  --    xid
  --      Local or global transaction id.
  procedure begin_discrete_transaction;
    pragma interface (C, begin_discrete_transaction);      -- 5 (see psdicd.c)
  --  Set "discrete transaction mode" for this transaction.
  --  Exceptions:
  --    ORA-08175 will be generated if a transaction attempts an operation 
  --      which cannot be performed as a discrete transaction.  If this 
  --      exception is encountered, rollback and retry the transaction.

  --    ORA-08176 will be generated if a transaction encounters data changed 
  --      by an operation that does not generate rollback data : create index,
  --      direct load or discrete transaction.  If this exception is
  --      encountered, retry the operation that received the exception.
  --    
  DISCRETE_TRANSACTION_FAILED exception;
    pragma exception_init(DISCRETE_TRANSACTION_FAILED, -8175);

  CONSISTENT_READ_FAILURE exception;
    pragma exception_init(CONSISTENT_READ_FAILURE, -8176);

  procedure purge_mixed(xid varchar2);
  --  When indoubt transactions are forced to commit or rollback (instead of
  --    letting automatic recovery resolve their outcomes), there is a
  --    possibility that a transaction can have a mixed outcome: some sites
  --    commit, and others rollback.  Such inconsistency cannot be resolved
  --    automatically by ORACLE; however, ORACLE will flag entries in
  --    DBA_2PC_PENDING by setting the MIXED column to a value of 'yes'.
  --    ORACLE will never automatically delete information about a mixed
  --    outcome transaction.  When the application or DBA is sure all
  --    inconsistencies that might have arisen as a result of the mixed
  --    transaction have been resolved, this procedure can be used to
  --    delete the information about a given mixed outcome transaction.
  --  Input arguments:
  --    xid
  --      This must be set to the value of the LOCAL_TRAN_ID column in 
  --      the DBA_2PC_PENDING table.

  procedure purge_lost_db_entry(xid varchar2);
  --  When a failure occurs during commit processing, automatic recovery will
  --    consistently resolve the results at all sites involved in the 
  --    transaction.  However, if the remote database is destroyed or 
  --    recreated before recovery completes, then the entries used to 
  --    control recovery in DBA_2PC_PENDING and associated tables will never
  --    be removed, and recovery will periodically retry.  Procedure 
  --    purge_lost_db_entry allows removal of such transactions from the 
  --    local site.

  --  WARNING: purge_lost_db_entry should ONLY be used when the other
  --  database is lost or has been recreated.  Any other use may leave the
  --  other database in an unrecoverable or inconsistent state.

  --    Before automatic recovery runs, the transaction may show 
  --    up in DBA_2PC_PENDING as state "collecting", "committed", or
  --    "prepared".  If the DBA has forced an in-doubt transaction to have
  --    a particular result by using "commit force" or "rollback force",
  --    then states "forced commit" or "forced rollback" may also appear.  
  --    Automatic recovery will normally delete entries in any of these 
  --    states.  The only exception is when recovery finds a forced
  --    transaction which is in a state inconsistent with other sites in the 
  --    transaction;  in this case, the entry will be left in the table
  --    and the MIXED column will have a value 'yes'.

  --    However, under certain conditions, it may not be possible for 
  --    automatic recovery to run.  For example, a remote database may have 
  --    been permanently lost.  Even if it is recreated, it will get a new 
  --    database id, so that recovery cannot identify it (a possible symptom 
  --    is ORA-02062).  In this case, the DBA may use the procedure 
  --    purge_lost_db_entry to clean up the entries in any state other 
  --    than "prepared".  The DBA does not need to be in any particular 
  --    hurry to resolve these entries, since they will not be holding any 
  --    database resources.
  
  --    The following table indicates what the various states indicate about
  --    the transaction and what the DBA actions should be:

  --    State       State of     State of     Normal Alternative
  --    Column      Global       Local        DBA    DBA 
  --                Transaction  Transaction  Action Action
  --    ----------  ------------ ------------ ------ ---------------
  --    collecting  rolled back  rolled back  none   purge_lost_db_entry (1)
  --    committed   committed    committed    none   purge_lost_db_entry (1)
  --    prepared    unknown      prepared     none   force commit or rollback
  --    forced      unknown      committed    none   purge_lost_db_entry (1)
  --      commit
  --    forced      unknown      rolled back  none   purge_lost_db_entry (1)
  --      rollback
  --    forced      mixed        committed    (2)
  --      commit
  --      (mixed)                              
  --    forced      mixed        rolled back  (2)
  --      rollback
  --      (mixed)                             
   
  --    Note 1: Use only if significant reconfiguration has occurred so that
  --      automatic recovery cannot resolve the transaction.  Examples are
  --      total loss of the remote database, reconfiguration in software
  --      resulting in loss of two-phase commit capability, or loss of 
  --      information from an external transaction coordinator such as a TP
  --      Monitor.
  --    Note 2: Examine and take any manual action to remove inconsistencies, 
  --      then use the procedure purge_mixed.
  --  Input arguments:
  --    xid
  --      This must be set to the value of the LOCAL_TRAN_ID column in
  --      the DBA_2PC_PENDING table.

  FUNCTION local_transaction_id(create_transaction BOOLEAN := FALSE)
    RETURN VARCHAR2;
  --  Return local (to instance) unique identfier for current transaction
  --  Return null if there is no current transction.
  --  Input parameters:
  --     create_transaction 
  --       If true , start a transaciton if one is not currently 
  --       active.
  --
  FUNCTION step_id RETURN NUMBER;
  --  Return local (to local transaction ) unique positive integer that orders
  --  The DML operations of a transaction.
  --  Input parmaeters:

end;
/
drop public synonym dbms_transaction
/
create public synonym dbms_transaction for sys.dbms_transaction
/
grant execute on dbms_transaction to public
/

create or replace package dbms_session is
  ------------
  --  OVERVIEW
  --
  --  This package provides access to SQL "alter session" statements, and
  --  other session information from, stored procedures.

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure set_role(role_cmd varchar2);
  --  Equivalent to SQL "SET ROLE ...".
  --  Input arguments:
  --    role_cmd
  --      This text is appended to "set role " and then executed as SQL.
  procedure set_sql_trace(sql_trace boolean);
  --  Equivalent to SQL "ALTER SESSION SET SQL_TRACE ..."
  --  Input arguments:
  --    sql_trace
  --      TRUE or FALSE.  Turns tracing on or off.
  procedure set_nls(param varchar2, value varchar2);
  --  Equivalent to SQL "ALTER SESSION SET <nls_parameter> = <value>"
  --  Input arguments:
  --    param
  --      The NLS parameter. The parameter name must begin with 'NLS'.
  --    value
  --      The value to set the parameter to.  If the parameter is a
  --      text literal then it will need embedded single-quotes.  For
  --      example "set_nls('nls_date_format','''DD-MON-YY''')"
  procedure close_database_link(dblink varchar2);
  --  Equivalent to SQL "ALTER SESSION CLOSE DATABASE LINK <name>"
  --  Input arguments:
  --    name
  --      The name of the database link to close.
  procedure reset_package;
  --  Deinstantiate all packages in this session.  In other words, free
  --    all package state.  This is the situation at the beginning of
  --    a session.
  function unique_session_id return varchar2;
  pragma restrict_references(unique_session_id,WNDS,RNDS,WNPS);
  --  Return an identifier that is unique for all sessions currently
  --    connected to this database.  Multiple calls to this function 
  --    during the same session will always return the same result.
  --  Output arguments:
  --    unique_session_id
  --      can return up to 24 bytes.
  function is_role_enabled(rolename varchar2) return boolean;
  --  Determine if the named role is enabled for this session.
  --  Input arguments:
  --    rolename
  --      Name of the role.
  --  Output arguments:
  --    is_role_enabled
  --      TRUE or FALSE depending on whether the role is enabled.
  function is_session_alive(uniqueid varchar2) return boolean;
  --  Determine if the specified session is alive.
  --  Input arguments:
  --    uniqueid
  --      Uniqueid of the session.
  --  Output arguments:
  --    is_session_alive
  --      TRUE or FALSE depending on whether the session is alive.
  procedure set_close_cached_open_cursors(close_cursors boolean);
  --  Equivalent to SQL "ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS ..."
  --  Input arguments:
  --    close_cursors
  --      TRUE or FALSE.  Turns close_cached_open_cursors on or off.
  procedure free_unused_user_memory;
  --  Procedure for users to reclaim unused memory after performing operations
  --  requiring large amounts of memory (where large is >100K).  Note that 
  --  this procedure should only be used in cases where memory is at a 
  --  premium.  
  --
  --  Examples operations using lots of memory are:
  -- 
  --     o  large sorts where entire sort_area_size is used and
  --        sort_area_size is hundreds of KB
  --     o  compiling large PL/SQL packages/procedures/functions
  --     o  storing hundreds of KB of data within PL/SQL indexed tables
  --
  --  One can monitor user memory by tracking the statistics 
  --  "session uga memory" and "session pga memory" in the 
  --  v$sesstat/v$statname fixed views.  Monitoring these statistics will
  --  also show how much memory this procedure has freed.
  --
  --  The behavior of this procedure depends upon the configuration of the 
  --  server operating on behalf of the client:
  --  
  --     o  dedicated server - returns unused PGA memory and session memory
  --          to the OS (session memory is allocated from the PGA in this 
  --          configuration)
  --     o  MTS server       - returns unused session memory to the
  --          shared_pool (session memory is allocated from the shared_pool
  --          in this configuration)
  --  
  --  In order to free memory using this procedure, the memory must 
  --  not be in use.  
  -- 
  --  Once an operation allocates memory, only the same type of operation can 
  --  reuse the allocated memory.  For example, once memory is allocated 
  --  for sort, even if the sort is complete and the memory is no longer 
  --  in use, only another sort can reuse the sort-allocated memory.  For
  --  both sort and compilation, after the operation is complete, the memory
  --  is no longer in use and the user can invoke this procedure to free the
  --  unused memory. 
  --
  --  An indexed table implicitly allocates memory to store values assigned
  --  to the indexed table's elements.  Thus, the more elements in an indexed 
  --  table, the more memory the RDBMS allocates to the indexed table.  As 
  --  long as there are elements within the indexed table, the memory
  --  associated with an indexed table is in use. 
  -- 
  --  The scope of indexed tables determines how long their memory is in use. 
  --  Indexed tables declared globally are indexed tables declared in packages
  --  or package bodies.  They allocate memory from session memory.  For an
  --  indexed table declared globally, the memory will remain in use
  --  for the lifetime of a user's login (lifetime of a user's session),
  --  and is freed after the user disconnects from ORACLE.
  --     
  --  Indexed tables declared locally are indexed tables declared within
  --  functions, procedures, or anonymous blocks.  These indexed tables
  --  allocate memory from PGA memory.  For an indexed table declared 
  --  locally, the memory will remain in use for as long as the user is still
  --  executing the procedure, function, or anonymous block in which the 
  --  indexed table is declared.  After the procedure, function, or anonymous
  --  block is finished executing, the memory is then available for other 
  --  locally declared indexed tables to use (i.e., the memory is no longer
  --  in use).
  --  
  --  Assigning an uninitialized, "empty," indexed table to an existing index
  --  table is a method to explicitly re-initialize the indexed table and the
  --  memory associated with the indexed table.  After this operation,
  --  the memory associated with the indexed table will no longer be in use, 
  --  making it available to be freed by calling this procedure.  This method
  --  is particularly useful on indexed tables declared globally which can grow
  --  during the lifetime of a user's session, as long as the user no 
  --  longer needs the contents of the indexed table.  
  --  
  --  The memory rules associated with an indexed table's scope still apply; 
  --  this method and this procedure, however, allow users to 
  --  intervene and to explictly free the memory associated with an
  --  indexed table. 
  -- 
  --  The PL/SQL fragment below illustrates the method and the use 
  --  of procedure free_unused_user_memory.
  --
  --  create package foobar
  --     type number_idx_tbl is table of number indexed by binary_integer;
  -- 
  --     store1_table  number_idx_tbl;     --  PL/SQL indexed table
  --     store2_table  number_idx_tbl;     --  PL/SQL indexed table
  --     store3_table  number_idx_tbl;     --  PL/SQL indexed table
  --     ...
  --  end;            --  end of foobar
  --
  --  declare
  --     ...
  --     empty_table   number_idx_tbl;     --  uninitialized ("empty") version
  --  
  --  begin
  --     for i in 1..1000000 loop
  --       store1_table(i) := i;           --  load data
  --     end loop;
  --     ...
  --     store1_table := empty_table;      --  "truncate" the indexed table
  --     ... 
  --     -
  --     dbms_session.free_unused_user_memory;  -- give memory back to system
  --  
  --     store1_table(1) := 100;           --  index tables still declared;
  --     store2_table(2) := 200;           --  but truncated.
  --     ...
  --  end;
  -- 
  --  Performance Implication: 
  --     This routine should be used infrequently and judiciously.
  --       
  --  Input arguments:
  --     n/a
  procedure set_context(namespace varchar2, attribute varchar2, value varchar2);
  --  Input arguments:
  --    namespace
  --      Name of the namespace to use for the application context
  --    attribute
  --      Name of the attribute to be set
  --    value
  --      Value to be set
  TYPE AppCtxRecTyp IS RECORD ( namespace varchar2(30), attribute varchar2(30), 
      value varchar2(4000));
  TYPE AppCtxTabTyp IS TABLE OF AppCtxRecTyp INDEX BY BINARY_INTEGER;
  procedure list_context(list OUT AppCtxTabTyp, lsize OUT number);
  --  Input arguments:
  --    list
  --      buffer to store a list of application context set in current
  --      session
  --  Output arguments:
  --    list
  --      contains a list of of (namespace,attribute,values) set in current
  --      session
  --    size
  --      returns the number of entries in the buffer returned
  procedure switch_current_consumer_group(new_consumer_group IN VARCHAR2,
                                          old_consumer_group OUT VARCHAR2,
					  initial_group_on_error IN BOOLEAN);
  -- Input arguments:
  -- new_consumer_group
  --    name of consumer group to switch to
  -- old_consumer_group
  --    name of the consumer group just switched out from
  -- initial_group_on_error
  --   If TRUE, sets the current consumer group of the invoker to his/her 
  --   initial consumer group in the event of an error.
  -- 
end;
/

drop public synonym dbms_session
/
create public synonym dbms_session for sys.dbms_session
/
grant execute on dbms_session to public
/

create or replace package dbms_ddl is
  ------------
  --  OVERVIEW
  --
  --  This package provides access to some SQL DDL statements from
  --  stored procedures.
  --  It also provides some special administration operations that are
  --  not available as DDLs.

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure alter_compile(type varchar2, schema varchar2, name varchar2);
  --  Equivalent to SQL "ALTER PROCEDURE|FUNCTION|PACKAGE [<schema>.]
  --    <name> COMPILE [BODY]".  If the named object is this package,
  --    or any packages upon which it depends (currently "standard" or 
  --    "dbms_standard") then the procedure simply returns (since these
  --    packages are clearly successfully compiled).
  --  Input arguments:
  --    type
  --      Must be one of "PROCEDURE", "FUNCTION", "PACKAGE", "PACKAGE
  --      BODY" or "TRIGGER".
  --    schema
  --      The schema name.  If NULL then use current schema.  Case sensitive.
  --    name
  --      The name of the object.  Case sensitive.
  --  Exceptions:
  --    ORA-20000: Insufficient privileges or object does not exist.
  --    ORA-20001: Remote object, cannot compile.
  --    ORA-20002: Bad value for object type.  Should be one of PACKAGE,
  --      PACKAGE BODY, PROCEDURE, FUNCTION, or TRIGGER.
  procedure analyze_object
    (type varchar2, schema varchar2, name varchar2, method varchar2,
     estimate_rows number default null, 
     estimate_percent number default null, method_opt varchar2 default null,
     partname varchar2 default null);
  --  Equivalent to SQL "ANALYZE TABLE|CLUSTER|INDEX [<schema>.]<name>
  --    [<method>] STATISTICS [SAMPLE <n> [ROWS|PERCENT]]"
  --  Input arguments:
  --    type
  --      One of 'TABLE', 'CLUSTER' or 'INDEX'.  If none of these, the
  --      procedure just returns.
  --    schema
  --      schema of object to analyze.  NULL means current schema.  Case
  --      sensitive.
  --    name
  --      name of object to analyze.  Case sensitive.
  --    method
  --      One of 'ESTIMATE', 'COMPUTE' or 'DELETE'. If 'ESTIMATE' then either 
  --      estimate_rows or estimate_percent must be non-zero.
  --    estimate_rows
  --      Number of rows to estimate
  --    estimate_percent
  --      Percentage of rows to estimate.  If estimate_rows is specified
  --      than ignore this parameter.
  --    method_opt
  --      method options of the following format
  --      [ FOR TABLE ]
  --      [ FOR ALL [INDEXED] COLUMNS] [SIZE n]
  --      [ FOR ALL INDEXES ]
  --    partname
  --      specific partition to be analyzed.
  --  Exceptions:
  --    ORA-20000: Insufficient privileges or object does not exist.
  --    ORA-20001: Bad value for object type.  Should be one of TABLE, INDEX
  --      or CLUSTER.
  --    ORA-20002: METHOD must be one of COMPUTE,ESTIMATE or DELETE
  procedure alter_table_referenceable
    (table_name varchar2, table_schema varchar2 default null,
     affected_schema varchar2 default null);
  --  Alter the given object table table_schema.table_name so it becomes the
  --  referenceable table for the given schema affected_schema.
  --  This is equivalent to SQL "ALTER TABLE [<table_schema>.]<table_name>
  --  REFERENCEABLE FOR <affected_schema>" which is currently not supported or
  --  available as a DDL statement.
  --
  --  When you create an object table, it automatically becomes referenceable,
  --  unless you use the OID AS clause when creating the table.
  --  The OID AS clause allows you to create an object table and to assign
  --  to the new table the same EOID as another object table of the same type.
  --  After you create a new table using the OID AS clause, you end up with
  --  two object table with the same EOID; the new table is not referenceable,
  --  the original one is.  All references that used to point to the objects
  --  in the original table still reference the same objects in the same
  --  original table.
  --
  --  If you execute this procedure on the new table, it will make the new
  --  table the referenceable table replacing the original one; thus, those
  --  references now point to the objects in the new table instead of the
  --  original table.
  --
  --  For example, the following steps recreate an object table that needs
  --  to be reorganized for some reasons:
  --    CREATE TABLE EMP_NEW OF EMPLOYEE OID AS EMP;
  --    INSERT INTO EMP_NEW (SYS_NC_OID$, EMP_NEW)
  --      SELECT SYS_NC_OID$, EMP FROM EMP;
  --    EXECUTE DBMS_DDL.ALTER_TABLE_REFERENCEABLE('EMP_NEW');
  --      -- table_schema defaults to NULL, thus use the current schema, and
  --      -- affected_schema defaults to NULL, thus use PUBLIC, which means
  --      -- all schemas will be affected
  --    RENAME EMP TO EMP_OLD;
  --    RENAME EMP_NEW TO EMP;
  --
  --  The affected schema can be PUBLIC or a particular schema.  If it is
  --  PUBLIC, all schemas are affected.  If it is a particular schema, only
  --  that schema is affected.
  --
  --  The user that executes this procedure must own the new table (i.e.,
  --  the schema is the same as the user), and the affected schema must be the
  --  same as the user or PUBLIC. If the affected schema is PUBLIC, then the
  --  user must own the old mapping table for PUBLIC as well.
  --
  --  If the user executing this procedure has ALTER ANY TABLE and SELECT ANY
  --  TABLE and DROP ANY TABLE privileges, the user doesn't have to own the
  --  tables, and the affected schema can be any valid schema or PUBLIC.
  --
  --  Input arguments:
  --    table_name
  --      The name of the table to be altered.  Cannot be a synonym.
  --      Must not be NULL.  Case sensitive.
  --    table_schema
  --      The name of the schema owning the table to be altered.
  --      If NULL then the current schema is used.  Case sensitive.
  --    affected_schema
  --      The name of the schema affected by this alteration.
  --      If NULL then PUBLIC is used.  Case sensitive.
  --  Exceptions:
  --    ORA-20000: insufficient privileges, invalid schema name
  --               or table does not exist,
  procedure alter_table_not_referenceable
    (table_name varchar2, table_schema varchar2 default null,
     affected_schema varchar2 default null);
  --  Alter the given object table table_schema.table_name so it becomes not
  --  the default referenceable table for the schema affected_schema.
  --  This is equivalent to SQL "ALTER TABLE [<table_schema>.]<table_name>
  --  NOT REFERENCEABLE FOR <affected_schema>"
  --  which is currently not supported or available as a DDL statement.
  --  This procedure simply reverts for the affected schema to the default
  --  table referenceable for PUBLIC; i.e., it simply undoes the previous
  --  alter_table_referenceable call for this specific schema.
  --
  --  The affected schema must a particular schema (cannot be PUBLIC).
  --
  --  The user that executes this procedure must own the table (i.e.,
  --  the schema is the same as the user), and the affected schema must be
  --  the same as the user.
  --
  --  If the user executing this procedure has ALTER ANY TABLE and SELECT ANY
  --  TABLE and DROP ANY TABLE privileges, the user doesn't have to own the
  --  table and the affected schema can be any valid schema.
  --
  --  Input arguments:
  --    table_name
  --      The name of the table to be altered.  Cannot be a synonym.
  --      Must not be NULL.  Case sensitive.
  --    table_schema
  --      The name of the schema owning the table to be altered.
  --      If NULL then the current schema is used.  Case sensitive.
  --    affected_schema
  --      The name of the schema affected by this alteration.
  --      If NULL then the current schema is used.  Case sensitive.
  --  Exceptions:
  --    ORA-20000: insufficient privileges, invalid schema name or
  --               table does not exist,
end;
/

drop public synonym dbms_ddl
/
create public synonym dbms_ddl for sys.dbms_ddl
/
grant execute on dbms_ddl to public
/

create or replace package dbms_utility is
  ------------
  --  OVERVIEW
  --
  --  This package provides various utility routines.

  ----------------------------
  --  PL/SQL TABLES
  --
  type uncl_array IS table of VARCHAR2(227) index by BINARY_INTEGER;
  --  Lists of "USER"."NAME"."COLUMN"@LINK should be stored here

  type name_array IS table of VARCHAR2(30) index by BINARY_INTEGER;
  --  Lists of NAME should be stored here

  type dblink_array IS table of VARCHAR2(128) index by BINARY_INTEGER;
  --  Lists of database links should be stored here
  
  TYPE index_table_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
  --  order in which objects should be generated is returned here
  
  TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  --  order in which objects should be generated is returned here for users.
  TYPE instance_record IS RECORD (
       inst_number   NUMBER,
       inst_name     VARCHAR2(60));
  
  TYPE instance_table IS TABLE OF instance_record INDEX BY BINARY_INTEGER;
  -- list of active instance number and instance name
  -- the starting index of instance_table is 1
  -- instance_table is dense
  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure compile_schema(schema varchar2);
  --  Compile all procedures, functions, packages and triggers in the specified
  --  schema.  After calling this procedure you should select from view
  --  ALL_OBJECTS for items with status of 'INVALID' to see if all objects
  --  were successfully compiled.  You may use the SQLDBA command "SHOW
  --  ERRORS <type> <schema>.<name>" to see the errors assocated with
  --  'INVALID' objects.
  --  Input arguments:
  --    schema
  --      Name of the schema.
  --  Exceptions:
  --    ORA-20000: Insufficient privileges for some object in this schema.
  procedure analyze_schema(schema varchar2, method varchar2, 
    estimate_rows number default null, 
    estimate_percent number default null, method_opt varchar2 default null);
  --  Analyze all the tables, clusters and indexes in a schema.
  --  Input arguments:
  --    schema
  --      Name of the schema.
  --    method, estimate_rows, estimate_percent, method_opt
  --      See the descriptions above in sql_ddl.analyze.object.
  --  Exceptions:
  --    ORA-20000: Insufficient privileges for some object in this schema.
  procedure analyze_database(method varchar2, 
    estimate_rows number default null, 
    estimate_percent number default null, method_opt varchar2 default null);
  --  Analyze all the tables, clusters and indexes in a database.
  --  Input arguments:
  --    method, estimate_rows, estimate_percent, method_opt
  --      See the descriptions above in sql_ddl.analyze.object.
  --  Exceptions:
  --    ORA-20000: Insufficient privileges for some object in this database.
  function format_error_stack return varchar2;
    pragma interface (C, format_error_stack);		    -- 1 (see psdicd.c)
  --  Format the current error stack.  This can be used in exception
  --    handlers to look at the full error stack.
  --  Output arguments:
  --    format_error_stack
  --      Returns the error stack.  May be up to 2000 bytes.
  function format_call_stack return varchar2;
    pragma restrict_references(format_call_stack,WNDS);
    pragma interface (C, format_call_stack);		    -- 2 (see psdicd.c)
  --  Format the current call stack.  This can be used an any stored
  --    procedure or trigger to access the call stack.  This can be
  --    useful for debugging.
  --  Output arguments:
  --    format_call_stack
  --      Returns the call stack.  May be up to 2000 bytes.
  function is_parallel_server return boolean;
  --  Find out if this database is running in parallel server mode.
  --  Output arguments:
  --    is_parallel_server
  --      TRUE if this instance was started in parallel server mode,
  --      FALSE otherwise.
  function get_time return number;
  --  Find out the current time in 100th's of a second.
  --  Output argukments:
  --    get_time
  --      The time is the number of 100th's of a second from some
  --      arbitrary epoch.
  function get_parameter_value(parnam in     varchar2,
		               intval in out binary_integer,
		               strval in out varchar2)
    return binary_integer;
  --  Gets value of specified init.ora parameter.
  --  Input arguments:
  --    parnam
  --      Parameter name
  --  Output arguments:
  --    intval
  --      Value of an integer parameter or value length of a string parameter
  --    strval
  --      Value of a string parameter
  --  Returns:
  --    partyp
  --      Parameter type
  --        0 if parameter is an integer/boolean parameter
  --        1 if parameter is a  string/file parameter
  -- Example usage:
  -- DECLARE
  --   parnam VARCHAR2(256);
  --   intval BINARY_INTEGER;
  --   strval VARCHAR2(256);
  --   partyp BINARY_INTEGER;
  -- BEGIN
  --   partyp := dbms_utility.get_parameter_value('max_dump_file_size',
  --                                               intval, strval);
  --   dbms_output.put('parameter value is: ');
  --   IF partyp = 1 THEN
  --     dbms_output.put_line(strval);
  --   ELSE
  --     dbms_output.put_line(intval);
  --   END IF;
  --   IF partyp = 1 THEN
  --     dbms_output.put('parameter value length is: ');
  --     dbms_output.put_line(intval);
  --   END IF;
  --   dbms_output.put('parameter type is: ');
  --   IF partyp = 1 THEN
  --     dbms_output.put_line('string');
  --   ELSE
  --     dbms_output.put_line('integer');
  --   END IF;
  -- END;
  procedure name_resolve(name in varchar2, context in number,
    schema out varchar2, part1 out varchar2, part2 out varchar2,
    dblink out varchar2, part1_type out number, object_number out number);
  --  Resolve the given name.  Do synonym translation if necessary.  Do
  --    authorization checking.
  --  Input arguments:
  --    name
  --      The name of the object.  This can be of the form [[a.]b.]c[@d]
  --      where a,b,c are SQL identifier and d is a dblink.  No syntax
  --      checking is performed on the dblink.  If a dblink is specified,
  --      of the name resolves to something with a dblink, then object
  --      is not resolved, but the schema, part1, part2 and dblink out
  --      arguments are filled in.  a,b and c may be delimted identifiers,
  --      and may contain NLS characters (single and multi-byte).
  --    context
  --	  Must be an integer between 0 and 8.
  --  Output arguments:
  --    schema
  --      The schema of the object.  If no schema is specified in 'name'
  --      then the schema is determined by resolving the name.
  --    part1
  --      The first part of the name.  The type of this name is specified
  --      part1_type (synonym, procedure or package).
  --    part2
  --      If this is non-null, then this is a procedure name within the
  --      package indicated by part1.
  --    dblink
  --      If this is non-null then a database link was either specified
  --      as part of 'name' or 'name' was a synonym which resolved to
  --      something with a database link.  In this later case, part1_type
  --      will indicate a synonym.
  --    part1_type
  --      The type of part1 is
  --        5 - synonym
  --        7 - procedure (top level)
  --        8 - function (top level)
  --        9 - package
  --      If a synonym, it means that 'name' is a synonym that translats
  --      to something with a database link.  In this case, if further
  --      name translation is desired, then you must call the 
  --      dbms_utility.name_resolve procedure on this remote node.
  --    object_number
  --      If non-null then 'name' was successfully resolved and this is the
  --      object number which it resolved to.
  --  Exceptions:
  --    All errors are handled by raising exceptions.  A wide variety of
  --    exceptions are possible, based on the various syntax error that
  --    are possible when specifying object names.
  procedure name_tokenize( name    in  varchar2,
		           a       out varchar2,
		           b       out varchar2,
	                   c       out varchar2,
		           dblink  out varchar2, 
                           nextpos out binary_integer);
  --  Call the parser to parse the given name as "a [. b [. c ]][@ dblink ]".
  --  Strip doublequotes, or convert to uppercase if there are no quotes.
  --    Ignore comments of all sorts.  Do no semantic analysis.  Leave any
  --      missing values as null. 
  --  For each of a,b,c,dblink, tell where the following token starts
  --    in anext,bnext,cnext,dnext respectively.
  PROCEDURE comma_to_table( list   IN  VARCHAR2,
                            tablen OUT BINARY_INTEGER,
                            tab    OUT uncl_array);
  --  Convert a comma-separated list of names into a PL/SQL table of names
  --  This uses name_tokenize to figure out what are names and what are commas

  PROCEDURE table_to_comma( tab    IN  uncl_array, 
                            tablen OUT BINARY_INTEGER,
                            list   OUT VARCHAR2);
  --  Convert a PL/SQL table of names into a comma-separated list of names

  FUNCTION port_string RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES(port_string, WNDS, RNDS, WNPS, RNPS);
  --  Return a string that uniquely identifies the port (operating system)
  --  and the two task protocol version of Oracle.  EG "VAX/VMX-7.1.0.0"
  --  "SVR4-be-8.1.0" (SVR4-b(ig)e(ndian)-8.1.0)
  --  maximum length is port specific.

  PROCEDURE db_version(version       OUT VARCHAR2,
                       compatibility OUT VARCHAR2);
  -- Return version information for the database:
  -- version -> A string which represents the internal software version
  --            of the database (e.g., 7.1.0.0.0). The length of this string
  --            is variable and is determined by the database version.
  -- compatibility -> The compatibility setting of the database determined by
  --                  the "compatible" init.ora parameter. If the parameter 
  --                  is not specified in the init.ora file, NULL is returned.

  function make_data_block_address(file number, block number) return number;
  PRAGMA RESTRICT_REFERENCES(make_data_block_address, WNDS, RNDS, WNPS, RNPS);
  --  Creates a data block address given a file# and a block#.  A data block
  --  address is the internal structure used to identify a block in the
  --  database.  This is function useful when accessing certain fixed tables
  --  that contain data block addresses.
  --  Input arguments:
  --    file  - the file that contains the block
  --    block - the offset of the block within the file in terms of block 
  --            increments
  --  Output arguments:
  --    dba   - the data block address
  function data_block_address_file(dba number) return number;
  PRAGMA RESTRICT_REFERENCES(data_block_address_file, WNDS, RNDS, WNPS, RNPS);
  --  Get the file number part of a data block address
  --  Input arguments:
  --    dba   - a data block address
  --  Output Arguments:
  --    file  - the file that contains the block
  function data_block_address_block(dba number) return number;
  PRAGMA RESTRICT_REFERENCES(data_block_address_block, WNDS, RNDS, WNPS, RNPS);
  --  Get the block number part of a data block address
  --  Input arguments:
  --    dba   - a data block address
  --  Output Arguments:
  --    block  - the block offset of the block  
  function get_hash_value(name varchar2, base number, hash_size number)
    return number;
  PRAGMA RESTRICT_REFERENCES(get_hash_value, WNDS, RNDS, WNPS, RNPS);
  --  Compute a hash value for the given string
  --  Input arguments:
  --    name  - The string to be hashed.
  --    base  - A base value for the returned hash value to start at.
  --    hash_size -  The desired size of the hash table.
  --  Returns:
  --    A hash value based on the input string.
  --    For example, to get a hash value on a string where the hash value 
  --    should be between 1000 and 3047, use 1000 as the base value and
  --    2048 as the hash_size value.  Using a power of 2 for the hash_size
  --    parameter works best.
procedure analyze_part_object 
   (schema in varchar2 default null,
    object_name in varchar2 default null,
    object_type in char default 'T',
    command_type in char default 'E',
    command_opt in varchar2 default null,
    sample_clause in varchar2 default 'sample 5 percent');
  --  Equivalent to SQL "ANALYZE TABLE|INDEX [<schema>.]<object_name>
  --    PARTITION <pname> [<command_type>] [<command_opt>] [<sample_clause>]
  --  for each partition of the object, run in parallel using job queues.
  --  The package will submit a job for each partition
  --  It is the users responsibilty to control the number of concurrent
  --  jobs by setting the INIT parameter JOB_QUEUE_PROCESSES correctly
  --  There is minimal error checking for correct syntax.  Any error will be 
  --  reported in SNP trace files.
  --  Input arguments:
  --  schema
  --    schema of the object_name
  --  object_name
  --    name of object to be analyzed, must be partitioned
  --  object_type
  --    type of object, must be T(able) or I(ndex)
  --  command_type
  --    must be one of the following
  --      - C(omput statistics)
  --      - E(stimate statistics)
  --      - D(elete statistics)
  --      - V(alidate structure)
  --  command_opt
  --    Other options for the command type.
  --    For C, E it can be FOR table, FOR all LOCAL indexes, FOR all columns or
  --    combination of some of the 'for' options of analyze statistics (table)
  --    For V, it can be 'CASCADE' when object_type is T
  --  sample_clause
  --    Specifies the sample clause to use when command_type is 'E'
procedure exec_ddl_statement(parse_string in varchar2);
  -- Will execute the DDL statement in parse_string
  --  parse_string
  --    DDL statement to be executed

function current_instance return number;
  -- Return the current connected instance number
  -- Return NULL when connected instance is down

procedure  active_instances(instance_table   OUT instance_table,
			    instance_count   OUT number);
  -- instance_table contains a list of the active instance numbers and names
  -- When no instance is up ( or non-OPS setting), the list is empty
  -- instance_count is  the number of active instances, 0 under non-ops setting
 
end;
/

drop public synonym dbms_utility
/
create public synonym dbms_utility for sys.dbms_utility
/
grant execute on dbms_utility to public
/


create or replace package dbms_space is
  ------------
  --  OVERVIEW
  --
  --  This package provides segment space information not currently
  --  available through the standard views.

  --  SECURITY
  --
  --  The execution privilege is granted to PUBLIC. Procedures in this
  --  package run under the caller security. The user must have ANALYZE
  --  privilege on the object.


  ----------------------------

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure unused_space(segment_owner IN varchar2, 
                         segment_name IN varchar2,
                         segment_type IN varchar2,
                         total_blocks OUT number,
                         total_bytes OUT number,
                         unused_blocks OUT number,
                         unused_bytes OUT number,
                         last_used_extent_file_id OUT number,
                         last_used_extent_block_id OUT number,
                         last_used_block OUT number,
                         partition_name IN varchar2 DEFAULT NULL
                         );
  pragma restrict_references(unused_space,WNDS);

  --  Returns information about unused space in an object (table, index,
  --    or cluster).
  --  Input arguments:
  --   segment_owner  
  --	  schema name of the segment to be analyzed
  --   segment_name  
  --	  object name of the segment to be analyzed
  --   partition_name  
  --	  partition name of the segment to be analyzed
  --   segment_type  
  --	  type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
  --  Output arguments:
  --   total_blocks  
  --	  total number of blocks in the segment
  --   total_bytes  
  --	  the same as above, expressed in bytes
  --   unused_blocks  
  --	  number of blocks which are not used 
  --   unused_bytes  
  --	  the same as above, expressed in bytes
  --   last_used_extent_file_id 
  --	  the file ID of the last extent which contains data
  --   last_used_extent_block_id 
  --	  the block ID of the last extent which contains data
  --   last_used_block  
  --	  the last block within this extent which contains data
  procedure free_blocks (segment_owner IN varchar2, 
                         segment_name IN varchar2,
                         segment_type IN varchar2,
                         freelist_group_id IN number,
                         free_blks OUT number,
                         scan_limit IN number DEFAULT NULL,
                         partition_name IN varchar2 DEFAULT NULL
                         );
  pragma restrict_references(free_blocks,WNDS);

  --  Returns information about free blocks in an object (table, index,
  --    or cluster).
  --  Input arguments:
  --   segment_owner  
  --	  schema name of the segment to be analyzed
  --   segment_name  
  --	  name of the segment to be analyzed
  --   partition_name  
  --	  partition name of the segment to be analyzed
  --   segment_type  
  --	  type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
  --   freelist_group_id  
  --	  freelist group (instance) whose free list size is to be computed
  --   scan_limit (optional)
  --	  maximum number of free blocks to read
  --  Output arguments:
  --   free_blks  
  --	  count of free blocks for the specified group
end;
/
drop public synonym dbms_space
/
create public synonym dbms_space for sys.dbms_space
/
grant execute on dbms_space to public
/

create or replace package dbms_rowid is
  ------------
  --  OVERVIEW
  --
  --  This package provides procedures to create ROWIDs and to interpret
  --  their contents

  --  SECURITY
  --
  --  The execution privilege is granted to PUBLIC. Procedures in this
  --  package run under the caller security. 


  ----------------------------

  ----------------------------

  --  ROWID TYPES:
  --
  --   RESTRICTED - Restricted ROWID
  --
  --   EXTENDED   - Extended ROWID 
  --
  rowid_type_restricted constant integer := 0;
  rowid_type_extended   constant integer := 1;

  --  ROWID VERIFICATION RESULTS:
  --
  --   VALID   - Valid ROWID
  --
  --   INVALID - Invalid ROWID 
  --
  rowid_is_valid   constant integer := 0;
  rowid_is_invalid constant integer := 1;

  --  OBJECT TYPES:
  --
  --   UNDEFINED - Object Number not defined (for restricted ROWIDs)
  --
  rowid_object_undefined constant integer := 0;

  --  ROWID CONVERSION TYPES:
  --
  --   INTERNAL - convert to/from column of ROWID type
  --
  --   EXTERNAL - convert to/from string format
  --
  rowid_convert_internal constant integer := 0;
  rowid_convert_external constant integer := 1;

  --  EXCEPTIONS:
  --
  -- ROWID_INVALID  - invalid rowid format
  --
  -- ROWID_BAD_BLOCK - block is beyond end of file
  --
  ROWID_INVALID exception;
     pragma exception_init(ROWID_INVALID, -1410);
  ROWID_BAD_BLOCK exception;
     pragma exception_init(ROWID_BAD_BLOCK, -28516);

  --  PROCEDURES AND FUNCTIONS:
  --

  --
  -- ROWID_CREATE constructs a ROWID from its constituents:
  --
  -- rowid_type - type (restricted/extended) 
  -- object_number - data object number (rowid_object_undefined for restricted)
  -- relative_fno - relative file number
  -- block_number - block number in this file
  -- file_number - file number in this block
  --
  function rowid_create(rowid_type IN number, 
                        object_number IN number,
                        relative_fno IN number,
                        block_number IN number,
                        row_number IN number) 
			return rowid;
  pragma RESTRICT_REFERENCES(rowid_create,WNDS,RNDS,WNPS,RNPS);

  --
  -- ROWID_INFO breaks ROWID into its components and returns them:
  --
  -- rowid_in - ROWID to be interpreted
  -- rowid_type - type (restricted/extended) 
  -- object_number - data object number (rowid_object_undefined for restricted)
  -- relative_fno - relative file number
  -- block_number - block number in this file
  -- file_number - file number in this block
  --
  procedure rowid_info( rowid_in IN rowid,
			rowid_type OUT number, 
                        object_number OUT number,
                        relative_fno OUT number,
                        block_number OUT number,
                        row_number OUT number);
  pragma RESTRICT_REFERENCES(rowid_info,WNDS,RNDS,WNPS,RNPS);

  --
  -- ROWID_TYPE returns the type of a ROWID (restricted/extended_nopart,..)
  --
  -- row_id - ROWID to be interpreted
  --
  function rowid_type(row_id IN rowid) 
			return number;
  pragma RESTRICT_REFERENCES(rowid_type,WNDS,RNDS,WNPS,RNPS);

  --
  -- ROWID_OBJECT extracts the data object number from a ROWID. 
  -- ROWID_OBJECT_UNDEFINED is returned for restricted rowids.
  --
  -- row_id - ROWID to be interpreted
  --
  function rowid_object(row_id IN rowid) 
			return number;
  pragma RESTRICT_REFERENCES(rowid_object,WNDS,RNDS,WNPS,RNPS);

  --
  -- ROWID_RELATIVE_FNO extracts the relative file number from a ROWID. 
  --
  -- row_id - ROWID to be interpreted
  --
  function rowid_relative_fno(row_id IN rowid) 
			return number;
  pragma RESTRICT_REFERENCES(rowid_relative_fno,WNDS,RNDS,WNPS,RNPS);

  --
  -- ROWID_BLOCK_NUMBER extracts the block number from a ROWID. 
  --
  -- row_id - ROWID to be interpreted
  --
  function rowid_block_number(row_id IN rowid) 
			return number;
  pragma RESTRICT_REFERENCES(rowid_block_number,WNDS,RNDS,WNPS,RNPS);

  --
  -- ROWID_ROW_NUMBER extracts the row number from a ROWID. 
  --
  -- row_id - ROWID to be interpreted
  --
  function rowid_row_number(row_id IN rowid) 
			return number;
  pragma RESTRICT_REFERENCES(rowid_row_number,WNDS,RNDS,WNPS,RNPS);

  --
  -- ROWID_TO_ABSOLUTE_FNO extracts the relative file number from a ROWID,
  -- which addresses a row in a given table
  --
  -- row_id - ROWID to be interpreted
  --
  -- schema_name - name of the schema which contains the table
  --
  -- object_name - table name 
  --
  function rowid_to_absolute_fno(row_id IN rowid,
				 schema_name IN varchar2,
				 object_name IN varchar2)
			return number;
  pragma RESTRICT_REFERENCES(rowid_to_absolute_fno,WNDS,WNPS,RNPS);

  --
  -- ROWID_TO_EXTENDED translates the restricted ROWID which addresses
  -- a row in a given table to the extended format. Later, it may be removed
  -- from this package into a different place
  --
  -- old_rowid - ROWID to be converted
  --
  -- schema_name - name of the schema which contains the table (OPTIONAL)
  --
  -- object_name - table name (OPTIONAL)
  --
  -- conversion_type - rowid_convert_internal/external_convert_external
  --                   (whether old_rowid was stored in a column of ROWID
  --                    type, or the character string)
  --
  function rowid_to_extended(old_rowid IN rowid,
                             schema_name IN varchar2,
                             object_name IN varchar2,
                             conversion_type IN integer)
			return rowid;
  pragma RESTRICT_REFERENCES(rowid_to_extended,WNDS,WNPS,RNPS);

  --
  -- ROWID_TO_RESTRICTED translates the extnded ROWID into a restricted format
  --
  -- old_rowid - ROWID to be converted
  --
  -- conversion_type - internal/external (IN)
  --
  -- conversion_type - rowid_convert_internal/external_convert_external
  --                   (whether returned rowid will be stored in a column of 
  --                    ROWID type, or the character string)
  --
  function rowid_to_restricted(old_rowid IN rowid,
                               conversion_type IN integer)
			return rowid;
  pragma RESTRICT_REFERENCES(rowid_to_restricted,WNDS,RNDS,WNPS,RNPS);

  --
  -- ROWID_VERIFY verifies the ROWID. It returns rowid_valid or rowid_invalid
  -- value depending on whether a given ROWID is valid or not. 
  --
  -- rowid_in - ROWID to be verified
  --
  -- schema_name - name of the schema which contains the table
  --
  -- object_name - table name 
  --
  -- conversion_type - rowid_convert_internal/external_convert_external
  --                   (whether old_rowid was stored in a column of ROWID
  --                    type, or the character string)
  --
  function rowid_verify(rowid_in IN rowid,
                        schema_name IN varchar2,
                        object_name IN varchar2,
                        conversion_type IN integer)
			return number;
  pragma RESTRICT_REFERENCES(rowid_verify,WNDS,WNPS,RNPS);

end;
/
drop public synonym dbms_rowid
/
create public synonym dbms_rowid for sys.dbms_rowid
/
grant execute on dbms_rowid to public
/

create or replace package dbms_pclxutil as 
  ------------
  --  OVERVIEW
  --  
  --  a package that provides intra-partition parallelism for creating 
  --  partition-wise local index.
  --
  --  SECURITY
  --
  --  The execution privilege is granted to PUBLIC. The procedure
  --  build_part_index in this package run under the caller security. 
  --

  ----------------------------

  ----------------------------

  type JobList is table of number;

  procedure build_part_index (
     jobs_per_batch in number default 1,
     procs_per_job  in number default 1,
     tab_name       in varchar2 default null,
     idx_name       in varchar2 default null,
     force_opt      in boolean default FALSE); 
  --
  -- jobs_per_batch: #jobs to be created (1 <= job_count <= #partitions)
  --
  -- procs_per_job:  #slaves per job (1 <= degree <= max_slaves)
  --
  -- tab_name:       name of the partitioned table (an exception is 
  --                 raised if the table does not exist or not 
  --                 partitioned)
  --
  -- idx_name:       name given to the local index (an exception is 
  --                 raised if a local index is not created on the 
  --                 table tab_name)
  --
  -- force_opt:      if TRUE force rebuild of all partitioned indices; 
  --                 otherwise rebuild only the partitions marked 
  --                 'UNUSABLE'
  --

  function calc_default_slaves (
      default_str    in varchar2) return number;

  procedure wait_till_queue_ready (
     job_count      in number,
     current_jobs   in out JobList); 

  procedure update_queue_status (
     this_job       in number, 
     current_jobs   in out JobList);

  procedure submit_job (
     job_count      in number,
     current_jobs   in out JobList,
     idx_name       in varchar2, 
     part_name      in varchar2,
     slaves         in number,
     composite      in boolean);

  procedure clean_my_queue ( 
     current_jobs   in JobList);

end dbms_pclxutil;
/
drop public synonym dbms_pclxutil
/
create public synonym dbms_pclxutil for sys.dbms_pclxutil
/
grant execute on dbms_pclxutil to public
/
