Oracle Interview Questions – Part 43

Q.Explain different types of segment. Data segment, Index segment, Rollback segment and temporary segment.
A.There are four types of segments used in Oracle databases:
-data segments
-index segments
-rollback segments
-temporary segments
Data Segments:
There is a single data segment to hold all the data of every non clustered table in an oracle database. This data segment is created when you create an object with the CREATE TABLE/SNAPSHOT/SNAPSHOT LOG command. Also, a data segment is created for a cluster when a CREATE CLUSTER command is issued.
The storage parameters control the way that its data segment’s extents are allocated. These affect the efficiency of data retrieval and storage for the data segment associated with the object.
Index Segments:
Every index in an Oracle database has a single index segment to hold all of its data. Oracle creates the index segment for the index when you issue the CREATE INDEX command. Setting the storage parameters directly affects the efficiency of data retrieval and storage.
Rollback Segments
Rollbacks are required when the transactions that affect the database need to be undone. Rollbacks are also needed during the time of system failures. The way the roll-backed data is saved in rollback segment, the data can also be redone which is held in redo segment.
A rollback segment is a portion of the database that records the actions of transactions if the transaction should be rolled back. Each database contains one or more rollback segments. Rollback segments are used to provide read consistency, to rollback transactions, and to recover the database.
Types of rollbacks:
– statement level rollback
– rollback to a savepoint
– rollback of a transaction due to user request
– rollback of a transaction due to abnormal process termination
– rollback of all outstanding transactions when an instance terminates abnormally
– rollback of incomplete transactions during recovery.
Temporary Segments:
The SELECT statements need a temporary storage. When queries are fired, oracle needs area to do sorting and other operation due to which temporary storages are useful.
The commands that may use temporary storage when used with SELECT are:

Q.Explain SGA memory structures: Shared Pool, Database buffer Cache, Redo log Cache, Large Pool Java Pool.
A.SGA (System Global Area) is a dynamic memory area of an Oracle Server. In SGA,the allocation is done in granuels. The size of the SGA is dependent on SGA_MAX_SIZE parameter.
The memory structures contained by SGA are:-
Shared Pool –
this memory structure is divided into two sub-structures which are Library Cache and Data Dictionary Cache for storing recently used PL/SQL statements and the recent data definitions. The maximum size of the Shared Pool depends on the SHARED_POOL_SIZE parameter.
Database Buffer Cache –
This memory structure improves the performance while fetching or updating the recently used data as it stores the recently used datafiles. The size of this block is decided by DB_BLOCK_SIZE.
Redo Log Buffer –
This memory structure is used to store all the changes made to the database and it’s primarily used for the data recovery purposes. The size of this block is decided by LOG_BUFFER.
Java Pool –
This memory structure is used when Java is installed on the Oracle server. Size that can be used is stored in parameter named JAVA_POOL_SIZE.
Large Pool –
This memory structure is used to reduce the burden of the Shared Pool, as the Session memory for the Shared Server, as the temporary storage for the I/O and for the backup and restore operations or RMAN. Parameter that stores the maximum size is LARGE_POOL_SIZE.

Q.What is synonym and how do we create synonym?
A.Table references can be complicated. So oracle allows you create synonym for a complicated reference. It renames a table reference.
CREATE SYNONYM syn_name FOR reference;
This object can be a table, procedure, sequence or any other database object. A synonym is a Data Dictionary object.

Q.What are the guidelines to decide which table s to index?
A.On rebuilding an Index, the existing index is dropped and a new one is built. This operation can consume a lot of time and resources.
When an index is reorganized, the leaf level pages of the index are defragmented to match the logical order by physically reordering them.
As rebuilding indexes is an expensive operation, following general guidelines should be taken into consideration:
Reorganise an index when the degree of fragmentation is between 5 and 30%
Rebuild an index when the degree of fragmentation is over 30%

•A snapshot is a full set or a subset of rows of a table or view at a point in time.
•A snapshot is created by executing a SQL query against a base table.
•Snapshots are either read-only or updateable.
•They vary in complexity.

Q.Explain the steps executed during SQL statement processing.
•The parser scans the statement and breaks it into logical units such as keywords, identifiers and operators.
•A query or a sequence tree is built using the units above. This is done to transform the source data into the format required by the result set.
•The Query optimizer analyzes the fastest way to access the source tables using minimum resources. The final optimized version of the updated query tree is called as execution plan.
•Now, the relational engine starts to execute this plan. the relational engine requests that the storage engine pass up data from the row sets requested from the relational engine.
•In turn, the relational engine processes this data into the format as desired by result set and returns the same.

Q.Explain the difference between trigger and stored procedure.
A.Procedure runs only when one call them manually whereas a trigger runs when there is any activity (insert,update,delete) on table on which the trigger is written.

Q.What is a database buffer cache?
A.Database buffer cache is a component of SGA. It has the responsibility to cache most recent accessed data. It keeps the transaction in the buffer cache till it is written on the disk.

Q.Define Statement Auditing, Privilege Auditing and Object Auditing in oracle?
A.Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects.
Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.
Object auditing is the auditing of accesses to specific schema objects without regard to user.

Q.What is snapshot log?
A.Snapshot log table maintains a record of modifications to the master table in a snapshot.

Q.What is a cursor variable?
A.cursor variable is capable to get associated with different SELECT statements at run time. It is a reference type which is quite similar to pointer in C. In order to use cursor variable, it has to be declared first, and then the storage has to be allocated.

Q.What is WebDB?
A.WebDB is a tool written in PL/SQL, used to develop HTML based application that can easily interact with Oracle data. It uses HTML components and follows HTML standards which makes it compatible to many browsers in the market.

Q.Explain the difference between a hot backup and a cold backup in oracle.
A.Cold Backup
In this type of backup, after the database is shut down, DBA exits the SVRMGR utility and copies the log files, data files and control files onto a backup media. Once the backup has been completed the DBA can restart the database.
Hot backup
A few systems that need to support continuous operation, it is difficult to bring down the database without interrupting the service provided to the users. In such cases, hot backup approach should be used.

Q.Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode in oracle.
A.There are two modes in which the hot backup works:
The database operations are suspended until archiving has been completed if the on-line redo log files are filled before they can be archived.
It is important that the directory containing the archived log files doesn’t become full because if it does the ARCH thread wouldn’t be able to archive the redo log files. The DBA has to continuously monitor the used-space percentage in the archive directory.
The database can be placed into ARCHIVELOG mode at the creation time or after the database has been created.
However, the database is shutdown before connecting as the user INTERNAL.
It was originally created in NOARCHIVELOG mode; this technique can be used to place the database in ARCHIVELOG mode.

Q.Describe in brief about snapshot in oracle.
A.A recent copy of a table or a subset of rows or cols of a table is called as snapshot in oracle. A snapshot is more useful in distributed computing environment. We can create snapshot in oracle using command create snapshot. We can specify refresh interval while creating snapshot.

Q.What is a synonym?
A.Synonym simplifies the use of the table, the table for which synonym is created can be referred by synonym name.
create synonym Emp
for Employees;
In the above example Emp is the synonym created for the employees table. Employees now can also be referred by Emp.

Q.What is Data Block?
A.Data blocks are also called logical blocks, Oracle blocks, or pages.At the finest level of granularity, Oracle stores data in data blocks.A data block corresponds to a specific number of bytes of physical database space on disk.

Q.What is Rollback Segment in oracle?
A.Rollback Segment in oracle is used to store “undo” information temporarily.

Q.How many types of Tables supported by Oracle? Explain them
A.Oracle supports 4 types of tables based on how data is organized in storage:
1.Ordinary (heap-organized) table
A basic, general purpose table
Data is stored as an unordered collection (heap)
2.Clustered table
A part of a cluster.A cluster is a group of tables that share the same data blocks as they share common columns and are often used together.
3.Index-organized table
Data is stored in a B-tree index structure in a primary key sorted manner.Each index entry in the B-tree stores the non-key column values as well.
4.Partitioned table
Data is broken down into smaller, more manageable pieces called partitions or sub-partitions.Each partition can be managed individually.Each partition can operate independently.
A.The structure obtained from partitioning is better tuned for availability and performance

Q.What is the purpose of Indexes?
A.Oracle uses indexes to avoid the need for large-table, full-table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query.

Q.What are SQL*Plus Environment variables?
A.The behaviour of SQL PLUS depends on some environmental variables predefined in the OS:
1.ORACLE_HOME: This variable stores the home directory where the Oracle client application is installed
2.PATH: It contains a list of directories where SQL*Plus can look for executables or DLLs. PATH should include $ORACLE_HOMEbin
3.SQLPLUS: The directory where localization messages are stored. SQLPLUS should be set to $ORACLE_HOMEsqlplusmesg
4.TNS_ADMIN: The directory where the connect identifier file, tnsnames.ora is located. TNS_ADMIN should be set to $ORACLE_HOME/network/admin.

Q.What is Output Spooling in SQL*Plus?
A.The spooling feature facilitates copying of all the contents of the command line SQL*Plus to a specified file. This feature is called Spooling.
SPOOL filename: Turns on output spooling with the specified file.
SPOOL OFF: Turns off output spooling and close the spool file

Q.Explain the different types of queries in Oracle.
A.Session Queries are implicitly constructed and executed by a Session based on input parameters.Input parameters are used to perform the most common data source actions on objects.Database Queries are created and then executed to perform any data source action on either objects or data. They can be further refined by also creating and configuring its Call.Named Queries can be executed repeatedly by name. They are an instance of DatabaseQuery stored by name in a Session or a descriptor’s DescriptorQueryManager where it is constructed and prepared once.Call Queries are instances of Call that are created and then either executed directly or indirectly in the context of a DatabaseQuery.Redirect Queries are instances of MethodBasedQueryRedirector set on a named query. When the query is executed, the static method is invoked.Historical Queries are queries executed in the context of a historical session using the time-aware features of the TopLink Expression framework.Interface and Inheritance Queries are queries that references an interface type or super and subclasses of an inheritance hierarchy.

Q.What is SQL*Plus?
A.SQL*Plus is an interactive and batch query tool.It gets installed with every Oracle Database Server or Client installation.It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.SQL*Plus has its own commands and environment, and it provides access to the Oracle Database.
Following types of commands can be entered and executed:
Operating system commands
With the above commands, following actions can be performed:
Develop and run batch scripts
Perform database administration
Format, perform calculations on, store, and print from query results
Examine table and object definitions
SQL*Plus can be used to:
Generate reports interactively
Generate reports as batch processes
Output the results to text file, to screen, or to HTML file for browsing on the Internet.
Generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page.

Q.What is snapshot log?
A.Snapshot log table maintains a record of modifications to the master table in a snapshot.

Q.What is a synonym?
A.Synonym simplifies the use of the table, the table for which synonym is created can be referred by synonym name.
create synonym Emp
for Employees;
In the above example Emp is the synonym created for the employees table. Employees now can also be referred by Emp.

Q.What is an Archiver?
A.Archiving is the process of removing of old data and unused data from the main databases. This process keeps databases smaller, more manageable and thus acquires performance gain. To archive data, you can either delete data from the database or copy the data to a history table and delete that data from the database.

Q.What is a sequence in oracle?
A.A Sequence is a user created database object. A sequence can be shared by multiple users to generate unique integers. This object is used to create a primary key value. The sequence is generated and incremented by internal Oracle routine. This can be time-saving object because it can reduce the amount of application code needed to write a sequence generating routine.
Create sequence seq_name
Increment by n
Start with n
Maxvalue n | NoMaxvalue
Minvalue n | NoMinvalue
Cycle | NoCycle
Cache n | NoCache ;
Oracle sequence generates a series of unique numbers for numeric column of a database’s tables.

Q.What is a subquery in Oracle?
A.When a query needs to be run which has a condition that needs to be a result of another query then, the query in the condition part of the main one is called a sub-query.

Q.What are the uses of Rollback Segment?
A.Rollback segments undo changes when a transaction is rolled back, they also ensure that transactions leave the uncommitted changes unnoticed.

Q.What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
A.When a SQL statement raises an exception, Oracle captures the error codes by using the SQLCODE and SQLERRM globally-defined variables.SQLCODE and SQLERRM can track exceptions that are handled by the OTHERS clause of the exception handler.SQLCODE returns the current error code from the error stack and the error message from the current error.

Q.Explain the concepts of Exception in Oracle. Explain its type.
A.Exceptions in oracle occur when unwanted situations happen during the execution of a program. They can occur due to system error, user error or application error. When any of these situations arise, the control of the program is shifted to Exception section for it to handle.
Exception can be
1.Predefined oracle exceptions
2.User-defined exceptions
To have predefined oracle exception in the code, you have to start a block of code with the keyword EXCEPTION, followed by when clause.
Examples of Predefined Exceptions and their description:
1.No_data_found- It occurs when the single row select statement returns no data.
2.Too_many_rows- This arises when the single row select statement returns more than one row.
3.Zero_divide- It occurs when we try to divide the number by zero.
4.Login_denied- It arises when a login to Oracle failed because of an invalid username or password.

Q.Explain oracle memory structures.
A.Two memory area.
1.System global area(SGA)
2.Program Global Area(PGA)
SGA consist memory structure such as
1.Shared Pool
2.Database buffer cache
3.Redo log buffer
4.large Pool
5.Java Pool
We have two memory areas that comprise the memory structure of oracle.
System Global Area(SGA) : It is also called as Shared Global Area. It stores data and control information for the oracle server and can be shared by database processes.
Program Global Area(PGA) : It is also called as Process Global Area. It contains data and control information for a single server process.
Following are the structure contained in the SGA:
1.Shared pool: It stores most recently executed statements.
2.Database buffer cache: It stores copies of data block.
3.Redo log buffer: It records all changes made to the database blocks.
4.Large pool: It is optional area of the memory that can be used by the shared pool in order to relieve load on it.
5.Java pool: This area is used for parsing java command.

Q.What is Program Global Area (PGA)?
A.The PGA is a memory area that contains data and control information for the Oracle server processes. This area consists of the following components:
1.Stack space-This holds the session’s variables and arrays.
2.Session information-If you are not running the multithreaded server, the session information is stored in the PGA. If you are running the multithreaded server, the session
3.Private SQL area-This area keeps information about binding variables and runtime buffers.

Q.What is the BFILE datatypes?
A.The BFILE datatype is used to store unstructured binary data outside the database. The column of BFILE type stores file locator that points the OS file which actually stores data.

Q.Define Bfile datatypes.
A.Oracle provides Bfile datatypes to store unstructured binary data. The actual data is stored outside the database in the operating system files. A bfile column stores file locator that points to an external file containing data. Bfiles are read-only, one can’t modify them.

Q.What are the drawbacks of a cursor?
A.Implicit cursors are less efficient than explicit cursors.They are more vulnerable to data errors.They provide less programmatic control .
Q.What is a cursor variable?
A.A cursor variable is capable to get associated with different SELECT statements at run time. It is a reference type which is quite similar to pointer in C. In order to use cursor variable, it has to be declared first, and then the storage has to be allocated.A cursor variable is a variable of REF CURSOR data type which is a pointer to a data structure resource.It connects to query statement result, similar to the CURSOR data type.To define cursor variable, you must decide which REF CURSOR data type to use.
The REF CURSOR data type can be selected in 3 different ways:
1.By defining a specific REF CURSOR types using the TYPE … RETURN statement.
2.By defining a generic REF CURSOR type using the TYPE … statement.
3.By using the system defined SYS_REFCURSOR.

Q.Explain the difference between trigger and stored procedure.
A.Procedure runs only when one call them manually whereas a trigger runs when there is any activity (insert,update,delete) on table on which the trigger is written.

Q.Explain Row level and statement level trigger.
A.1.Row Level Trigger
Row Level Trigger is fired each time row is affected by Insert, Update or Delete command. If statement doesn’t affect any row, no trigger action happens.
2.Statement Level Trigger
This kind of trigger fires when a SQL statement affects the rows of the table. The trigger activates and performs its activity irrespective of number of rows affected due to SQL statement.

Q.Explain the difference between trigger and stored procedure.
A.Procedure runs only when one call them manually whereas a trigger runs when there is any activity (insert,update,delete) on table on which the trigger is written.

Q.What are cascading triggers?
A.When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.

Q.Differences between CHAR and VARCHAR2 in Oracle.
A.CHAR values have a fixed length. They are padded with space characters to match the specified length.VARCHAR2 values have a variable length. They are not padded with any characters.

Q.Differences between DATE and TIMESTAMP in Oracle
A.TIMESTAMP and DATE vary in formats as follows:
DATE stores values as century, year, month, date, hour, minute, and second.TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.

Q.What is a shared pool?
A.It is the area in SGA that allows sharing of parsed SQL statements among concurrent users.

Q.What is a Schema?
A.The set of objects owned by user account is called the schema.

Q.What is the advantage of a stored procedure over a database trigger?
A.Stored procedure is a set of pre-compiled SQL statements, executed when it is called in the program.
Triggers are similar to stored procedure except it is executed automatically when any operations are occurred on the table.

Q.Explain referential integrity?
A.Referential integrity is the rules that governs the relationships between primary keys and foreign keys of the tables and ensure data consistency. It ensures the value of foreign key be matched by the value of a primary key in another table.

Q.Explain the characteristics of Data Files in oracle.
A.One or more data files form a logical unit of database storage called a tablespace.The size of the data file can’t be changed once it is created.

Q.What are constraints?
A.It is the rules that prevent the invalid entry into the table. They are stored in the data dictionary. They can be defined either at column level or table level.
Following are the constraints available in oracle.
1.Not Null – Specifies that column can’t contain a null value.
2.Unique – Enforce unique value for all rows in the table.
3.Primary key – Uniquely identifies each row of the table.
4.Foreign key – Enforces a foreign key relationship between the columns of the referenced table.
5.Check – specifies condition that must be true.