What are the differences between database designing and database modeling?
If the large table contains thousands of records and the application is accessing 35% of the table, which method do you use: index searching or full table scan?
In which situation whether peak time or off peak time you will execute the ANALYZE TABLE command. Why?
How to check to memory gap once the SGA is started in Restricted mode?
All the users are complaining that their application is hanging. How you will resolve this situation in OLTP?
If the SQL * Plus hangs for a long time, what is the reason?
Shall we create procedures to fetch more than one record?
How do you increase the performance of %LIKE operator?
You are regularly changing the package body part. How will you create or what will you do before creating that package?
How can you see the source code of the package?
Dual table explain. Is any data internally storing in dual table.
Lot of users are accessing select sysdate from dual and they getting some millisecond differences. If we execute SELECT SYSDATE FROM EMP; what error will we get. Why?
In exception handling we have some NOT_FOUND and OTHERS. In inner layer we have some NOT_FOUND and OTHERS. While executing which one whether outer layer or inner layer will check first?
What is mutated trigger, is it the problem of locks. In single user mode we got mutated error, as a DBA how you will resolve it?
Schema A has some objects and created one procedure and granted to Schema B. Schema B has the same objects like schema A. Schema B executed the procedure like inserting some records. In this case where the data will be stored whether in Schema A or Schema B?
What is bulk SQL?
How to do the scheduled task/jobs in Unix platform?
If the entire disk is corrupted how will you and what are the steps to recover the database?
How will you monitor rollback segment status?
List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into another extend?
What is redo log file mirroring?
How can we plan storage for very large tables?
When will be a segment released?
What are disadvantages of having raw devices?
List the factors that can affect the accuracy of the estimate?
What is the difference between $$DATE$$ & $$DBDATE$$? - $$DBDATE$$ retrieves the current database date$$date$$ retrieves the current operating system.
How to prevent unauthorized use of privileges granted to a Role?
What is a deadlock and Explain?
What are the basic element of base configuration of an Oracle database?
What is an index and How it is implemented in Oracle database?
If we find any problem after data manipulation and if we want to undo it, then we can use this ROLLBACK segment.
SAVEPOINT is the command used to save the workdone. After saving data, if we want to perform insertion/deletion and we done it wrongly, then we can ROLLBACK it by using the SAVEPOINT.
Waiting for an event that never happens or processed.
Ex : Say there are three process P1, P2, P3 and three resources R1, R2, R3.
(1) P1 is started and it is using R1.
(2) P2 is started and it is using R2.
(3) P3 is started and it is using R3.
To complete process P1 it needs resource R2 which is currently used by P2.
To complete process P2 it needs resource R3 which is currently used by P3.
To complete process P3 it needs resource R1 which is currently used by P1.
In this case each and every process is waiting for unoccured events. This
situation we called as DEADLOCK.
To prevent this concept is there called DEADLOCK PREVENTION.
Yes. We can create procedures to fetch more than a row.
By using CURSOR commands we could able to do that.
Ex:
CREATE OR REPLACE PROCEDURE myprocedure IS
CURSOR mycur IS select id from mytable;
new_id mytable.id%type;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO new_id;
exit when mycur%NOTFOUND;
–do some manipulations–
END LOOP;
CLOSE mycur;
END myprocedure;
In this example iam trying to fetch id from the table mytable. So it fetches
the id from each record until EOF.
(EXIT when mycur%NOTFOUND-is used to check EOF.
For further informations, refer CURSORS.
7 Comments on Popular interview questions for DBA
37.
If we find any problem after data manipulation and if we want to undo it, then we can use this ROLLBACK segment.
SAVEPOINT is the command used to save the workdone. After saving data, if we want to perform insertion/deletion and we done it wrongly, then we can ROLLBACK it by using the SAVEPOINT.
Ex:
SAVEPOINT s1;
ROLLBACK s1;
32.
Schema is the complete design of the database or data objects.
28.
Waiting for an event that never happens or processed.
Ex : Say there are three process P1, P2, P3 and three resources R1, R2, R3.
(1) P1 is started and it is using R1.
(2) P2 is started and it is using R2.
(3) P3 is started and it is using R3.
To complete process P1 it needs resource R2 which is currently used by P2.
To complete process P2 it needs resource R3 which is currently used by P3.
To complete process P3 it needs resource R1 which is currently used by P1.
In this case each and every process is waiting for unoccured events. This
situation we called as DEADLOCK.
To prevent this concept is there called DEADLOCK PREVENTION.
30.
INDEX is a one which provides quick access to a row.
7.
Yes. We can create procedures to fetch more than a row.
By using CURSOR commands we could able to do that.
Ex:
CREATE OR REPLACE PROCEDURE myprocedure IS
CURSOR mycur IS select id from mytable;
new_id mytable.id%type;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO new_id;
exit when mycur%NOTFOUND;
–do some manipulations–
END LOOP;
CLOSE mycur;
END myprocedure;
In this example iam trying to fetch id from the table mytable. So it fetches
the id from each record until EOF.
(EXIT when mycur%NOTFOUND-is used to check EOF.
For further informations, refer CURSORS.
12.
No error message indication will be there.
It displays the current system date.
13.
Once you got inside the OUTER_LOOP, it comes out only after the NOTFOUND
statement.
So OUTER_LOOP will be valuated first.Its similar to ‘C’ language, first
OUTER_LOOP will valuated followed by an INNER_LOOP.