21⟩ What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any
other user.
Oracle DBA
Home Oracle Oracle DBA
“Oracle DBA Interview Questions and Answers will guide us now that the Oracle Database commonly referred to as Oracle RDBMS or simply Oracle is a relational database management system (RDBMS) produced and marketed by Oracle Corporation. As of 2009, Oracle remains a major presence in database computing. So get preparation of Oracle DBA Jobs with this Oracle DBA Interview Questions with Answers guide”
50 Oracle DBA Questions And Answers
22⟩ Which function is used to find the largest integer less than or equal to a specific value?
FLOOR
23⟩ What operator tests column for the absence of data?
IS NULL operator
24⟩ What are the steps involved in Database Start up?
Start an instance, Mount the Database and Open the Database.
25⟩ What are the different modes of mounting a Database with the Parallel Server?
Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only
that Instance can mount the database.
Parallel Mode If the first instance that mounts a database is started in parallel mode, other
instances that are started in parallel mode can also mount the database.
26⟩ What is Oracle Full Backup?
A full backup is an operating system backup of all data files, on-line redo log files and control
file that constitute ORACLE database and the parameter.
27⟩ What is Oracle Partial Backup?
A Partial Backup is any operating system backup short of a full backup, taken while the
database is open or shut down.
28⟩ What is Oracle Archived Redo Log?
Archived Redo Log consists of Redo Log files that have archived before being reused.
29⟩ What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode?
Complete database recovery from disk failure is possible only in ARCHIVELOG mode.
Online database backup is possible only in ARCHIVELOG mode.
30⟩ What is Oracle Log Switch?
The point at which ORACLE ends writing to one online redo log file and begins writing to another
is called a log switch.
31⟩ What are the steps involved in Instance Recovery in Oracle?
Rolling forward to recover data that has not been recorded in data files, yet has been recorded
in the on-line redo log, including the contents of rollback segments.
Rolling back transactions that have been explicitly rolled back or have not been committed as
indicated by the rollback segments regenerated in step a.
Releasing any resources (locks) held by transactions in process at the time of the failure.
Resolving any pending distributed transactions undergoing a two-phase commit at the time of
the instance failure.
32⟩ Do you need a commit after DDL statements?
DDL IS A AUTO COMMIT, YOU DONT NEED TO COMMIT AGAIN
33⟩ How would you pass hints to the SQL processor?
USING COMMENT LINES WITH (+) SIGN YOU CAN PASS THE HINTS TO SQL ENGINE
For example
/* +PARALLEL( ) */
34⟩ what is the difference between group by and order by?
group by is used when we use aggregate functions on the columns in a query the other columns
should be in group by query
example:
select empno,ename,sum(sal) from emp
group by empno,ename
Order by is used to sort values either in ascending or descending order
35⟩ A DBA had to remove some Archivelogs to free up space in filesystem. Now when the RMAN job starts to backup Archivelogs, it complains about missing Archivelogs that were deleted by DBA. To resolve the issue and continue backing up remainder of Archivelogs, which RMAN command can be used so it wo not complain about missing Archivelogs.
Crosscheck command
36⟩ Which RMAN command is used to create an exact replica of a database in new host?
DUPLICATE DATABASE
37⟩ How do you install Statspack?
By running $ORACLE_HOME/rdbms/admin/spcreate.sql script
38⟩ Process you follow to start looking into Performance issue at database level (If the application is running very slow, at what points do you need to go about the database in order to improve the performance?)
► Run a TOP command in Unix to see CPU usage (identify CPU killer processes)
► Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and memory usage and possible blocking
► Run STATSPACK report to identify:
1. TOP 5 WAIT EVENTS
2. RESOURCE intensive SQL statements
► See if STATISTICS on affected tables needs to be re-generated
► IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and see whether new index or use of HINT brings the cost of SQL down.
39⟩ Explain below wait events in STATSPACK report DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE?
DB SCATTERED READ - FULL TABLE SCAN
DB SEQUENTIAL REAL - IO
ENQUEUE - LOCKING
40⟩ List five most important parameter in 9i affecting performance?
CURSOR_SHARING
DB_CACHE_SIZE
PGA_AGGREGATE_TARGET
DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE
