Oracle DBA Interview Questions For Experienced

Q1. Explain how “shared server” architecture works?

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.

Q2. What are the instance parameters that are used for configuring shared server architecture?

Ans. DISPATCHERS: A string value that is used to configure dispatchers.
SHARED_SERVERS: Minimum number of shared server processes that will be present in the server. Also, this number of shared servers is created during startup.
MAX SHARED_SERVERS: This parameter determines the maximum number of shared server processes that can run at the same time.
SHARED_SERVER SESSIONS: This parameter specifies the maximum number of sessions that can exist at the same time using a shared server connection.
CIRCUITS: This parameter determines the maximum number of virtual circuits that can exist in the system.

Q3. Why is the index used?

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.

Q4. A user is logged on to a Linux server as root where the Oracle database is running. The Oracle is installed at “/uo 1/app/oracle/product/11.2.0.4/dbhome” and the name of the SID is “ORCL”. The user wants to connect to the database locally using operating system authentication with SYSDBA privileges. Show the command that the user has to execute?

Ans. First, he needs to switch to “oracle” user:# su – oracle
Later he needs to set the required environment variables:

$ export ORACLE_SID=ORCL
$ export
ORACLE_HOME=/uo1/app/oracle/product/11.2.0.4/dbhome

Finally, he needs to execute the following command to connect to the database:

$/uol/app/oracle/product/11.2.0.4/dbhome/bin/sqlplus/ as sysdba

Q5. What is the definition of the table in Oracle?

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.

Q6. What do you mean by the view and what are its types?

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.

Q7. In Oracle terminology, what do you mean by tablespace?

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.

Q8. When does the SYSTEM tablespace get created?

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.

Q9. What is the relationship between tablespace and datafiles?

Ans. Each tablespace is divided into one or more data files and one and more tablespace(s) are created for each database.

Oracle DBA Interview Questions For Experienced

Q10. How do we use the materialized view?

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.

Q11. You’re at a client’s office and you are expected to solve a problem in their database. The client is not sure about their database version and you want to find out the version of their existing database. Describe three different methods you can use to find the version of database software?

Ans.

You can find the version by connecting to the database with SQLPlus. SQLPlus will print the name and the version of the database software once you’re connected to the database. A sample output will look like below:
“Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining, and Real Application Testing options”

You can find the version by querying the “vs version” view. You can execute the SQL query below to find the version of the database:

SQL: SELECT * FROM v$version;
A sample output would look like below:
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production
PL/SQL Release 11.2.0.4.0 – Production
CORE 11.2.o.4.o Production
TNS for Linux: Version 11.2.0.4.0 – Production
NLSRTLVersion 11.2.0.4.0 – Production

You can find the version from Enterprise Manager. If you logon to Oracle Enterprise Manager, the version of the database software will be listed on the home page under the “General” web part.

Q12. Different types of synonyms are?

Ans. Synonym types are private and public.

Q13. What do you understand by public synonym?

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.

Q14. What do you understand by a private 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.

Q15. What are the advantages of synonyms?

Ans. A synonym is used to mask the original name and owner of an object and provides public access to an object.

Q16. What is the sequence?

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.

Q17. What do you understand by a private database link?

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.

Q18. What do you understand about the public database link?

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.

Q19. What do you mean by row chaining?

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.

Q20. What is the definition of extent?

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.

Q21. Explain the advantages of using view?

Ans. The view helps provide security, presentation of data from a different perspective, and store complex queries.

Q22. What do you mean by datafile?

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.

Q23. Explain the properties of 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.

Q24. What do you mean by redo log?

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.

Q25. Explain how the “Database Writer” process works?

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.

Hope you liked Laraonlinetraining Oracle DBA Interview Questions For Experienced

Oracle DBA Training Interview Questions For Experienced