Oracle DBA Interview Questions

If you're searching for Oracle DBA interview questions and answers for experienced professionals, then you are in the right place. There are plenty of opportunities in many organizations in the world. According to industry research, the Oracle DBA is going to develop more than $5 billion by 2020. So, you have the chance to push your career forward in Oracle DBA development. Tekslate offers Oracle DBA database administrator interview questions that will help you in achieving your dream job. 

We have divided these interview Questions into a few sections:

• Oracle DBA Interview Questions and Answers
• Oracle Database Interview Questions
• Basic Oracle Apps DBA Interview Questions
• Oracle DBA Scenario Based Interview Questions
• Oracle DBA Interview Questions for 3 years Experienced

Most frequently asked Oralace DBA Interview Questions How many memory layers are in the shared pool?  How do you find out from the RMAN catalog if a particular archive log has been backed-up?  How do you tell what your machine name is and what is its IP address? What is the recommended interval at which to run statspack snapshots, and why?  How would you begin to troubleshoot an ORA-3113 error?  How do you increase the OS limitation for open files (LINUX and/or Solaris)? Explain how you would restore a database using RMAN to Point in Time? What are nested tables?  What are the attributes or cursor?  What is a system tablespace?  What is a schema?  Define SQL?  What are the different types of SQL statements?

Oracle DBA Interview Questions and Answers

Q1) How many memory layers are in the shared pool? 

Ans: The shared pool portion of the SGA contains three major areas: library cache(contains parsed SQL statements, cursor information, execution plans), dictionary cache (contains cache -user account information, privileges information, datafile, segment and extent information), buffers for parallel execution messages, and control structure.

Q2) How do you find out from the RMAN catalog if a particular archive log has been backed-up? 

Ans: list archive log all;

Q3) How can you tell how much space is left on a given file system and how much space each of the file system’s subdirectories take-up? 

Ans: df -kh and du-sh

Q4) Define the SGA and

i) How you would configure SGA for a mid-sized OLTP environment?

ii) What is involved in tuning the SGA? 

Ans:

SGA: The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM). All Oracle processes use the SGA to hold information. The SGA is used to store incoming data (the data buffers as defined by thedb_cache_size parameter), and internal control information that is needed by the database. You control the amount of memory to be allocated to the SGA by setting some of the Oracle “initialization parameters”.  These might include db_cache_size, shared_pool_size and log_buffer.

i) 40% of RAM can be used for sizing SGA rest is reserved for OS and others in the 64-bit machine and 32-bit machine max SGA configured can be 1.5GB only.

ii) Check the statspack report. Check the hit ratio of the Data buffer. If it is less than 90%, then we need to increase the Data buffer. Check the hit ratio of the shared pool. If it is less than 95%, then we need to increase the Shared pool. Check the log buffer. If redo buffer allocation retries/redo entries are greater than 1%, then we need to increase log_buffer.

Q5) What is the cache hit ratio, what impact does it have on the performance of an Oracle database, and what is involved in tuning it? 

Ans:

Buffer cache hit ratio: It calculates how often a requested block has been found in the buffer cache without requiring disk access. This ratio is computed using data selected from the dynamic performance view V$SYSSTAT. The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.

sql> select name, value From v$sysstat Where name in (‘db block gets’, ‘consistent gets’, ‘physical reads’);

The cache-hit ratio can be calculated as follows: Hit ratio = 1 – (physical reads / (db block gets + consistent gets)) If the cache-hit ratio goes below 90% then:  increase the initialization parameter DB_CACHE_SIZE.

Library cache hit ratio: It calculates how often the parsed representation of the statement can be reused. It also is known as soft parse.

sql>  select namespace, pins, pinhits, reloads, invalidations from v$librarycache order by namespace;

Library Cache Hit Ratio = sum(pinhits) / sum(pins)

Dictionary cache hit ratio: It is a measure of the proportion of requests for information from the data dictionary, the collection of database tables and views containing reference information about the database, its structures, and its users. For instance startup, the data dictionary cache contains no data, so any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses should decrease. Eventually, the database should reach a "steady state" in which the most frequently used dictionary data is in the cache.

Q6) Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database? 

Ans: Daily Monitoring activities and check different logs for any sort of errors.

Q7) How do you tell what your machine name is and what is its IP address?

Ans: hostname, uname -n and ifconfig

Q8) How would you go about verifying the network name that the local_listener is currently using?

Ans: lsnrctl stat or ps-eaf|grep tns

Want to acquire industry skills and gain complete knowledge of Oracle DBA? Enroll in Instructor-Led live Oracle DBA Training to become Job Ready!

Q9) You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance? 

Ans: SQL> oradebug setmypid SQL> oradebug ipc SQL>oradebug tracfile_name

Also, you can check the spfile. The parameters will start with instance_name. parameter_name naming.

Q10) What view(s) do you use to associate a user’s SQLPLUS session with his o/s process? 

Ans: v$process and v$session sql> select a.spid from v$process a, v$session b where a.addr = b.addr and b.audsid=userenv(‘sessionid’); 

Q11) What is the recommended interval at which to run statspack snapshots, and why? 

Ans: Should be in minutes (15-20 mins approx)  because where the time between the two snapshots is measured in hours, the events that caused serious performance issues for 20 minutes during peak processing don’t look so bad when they’re spread out over an 8-hour window. It’s also true with STATSPACK that measuring things over too long of a period tends to level them off over time. Nothing will stand out and strike you as being wrong.

Q12) What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly? 

Ans: OPTIMIZER_INDEX_COST_ADJ= FORCE

Q13) Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9 AM and to run subsequently every other day at 2 AM. 

Ans: dbms_job.submit(:jobno,'statspack.snap;',trunc(sysdate)+9/24,'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

Q14) How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2 PM? 

Ans: 00 02 * * * /test/test.sh

Q15) In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?

Ans: SQL> SELECT MVIEW_NAME,LAST_REFRESH_DATE from USER_MVIEWS;

Oracle Database Interview Questions

Q16) How would you best determine why your MVIEW couldn’t FAST REFRESH? 

Ans: Possibly by checking the MVIEW LOG for errors.

Q17) How would you begin to troubleshoot an ORA-3113 error? 

Ans: End of File Communication Error. Check Alert Logfile. CheckNetwrok Latency. Check sqlnet.ora file has expire_time = 0, delete unwanted files and check the swap and temp spaces.

Q18) Which dictionary tables and/or views would you look at to diagnose a locking issue?

Ans: v$lock, v$session, v$process

Q19) An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed”, how do you approach troubleshooting this issue? 

Ans: Check the log and possible reason for the JOB failed.

Q20) How would you extract the DDL of a table without using a GUI tool? 

Ans: select dbms_metadata.get_ddl('OBJECT','OBJECT_NAME') from dual;

Q21) You’re getting high “busy buffer waits” - how can you find what’s causing it? 

Ans: Buffer busy-wait means that the queries are waiting for the blocks to be read into the db cache. There could be a reason when the block may be busy in the cache and the session is waiting for it. It could be undo, a data block or segment header wait.

Run the following query to find out the p1,p2, and p3 of a session causing buffer busy wait

sql> select p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait where event = 'buffer busy waits';

After that running the following query to find the segment causing buffer busy wait:-

sql> select owner,segment_name,segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1;

Q22) What query tells you how much space a tablespace named “test” is taking up, and how much space is remaining? 

Ans: 

SET SERVEROUTPUT ON SET LINESIZE 1000 SET FEEDBACK OFF

rem column dummy noprintcolumn  pct_used format 999.9

heading "%|Used" column  name    format a25     

heading "Tablespace Name" column  Kbytes   format 999,999,999   

heading "MBytes" column  used    format 999,999,999  

heading "Used(MB)" column  free    format 999,999,999 

heading "Free(MB)" column  largest    format 999,999,999 

heading "Largest" break  

on report compute sum of kbytes on report compute sum of free on report compute sum of used on report set page size 100 select nvl(b.tablespace_name, nvl(a.tablespace_name,'UNKOWN')) name,(kbytes_alloc/1024) kbytes, ((kbytes_alloc-nvl(kbytes_free,0))/1024) used,(nvl(kbytes_free,0)/1024) free,((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 "%used", nvl(largest,0)/1024  largest from ( select sum(bytes)/1024 Kbytes_free, max(bytes)/1024 largest,

tablespace_name from sys.dba_free_spacegroup by tablespace_name ) a, (select sum(bytes)/1024 Kbytes_alloc, tablespace_name from sys.dba_data_files group by tablespace_name )b

where a.tablespace_name (+) = b.tablespace_name order by 1 /

Q23) Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session is able to connect. 

Ans: Log into the system and find whether there are any deadlocks in the system using the following query.

select    'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking from    v$lock l1, v$lock l2 where    l1.block =1 and l2.request > 0 and    l1.id1=l2.id1 and    l1.id2=l2.id2 /

If so kill the processes caught in deadlock

alter system kill session 'SID, SERIAL#' immediate;

Also, find out which wait events exist in the system using the following commands and go in detail as to what events are causing these waits and take appropriate actions.

select event,count(*) from v$session group by event

/

select u.sid,u.serial#, u.username,p.spid,to_char(u.logon_time,'DD-MON-YYYY:HH24:MI:SS')  from v$session u, v$session w,v$process p where u.sid = w.sid and w.event like '%&a%' and u.paddr = p.addr

/

Q24) Database crashes. Corruption is found scattered among the file system neither of your doing nor Oracle’s. What database recovery options are available? The database is in archive log mode.

Ans: First of all secure all the archives and all the backups you have on the tape or other system. Then run fschk to check the filesystem. If the corruption is detected at the filesystem level and is not recoverable by fschk format the file system and restore the database through  RMAN.

Q25) How do you increase the OS limitation for open files (LINUX and/or Solaris)?

Ans: Set the file-max parameter is /etc/sysctl.conf to the number you want. Save the file and execute it by using command /etc/sysctl.conf-p

Q26) Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.

Ans: 

export ORACLE_BASE=/oracle

export ORACLE_HOME=/oracle/ora10g

export ORACLE_SID=ora10g

export path=$ORACLE_HOME/lib

sqlplus sys as sysdba << EOF

@/oracle/date.sql

exit;

Now the contents of /oracle/date.sql

select SYSDATE from dual;

select to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss') from dual; drop table tablename cascade constraints; select to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss') from dual;

/

Related Article: Oracle DBA Tutorial

Q27) Explain how you would restore a database using RMAN to Point in Time?

Ans: restore the database until time "to_date('Aug 27 2001 02:00:00','Mon DD YYYY HH24:MI:SS')"; recover database

Q28) How does Oracle guarantee the data integrity of data changes?

Ans: Oracle exadata training enables you to define and enforce data integrity constraints like PRIMARY KEY CONSTRAINTS, FOREIGN KEY CONSTRAINTS, and UNIQUE CONSTRAINTS.

Q29) Which environment variables are absolutely critical to run the OUI?

Ans: ORACLE_BASE, ORACLE_HOME, ORACLE_SID, path and library path

Q30) What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?

Ans: select count(1) from v$session where USERNAME='username';

 

Basic Oracle Apps DBA Interview Questions

Q31) What is a RAW datatype? 

Ans: RAW datatype stores values in the binary data format. The maximum raw size in a table is 32767 bytes. 

Q32) What is the use of the NVL function? 

Ans: The NVL function replaces NULL values with another or given value. For example, NVL(Value, replace value) 

Q33) What are nested tables? 

Ans: A nested table is an Oracle data type that supports columns containing multi-valued attributes. It also holds an entire sub table. 

Q34) How are comments represented in Oracle? 

Ans: You can represent comments in Oracle in two ways: 

1. Two dashes (-) before the beginning of the line - a single statement 

2. /*-- * / is used to represent it as comments of blocks of statements. 

Q35) What is DML? 

Ans: Data Manipulation Language (DML) is used to access and manipulate data in existing objects. The different DML statements are as follows: insert, select, update, and delete. 

Q36) What is the NULL value in Oracle? 

Ans: The NULL value represents the missing or unknown data. It is defined as a default entry to indicate that there is no actual data present. 

Q37) What is an aggregate function? 

Ans: In an aggregate function, values of multiple rows or records are joined together to receive a single value output. The standard aggregate functions are: 

• Average 

• Count 

• Sum 

Q38) What is a subquery? What are the different types of subqueries? 

Ans:

• A subquery is also called a nested query or inner query. It is used to get data from multiple tables. A subquery is added to the main query clause. 
• There are two different types of subqueries: 

1. Correlated subquery: It is not an independent query. 

2. Non-correlated subquery: It can be evaluated as if it were an independent query. 

Q39) What are the different temporal data types in Oracle? 

Ans: There are three temporal data types in Oracle: 

• Date data type: Different data formats 

• TimeStamp data type: Different timestamp formats

• Interval data type: Interval between dates and time. 

Q40) What is the difference between rename and alias? 

Ans: The differences between rename and alias are: 

Q41) What is a cursor variable? 

Ans: A cursor variable is associated with different statements that hold different values at run time. It is a reference type. 

Q42) What are the attributes or cursor? 

Ans: Each cursor in Oracle has its own set of attributes. Application programs use these attributes to test the status of the cursor, whether it is opened or closed, found, or not found, and also to find the row count. 

%FOUND

• Returns NULL if the cursor is open and fetch has not been executed. 

• Returns TRUE if the cursor fetch is executed successfully. 

• Returns FALSE if no rows are returned. 

%NOT FOUND 

• Returns NULL if the cursor is open and fetch has not been executed. 

• Returns FALSE if fetch has been executed. 

• Returns TRUE if no row is returned. 

%OPEN 

• Returns TRUE if the cursor is open. 

• Returns FALSE if the cursor is closed. 

%ROWCOUNT

• Returns the number of rows that have been fetched. It has to be repeated through the entire cursor to give an accurate count. 

Q43) What are the various constraints used in Oracle?

Ans: 

• NULL: It is used to indicate that a particular column can contain NULL values. 

• NOT NULL: It is used to indicate that a particular column cannot contain NULL values. 

• CHECK: It is used to validate that the values in a given column meet the specific criteria. 

• DEFAULT: It is used to indicate that the value is assigned to a default value. 

Q44) What are the different Oracle database objects?

Ans: 

• Tables: A set of elements organized vertically and horizontally. 

• Views: A virtual table that is derived from one or more tables. 

• Indexes: It is a performance tuning method for record processing. 

• Synonyms: It is an alias name for tables. 

• Sequences: Multiple users generate unique numbers. B

• Tablespaces: They are logical storage units in Oracle. 

Q45) What role does an Oracle software engineer play? 

Ans: Oracle software engineers address the software development lifecycle. They analyze the requirements, and then design, test, and develop software to meet those requirements. The engineers document the process with the use of diagrams and flowcharts and develop computer instructions through the use of algorithms. A software engineer should have a thorough understanding of computer systems to detect any hardware limitations that could affect the software design. 

Q46) What are the responsibilities of an Oracle software engineer?

Ans:  

• Collaborating with other programmers to design and implement features. 
• Producing well-organized, optimized, and documented source codes. 
• Creating software tools for other developers, 
• Debugging existing source codes. 
• Contributing to technical design documentation. 

Oracle DBA Scenario Based Interview Questions

Q47) Explain Oracle Database. 

Ans: The Oracle database contains physical and logical structures that store the system, user, and control information. The software that manages the database is called the Oracle database server. The software that runs both Oracle and the physical database is called the Oracle Database system. 

Q48) What is an Oracle Index? 

Ans: An optional structure associated with a table is called an index. It has direct access to the rows that increase the performance of data retrieval.

Q49) What is a system tablespace? 

Ans: A system tablespace contains all the data, dictionary tables. It helps in the formation of different database objects. 

Q50) What is the difference between a hot backup and a cold backup? 

Ans:

Q51) What is a recovery catalog? 

Ans: A recovery catalog is a database plan that holds the metadata used by RMAN for reinstallation and healing processes. It stores information on: 

• Data files and backup files 
• Incarnation 
• Stored scripts 
• Backup history 
• Archived redo logs and their backup sets. 

Q52) What is cache fusion? 

Ans: Cache Fusion is the process of transferring data from one case buffer cache to another at a rate within a cluster. We can access the data block directly from the cache. 

Q53) What are the different types of segments? 

Ans: The different types of segments are: 

• Data segment 
• Index segment 
• Rollback segment 
• Temporary segment 

Q54) What are the components of a SELECT statement? 

Ans:

1. Projection: It defines a set of columns to read from the table. 

2. FROM: It defines a set of tables or views to read data. 

3. Predicate: It filters and is also called the “where” clause. 

4. Sorting: It sorts the result, according to columns. 

5. Grouping: It groups the results according to columns.

Q55) What are the duties and responsibilities of an Oracle DBA in an organization? 

Ans: An Oracle DBA has the following responsibilities: 

• An Oracle DBA manages the Oracle databases of the organization. 
• On a running system, only the DBA can shut down and startup the database. 
• The DBA creates new users and manages the privileges of users. 
• The DBA also takes regular backups and ensures the safety of the data. In case of a disaster, the DBA is responsible for restoring the database from the backups. 
• The DBA monitors space usage and does capacity planning for the database. 
• The DBA is responsible for enforcing security policies. 

Q56) What are the components of an Oracle Database? 

Ans: An Oracle database is composed of files stored on the disk. There are three types of such files: 

• DataFiles: These contain the “user” data or the “system” data. 
• OnlineRedo Log Files: These contain the “change” records. 
• ControlFiles: These files contain information like the location of data files, online redo log files, etc. 

Q57) What are the tools available to start up an Oracle database? 

Ans: There are three tools you can use: 

• SQL*Plus
• Oracle Enterprise Manager 
• RMAN 

Q58) What are the different shutdown modes of an Oracle database? 

Ans: There are four shutdown modes: 

• Normal: In this mode, new connections are not allowed. The database is closed after all the sessions are disconnected. 
• Immediate: New connections are not allowed in this mode. The existing active transactions are rolled back. 
• Transactional: In this mode, new connections are not allowed, and Oracle waits until all active transactions are completed. 
• Abort: In this mode, the database performs an instance recovery next time it is started in the case of the database not shutting down cleanly. 

Q59) What is the difference between a public synonym and a private synonym? 

Ans: Any database user can access a public synonym whereas a private synonym can be accessed only by the owner. 

Q60) What is the Oracle datafile? 

Ans: The Oracle datafile is a big physical storage unit that stores tables and indexes allocated to the database. Databases consist of one or more data files. 

Q61) Define SQL? 

Ans: Structured query language (SQL) is a database computer language that is designed to manage data in relational database management systems (RDBMS). It includes data insert, query, updates and deletes, schema creation and modification, and data access control. 

Q62) What are the different types of SQL statements? 

Ans: There are five types of SQL statements: 

• Data definition language 
• Data manipulation language 
• Transactional control 
• Session control and 
• System control. 

Oracle DBA Interview Questions for 3 years Experienced

Q63) What are the steps to perform a complete recovery on the whole database? 

Ans: The steps to perform a complete recovery are on the whole database are: 

• Mount the database. 
• Ensure that all the data files you want to recover are online 
• Restore a backup of the whole database 
• Apply online or archived redo logs, or a combination of both. 

Q64) What is a schema? 

Ans: A schema is a collection of database objects of a user. 

Q65) What are schema objects? 

Ans: Schema objects are the logical structures that contain the database data. They include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages, and database links.

Q66) What are the advantages of views in Oracle?

Ans: 

• They provide additional table security. 
• They hide data complexity. 
• They simplify the commands for the user. 
• They present the data from different perspectives from that of the base table. 
• They store complex queries. 

Q67) What does the control file contain? 

Ans: A control file is a small binary file that records the database’s physical structure. It contains: 

• The database name 
• The names and locations of the associated data files and online redo log files 
• The timestamps of the database creation 
• The current log sequence numbers 
• Checkpoint information. 

Q68) What is row chaining? 

Ans: Row Chaining happens when the row is so large that it cannot fit into one data block when it is first inserted. In row chaining, Oracle stores the data for the row in a chain of one or more data blocks. 

Q69) What is a synonym in Oracle? 

Ans: A synonym is an identifier that is used to reference database objects in a SQL statement. A synonym may be created for the following types of database objects: table, view, sequence, or another synonym.