What are Deadlock queries and how to identify the locked SQL statements.

Deadlock condition occurs when two or more user is trying to perform the operation on the same data or in other words transaction override explicitly so the record is locked by each other.

Suppose we have two sessions and we are trying to update the same record. 

For E.g:- 

Session 1:-  Suppose we have session-id “101” for session 1.

Query:- Create Table Emp (Id Number(10),Name Varchar(100));

O/P:- Table Created

Now we are inserting the records into the table

Query:- Insert Into Emp Values(1,'Jon');

Insert Into Emp Values(2,'Greg');

Commit;

O/P:- 1 row inserted.

          1 row inserted.

          Commit complete.

Query:- Select * from Emp;

O/P:-

Id	Name
1	Jon
2	Greg

Condition:-  Now we will update the name of the employee for id 2 but we will not commit this transaction.

Query:- Update Emp Set Name=’Ted’ where id=2;

After this we are starting a new session.

Session 2:- Here we have session-id is “201” for session 2.

In this new session “201” we are will try to update the same record where id is 2.

Query:- Update Emp Set Name=’Paul’ where id=2;

This time DeadLock condition occurs and database enter into the deadlock trap, We need to execute the below query from third session to check what’s going on? why query is taking so much time to update the single record in session 2 (i.e session-id “201”).

Query:- SELECT 'Session-Id :'||' '||v1.sid ||' '||'Is Blocking'||' Session-id: '

            || v2.sid as SessionLockInfo,

Qr.sql_fulltext as QueryLocked

from   v$lock v1, v$lock v2 ,

(select a.sid, a.username,b.sql_id, b.sql_fulltext from v$session a, v$sql b

where a.sql_id = b.sql_id and a.status = 'ACTIVE' and a.username != 'SYS') Qr

where  v1.block =1 and v2.request > 0

and v1.id1=v2.id1 and v1.id2=v2.id2 and v2.sid =Qr.sid;

O/P:-

SessionLockInfo	QueryLocked
Session-Id : 101 Is Blocking Session-id: 201	Update Emp set Name='Paul' Where id=2

Deadlock condition automatically clear once the transaction for session 1(i.e session-id “101”) is completed or canceled, transaction completed means the user needs to hit the COMMIT or ROLLBACK statement in the session 1(i.e session-id “101”), then the session automatically releases.

What other ideas can you add to this post that I may have not mentioned?

Compare two arguments in oracle through "NULLIF" function

Oracle NULLIF() function accepts two arguments. It returns a null value if both arguments are equal. In case the arguments are not equal, the NULLIF() function returns the first argument.

For E.g:-

Condition 1:- If Expression 1 and Expression 2 are the same then it will return the NULL value.

Query:- SELECT NULLIF('Database Dynasty', 'Database Dynasty') as CheckArgument 

               FROM dual;

O/P:- Null

Condition 2:- If Expression 1 and Expression 2 are different then it will return the first argument.

Query :- SELECT NULLIF('Database', 'Database Dynasty') as CheckArgument 

               FROM dual;

O/P:- Database

What other ideas can you add to this post that I may have not mentioned?

Query to check the list of the dependent tables used in view

Suppose we have a requirement to generate the report of fetch the data from the database that shows the information about the view like schema where the view is created, referenced owner, table etc.

Query:-

select owner as View_Schema,

       name as View_Name,

       referenced_owner as Referenced_Schema,

       referenced_name as Referenced_Name,

       Referenced_Type

from sys.dba_dependencies

where type='VIEW'

      and referenced_type in ('TABLE','VIEW')

      and owner = 'Enter the name of the schema'

      and name = 'Enter the name of the view' 

order by owner, view_name;  

O/P:-

View_Schema	View_Name	Referenced_Schema	Referenced_Name	Referenced_Type
EMP	Vw_EmployeeDetails	HR	EmployeeDetails	Table

View_Schema          -> Where the view is created

View_Name               -> Name of the view

Referenced_Schema-> Referenced schema where the table is available i.e used in the view.

Referenced_Type      -> Type of reference like table

What other ideas can you add to this post that I may have not mentioned?

What is self join in oracle database?

A self-join is a join in which a table is joined with itself. To join a table itself means that each row of the table is combined with itself and with every other row of the table.

For E.g:

Suppose we have a table called “SelfJoin” and their data shown below

Query:- Select * from SelfJoin;

O/P:-

EmpId	Name	MgrId
1	Paul
2	Greg	1
3	Jon
4	Samy	3

In the table we have Employee’s id i.e EmpId, Name of the Employee and Manager of those employees i.e MgrId.

Condition:- Fetch the name of the employee and their managers.

Query:- Select M.Name ||' is the employee and '||E.Name||' their manager' as Details

from SelfJoin E,SelfJoin M

where M.mgrid=E.EmpId;

O/P:-

Details

Greg is the employee and Paul their manager

Samy is the employee and Jon their manager

Here the table “SelfJoin” is joined itself on the basis of EmpId and MgrId.

What is a sequence in oracle database?

A sequence is an object in oracle database i.e used to generate a number sequence. This can be useful when you need to create a unique number that acts as the primary key.

E.g:-

Create Sequence SEQ_TEST

MinValue 1

MaxValue 999999999

Increment by 1

Start With 1

NoCache NoOrder NoCycle;

Note:- Here SEQ_TEST is the name of the sequence, then after we need to set the parameter according to our conditions and requirements.

Call the Sequence in select statement:-

Query:- select SEQ_TEST1.nextval from dual;

Check the list of sequences created in database user:

Query:- Select  * from User_Sequences;

Conditions:-
* When we have a requirement to change the sequence increment number from 1 to increment by 5 then

Query:-  Alter Sequence SEQ_TEST increment by 5;

*When we a condition to restart the sequence then
Query:-  Alter Sequence SEQ_TEST Restart With 1;

* In this sequence code we have set NoCache, Suppose we have set Cache 5 then it will store the upcoming 5 number in the memory and the LAST_NUMBER of the sequence as per the cache i.e +5 it will update automatically.

Query to update or change the cache value in sequence:-
Alter Sequence SEQ_TEST Cache 5; ----> (Here it will set the cache 5 in your sequence)

What is the difference between DenseRank and Rank function in oracle?

RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK again gives you the ranking within your ordered partition, but no ranks are skipped if there are ranks with multiple items.

E.g:- Suppose we have a table called SalaryDetails as shown below

Query:- Select * from SalaryDetails;

O/P:-

Id	EmployeeDetailsId	Salary
101	1	2000.50
102	2	3050.50
103	3	4000.00
104	4	2000.50
105	5	2000.50

Condition:- Fetch the records from the table and assign the rank to the EmployeeDetailsId on the basis of salary ascending to descending order.

Query:- Select EmployeeDetailsId,Salary,

                            DENSE_RANK() Over(Order By Salary) as DenseRank_Salary,

                           Rank() Over(Order by Salary) as Rank_Salary

              from SalaryDetails;

O/P:-

EmployeeDetailsId	Salary	DenseRank_Salary	Rank_Salary
1	2000.50	1	1
5	2000.50	1	1
4	2000.50	1	1
2	3050.50	2	4
3	4000.50	3	5

How to Use Case Statement with Order By Clause in Oracle?

Suppose we have a condition to get the result from SQL query on the basis of check conditions.
E.g:- we have a table called EmpoyeeDetails

EmployeeDetails
Id	Name
101	Ravi
102	Yash
103	Harsh

Condition:- We have a condition to fetch the Id, Name from EmployeeDetails table when Name is “Yash” then it will come in first priority, when the name is “Ravi” then it will come in the second priority then rest of the records.

Query:-  select emp.* from EMPLOYEESDETAILS emp ------> (Emp is alias of the table)

               order by case when emp.Name='Yash' then 1

                                       when emp.Name='Ravi' then 2 else 3

                               end;

O/P:-

EmployeeDetails
Id	Name
102	Yash
101	Ravi
103	Harsh

Difference between Scalar Function, Table-Valued function, and Aggregate Function in Oracle

Scalar Function:-  Scalar Function are the function that returns a single value, not the result set. we can not use aggregate functions in the scalar function because scalar functions execute row by row, not in the set of values.

E.g:- Upper(), Lower(),Length() etc.

Query:- Select Upper('database dynasty') from dual;

O/P:-      DATABASE DYNASTY

Table-Valued Function:- Table-Valued functions return result set and it can be used in  a From clause, Joins , Outer apply of any query but it can not target any DML operations like (Insert, Update, Delete).

E.g:- we have two tables one is EmployeesDetails and another one is SalaryDetails.

EmployeesDetails			SalaryDetails
Id	Name		Id	EmployeesDetailsId	Salary
1	Ravi		101	1	2000.50
2	Yash		102	2	3050.50

Create or replace Type Typ_EmployeesDetails as Object

(Id Number(10),

Name Varchar2(100));

O/P:- TYPE TYP_EMPLOYEESDETAILS compiled

Create or replace type Tbl_EmployeesDetails as table of Typ_EmployeesDetails;

O/P:- TYPE Tbl_EmployeesDetails compiled

Create or replace function FN_EmployeeDetails

return Tbl_EmployeesDetails

Is 

V_Emp Tbl_EmployeesDetails;

Begin

Select Typ_EmployeesDetails (Id,Name)

Bulk Collect into V_Emp

from EmployeesDetails;

Return V_Emp;

End;

/

O/P:- FUNCTION FN_EMPLOYEEDETAILS compiled

To Execute or use the function with join in select statement.

Select Emp.ID as EmployeeId, Emp.Name,s.Salary

from SalaryDetails s

Inner Join Table(FN_EmployeeDetails())Emp on Emp.Id=s.EmployeeDetailsId;

O/P:- 

EmployeeId	Name	Salary
1	Ravi	2000.50
2	Yash	3050.50

Aggregate Function:- Aggregate Function are user-defined aggregates like Sum(), Count(), Min(), Max() etc. and it typically requires the Group By clause. 

E.g:-  Suppose we have table called Emp 

Query:- Select * from Emp;

O/P:-   

Id	Name
101	Database
102	Dynasty

Condition:- Check the total number from records in Emp table
Query:- Select Count(*) from Emp;
O/P:-    2

How to Create a Package in Oracle?

PL/SQL package is like a library that once written stored in the Oracle database and can be used by many applications. For E.g:- 

Create Table EMP_Package

(Id Number(5),

Name Varchar2(100));

O/P:- table EMP_PACKAGE created.

Create or Replace Package PKG_Test as

Procedure Insert_Record (v_id in number,v_name in varchar2);

Procedure Update_Record (v_id in number,v_name in varchar2);

Procedure Delete_Record (v_id in number);

end;

/

O/P:-  PACKAGE PKG_TEST compiled

Note:- v_id and v_name are the input variables

Now we need to create the body of the package for the procedures that we have defined in the package.

Create or Replace Package Body PKG_Test as

--Procedure to Insert Record

Procedure Insert_Record (v_id in number,v_name in varchar2) as

begin

Insert into EMP_Package (id,name)

values (v_id,v_name);

commit;

end;

--Procedure to Update Record

Procedure Update_Record (v_id in number,v_name in varchar2) as

begin

Update EMP_Package

Set Name=v_name Where Id=v_id;

commit;

end;

--Procedure to Delete Record

Procedure Delete_Record (v_id in number) as

begin

Delete from EMP_Package Where Id=v_id;

commit;

end;

end;

/

O/P:- PACKAGE BODY PKG_TEST compiled

To execute the package follow the below commands:-

*Insert the record into the table i.e EMP_Package through package

  Exec PKG_Test.Insert_Record(1,'Database Dynasty');

  select * From EMP_Package;

  O/P:-

Id	Name
1	Database Dynasty

*Update the record into the table i.e EMP_Package through package

  Exec PKG_Test.Update_Record(1,'Database Dynasty Update');

  select * From EMP_Package;

  O/P:-

Id	Name
1	Database Dynasty Update

*Delete the record from the table i.e EMP_Package through package

  Exec PKG_Test.Delete_Record(1);

  select * From EMP_Package;

  O/P:-

Id	Name