Frequently asked Oracle PL/SQL Interview Questions

Oracle PLSQL Interview Questions/answers

1. What is the use of package/function/procedure?

Answer:

A Procedure that performs an action and a function that computes a value.

Package is a collection of related variables, cursor, procedure and functions. A package is a schema object which groups logically related PL/SQL types, items and subprograms.
Functions return a single value, procedures are used to return multiple values, otherwise, it is generally similar to a function. 
Functions can be used in SQL query but procedure we can't use in SQL query. 

2. What is the use of FORALL, BULK COLLECT in PL/SQL?
Answer:

3. What is PL/SQL table (now PL/SQL tables are called Associative Arrays)?

Answer:
PL/SQL table or associative array is a one-dimensional array.
An associative array is an unbounded set of key-value pairs. Each key is unique, and serves as the subscript of the element that holds the corresponding value.

4. How to compile procedure/package?

Answer:
alter procedure upd_int compile;
alter package SCOTT.LOCA_SER compile;

5. What are the differences between if and nullif?
Answer:

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

Answer:
12 triggers

7. What are the differences between decode and case?
Answer:

8. What is pragma?

Answer:
Triggers can run under PRAGMA AUTONOMOUS_TRANSACTION, in which case they must have a COMMIT. But this is an edge case, as there are few meaningful uses of nested transactions in Oracle.
Pragma is a compiler directive in oracle.
Autonomous_Transaction is creating a new or independent session in the same transaction.
Autonomous Transaction provides functionality to the developer in which it allows to do changes in a separate transaction and to save/discard that particular transaction without affecting the main session transaction.

9. Want output in below format, salary in descending order and with serial numbers.

Answer:
Empno empname   Sal S.No
123     Satya      12000 1
345     Rajesh     10000 2
796     Prasad      9000 3
986     Bhanu       8000 4

select t1.*, rownum from (select empno, empname, sal from emp order by sal desc)t1 order by rownum;

10. What is the difference between function and procedure?

Answer:

Functions return a single value, procedures are used to return multiple values, otherwise, it is generally similar to a function. 
Functions can be used in SQL query but procedure we can't use in SQL query. 
A Procedure that performs an action and a function that computes a value.

Package is a collection of related variables, cursor, procedure and functions. A package is a schema object which groups logically related PL/SQL types, items and subprograms.

11. What is mutating of a trigger?

Answer:
A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired.

12. What are cursor attributes?

Answer:
Cursor attributes are used to get information about the current status of the cursor.

Both explicit and implicit cursors have four attributes:
• FOUND – It Returns TRUE if the record was fetched successfully, FALSE otherwise
• NOTFOUND - Returns TRUE if the record was not fetched successfully, FALSE otherwise.
• ROWCOUNT - Returns the number of records fetched from the cursor at that point in time.
• ISOPEN - Returns TRUE if the cursor is open, FALSE otherwise.

13. What are the different types of triggers?
Answer:

14. Can trigger contain commit?

Answer:
No, regular triggers can not contain commit. 
Any change that a trigger does is committed with the transaction that fired the trigger.
You will get the below error.
ORA-04092: cannot COMMIT in a trigger

Autonomous triggers (PRAGMA AUTONOMOUS_TRANSACTION) can contain COMMIT and ROLLBACK.

15. Can you call a procedure/function inside a trigger?

Answer:
Yes, you can fire a procedure/function from a Trigger.
But, keep in mind that trigger & procedure/function should not access the same table.

Related Oracle Articles: Oracle Exadata Interview Questions   Oracle Data Guard Interview Questions/FAQs  Oracle Export/Import - Data Pump Interview Questions/FAQs

Top 30 Oracle Performance Related Interview Questions for DBAs

Oracle DBA Performance Related Interview Questions with answers [ 2023 ] - Part 2

21. What you’ll check whenever a user complains that his session/database is slow?
Answer :

22. What is the use of statistics?
Answer :

23. How to generate explain plan?
Answer :

24. How to check explain plan of already ran SQLs?
Answer :

25. What are the top 5 wait events (in AWR report) and how you will resolve them?
Answer :
http://satya-dba.blogspot.in/2012/10/wait-events-in-oracle-wait-events.html

db file sequential read => tune indexing, tune SQL (to do less I/O), tune disks, increase buffer cache. This event is indicative of disk contention on index reads. Make sure all objects are analyzed. Redistribute I/O across disks. The wait comes from the physical side of the database. It is related to memory starvation and non-selective index use. Sequential read is an index read followed by the table read because it is doing index lookups which tell exactly which block to go to.
db file scattered read => disk contention on full table scans. Add indexes, tune SQL, tune disks, refresh statistics, and create materialized view. Caused due to full table scans maybe because of insufficient indexes or unavailability of updated statistics.
db file parallel read => tune SQL, tune indexing, tune disk I/O, increase buffer cache. If you are doing a lot of partition activity then expect to see that wait even. It could be a table or index partition.
db file parallel write => if you are doing a lot of partition activity then expect to see that wait even. It could be a table or index partition.
db file single write => if you see this event then probably you have a lot of data files in your database.

control file sequential read
control file parallel write

log file sync => committing too often, archive log generation is more. Tune applications to commit less, tune disks where redo logs exist, try using nologging/unrecoverable options, log buffer could be too large.
log file switch completion => May need more log files per group.
log file parallel write => Deals with flushing out the redo log buffer to disk. Disks may be too slow or have an I/O bottleneck. Look for log file contention.
log buffer space => Increase LOG_BUFFER parameter or move log files to faster disks. Tune application, use NOLOGGING, and look for poor behavior that updates an entire row when only a few columns change.
log file switch (checkpoint incomplete) => May indicate excessive db files or slow IO subsystem.
log file switch (archiving needed)  => Indicates archive files are written too slowly.
redo buffer allocation retries => shows the number of times a user process waited for space in the redo log buffer. 
redo log space wait time => shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer.

buffer busy waits/ read by other session => Increase DB_CACHE_SIZE. Tune SQL, tune indexing, we often see this event along with full table scans, if the SQL is inserting data, consider increasing FREELISTS and/or INITRANS, if the waits are on segment header blocks, consider increasing extent sizes.
free buffer waits => insufficient buffers, process holding buffers too long or i/o subsystem is over loaded. Also check you db writes may be getting clogged up.
cache buffers lru chain => Freelist issues, hot blocks.
no free buffers => Insufficient buffers, dbwr contention.

latch free
latch: session allocation
latch: in memory undo latch => If excessive could be bug, check for your version, may have to turn off in memory undo.
latch: cache buffer chains => check hot objects.
latch: cache buffer handles => Freelist issues, hot blocks.
direct path write => You wont see them unless you are doing some appends or data loads.
direct Path reads => could happen if you are doing a lot of parallel query activity.
direct path read temp or direct path write temp => this wait event shows Temp file activity (sort,hashes,temp tables, bitmap) check pga parameter or sort area or hash area parameters. You might want to increase them.
library cache load lock
library cache pin => if many sessions are waiting, tune shared pool, if few sessions are waiting, lock is session specific.
library cache lock => need to find the session holding the lock, look for DML manipulating an object being accessed, if the session is trying to recompile PL/SQL, look for other sessions executing the code.
undo segment extension => If excessive, tune undo.
wait for a undo record => Usually only during recovery of large transactions, look at turning off parallel undo recovery.

enque wait events => Look at V$ENQUEUE_STAT

SQL*Net message from client
SQL*Net message from dblink
SQL*Net more data from client
SQL*Net message to client
SQL*Net break/reset to client

26. What are the init parameters related to performance/optimizer?
Answer :
optimizer_mode = choose
optimizer_index_caching = 90
optimizer_index_cost_adj = 25
optimizer_max_permutations = 100
optimizer_use_sql_plan_baselines=true
optimizer_capture_sql_plan_baselines=true
optimizer_use_pending_statistics = true;
optimizer_use_invisible_indexes=true
_optimizer_connect_by_cost_based=false
_optimizer_compute_index_stats= true;

27. What are the values of optimizer_mode init parameters and their meaning?
Answer :
optimizer_mode = choose

28. What is the use of AWR, ADDM, ASH?
Answer :

29. How to generate AWR report and what are the things you will check in the report?
Answer :

30. What is Elapsed time and DB Time in the AWR report?
Answer :
Elapsed Time - When looking into the AWR report, the Elapsed Time is the wall clock time of duration for which the AWR report has been generated. For example, if we generate AWR report for 1 hour then Elapsed Time in AWR report will be 60 mins.

DB CPU - DB CPU is the CPU consumption by all Oracle server processes/foreground processes during snapshot interval time.

DB Time - DB time is a statistic which represents CPU time consumed by all Oracle processes over a period of time plus non-idle wait time. DB Time is the time spent by the database server executing user calls. DB Time is the total time spent by the all user processes which are actively working or actively waiting in the database calls. It includes the CPU Time, IO Wait time and non-idle time. It tells us that how much activity is performed by the database for the duration.

31. How to generate ADDM report and what are the things you will check in the report?
Answer :

32. How to generate ASH report and what are the things you will check in the report?
Answer :

33. How to generate STATSPACK report and what are the things you will check in the report?
Answer :

34. How to generate TKPROF report and what are the things you will check in the report?
Answer :
The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. Use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

Related Oracle Interview Articles: Oracle Data Guard Interview Questions  Oracle DBA Interview Questions – Part4   UNIX Interview Questions for Oracle DBAs

Top 30 Essential Linux/UNIX Interview Questions for Oracle DBAs [ 2023 ]

Unix/Linux/Solaris Interview Questions/answers for Oracle DBAs

1. What is the difference between soft link and hard link?
Answer:
A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system, because they share the same inode number and an inode table is unique to a file system, both must be on the same file system.

2. How you will read a file from a shell script?
Answer:
while read line
do
echo $line
done < file_name

3. What is the use of umask?
Answer:
Will decide the default permissions for files.

4. What is the default value of umask?
Answer:
022

5. What is crontab and what are the arguments?
Answer:
The entries have the following elements:
field             allowed values
-----             --------------
minute            0-59
hour                0-23
day of month   1-31
month             1-12
day of week     0-7 (both 0 and 7 are Sunday)
user                 Valid OS user
command         Valid command or script

? ? ? ? ? command
|  | |  | |_________day of the week (0-6, 0=Sunday)
|  | |  |___________month (1-12)
|  | |_____________day of the month (1-31)
|  |_______________hour (0-23)
|_________________minute (0-59)


6. How to find an operating system (OS) version?
Answer:
uname -a

7. How to find out the run level of the user?
Answer:
uname -r

8. What is load average ?
Answer:
Load Average is the value that represents the load on the system for a specific period of time. Also it can be considered the ratio of the number of active tasks to the number of available CPUs.
run queue length - the sum of the number of processes that are currently running plus the number that are waiting (queued) to run.

9. What is the top command?
Answer:
top is an operating system command, it will display top processes which are taking high CPU and memory.

10. How to delete 7 days old trace files?
Answer:
find ./trace –name *.trc –mtime +7 –exec rm {} \;

11. How to get the 10th line of a file (by using grep)?
Answer:

12. (In Solaris) how to find out whether it is 32bit or 64bit?
Answer:

13. What is paging?

Answer:

14. What are huge pages?

Answer:

15. How to find out the status of the last command executed?
Answer:
$?

16. How to find out the number of arguments passed to a shell script?
Answer:
$#

17. How to add users in Solaris/Linux?
Answer:
useradd command

18. What does sudo stand for in Linux systems?
Answer:
Abbreviation of sudo is "substitute user do" (some people will abbreviate it as "super user do"), which allows users to run programs with the security privileges of another user, by default the superuser.

19. How to find out the memory size of the Linux host?

Answer:
grep MemTotal /proc/meminfo | awk '{FS=":"}{print $2 }' | awk '{print $1/1024/1024}'

20. How to find out the number of CPUs in Linux host?

Answer:
grep "physical id" /proc/cpuinfo | wc -l

21. What is inode ? how to get inode information ?
Answer:

inode (index node) is a data structure that describes a file system object, such as a file or a directory.

Each file/directory is associated with an inode, which is identified by an integer, often referred to as an i-number or inode number.

Each inode stores the attributes and disk block locations of the object's data.

File system object attributes include metadata (times of last change, access, modification), ownership and permissions.

A file's inode number stays the same when it is moved to another directory.

ls -i  -- inode number

df -i -- to show inode information

22. How to get the creation time of a file ?

Answer:

23. What are RAID levels? What is RAID 0, RAID 1 and RAID 10? 

Answer:

24. Which RAID level best suits databases ?

Answer:

25. How to split a bigger file into smaller files ?
Answer:

26. How will you execute the last command executed?

Answer:

27. How to find out all files larger than 100MB ?

Answer:

28. Which command will tell you for how many days the host has been running?

Answer:

29. Which commands will show the load average of the host ?

Answer:

30. How can you eliminate the ongoing process in Linux? (or) How to kill an OS process ?

Answer:

Related Oracle DBA Interview Articles: Oracle Exadata Interview Questions/FAQs   Oracle Data Guard Interview Questions/FAQs  Oracle DBA RAC Interview Questions/FAQs