My Profile

My Profile

Oracle Deferred Constraints :
Recently we came across a unique situation of updating a primary key of master table which being referenced by child table.Problem with updating primary key of master table was that it would have made child table orphan (ora-02292 child found)and we could not even update child table as it would have violated referential integrity.

To overcome this we used the deferrable constraint introduced by oracle.A deferrable constraint is only checked at transaction commit time.We altered the foreign key on child table to make constraint deferrable .After altering the foreign key constraint on child table we were able to update master table primary key with new values as constraint checking was deferred till commit point.

Now we will explain the deferrable constraints in detail.

Constraint are of three types
1) Non deferrable (Check constraint at time of commit and can not be deferred)
2) Deferrable-Initially immediate (Check constraint at time of statement execution )
3) Deferrable-Initially deferred (Check constraint at time of commit)

SQL> create table test_def (a varchar2(1) constraint chk_a check ( a in ('X','Y','Z') )
2 deferrable
3* initially immediate);
Table created.
SQL> insert into test_def values ('C');insert into test_def values ('C');

*ERROR at line 1:ORA-02290: check constraint (ITN.CHK_A) violated

Error as constraint is being checked at time of statement execution. Now we will change constraint to initially deferred.

SQL> alter table test_def modify constraint chk_a initially deferred;
Table altered.
SQL> insert into test_def values ('C');
1 row created.
SQL> commit ;
*ERROR at line 1:ORA-02091: transaction rolled back ORA-02290: check constraint (ITN.CHK_A) violated

As constraint is initially deferred it is being checked at commit type.
When should we use deferrable constraint :
Deferred integrity constraints should only be used when absolutely necessary but following are some scenario where deferrable constraint are used

1) In case of inserting large amounts of data into a table in a data warehousing environment lot of time can be saved.
2) Design issues (Updating Primary Key of Parent table in Parent Child table scenario).
Which constraints are deferrable? Only constraint created with the deferrable option can be deferred.by default constraints created are non-deferrable.

How to check constraint is deferrable ?
SELECT constraint_name , deferrable , deferred
FROM user_constraints
WHERE constraint_name like 'test_def%';

This article will not be complete without mention of option No Validate with constraints.

No Validate option with constraint : No Validate option will direct Oracle to enable the constraint but not to check existing data for constraint violation.This is particular useful where we have existing data that violates the constraint but urgent business requirement to enable constraints to avoid any future data with constraint violation.

If you have any question please contact us at support@ITNirvanas.com My Profile

Synonym : A synonym is an alias for table, stored procedure and other database objects.

Main use of synonym :
1. Hide Complexity : If you have to access a object owned by another database user then
You have to use syntax Owner.Objectname every time to use that object. Instead of using cumbersome syntax every time we can create synonym for that
Create Synonym Syn_Name for Owner.Objectname
Suppose you have to access Table Tab_Y owner by user User_Y .Before creating synonym you have to use
Select * from User_Y.Tab_Y
Now if we create a synonym
Create Synonym Tab_Y for User_Y.Tab_Y
After creating synonym you can use
Select * from Tab_Y
Note : The object does not need to exist at the time of its creation

2 .Hide Owner/Location : In some applications application designers do not want to reveal the owner and location of object to other user. To achieve this they create synonym
Type of synonym : There are primarily two type of synonym
1. Private : Only owner of synonym can use private synonym By default synonym created is private
Create Synonym Tab_Y for User_Y.Tab_Y .
2. Public : Everyone can use public synonym. Syntax for creating public synonym
Create Public Synonym Tab_Y for User_Y.Tab_Y .

Order of precedence for synonym :
There are lot of confusion when both object and synonym exist in database, which one will be used first. Following is order of precedence
1. Local objects will always be accessed first.
2. If a local object does not exist, the object with a private synonym will be accessed.
3. If a private synonym does not exist or the object does not exist, then the public synonym will be used.

Flip Side of using synonym :
1. There are security issues related with public synonym
2. If there are lot of synonym in database then database performance will degrade i.e. suppose user want to query Tab_A in above mentioned example database parser have to go across large set of synonym and have to put each synonym in library cache along with dependency.

Alternative to using synonyms:
Use logon trigger to set ALTER SESSION SET CURRENT_SCHEMA This will make default schema for unqualified object
create or replace trigger trg_at_logon on database after logonbegin
if user in ('X','Y') then execute immediate 'alter session set current_schema=’DFLT’;
end if
end;
now if user X,Y login their default schema for unqualified object will be DFLT
Misc point about synonym :
We cannot use Drop and truncate command with synonym

Removing Synonyms :
We can use following syntax to drop private synonym
Drop synonym syn_name
To drop public synonnynm
Drop Public synonym syn_name
We can also use force command
Drop synonym syn_name force
The force syntax will force Oracle to drop the synonym even if it has dependencies. It is not a good idea to use the force phrase as it can cause invalidation of Oracle objects.

Query to find Synonym use in database :
Suppose we want to object that reference synonynm test_syn
Select owner, name, type
From dba_dependencies
Where referenced_name =
'TEST_SYN'
and referenced_type =
'SYNONYM'

Common Error with Synonym :
One of the most common error encountered with synonynm is
"ORA-00980: synonym translation is no longer valid"
Main causes for this to happen are
1. One has created a synonym on non-existing object by mistake.
2. You dropped an object but did not drop the synonyms which are referencing the object.

My Profile

Inline view :

An inline view is term given to sub query in FROM clause of query which can be used as table. Inline view effectively is a named sub query

Ex : Select Tab1.col1,Tab1.col.2,Inview.col1,Inview.Col2
From Tab1, (Select statement) Inview
Where Tab1.col1=Inview.col1

SELECT DNAME, ENAME, SAL FROM EMP ,
(SELECT DNAME, DEPTNO FROM DEPT) D
WHERE A.DEPTNO = B.DEPTNO

In the above query (SELECT DNAME, DEPTNO FROM DEPT) D is the inline view.

Inline views are determined at runtime, and in contrast to normal view they are not stored in the data dictionary,

There are lot of instances when we want data from sub query to available in main query, before oracle 7.3 only option was to create view and use that in sub query

Disadvantage of using this is

1.Separate view need to be created which is an overhead
2.Extra time taken in parsing of view

This problem is solved by inline view by using select statement in sub query and using that as table.

Advantage of using inline views:

1. Better query performance
2. Better visibility of code

Practical use of Inline views:

1. Joining Grouped data with Non grouped data
2. Getting data to use in another query

I wll further add in this post tommorow with practical example of inline views. My Profile

High-water mark

High-water mark indicates the point up to which table or index has ever contained data.Let me explain .Suppose we create an empty table,the high-water mark would be at the start of the table segment


Now if we insert some data into the table segment, the HWM moves and it will be at position up to which the data is in the segment


If insert more data in the table segment, HWM moves further to point the position up to which the data is in the segment

This is one of most frequently asked question in Oracle interviews.
I tried to put all the differences i know,you are welcome to put your thoughts and comment on that

Delete

1. It is a DML statement


2. Can Rollback


3. Can delete selective records


4. It fires database triggers.


5. It does not requires disabling of referential constraints


6. Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all records have been deletedretains all of its original blocks.

Truncate

1. It is a DDL statement
2. Can’t Rollback
3. Can’t delete selective records.It will delete all the records in table


4. Doesn't fire database triggers


5. It requires disabling of referential constraints


6. Truncate moves the High Water Mark of the table back to zero.No row-level locks are taken,no redo or rollback is generated.