Oracle Scenarios Questions And Answers

1⟩ Explain If the large table contains thousands of records and the application is accessing 35% of the table which method to use index searching or full table scan?

A full table scan could be quicker,since it will fetch multiple blocks in one fetch, compared to index->table, index->table for each row.

Measure both ways and decide

Is this answer helpful? 0 Yes | 0 No

Answer This Question

163 views

2⟩ We are regularly changing the package body part. How will we create or what will we do before creating that package?

Create package specifications before creatin package body.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

164 views

3⟩ Explain What is spooling?

Acronym for simultaneous peripheral operations on-line, spooling refers to putting jobs in a buffer, a special area in memory or on a disk where a device can access them when it is ready. Spooling is useful because devices access data at different rates. The buffer provides a waiting station where data can rest while the slower device catches up.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

166 views

4⟩ Can we create procedures to fetch more than one record?

We can create a procedure to return REF cursor or VARRAY or PL/SQL Table type out parameters which can return more than one value.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

145 views

5⟩ 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?

Schema1 Leo

Table Name emp

Procedure Test

Schema2 Leo1

Table Name emp

Schema 1

SQL>

SQL> CREATE TABLE emp (

2 emp_id NUMBER(2),

3 emp_name VARCHAR2(25),

4 dep_id NUMBER(2),

5 emp_status CHAR(1)

6 );

Table created.

SQL> SQL> CREATE OR REPLACE PROCEDURE test AS

2 BEGIN

3 INSERT INTO emp VALUES (1,'LEO',2,'Y');

4 COMMIT;

5 END;

6 /

Procedure created.

SQL> EXEC test

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMP_ID EMP_NAME DEP_ID E

---------- ------------------------- ---------- -

1 LEO 2 Y

SQL> GRANT EXECUTE ON test TO leo1;

Grant succeeded.

SQL> GRANT SELECT ON emp TO leo1;

Grant succeeded.

@Schema Leo1

SQL> CREATE TABLE emp AS SELECT * FROM leo.emp WHERE ROWNUM = 0;

Table created.

SQL> desc emp

Name Null? Type

----------------------------------------- -------- --------------------------

EMP_ID NUMBER(2)

EMP_NAME VARCHAR2(25)

DEP_ID NUMBER(2)

EMP_STATUS CHAR(1)

Now we created the table exactly as the same structure of emp table in schema leo. Now let us try to execute the procedure.

SQL> EXEC test

BEGIN test; END;

*

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00201: identifier 'TEST' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Guess what if you think this should work (as I did) we are wroung. It took a while for me to figure this out. To execute the procedure from leo1 do as follows:

SQL> exec leo.test

PL/SQL procedure successfully completed.

Now let us check where the rows are being inserted.

@Schema leo1:

SQL> select * from emp;

no rows selected

@Schema leo:

SQL> select * from emp;

EMP_ID EMP_NAME DEP_ID E

---------- ------------------------- ---------- -

1 LEO 2 Y

There you go. You added one more row now. So even though you execute the procedure from schema leo1 you inserted a row in leo.

So the ANSWER to the question is : Schema A.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

179 views

6⟩ Explain If the entire disk is corrupted how will you and what are the steps to recover the database?

if the entire disk is corrupted and no backup is there don nothing sit and relax their is no possibility of recovery ...a backup is required for restoration and for recovery redo log and archive logs.

Once if you have theses than think of recovering ..a dba should always plan for the recovery scenario depending upon the criticality of the database.oracle provides 0% data loss facilty through data guard and online backup .its dba who has to decide.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

177 views

7⟩ If All the users are complaining that their application is hanging. How we will resolve this situation in OLTP?

If the user is complaining the hang problem ..then the experience of a dba reflects the work style that he is going to perform and basically as the rule suggest first try to connect to the database itself and fire some query to check whether you are allowed to connect or not if you are connected then check for any locked_objects by checking v$locked_object and dba_waiters and dba_blockers.then you have to eliminate the things which are working properly and targeting yourself to the weaker place and then check at os level that which process is consuming the most of the time and then analyze the problem if the problem relates to a single user process then check what that user is doing by checking the sql statement he is firing.than so on

Is this answer helpful? 0 Yes | 0 No

Answer This Question

166 views

8⟩ Explain the total process of eim?

after you get the legacy data thorugh exl format

1. you identify the which base table and which base column suitable for legacy data.

2.after that u have to use control file or dts processing to load the lagacy to eim table.

3. after that by using IFB file from eim to base table store the data.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

140 views

9⟩ Explain How do we increase the performance of %LIKE operator?

The wildcard char % can be placed in one of three ways:

%searchwordhere%

searchwordhere%

%searchwordhere

The searchwordhere% is the fastest because it can use an index if one is specified on that column. The other two %searchwordhere%, and %searchwordhere would never use the index even if one is specified and thus result in slow table scans.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

144 views

10⟩ Explain If the SQL * Plus hangs for a long time, what is the reason?

You are running a cartisian query, typically by mistake. Make sure every table has a join criteria specified for it.

You are working on a table with 100+million rows.

The database server is busy doing a backup.

Check the disk IO for the process that appears hung and if the disk IO is increasing every 5-10 seconds then the job is not hung, it is just taking a while to complete.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

157 views

11⟩ Tell us In which situation whether peak time or off peak time we will execute the ANALYZE TABLE command and Why?

We have to run the analyze command during off peak time only because it actually performs full table scan.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

198 views

12⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

143 views

13⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

184 views

14⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

152 views

15⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

162 views

16⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

181 views

17⟩ Explain What are the differences between database designing and database modeling?

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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

155 views

18⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

178 views

19⟩ 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,....

Is this answer helpful? 0 Yes | 0 No

Answer This Question

152 views

20⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

171 views

Oracle Scenarios

Home Oracle Oracle Scenarios

23 Oracle Scenarios Questions And Answers

1⟩ Explain If the large table contains thousands of records and the application is accessing 35% of the table which method to use index searching or full table scan?

2⟩ We are regularly changing the package body part. How will we create or what will we do before creating that package?

3⟩ Explain What is spooling?

4⟩ Can we create procedures to fetch more than one record?

5⟩ 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?

6⟩ Explain If the entire disk is corrupted how will you and what are the steps to recover the database?

7⟩ If All the users are complaining that their application is hanging. How we will resolve this situation in OLTP?

8⟩ Explain the total process of eim?

9⟩ Explain How do we increase the performance of %LIKE operator?

10⟩ Explain If the SQL * Plus hangs for a long time, what is the reason?

11⟩ Tell us In which situation whether peak time or off peak time we will execute the ANALYZE TABLE command and Why?

12⟩ Explain What is bulk SQL?

13⟩ 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?

14⟩ Explain How to do the scheduled task/jobs in Unix platform?

15⟩ 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?

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

17⟩ Explain What are the differences between database designing and database modeling?

18⟩ 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?

19⟩ Explain How can we see the source code of the package?

20⟩ Explain What is Testing Scenario? What is scenario based testing? can u explain with an example?

Quick Links:

Oracle Scenarios

Home Oracle Oracle Scenarios

23 Oracle Scenarios Questions And Answers

1⟩ Explain If the large table contains thousands of records and the application is accessing 35% of the table which method to use index searching or full table scan?

2⟩ We are regularly changing the package body part. How will we create or what will we do before creating that package?

3⟩ Explain What is spooling?

4⟩ Can we create procedures to fetch more than one record?

5⟩ 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?

6⟩ Explain If the entire disk is corrupted how will you and what are the steps to recover the database?

7⟩ If All the users are complaining that their application is hanging. How we will resolve this situation in OLTP?

8⟩ Explain the total process of eim?

9⟩ Explain How do we increase the performance of %LIKE operator?

10⟩ Explain If the SQL * Plus hangs for a long time, what is the reason?

11⟩ Tell us In which situation whether peak time or off peak time we will execute the ANALYZE TABLE command and Why?

12⟩ Explain What is bulk SQL?

13⟩ 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?

14⟩ Explain How to do the scheduled task/jobs in Unix platform?

15⟩ 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?

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

17⟩ Explain What are the differences between database designing and database modeling?

18⟩ 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?

19⟩ Explain How can we see the source code of the package?

20⟩ Explain What is Testing Scenario? What is scenario based testing? can u explain with an example?

BE THE FIRST TO KNOW

Quick Links: