SBI has previously asked some database questions specific to oracle. Today, we'll see some important questions pertaining to that. Version 10g & 11g is what most of the systems run today & Oracle has come up with 12c as the latest one.
1. What is data scrubbing?
It is a process to upgrade the quality of data before moving it into a data warehouse.2. what are the types of triggers in Oracle?
A trigger is a stored procedure that Oracle database invokes when a particular event occurs.Types are as follows:
- Before trigger
- After trigger
- Row level trigger
- Schema trigger
- Instead of trigger
- Database trigger
- Statement level trigger
- compound trigger (introduced in 11g)
3. What is a System change number (SCN) in Oracle?
It is a clock value that is incremented whenever database changes are made.
4. What do you mean by Forward Recovery (roll forward) & Backward Recovery (roll backward)?
Backward recovery is used to undo unwanted changes to the database made by transactions that have been aborted abnormally. We always use rollback whenever any partial commit or abnormal termination takes place.
Forward recovery starts with an earlier state of database and results of good transactions (after images) are applied to database then. After image is a copy of the record after it has been modified.
However the preferred way to recover a database after a system failure is "switch to duplicate database"
5. What is common gateway interface (CGI)?
It is an interface that accepts and returns data written in any language that produces an executable file.
6. What are redo logs?
Redo logs are logical and physical structures designed to hold all changes made to the database and aid in the event of a recovery.
7. What is RFM analysis?
RFM analysis is a business intelligence reporting system that analyzes and ranks customers based on their purchasing patterns. R refers to how recently the customer purchased, F refers to how frequently he purchases and M refers to amount of money he spends on the purchase.
8. What is meant by hot spot?
A hot spot is a single shared row of a table that is used and updated so frequently that it creates a performance issue for the system.
9. What are some important packages provided by Oracle which database administrators should know about?
- DBMS_SHARED_POOL
- DBMS_UTILITY
- DBMS_SQL
- DBMS_DDL
- DBMS_SESSION
- DBMS_OUTPUT
- DBMS_SNAPSHOT
These packages are owned by SYS user.
10. When to use WHERE clause and when to use HAVING Clause?
- WHERE clause - is used when you specify a condition for coloumns and single row functions. It is written before GROUP BY clause.
- HAVING clause - is used when you specify a condition for a group function and is written after GROUP BY clause.
11. What are some common performance tuning utilities used in Oracle?
- Explain plan
- SQL Trace
- TKProf
- Statspack
- Improve Buffer cache hit ratio
- Pin objects in shared pool
GK fact of the day
China has highest percentage of internet users (22%) while Bermuda has highest penetration of internet users (98%).
