Best Oracle Interview Questions and Answers

1.List components of an Oracle instance?
An Oracle instance is comprised of memory structures and background processes. The Systems Global Area (SGA) and shared pool are memory structures. The process monitor is a background process (DBWn, LGWR, ARCn, and PMON). The Oracle database consists of the physical components such as data files; redo log files, and the control file.

2.Which background process and associated database component guarantees that committed data is saved even when the changes have not been recorded in the data files?
LGWR (log writer) and online redo log files. The log writer process writes data to the buffers when a transaction is committed. LGWR writes to the redo log files in the order of events (sequential order) in case of a failure.

3.What is the maximum number of database writer processes allowed in an Oracle instance?
The maximum is ten. Every Oracle instance begins with only one database writer process, DBW0. Additional writer processes may be started by setting the initialization parameter DB_WRITER_PROCESSES (DBW1 through DBW9).

4.Which background process is not started by default when you start up the Oracle instance?
ARCn. The ARCn process is available only when the archive log is running (LOG_ARCHIVE_START initialization parameter set to true). DBWn, LGWR, CKPT, SMON, and PMON are the default processes associated with all instances (start by default).

5.Describe a parallel server configuration?
In a parallel server configuration multiple instances known as nodes can mount one database. In other words, the parallel server option lets you mount the same database for multiple instances. In a multithreaded configuration, one shared server process takes requests from multiple user processes.

6.Choose the right hierarchy, from largest to smallest, from this list of logical database structures?
Database, tablespace, segment, extent, data blocks.

7.Which component of the SGA contains the parsed SQL code?
The library cache contains the parsed SQL code. During parsing, Oracle allocates a shared SQL area for the statement in the library cache, and stores its parsed representation there. If a query is executed again before its aged out of the library cache, Oracle will use the parsed code and execution plan from the library cache.

8.Name the stages of processing a DML statement. What stages are parts of processing a query?
When processing a query or select statement, the parsing operation occurs first, followed by the fetch operation and the execute operation. However, when processing a DML statement, the parse operation is conducted as well as the execute operation, but not the fetch operation.

9.Which background process is responsible for writing the dirty buffers to the database files?
The purpose if the DBWn is to write the contents of the dirty buffer to the database file. This occurs under two circumstances – when a checkpoint occurs or when the server process searches the buffer cache for a set threshold.

10.Which component in the SGA has the dictionary cache?
The dictionary cache is part of the shared pool. The shared pool also contains the library cache and control structures.

11.When a server process is terminated abnormally, which background process is responsible for releasing the locks held by the user?
The process monitor (PMON) releases the locks on tables and rows held by the user during failed processes and it reclaims all resources held by the user. PMON cleans up after failed user processes.

12.What is a dirty buffer?
A dirty buffer refers to blocks in the database buffer cache that are changed, but are not yet written to the disk.

13.If you are updating one row in a table using the ROWID in the WHERE clause (assume that the row is not already in the buffer cache), what will be the minimum amount of information read to the database buffer cache?
The block is the minimum amount of information read/copied to the database buffer cache.

14.What happens next when a server process is not able to find enough free buffers to copy the blocks from disk?
To reduce I/O contention, the DBWn process does not write the changed buffers immediately to the disk. They are written only when the dirty buffers reach a threshold or when there are not enough free buffers available or when the checkpoint occurs.

15.Which memory structures are shared? Name two.
The library cache contains the shared SQL areas, private SQL areas, PL/SQL procedures, and packages, and control structures. The large pool is an optional area in the SGA.

16.When a SELECT statement is issued, which stage checks the user’s privileges?
Parse checks the user’s privileges, syntax correctness, and the column names against the dictionary. Parse also determines the optional execution plan and finds a shared SQL area for the statement.

17.Which memory structure records all database changes made to the instance?
The redo log files holds information on the changes made to the database data. Changes are made to the database through insert, update, delete, create, alter, or drop commands.

18.What is the minimum number of redo log files required in a database?
The minimum number of redo log files required in a database is two because the LGWR (log writer) process writes to the redo log files in a circular manner.

19.When is the system change numbers assigned?
System changed numbers (SCN) are assigned when a transaction is committed. The SCN is a unique number acting as an internal timestamp, used for recovery and read-consistent queries. In other words, the SCN number is assigned to the rollback statement to mark it as a transaction committed.

10.Name the parts of the database buffer pool?
The database buffer pool consists of the keep buffer pool; recycle buffer pool, and the default buffer pool.
The keep buffer pool retains the data block in memory.
The recycle buffer pool removes the buffers from memory when it’s not needed.
The default buffer pool contains the blocks that are not assigned to the other pools.

11.List all the valid database start-up option?
STARTUP MOUNT, STARTUP NOMOUNT, and STARTUP FORCE.
STARTUP NOMOUNT is used for creating a new database or for creating new control files. STARTUP MOUNT is used for performing specific maintenance operations such as renaming data files, enabling or disabling archive logging, renaming, adding or dropping redo log files, or for performing a full database recovery. Finally, STARTUP FORCE is used to start a database forcefully, (if you have problems starting up an instance.) STARTUP FORCE shuts down the instance if it is already running and then restarts it.
Which two values from the V$SESSION view are used to terminate a user session?
The session identifier (SID) and the serial number (SERIAL #) uniquely identify each session and both are needed to kill a session. Ex. SQL > ALTER SYSTEM KILL SESSION ‘SID’,’ SERIAL #’;

12.To use operating system authentication to connect the database as an administrator, what should the value of the parameter REMOTE_LOGIN_PASSWORDFILE be set to?
The value of the REMOTE_LOGIN_PASSWORDFILE parameter should be set to NONE to use OS authentication. To use password file authentication, the value should be either EXCLUSIVE or SHARED.

13.What information is available in the alert log files?
The alert log store information about block corruption errors, internal errors, and the non-default initialization parameters used at instance start-up. The alert log also records information about database start-up, shutdown, archiving, recovery, tablespace modifications, rollback segment modifications, and the data file modifications.

14.Which parameter value is use to set the directory path where the alert log file is written?
The alert log file is written in the BACKGROUND_DUMP_DEST directory. This directory also records the trace files generated by the background processes. The USER_DUMP_DEST directory has the trace files generated by user sessions. The CORE_DUMP_DEST directory is used primarily on UNIX platforms to save the core dump files. ALERT_DUMP_DEST is not a valid parameter.

15.Which SHUTDOWN option requires instance recovery when the database is started the next time?
SHUTDOWN ABORT requires instance recovery when the database is started the next time. Oracle will also roll back uncommitted transactions during start-up. This option shuts down the instance without dismounting the database.

16.Which SHUTDOWN option will wait for the users to complete their uncommitted transactions?
When SHUTDOWN TRANSACTIONAL is issued, Oracle waits for the users to either commit or roll back their pending transactions. Once all users have either rolled back or committed their transactions, the database is shut down. When using SHUTDOWN IMMEDIATE, the user sessions are disconnected and the changes are rolled back. SHUTDOWN NORMAL waits for the user sessions to disconnect from the database.

17.How do you make a database read-only?
To put a database into read-only mode, you can mount the database and open the database in read-only mode. This can be accomplished in one step by using STARTUP OPEN READ ONLY.

18.Which role is created by default to administer databases?
The DBA role is created when you create the database and is assigned to the SYS and SYSTEM users.

19.Which parameter in the ORAPWD utility is optional?
The parameter ENTRIES is optional. You must specify a password file name and the SYS password. The password file created will be used for authentication.

20.Which privilege do you need to connect to the database, if the database is started up by using STARTUP RESTRICT?
RESTRICTED SESSION privilege is required to access a database that is in restrict mode. You can start up the database in restrict mode by using STARTUP RESTRICT, or change the database to restricted mode by using ALTER SYSTEM ENABLE RESTRICTED SESSION.

21.At which stage of the database start-up is the control file opened?
The control file is opened when the instance mounts the database. The data files and redo log files are opened after the database is opened. When the instance is started, the background processes are started.

22.User SCOTT has opened a SQL * Plus session and left for lunch. When you queried the V$SESSION view, the STATUS was INACTVE. You terminated SCOTT’s session in V$SESSION?
When you terminate a session that is INACTIVE, the STATUS in V$SESSION will show as KILLED. When SCOTT tries to perform any database activity in the SQL *Plus window, he receives an error that his session is terminated. When an ACTIVE session is killed, the changes are rolled back and an error message is written to the user’s screen.

23.Which command will “bounce” the database-that is, shut down the database and start up the database in a single command?
STARTUP FORCE will terminate the current instance and start up the database. It is equivalent to issuing SHUTDOWN ABORT and STARTUP OPEN.

24.When performing the command SHUTDOWN TRANASACTIONAL, Oracle performs the following tasks in what order?
1) Wait for all user transactions to complete;
2) Closes all sessions;
3) Performs a checkpoint;
4) Closes the data files and redo log files;
5) Dismounts the database;
6) Terminates the instance.
SHUTDOWN TRANSACTIONAL waits for all user transactions to complete. Once no transactions are pending, it disconnects all sessions and proceeds with the normal shutting down process. The normal shut down process performs a checkpoint, closes data files and redo log files, dismounts the database, and shuts down the instance.

25.How many panes are there in the Enterprise Manager console?
There are four panes in the Enterprise Manager console: Navigator, Group, Jobs, and Events.
The Navigator pane displays a hierarchical view of all the databases, listeners, nodes, and other services in the network and all their relationships. The Group pane enables you to graphically view and construct logical administrative groups of objects for more efficient management and administration. The Jobs pane is the user interface to the Job Scheduling System, which can be used to automate repetitive tasks at specified times on one or multiple databases. The Events pane is the user interface to the Event Management System, which monitors the network for problem events.

26.Using SQL*Plus, list two options which show the value of the parameter DB_BLOCK_SIZE?
The SHOW PARAMETER command (SHOW PARAMETER
DB_BLOCK_SIZE or SHOW ALL) will show the current value of the parameter. If you provide parameter name, its value is shown; if you omit the parameter name, all the values are shown. SHOW ALL in SQL *Plus will display the SQL *Plus environment settings, not the parameters.

27.When you issue the command ALTER SYSTEM ENABLE RESTRICTED SESSION, what happens to the users who are connected to the database?
If you enable the RESTRICTED SESSION when users are connected, nothing happens to the already connected sessions. Future sessions are started only if the user has the RESTRICTED SESSION privilege.

28.Which view has information about users who are granted SYSDBA or SYSOPER privilege?
A dynamic view of V$PWFILE_USERS has the username and a value of TRUE in column SYSDBA if the SYSDBA privilege is granted, or a value of TRUE in column SYSOPER if the SYSOPER privilege is granted.

29.What is the recommended configuration for control files?
Oracle allows multiplexing of control files. If you have two control files on two disks, one disk failure will not damage both control files.

30.How many control files are required to create a database?
You do not need any control files to create a database; the control files are created when you create a database, based on the filenames specified in the CONTROL_FILES parameter of the parameter file

31.Which DB administration tools are included in the DBA Studio Pack?
The DBA Management Pack is a set of tools integrated with the OEM, which helps administrators with their daily routine tasks. These tools provide complete database administration, via GUI tools (vs. SQL *Plus), and can be accessed by using the OEM, through DBA Studio, or by individually accessing each tool.
The DB Studio Pack includes Instance Manager, Schema Manager, Storage Manager, and Security Manager. Instance Manager allows you to startup or shut down an instance; modify parameters; view and change memory allocations, redo logs, and archival status; age resource allocations and long-running sessions. Schema Manager allows you to create, alter, or drop any schema object, including advanced queries and Java-stored procedures. You can clone any object. Storage Manager allows you to manage tablespaces, data files, rollback segments, redo log groups, and archive logs. Security Manager allows you to change the security privileges for users and roles, and create and alter users, roles, and profiles.

32.Which environment variable or registry entry variable is used to represent the instance name?
The Oracle_SID environment variable is used to represent the instance name. When you connect to the database without specifying a connect string, Oracle connects you to this instance.

33.You have specified the LOGFILE clause in the CREATE DATABASE command as follows. What happens if the size of the log file redo0101.log, which already exists, is 10MB?
LOGFILE GROUP 1
(‘/oradata02/PR0D01/redo0101.log’,
‘/oradata03/PR0D01/redo0102.log’) SIZE 5M REUSE,
GROUP 2
(‘/oradata02/PR0D01/redo0201.log’,
‘/oradata03/PR0D01/redo0202.log’) SIZE 5M REUSE
The CREATE DATABASE command fails. To use the REUSE clause, the file that exists should be the same size as the size specified in the command.

34.Which command should be issued before you can execute the CREATE DATABASE command?
You must start up the instance to create the database. Connect to the database by using the SYSDBA privilege and start the instance by using the command STARTUP NOMOUNT.

35.Which initialization parameter cannot be changed after creating the database?
The block size of the database cannot be changed after database creation. The database name can be changed after re-creating the control file with a new name, and the CONTROL_FILES parameter can be changed if the files are copied to a new location.

36.What does OFA stand for?
OFA- Optimal Flexible Architecture is a set of guidelines to organize the files related to the Oracle database and software for better management and performance.

37.When creating a database, where does Oracle find information about the control files that need to be created?
The control file names and locations are obtained from the initialization parameter file. The parameter name is CONTROL_FILES. If this parameter is not specified, Oracle creates a control file; the location and name depend on the OS platform

38.Which script creates the data dictionary views?
The catalog.sql script creates the data dictionary views. The base tables for these views are created by the script sql.bsq, which is executed when you issue the CREATE DATABASE command.

39.Which prefix for the data dictionary views indicate that the contents of the view belong to the current user?
DAB_prefixed views are accessible to the DBA or anyone with the SELECT_CATALOG_ROLE privilege; these views provide information on all the objects in the database and have an OWNER column. The ALL_views show information about the structures owned by the user.

40.Which data dictionary view shows information about the status of a procedure?
The DBA_OBJECTS dictionary view has information on the objects, their creation, and modification timestamp and status.

41.How do you correct a procedure that has become invalid when one of the tables it is referring to was altered to drop a constraint?
The invalid procedure, trigger, package, or view can be recompiled by using the ALTER