12 April, 2021
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.
If you would like to enrich your career in Oracle Apps with certified working professionals, then visit Tekslate - an e-learning marketplace: "Oracle Apps Technical Training" course. This course will provide placement oriented training with hands-on-real-time projects.
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.
Ans: list archive log all;
Ans: df -kh and du-sh
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.
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.
Ans: Daily Monitoring activities and check different logs for any sort of errors.
Ans: hostname, uname -n and ifconfig
Ans: lsnrctl stat or ps-eaf|grep tns
Inclined to build a profession as an Oracle DBA Developer? Then here is the blog post on, explore Oracle DBA Training
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.
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’);
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.
Ans: OPTIMIZER_INDEX_COST_ADJ= FORCE
Ans: dbms_job.submit(:jobno,'statspack.snap;',trunc(sysdate)+9/24,'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
Ans: 00 02 * * * /test/test.sh
Ans: SQL> SELECT MVIEW_NAME,LAST_REFRESH_DATE from USER_MVIEWS;
Ans: Possibly by checking the MVIEW LOG for errors.
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.
Ans: v$lock, v$session, v$process
Ans: Check the log and possible reason for the JOB failed.
Ans: select dbms_metadata.get_ddl('OBJECT','OBJECT_NAME') from dual;
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;
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 pagesize 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_space group 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 /
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
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.
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
sqlplus sys as sysdba << EOF
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;
Ans: restore the database until time "to_date('Aug 27 2001 02:00:00','Mon DD YYYY HH24:MI:SS')"; recover database
Ans: Oracle exadata training Bangalore enables you to define and enforce data integrity constraints like PRIMARY KEY CONSTRAINTS, FOREIGN KEY CONSTRAINTS, and UNIQUE CONSTRAINTS.
Ans: ORACLE_BASE, ORACLE_HOME, ORACLE_SID, path and library path
Ans: select count(1) from v$session where USERNAME='username';
Ans: RAW datatype stores values in the binary data format. The maximum raw size in a table is 32767 bytes.
Ans: The NVL function replaces NULL values with another or given value. For example, NVL(Value, replace value)
Ans: A nested table is an Oracle data type that supports columns containing multi-valued attributes. It also holds an entire sub table.
Ans: You can represent comments in Oracle in two ways:
Two dashes (-) before the beginning of the line - a single statement
/*-- * / is used to represent it as comments of blocks of statements.
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.
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.
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:
Non-correlated subquery: It can be evaluated as if it were an independent query.
Ans: There are three temporal data types in Oracle:
TimeStamp data type: Different timestamp formats
Interval data type: Interval between dates and time.
Ans: The differences between rename and alias are:
It is a permanent name given to a table or a column.
It is a temporary name given to a table or column.
It is a replacement for a name.
It is an alternate name of the table or column.
Ans: A cursor variable is associated with different statements that hold different values at run time. It is a reference type.
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.
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.
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.
Returns TRUE if the cursor is open.
Returns FALSE if the cursor is closed.
Returns the number of rows that have been fetched. It has to be repeated through the entire cursor to give an accurate count.
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.
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.
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.
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.
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.
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.
Ans: A system tablespace contains all the data, dictionary tables. It helps in the formation of different database objects.
It is the process of taking the database endorsement when the database is on a row mode. It is also called online Backup.
It is used only when the database is in shut down mode. It is also called offline backup.
Ans: A recovery catalog is a database plan that holds the metadata used by RMAN for reinstallation and healing processes. It stores information on:
Archived redo logs and their backup sets.
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.
Ans: The different types of segments are:
Projection: It defines a set of columns to read from the table.
FROM: It defines a set of tables or views to read data.
Predicate: It filters and is also called the “where” clause.
Sorting: It sorts the result, according to columns.
Grouping: It groups the results according to columns.
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.
Ans: An Oracle database is composed of files stored on the disk. There are three types of such files:
OnlineRedo Log Files: These contain the “change” records.
ControlFiles: These files contain information like the location of data files, online redo log files, etc.
Ans: There are three tools you can use:
Oracle Enterprise Manager
Ans: There are four shutdown modes:
Ans: Any database user can access a public synonym whereas a private synonym can be accessed only by the owner.
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.
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.
Ans: There are five types of SQL statements:
Data manipulation language
Session control and
Ans: The steps to perform a complete recovery are on the whole database are:
Ans: A schema is a collection of database objects of a user.
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.
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.
Ans: A control file is a small binary file that records the database’s physical structure. It contains:
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
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.
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.
For In-depth knowledge on Oracle DBA click on: