Oracle Scenarios Interview Preparation Guide

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

Create package specifications before creatin package body.

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.

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.

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

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.

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

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.

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.

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.