Feb 10, 2010

Insert Statements

Insert into (select S_DESIGNATION, S_DESCRIPTIONDESG from designation_mast where N_DESGCODE=2) values ( 'tttttttttt','ere')

which is similar to

Insert into designation_mast(S_DESIGNATION, S_DESCRIPTIONDESG) values ( 'tttttttttt','ere')

Feb 2, 2010

Oracle Workspace Manager

Difference between Long Transaction (Oracle WorkSpace) and Short Transaction (Normal )

Long Transactions

Complete over days or weeks
Likelihood of conflict is low - Optimistic concurrency permits conflicts

Selective versioning tables
Update creates a new row version
Each update is part of a short transaction

Collections of updates isolated in workspaces until merged into production

Multi user update

Short Transactions
Complete in seconds or less
Likelihood of conflict is high - Pesimistic concurrency permits conflicts
Data is in a single state
Updates accessible upon commit
Single user updates


WorkSpace Manager Operations

Workspace : Create , Refresh,merge,rollback,remove,goto,compress,alter
Savepoints : Create , Alter,Goto,Compare,Rollback and Delete
History : Gotodate
Privileges : Access,Create,Delete,Rollback,Merge
Locks : Exclusive and Shared (Exclusive lock prevents changes by any other user Shared locks allow other users in the workspace to change row )
Differences : Compares savepoints and workspaces
Detect /Resolve Conflicts : choose version to merge.

Since the versioning process removes the physical unique index from the base table, multiple session would be allowed to enter the same value into a column that has a unique constraint defined on it. Since the changes by the other session might be part of an uncommitted transaction, the instead of triggers that are defined on the view are unable to enforce the constraint in this particular case. As a result,OWM use the _LCK VIEW to prevent this scenario by maintaining a unique constraint on the underlying table which the dmls are applied to.

Disabling Versioning for a table

Disable versioning when changes to the version-enabled table are completed
Improves performance
Workspace hierarchy and savepoints remain
The latest version of each row in LIVE workspace remains.

Freeze WorkSpace
Freezing a workspace specifies the kind of user access allowed to the workspace .

NO_ACCESS is default
READ_ONLY allows all workspace users to read.
1WRITER: Sessions are allowed in the workspace, but only one user
1WRITER_SESSION: Sessions are allowed in the workspace, but only the database session (as opposed to the database user) that called the FreezeWorkspace procedure is allowed to perform write operations
WM_ONLY: Only Workspace Manager operations are permitted.