Explain share and exclusive type of lock.

hmounir

 Profile Answers by hmounir Questions by hmounir

• Jan 25th, 2010

Oracle uses two modes of locking in a multi-user database:Exclusive lock mode (X) prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction t...

rahulmca

 Profile Answers by rahulmca Questions by rahulmca

• Jan 24th, 2010

The two basic types of locks are DML (table) locks, and DDL (dictionary) locks. DML locks are designed to guarantee data integrity in a multi-user environment. DML locks prevent conflicting DML and DD...

Answer Question  Select Best Answer

What is the meaning of execute immediately?

hmounir

 Profile Answers by hmounir Questions by hmounir

• Jan 25th, 2010

EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created a...

Brajesh Singh Bhadoriya

 Profile Answers by Brajesh Singh Bhadoriya Questions by Brajesh Singh Bhadoriya

• Jan 25th, 2010

Execute immediate is used to prepare dynamic SQL. There might be scenario where one has to prepare the SQL statement at run time in a PL/SQL object. Execute immediate requires one SQL statement which ...

Answer Question  Select Best Answer

If a deadlock has occured and if you have given with the dump, how will you identify that where the deadlock has occured, how the deadlock occured and how to rectify the same.

kari

• Oct 16th, 2011

the steps necessary to identify and rectify code causing deadlocks are: * Locate the error messages in the alert log. * Locate the relevant trace file(s). * Identify the SQL statements i...

skumar.t15

 Profile Answers by skumar.t15 Questions by skumar.t15

• Aug 5th, 2010

Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that c...

Answer Question  Select Best Answer

What is unconstrained loop?suggest some example.

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 27th, 2011

Did you mean infinite loop?

vikz.pal

 Profile Answers by vikz.pal Questions by vikz.pal

• Jan 23rd, 2011

Unconstrained loop means loop that can't be handled as per our will.Basic loop is a simple example of unconstrained loop - if we ignore proper exit statement in basic loop then it becomes an infin...

Answer Question  Select Best Answer

Susil Kumar Nagarajan

• Jan 21st, 2012

Use the combination of To_char and MIN functions.. to_char(min(created),dd-Mon-yyyy hh:mm:ss) Example:

Code
 

Hitendra Yadav

• Dec 15th, 2011

Just use min() function and find minimum timestamp from a table.

Answer Question  Select Best Answer

What is an autonomous transaction?

Sonal

• Nov 8th, 2012

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without ...

VASU

• Oct 20th, 2012

BY USING AUTONOMOUS TRANSACTION
WE GET THE FOLLOWING ADVANTAGES
WE PROCEDURE HAVING TRANSACTIONS STATEMENTS WE CALL PROCEDURE AUTOMATICALLY EFFECT THE ABOVE TRANSACTIONS ALSO TO OVERCOME THAT PROBLEM WE HAVE TO USE AUTONOMOUS TRANSACTION..

Answer Question  Select Best Answer

We have a oracle package that package is calling in user defined package ?from this user defined package how will u debug the oracle package?

ChandraSekhar thimmapthruni

• Apr 16th, 2015

Use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Rohit

• Jan 6th, 2013

Using trace_on utility

eg:- For HRMS hr_utility.trace_on ;
expression(here call the package which you want to debug);
HRMS hr_utility.trace_off;

Answer Question  Select Best Answer

Is it possible to use function inside a trigger.

tinku981

 Profile Answers by tinku981 Questions by tinku981

• Jun 16th, 2013

Code
span style="font-style: italic;">--referencing old as old and new as new

tinku981

 Profile Answers by tinku981 Questions by tinku981

• Jun 16th, 2013

Yes, by using CALL statement.

Answer Question  Select Best Answer

Difference between PL/SQL table and Global Temporary Table. why we use Global Temporary Table without using PL/SQL Table.

nivi

• Sep 3rd, 2013

1.Global Temporary table is stored in Temporary tablespace while PL/SQL table is stored in PGA. 2.SQL operations can not be performed on pl/sql tables whereas it can be performed on GTT as any normal...

plsqlgeek

 Profile Answers by plsqlgeek Questions by plsqlgeek

• Aug 16th, 2013

PL/SQL table or associated array is a collection (data type). Where as GTT is a temp table created to store/process data for a particular session. Once you log out of the session the data is gone, not the table structure.

Answer Question  Select Best Answer

How to call/reference a cursor defined in a procedure A from another procedure B?

How to pass a cursor as a parameter of a cursor?

UTTAM GHOSH

• Oct 31st, 2013

How I can an Ac dynamo of a car/vehicle test with a12v dc battery good or bad

Pavan

• Aug 27th, 2013

This can be done by using REFCURSORS.

Answer Question  Select Best Answer

What are bind variables in sql? explain in detail?

pradipta

 Profile Answers by pradipta Questions by pradipta

• Feb 8th, 2014

The way to get Oracle to reuse the execution plans for "the statements having different hard coded value in where clause" there should use bind variables. Bind variables are ?substitution? variable...

Aarti

• Jan 15th, 2014

Bind variable is session specific variable. It is declared using variable keyword. Bind variable cant be used in named block. It cant have default value.

Answer Question  Select Best Answer

When use Before and After Trigger? Please any help for this one

chaithanya

• Jul 1st, 2014

Before trigger is used while performing DML operations and whereas After Trigger is used for after performing DML operations Real time Example for before trigger: in banking systems: ...

Mrutyunjay Nayak

• Jul 1st, 2014

Before Trigger is used only when u want to restrict the invalid data entry in a table.After trigger is used when u perform the operation in one table that will effect on other table.

Answer Question  Select Best Answer

Raviteja

 Profile Answers by Raviteja Questions by Raviteja

• Aug 23rd, 2014

Instead of cursor is not there,, instead of trigger is used incase of complex views. when we use views with joins

Kumar

• Jul 25th, 2014

Its instead of trigger not cursor and it is used to apply trigger on views.

Answer Question  Select Best Answer

What is recursive function, explain with example?

Vishant Gupta

• Oct 11th, 2014

It is function that calls itself to implement some repetitive task

VIVEK

• Sep 24th, 2014

Function is calling the same function

Answer Question  Select Best Answer

How can you move the entire data from one table to another? If in same instance and in different instances, both cases?

KARTHIKA

• Nov 7th, 2014

Use the below code to perform the task

Code
 

A MOHAN KUMAR

• Nov 7th, 2014

INSERT INTO < old table > SELECT * FROM < new table > Note the table should have same column name and their respective data types as old table

Code
 

Answer Question  Select Best Answer

How to optimise your pl/sql code? Not using explain plan which is for tuning the quey

Nikhil Inamdar

 Profile Answers by Nikhil Inamdar Questions by Nikhil Inamdar

• Jul 8th, 2016

You can use DBMS_PROFILER to check performance bottlenecks and then change the code where the PLSQL code is taking more time and improve performence.

For more details go through below link.
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_profil.htm

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

The best technique used is to avoid repeatative coding by putting repeated logic in a subroutine/function. Save the DML operations after a specific interval or records insertion/updation/deletions , depending on your archive log size.

Answer Question  Select Best Answer

A table has both before insert and after insert trigger. Also we have declared both the trigger as pragma autonomous transaction for saving the error record alone in the error table if any exception occurs. Now if we have a error in the trigger will the before insert trigger will get executed and the error data will be stored in the error table?. what is the exact use of before and after insert trigger...

DInesh Shukla

• Apr 16th, 2015

As we declared pragma here. It will insert the record in error table if got fail during execution.

sukrathik

 Profile Answers by sukrathik Questions by sukrathik

• Mar 6th, 2015

If an autonomous transaction attempts to access a resource held by the main transaction, a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exc...

Answer Question  Select Best Answer

Is it possible to define a RETURN statement in the exception part of a function?
Explain it with example.

Aryan

• May 26th, 2015

Yes it is possible to use Return in exception block of pl/sql. Example: "oracle8 CREATE OR REPLACE FUNCTION [(input/output variable declarations)] RETURN return_type [AUTHID ...

Kamal

• May 23rd, 2015

Yes, you can return from exception block. example: create or replace function TestReturn return varchar2 is Result varchar2(1);--size 1 begin Result := 123;--size 3 return Test; except...

Answer Question  Select Best Answer

Can we keep trigger name same as table name? Explain

Manisha

• Sep 21st, 2016

Trigger names must be unique with respect to other triggers in the same schema. Trigger names need not be unique with respect to other schema objects, such as tables, views, and subprograms. For examp...

Govind

• Sep 13th, 2016

No. Object Names should be unique.

Answer Question  Select Best Answer

How will you check performance of the query, if the query was running earlier but today its taking time to run?

inamdnik

 Profile Answers by inamdnik Questions by inamdnik

• Feb 10th, 2017

There are various ways to check performance of query. 1. Use EXPLAIN PLAN if you want to check execution plan used by oracle optimizer to run a query(This is more session specific.) 2. Use AUTOTRACE...

Subhapratim

• Feb 9th, 2017

We can check from AWR report

Answer Question  Select Best Answer