If you're looking for Oracle DBA Interview Questions for Experienced or Freshers, you are at the right place. There are a lot of opportunities from many reputed companies in the world. According to research, Oracle DBA has a market share of about 0.7%.
So, You still have an opportunity to move ahead in your career in Oracle DBA Development. Mindmajix offers Advanced Oracle DBA Interview Questions 2021 that help you in cracking your interview & acquire a dream career as Oracle DBA Developer.
|Feature||Oracle DBA||Oracle Developer|
|Key Roles||Managing Databases||Development(Coding)|
|Type of Tasks||Streamlined||Wide Range|
|Work Environment||Database maintenance||Database development|
|Area of work||Backend database management||Front-end development|
|Few other tasks||Backup, recovery, server connectivity, etc||Coding, designing UI, etc|
Ans. Oracle automatically maintains and uses indexes and when any change is made in the table data Oracle automatically distributes it into relevant indexes.
Ans. “11”: This first digit shows the major database version. Oracle usually publishes a major release once a 4 year. This digit is usually followed by a character describing the nature of the release. For example: 9i (internet), 10g (grid), 11g (grid), 12c (cloud).
“2”: This second digit shows the maintenance release number of the software. Oracle publishes the major release as maintenance release 1 and then usually publishes a second maintenance release during the lifetime of the software. New features are added to database Software with maintenance releases.
“o”: This third digit is Fusion Middleware Number. This will be o for database software.
“4”: This fourth digit is called Component-Specific Release Number and it shows the path set update that was applied to the software. Patch set updates are published 4 times a year by Oracle and as you apply them to your database software, this fourth digit advances.
Ans. A synonym is an identifier that can be used to reference another database object in a SQL statement. The types of database objects for which a synonym may be created are a table, view, sequence, or another synonym.
Ans. 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 are checked against the values stored in the database.
If the username and password match, the user is granted access to the 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 login into the 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 a 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.
Ans. You need to query the “v$pwfile_users” view to get information about the existing users in the password file. Execute the SQL query below:
Sql>SELECT * FROM v$pwfile_users;
The query above will return four columns for each user in the password file. The column names are USERNAME, SYSDBA, SYSOPER, and SYSASM.
Ans. The main duty of an Oracle DBA is to 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 startup 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 for 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.
Ans. An Oracle developer is mainly responsible for developing backend applications. They do data modeling according to business rules. The design tables, create indexes and other types of constraints. They are expected to know SQL and PL/SQL. They develop procedures using this language.
However, the Oracle developers are not expected to administer the database software itself.
On the other side, an Oracle DBA’s main duty is to administer the database which involves tasks like doing maintenance to keep the databases up and running, taking backups, enforcing security policies, etc. DBAs are not primarily assigned to develop code.
DBAs are supposed to have a good knowledge of SQL and PL/SQL like a developer as these are also required for administering the database.
According to the structure of the organization, DBAs might also be assigned development tasks or at least assist the developers where necessary.
Ans. If you are going to do batch installations, it is best to do it with Oracle Universal Installer in silent mode. For single installations.it is best to start the installer in “interactive mode” and set installation options at each window.
However, in batch installations, this will take longer. You need to do the installations in “silent” mode with a “response file”. In a silent installation, you start the Oracle Universal Installer from a command prompt and specify the location of the “response file”.
The installation files and the response file can be shared among the servers via NFS so that you won’t have to copy the setup files to each server.
Ans. A response file is a plain text file, where options to create a database are stored. It is possible to create it manually from scratch but that would take longer and would be erroneous.
Installation media comes with a template response file. It is rather easier to customize it manually. This file also contains notes about the parameters.
However, the easiest and most reliable way to create a response file is by using Oracle Universal Installer. If you start the installer in “record” mode, every option you choose at each step is automatically recorded in a response file in the correct format. After the installer completes in “record” mode, you’ll have a complete response file with all the options set in it.
Ans. It is also possible to create a database via an SQL script. In this script I would specify:
Ans. An instance is made up of a shared memory region on RAM called System Global Area (SGA) and background processes.
The system's global area is a shared memory, which means it can be accessed by multiple processes.
This holds data that is required by the instance to operate.
The background processes are operating system processes and each process has a specific responsibility in the instance.
The System Global Area and background processes are created when the instance is “started”. When the instance is “shut down”, the processes are killed and the shared memory region is “released” back to the operating system.
Ans. An Oracle database resides on disk and this is permanent. It is composed of files that are stored on a disk. These files can be categorized into three types:
Ans. You can start up a database with three tools.
Ans. A parameter file holds instance parameters that govern how an instance operates. In order to start up an instance, Oracle needs to locate this file.
The search order is as below: /DBS/spfile.ora – This is a server parameter file and this is the first place that oracle will look for. SID- is the service identifier of the instance.
<$ORACLE_HOME-/dbs/spfile.ora -If Oracle cannot find the file in the first location, it will search this file. This is again a server parameter file.
/dbs/init.ora – This is a parameter file and it is plain text. If Oracle cannot find the two files listed above, it will search for this file. This is the last location to search.
Ans. You can start up a database with the modes below:
Ans. When a database is open, any user with the “CREATE SESSION” privilege can make a connection. However, it is possible to open the database in “restricted” mode.
When a database is open in restricted mode, only users with the “RESTRICTED SESSION” privilege can make a connection to the database. By default, only DBAs have the “RESTRICTED SESSION” privilege and it should not be granted to regular users.
Opening a database in “restricted” mode is a good way to prevent regular users from accessing the database during maintenance.
Ans. I would put the database in “restricted mode”. While in restricted mode, only users with the “RESTRICTED SESSION” privilege can make a connection. I would run the below command to put the database in restricted mode:
Sql> alter system enable restricted session;
After executing this command regular users won’t be able to loggon into the database. Once I want to revert the database to normal, I execute this command:
Sql>alter system disable restricted session;
Ans. Yes, it is possible to stop all I/O activity while the database is open. Normally, when a database is open, there will be constant I/O to online redo log files or data files. Even if the database is idle, there is no guarantee that the database will not write anything to files during the snapshot.
However, if you “suspend” the database, Oracle will halt I/O operations to these datafiles until it is reverted back to normal mode. So, you should “suspend” the database, take the snapshot of the disk and then put the database back in normal mode immediately after that.
Ans. In the default configuration, the Oracle database will not automatically start after the server reboots. You’ll have to start it manually after each reboot. You’ll usually want it to start automatically. There are two methods to accomplish this:
Ans. When a user connects to a database, he sends SQL queries to the database to execute. These SQL queries are executed by a “server process” and the result is returned back to the user. In the “dedicated server” architecture, the instance will create one server process for each connected user.
That process will be “dedicated” to that user and will only serve that client.
However, in “shared server” architecture, a single server process will serve multiple clients. In shared server architecture, the total memory consumption will be less.
However, certain operations like DBA activities can only be performed on a dedicated server.
Ans. In shared server architecture, the clients connect to a “dispatcher” process. This dispatcher is responsible for delivering the SQL requests to the “request queue”.
The shared server process monitors the request queue. When they find an incoming request, they execute this SQL query and place the results in the response queue. The request queue and the response queue reside in the system global area.
The dispatcher processes also monitor the response queue. When it receives a result, they deliver the result to the relevant client.
In this architecture, there will be multiple shared server processes and dispatcher processes.
Ans. There can be multiple database background processes. They are named “DBWn” in the operating system. This process is responsible for writing “dirty” buffers to the disk. When a server process wants to update a data block, it reads the block from disk to buffer cache if the block is not already in the cache and then updates the copy in the cache. The modified database block in the buffer cache is called a “dirty” block.
Ans. The index is used to increase the performance of retrieval. We can make use of one or more rows in order to make the index. The index can increase the performance of retrieval and slows down the performance of insertion.
$ export ORACLE_SID=ORCL $ export ORACLE_HOME=/uo1/app/oracle/product/188.8.131.52/dbhome
$/uol/app/oracle/product/184.108.40.206/dbhome/bin/sqlplus/ as sysdba
Ans. The table is the first level of the physical unit in the database. Oracle uses tables of a database to store data into rows and columns. The table is the first level of the physical unit in the database.
Ans. The 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.
[Related Article: Revoking User Privileges and Roles - Oracle DBA]
Ans. The tablespace is a Logical Storage Unit used to group related logical structures together. It is the logical structure where all the objects of the database will be grouped.
Ans. In Oracle, every database has a tablespace called SYSTEM and it is automatically created when the database is created. It also contains the data dictionary table for the whole data.
Ans. Each tablespace is divided into one or more data files and one and more tablespace(s) are created for each database.
Ans. Materialized views are objects that have reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouses or decision support systems.
[Related Article: Data Warehouse Interview Questions]
Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 – Production
With the Partitioning, OLAP, Data Mining, and Real Application Testing options”
SQL: SELECT * FROM v$version; A sample output would look like below: BANNER Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 – Production PL/SQL Release 22.214.171.124.0 – Production CORE 11.2.o.4.o Production TNS for Linux: Version 126.96.36.199.0 – Production NLSRTLVersion 188.8.131.52.0 – Production
Ans. Synonym types are private and public.
Ans. A public synonym does not belong to any schema. In other words, when any database user can access it, it is called a public synonym.
Ans. A private synonym is one that does belong to a specific schema. In other words, when only the owner can access it, it is called a private synonym.
Ans. A synonym is used to mask the original name and owner of an object and provides public access to an object.
Ans. A sequence generates a serial list of unique numbers for numerical columns of database tables. We can use the sequence on columns for data where we want to insert data in a sequential manner.
Ans. A private database link is created for a specific user. It is only used when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner’s views or procedures.
Ans. A database link is a schema object in one database to access objects in another database. When you create a database link with a Public clause it is available for access to all the users.
Ans. Row Chaining occurs when the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG, LONG RAW, LOB, etc. Row chaining in these cases is unavoidable.
Ans. An extent is a set of contiguous blocks allocated in a database. In the Oracle database program, the first set of contiguous blocks, set up automatically when a segment is created, is called the initial extent.
After the initial extent has been filled, the program allocates more extents automatically. These are known as the next extents.
The total number of extents that can be allocated in a database is limited by the amount of storage space available, or in some cases, by the program used.
Ans. The view helps provide security, presentation of data from a different perspective, and store complex queries.
Ans. An Oracle datafile is a big unit of physical storage in the OS file system. One of many Oracle data files is organized together to provide physical storage to a single Oracle tablespace.
The data file is used to store tables and indexes allocated to the database. Every database consists of one or more data files.
Ans. Each data file can only be associated with only one database and once it is created it can not change its size.
Ans. The most crucial structure for recovery operations is the redo log, which consists of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.
Ans. Redo log’s main function is to store all changes made to the database as they occur.
Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes:
Ans. The control file must be available for writing by the Oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult. You might also need to create control files if you want to change particular settings in the control files.
Ans. SQL is a database computer language designed for managing data in relational database management systems (RDBMS) and originally based upon relational algebra. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
Ans. The SELECT statement is used to select the set of specific values from a table in a database depending on the various conditions specified in a SQL query.
Ans. In order to compare parts, we use the LIKE operator which acts like a regex engine for a database.
Ans. SELECT DISTINCT allows the user to select the distinct values from a table in a database.
Ans. ORDER BY keyword is used for sorting the results. It returns the sorted results to your program.
Ans. To find the total number of records in a table, the COUNT keyword is used.
Ans. GROUP BY keyword is an aggregate function such as SUM, MULTIPLE, etc and without this function sum for each individual group value can not be calculated.
There are five types of SQL statements
Ans. Oracle supports transactions as defined by the SQL standard. A transaction is a sequence of SQL statements that Oracle treats as a single unit of work. As soon as you connect to the database, a transaction begins. Once the transaction begins, every SQL DML (Data Manipulation Language) statement you issue subsequently becomes a part of this transaction. A transaction ends when you disconnect from the database, or when you issue a COMMIT or ROLLBACK command.
Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.