Locks , Dummy Table

To get the locks on an object

SELECT oracle_username USERNAME,owner OBJECT_OWNER,object_name, object_type, s.osuser,s.SID SID,s.SERIAL# SERIAL,DECODE(l.block,
0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS, DECODE(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode) ) MODE_HELD
FROM gv$locked_object v, dba_objects d,gv$lock l, gv$session s WHERE v.object_id = d.object_id AND (v.object_id = l.id1) and v.session_id = s.sid ORDER BY oracle_username, session_id;

-------------------------------------------------------------
To create our own much faster DUMMY Table


CREATE TABLE MYDUAL(DUMMY VARCHAR2(1) PRIMARY KEY CONSTRAINT ONE_ROW
CHECK(DUMMY='X')) ORGANIZATION INDEX;

Check Constraint

Check Constraint

CREATE TABLE EMP_DETAILS(
NAME VARCHAR2(20),
MARITAL CHAR(1) CHECK(MARITAL='S' OR MARITAL = 'M'),
CHILDREN NUMBER,
CHECK((MARITAL = 'S' AND CHILDREN=0) OR
(MARITAL = 'M' AND CHILDREN >=0)));

This constraint can only be kept at table level because it accesses more than one field. Now the valid values for CHILDREN field is based on MARITAL field. MARITAL is also one of 'S' or 'M'. If the field MARITAL is 'S' it will only allow 0 as a valid value. If the MARITAL field is 'M' it will allow either 0 or a value greater than 0.

Much More About Constraints

PRIMARY KEY LOOK UP DURING FOREIGN KEY CREATION


The lookup of matching primary keys at time of foreign key insertion takes time.In realease Oracle 9i , the first 256 primary keys can be cached so the addition of multiple foreign keys become significantly faster .The cache is only set up after the second row is processed.this avoid overhead of setting up a cache for single row DML.

----------------------
Constraints On View
--------------------
Constraint definitions are done on View from Oracle 9i onwards.
Views constraint definitions are declarative in nature;therefore DML operations on view are subject to the constraints defined on base tables.
defining constraints on base table is necessary ,not only for data correctness and cleanliness but also for MV query

NOT NULL and CHECK constraint are not supported on Views

For Creating View with constraints you must specify [RELY|NORELY ](Allows/disallows query rewrites) DISABLE NOVALIDATE (valid state for view constraint )
Otherwise it will result an error message.

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

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