Table Creation/// NOT NULL (check constraint)

Everything (Table Creation including Primary ,Foreign key ,Check constraint and giving user defined CONSTRAINT NAME for NOT NULL CONSTRAINT) in a single Script ;without using alter command for predefined constraints till design time


CREATE TABLE   STUDENTS_MAST(
N_STUDID NUMBER(5),CONSTRAINT PK_STUDID  PRIMARY KEY(N_STUDID))


CREATE TABLE STUDENT_TEAM (
N_STDTMID NUMBER(10) ,CONSTRAINT PK_STUDENT_TEAM PRIMARY KEY(N_STUDTMID),
N_STUDID NUMBER(10),CONSTRAINT FK_STUD_TEAM_EMPID FOREIGN KEY(N_STUDID) REFERENCES STUDENTS_MAST(N_STUDID),
C_TLFLAG CHAR(1) ,CONSTRAINT CK_STUD_TEAM_TLFLAG CHECK (C_TLFLAG IN ('Y','N')) ,
D_STDATE DATE CONSTRAINT NN_STUD_TEAM_STDATE NOT NULL,
D_ENDDATE DATE CONSTRAINT NN_STUD_TEAM_ENDATE NOT NULL,
D_ENTRYDAT DATE DEFAULT SYSDATE )


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

CREATE TABLE STUDENT_TEAM (N_STDTMID NUMBER(10) CONSTRAINT NN_STUDENT_STATUS_TMID NOT NULL)


ALTER TABLE STUDENT_STATUS ADD ( CONSTRAINT NN_ST_STATUS_TMID CHECK (N_STDTMID IS NOT NULL))


Both Scripts will restrict not null values but error code will be different first script results


ORA-01407: cannot update to NULL

ORA-01400: cannot insert NULL into N_STDTMID

second script results


Another difference is the null(nullable) field will be true when null is checked by first script but it will not be for second second script
-----------
During check constraint creation if already exists some records in the particular table which violates the new check constraint.This error will be displayed
ORA-02293: cannot validate (##########) - check constraint violated

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

Oracle Wokspace 4

dbms_wm.gotodate

begin
dbms_wm.gotoworkspace('B_focus_1');
end;

insert into test values (6,'Wayanad','YS','n',null,sysdate);

select * from test order by one ;

begin
dbms_wm.gotodate( (sysdate-5)) ;
end;

select * from test order by one ;

begin
dbms_wm.gotoworkspace('B_focus_1');
end;

select * from test order by one ;

Freezeworkspace

begin
dbms_wm.freezeworkspace('B_focus_1', 'READ_ONLY');
end;

insert into test values (6,'Wayanad','YS','n',null,sysdate);


This Error will be throwed when we try any DML operations (here to insert data) into a readonly workspace.

ORA-20123: workspace 'B_focus_1' is currently frozen in READ_ONLY mode


Export And Import Implications
The following implications are a result of version-enabling tables:

* Imports of version-enabled tables can only be performed if the target database has Workspace Manager installed and no workspaces defined other than LIVE.
* Only full database exports are supported with version-enabled databases.
* The IGNORE=Y parameter must be set for imports of version enabled databases.
* Imports of version-enabled databases cannot use the FROMUSER and TOUSER functioanlity.

For More about workspaces Refer  here

Oracle Wokspace 3

To Refresh workspace

begin
dbms_wm.gotoworkspace('B_focus_9');
dbms_wm.refreshworkspace('B_focus_9');
end;

But if there are conflicts refreshworkspace will result an error

ORA-20056: conflicts detected for workspace: 'B_focus_9' in table: 'USER.TEST'

LIVE WORKSPACE CANNOT BE REFRESHED

The two rows that changed in both the LIVE and the 'B_focus_9' workspace create a conflict. These conflicts can be seen from the view Test_conf. But first go to the 'B_focus_9' workspace. The conflicts are not visible in this view while being in the LIVE workspace.

--------------------------
To Resolve this conflicts

SELECT * FROM test_conf ORDER BY wm_workspace;

begin
dbms_wm.gotoworkspace('B_focus_9');
dbms_wm.beginresolve('B_focus_9');
dbms_wm.resolveconflicts('B_focus_9','TEST',null/*where clause*/,'PARENT');
commit;
end;

begin
dbms_wm.commitresolve('B_focus_9');
end;

SELECT * FROM test_conf ORDER BY wm_workspace;

After resolving this conflict the workspace can be refreshed

begin
dbms_wm.refreshworkspace('B_focus_9');
end;


Merge Workspace

begin
dbms_wm.mergeworkspace('B_focus_9');
end;


When B_focus_9 workspace is merged into the LIVE workspace. A merge is the opposite of a refresh : it updates the parent workspace (LIVE) with the changes made to the merged workspace (B_focus_9). Where as refresh operation updates the refreshing workspace.
---------------------------------

After MergeWorkspace See Difference

begin
dbms_wm.gotoworkspace('B_focus_1');
end;

select * from test order by one




begin
dbms_wm.gotoworkspace('B_focus_9');
end;

select * from test order by one




begin
dbms_wm.gotoworkspace('LIVE');
end;

select * from test order by one



Here parent workspace (LIVE) was updated by merged workspace (B_focus_9) .

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

Oracle Workspace 2

The merge operation does not currently work with versioned tables. The optimizer translates the merge into
insert/update statements on the underlying _LT table

To have several versions of data

create table TEST (
one number primary key, -- Without primary key: ORA-20133:
two varchar2(15), three char(2), four clob,
five blob, six date );

insert into test values (1,'Cochin','NO','Kochi is a vibrant city situated on the south-west coast of the Indian peninsula in the breathtakingly scenic and prosperous state of Kerala, hailed as Gods Own Country',null,sysdate);

insert into test values (2,'Kerala','YS','n',null,sysdate);
insert into test values (3,'TVM','NO','n',null,sysdate);
insert into test values (4,'Calicut','NO','n',null,sysdate);
insert into test values (5,'Kannur','YS','n',null,sysdate);

commit;

begin
dbms_wm.enableversioning ('test');
end;

begin
dbms_wm.createworkspace ('B_focus_1');
dbms_wm.createworkspace ('B_focus_9' );
end;

begin
dbms_wm.gotoworkspace('B_focus_1');
dbms_wm.createworkspace ('test' );
end;

select workspace, parent_workspace from user_workspaces;

select * from test order by one;

update test set two='Kasargode',three='NO',four='Top end of Kerala',Six=sysdate where one=2;

commit;



begin
dbms_wm.gotoworkspace('LIVE');
end;

select * from test order by one



begin
dbms_wm.gotoworkspace('B_focus_9');
end;

select * from test order by one

update test set two='Thrissur',three='NO',four='Middle of Kerala',Six=sysdate where one=2;
commit;

select * from test order by one



begin
dbms_wm.gotoworkspace('test');
end;

select * from test order by one

Oracle Workspace 1

Workspace Manager PL/SQL APIs

The PL/SQL APIs in the DBMS_WM package can be executed

* Workspace operations: create,refresh,merge,rollback remove,goto,compress,alter
* Savepoints: create, alter, goto
* History: goto date
* Privileges: access, create, delete, rollback and merge workspace
* Access Modes: read, write, management or no access to workspaces
* Locks: exclusive and shared workspace locks to prevent data update conflicts
* Find Differences: compare savepoints and workspaces
* Detect / Resolve Conflicts: Automatically detect and resolve conflicts

When a particular table is versioned primary key index is rebuild version field is added with the current primary key fields inorder to uniquely identify each row version .Each table that is versioned with the workspace manager must have a primary key.No issue if there is a Foreign Key

To get Lock Mode

SELECT dbms_wm.GetLockMode FROM dual;


Some Errors
-----------

(I)ORA-20101: child table must be version enabled (This error occur if TEST table is referenced by another table TESTCHILD ;if TESTCHILD is version enabled then only TEST table can be versioned )


(II)ORA-20100: 'USER.TEST' is both parent and child tables of referential integrity constraints

If a field of TEST table is referenced by another field of TEST table itself such tables cannot be version-
enabled ie:( In Scott.EMP table MGR field is referenced by EMPNO field )

ALTER TABLE EMP ADD CONSTRAINT FK_EMP
FOREIGN KEY (MGR) REFERENCES EMP (EMPNO) )

(III) Temporary Table cannot be version enabled

ORA-20229:statement 'CREATE INDEX TMP_TEST_PKI$ on TMP_TEST_LT(A)LOGGING PCTFREE 10 INITRANS 2 M' failed during EnableVersioning.Error:
ORA-14451: unsupported feature with temporary table

Referential integrity constraints cannot be added after versioning is enabled. They must be present before version-enabling
------------------*------------------*---------------------*----------------

EXEC DBMS_WM.FINDRICSET('B_focus_1', 'RICTEST') ; --CREATE A TABLE WITH FIELD TABLE_OWNER & TABLE_NAME WITH DATA

SELECT dbms_wm.GetPrivs('LIVE') FROM dual ; //GET Privileges of particular workspace

To Get Lock Mode
SELECT dbms_wm.GetLockMode FROM dual ;


DECLARE
lockmode varchar2(1);
begin
lockMode := sys.lt_ctx_pkg.lock_Mode;
dbms_output.put_line(lockMode);
end;


The min & max time that Oracle supports
SELECT DBMS_WM.max_time,DBMS_WM.min_time FROM DUAL; (from 10g onwards)