Oracle Interview Questions – Part 40

Q.What is the difference between DATABASE ADMINISTRATION & ORACLE?
A.Database Administration is task of managing the database.
Oracle is RDBMS(Relational database management system),is a software used to create and manage the database.
Basically used to:
1.Create database.
2.Store data in to the database.
3.Manipulate the already existing data in the database.
4.Retrieve the data from database.

Q.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.Name some built-in functions that can be used in SQL queries?
A.There are six basic types of built-in functions
Character functions analyze and modify the contents of CHAR and VARCHAR2 string variables.
Numeric functions are a full range of operations that manipulate numbers, including trigonometric, logarithmic, and exponential functions.
Date functions are utilities that allow programmers to perform high-level actions on date variables, including date arithmetic.
Conversion functions convert from one datatype to another, often formatting the output data at the same time.
LOB functions allow operations on LOB (large object) data.
Miscellaneous functions perform operations that don’t fall into any of the other categories.
Example of built in functions:
1.to_date – a convertion function that will convert a string date to a date datatype
2.sum – a group function that will return the summation of all values in the column
3.upper – a single row function that will return all characters in a column in upper case

Q.What is an index segment ?
A.Oracle creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment. (The segments of a table and an index associated with it do not have to occupy the same tablespace.) Setting the storage parameters directly affects the efficiency of data retrieval and storage

Q.What is clusters ?

A.Group of tables physically stored together because they share common columns and are often used together is called Cluster.

Q.What is a datafile ?

A.It is the file where actual data are stored.
It is the physical part of the database

Q.What is a Database Schema in Oracle?

A.A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include: tables, views, and other types of data objects.

Q.What is a data segment ?

A.A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table’s data segment, and for each index, Oracle allocates one or more extents to form its index segment.
Oracle databases use four types of segments, which are described in the following sections:
A single data segment in an Oracle database holds all of the data for one of the following:
A table that is not partitioned or clustered
A partition of a partitioned table
A cluster of tables

Q.Explain What is a cursor? Why do you need them? What are the different kinds of cursor? uses private sql area to execute sql queries and store the uses cursors to name these private sql area and access its stored information
there r two types of cursors
implicit cursors are used for all dml and single row queries.these are system defined
explicit cursors are used for queries which return multiple row .these are user defined.

Q.What Is a User Role in Oracle?

A.A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users.

Q.What are the characteristics of data files ?

A.Characterstics of Data File :
A Data file can be associated with only one database.
Once created, a data file cannot change is size.
One or more data files form a logical unit of database storage called a Tablespace.

Q.What are the basic element of Base configuration of an oracle Database ?

A.It consists of
one or more data files.
one or more control files.
two or more redo log files.
The Database contains
multiple users/schemas
one or more rollback segments
one or more tablespaces
Data dictionary tables
User objects (table,indexes,views etc.,)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool)
SMON (System MONito)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
CKPT (Check Point)
User Process with associated PGS

Q.What is a tablespace?

A.A tablespace is a logical storage unit in a database which is used to group related structures together.

Q.What is SYSTEM tablespace?

A.SYSTEM tablespace is automatically created when the database is created. It contains data dictionary for the entire database.

Q.What are the types of Segments in oracle?

A.An Oracle database can use four types of segments:
Data segment–Stores user data within the database.
Index segment–Stores indexes.
Rollback segment–Stores rollback information used when data must be rolled back.
Temporary segment–Created when a SQL statement requires a temporary work area such as during sorting of data.

Q.Define Data-Dictionary Cache ?

A.Data-Dictionary Cache keeps information about the logical and physical structure of the database.
The data dictionary contains information such as the following:
User information, such as user privileges
Integrity constraints defined for tables
Names and data types of all columns in database tables
Information on space allocated and used for schema objects

Q.What are LOB data types?

A.The LOB datatypes such as BLOB, CLOB, NCLOB and BFile can store large blocks of unstructured data such as graphics, image, video clips etc. They provide random and piece-wise access to the data.

Q.List out advantages of PL/SQL?

A.Support SQL data manipulation.
Provide facilities like conditional checking, branching and looping.
Provide fast code execution since it sends SQL statement as a block to the oracle engine.

Q.What are the privilege types?

A.The privileges can be of system or object level.
SYSTEM level privileges can be assigned by the DBA and the object level privileges are assigned by the owner of the particular object.

Q.What are the kinds of roles in oracle?

A.Following are the kinds of roles available in oracle:
This role allows access to the table belonging to other users. It allows using Select, Insert, Update and Delete command.
With this role, one can create his own tables, sequences, procedures, triggers, indexes and clusters.
It has system privilege and can grant those privileges to other.

Q.What is composite data type?

A.Collections are usually referred to as Composite Data types. Composite type is one that has components in it. A variable of a composite type contains one or more scalar types.

Q.Explain the types of composite data type.

A.Following are the types of composite data types:
RECORD – PL/SQL records are similar to C structures. A record provides with a way to deal with separate but related variables as a unit.
TABLE – They are similar to arrays in C. However, they are implemented differently.
In order to declare a PL/SQL table, you need to first define a table type, then you declare a variable of this type.

Q.Explain ROWID in oracle.

A.Each table in oracle has a pseudocolumn called ROWID. Oracle uses ROWID to store address of each rows of the table. Oracle uses ROWID to maintain indexes. Each key in an index is associated with a ROWID that points to the associated row’s address for fast access. Explain DCL command.
DCL: Data Control Language
DCL are used to configure and control database objects.
Following are the DCL commands:

Q.What is TCL command?

A.TCL – Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
•COMMIT – save work done
•SAVEPOINT – identify a point in a transaction to which you can later roll back

•ROLLBACK – restore database to original since the last COMMIT
•SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use

Q.How PL/SQL Exceptions Are Raised?

A.The PL/SQL exceptions can be raised in 4 ways. Either the user can raise them or they can be raised by the PL/SQL engine. They are as follows:
The PL/SQL runtime engine raised named system exception: These exceptions are raised automatically by the program. You cannot control when PL/SQL will raise a system exception.
The programmer raised named exception: The programmer can use an explicit call to the RAISE statement to raise a programmer-defined or system-named exception.
The programmer raised unnamed, programmer-defined exception: These are raised with an explicit call to the RAISE_APPLICATION_ERROR procedure in the DBMS_STANDARD package.
The programmer re-raised “current” exception: From within an exception handler, you can re-raise the same exception for propagation to the enclosing block.

Q.Explain the guidelines for Avoiding and Handling PL/SQL Errors and Exceptions.

A.Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions
•Use both error checking and exception handling to ensure your program can handle all possibilities.
•Add exception handlers whenever there is any possibility of an error occurring.
•Add error-checking code whenever you can predict that an error might occur if your code gets bad input data.
•Make your programs robust enough to work even if the database is not in the state you expect.
•Handle named exceptions whenever possible, instead of using WHEN OTHERS in exception handlers.
•Test your code with different combinations of bad data to see what potential errors arise.
•Write out debugging information in your exception handlers.
•Carefully consider whether each exception handler should commit the transaction, roll it back, or let it continue.

Q.Explain the rules for writing a package.

A.Packeges are PL/SQl constructs that allow related data to be stored together. A package has two parts: specification and a body.
The syntax of writing a package is:
procedure_specification |
function_specification |
variable_declaration |
type_definition |
exception_declaration |
END [pkg_name];

Q.What are the use of Export and Import command?

A.Use the IMPORT command to import data into a nickname from a file and the EXPORT command to export data from a nickname to a file.

Q.Explain drop and truncate table command.

A.TRUNCATE removes all rows from a table.
The operation cannot be rolled back and no triggers will be fired.
The following example will show what a TRUNCATE does:
Table truncated.
The DROP command removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
The following example will show what a DROP does:
Table dropped.

Q.What is correlated sub-query?

A.In a simple SubQuery, the result retrieved by the inner query is fed to the outer query. The outer query takes the result as its input and processes it to produce its output.
However, in a corelated sub query, a correlated sub-query is dependent upon the outer query.
The outer query and the sub-query are related typically through a WHERE statement located in the sub-query.
The sub query gives a reference to the outer query. Then the outer query executes and the result is returned to the sub query. Finally the sub query is executed for every row that is selected by the outer query.

Q.Explain types of tables in oracle.

A.In Oracle, tables are the basic storage units. There are several types of database tables. Different types of tables have different characteristics.
Heap Organized Table:
The default table type in Oracle is heap table. If you use “create table” clause normally, you create a standard database table. Exp:
create table A
x char
Object Table
This type of table is created based on an object type and not collection of columns. It is usually used in special cases. The logic depends on object-oriented programming. “create table ” statement for object tables is:
create table test of x_type;
create or replace type atype
as object
a varchar2(30),
b date
Create table p of atype;
Index Organized Tables
Index Organized Tables have their primary key and non-key column data stored within the same B-Tree structure.
The data is stored within the primary key index.
IOTs make accessing data via the primary key quicker as the key and the data reside in the same structure.
Since there is no need to read an index, the table data is read in a separate structure.
Less duplication of the key columns in an index and table mean low storage requirements.
Hash clustered tables
Hash clusters provide a good facility for storage and retrieval.
In the case of a heap table, the rows are not sorted in a user-controlled manner.
Oracle hash cluster tables can improve random row access speed by foour times as the hash can get the row location far faster than index access.
Multiple table hash clusters store logically-related rows on a single data block due to which you can access a whole unit of data in a single physical I/O.
Nested tables
Nested table can be used to link the subordinate data items to the base table using the object ID (OID).
Oracle objects can be directly referenced by using pointers as opposed joining relational.
Objects in standard relational databases need to be reassembled every time it is used.
Index cluster tables
An index cluster uses an index to maintain row sequence.
A table cluster is a group of tables that share the same data blocks, since they share common columns and are often used together.
All rows for each table are stored in the same data blocks, When you create cluster tables are created.
The cluster key value is the value of the cluster key columns for a particular row.
Index cluster tables can be either multi-table or single-table.
Global temporary tables
The maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
The data in a global temporary table is private.

Q.Difference between database triggers and form triggers.

A.Database triggers (DBA)
•Fired when a DML operation is performed
•They manipulate data stored in Oracle tables
•They can cause other database triggers to fire.
•They can be fired from any session executing the triggering DML statements.
Form trigger (FT)
•Fired in response to any event that takes place while working with the forms.
•They manipulate data in Oracle tables as well as variables in forms.
•They cannot cause other form triggers to fire, but can fire other database triggers.
•They can be fired only from the form that define the trigger.

Q.What WHERE CURRENT OF clause does in a cursor?

A.PL/SQL provides the WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor.This allows you to easily make changes to the most recently fetched row of data.
UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
Notice that the WHERE CURRENT OF clause references the cursor and not the record into which the next fetched row is deposited.

Q.Can you pass a parameter to a cursor?        
A.PL/SQL also allows you to pass parameters into cursors. It eases your work because:
-A parameter makes the cursor more reusable.
-A parameter avoids scoping problems.
However, you should pass parameters when you are goint to use it at more then one place and when there are hoing to be different values for the same WHERE statement.

Q.Explain the functioning of CURSOR FOR LOOP with example.
A.Lets have a look at what Cursors are before going to the Cursor FOR loop.
A Cursor is a PL/SQL construct and accesses the stored information in a named work area.
There are 2 types of cursors:
Implicit: queries that return only one row
Explicit: can be declared by us for the queries that return more than one row.
CURSOR cursor_1 IS
SELECT roll_no, student_name FROM student WHERE grade = 4;
A PL/SQL program opens a cursor, processes rows returned by a query, then closes the cursor.
This can be done with the help of:
OPEN, FETCH, and CLOSE statements
Cursor FOR Loops
Instead of using OPEN, FETCH, and CLOSE statements, coding can be simplified by using FOR loops.
A cursor FOR loop opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed.
In the example below, the cursor FOR loop implicitly declares stud_record as a record:
CURSOR cursor_1 IS
SELECT student_name, birthdate FROM student;

FOR stud_record IN cuesor_1 LOOP


Q. Define Simple/Explicit , Parametric and Internal/Implicit cursor.
A Cursor is a PL/SQL construct and accesses the stored information in a named work area.
There are 2 types of cursors:
Implicit: queries that return only one row
Explicit: can be declared by us for the queries that return more than one row

Q.What are dynamic reports? How will you create them?
A.Following steps should be followed to build a Dynamic Report:
•Create a temporary table first.
•Then insert data to the temporary table using the After Form Trigger.
•Generate the report using this temporary table in the Data Model.
•Delete all records from the temporary table in the After Report trigger.

Q.Difference between Oracle Forms and Apps Forms.
Oracle Forms
•Oracle Forms renders applications using metadata stored in an .fmx file.
•It runs client-side PL/SQL.
•It is accessed using a web browser and its user interface is rendered using a JVM.
•It uses exact positioning.
•It provides robust field-level validation and event processing.
•It uses BI Beans as its integrated charting engine.
•It supports a range of locking models with pessimistic as the default.
•Each connected user in It maintains a synchronous connection to the Oracle database.
•It uses synchronous connections to allow transactions to span multiple screen interactions.
•With Oracle Forms, Application logic is processed in the Oracle database, a mid-tier Forms Server, or in the rich client.

Apps Forms
•It renders applications using metadata stored in an Oracle database.
•It uses server-side PL/SQL.
•It is also invoked from a Web browser but its user interface is HTML and JavaScript.
•It uses HTML-relative positioning.
•It supports declarative page-level validation and event processing. Programmatic field-level validation and event processing requires Javascript and AJAX.
•It uses Flash Charts as its integrated charting engine.
•Due to its asynchronous architecture, It uses an optimistic locking model.
•It does not transparently allow transactions to span page views. It programmatically supports transactions spanning page views using collections.
•Its users are asynchronously connected to the Oracle database.
•With Apps Forms, PL/SQL application logic is processed within the Oracle database. Client-side logic is implemented using JavaScript. HTTP communications are facilitated using Apache and Mod/PLSQL.
Both Oracle Forms and Apps Forms support the calling of Web Services, for example BPEL.
Sequence of firing triggers in forms
The following sequence should be used to fire triggers when a form opens:
The folowing sequence should be followed when you exit from the form: text item record block form

Q.How many memory layers are in the oracle shared pool? Explain them
A.Oracles shared pool consists of two layers namely, Library cache and Data dictionary cache.
Library cache: This layer has information about SQL statements that were parsed, information about cursors and any plan data.
Data Dictionary cache: this Layer has information about the accounts of the users, their privileges and segments information.

Q.What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
A.When the cache client like a CPU, web browser etc finds a successful entry in the cache memory, it is called as a cache hit. The percentage of these successful cache hits is called as cache hit ratio. Higher the cache hit ratio better will be the performance because data read from the cache is faster than the memory.

Q.What are PCT Free and PCT Used? What is PCT increase parameter in segment?
A.PCTFREE is a parameter used to find how much space should be left in a database block for future updates. This means that if the PCTFREE = 20, new rows will be added in the block until it is 80% full.
PCTUSED is a parameter helps Oracle to find when it should consider a database block to be empty enough to be added to the freelist. This means that if the PCTFREE = 50, new rows will be not be added in the block until sufficient rows are deleted from the block so that it falls below 40% empty.
PCTINCREASE parameter is used to find how much will the each subsequent segment will grow. This value is in %.

 in Oracle


This site uses Akismet to reduce spam. Learn how your comment data is processed.