Top Oracle DBA Interview Questions & Answers 2024

If you’re seeking Oracle DBA Interview Questions for both experienced professionals and freshers, you’ve come to the right place. Numerous reputed companies worldwide are offering opportunities in this field. Research indicates that Oracle DBA holds a market share of approximately 70%.

Categories of Oracle DBA Interview Questions and Answers

For Experienced
For Advance Level
For Freshers

Oracle DBA Interview Questions For Freshers & Experienced

Q.1 What are clusters?

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

Q.2 What is cluster key?

The related columns of the tables in a cluster are called the cluster key.

Q.3 What is index cluster?

A cluster with an index on the cluster key.

Q.4 What are the components of physical database structure of Oracle database?

Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

Q.5 What is a tablespace?

A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

Q.6 What are the components of logical database structure of Oracle database?

There are tablespaces and database’s schema objects.

Q.7 What is SYSTEM tablespace and when is it created?

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

Q.8 Explain the relationship among database, tablespace and data file?

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

Q.9 What is schema?

A schema is collection of database objects of a user.

Q.10 What are Schema Objects?

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.

Q.11 Can objects of the same schema reside in different table spaces?

Yes.

Q.12 Can a tablespace hold objects from different schemes?

Yes.

Q.13 What is Oracle table?

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.

Q.14 What is an Oracle view?

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.)

Q.15 Do a view contain data?

Views do not contain or store data.

Q.16 Can a view based on another view?

Yes.

Q.17 What are the advantages of views?

Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
Hide data complexity.
Simplify commands for the user.
Present the data in a different perspective from that of the base table.
Store complex queries.

Q.18 What is an Oracle sequence?

A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.

Q.19 What is a synonym?

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

Q.20 What are the types of synonyms?

There are two types of synonyms private and public.

Q.21 What is a private synonym?

Only its owner can access a private synonym.

Q.22 What is a public synonym?

Any database user can access a public synonym.

Q.23 What are synonyms used for?

Mask the real name and owner of an object.
Provide public access to an object
Provide location transparency for tables, views or program units of a remote database.
Simplify the SQL statements for database users.

Q.24 What is an Oracle index?

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.

Q.25 What would be the main responsibilities of an Oracle DBA in an organization?

An Oracle DBA (Database Administrator) plays a crucial role in managing and maintaining an organization’s Oracle databases. Here are the main responsibilities:

Database Installation and Configuration: Setting up Oracle databases, including installing software and configuring database parameters.
Database Maintenance: Performing regular maintenance tasks such as backups, patching, and updates to ensure database health and security.
Performance Tuning: Monitoring and optimizing database performance to ensure efficient data retrieval and processing.
Security Management: Implementing security measures to protect data, including user access controls and encryption.
Data Recovery: Planning and executing data recovery strategies to minimize data loss in case of failures.
Database Design: Designing database schemas, tables, and relationships to support application requirements.
Troubleshooting: Identifying and resolving database-related issues and errors.
Capacity Planning: Forecasting future database growth and planning for necessary resources.
Documentation: Maintaining detailed documentation of database configurations, procedures, and changes.
Collaboration: Working with developers, system administrators, and other stakeholders to support database-related needs.

These responsibilities ensure the database systems are reliable, secure, and perform optimally to support the organization’s operations.

Q.26 How would you go about creating a response file to expedite the database installation process?

Creating a response file to expedite the Oracle database installation process is a common task for DBAs. Here’s a step-by-step approach:

Run the Oracle Universal Installer (OUI) in Interactive Mode:

Start the OUI and proceed with the installation steps as you normally would.
When you reach the summary page, you have the option to save the installation settings to a response file.

Save the Response File:

Click on the “Save Response File” button.
Choose a location and file name for the response file. This file will contain all the installation parameters you selected.

Edit the Response File (if necessary):

Open the saved response file in a text editor.
Modify any parameters as needed. For example, you might want to change the installation path or database name.

Run the Installer in Silent Mode:

Use the response file to perform the installation in silent mode. This allows the installation to proceed without any user interaction.
Execute the following command in the terminal:

./runInstaller -silent -responseFile /path/to/response_file.rsp

Run Post-Installation Scripts:

After the installation completes, you may need to run some root scripts as prompted by the installer.

By following these steps, you can streamline the installation process and ensure consistency across multiple installations.

Q.27 What is Oracle Instance?

An Oracle instance is a crucial component in Oracle Database architecture. It consists of the memory structures and background processes that manage database files. Here’s a breakdown:

Memory Structures:

System Global Area (SGA): A shared memory area that contains data and control information for the database instance. It includes components like the database buffer cache, shared pool, and redo log buffer.
Program Global Area (PGA): A memory area used by a single Oracle server process. It contains data and control information exclusive to that process.

Background Processes:

Database Writer (DBWR): Writes modified data from the database buffer cache to the data files.
Log Writer (LGWR): Writes redo log entries from the redo log buffer to the online redo log files.
Checkpoint (CKPT): Signals DBWR to write data to disk and updates data file headers and control files.
System Monitor (SMON): Performs recovery when the instance starts and cleans up temporary segments.
Process Monitor (PMON): Cleans up after failed processes and releases resources.

An Oracle instance is started and stopped independently of the database it manages. When an instance is started, it allocates memory and starts the background processes. When it is stopped, it deallocates memory and terminates the processes.

Q.28 What are the stages of Oracle Startup?

When starting an Oracle database, the instance goes through three main stages: NOMOUNT, MOUNT, and OPEN. Here’s a brief overview of each stage:

NOMOUNT Stage:

The instance is started, but it is not yet associated with any database.
Oracle reads the initialization parameter file (SPFILE or PFILE) to configure the instance.
Memory structures (SGA) are allocated, and background processes are started.

MOUNT Stage:

The instance is associated with a database by reading the control files specified in the initialization parameters.
The control files are opened, but the data files and redo log files are not yet accessible.
This stage is often used for database maintenance tasks, such as renaming data files or enabling archiving.

OPEN Stage:

The database is fully opened, and all data files and redo log files are accessible.
The database is available for normal operations, allowing users to connect and perform transactions.

These stages ensure that the database is properly initialized and ready for use.

Q.29 What are the types of shutdown modes in an Oracle Database?

What are the types of shutdown modes of an Oracle database?

Oracle databases can be shut down using several different modes, each serving a specific purpose. Here are the main shutdown modes:

Normal Shutdown:

The database waits for all currently connected users to disconnect.
No new connections are allowed during this process.
This mode ensures a clean shutdown without requiring instance recovery on the next startup.

Immediate Shutdown:

The database terminates all active transactions and disconnects all users immediately.
All uncommitted transactions are rolled back.
This mode is faster than a normal shutdown and does not require instance recovery.

Transactional Shutdown:

The database waits for all active transactions to complete before shutting down.
No new transactions are allowed.
This mode ensures that all work is saved without requiring users to log off manually.

Abort Shutdown:

The database shuts down immediately without waiting for users or transactions.
This mode is similar to a power-off situation and can leave the database in an inconsistent state.
Instance recovery is required on the next startup.

Each shutdown mode is useful in different scenarios, depending on the urgency and the need to maintain data consistency.

Q.30 In what sequence does Oracle software search for a parameter file during database startup?

During the startup of an Oracle database, the software searches for a parameter file in the following sequence:

Server Parameter File (SPFILE) with the database SID: spfileORACLE_SID.ora
Default Server Parameter File: spfile.ora
Text Initialization Parameter File: initORACLE_SID.ora

If none of these files are found in the default location, the startup process will fail unless you specify the parameter file explicitly using the SPFILE or PFILE parameters in the STARTUP command.

Q.31 Explain the Oracle Database architecture components and their roles?

The Oracle Database architecture consists of several key components:

System Global Area (SGA): A shared memory region that contains data and control information for one Oracle Database instance. It includes: Database Buffer Cache: Stores copies of data blocks read from the database. Shared Pool: Caches various constructs that can be shared among users, such as SQL statements and data dictionary cache. Redo Log Buffer: Stores redo entries that describe changes made to the database. Large Pool, Java Pool, Streams Pool: Used for specific memory needs.

Program Global Area (PGA): A memory region that contains data and control information for a server process.

Database Files: Data Files: Store actual data. Control Files: Maintain the structure of the database. Redo Log Files: Record all changes made to the data.

Oracle Processes: Server Processes: Handle user requests. Background Processes: Perform maintenance tasks, such as DBWR (Database Writer), LGWR (Log Writer), SMON (System Monitor), PMON (Process Monitor), CKPT (Checkpoint), and others.

Q.32 Describe the steps involved in installing Oracle Database software on a Linux system?

1. Prepare the System:

Check System Requirements: Ensure your system meets the hardware and software requirements for the Oracle version you are installing.
Create Required Groups and Users: Create the necessary groups (e.g. “oinstall”, “dba”) and a user (e.g. “Oracle”) for the installation.

2. Download Oracle Software:

Download the Oracle Database software from the Oracle website.

3. Configure Kernel Parameters:

Modify kernel parameters as required by Oracle. This typically involves editing the “/etc/sysctl.conf” file and applying the changes using “sysctl -“.

4. Set Up Environment Variables:

Set environment variables such as “ORACLE_BASE”, “ORACLE_HOME”, and “PATH” in the Oracle user’s profile (e.g. “.bash_profile”).

5. Unzip and Install the Software:

Unzip the downloaded Oracle software and navigate to the “runInstaller” script.
Run the “runInstaller” script to start the Oracle Universal Installer (OUI).

6. Follow the Oracle Universal Installer (OUI):

Follow the prompts in the OUI to install the Oracle Database software. This includes specifying the Oracle base directory, Oracle home directory, and other configuration options.

7. Run Root Scripts:

After the installation, the OUI will prompt you to run some scripts as the root user. These scripts typically configure the Oracle Inventory and set permissions.

8. Create and Configure a Database:

Use the Database Configuration Assistant (DBCA) to create and configure a new database. This can be done during the installation or afterward.

9. Post-Installation Tasks:

Verify the installation by connecting to the database using SQL*Plus.
Apply any necessary patches using the Oracle OPatch utility.

Q.33 Explain the difference between RMAN full backup, incremental backup, and cumulative incremental backup.

1. Full Backup

Definition: A full backup captures the entire database, including all data files, control files, and archived redo logs.
Usage: This type of backup is comprehensive and can be used to restore the entire database to the point in time when the backup was taken.
Frequency: Typically performed less frequently due to the time and storage space required.

2. Incremental Backup

Definition: An incremental backup captures only the data blocks that have changed since the last backup (either full or incremental).
Levels:
Level 0: Equivalent to a full backup. It serves as the base for subsequent incremental backups.
Level 1: Backs up only the blocks that have changed since the last Level 0 or Level 1 backup.
Usage: Useful for reducing backup time and storage space, as only changed data is backed up.
Example: If you take a Level 0 backup on Sunday and Level 1 backups on Monday, Tuesday, and Wednesday, the Wednesday backup will include changes since Tuesday.

3. Cumulative Incremental Backup

Definition: A cumulative incremental backup captures all the data blocks that have changed since the last Level 0 backup.
Difference from Incremental Backup: Unlike a regular incremental backup, which only captures changes since the last incremental backup, a cumulative incremental backup includes all changes since the last full (Level 0) backup.
Usage: Simplifies the restore process because you only need the last cumulative backup and the Level 0 backup to restore the database.
Example: If you take a Level 0 backup on Sunday and cumulative incremental backups on Monday, Tuesday, and Wednesday, the Wednesday backup will include all changes since Sunday.

Q.34 How does Oracle Data Guard work, and what are its different protection modes?

Oracle Data Guard is a feature of the Oracle Database that ensures high availability, data protection, and disaster recovery. It achieves this by maintaining one or more standby databases as copies of the primary (production) database. Here’s how it works and the different protection modes it offers:

How Oracle Data Guard Works

1. Primary and Standby Databases:

Primary Database: This is the main database where all transactions occur.

Standby Database: This is a copy of the primary database. It can be either a physical standby (exact copy) or a logical standby (data is logically the same but can be reorganized).

2. Redo Transport Services:

Oracle Data Guard uses redo transport services to send redo data from the primary database to the standby databases. This ensures that the standby databases are kept up-to-date with the primary database.

3. Apply Services:

On the standby database, apply services apply the redo data to keep the standby database synchronized with the primary database. This can be done in real-time (real-time apply) or at regular intervals.

4. Role Transitions:

In the event of a failure or planned maintenance, Oracle Data Guard can switch the roles of the primary and standby databases. This process is known as a switchover (planned) or failover (unplanned).

Protection Modes

Oracle Data Guard provides three protection modes, each offering a different balance between data protection and performance:

1. Maximum Protection:

Goal: Zero data loss.
Mechanism: Transactions on the primary database do not commit until the redo data is written to both the local online redo log and at least one standby redo log.
Impact: This mode can impact performance because it requires synchronous redo transport. If the standby database is unavailable, the primary database will shut down to ensure no data loss.

2. Maximum Availability:

Goal: Zero data loss without compromising availability.

Mechanism: Similar to Maximum Protection, but if the standby database is temporarily unavailable, the primary database continues to operate in a mode similar to Maximum Performance until the standby is available again.

Impact: Provides a balance between data protection and availability. It ensures no data loss as long as the standby database is available.

3. Maximum Performance:

Goal: Minimize impact on primary database performance.
Mechanism: Transactions commit as soon as the redo data is written to the local online redo log. Redo data is then asynchronously sent to the standby database.
Impact: This mode offers the best performance but may result in some data loss if the primary database fails before the redo data is transmitted to the standby.

Oracle Data Guard is a powerful tool for ensuring the resilience and reliability of your database environment.

Q.35 Describe the process of applying a patch to an Oracle database.

Applying a patch to an Oracle database involves the following steps:

Pre-Patching Tasks: Review the patch documentation and release notes. Verify that a backup of the database and related files exists. Check for any prerequisite patches.
Download the Patch: Download the patch from the Oracle Support website.
Apply the Patch: Stop the Oracle services (database, listener). Use the OPatch utility to apply the patch:

$ export ORACLE_HOME=/path/to/oracle_home

$ $ORACLE_HOME/OPatch/opatch apply

Follow the OPatch prompts and review the log files for any errors.
Post-Patching Tasks: Start the Oracle services. Run any required SQL scripts provided with the patch. Verify the patch application by checking the inventory and ensuring the database functions correctly.

Q.36 What are the roles of DBA?

A DBA plays a crucial role in database management, responsible for user administration, storage management, security, performance monitoring, and executing backup and recovery protocols.

Q.37 What is an ASM instance?

An ASM (Automatic Storage Management) instance manages disk groups comprising the System’s Global Area (SGA) and background processes, essential for efficient database file layout and metadata management.

Q.38 Explain Briefly Shared Server Architecture.

Shared Server Architecture in Oracle reduces the need for dedicated server processes by routing requests to a shared pool of server processes, optimizing memory use and user support.

Q.39 What is a backup set?

A backup set in the RMAN context is a collection of backup files generated by an RMAN backup command consisting of one or more backup pieces.

Q.40 What do you mean by recovery catalog?

A recovery catalog is a repository of metadata used by RMAN for restoration and recovery processes. It stores detailed information about data files, backup history, archived redo logs, and more.

Q.41 Can you drop the redo log groups while the database is up and running?

Yes, we can drop the redo-log group but the redo log should be inactive

Q.42 What is the difference between Oracle home and oracle base

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

Q.43 Where do you check the free space of objects?

SQL>dba_free_space;
select * from dba_free_space;

Q.44 What is the difference between putty and sqlplus?

Putty is an SSH client to connect to the database host from remotely.
SQL database connectivity tool to connect to the database using tns names entry

Q.45 Can you drop the system tablespace, if so what happened to database?

No, we can’t drop the system tablespace.Oracle will not allow it

Q.46 Can you drop the normal tablespace, if so what happened to database?

Yes we can drop the normal table spaces but the associated objects will be dropped
but the table space should be empty if not we have to use
SQL>drop tablespace tablespace name including contents;
if you want to drop the associated datafiles also with table space we should use
SQL>drop tablespace tablespace name including contents and datafiles;

Q.47 How to multiplex redo log files?

alter database add log ‘  ‘ to group3;

Q.48 How to add redo log groups to a database?

alter database add log ‘ ‘ size 50m group6;
v$log or v$logfiles;

Q.49 What is tns string?

tns string is an entry to identify the database host,db port and the db name.Oracle will use oracle sqlplus will use this entries appropriate or right database host.

Q.50 What is tnsentry?

Tns entry is a address to the database host and database written in the tnsnames.ora, generally tnsnames.ora located at $ORACLE_HOME/network/admin

Q.51 Where to view the undo usage information?

v$undostat

Q.52 How to find whether the instance is using spfile or pfile?

show parameter spfile;

Q.53 How to set the new password for Oracle user?

alter user username identified by newpassword;

Q.54 How to create password file?

orapwd file=$ORACLE_HOME/dbs/pwjohn.ora entry=5 ignore case=Y;

Q.55 What is checkpoint? why database need it?

Checkpoint which occurs when ever the redo-log switch happens, during this ckpt process writes the check point information to control file and the data file header and tells to dbwr to flush the dirty buffer from buffer cache to disk until that check point.

Q.56 How to revoke privilege or role?

revoke select on T from John;
revoke Walt from John; ( Walt is a role here )

Q.57 Where do you find the password for oracle user?

select username,password from dba_users where username=’username’;

Q.58 How to switch from pfile to spfile?

SQL>create spfile from pfile;
bounce the database;

Now the database will pickup the spfile automatically

Q.59 Can you kill the pmon or smon or ckpt ? what happens to database?

These are all the mandatory process to run the database. if we kill any of the process the DB will be crash.

Q.60 How to set the same password to oracle user when the password is expired?

select username,password from dba_users where username=’username’;
SQL>alter user username identified by values ‘above password’;

Q.61 How to change the default tablespace for a user?

alter user John default tablespace t;

Q.62 How to set the password to expiry of 90 days?

Identify the profile for that user

SQL> select username,default_profile from dba_user where username=’user’;
SQL> select * from dba_profiles where profile=’DEFAULT’;
change the profile for password life time
SQL> alter profile default set limit=’PASSWORD_LIFE_TIME=90′;

Q.63 What is log switch, when does it occurs?

Log switch occurs when the current redo log is full and the log writer has to go to next redo log group.

Q.64 Where to see the information about latches?

v$latch and v$latch_children

Q.65 How to give a tablespace quota to a user?

alter user John grant unlimited quota on tablespace t;

Q. 66 How you restore Database from available backup? Describe with steps.

At the RMAN prompt, connect to the target database using the command connect target.
At the RMAN prompt, start the database in NOMOUNT state using the command:

RMAN> STARTUP NOMOUNT;

Restore the Server Parameter File from Autobackup using the command

RMAN> RESTORE SPFILE FROM “<PATH_TO_AUTOBACKUP_OF_SPFILE>”;

Restore the Control File from Autobackup using the command

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
RMAN> exit
[oracle@prim ~]$ sqlplus / as sysdba
startup

Q.67 Describe few steps of Oracle DB Upgrade?

Backup your database.
Empty recycle bin & gather stats.
Run oracle’s database pre-upgrade utility.
Install the oracle’s 19c database s/w.
Run the DBUA Utility.
Run post-upgrade script & restart database.

Q.68 How to check version of Database?

SELECT * FROM V$VERSION ;

Q.69 You recently changes the password of USER U1 and after sometime USER U1 come to you that he is not able to connect to DB what is the reason and what you will check to resolve?

1st thing is listener should be up & Running.
2nd this is try to set another password to the USER U1

alter user usename identified by new_password;

Q.70 What is command for adding datafiles on tablespace?

alter tablespace tablespace_name add datafile ‘ DataFile_name’ size 5m;

Q.71 What the error related to UNDO Tablespace?

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

Q.72 Have you worked on RAC, What are the services related to RAC?

Oracle Services.
Database Resource Manager.
Oracle Rac High Availability Framework.
FAN (Fast Application Notification)
Load Balancing Advisory.
Connection Load Balancing.

Q.73 What is snapshot too old error? Why it is encounter?

ORA-01555 Error Message “Snapshot Too Old” Error ORA-01555 contains the message, “snapshot too old.” This message appears as a result of an Oracle read consistency mechanism. While your query begins to run, the data may be simultaneously changed by other people accessing the data.

Q.74 Difference between traditional IMP/EXP vs Current IMPDP/EXPDP?

Below Are the differences in tradionational exp/imp vs Datapump

Datapump operates on a group of files called dump file sets. However, normal export operates on a single file.
Datapump access files in the server (using ORACLE directories). Traditional export can access files in client and server both (not using ORACLE directories).
Exports (exp/imp) represent database metadata information as DDLs in the dump file, but in datapump, it represents in XML document format.
Datapump has parallel execution but in exp/imp single stream execution.
Datapump does not support sequential media like tapes, but traditional export supports.
Impdp/Expdp use parallel execution rather than a single stream of execution, for improved performance.
Data Pump will recreate the user, whereas the old imp utility required the DBA to create the user ID before importing.
In Data Pump, we can stop and restart the jobs.

Q.75 Is differential & Cumulative backup is same?

No,

Differential: The default type of incremental backup that backs up all blocks changed after the most recent incremental backup at either level 1 or level 0
Cumulative: Backs up all blocks changed after the most recent backup at level 0

Q.76 DR stands for?

DR stands for Disaster Recovery.

Q.77 what s the full form of DBCA?

Database Configuration Assistant.

Q.78 How you create a USER?

Create user username identified by password;

Q.79 What do you mean by the statement select * from HR.TABLE? what is HR here?

This query will display all the information from TABLE. HR IS THE SCHEMA NAME

Q.80 Can We resize redolog file?

We cannot resize the redo log files. We must drop the redolog file and recreate them .This is only method to resize the redo log files.

Q.81 What are the different modes of oracle dataguard?

Physical Standby Databases. A physical standby database is an exact, block-for block copy of a primary database.
Logical Standby Databases
Snapshot Standby Databases
Active Dataguard

Q.82 What are the steps for deleting archivelogs?

Connect to RMAN target / 
Delete archivelog all;

Q.83 Database startup and shutdown stages?

Startup Stages :

No mount
Mount
Open

Shutdown Stages:

database closed
database dismounted
Oracle Instance Shutdown

Q.84 Can you rename the table once it is created?

Yes, we can rename the table once it is created. Connect to user Oracle

SQL> connect Oracle/Oracle
SQl> select * from tab
SQL> create table t as select * from user_tables;
(or)
SQL> create table dummy (id number,name varchar2(50),salary number);
SQL>rename t to t1

Q.85 How can we identify the resources for which the sessions are waiting?

We can find it out using v$session_waits and v$ system _waits.

Q.86 How to create schema?

Schema is nothing but an user.

Q.87 How would you identify the SHARED_POOL_SIZE parameter that needs to be adjusted?

Below is the indications for the same:

Getting an ORA-04031 error.
Degrading the performance even when all the other parameters are already optimized.
Poor library cache/data dictionary hits.

Q.88 What is profile? what is the benefit of profile? Where do you see the information of profiles? Provide an example of profile?

Profile is a set of properties assign to an user
For an example password complexity, password reuse, password expiry, idle time etc.

SQL>desc dba_profiles;
SQL> select username,profile from dba_users;

Q.89 How can we tune a SQL query to optimize the performance of a database?

Enlisted are a few of the best practices for writing SQL queries.

Column names should be provided instead of * in SELECT statements.
Joins should be used in the place of sub-queries.
EXISTS should be used instead of IN to verify the existence of data.
UNION ALL should be used in the place of UNION.
HAVING should be used only for filtering the resulted rows from the SQL query.

Q.90 How to delete the datafile?

alter tablespace t drop datafile ‘/u01/oradata/paddu/tbs1’

Q.91 What are the different tools that are provided by Oracle to assist performance monitoring?

Various tools include:

AWR (Automatic Workload Repository)
ADDM (Automated Database Diagnostics Monitor)
TKPROF
STATSPACK
OEM (Oracle Enterprise Manager)

Q.92 How to move datafiles from one location to another location? Can you provide the steps?

Connect as SYS DBA with CONNECT / AS SYSDBA command.
Make offline the affected tablespace with ALTER TABLESPACE <tablespace name> OFFLINE; command.
Copy the datafiles from old location to new location using OS cp
Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
ALTER database RENAME DATAFILE <old location> TO <new location>’;
Bring the tablespace online again with ALTER TABLESPACE alter tablespace <tablespace name> ONLINE; command

Q.93 What are the different optimizers that are used to optimize the database?

There are two types of optimizers:

Rule-Based Optimizer (RBO): If the referenced objects don’t maintain any internal statistics, RBO is used.
Cost-Based Optimizer (CBO): If the referenced objects maintain internal statistics, CBO will check all the possible execution plans and select the one with the lowest cost.

Q.94 How to change the profile of a user?

alter user username profile profile name

Q.95 How to add datafile to a tablespace?

alter tablespace t add datafile ‘/u01/oradata/paddu/tbs1’ size 100M;

Q.96 How can we monitor the space allocations in a database?

We can use the below data dictionary tables to monitor the space allocations:

DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES

Q.97 How to grant privileges to user?

using grant command

grant create table to user;
grant create table to user with grant option;

Note: with grant option provides user to grant the privilege to other users as well, kind of admin

Q.98 What do you understand by “Performance Tuning of DB” & what are the different areas where we can perform tuning?

It is the process of enhancing database performance by making optimal use of the available resources.

Performance can be enhanced by tuning any of the below areas:

Database design.
Memory allocation.
Disk I/Os.
Database contention.
OS level (CPU).

Q.99 Can you kill the pmon or smon or ckpt ? what happens to database?

These are all the mandatory process to run the database. if we kill any of the process the DB will be crash.

Q.100 How to create user?

create user username identified by password default tablespace testtbs1 profile test;
ex: create user Jimmy identified by Jimmy default tablespace testtbs1 profile test;
SQL> select username, profile from dba_users;
SQL> grant connect, resource to Jimmy;

We hope this article has been valuable in helping you learn about Oracle DBA Interview Questions & Answers.