Oracle Interview Questions – Part 45

Q.what is the physical database structure?

A.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 a tablespace.
2.Redo log files
The purpose of these files is to record all changes made to data. These files protect database against failures.
3.Control files
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 database objects in oracle?
1.Table:Composed of rows and column that stores data.
2.View:Represents subset of data from one or more tables.
3.Sequence:Auto generates primary key value.
4.Index:Improve performance of queries.
5.Synonym:Gives alternative names to object.

Q.Explain how to add a new column to an existing Table in Oracle.

A.Use the ALTER TABLE command to do this.
ALTER TABLE employee ADD (department VARCHAR2);

Q.How exceptions are raised in oracle?
A.Internal exceptions are raised implicitly by the run-time system. However, user-defined exceptions must be raised explicitly by RAISE statements

Q.Explain how to list all indexes in your schema.
A.The list of all indexes in a schema can be obtained through the USER_INDEXES view with a SELECT statement:
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = ‘tablename’;

Q.What are the purposes of Import and Export utilities?
A.Import and Export utilities are helpful in following ways:
They can be used for backup and recovery process.They can also be used while moving database between two different oracle instances.These utilities can be used to move data from one tablespace to other.

Q.What is the ref cursor in Oracle?
A.Cursor is a reference type in oracle. We can allocate different storage locations to the cursor when the program runs.
Return_type – Name of new reference type.
Return Type – This represents select list type that will be returned by the query.

Q.How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.

Q.What type of index should you use on a fact table?
A Bitmap index.

Q.Give two examples of referential integrity constraints.
A primary key and a foreign key.

Q.What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?
The buffer size limit is 1000000 and 32767 bytes per line.

Q.Difference between database triggers and form triggers?
-Data base trigger(DBT) fires when a DML operation is performed on a data base table. Form trigger(FT) Fires when user presses a key or navigates between fields on the screen
-Can be row level or statement level No distinction between row level and statement level.
-Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms.
-Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger.
-Can cause other database triggers to fire.Can cause other database triggers to fire, but not other form triggers.

Q.What is a cursor?
Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

Q.Which is more faster – IN or EXISTS?
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

Q.What is the purpose of a cluster?
Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.

Q.What is difference between SQL and SQL*PLUS?
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

Q.Can a primary key contain more than one columns?
Yes, primary key not allow null values,unique key allow only one null value.In table only one primary is allowed,unique key as many as it can allow.

Q.What is OCI. What are its uses?
Oracle Call Interface is a method of accesing database from a 3GL program. Uses–No precompiler is required,PL/SQL blocks are executed like other DML statements. The OCI library provides
–functions to parse SQL statemets
–bind input variables
–bind output variables
–execute statements
–fetch the results

Q.Can the default values be assigned to actual parameters?
yes you can assign default value to an actual parameter.. If the actual parameter holds NULL value then the DEFAULT Value will be assigned.

Q.Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?
SELECT customer_no, payments from customer C1
WHERE 3<=(SELECT COUNT(*) from customer C2
WHERE C1.payment <= C2.payment)

Q.How will you delete duplicating rows from a base table?
delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); or
delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);

Using ORACLE PRECOMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as Pro*C,Pro*Cobol,…
This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to creater an executable

Q.The maximum number of columns a table can have in oracle SQL ?
Maximum number of columns in a table or view is 1000

Q.How you open and close a cursor variable.Why it is required?
OPEN cursor variable FOR SELECT…Statement
CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used. In order to free the resources used for the query CLOSE statement is used.

Q.Explain What is an UTL_FILE.What are different procedures and functions associated with it?
UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to
output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.

Q.What is use of a cursor variable? How it is defined?
A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type (like a pointer in C).
Declaring a cursor variable:
TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list
that will eventually be returned by the cursor variable.

Q.Explain What is an UTL_FILE.What are different procedures and functions associated with it?
UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to
output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.

Q.Explain What are various joins used while writing SUBQUERIES?
Self join-Its a join foreign key of a table references the same table. Outer Join–Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.
Equi-join–Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.

Q.Explain Which datatype is used for storing graphics and images?
the following data types are used to store graphics or binary data
1)long raw
2)binary large object(BLOB)
3)binary file(BFILE)
*They are Mostly Used to Store Graphics,sounds,Scanned Documents etc.
The LOB Data Type Can Store Large and Unstructured Data like Text,Image,Video etc.
The Maximum Storage Size is Up to 4GB.
Binary FILE(BFILE) Data Type:
*The Maximum Size is 4GB.

Q.Explain Difference between an implicit and an explicit cursor?
PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR…IS statement. An implicit cursor is used for all SQL
statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.
The implicit cursor is used to process INSERT, UPDATE,DELETE, and SELECT INTO statements. During the processing of an implicit cursor,Oracle automatically performs the OPEN,FETCH, and CLOSE operations.
Where as in explicit cursors,the process of its working is done in 4 steps namely DECLARE a cursor,OPEN a cursor, FETCH from cursor and CLOSE a cursor.
IMPLICT CURSOR:- Automatically porvide by oracle which perform DML statements. queries returns only one row.
EXPLICT CURSOR:- Defined by user. queries returns more than rows.
Explicit Cursor:-We are not able to Handle NO_DATA_FOUND Exception.
Implicit Cursor:-We are able to Handle NO_DATA_FOUND Exception.

Q.Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive
log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in
archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is
occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is
typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the
database does not require being in archive log mode and thus there will be a slight performance gain as the database
is not cutting archive logs to disk.

Q.You have just had to restore from backup and do not have any control files. How would you go about
bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the
recover command with the using backup control file clause.

Q.What type of index should you use on a fact table?
A Bitmap index.

Q.Give two examples of referential integrity constraints.
A primary key and a foreign key.

Q.How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was
necessary then I would use the alter system set db_cache_size command.

Q.Explain an ORA-01555
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo
retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the
error message.

Q.Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or
aggregated from base tables. They are typically used in data warehouse or decision support systems.

Q.When a user process fails, what background process cleans up after it?

Q.What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to
store those objects meant to be used as the true objects of the database.

Q.Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.

Q.What view would you use to determine free space in a tablespace?

Q.How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.

Q.What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies
on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be
loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII
formatted or delimited files.

Q.Name two files used for network connection to a database.


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.

Q.Find out nth highest salary from emp table

For Eg:-
Enter value for n: 2

Q.Difference between procedure and function.
Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

Q.Which data type is used for storing graphics and images?

LONG RAW data type is used for storing BLOB’s (binary large objects).

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

SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
UPDATE students
SET current_credits=current_credits+v_numcredits


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