Interview Questions: 2011

what are exception in oracle

Some of the standard Oracle PLSQL Exception are as follow:

1	NO_DATA_FOUND
2	TOO_MANY_ROWS
3	CURSOR_ALREADY_OPEN
4	DUP_VAL_ON_INDEX
5	INVALID_CURSOR
6	INVALID_NUMBER
7	ZERO_DIVIDE

DUP_VAL_ON_INDEX - Attempted to insert a duplicate value

INVALID_CURSOR - Illegal cursor operation occurred

INVALID_NUMBER - Conversion of character string to number
fails

NO_DATA_FOUND - Single row SELECT returned no data

TOO_MANY_ROWS - Single-row SELECT returned more than one row

ZERO_DIVIDE - Attempted to divide by zero

what is the structure of plsql block

DECLARE
             Variable Declaration
              Cursor Declaration
              User Defined Exception
IS

BEGIN
Executable Statement

EXCEPTION
Exception Handling. Action to perform when error occurs.
END;

Declare Section is optional. Variable and Cursor declaration is defined in declare section.

Executable Statement is defined in Begin Section.

Exception Section is the section where exception can be handled. This is an optional section

difference between procedure and function

	Function must return value whereas procedure does not

	Function can be used in SQL statement whereas procedure can not. Function can be used in SQL statement, if it do not perform any DML operation

	USER_FUNCTIONS data definition stores function details. USER_PROCEDURES data definition stores procedure details

difference between procedure and function

	Function must return value whereas procedure does not

	Function can be used in SQL statement whereas procedure can not. Function can be used in SQL statement, if it do not perform any DML operation

	USER_FUNCTIONS data definition stores function details. USER_PROCEDURES data definition stores procedure details

what are build in package available in plsql

1	DBMS_OUTPUT
2	DBMS_SQL
3	DBMS_UTL
4	UTL_FILE
5	DBMS_STANDARD

how to create global temporary table in oracle

A temporary table has a definition and structure like that of a regular table, except that the data only exists for the duration of the current transaction or session.

CREATE GLOBAL TEMPORARY TABLE temp_table
     (a number,
      b varchar2(10)
     );

Temporary table is cleaned up atomically at session end or transaction end.

INSERT INTO temp_table
VALUES (1,'ABC');

If you fire select statement, it will display data.

SELECT * FROM temp_table;

OUTPUT
-------------------
A B
---------------
1 ABC

Once you commit, the data from temporary table is deleted.

COMMIT;

If you fire select statement, it will not display any data.

SELECT * FROM temp_table;

i.e., in same session once the data is committed it will delete data
from temporary table.

The default behaviour of temporary table is ON COMMIT DELETE ROWS, which means that rows are visible only within the transaction.

ON COMMIT PRESERVE ROWS - The rows are visible after the transaction, but within the session only.

create global temporary table temp1(a number, b varchar2(5))
ON COMMIT PRESERVE ROWS;

what are the different optimisation techniques

	Execute Plan - we can see the plan of the query and change it accordingly based on the indexes

	Optimizer_hint - set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS'); Select /+ First_Rows / Deptno,Dname,Loc,Rowid from dept where (Deptno > 25)

	Optimize_Sql - By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for ever time the SQL must be parsed whenever they are executed.

	Optimize_Tp - By setting the Optimize_Tp= No, Oracle Forms assigns separate cursor only for each query SELECT statement. All other SQL statements reuse the cursor. f45run module = my_firstform userid = scott/tiger optimize_Tp = No

how to implement If statement in the select statement

We can implement the if statement in the select statement by using the Decode statement.

Example

SELECT DECODE (EMP_CAT,'1','First','2','Second'Null);

Here the Null is the else statement where null is done . Here DECODE implement below IF – ELSE logic

                     IF EMP_CAT = ‘1’ THEN
                                  ‘FIRST’
                     ELSIF EMP_CAT = ‘2’ THEN
                                  ‘SECOND’
                     ELSE
                                   NULL
                     END IF;

how many types of sql statements are there in oracle

There are basically 6 types of sql statement

Data Definition Language(DDL) - The DDL statements define and maintain objects and drop objects.

Data Manipulation Language(DML) - The DML statements manipulate database data.

Transaction Control Statements - Manage change by DML

Session Control - Used to control the properties of current session enabling and disabling roles and changing .e.g., Alter Statements, Set Role

System Control Statements - Change Properties of Oracle Instance .e.g., Alter System

Embedded Sql - Incorporate DDL,DML and T.C.S in Programming Language.e.g.,Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

how many Integrity Rules are there

	Entity Integrity Rule - The Entity Integrity Rule enforces that the Primary key cannot be Null.

	Foreign Key Integrity Rule - The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.

	Business Integrity Rules - The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

what does database do during mounting process

During database mount process, Oracle would check for the existence of control files mentioned in init.ora file but it wont check the contents of the control file which is done during the opening of database.

what is the use of redo log information

Redo log information are used to recover database if it get corrupt.

what is the frequency of log updated

COMMIT or ROLLABCK
Time out occurs (3 secs)
1/3 of Log full
1 MB of Redo
Checkpoint occurs

Can objects of the same schema reside in different tablespaces

Yes, it can. For example if you specify a different tablespace (B) for indexes, the indexes of the tables that the user create would be residing in B, and the table would reside in the user's default tablespace A.

Yes: Schema objects can stored in different tablespace and a tablespace can contained one or more schema objects data.

does view contain data

A view does not contain any data of its own, but is like a window through which data from other tables can be viewed and changed

The answer depends on the type of view. In case of normal view, the answer is NO it only contains query based on a base table but in case of materialized view, YES it does contain data and for the updated data in the base table, it needs to be refreshed.

check which user has which role

You can use the below tables to find which user has assigned which role.

DBA_TAB_PRIVS
ROLE_TAB_PRIVS
DBA_ROLE_PRIVS

Run the below script:

                             SELECT *
                                FROM dba_role_privs
                              ORDER BY grantee;

what is an Oracle index

An Index is a tree structure that allows direct access to a row in a table. Indexes can be classified based on their logical design or on their physical implementation. The Logical classification groups indexes from an application perspective, while the physical classification is derived from the way the indexes are stored.

An index is a schema object that can speed up the retrieval of rows by using pointers. If you do not have an index, then a full table scan occurs. Its purpose is to reduce disk I/O by using an indexed path to locate data quickly. If a table is dropped, the corresponding indexes are also dropped.

It is created in existing table to locate rows more quickly & efficiently. The users cannot see the indexes; they are just used to speed up the queries

Oracle provides many different types of indexes:

B*Tree Indexes
Index Organized Tables
B*Tree Cluster Indexes
Reverse Key Indexes
Descending Indexes
Bitmap Indexes
Function-based indexes

USER_INDEXES data dictionary view contains the name of the index and its uniqueness

USER_IND_COLUMNS view contains the index name, the table name, and the column name.

can we create index on long column

NO we can't create index on long raw column.

what are clusters

Groups of tables physically stored together because they share common columns and are often used together is called clusters.

what is a database instance

An Oracle instance is a combination of background processes and memory structures (SGA).

What is bitmap index

Bitmap indexes are designed for data warehousing/ad‐hoc query environments where the full set of queries that may be asked of the data is not totally known at system implementation time.

Bitmap indexes are structures that store pointers to many rows with a single index key entry, as compared to a B*Tree structure where there is parity between the index keys and the rows in a table.

Example
CREATE BITMAP index job_idx on emp(job);

Oracle will store something like this:

Value	1	2	3	4	5	6	7	8	9	10
ANALYST	0	0	0	0	0	0	0	1	0	1
CLERK	1	0	0	0	0	0	0	0	0	0
MANAGER	0	0	0	1	0	1	1	0	0	0
PRESIDENT	0	0	0	0	0	0	0	0	1	0
SALESMAN	0	1	1	0	1	0	0	0	0	0

Row 8 and 10 have the value for ANALYST whereas row 4,6 and 7 have the value for MANAGER.

what is a functional index

Function-based indexes can use any Function or Object method that is declared as repeatable. Function‐based indexes give us the ability to index computed columns and use these indexes in a query.

Queries using expressions can use the index.
Example
CREATE INDEX sales_margin_inx
ON sales (revenue - cost);

SELECT ordid
FROM sales
WHERE (revenue - cost) > 1000;

We have to enable Function-based indexes by enabling the following initialization parameters:

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Function based Index

difference between and constraints and triggers

Constraints are used to maintain the integrity and atomicity of database.
In other words it can be said they are used to prevent invalid data entry.
The main 5 constraints:

NOT NULL
PRIMARY KEY
FOREIGN KEY
UNIQUE KEY
CHECK

Triggers are basically stored procedures, which automatically fired when any insert, update
or delete is issued on table.

Trigger effected only those row after which trigger applied but constraint effected all row of table.

what is normalization and its advantage

1	The process of separating data into distinct, unique sets is called normalization. This is implemented to improve the performance of the RDBMS, such as reduces redundancy of data and data inconsistency.

2	Normalization is the process of removing redundant data from your tables in order to improve storage efficiency, data integrity and scalability.

3	Database normalization is a series of steps followed to obtain a database design that allows for consistent storage and efficient access of data in a relational database. These steps reduce data redundancy and the risk of data becoming inconsistent.

4	Normalization is the process used to reduce the unnecessary repetition of data i.e., redundant data. It is performed on the data, which is redundant and makes the data in a normalized format. It is of step-by-step process Ist Normal, Form IInd Normal, form IIIrd Normal, form IVth Normal form or Boyce odd Normal form By performing this we will get the data in the Normalized formatted from DBMS to RDBMS.

Find how many database resides in Oracle Server

select count(*) from v$database;

Script will return the number of database that resides on Oracle Server.

What is meant by Virtual Indexes in Oracle

Virtual Indexes are another undocumented feature used by Oracle. Virtual indexes, as the name suggests are pseudo-indexes that will not behave the same way that normal indexes behave, and are meant for a very specific purpose. A virtual index is created in a slightly different manner than the normal indexes. A virtual index has no segment pegged to it, i.e., the DBA_SEGMENTS view will not show an entry for this.

Oracle handles such indexes internally and few required dictionary tables are updated so that the optimizer can be made aware of its presence and generate an execution plan considering such indexes.As per Oracle, this functionality is not intended for standalone usage. It is part of the Oracle Enterprise Manger Tuning Pack (Virtual Index Wizard).

The virtual index wizard functionality allows the user to test a potential new index prior to actually building the new index in the database.It allows the CBO to evaluate the potential new index for a selected SQL statement by building an explain plan that is aware of the potential new index.

This allows the user to determine if the optimizer would use the index, once implemented.

What are materialized views

Materialized view is like a view but stores both definition of a view plus the rows resulting from execution of the view. It uses a query as the bases and the query is executed at the time the view is created and the results are stored in a table. You can define the Materialized view with the same storage parameters as any other table and place it in any table space of your choice. You can also index and partition the Materialized view table like other tables to improve performance of queries executed against them.

The main concept is to reduce the execution time of long running query, bu summarizing data in the database.

CREATE MATERIALIZED VIEW my_all_objects_aggs
BUILD IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT owner, count(*)
FROM my_all_objects
GROUP BY owner;

Uses of Materialized View

Less physical reads - There is less data to scan through.
Less writes - We will not be sorting/aggregating as frequently.
Decreased CPU consumption - We will not be calculating aggregates and functions on the data, as we will have already done that.
Markedly faster response times - Our queries will return incredibly quickly when a summary is used, as opposed to the details. This will be a function of the amount of work we can avoid by using the materialized view, but many orders of magnitude is not out of the question.

Difference Between Union and Union All

UNION only selects distinct values.
UNION ALL selects all values, including duplicates.

Syntax
SQL Statement1
UNION [ALL]
SQL Statement2

In order to perform UNION the column in SQL statement1 must match column in SQL statement2

SELECT * FROM employees
UNION
SELECT * FROM employees2;

ID	Last Name	First Name	Title
1	Johnson	David	crew
2	Hively	Jessica	crew
9	Hicks	Freddy	crew
10	Harris	Joel	crew
11	Davis	Julie	manager
101	Yazzow	Jim	crew
102	Anderson	Craig	crew
103	Carlson	Kevin	crew
104	Maines	Brad	crew

UNION ALL selects all rows from each table and combines them into a single table.

SELECT * FROM employees
UNION ALL
SELECT * FROM employees2;

ID	Last Name	First Name	Title
1	Johnson	David	crew
2	Hively	Jessica	crew
9	Hicks	Freddy	crew
10	Harris	Joel	crew
11	Davis	Julie	manager
101	Yazzow	Jim	crew
102	Anderson	Craig	crew
103	Carlson	Kevin	crew
11	Davis	Julie	manager
104	Maines	Brad	crew

Create Table from another Table

CREATE TABLE new_table
AS
(SELECT * FROM old_table
WHERE 1 = 2);

This would create a new_table that will not included data from old_table. It will only copy the table structure.

To create a new table from the existing multiple table.

CREATE table new_table
AS
(SELECT column1,column2,..column_n
FROM old_table1,old_table2,..old_table_n);

To Create new table from existing table along with the data.
CREATE TABLE new_table
AS
(SELECT * FROM old_table
WHERE 1 = 1);

How to Delete Duplicate Rows

DELETE FROM employees
WHERE rowid NOT IN
                   (SELECT max(rowid)
                      FROM employees
                     GROUP BY emp_id);

Subquery select a unique rowid from all the rows. Rows which are not return by subquery are the duplicate rows.

How to alter column name

alter table <table_name>
rename column <old_column_name> to <new_column_name>;

Rename column name to new name.

To rename more than one column

alter table <table_name>
rename(old col1,old col2,....) to (new col1,new col2,...);

What is difference between varchar and varchar2

	VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.

	If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.

	Name Varchar(10) - If you enter value less than 10, it utilize total 10 spaces. Name Varchar2(10) - If you enter value less than 10 then remaining space is not utilize.

Difference between procedure and function

Function return value. Whereas Procedure does not return value.

You can call function in SQL query. If function has DML (insert,update,delete) statements you can not call that function from SQL query. You cannot call a procedure in SQL query.

CREATE OR REPLACE FUNCTION function_name
      (argument IN | OUT | IN OUT data_type)
RETURN data_type
IS
BEGIN
          SQL statement1
                  statement2
          RETURN value;
EXCEPTION
   WHEN OTHERS
    THEN
          DBMS_OUTPUT.put_line(‘ERROR - ’||SQLERRM);
END;

CREATE OR REPLACE PROCEDURE procedure_name
     (argument IN | OUT | IN OUT data_type)
IS
BEGIN
          SQL statement1
                  statement2
EXCEPTION
   WHEN OTHERS
    THEN
           DBMS_OUTPUT.put_line(‘ERROR - ’||SQLERRM);
END;

Which date function is used to find the difference between two dates

MONTHS_BETWEEN(date2,date1)

Gives date2 minus date1 in months.

SELECT MONTHS_BETWEEN(sysdate,Birthdate)/12 as Age
FROM Birthday;

It calculate person's age.

SELECT trunc(date1-date2)
FROM dual;

Returns no of days

SQL Query Second highest salary

SELECT DISTINCT (a.sal)
    FROM emp a
WHERE &n = (SELECT COUNT (DISTINCT (b.sal))
                FROM emp B
               WHERE a.sal<=b.sal);

To Find Second Highest Salary. Enter Value for n = 2

OUTPUT
--------------
16000

SELECT LEVEL MAX(SAL)
FROM emp
WHERE LEVEL = &n
CONNECT BY PRIOR sal>sal
GROUP BY LEVEL;

What is difference between Decode and Case

Decode is function while Case is Statement.

Case cannot process null while decode can process null.

Case is an ANSI standard where as Decode is Oracle proprietary.

SELECT
       DECODE(grade,'A','Distinction',
                    'B','First Class',
                    'C','Second Class',
                    'D','Failed',
                    'Failed') Per_Grade
FROM grade;

SELECT
       CASE grade
         WHEN 'A' then 'Distinction'
         WHEN 'B' then 'First Class'
         WHEN 'C' then 'Second Class'
         WHEN 'D' then 'Failed'
         Else 'No Grade'
       END Per_Grade
FROM Grade;

Difference between primary key and foreign key

Difference between Primary key and Foreign Key

Primary Key	Foreign Key
Primary key can not be NULL	Foreign key can be NULL

Primary keys enforce entity integrity by uniquely identifying entity instances	Foreign keys enforce referential integrity by completing an association between two entities

What is difference between replace and translate functions

Replace function searches for a string and replaces with the given string.

Translate function searches for a character and it replaces in occurrence of the character.

REPLACE

The Replace function replaces one value in a string with another.
For example, you can replace each occurrence of a letter with matching number.

REPLACE (char,search_string,replace_string)

If value for replace_string is not specify, the search_string value, when found, is removed.

Possible input can be any character data types, like CHAR, VARCHAR2,NCHAR,CLOB.

SELECT REPLACE('COMPUTER','OM','AB')
FROM dual;

Output
-----------
CABPUTER

TRANSLATE

Translate does an orderly character-by-character substitution in a string.

TRANSLATE (string,if,then)

SELECT TRANSLATE(1256364,2345678,'BDEFGHI')
FROM dual;
Output
----------
BFGDGE

What is the use of cascade constraints

What is the use of CASCADE CONSTRAINTS

When this clause is used with DROP command, it deletes all foreign keys that reference the table to

be dropped, then drops the table.
i.e., parent table can be dropped even when a child table exists.

DROP TABLE table_name CASCADE CONSTRAINTS;

What is Difference between truncate delete and drop

DELETE
DELETE command is used to remove rows from table, it can be used with WHERE clause After DELETE operation you need to COMMIT or ROLLBACK to make the changes permanent DELETE command will cause all DELETE triggers on the table to fire DELETE is DML command
TRUNCATE
TRUNCATE removes all rows from tabl,it do not accept WHERE clause The operation cannot be ROLLBACKED and no trigger will be fired TRUNCATE is faster and it does not use much undo space as compared to DELETE TRUNCATE is DDL command
DROP
DROP command removes a table from the database. All the table's rows, indexes and privileges will also be removed No DML triggers will be fired. The operation cannot be rolled back DROP is DDL command

DELETE FROM employee WHERE job = 'CLERK';

TRUNCATE TABLE employee;

DROP TABLE employee;