Oracle DBA Interview Questions and Answers

• Database offer a single point of mechanism for storing and retrieving information with the help of tables.
• Table is made up of columns and rows where each column stores specific attribute and each row displays a value for the corresponding attribute.
• It is a structure that stores information about the attributes of the entities and relationships among them.
• It also stores data types for attributes and indexes.
• Well known DBMS include Oracle, ibm db2, Microsoft sql server, Microsoft access, mysql and sqlLite.

Two types of storage systems are available

• Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM)
• Most databases use RDBMS model, Oracle also uses RDBMS model.
• Hierarchical Storage Management System (HSM)
• Information Management System (IMS) from IBM.
• Integrated Database Management System (IDMS) from CA.

Join methods are of mainly 3 types.

Merge Join

• Sorting both the tables using join key and then merge the rows which are sorted.

Nested loop join

• It gets a result set after applying filter conditions based on the outer table.
• Then it joins the inner table with the respective result set.

Hash join –

• It uses hash algorithm first on smaller table and then on the other table to produce joined columns. After that matching rows are returned.

Components of logical data model are

Entity – Entity refers to an object that we use to store information. It has its own table.

Attribute – It represents the information of the entity that we are interested in. It is stored as a column of the table and has specific datatype associated with it.

Record – It refers to a collection of all the properties associated with an entity for one specific condition, represented as row in a table.

Domain – It is the set of all the possible values for a particular attribute.

Relation – Represents a relation between two entities.

Logical data model represents database in terms of logical objects, such as entities and relationships.

Physical data model represents database in terms of physical objects, such as tables and constraints.

EXISTS can be better than IN under various conditions.

UNION ALL is faster than UNION (not sorting).

• CREATE INDEX.
• MINUS
• GROUP BY.
• ORDER BY.
• UNINDEXED TABLE JOIN.
• UNION
• DISTINICT

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics.

In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

The Job Queue Processes.

Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.

ALTER SYSTEM SWITCH LOGFILE.

Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.

A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>.

ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>.

DBA_DATA_FILES.

DBA_FREE_SPACE.

Turn on fine grain auditing for the table.

ALTER INDEX <index_name> REBUILD.

Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

The ANALYZE command.

Use the DBMS_SESSION.SET_SQL_TRACE or

Use ALTER SESSION SET SQL_TRACE = TRUE.

These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.

SMON: System Monitor Process – Performs recovery after instance failure, monitors temporary segments and extents; cleans temp segments, coalesces free space (mandatory process for DB and starts by default).

PMON: Process Monitor – Recovers failed process resources. In Shared Server architecture, monitors and retarts any failed dispatcher or server proceses (mandatory process for DB and starts by default).

[oracle@hostname ~]$ ps -ef |grep -e pmon -e smon |grep -v grep

oracle 6755 1 0 12:59 ? 00:00:05  ora_pmon_DB1_SID

oracle 6779 1 0 12:59 ? 00:00:06  ora_smon_DB1_SID.

The db_files parameter is a “soft limit ” parameter that controls the maximum number of physical OS files that can map to an Oracle instance. The maxdatafiles parameter is a different – “hard limit” parameter. When issuing a “create database” command, the value specified for maxdatafiles is stored in Oracle control files and default value is 32. The maximum number of database files can be set with the init parameter db_files.

A tablespace is a logical storage unit within an Oracle database. Tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server.

A datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace.The tablespace builds the bridge between the Oracle database and the filesystem in which the table’s or index’ data is stored.

There are three types of tablespaces in Oracle:

• Permanent tablespaces.
• Undo tablespaces.
• Temporary tablespaces.

Before Oracle changes data in a datafile it writes these changes to the redo log. If something happens to one of the datafiles, a backed up datafile can be restored and the redo, that was written since, replied, which brings the datafile to the state it had before it became unavailable.

The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures. The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE.

The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.

In 90% cases – NEVER. When the data in index is sparse (lots of holes in index, due to deletes or updates) and your query is usually range based. Also index blevel is one of the key indicators of performance of sql queries doing Index range scans.

YES. You can create and rebuild indexes online.

This enables you to update base tables at the same time you are building or rebuilding indexes on that table.

You can perform DML operations while the index build is taking place, but DDL operations are not allowed.

Parallel execution is not supported when creating or rebuilding an index online.

The following statements illustrate online index build operations:

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE.

The Buffer Cache Advisor provides advice on how to size the Database Buffer Cache to obtain optimal cache hit ratios. Member of Performance Advisors –> Memory Advisor pack.

STATSPACK: is a performance diagnosis tool provided by Oracle starting from Oracle 8i and above.STATSPACK is a diagnosis tool for instance-wide performance problems it also supports application tuning activities by providing data which identifies high-load SQL statements.

Although AWR and ADDM (introduced in Oracle 10g) provide better statistics than STATSPACK,

users that are not licensed to use the Enterprise Manager Diagnostic Pack should continue to use statspack.

A schema is collection of database objects of a user.

Schema objects are the logical structures that directly refer to the database’s data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses).

A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

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.

When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

An integrity constraint is a declarative way to define a business rule for a column of a table.

An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses).

A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

A synonym is an alias for a table, view, sequence or program unit.

Only its owner can access a private synonym.

Any database user can access a public synonym.

A Cluster with an index on the Cluster Key.