Oracle DBA Interview Questions

Oracle DBA Interview Questions

  • What is Oracle?

Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multi user environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery. A standby database is a database replica created by taking a backup of a primary database.

  • What is a Oracle Database?

Oracle provides software to create and manage the Oracle database. The database consists of physical and logical structures in which system, user, and control information is stored. The software that manages the database is called the Oracle database server. Collectively, the software that runs oracle and the physical database are called the Oracle database system. A database buffer cache stores the data in memory for quicker access. The redo logs track and store all the changes made to the database. A Data Guard ensures data protection and high availability of data and control file records the physical structure of the database. Click on the above link to read more and to become a Oracle DBA.
Oracle DBA Interview Questions

  • What are the roles of DBA?

DBA has the authority to create new users, remove existing users or modify any of the environment variables or privileges assigned to other users.

Manage database storage
Administer users and security
Manage schema objects
Monitor and manage database performance
Perform backup and recovery
Schedule and automate jobs

  • What are different Oracle database objects?


Oracle DBA Interview Questions

  • What are the benefits of ORDBMS?

The objects as such can be stored in the database. The language of the DBMS can be integrated with an object- oriented programming language. The language may even be exactly the same as that used in the application, which does not force the programmer to have two representations of his objects.

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

Oracle DBA Interview Questions

  • What are the Common Oracle DBA Tasks?

As an Oracle DBA, you can expect to be involved in the following tasks:

Installing Oracle software
Creating Oracle databases
Performing upgrades of the database and software to new release levels
Starting up and shutting down the database
Managing the database’s storage structures
Managing users and security
Managing schema objects, such as tables, indexes, and views
Making database backups and performing recovery when necessary
Proactively monitoring the database’s health and taking preventive or corrective action as required
Monitoring and tuning performanceIn a small to midsize database environment, you might be the sole person performing these tasks. In large enterprise environments, the job is often divided among several DBAs, each with their own area of specialty, such as the database security administrator or database tuning expert.

Oracle DBA Interview Questions

  • List out the Tools for Administering the Database?

The following are some of the products, tools, and utilities you can use in achieving your goals as a database administrator.

Oracle Universal Installer (OUI)

The Oracle Universal Installer installs your Oracle software and options. It can automatically launch the Database Configuration Assistant to install a database.
Database Configuration Assistant (DBCA)

The Database Configuration Assistant creates a database from templates that are Oracle supplied, or you can create your own. It enables you to copy a preconfigured seed database, thus saving the time and effort of customizing and generating a database from scratch.

Database Upgrade Assistant

This tool guides you through the upgrade of your existing database to a new Oracle release.

Oracle Net Manager

This tool guides you through your Oracle Net network configuration.

Oracle Enterprise Manager

The primary tool for managing your database is Oracle Enterprise Manager, a web-based interface. After you have installed the Oracle software, created or upgraded a database, and configured the network, you can use Oracle Enterprise Manager as the single interface for managing your database. In addition, Oracle Enterprise Manager also provides an interface for performance advisors and an interface for Oracle utilities such as SQL*Loader and Recovery Manager.

Oracle DBA Interview Questions

  • Differentiate between a cluster and a grid?

Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners. Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in the grid are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.

At the highest level, the idea of grid computing is computing as a utility. In other words, you should not care where your data resides, or what computer processes your request. You should be able to request information or computation and have it delivered – as much as you want, and whenever you want. This is analogous to the way electric utilities work, in that you don’t know where the generator is, or how the electric grid is wired, you just ask for electricity, and you get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence the name ‘The Grid’. This view of utility computing is, of course, a “client side” view.

From the “server side”, or behind the scenes, the grid is about resource allocation, information sharing, and high availability. Resource allocation ensures that all those that need or request resources are getting what they need, that resources are not standing idle while requests are going unserviced. Information sharing makes sure that the information users and applications need is available where and when it is needed. High availability features guarantee all the data and computation is always there, just like a utility company always provides electric power.

Oracle DBA Interview Questions

  • Explain Oracle Grid Architecture?

The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes demand for resources and adjusts supply accordingly.

For example, you could run different applications on a grid of several linked database servers. When reports are due at the end of the month, the database administrator could automatically provision more servers to that application to handle the increased demand.

Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

Oracle DBA Interview Questions

  • List four possible ways (direct or indirect) to execute an SQL query against an Oracle Database?

Using the SQL*Plus command line tool. With this tool, you can directly execute SQL commands.
2. Using a GUI (Graphical User Interface) tool like SQL Developer. You can directly execute SQL commands with such tools.
3. Using Oracle Enterprise Manager. This is an indirect way of executing an SQL query. When you perform certain operations with Oracle Enterprise Manager, they are converted to SQL queries implicitly and these SQL queries are executed against the database.
4. Writing your own program. This is not a conventional way of executing your queries but actually it is widely used. Any web or windows program that uses Oracle database at backend, executes SQL queries. These programs are written using a programming language like .NET or JAVA and they use a driver to connect to database.

  • What is a password file and why is it needed?

Passwords for database users are stored in the data dictionary of the database. When a user wants to log into the database, the username and password provided by the user is checked against the values stored in the database. If the username and password match, the user is granted access to database. The data dictionary is part of the database and it will be accessible as long as the database is open. The passwords for administrators are stored in the dictionary as well.
When the database is closed, the data dictionary will be inaccessible. There needs to be a mechanism for administrators to logon to database even when it is closed, because it is one of the administrator’s tasks to start up a down database. A password file is a separate operating system file that is stored on disk outside of the database. The username and password for the users who have SYSDBA or SYSOPER privileges are stored in it. Administrators who have those privileges are authenticated using this password file even when the database is down.
Oracle DBA Interview Questions

  • What would be the main responsibilities of an Oracle DBA in an organization?

The main duty of an Oracle DBA isto keep the Oracle Databases of the organization up and running. This may involve installing and configuring a database from scratch.
On a running system, the DBA will be the only privileged person who can shut down and start up the database.
The DBA will create new users and manage the privileges of each user.
He will take regular backups to ensure that data is safe. In case of a disaster, he will be responsible of restoring the database from backups. He will have to do monitor the space usage and do capacity planning for the database.
He will be responsible for enforcing security policies. He will have to monitor database activities. He will have to tune the database so that it works at an acceptable speed.
He is expected to follow the latest patches and apply them when applicable.

  • What constitutes an Oracle Database?

An Oracle database is resides on disk and this is permanent. It is composed of files that are stored on disk. These files can be categorized into three types:
a) DataFiles: These files hold “user” data or “system” data. Any data that belongs to an application is an example of “user” data. The “data dictionary” of the database is an example of “system” data.
b) OnlineRedo Log Files: These files hold the “change” records. Any change, which will be made to a data file, is first written to online redo log files.
c) ControlFiles: These files are relatively small but they are essential for a database. They hold information about the physical structure of the database like location of data files, online redo log files etc.

  • What is the definition of table in Oracle?

Table is the first level of physical unit in database. Oracle uses tables of a database to store data into rows and columns. Table is the first level of physical unit in database.

  • What do you mean by view and what are its types?

View is a type of virtual table and there is a query attached to every view in order to identify specific rows and columns of the table. Views are read-only as well as read-write.

  • In Oracle terminology, what do you mean by tablespace?

Tablespace is a Logical Storage Unit used to group related logical structures together. It is the logical structure where all the objects of database will be grouped.
Oracle DBA Interview Questions

  • When does the SYSTEM tablespace get created?

In Oracle every database has a tablespace called SYSTEM and it is automatically created when database is created. It also contains the data dictionary table for the whole data.

  • What is a sequence?

A sequence generates a serial list of unique numbers for numerical columns of a database tables. We can use sequence on columns for data where we want to insert data in sequential manner.


This information box about the author only appears if the author has biographical information. Otherwise there is not author box shown. Follow YOOtheme on Twitter or read the blog.
+91 9952948899