Question And Ans

1. Question: What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

Ans : SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

2. Question: You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?

Ans: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "||". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.

3. Question: Where clause restricts rows, what does having clause restrict?
i) . only group results.
ii). rows results.
iii). both rows and groups result.

Ans : 1

4. Question: what is difference between sub query and nested query? Is there any difference?

Ans : Query inside the query is nested query. It is also called as sub query.

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

5. Question: How and in what way REPLACE is different from TRANSLATE?

Ans : It is used to replace charecter by charecter
Ex: Select translate('tech' , 'h', 'x') from dual;

result:   tecx

It is used to replace word by word
Ex: select replace('tech world','tech','technology') from dual;

result:   technology world

6. Question: how we can eliminate duplicates without using distinct command?

Ans :

select * from tale_name a where rowid >any(select rowid from table_name b where a.col1 b.col1)

Or

select unique * from table_name;

7. Question:  How do I eliminate the duplicate rows ?

Ans :

SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
or

SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid)  from table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101               Scott
102               Jiyo
103               Millor
104               Jiyo
105               Smith

delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
The output like,
Empno Ename
101               Scott
102               Millor
103               Jiyo
104               Smith

8. Question: Write down frequently used  PL/SQL Exceptions?
Ans: Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

9. Question: What is the maximum number of triggers, can apply to a single table?

Ans:  12 triggers.

10. Question: Display Odd/ Even number of records of Employee table.
Ans:  Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

11. Question: What is Oracle cursor, Describe Implicit & Explicit cursors.
Ans: Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.

There are four implicit cursor attributes used in Oracle  cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

Same as explicit cursor but prefixed by the word SQL , SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements.
: 2.  All are  Boolean attributes.

12. Question:  what is SGA? Where it is located? Why the system performance will be slow after loading the oracle?

Ans: sga stands fro system global area.which is the part of the oracle instance(includes memory struture and background processes). for faster executon and better performance everthing comes in the sga.

so sga is located in oracle instance which itself consist of two parts.

1>database buffercache: which stores the recently executed database blocks when user fires a query oracle searches that block in dictionary and stores it into buffer cache.. so if the next time when the user fires the same query ... users gets output from the buffer cache itself.. no need for parsing again.. only fetch and execute will work.

2> redolog buffer: it stores all the changes made in the database and also all DMLs when user fires an commit statement lgwr back process writes it into the redolog files.. which can be used for recovery of database during instance crash .

when u install an oracle in ur machine system will become slow because of the unstructured sga configuration and also ur system configuration once the oracle instance instants starts .. oracle itself will allocate the space from system .

for example : if u have system confi 512 mb and if will install oracle with specified sga congi. obviously system performance will go low.

so in order to increase the system performance use the system configu should be well enough to run the oracle instance.... and also allocates enough space for sga....

13. Question: What is function of RECO ?

Ans : RECOver (RECO) is used to resolve distributed transactions that are pending due  to  a  network  or  system failure in a distributed database. At timed intervals,the local  RECO  attempts  to  connect  to  remote databases and automatically  complete  the commit or rollback of the local portion of any pending distributed transactions.

14. Question: how to convert local management tablespace to dictionary managed tablespace

Ans: Use the following package
execute dbms_space_admin.tablespace_convert_to_local('tablespace_name');
execute dbms_space_admin.tablespace_convert_from_local('tablespace_name');

15. Question: Which command is used for locking a user from performing DML Operation?

Ans: LOCK TABLE tablename IN EXCLUSIVE MODE;

16. Question: How can we start the database if both the pfile and spfile is lost?

Ans: All the changes to the init.ora file is recorded in the alert.log file. So go to the alert log file in bdump directory and delete the unnecessary parameters from the file and copy that file to the desired location and restart the database.

17. Question: Can you perform DML operations when the standby database is in read only mode?

Ans: One can do SELECTS only on a Physical/Logical Standby database. The basic principle behind Oracle Standby database is to provide protection against media failures. So no DMLs are allowed on the standby database and the most important things is that Standby databases can always be opened in READ-ONLY mode which won't allow DMLs.

18. Question: What would you use to view contents of a large error log file?

Ans. tail  -10 file_name   ( last 10 rows)

19. Question: What is the purpose of the IMPORT option IGNORE? What is it's default setting?

Ans: The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the table’s data will be inserted. The default value is N.

20. Question: What is Optimal Flexible Architecture in Oracle? Please describe in the context of Unix environment

Ans: OFA stands for Optimal Flexible Architecture. It is a method of placing directories and files in an Oracle system so that you get the maximum flexibility for future tuning and file placement.

21. Question: How can you determine the space left in a file system?

Ans: There are several commands to do this: du, df, or bdf

22. Question:  How can you determine the number of SQLNET users logged in to the UNIX system?

Ans: SQLNET users will show up with a process unique name that begins with oracle, if you do a ps -ef|grep oracle|wc -l you can get a count of the number of users.

23. Question: How can you find dead processes?

Ans:  ps -ef|grep zombie -- or -- who -d depending on the system.

24. Question: What are the different Oracle Kernel Layers?

Ans :

The Oracle call interface (OCI)

The user program interface (UPI)

The Oracle program interface (OPI)

The execution layer (KX)

The compilation layer (KK)

The distributed execution layer (K2)

The network program interface (NPI)

The security layer (KZ)

The query layer (KQ)

The recursive program interface (RPI)

The access layer (KA)

The data layer (KD)

The transaction layer (KT)

The cache layer (KC)

The services layer (KS)

The lock management layer (KJ)

The generic layer (KG)

The operating system dependencies (S)

25. Question: How can we schedule the procedure to run automatically?



Ans:

1. Using DBMS_JOB package

2. Using DBMS_SCHEDULER