Top Oracle Scenarios Interview Questions & Answers:

1. Explain about the oracle disaster recovery scenarios?

USE DBNAME
go
EXEC sp_change_users_login 'Update_One', 'LoginUserid', 'LoginUserid'

Note: if you are using a different SQl login userid then this 'Login Userid' ,'NewloginUserid'

2. Explain What is Testing Scenario? What is scenario based testing? can u explain with an example?

Scenario testing is the real time testing techniques implemented on an application with the presence of certain applied conditions and environment.In this type of testing the behavior of the application or s/w is tracked and results are analyzed.And bugs are reported

3. Explain How can we see the source code of the package?

SELECT TEXT FROM USER_SOURCE/ALL_SOURCE/DBA_SOURCE

WHERE NAME=<NAME OF THE PACKAGE> AND TYPE='PACKAGE';

HERE THE TYPE MAY BE PACKAGE,PROCEDURE,....

4. Explain 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?

inner layer. execution carry on furthur without going to outer exception blocks.

5. Explain What are the differences between database designing and database modeling?

Database modeling comprises: discovery, design, documentation, communication, DDL generation, re engineering

6. Explain What are the differences you have seen while installing Oracle on NT and Unix platform?

Oracle Server = Oracle Instance + Oracle Database Oracle instance comprises of Background Process and memory structures in Unix all background processes are treated as independent processes but in windows all are combined together within oracle.exe in unix when a user logins he is dedicated to the server via an independed process so if require you can kill a process through os level ..this is one of the major advantage of using oracle on unix based system rather then windows system bcoz in case database stucks or hangs an independent process causing the problem can be killed and database will be made resumable immediately.

And one more diffrence is in windows you are require to create a service to start the instance by using oradim but in unix its not required .you can start the instance.in windows unique service name is required but in unix diffrent user can have the same service name started by them but its not advisable.

7. Explain the Dual table. 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 and Why?

The built-in function SYSDATE returns a DATE value containing the current date and time on your system. DUAL is built-in relation in Oracle which serves as a dummy relation to put in the FROM clause when nothing else is appropriate. For example, try "select 1+2 from dual;".So "select sysdate from EMP" won't generate the desired result.

Select sysdate from emp will not return any error.. It will return sysdate in all the rows. i.e if emp has 100 rows sysdate will be returned 100 times

8. Explain How to do the scheduled task/jobs in Unix platform?

Use DBMS_JOB/DBMS_SCHEDULER and give the Job_type as EXECUTABLE and give the path name of the shell script to execute.

9. Explain 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?

mutated trigger: example:Table A has an insert trigger.In that Trigger: There is a statement like insert into Table A, which caues mutated trigger.Avoid to have those kind of triggers in the database.

10. Explain What is bulk SQL?

Bulk sql are forall and bulk collect INTO statement,
For performence reason the bulk bind is used to eleminate the context switching between two sql and pl/sql eng.