For workspace-level export operations, each version-enabled table can be exported at the workspace level. Follow these steps to export a version-enabled table from one database into another database: 1. Call the Export procedure to store all of the data that needs to be exported into a staging table for example, t1. The data that is exported can either be all of the data as seen from a particular workspace, savepoint, or instant, or only the data that was modified in the particular workspace. To export multiple workspaces for a version-enabled table, call the Export procedure again, specifying the new workspace that needs to be exported as well as the original staging table.
Oracle Database 12c: Workspace Manager
Export the staging table for example, t1 , using the Oracle Data Pump Export utility or the original Export utility. Import the staging table for example, t1 , using the Oracle Data Pump Import utility or the original Import utility, into the destination database. If you are importing into a version-enabled table, call the Import procedure to move the data from the staging table to the version-enabled table, specifying the workspace where the data resided on the source database and the workspace into which the data should be stored.
The structure of the staging table must match that of the version-enabled table. By default, all enabled constraints must be validated before the import procedure successfully completes.
- The Lost World of Thomas Jefferson.
- New BPEL and Web Services Blog By Mike Lehmann.
- ORACLE Software Suite.
- Understanding Leadership Perspectives: Theoretical and Practical Approaches.
- Still life with Murder (Nell Sweeny, Book 1).
You can perform both direct-path and conventional-path bulk loading of data into either the latest version of any workspace or into the root version version number 0, which is in the LIVE workspace. The root version is the ancestor of all other versions, so data in the root version is visible from all other workspaces unless non-LIVE workspaces have updated the data. Follow these general steps for bulk loading into a version-enabled table: 1. Call the BeginBulkLoading procedure to prepare the table for bulk loading. When data is being bulk loaded into a version-enabled table, DML and workspace operations on the table are not allowed, although workspace operations that do not involve this table are allowed.
The BeginBulkLoading procedure prevents invalid operations from being performed on this table. For example, assume that the existing control file has the following line: Load data into table departments name, loc. Note: In versions of Workspace Manager before This column is now automatically populated by Workspace Manager, and an error will be generated if you populate it explicitly. This ensures that all the bulk-loaded rows will be tagged with the appropriate version, and that the other Workspace Manager-specific columns for these rows will have null values.
Complete the bulk loading process by calling either the CommitBulkLoading procedure to commit the bulk loading changes or the RollbackBulkLoading procedure to roll back the bulk loading changes. If you commit the bulk loading changes, Workspace Manager ensures that the data is updated in the required workspace and version.
By default, the bulk-loaded data is checked for each unique or referential constraint defined on the table, and any bulk- loaded rows that are in violation of any constraints are moved to a discards table specified as a parameter to the CommitBulkLoading procedure. If you specified to check for duplicates that is, records in the data to be bulk loaded that have the same values in the primary key columns , for any duplicate records only the record with the lowest ROWID value is loaded into the table, and the rest are moved to the discards table.
Use the LockRows procedure to lock these rows. You cannot perform DDL operations in the usual manner on the table or any index or trigger that refers to the table.
For example, to add a column to a table named. The reason for these requirements is to ensure that Workspace Manager versioning metadata is updated to reflect the DDL changes. Note: An exception to this procedure is adding valid time support to an existing version-enabled table.
For information about Workspace Manager referential integrity support, see Referential Integrity Support. For information about Workspace Manager unique constraint support, see Unique Constraints. You can create the following types of indexes on version-enabled tables: normal, bitmap, function-based normal, function-based bitmap, invisible, reverse, and domain. You cannot create or drop a partitioned or join index on a version-enabled table. You can, however, version-enable a table that has a partitioned or join index.
Invisible columns are not supported in any DDL operations. If you try to perform an unsupported DDL operation, the change will not be made, and an exception might be raised by the CommitDDL procedure. Name Null? The child table is the one on which the constraint is defined. However, all tables that are involved in multilevel referential integrity constraints must be version-enabled and version-disabled together, unless all the referential integrity.
If all the constraints involved have the Restrict rule, you can version-enable the tables either all together or one at a time with child tables preceding their parent tables. The table names must be passed as a comma-delimited list to the EnableVersioning and DisableVersioning procedures.
If you need to add, drop, enable, or disable a referential integrity constraint that involves two tables, it is more convenient if you perform the operation before version- enabling the tables. However, you can add, drop, enable, or disable a referential integrity constraint that involves a version-enabled table if you follow these steps: 1. If the parent table has been version-enabled, begin a DDL session specifying the parent table.
Begin a DDL session specifying the child table. Commit the DDL changes specifying the child table. If the parent table has been version-enables, commit the DDL changes specifying the parent table. Example adds a foreign key constraint. If you are in a DDL session that is, if you have called the BeginDDL procedure , you cannot add, drop, enable, or disable a referential integrity constraint that involves two tables if one table is version-enabled and the other is not version-enabled. Both tables must be version-enabled. For example, during the time that a department is being deleted, new employees cannot be added and existing employees cannot be assigned to different departments.
Note: For general information about locking performed by Workspace Manager, including explanations of shared and exclusive locks, see Lock Management with Workspace Manager. Per-statement triggers are not supported. Any triggers that are not supported for version-enabled tables are deactivated when versioning is enabled, and are activated when versioning is disabled.
You can selectively enable specific user-defined triggers for certain kinds of events by using the SetTriggerEvents procedure. Virtual private databases are described in Oracle Database Security Guide. A call to MergeWorkspace will merge all the changes made in a workspace, not just the changes that the current user can see. When Workspace Manager looks for a table, it searches in the following sequence and uses the first match for the specified name: 1.
Oracle Workspace Manager + APEX + VPD
A table in the specified schema or local schema if no schema is specified 2. A private synonym in the specified schema or local schema if no schema is specified 3. A public synonym. You cannot version-enable a materialized view or the base table of a materialized view. When the materialized view is created, its content is based on the workspace in which the session is at that time. When the materialized view is created or refreshed, it shows the same data in all workspaces. If you want to version-enable any topology tables, you must version-enable all tables associated with the topology.
A version-enabled topology must have at least one feature table. That is, first call the EnableVersioning or DisableVersioning procedure on the feature table along with any tables required by the referential integrity constraint , and then invoke the EnableVersioning or DisableVersioning procedure specifying the topology name. The preceding example puts version locks on all the rows of the specified topology contained in the specified window.
- From Wikipedia, the free encyclopedia;
- Moving an application from 8i to 9i.
- Oracle 9i. SQL Reference (Part No. A) (Release ) - PDF Free Download.
To edit the elements of a topology in a workspace including the LIVE workspace , follow these steps:. Invoke the LockRows procedure to put version locks on all the elements of the topology contained in a window of interest. Instead, use the MergeWorkspace, RefreshWorkspace, or RollbackWorkspace procedure to merge, refresh, or roll back tables associated with a topology. Table lists kinds of objects and restrictions that apply to their names.
The subprograms can be logically grouped into the categories described in this section. Note: Most Workspace Manager subprograms are procedures, but a few are functions. A function returns a value; a procedure does not return a value. Table shows the subprograms available for table management. Procedure Description EnableVersioning Version-enables a table, creating the necessary structures to enable the table to support multiple versions of rows. DisableVersioning Deletes all support structures that were created to enable the table to support versioned rows. RecoverAllMigratingTables Attempts to complete the migration process on a table that was left in an inconsistent state after the Workspace Manager migration procedure failed.
RecoverAllMigratingTables Attempts to complete the migration process on all tables that were left in an inconsistent state after the Workspace Manager migration procedure failed. Export Exports data from a version-enabled table all rows, or as limited by any combination of several parameters to a staging table. Import Imports data from a staging table all rows, or as limited by any combination of several parameters into a version-enabled table in a specified workspace. Table shows the subprograms available for workspace management. Procedure Description CreateWorkspace Creates a new workspace in the database.
GotoWorkspace Moves the current session to the specified workspace. SetDiffVersions Finds differences in values in version-enabled tables for two savepoints and their common ancestor base. It creates rows in the differences views describing these differences.
Using OWB and the AWM To Create Analytic Workspaces
GetDiffVersions Returns the names of the workspace, savepoint pairs on which the session has performed the SetDiffVersions operation. MergeWorkspace Applies all changes in a workspace to its parent workspace, and optionally removes the workspace. RollbackWorkspace Discards all data changes made in the workspace to version- enabled tables. Procedure Description RollbackToSP Discards all data changes made in the workspace to version- enabled tables since the specified savepoint.
Oracle9i Database Release 2: New Features
RefreshWorkspace Applies to a workspace all changes made in its parent workspace. AlterWorkspace Modifies the description of a workspace. ChangeWorkspaceType Changes a workspace that is not continually refreshed to be continually refreshed. RemoveWorkspace Discards all row versions associated with a workspace and deletes the workspace.
RemoveWorkspaceTree Discards all row versions associated with a workspace and its descendant workspaces, and deletes the affected workspaces. FreezeWorkspace Restricts access to a workspace and the ability of users to make changes in the workspace.