CTA
Many organizations are hiring Oracle DBA experts to meet their requirements and the needs and demands of their customers. In case you are looking for Oracle DBA interview questions, this is the correct place for you. These Oracle Apps DBA interview questions and their answers are prepared by industry experts who have more than 8–12 years of experience in Oracle DBA. Let’s start by looking at a few of the most frequently asked Oracle DBA interview questions.
Q1. Mention Oracle DBA Characteristics.
Q2. What is Oracle?
Q3. What is an Oracle Database?
Q4. What is an Oracle index?
Q5. Why is the index used?
Q6. Who is responsible to update the indexes?
Q7. What are the various databases available in the market?
Q8. What are the physical components of Oracle Database?
Q9. What are the roles of DBA?
Q10. What are the different Oracle Database objects?
Listed below are the two parts into which this Oracle DBA Interview Questions blog is divided:
1. Basic Interview Questions
2. Intermediate Interview Questions
Basic Oracle DBA Interview Questions for Freshers
1. Mention Oracle DBA Characteristics.
| Criteria |
Oracle DBA Characteristics |
| Main storage structures |
Logical and physical |
| Architecture |
Grid architecture |
| ACID Compliance |
Fully compliant |
| Data stored as |
Data blocks, extents, segments, and tablespaces |
2. 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 the 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.
3. What is an 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 are stored. The software that manages the database is called the Oracle Database server. Collectively, the software that runs Oracle and the physical database is 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 a control file records the physical structure of the database.
Learn more about Oracle DBA from this Oracle DBA Tutorial!
4. What is an Oracle index?
An index is an optional structure associated with a table to have direct access to rows that can be created to increase the performance of data retrieval. An index can be created on one or more columns of a table.
5. Why is the index used?
Indexes are used for the easy access of data. To increase the performance of retrieval data, Indexes are used. It helps to find the required data quickly without actually searching each row.
Get 100% Hike!
Master Most in Demand Skills Now !
6. Who is responsible to update the indexes?
Oracle is capable of maintaining and using indexes and Oracle automatically distributes the data into relevant indexes when any change is made in the table data.
7. What are the various databases available in the market?
There are many databases available in the market. The commonly used databases are Oracle,
IBM db2, Microsoft SQL Server, Microsoft Access, MySQL and SQLite, PostgreSQL, MariaDB.
Intellipaat gives its learners industrial training in SQL Courses. Enroll now to get a professional certificate!
8. What are the physical components of Oracle Database?
Below 5 are the physical components of Oracle Database:
- Data files
- Control files
- Redo log files
- Password files
- Parameter files
Here is the Database Training Course provided by Intellipaat to get you professionally certified.
9. What are the roles of DBA?
A DBA has the authority to create new users, remove the 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
Want to become a certified Oracle DBA Course? Enroll now!
10. What are the different Oracle Database objects?
- Tables
- Views
- Indexes
- Synonyms
- Sequences
- Tablespaces
11. What is a Synonym in Oracle terminology?
A synonym which is also known as an alias is an identifier that can be used to reference another database object in a SQL statement. A table, view, sequence are the types of database objects that can be created for a synonym.
12. Explain the types of Synonyms?
There are two types of Synonyms which are- Public and Private.
A public synonym isn’t part of any schema. A public synonym is one that can be used by any database user.
A private synonym does belong to a specific schema. In other words, when only the owner can access it, it is called a private synonym.
If you want to become a professional MS SQL Server DBA expert. Learn and master this technology by enrolling in Intellipaat’s MS SQL Server DBA Course in Hyderabad.
13. What are the types of backups in Oracle?
The main four types of backups in Oracle are:
(ⅰ) COLD Backup
(ⅱ) HOT Backup
(ⅲ) Import – Export Backup
(ⅳ) RMAN Backup
14. What are Hot Backup and Cold Backup?
Hot backup is also recognized as an Online Backup because here the backup is taken while the database is active and running. And when the backup can only happen while the database is in shut down mode then it is called Cold Backup which can also be identified as Offline Backup.
15. What is the password file and why it is needed?
Database users’ passwords are stored in the database’s data dictionary. When a user attempts to log into the database, the user’s usernames and passwords are compared to the values contained in the database. The user is given database access only if the username and password match. The data dictionary is stored in the database and can be accessed as long as the database is available. The dictionary also contains the passwords for the administrators.
The data dictionary would be unavailable until the database is locked. Since starting up a down database is one of the administrator’s jobs, there needs to be a way for them to log in even if the database is locked. That’s where the password file comes into the picture. A password file is an operating system file that is held on a separate disc from the database. It stores the username and password for users with the SYSDBA or SYSOPER privileges. And when the database is down, administrators with certain privileges are authenticated using the password files.
16. What are datafiles?
All the tables are stored in data files. The data file has all the data stored. The data files hold all the database data. The data of relational database structures, such as tables and indexes, is bodily stored in the data files owed for a database
17. How do you switch from an init.ora file to a spfile?
To switch from an init.ora file to a spfile, we should create spfile from pfile command then shutdown instance and startup once again
18. What are the different types of SQL statements?
The five type of SQL statements are:
- Data Definition Language
- Data Manipulation Language
- Transactional control
- Session Control
- System Control
19. What is Data Normalization?
The process of arranging data in a database is known as normalization. This involves building tables and defining relationships between them according to rules designed to protect data while also allowing the database to be more flexible by removing redundancy and conflicting dependencies.
20. What is a control file?
A binary file that records the physical structure of the database and is required to start and run the database.
A control file contains information such as
- Data file
- Database name
- Redo file name
- Locations of associated data files and redo files.
- Timestamp of database creation.
- Current log sequence number
- Checkpoint information
21. How will you recover a lost control file?
Steps to recover a lost control file are:
- Start the database in the NOMOUNT mode
- Create the control file from the control file backup with CREATE CONTROLFILE statement, and place it in the correct location.
- Mount the database
- Recover the database
- Open the database
22. What do you mean by Red logo files?
The redo log’s main purpose is to keep track of all data changes. If a failure stops personalised data from being permanently written to data files, corrections may be made from the redo log, ensuring that the work is never wasted.
23. Define Parameter files?
A parameter file is a text file that includes a list of initialization parameters as well as their values. Initialization parameters are defined in a parameter file that is exclusive to your installation.
24. What do you mean by Recovery Catalog?
The recovery catalog is a database scheme that contains the metadata that RMAN manages for data restoration and recovery processes. The metadata used by RMAN for reinstallation and healing process is stored in a recovery catalog. The RMAN metadata is still present in the recovery catalogue even if the target control file and all backups are lost.
25. Define system tablespace and why do we need it?
When the database is created, the system tablespace is formed. Since this tablespace contains all of the data dictionary tables, it is used to create a number of database objects. For the database to run efficiently, the system tablespace must remain online.
26. How to find the database version?
Running a query from the command prompt will reveal to you the Oracle version. The version information is held in the v$version table.
SELECT * FROM v$version;
27. What is the sequence?
For numerical columns in database tables, a sequence produces a serial list of unique numbers. For data where we want to insert data in a systematic way, we may use the sequence on columns.
28. What is the definition of the table in Oracle?
In a database, the table is the first physical object. Oracle divides data into rows and columns using tables in a database. In a database, the table is the first physical object.
29. What do you mean by a view in SQL terminology?
A query is added to any view in order to define specific rows and columns of the table. The view is a type of virtual table. Read-only and read-write views are available types of views.
30. Explain the advantages of using view?
The advantages of using a view in the table are
- It is a subset of the data in table
- It store complex queries
- It can simplify multiple tables into one
- It occupies very little space
- It presents the data from different perspectives
31. In Oracle terminology, what do you mean by tablespace?
A tablespace is a logical storage unit that groups together similar logical structures. It is the logical structure that will organise all of the database’s objects.
32. How long does it take for the SYSTEM tablespace to be created?
Every database in Oracle has a tablespace called SYSTEM, which is generated automatically when the database is created. It also includes a data dictionary table for the entire set of data.
33. What is the relationship between tablespace and datafiles?
Each database has one or more tablespaces, each of which is divided into one or more data files.
34. How do we use the materialized view?
Materialized views are items that contain condensed sets of data from base tables that have been summarised, clustered, or aggregated. They’re commonly used in data centres and decision-making systems.
35. What is the use of the SELECT statement?
The SELECT statement is used to retrieve a collection of unique values from a database table based on the conditions defined in a SQL query.
36. How can you compare a part of the name rather than the entire name?
To compare different parts, we use the LIKE operator, which functions as a database’s regex engine.
37. What is the keyword to get distinct records from a table?
The user will use SELECT DISTINCT to select distinct values from a database table.
38. In order to get sorted records from a table, what is the keyword?
The keyword ORDER BY is used to sort the data.. It returns the sorted results to your program.
39. In order to get total records from a table, what is the keyword?
The COUNT keyword is used to find the total number of records in a table.
40. What is the definition of GROUP BY?
The keyword GROUP BY is an aggregate function like SUM, MULTIPLE, and so on, and without it, the sum for each individual group value cannot be determined.
41. What are the methods to shutdown an Oracle database?
Oracle has several modes for shutting down the database
- Normal Mode:
In normal mode the database is shut down by default. It can be used when no other clause is provided. the database waits for all currently linked users to detach from the database before shutting it down and no new connections are permitted once the statement is released. The command line is
SHUTDOWN NORMAL
- Transactional Mode:
Transactional mode helps to shut down the database while allowing to complete the active transactions. In this process, no new connections are allowed and this mode waits for all transactions to finish before shutting down the database. Use this command line to shut down the database
SHUTDOWN TRANSACTIONAL
- Immediate Mode:
When you know a power outage is coming up fast, then you can use immediate mode. All sessions will be disconnected, all running transactions will be rolled back, the database does not wait for existing database users to disconnect until proceeding and the database will be shut down. No instance recovery is needed during this next startup.
Issue the command SHUTDOWN with an IMMEDIATE clause to shut down a database immediately.
SHUTDOWN IMMEDIATE
- Abort Mode:
When you want to shut down the database immediately within seconds without any active transactions then you can use this abort method. Hereafter the statement has been released, no new connections or transactions are permitted to be initiated. The Oracle database automatically terminates all current client SQL statements and does not wait for existing database users to disconnect. Transactions that have not been committed will not roll back. All linked users are automatically disconnected by the database.
Use the command SHUTDOWN with ABORT clause:
SHUTDOWN ABORT
Intermediate Oracle DBA Interview Questions for Experienced Professionals
42. What are the benefits of ORDBMS?
In ORDBMS, the objects can be stored as they are. 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.
43. What are the common Oracle DBA tasks?
As an Oracle DBA, we have to carry out 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 actions as required
- Monitoring and tuning performance
In a small-to-midsize database environment, a single DBA might be the sole person performing all these tasks. In large enterprise environments, the whole job is often divided among several DBAs titled as Database Security Administrator or Database Tuning Expert, each with his/her own area of specialties.
Get ready for the industry with an Oracle DBA Certification now!
44. List out the tools for administering the database.
Following are some of the products, tools, and utilities we use in achieving our goals as a Database Administrator:
- Oracle Universal Installer (OUI): The Oracle Universal Installer installs the 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 the templates that are supplied by Oracle, or we can create our own templates. In this case, it enables us 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 us through the upgrading of our existing database to a new Oracle release.
- Oracle Net Manager: This tool guides us through our Oracle network configuration.
- Oracle Enterprise Manager: The primary tool for managing our database is Oracle Enterprise Manager, a web-based interface. Once we install the Oracle software, create or upgrade a database, and configure the network, we can use Oracle Enterprise Manager as the single interface for managing our 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.
45. 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 it 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, we need not care where our data resides or which computer processes our request. We should be able to request information or computation and have it delivered as per our requirement. This is analogous to the way the electric utilities work; without knowing where the generator is or how the electric grid is wired, we just ask for electricity and we get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence the name ‘Grid’. This perspective 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, who request resources, are getting what they need and that those resources are not standing idle while requests go unserviced. Information sharing makes sure that the information, the users and applications need, is available as and when it is requested for. High availability features guarantee that all the data and computation are always available as a utility.
46. Explain the architecture of Oracle Grid.
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 the demand for resources and adjusts the supply accordingly.
For example, we can run different applications on a grid of several linked database servers. When reports are due at the end of the month, the Database Administrator can 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.
47. What are the tools you can use to start up an Oracle database?
You can start up a database using three tools:
- SQL *Plus: To startup an Oracle database instance, you can use the SQL *Plus startup command.
- Oracle Enterprise Manager: It is a system management tool, you can startup the Oracle database with Oracle enterprise manager also. It provides an integrated solution for managing your heterogeneous environment. Even if the database is stopped you can still log in to OEM. It will present you with the Startup button by detecting the status of the down database.
- Recovery Manager: RMAN is also known as the RMAN repository that is connected with the TARGET keyword which is also a database on which RMAN performs backup and recovery operations in the control file of the database.
48. What would you specify in the script while creating a database with SQL script?
An SQL script can also be used to build a database. I will include the following in this script:
- The database’s name
- The SYS user’s password.
- The Device user’s password.
- At least three redo log classes are available online. In my view, each redo log group should have at least two members.
- The database’s character set and national character set.
- SYSTEM and SYSAUX tablespace locations and sizes. These tablespaces will be used to store device information.
- As the database’s default tablespace, I will define a normal tablespace.
- I’d specify a temporary tablespace to use as the database’s default temporary tablespace.
- I’d build an undo tablespace.
49. Can you find out the indexes for a table in Oracle ?
This is the basic syntax that is used to find out the indexes for a table in Oracle.
SELECT owner, index_name,
tablespace_name, status
Set pages
Break on table_name on index_name
column table_name
column index_name
column column_name
select table_name, index_name, column_name
from dba_ind_columns
where table_owner
order by table_name, index_name
column_position
50. What is SQL*plus? And what can be done with it?
SQL* Plus is a component of Oracle Database. It is basically a command-line tool that allows you to submit SQL queries to the server interactively.
- We can view the results by Running a SELECT query
- With SQL*Plus we can startup and shutdown a Oracle database.
- Submit PL/SQL blocks for execution to the Oracle server.
- Execution of SQL*Plus script file can be done.
- Create, modify, or drop database objects(eg., tables, indexes, and users) using DDL statements, as well as any other SQL statements that Oracle supports.
- We can write the output to a file.
- Execute stored procedures and functions in a database
51. Can you explain each digit in the Oracle database version?
- This first digit indicates the major database version and it describes the nature of release. Oracle releases a new release every four years on average.
For example: Oracle 9i (internet), Oracle 10g (grid), Oracle 11g (grid), Oracle 12c (cloud).
- The second digit represents the software’s maintenance release number. Oracle releases the major update as maintenance release 1 and then follows up with a second maintenance release later in the software’s lifecycle. With maintenance releases, new features are introduced to database software.
For example: Oracle 12c release 1; Oracle 12c release 2.
- Fusion Middleware Number is the third digit. This will be 0 for database software.
- This fourth digit is called Component-Specific Release Number And a component release level is identified by the fourth digit. Depending on component patch sets or temporary releases, different components can have different numbers in this role.
Example: 12.1.0.1; 12.1.0.2; 12.2.1.1
- A platform- specific release is identified by the fifth digit. This is usually a patch kit. this digit will be the same across all affected platforms when different platforms need the same patch package.
Example: GA release (initial release-no patch): 12.2.0.1.0
52. What kind of information can be given while creating a sequence?
Syntax for creating sequence:
CREATE SEQUENCE schema_name. sequence_name
[INCREMENT BY interval]
[START WITH first_number]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE ]
[CACHE cache_size | NOCACHE]
[ORDER |NOORDER];
While creating a sequence, Use the CREATE SEQUENCE command and add a sequence_name and that should be unique. The next increment by is used to show how much the sequence will increment at each move.
Then using START WITH, we start the sequence either in ascending or descending.
And MAXVALUE, NOMAXVALUE are maximum limits. with max value we can provide the maximum sequence value whereas nomax value is fixed, 10^27 for ascending sequence or -1 for descending sequence.
MINVALUE is to specify the minimum value of sequence. NOMINVALUE is fixed. For an ascending sequence it indicates a minimum value of 1 and -10^26 for a descending sequence.
To allow the sequence to generate value after it reaches the limit, then use CYCLE which is the minimum value for a descending sequence and max value for an ascending sequence. NOCYCLE is a default and it will be used when the sequence reaches the limit and you want to stop generating the next value.
And ORDER ensures that oracle generates sequence numbers in the order in which they are requested. if you don’t want Oracle to generate sequence numbers in the order of your requests, use NOORDER. This is a default setting.
53. Explain how the “Database Writer” process works?
Multiple database context processes are possible. In the operating system, they are referred to as “DBWn.” This method is in charge of storing “dirty” buffers on disc. When a server process has to update a data block, it first reads it from disc into the buffer cache if it isn’t already there, and then updates the cache copy. So, a “dirty” block refers to a modified database block in the buffer cache.
54. What are the instance parameters that are used for configuring shared server architecture?
DISPATCHERS: In the shared server architecture, configures dispatcher processes.
MAX _DISPATCHERS: The maximum number of dispatcher processes that can run at the same time is defined.
SHARED_SERVERS: There will be a minimum of shared server processes on the server. This number of shared servers is determined during the initialization process.
MAX_SHARED_SERVERS: The maximum number of shared server processes that can run at the same time is determined by this parameter.
SHARED_SERVER SESSIONS: This is the only required parameter for using shared servers and it defines the maximum number of concurrent sessions that can be used for shared server connection.
CIRCUITS: The maximum number of virtual circuits that can exist in the system is determined by this parameter.
55. How to know when operations happened on a table?
In the database, FLASHBACK_TRANSACTION_QUERY shows all information about flashback transaction queries.
SELECT *
FROM flashback_transaction_query
WHERE TABLE_NAME
AND table_owner
and operation
order by start_timestamp desc
56. Tell me a few important views used in Oracle you have learned?
These are the few commands that can be used to view in oracle
- V$Parameter
- V$Database
- V$Instance
- V$Datafiles
- V$controlfiles
- V$logfiles