Oracle Interview Questions – Part 41
Q.What Is a Dynamic Performance View in Oracle?
A.Oracle contains a set of underlying views that are maintained by the database server and accessible
to the database administrator user SYS. These views are called dynamic performance views because
they are continuously updated while a database is open and in use, and their contents relate primarily
to performance. Although these views appear to be regular database tables, they are not. These views
provide data on internal disk structures and memory structures. You can select from these views, but
you can never update or alter them.
Q.1)What ll be in the redolog file after deleting the table ,will it stores the table or any thing else? 2)Can we resize the redolog files?
A.1)A message will be recorded for deleted table.
2)There is no single command to resize an existing set of redo logs.
If you want to resize the redo log groups, then first you need to create some more of redo log file
group with the desired name and size.After that you can drop older redolog group.
Q.What is an index and How it is implemented in Oracle database ?
A.An index is a database structure used by the server to have direct access of a row in a table.
An index is automatically created when a unique of primary key constraint clause is specified in create
table comman (Ver 7.0)
Q.What are the types of database links ?
A.Oracle allows you to create private, public, and global database links.
Private Database Link: You can create a private database link in a specific schema of a database. Only
the owner of a private database link or PL/SQL subprograms in the schema can use a private database
link to access data and database objects in the corresponding remote database.
Public Database Link : You can create a public database link for a database. All users and PL/SQL
subprograms in the database can use a public database link to access data and database objects in the
corresponding remote database.
Global Database Link – When an Oracle network uses Oracle Names, the names servers in the system
automatically create and manage global database links for every Oracle database in the network. All
users and PL/SQL subprograms in any database can use a global database link to access data and
database objects in the corresponding remote database.
A private database link is more secure than a public or global link, because only the owner of the
private link, or subprograms within the same schema, can use the private link to access the specified
When many users require an access path to a remote Oracle database, an administrator can create a
single public database link for all users in a database.
When an Oracle network uses Oracle Names, an administrator can conveniently manage global
database links for all databases in the system. Database link management is centralized and simple.
Q.What is a Database instance ? Explain
A.A database instance (Server) is a set of memory structure and background processes that access a
set of database files.
The process can be shared by all users. The memory structure that are used to store most queried
data from database. This helps up to improve database performance by decreasing the amount of I/O
performed against data file.
1.Instance is the combination background process and memory structures.
2.Instance must be started to access the data in the database.
3.When instance is started, SGA is allocated and Background processes are started at nomount stage.
4.Instance can open and use only one database at a time.
Q.What is index cluster ?
A.An index cluster uses an index to maintain data within the cluster. The structure of a cluster index is
similar to a normal index.
Although a normal index does not store NULL key values, and index cluster can store it.
And, a cluster index is likely to be smaller than a normal index because, there is only one entry for
each key value in the cluster index, even if that is repeating.
Q.What Is an Oracle Tablespace?
A.An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used
by the Oracle server to store structures data objects, like tables and indexes.
Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical
structures that conform to the operating system in which Oracle is running.
Q.Explain Different types of table join?
A.different types of joins are
-Cartesian Product or Cross Join
-Equi Join or Natural Join
-Non Equi Join
-Left Outer Join
-Right Outer Join
-Full Outer Join
Q.Explain When can hash cluster used?
A.Hash clusters are useful in cases where :
(i) There is a uniform, even and predictable no. of key values.
(ii) Queries using equality predicates.
(iii) The table is NOT growing constantly, and the keys are rarely updated.
Q.Explain What is an Oracle sequence ?
A.A sequence is a database object created by a user that can be used to generate unique integers. A
typical usage of sequences is to generate primary key values which are unique for each row.
It is generated and incremented (or decremented) by an internal Oracle routine. It can be used by
multiple users and for multiple tables too. A sequence can be used instead of writing an application
code for sequence-generating routine.
Q.What are Schema Objects?
A.Schema objects are the logical database structure that represents database’s data. Schema objects
include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures and
Q.What are layers in Oracle Architecture?
A.The Oracle database has a logical layer and a physical layer. The physical layer consists of the files
that reside on the disk and logical layer map the data to these files of physical layer.
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 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.
Not Null – Specifies that column can’t contain a null value.
Unique – Enforce unique value for all rows in the table.
Primary key – Uniquely identifies each row of the table.
Foreign key – Enforces a foreign key relationship between the columns of the referenced table.
Check – specifies condition that must be true.
Q.Explain database objects in oracle.
Composed of rows and column that stores data.
Represents subset of data from one or more tables.
Auto generates primary key value.
Improve performance of queries.
Gives alternative names to object.
Q.What is the physical and logical structure of oracle?
A.Logical Database structures
Logical structures include tablespaces, schema objects, data blocks, extents and segments.
Database is logically divided into one or more tablespaces. Each tablespace creates one or more
datafiles to physically store data.
Schema objects are the structure that represents database’s data. Schema objects include structures
such as tables, views, sequences, stored procedures, indexes, synonyms, clusters and database links.
Data block represents specific number of bytes of physical database space on disk.
An extent represents continuous data blocks that are used to store specific data information.
A segment is a set of extents allocated for a certain logical structure.
Physical database structure
The physical database structure comprises of datafiles, redo log files and control files
Datafiles contain database’s data. The data of logical data structures such as tables and indexes is
stored in datafiles of the database. One or more datafiles form a logical unit of database storage called
Redo log files
The purpose of these files is to record all changes made to data. These files protect database against
Control files contain entries such as database name, name and location of datafiles and redo log files
and time stamp of database creation.
Q.Explain the constraints that can be applied to Oracle tables.
A.The syntax of writing a table is
create table tablename ( columnname type, columnname type …, primary key(keycolumn);
The keycolumn is associates with the key constraint.
You can even add the foreign key constraint by adding the references to the table for that foreign key
in the following way:
foreign key(column) references foreigntable
Q.Explain user defined exceptions in oracle
A.A user can explicitly raise an exception by using the Raise command.
Q.Explain IN, OUT and INOUT in procedures.
A.IN, OUT and INOUT are the arguments that are passed to the procedures.
•IN is a ‘read only’ argument and must be initialised.
•OUT is an uninitialised argument which must be initialized by a function.
•INOUT – A combination of the two above. That is, an initialised argument which can be written to.
Q.How the triggers are attached to the table?
A.When we write a trigger, we also have to give the reference of the table the trigger has to be fired
on. The Data Dictionary too is used for this purpose. The view includes the trigger body, WHEn
clause, triggering table, and trigger type.
Q.What are triggering attributes?
A.Triggers can be fired based on the following criteria:
Category – (INSERT, DELETE, UPDATE) i.e. which kind of DML statement causes the trigger to fire.
Timing – (BEFORE or AFTER) i.e. whether the trigger fires before the statement is executed of after.
Level – (Row or Statement) i.e. whether it fires once for each row affected by trigger statement or
whether it fires once.
Q.What is an SQL *FORMS?
A.Oracle Forms is part of Oracle’s Internet Developer Suite. Its earlier versions were called
Oracle Forms is a 4GL Rapid Application Development (RAD) environment.
A Forms Builder is used to create applications to enter, access, change, or delete data from Oracle
The Forms Runtime environment is needed to execute compiled Forms modules.
Q.How do you control the constraints in forms?
A.This can be done by selecting the Use Constrain Property to ON.
You can suppress or customize error messages in Forms. This can be done by setting the message
level of the system variable SYSTEM.MESSAGE_LEVEL. The other way to do this is by using the
ON-ERROR / ON-MESSAGE triggers.
Q.What is Partial Backup?
A.A partial backup is similar to a full database backup, but a it does not contain all the filegroups.
It contains all the data in the primary filegroup, every read/write filegroup, and any
optionally-specified read-only files.
Q.What is Mirrored on-line Redo Log?
A.Due to Mirrored online redo logs and disk array mirroring, the occurrence of redo log corruptions
has dropped to practically zero. The On-line Redo Log is a set of two or more on-line redo files that
record all committed changes made to the database.
Q.Explain the concept of the DUAL table.
A.On installing Oracle database, DUAL table Is present by default. It is a special table with just one
row. It has a single column called DUMMY. The data type of this column in VARCHAR2(1). It has a
value “X”. It is most commonly used to select pseudo columns in Oracle like sysdate.Select sysdate
Q.What are the ways tablespaces can be managed and how do they differ?
A.Objects can be assigned to a table space. The related objects can be then grouped together. Table
space can also be managed using extents. Extents consist of a specific number of contiguous data
blocks. For the required extent, the free extent closest in size is determined.
Q.What is Cache Fusion Technology?
A.In Cache fusion, multiple buffers join to act as one. It eliminates disk i/o operaions by making use
of a scalable shared cache. It treats multiple buffer caches as one thereby resolving data consistency
issues. Cash fusion technology can provide more resources and increases concurrency of users.
Q.What is the difference between Cloning and Standby databases?
A.The clone database is a copy of the database which can be opened in read write mode. It is treated
as a separate copy of the database that is functionally completely separate. The standby database is a
copy of the production database used for disaster protection. In order to update the standby database;
archived redo logs from the production database can be used. If the primary database is destroyed or
its data becomes corrupted, one can perform a failover to the standby database, in which case the
standby database becomes the new primary database.
Q.What is a join, explain the types of joins?
A.A Join is used to create query using two or more tables and views.
Q.Types of JOIN are
B.Outer (Left, Right and Full)
A.Inner joins are also called as Equi join. They are the most common joins used in the SQL*PLUS.
They are known as Equi Joins because the where clause generally compares two columns from the
respective tables with the equivalence operator ‘=’. This is default join in many systems.
Outer joins are similar to inner joins with a bit more flexibility while selecting data from the related
tables. This type of joins are generally used in cases where it is desired, to select all rows from either
table (left or right or both) regardless of whether the tables have values in common and usually enter
NULL where data is missing.
Cross Join returns something known as Cartesian’s Product. Cross join operation combines every row
from left table with every row from right table. In some cases it creates havoc but used in perfect
condition it is very useful. This type of join is used in very less cases as it may use long period of
time and memory while operation.
Q.What is a cursor? What are its types?
A.Oracle engine uses private working area to process queries. This work area is called as cursor. The
data that is stored in the cursor is called as Active Data Set.
The main types of Cursors are:
Implicit cursors (predefined cursor): – The oracle engine implicitly opens a cursor on the server to
process each SQL statement. Implicit cursors are managed by the Oracle engine itself.
Explicit Cursors (user defined cursor): – Explicit Cursors are the cursors created by the user inside a
PL/SQL block. The declare section of a PL/SQL block and used within its executable Section.
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 drawback of cursor?
•Implicit cursors are less efficient than explicit cursors
•Implicit cursors are more vulnerable to data errors
•Implicit cursors provide less programmatic control
Q.What is Data Block?
•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.Explain the memory structure of Oracle.
A.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:
Shared pool: It stores most recently executed statements.
Database buffer cache: It stores copies of data block.
Redo log buffer: It records all changes made to the database blocks.
Large pool: It is optional area of the memory that can be used by the shared pool in order to relieve
load on it.
Java pool: This area is used for parsing java command.
Q.Define CLOB and NCLOB datatypes ?
A.Both CLOB and NCLOB are used to store huge character data in the database.
CLOBs store single-byte character set data.
NCLOBs store fixes-length multi-byte character set data.
Both these datatypes participate fully in transactions.
Q.Write the command to view the structure of the table.
A.The desc table_name command is used to view the structure of the table
Q.What are the limitation of alter command?
A.ALTER Command supports only the RENAME TABLE and ADD COLUMN variants. Other kinds of
ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are omitted.