René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

dbms_wm

dmbs_wm is the work space manager and allows to travel back in time, so to speak.
Installing the workspace manager. The script owminst.plb must be run as user sys.
This step is not nessessary if the database was installed with the Database Configuration Assistant (DBCA). 
@?/rdbms/admin/owminst.plb
An example can be found here.

Procedures/Functions

addasparentworkspace

procedure addasparentworkspace (
 workspace                       in varchar2                        ,
 parent_workspace                in varchar2                        ,
 auto_commit                     in boolean                  default
);

add_topo_geometry_layer

procedure add_topo_geometry_layer (
 topology                        in varchar2                        ,
 table_name                      in varchar2                        ,
 column_name                     in varchar2                        ,
 topo_geometry_layer_type        in varchar2                        
);

altersavepoint

procedure altersavepoint (
 workspace                       in varchar2                        ,
 sp_name                         in varchar2                        ,
 sp_description                  in varchar2                        
);

alterversionedtable

procedure alterversionedtable (
 table_name                      in varchar2                        ,
 alter_option                    in varchar2                        ,
 parameter_options               in varchar2                 default,
 ignore_last_error               in boolean                  default
);

alterworkspace

procedure alterworkspace (
 workspace                       in varchar2                        ,
 workspace_description           in varchar2                        
);

beginbulkloading

procedure beginbulkloading (
 table_name                      in varchar2                        ,
 workspace                       in varchar2                        ,
 version                         in number(38)                      ,
 check_for_duplicates            in boolean                  default,
 ignore_last_error               in boolean                  default,
 single_transaction              in boolean                  default
);

beginddl

procedure beginddl (
 table_name                      in varchar2                        
);

beginresolve

procedure beginresolve (
 workspace                       in varchar2                        
);
This procedure cannot be invoked in the LIVE workspace. Otherwise, it causes a ORA-20158: this procedure cannot be invoked on the 'LIVE' workspace.

changeworkspacetype

procedure changeworkspacetype (
 workspace                       in varchar2                        ,
 workspace_type                  in varchar2                 default,
 auto_commit                     in boolean                  default
);

commitbulkloading

procedure commitbulkloading (
 table_name                      in varchar2                        ,
 discards_table                  in varchar2                        ,
 check_for_duplicates            in boolean                  default,
 enforceucflag                   in boolean                  default,
 enforcericflag                  in boolean                  default,
 ignore_last_error               in boolean                  default,
 single_transaction              in boolean                  default
);

commitddl

procedure commitddl (
 table_name                      in varchar2                        ,
 ignore_last_error               in boolean                  default,
 enforce_unique_constraints      in boolean                  default,
 enforce_rics                    in boolean                  default
);

commitresolve

procedure commitresolve (
 workspace                       in varchar2                        
);

compressworkspace

procedure compressworkspace (
 workspace                       in varchar2                        ,
 firstsp                         in varchar2                 default,
 secondsp                        in varchar2                 default,
 auto_commit                     in boolean                  default,
 commit_in_batches               in boolean                  default,
 batch_size                      in varchar2                 default,
 remove_latest_deleted_rows      in boolean                  default
);

procedure compressworkspace (
 workspace                       in varchar2                        ,
 compress_view_wo_overwrite      in boolean                         ,
 firstsp                         in varchar2                 default,
 secondsp                        in varchar2                 default,
 auto_commit                     in boolean                  default,
 commit_in_batches               in boolean                  default,
 batch_size                      in varchar2                 default,
 remove_latest_deleted_rows      in boolean                  default
);

compressworkspacetree

procedure compressworkspacetree (
 workspace                       in varchar2                        ,
 compress_view_wo_overwrite      in boolean                  default,
 auto_commit                     in boolean                  default,
 commit_in_batches               in boolean                  default,
 batch_size                      in varchar2                 default,
 remove_latest_deleted_rows      in boolean                  default
);

copyforupdate

procedure copyforupdate (
 table_name                      in varchar2                        ,
 where_clause                    in varchar2                 default
);

createfastlive

procedure createfastlive (
 table_name                      in varchar2                        
);

createsavepoint

procedure createsavepoint (
 workspace                       in varchar2                        ,
 savepoint_name                  in varchar2                        ,
 description                     in varchar2                 default,
 auto_commit                     in boolean                  default
);

createworkspace

procedure createworkspace (
 workspace                       in varchar2                        ,
 description                     in varchar2                 default,
 auto_commit                     in boolean                  default
);

procedure createworkspace (
 workspace                       in varchar2                        ,
 isrefreshed                     in boolean                         ,
 description                     in varchar2                 default,
 auto_commit                     in boolean                  default
);

deletesavepoint

procedure deletesavepoint (
 workspace                       in varchar2                        ,
 savepoint_name                  in varchar2                        ,
 compress_view_wo_overwrite      in boolean                  default,
 auto_commit                     in boolean                  default,
 commit_in_batches               in boolean                  default,
 batch_size                      in varchar2                 default
);

delete_topo_geometry_layer

procedure delete_topo_geometry_layer (
 topology                        in varchar2                        ,
 table_name                      in varchar2                        ,
 column_name                     in varchar2                        
);

disablereplicationsupport

procedure disablereplicationsupport (
);

disableversioning

procedure disableversioning (
 table_name                      in varchar2                        ,
 force                           in boolean                  default,
 ignore_last_error               in boolean                  default,
 istopology                      in boolean                  default,
 keepwmvalid                     in boolean                  default
);

disableversioning_repln

procedure disableversioning_repln (
 table_name                      in varchar2                        ,
 force                           in varchar2                 default,
 ignore_last_error_v             in varchar2                 default,
 islocalsite                     in varchar2                        ,
 hasadminrole                    in boolean                  default,
 istopology                      in boolean                  default,
 keepwmvalid                     in boolean                  default
);

dropfastlive

procedure dropfastlive (
 table_name                      in varchar2                        
);

dropreplicationsupport

procedure dropreplicationsupport (
);

enablereplicationsupport

procedure enablereplicationsupport (
);

enableversioning

procedure enableversioning (
 table_name                      in varchar2                        ,
 hist                            in varchar2                 default,
 istopology                      in boolean                  default,
 validtime                       in boolean                  default,
 undo_space                      in varchar2                 default
);

enableversioning_repln

procedure enableversioning_repln (
 table_name                      in varchar2                        ,
 hist                            in varchar2                 default,
 islocalsite                     in varchar2                        ,
 hasadminrole                    in boolean                  default,
 istopology                      in boolean                  default,
 validtime                       in boolean                  default,
 undo_space                      in varchar2                 default
);

export

procedure export (
 table_name                      in varchar2                        ,
 staging_table                   in varchar2                        ,
 workspace                       in varchar2                        ,
 where_clause                    in varchar2                 default,
 export_scope                    in varchar2                 default,
 after_savepoint_name            in varchar2                 default,
 as_of_savepoint_name            in varchar2                 default,
 after_instant                   in date                     default,
 as_of_instant                   in date                     default,
 versioned_db                    in boolean                  default,
 overwrite_existing_data         in boolean                  default,
 auto_commit                     in boolean                  default
);

findricset

procedure findricset (
 table_name                      in varchar2                        ,
 result_table                    in varchar2                 default
);

freezeworkspace

procedure freezeworkspace (
 workspace                       in varchar2                        ,
 session_duration                in boolean                         ,
 freezemode                      in varchar2                 default,
 freezewriter                    in varchar2                 default,
 force                           in boolean                  default
);

procedure freezeworkspace (
 workspace                       in varchar2                        ,
 freezemode                      in varchar2                 default,
 freezewriter                    in varchar2                 default,
 force                           in boolean                  default
);

generatereplicationsupport

procedure generatereplicationsupport (
 mastersites                     in varchar2                        ,
 groupname                       in varchar2                        ,
 groupdescription                in varchar2                 default
);

getbulkloadversion

function getbulkloadversion returns number(38) (
 workspace                       in varchar2                        ,
 savepoint_var                   in varchar2                 default
);

getconflictworkspace

function getconflictworkspace returns varchar2 (
);

getdiffversions

function getdiffversions returns varchar2 (
);

getlockmode

function getlockmode returns varchar2 (
);

getltlockstr

function getltlockstr returns varchar2 (
 curlock                         in varchar2                        ,
 curnextver                      in varchar2                        ,
 state_name                      in varchar2                        ,
 lock_mode                       in varchar2                        
);

getmultiworkspaces

function getmultiworkspaces returns varchar2 (
);

getopcontext

function getopcontext returns varchar2 (
);

getphysicaltablename

function getphysicaltablename returns varchar2 (
 table_owner                     in varchar2                        ,
 table_name                      in varchar2                        
);

getprivs

function getprivs returns varchar2 (
 workspace                       in varchar2                        
);

getsessioninfo

procedure getsessioninfo (
 workspace                      out varchar2                        ,
 context                        out varchar2                        ,
 context_type                   out varchar2                        
);

getsid

function getsid returns varchar2 (
);

getsystemparameter

function getsystemparameter returns varchar2 (
 name                            in varchar2                        
);

getvalidfrom

function getvalidfrom returns timestamp with time zone (
);

getvalidtill

function getvalidtill returns timestamp with time zone (
);

getversion

function getversion returns varchar2 (
);

getwmmetadataspace

function getwmmetadataspace returns number (
);

getworkspace

function getworkspace returns varchar2 (
);

gotodate

procedure gotodate (
 in_date                         in date                            
);

procedure gotodate (
 in_date                         in varchar2                        ,
 fmt                             in varchar2                 default,
 nlsparam                        in varchar2                 default,
 tswtz                           in boolean                  default
);

gotosavepoint

procedure gotosavepoint (
 savepoint_name                  in varchar2                 default
);

gotoworkspace

procedure gotoworkspace (
 workspace                       in varchar2                        
);

grantgraphpriv

procedure grantgraphpriv (
 priv_types                      in varchar2                        ,
 leaf_workspace                  in varchar2                        ,
 grantee                         in varchar2                        ,
 node_types                      in varchar2                 default,
 grant_option                    in varchar2                 default,
 auto_commit                     in boolean                  default
);

grantprivstorepadminonsyspkgs

procedure grantprivstorepadminonsyspkgs (
);

grantsystempriv

procedure grantsystempriv (
 priv_types                      in varchar2                        ,
 grantee                         in varchar2                        ,
 grant_option                    in varchar2                 default,
 auto_commit                     in boolean                  default
);

grantworkspacepriv

procedure grantworkspacepriv (
 priv_types                      in varchar2                        ,
 workspace                       in varchar2                        ,
 grantee                         in varchar2                        ,
 grant_option                    in varchar2                 default,
 auto_commit                     in boolean                  default
);

import

procedure import (
 staging_table                   in varchar2                        ,
 to_table                        in varchar2                        ,
 to_workspace                    in varchar2                        ,
 from_workspace                  in varchar2                 default,
 where_clause                    in varchar2                 default,
 import_scope                    in varchar2                 default,
 ancestor_savepoint_workspace    in varchar2                 default,
 ancestor_savepoint_name         in varchar2                 default,
 apply_locks                     in boolean                  default,
 enforceucflag                   in boolean                  default,
 enforcericflag                  in boolean                  default,
 auto_commit                     in boolean                  default
);

isworkspaceoccupied

function isworkspaceoccupied returns varchar2 (
 workspace                       in varchar2                        
);

lockrows

procedure lockrows (
 workspace                       in varchar2                        ,
 table_name                      in varchar2                        ,
 where_clause                    in varchar2                 default,
 lock_mode                       in varchar2                 default,
 xmin                            in number                   default,
 ymin                            in number                   default,
 xmax                            in number                   default,
 ymax                            in number                   default
);

max_time

function max_time returns timestamp with time zone (
);

mergetable

procedure mergetable (
 workspace                       in varchar2                        ,
 table_id                        in varchar2                        ,
 where_clause                    in varchar2                 default,
 create_savepoint                in boolean                  default,
 remove_data                     in boolean                  default,
 auto_commit                     in boolean                  default
);
If there is a conflict in the workspace, it causes a ORA-20055: conflicts detected for workspace: 'workspace-name' in table: 'table-name'.

mergeworkspace

procedure mergeworkspace (
 workspace                       in varchar2                        ,
 create_savepoint                in boolean                  default,
 remove_workspace                in boolean                  default,
 auto_commit                     in boolean                  default
);

min_time

function min_time returns timestamp with time zone (
);

move_proc

procedure move_proc (
 dest_tablespace                 in varchar2                 default
);

recoverallmigratingtables

procedure recoverallmigratingtables (
 ignore_last_error               in boolean                  default
);

recovermigratingtable

procedure recovermigratingtable (
 table_name                      in varchar2                        ,
 ignore_last_error               in boolean                  default
);

refreshtable

procedure refreshtable (
 workspace                       in varchar2                        ,
 table_id                        in varchar2                        ,
 where_clause                    in varchar2                 default,
 auto_commit                     in boolean                  default
);

refreshworkspace

procedure refreshworkspace (
 workspace                       in varchar2                        ,
 auto_commit                     in boolean                  default,
 copy_data                       in boolean                  default
);

relocatewritersite

procedure relocatewritersite (
 newwritersite                   in varchar2                        ,
 oldwritersiteavailable          in boolean                         
);

removeasparentworkspace

procedure removeasparentworkspace (
 mp_leaf_workspace               in varchar2                        ,
 parent_workspace                in varchar2                        ,
 auto_commit                     in boolean                  default
);

removeworkspace

procedure removeworkspace (
 workspace                       in varchar2                        ,
 auto_commit                     in boolean                  default
);

removeworkspacetree

procedure removeworkspacetree (
 workspace                       in varchar2                        ,
 auto_commit                     in boolean                  default
);

resolveconflicts

procedure resolveconflicts (
 workspace                       in varchar2                        ,
 table_name                      in varchar2                        ,
 where_clause                    in varchar2                        ,
 keep                            in varchar2                        
);

revokegraphpriv

procedure revokegraphpriv (
 priv_types                      in varchar2                        ,
 leaf_workspace                  in varchar2                        ,
 grantee                         in varchar2                        ,
 node_types                      in varchar2                 default,
 auto_commit                     in boolean                  default
);

revokesystempriv

procedure revokesystempriv (
 priv_types                      in varchar2                        ,
 grantee                         in varchar2                        ,
 auto_commit                     in boolean                  default
);

revokeworkspacepriv

procedure revokeworkspacepriv (
 priv_types                      in varchar2                        ,
 workspace                       in varchar2                        ,
 grantee                         in varchar2                        ,
 auto_commit                     in boolean                  default
);

rollbackbulkloading

procedure rollbackbulkloading (
 table_name                      in varchar2                        ,
 ignore_last_error               in boolean                  default
);

rollbackddl

procedure rollbackddl (
 table_name                      in varchar2                        
);

rollbackresolve

procedure rollbackresolve (
 workspace                       in varchar2                        
);

rollbacktable

procedure rollbacktable (
 workspace                       in varchar2                        ,
 table_id                        in varchar2                        ,
 sp_name                         in varchar2                 default,
 where_clause                    in varchar2                 default,
 remove_locks                    in boolean                  default,
 auto_commit                     in boolean                  default
);

rollbacktosp

procedure rollbacktosp (
 workspace                       in varchar2                        ,
 savepoint_name                  in varchar2                        ,
 auto_commit                     in boolean                  default
);

rollbackworkspace

procedure rollbackworkspace (
 workspace                       in varchar2                        ,
 auto_commit                     in boolean                  default
);

setcaptureevent

procedure setcaptureevent (
 event_name                      in varchar2                        ,
 capture                         in varchar2                 default
);

setcompressworkspace

procedure setcompressworkspace (
 workspace                       in varchar2                        ,
 firstsp                         in varchar2                 default,
 secondsp                        in varchar2                 default
);

setconflictworkspace

procedure setconflictworkspace (
 workspace                       in varchar2                        
);

setdiffversions

procedure setdiffversions (
 workspace1                      in varchar2                        ,
 savepoint1                      in varchar2                        ,
 workspace2                      in varchar2                        ,
 savepoint2                      in varchar2                        
);

procedure setdiffversions (
 workspace1                      in varchar2                        ,
 workspace2                      in varchar2                        
);

setlockingoff

procedure setlockingoff (
);

setlockingon

procedure setlockingon (
 lockmode                        in varchar2                        
);

setmultiworkspaces

procedure setmultiworkspaces (
 workspaces                      in varchar2                        
);

setsystemparameter

procedure setsystemparameter (
 name                            in varchar2                        ,
 value                           in varchar2                        
);

settriggerevents

procedure settriggerevents (
 triggername                     in varchar2                        ,
 triggerevents                   in varchar2                        
);

setvalidtime

procedure setvalidtime (
 validfrom                       in timestamp with time zone  default,
 validtill                       in timestamp with time zone  default
);

setwooverwriteoff

procedure setwooverwriteoff (
);

setwooverwriteon

procedure setwooverwriteon (
);

setworkspacelockmodeoff

procedure setworkspacelockmodeoff (
 workspace                       in varchar2                        ,
 auto_commit                     in boolean                  default
);

setworkspacelockmodeon

procedure setworkspacelockmodeon (
 workspace                       in varchar2                        ,
 lockmode                        in varchar2                        ,
 override                        in boolean                  default,
 auto_commit                     in boolean                  default
);

syncfastlive

procedure syncfastlive (
 table_name                      in varchar2                        
);

synchronizesite

procedure synchronizesite (
 newwritersite                   in varchar2                        
);

unfreezeworkspace

procedure unfreezeworkspace (
 workspace                       in varchar2                        
);

unlockrows

procedure unlockrows (
 workspace                       in varchar2                        ,
 table_name                      in varchar2                        ,
 where_clause                    in varchar2                 default,
 all_or_user                     in varchar2                 default,
 lock_mode                       in varchar2                 default,
 xmin                            in number                   default,
 ymin                            in number                   default,
 xmax                            in number                   default,
 ymax                            in number                   default
);

until_changed

function until_changed returns timestamp with time zone (
);

usedefaultvaluesfornulls

procedure usedefaultvaluesfornulls (
 mode_var                        in varchar2                        
);