3 + Years Oracle EXperience Interview Questions And Answers

What is trigger in oracle?

Answer

Triggers are constructs in PL/SQL that need to be just created and associated with a table. Once they are created, when the table associated with it gets updated due to an UPDATE, INSERT or a DELETE, the triggers get implicitly fired depending upon the instructions passed to them.

What are the types of triggers?

Answer

The types of triggers are:

* Row level triggers

* Statement level triggers

* BEFORE and AFTER triggers

Disadvantages of trigger in oracle.

• Triggers can execute every time some field in database is updated. If a field is likely to be updated often, it is a system overhead.
• Viewing a trigger is difficult compared to tables, views stored procedures.
• It is not possible to track or debug triggers.

Explain Row level and statement level trigger.

A trigger if specified FOR EACH ROW; it is fired for each of the table being affected by the triggering statement. For example if a trigger needs to be fired when rows of a table are deleted, it will be fired as many times the rows are deleted. 

If FOR EACH ROW is not specified, it is application to a statement and the trigger is executed at a statement level.

Advantage of a stored procedure over a database trigger.

Stored procedures can accept parameters and can return values. Triggers can neither accept parameters nor return values. A Trigger is dependent on a table and the application has no control to not fire a trigger when not needed. On the other hand, a stored procedure can be called as needed.

What are cascading triggers?

At times when SQL statement of a trigger can fire other triggers. This results in cascading triggers. Oracle allows around 32 cascading triggers. Cascading triggers can cause result in abnormal behavior of the application.

What is Varrays?

VARRAY is varying array type that is typically used when the number of instances to be stored is small. It has a set of data elements and all are of the same data type. The size of VARRAY determines the number of elements.

Example: 

Defining a VARRAY object

CREATE OR REPLACE TYPE ProjectList AS VARRAY(50) OF Project;

How the triggers are attached to the table?

Answer

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.

What are triggering attributes?

Answer

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.

Difference between database triggers and form triggers.

Answer

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.

Explain the different types of trigger in oracle.

Answer

The types of triggers are:

Row level triggers

Statement level triggers

BEFORE and AFTER triggers

What are the types of triggers and how the sequence of firing in text item.

The different types of triggers are:

* Row triggers and statement triggers: specifies how many times the trigger should get executed – Once for every row or once for every triggering statement.

* Before and after triggers – Before trigger is fired before and update, insert or triggering statement is executed. After trigger is fired after the triggering statement is executed.

* Instead of Trigger- Here, the trigger is fired instead of executing the triggering statement

* Key triggers – fired on some key action.

* Mouse trigger- fired on some mouse navigation

* Navigational trigger – fired as a result of some navigation.

The sequence of firing in a text item are as follows::

a) pre - text

b) when new item

c) key-next

d) when validate

e) post text

Difference between database triggers and form triggers?

A database trigger is fired when a DML operation is fired on some database table. On the other hand form triggers are fired when user makes some event like pressing a key or navigating between fields of a form. A database trigger can be fired from any session executing the triggering DML statements. While form trigger is specific to the form. Database triggers can cause other database triggers to fire. On the other hand, form triggers cannot cause other form triggers to fire.

Explain sub-queries in brief.

Sub-query is the technique that lets us use the result of one query as part of another query. Sub-queries are very simple to implement and understand. Some queries can't be solved without sub queries.

What are the different operators used in building sub queries?

Answer -

IN and NOT IN operator

ANY

ALL

Exists

What are the guidelines for using SUB-QUERIES?

Answer

Following things need to be kept in mind when you write sub-queries:

* Caution should be taken with simple sub-query, especially when a normal value operator is used on the results of a sub-query, only one field must be returned

* If you want to check for the existence of a single value within a set of other values, use the IN keyword as an operator upon the result set from a sub-query.

Example of a Sub-Query:

Code:

SELECT name FROM students

     WHERE stud_id = (SELECT stud_id FROM class 

              WHERE last_name='abc'

                  AND first_name='xyz');

What is correlated sub-query?

Answer

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.

Oracle function, procedure and package Interview questions

Explain IN, OUT and INOUT in procedures.

Answer

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.

What are the rules of writing package?

Answer

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:

Code:

CREATE [OR REPLACE] PACKAGE pkg_name {IS | AS}

procedure_specification |

function_specification |

variable_declaration |

type_definition |

exception_declaration |

cursor_declaration

END [pkg_name];

Thus the rules of writing a package would be:

* A package should have a name,

* The elements within the package should be the same as they are in the declarative section of an anonymous block.

* The same syntax rules apply to the package header as the declarative section, except for the procedures and the functions declarations.

Explain the rules for writing a package.

Answer

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:

Code:

CREATE [OR REPLACE] PACKAGE pkg_name {IS | AS}

procedure_specification |

function_specification |

variable_declaration |

type_definition |

exception_declaration |

cursor_declaration

END [pkg_name];

Oracle Functions Interview Questions and Answers

What are conversion functions?

Answer

CHAR TO ROWID

Converts a string to a ROWID.

CONVERT

Converts a string from one character set to another.

HEX TO RAW

Converts from hexadecimal to raw format.

RAWTOHEX

Converts from raw value to hexadecimal.

ROWIDTOCHAR

Converts a binary ROWID value to a character string.

TO_CHAR

Converts a number or date to a string.

TO_DATE

Converts a string to a date.

TO_NUMBER

Converts a string to a number.

What is nested function?

Answer

When one function is called inside the other, it is called a nested function.

What is NVL function?

Answer

Oracle deals with Null values using NVL function. The function replaces the NULL value in the given column with the value provide to it. The function accepts two parameters, the first one is the column name and the second one is the value with which NULL has to be replaced.

Example

Select Amount – NVL(Disc, 0) from Price

What are SQL functions in oracle?

Answer

There are two types of functions – Single row that operates row by row. Group function operates on multiple rows.

Functions that fall under single functions are Date, Numeric, Character, Conversion and miscellaneous function

Functions that fall under group functions are avg, max, min, count, and sum.

Oracle Error Handling Interview Questions and Answers

What is Exception in Oracle?

Answer - Errors occur during runtime processing due to either hardware or network failure or application logic errors are known as exception.

Types of Exceptions in Oracle

Predefined Oracle Exception

User-Defined Exception

What are the predefined oracle exceptions?

Answer - Following is the predefined oracle exceptions

No_data_found

Too_many_rows

Zero_divide

Login_denied

Program_error

Timeout_on_resource

Invalid_cursor

Cursor_already_open

Dup_val_on_index

Explain user defined exceptions in oracle.

Answer - A user can explicitly raise an exception by using the Raise command.

How PL/SQL Exceptions Are Raised?

Answer

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.

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

Answer

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.

Explain the types of Exceptions.

Answer

Predefined oracle exceptions

These are the PL/SQL runtime engine raised named system exceptions. These exceptions are raised automatically by the program. You cannot control when PL/SQL will raise a system exception.

User-defined exceptions

These are programmer raised named exceptions. The programmer can use an explicit call to the RAISE statement to raise a programmer-defined or system-named exception. The programmer can also raise unnamed, programmer-defined exceptions and re-raised "current" exceptions.

Oracle Data Types Interview Questions and Answers

Explain the differences between char and varchar2 data types.

Answer

The char data type accepts strings that are of fixed length.

The VARCHAR2 data type is a varying length data type.

Explain BLOB, CLOB, NCLOB and BFILE.

Answer

These are the built in large object data types.

* BLOB, CLOB, NCLOB are stored internally where as BFILE is stored externally

* The BLOB data type stores binary large objects.

* The CBLOB data type stores character large objects.

* The NCBLOB data type stores character large objects in multibyte national character set.

* The BFILE data type enables access to binary file LOBs that are stored in file systems outside the Oracle database.

All these data types have a capacity of 4GB

What is a LOB data type?

Answer - The LOB datatypes includes BLOB, CLOB, NCLOB and BFILE that can store large blocks of unstructured data such as graphic images, video clips, and sound wave. LOB datatypes can be stored within a table, out-of-line within a tablespaces using a LOB locator, or in an external file (BFILE datatypes).

Explain ROWID in oracle.

Answer - 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.

Oracle Constraints Interview Questions and Answers

Explain the constraints that can be applied to Oracle tables.

Answer

The syntax of writing a table is

Code:

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

Explain various types of constraints in Oracle.

Answer

Oracle "Check" Constraint:

It validates incoming columns at row insert time.

Not Null Constraint:

It is used to specify that a column may never contain a NULL value.

Primary Key Constraint:

It is used to identify the primary key for a table. This operation requires that the primary columns are unique, and it creates a unique index on the target primary key.

References Constraint:

This is the foreign key constraint and is only applied during INSERT and DELETE.

Unique Constraint:

It is used to ensure that all column values within a table never contain a duplicate entry.

What is an integrity constraint?

Answer - Integrity constraints define a business rule for a column of the table. They are defined with a table and are stored as part of a table’s definition.

Types of integrity constraints supported by oracle are

NOT NULL integrity constraint

Unique Key integrity constraint

Primary Key integrity constraint

Foreign key integrity constraint

Check integrity constraint

Oracle Partitioning Interview Questions and Answers

How does one enable partition support for a database?

Partitioning is only available with Oracle Enterprise Edition as a cost option (you need to buy licenses before you can use it).

Start the Oracle installer and check if "Oracle Partitioning" is installed. If it is, you can just start using it.

If not, you will get error ORA-00439: feature not enabled: Partitioning. If you get this error, upgrade to Enterprise Edition and/or install the partitioning option.

What partitioning types does Oracle support?

Oracle support the following partitioning methods:

* Range partitioning - data is mapped to partitions based on a range of column values (usually a date column)

* Hash partitioning - data is mapped to partitions based on a hashing algorithm, evenly distributing data between the partitions.

* List partitioning - data is mapped to partitions based on a list of discrete values.

* Interval partitioning - data is mapped to partitions based on an interval

* Composite partitioning - combinations of the above methods

o Range-Hash partitioning - data is partitioned by range, then hashed to sub-partitions.

o Range-List partitioning - data is partitioned by range, then to sub-partitions based on a value list.

o Range-range partitioning

o List-range partitioning

o List-hash partitioning

o List-list partitioning

* Interval partitioning, an extension of range partitioning. New partitions are automatically added.

* System partitioning, application controlled partitioning.

* Reference partitioning, partitioned like a parent table (referenced in a referential constraint).

How do I know if an object is partitioned or not?

A simple query against the data dictionary will show if a given table or indexe is partitioned or not:

Code:

SELECT * FROM dba_part_tables;

SELECT * FROM dba_part_indexes;

How does one list the partitions of a partitioned object?

One can list the partitions of a table with:

Code:

SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name>';

One can show the partitions of an given index with:

Code:

SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name>';

Oracle ASM Interview Questions and Answers

Why should we use separate ASM home?

ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently.

How many ASM instances should one have?

Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system.

For clustered systems, create one ASM instance per node (called +ASM1, +ASM2, etc).

How many diskgroups should one have?

Generally speaking one should have only one disk group for all database files - and, optionally a second for recovery files (see FRA).

Data with different storage characteristics should be stored in different disk groups. Each disk group can have different redundancy (mirroring) settings (high, normal and external), different fail-groups, etc. However, it is generally not necessary to create many disk groups with the same storage characteristics (i.e. +DATA1, +DATA2, etc. all on the same type of disks).

To get started, create 2 disk groups - one for data and one for recovery files. Here is an example:

Code:

CREATE DISKGROUP data    EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....;

CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....;

Here is an example how you can enable automatic file management with such a setup:

Code:

ALTER SYSTEM SET db_create_file_dest   = '+DATA' SCOPE=SPFILE;

ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE;

You may also decide to introduce additional disk groups - for example, if you decide to put historic data on low cost disks, or if you want ASM to mirror critical data across 2 storage cabinets.

Should I create table and index tablespaces in separate diskgroups?

No, tables and indexes can be stored within a single disk group. Do not create different disk groups for tables and indexes.

How to check how much disk space is allocated/ in-use by an ASM instance?

Login to your +ASM instance (SYS AS SYSDBA) and execute the following query:

Code:

SQL> COL % FORMAT 99.0

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 "%" FROM v$asm_diskgroup;

NAME                              FREE_MB   TOTAL_MB     %

------------------------------ ---------- ---------- -----

DATA                               917104    1482145  61.9

RECOVER                         17387        17437   99.7

From Oracle 10g Release 2, one can also use the asmcmd command line utility:

ASMCMD> du

Used_MB      Mirror_used_MB

   1523                1523

ASMCMD> lsdg

State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name

MOUNTED  EXTERN  N      N         512   4096  1048576     11264     9885                0            9885              0  DISKGROUP1/

MOUNTED  EXTERN  N      N         512   4096  1048576     10240     9906                0            9906              0  FLASH/

How big should I make my datafiles within ASM?

Use uniform sized files (say 2 or 4 GB each). Reason being that the a 2TB datafile will unnecessarily extend backup and recovery times.

ASMCMD is very slow. How can I speed it up?

The asmcmd utility appears to be very slow. This slowness is a result of queries against the v$asm_diskgroup view. To solve this problem edit the $ORACLE_HOME/bin/asmcmdcore script and change all v$asm_diskgroup references to v$asm_diskgroup_stat.

V$asm_diskgroup and v$asm_diskgroup_stat provides exactly the same information, but the %_stat view operates from cache, while v$asm_diskgroup rescans all disk headers. This method is also used by Oracle in their Enterprise Manager product.

ASM disk header/superblock backups?

ASM disk headers (superblocks) cannot be backed up and restored in Oracle 10g. By implication, if you use EXTERNAL REDUNDANCY and a single disk's header is accidentally overwritten, the entire disk group will have to be restored. To solve this problem, Oracle introduced the md_backup and md_restore asmcmd commands in Oracle 11g. In Oracle 10g, the only viable method to prevent logical corruption of ASM header block is to add failgroup, storage vendor has no responsibility to verify/checksum ASM disk header blocks (EXTERNAL REDUNDANCY is not going to help). There is a kfed utility to backup ASM disk headers and restore them for LIMITED scenario. It is best to be executed under guidance of a few elite support engineers. Oracle did not advertise the utility due to the potential damage it could cause. For those unrecoverable (tedious manual fixes) cases, restoring disk group is the last resort.

How does one create a database directly on ASM?

The trick is to create an SPFILE and restart the instance before issuing the CREATE DATABASE statement:

Code:

STARTUP NOMOUNT PFILE=initorcl_0.ora

CREATE SPFILE FROM pfile='initorcl_0.ora';

SHUTDOWN IMMEDIATE

STARTUP NOMOUNT

Point all OMF files into ASM:

Code:

ALTER SYSTEM SET db_create_file_dest = '+DATA';

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 134G;

ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER';

Issue the create database command:

Code:

CREATE DATABASE orcl

   UNDO TABLESPACE undots

   DEFAULT TEMPORARY TABLESPACE temp

   character set "WE8ISO8859P1"

   national character set "AL16UTF16";

Oracle Advanced Replication Interview Questions and Answers

What is replication and why should one use it?

Replication is the process of creating and maintaining replica versions of database objects (e.g. tables) in a distributed database system.

Replication can improve performance and increase availability of applications because alternate data access options becomes available. For example, users can access a local database rather than a remote server to minimize network traffic. Furthermore, the application can continue to function if parts of the distributed database are down as replicas of the data might still accessible.

Can one replicate between Oracle and non-Oracle databases?

Oracle does not support replication to non-Oracle databases like DB2, Informix, Sybase, SQL Server, etc. Some third-party vendors provide products that can replicate heterogeneous databases. One such company is Sybase another would be GoldenGate Software, which was recently purchased by Oracle. Contact these companies for more information about their respective product offering

What is the ref cursor in Oracle?

REF_CURSOR allows returning a recordset/cursor from a Stored procedure.

It is of 2 types:

Strong REF_CURSOR: Returning columns with datatype and length need to be known at compile time.

Weak REF_CURSOR: Structured does not need to be known at compile time.

Syntax till Oracle 9i

create or replace package REFCURSOR_PKG as

TYPE WEAK8i_REF_CURSOR IS REF CURSOR;

TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;

end REFCURSOR_PKG;

Procedure returning the REF_CURSOR:

create or replace procedure test( p_deptno IN number , p_cursor OUT 

REFCURSOR_PKG.WEAK8i_REF_CURSOR)

is

begin

open p_cursor FOR 

select *

from emp

where deptno = p_deptno;

end test;

Since Oracle 9i we can use SYS_REFCURSOR

create or replace procedure test( p_deptno IN number,p_cursor OUT SYS_REFCURSOR)

is

begin

open p_cursor FOR 

select *

from emp

where deptno = p_deptno;

end test;

For Strong

create or replace procedure test( p_deptno IN number,p_cursor OUT REFCURSOR_PKG.STRONG 

REF_CURSOR)

is

begin

open p_cursor FOR 

select *

from emp

where deptno = p_deptno;

end test;

What are the drawbacks of a cursor?

Cursors allow row by row processing of recordset. For every row, a network roundtrip is made unlike in a Select query where there is just one network roundtrip. Cursors need more I/O and temp storage resources, thus it is slower.

Oracle - What are the drawbacks of a cursor? 

What are the drawbacks of a cursor?  

• Implicit cursors are less efficient than explicit cursors
• Implicit cursors are more vulnerable to data errors
• Implicit cursors provide less programmatic control

What is a cursor variable?

In case of a cursor, Oracle opens an anonymous work area that stores processing information. This area can be accessed by cursor variable which points to this area. One must define a REF CURSOR type, and then declare cursor variables of that type to do so.

E.g.:

/* Create the cursor type. */

TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;

/* Declare a cursor variable of that type. */

company_curvar company_curtype;

What is a package cursor?

A Package that returns a Cursor type is a package cursor.

Eg:

Create or replace package pkg_Util is

        cursor c_emp is select * from employee;

        r_emp c_emp%ROWTYPE;

end;

Explain how to lock and unlock a user account in Oracle.  

SQL> ALTER USER user_name ACCOUNT LOCK;

SQL> ALTER USER user_name ACCOUNT UNLOCK;

What are background processes in oracle? 

 

Oracle uses background process to increase performance.

Database writer, DBWn

Log Writer, LGWR

Checkpoint, CKPT

System Monitor, SMON

Process Monitor, PMON

Archiver, ARCn

What is SQL*Loader?

SQL*Loader is a loader utility used for moving data from external files into the Oracle database in bulk. It is used for high performance data loads.

What is SQL*Loader?  

It is a database tool that allows data to be loaded from external files into database tables. 

It is available as part of the free Oracle 10g Expression Edition.

Features:

• Capability of loading data from multiple data files into multiple tables in a single session.
• Specification of the character set of data.
• Sophisticated error report generation.
• Capabilities of loading data directly to Oracle datafiles using the normal insert process.

What is SQL loader? What are the files used by SQL Loader? Explain the method of loading data.

Answer

SQL Loader loads data from external files into tables in oracle. SQL loader uses following files

Method for loading data

Connectional path load

Direct Path load 

What is a SQL*Loader Control File?

A SQL*Loader control file contains the following specification:

• Location of the input data file.
• The format of the input date file.
• The target table where the data should be loaded.
• The way input data fields should be mapped to target table columns.
• Select criteria to select input records for loading.
• Location where the errors should be reported.  .

Explain oracle memory structures.  

Two memory area.

System global area(SGA)

Program Global Area(PGA)

SGA consist memory structure such as 

Shared Pool

Database buffer cache

Redo log buffer

large Pool

Java Pool

What is a shared pool?

Shared pool in oracle contains cache information that collects, parses, interprets and executes SQL statements that goes against database. This shared pool acts like a buffer for these SQL statements.

Explain how to list all indexes in your schema.  

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';

What are the purposes of Import and Export utilities?

Export and Import are the utilities provided by oracle in order to write data in a binary format from the db to OS files and to read them back.

These utilities are used:

• To take backup/dump of data in OS files.
• Restore the data from the binary files back to the database.
• move data from one owner to another

What are data pump Export and Import Modes?

It is used for fast and bulk data movement within oracle databases. Data Pump utility is faster than the original import & export utilities.

What are data pump Export and Import Modes?  

Data pump export and import modes are used to determine the type and portions of database to be exported and imported:

Mode	Mode Specification Parameter	Description
Full	FULL	Exports and imports a full database.
Schema	SCHEMA	Enables you to export and import all objects that belong to a schema
Table	TABLES	Enables you to export and import specific tables and partitions
Tablespace	TABLESPACES	Enables a privileged user to move a set of tablespaces from one Oracle database to another
Tablespace	TRANSPORT_TABLESPACES	Enables a privileged user to move metadata from the tables within a specified set of tablespaces from one Oracle database to another

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE: It returns the error number for the last encountered error. 

SQLERRM: It returns the actual error message of the last encountered error.

How exceptions are raised in oracle?

Oracle exceptions are raised internally and user need not explicitly raise them. For example, when a number is attempted to be divided by ZERO, ZERO_DIVIDE exception is raised.

User defined exceptions need to be raised explicitly using RAISE command.

Example:

DECLARE

         total_stock NUMBER := 100;

         cust_sales NUMBER;

         sales_exceeded EXCEPTION;

BEGIN

         SELECT SUM (sales) INTO cust_sales

                FROM invoice WHERE customer_id = 1001;

         IF cust_sales > total_stock

         THEN

                RAISE sales_exceeded;

         END IF;

EXCEPTION

                 WHEN sales_exceeded

          THEN

                 DBMS_OUTPUT.PUT_LINE

                       (' Customer sales exceeded the stock quantity’);

END;

What is SQL*Plus?  

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:

• SQL
• PL/SQL
• SQL*Plus
• 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.

Explain how to change SQL*Plus system settings.  

The SET command can be used to change the settings in the SQl*PLUS environment.

SET AUTOCOMMIT OFF: Turns off the auto-commit feature

SET FEEDBACK OFF: Stops displaying the "27 rows selected." message at the end of the query output

SET HEADING OFF: Stops displaying the header line of the query output

SET LINESIZE 256: Sets the number of characters per line when displaying the query output

SET NEWPAGE 2: Sets 2 blank lines to be displayed on each page of the query output

SET NEWPAGE NONE: Sets for no blank lines to be displayed on each page of the query output

SET NULL 'null': Asks SQL*Plus to display 'null' for columns that have null values in the query output

SET PAGESIZE 60: Sets the number of lines per page when displaying the query output

SET TIMING ON: Asks SQL*Plus to display the command execution timing data

SET WRAP OFF: Turns off the wrapping feature when displaying query output.

What is Data Block?  

Data block is the optimum level of storage. Also known as pages, each data block corresponds to a specific number of bytes. Adjacent data blocks form an extent.

What are the uses of Rollback Segment?  

• Rollback segments undo changes when a transaction is rolled back
• They also ensure that transactions leave the uncommitted changes unnoticed.
• They can be used to recover the database to a consistent state in case of failures.
• 
  

What is clusters?

Clusters in Oracle contain data of multiple tables that have the same one or more columns. All the rows from all the tables that share the same cluster key are stored together.

Example:

Create a cluster name employee with size 512 bytes 

CREATE CLUSTER employee

          (department NUMBER(4))

SIZE 512 

STORAGE (initial 100K next 50K);

Create a cluster key:

CREATE INDEX id_employee ON CLUSTER employee;

After this tables can be added to the index.

How many types of Tables supported by Oracle? Explain them  

Oracle supports 4 types of tables based on how data is organized in storage:

Ordinary (heap-organized) table

• A basic, general purpose table
• Data is stored as an unordered collection (heap)

Clustered table

• A part of a cluster
• Cluster: A cluster is a group of tables that share the same data blocks as they share common columns and are often used together.

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.

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

Advantage:

The structure obtained from partitioning is better tuned for availability and performance

Explain how to recover a dropped Table in Oracle.

An oracle table that is accidentally dropped can be recovered using FLASHBACK command. When a table is dropped it stays in the recycle bin of Oracle until it is explicitly PURGED. Such tables can be recovered.

Example:

FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;

Define primary key and foreign key.  

A column or combination of columns that identify a row of data in a table is Primary Key. A key in a table that identifies records in other table in called a foreign key.

What is difference between UNIQUE and PRIMARY KEY constraints?

Answer  

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. Primary Key doesn't allow NULL value whereas UNIQUE key allows NULL value.

Explain how to DISABLE and ENABLE constraint.  

Constraints in Oracle like Foreign keys, CHECK constraint are used to maintain data integrity. Oracle by default enables a constraint if not enabled or disabled explicitly.

Enabling a constraint:

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

Disabling a constraint:

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

What is the difference between BASIC and ADVANCED replication?

Oracle Server supports two different forms of replication: basic and advanced replication.

Basic replication is implemented using standard CREATE SNAPSHOT or CREATE MATERIALIZED VIEW statements. It can only replicate data (not procedures, indexes, etc), replication is always one-way, and snapshot copies are read only.

Advanced replication supports various configurations of updateble-snapshot, multi-master and update anywhere replication. It is more difficult to configure but allows data and other database objects like indexes and procedures to be replicated.

What is the difference between a snapshot and a materialized view?

They are the same. Keywords "snapshot" and "materialized views" can be used interchangeably. Oracle 8i implements snapshots and materialized views as a single table, previous releases implemented it as a view with an underlying SNAP$_% table.

How does one implement basic snapshot replication?

Start by creating an optional snapshot log on the master database. If you do not want to do fast refreshes, you do not need to create a log. Also note that fast refreshes are not supported for complex queries. Create a snapshot/materialized view on the snapshot site. Look at this example:

MASTER SITE:

Code:

SQL> create materialized view log on EMP;

SNAPSHOT SITE:

Code:

SQL> create materialized view emp

              refresh fast with primary key

              start with sysdate

              next  sysdate + 1/(24*60)

              as (select * from emp);

What object types can and cannot be replicated?

The following can be replicated:

* Data (obviously)

* Triggers, views, indexes, synonyms, etc. (with advanced replication)

The following cannot be replicated:

* Sequences (Sequences needs to be created on each site to generate mutually exclusive sets of sequence numbers).

* LONG and LONG RAW data types (Use LOBs instead)

What is the difference between master definition and master destination sites?

If you are planning to implement Master-to-Master Replication, you need to decide which of your Oracle Database servers will become the Master Definition site. The remainder of your servers will become Master Destination sites. Replication support is configured from the Master Definition Site.

Note: You must have 1 master definition site for each replication group.

What questions should one ask before implementing Advanced Replication?

Users tend to think that Advanced Replication can be implemented on any application without having to make application changes. However, not all applications can operate with advanced replication enabled. The list of questions below will help you to establish if advanced replication can be implemented on your application:

* Was the application ever tested with replication enabled? If so, please provide detailed documentation/ installation procedures.

* Does all the tables have primary keys defined? If not, will you be able to identify the surrogate primary keys.

* Does the application generate unique sequences across both sites. If not, this will cause data conflicts.

* At what frequency should the data be replicated.

* Give an indication of the data change rate (network overhead between the sites).

* What database schemas and/or tables should be replicated?

* What will happen if transactions can not be replicated? For example: if the network goes down.

* How will replication conflicts be handled? Will you be able to assist us by writing conflict handlers?

How does one set up multi-master replication?

Steps for setting up multi-master replication:

* Run ?/rdbms/admin/catrep.sql as user SYS AS SYSDBA (or INTERNAL).

* The SID for each database must be unique.

* INIT.ORA parameters JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL need to be set if you want to schedule replication automatically.

* GLOBAL_NAMES must be set to TRUE if you are using the Oracle Replication Manager GUI.

* One can use the OEM Replication Manager to configure replication or by issuing PL/SQL API calls. Sample scripts are available here.

What is a surrogate repadmin user and how is one created?

If one creates a surrogate replication administrator at a remote site, one does not need to have SYS to SYS database links between the sites. The surrogate replication administrator performs actions on behalf of the symmetric replication facility at the remote site.

The surrogate user is thus an optional replacement user for SYS. To make an existing user a surrogate, execute the following API call:

Code:

SQL> EXECUTE DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT(userid => 'surrogate_user_name');

How does one monitor replication?

Look for the following:

* Broken jobs (select job, broken from sys.dba_jobs)

* Check sys.dba_repcatlog for outstanding admin requests

* Check for replication errors (select * from sys.deferrcount/ deferror)

* Etc.

The DBA_REPCATLOG view is not getting empty, what can one do?

The SYS.DBA_REPCATLOG view list pending admin requests. One needs to wait until all admin requests are completed before continuing to configure replication support. Some reasons why this view is not getting empty, or taking a long time to get empty is as follows:

* JOB_QUEUE_INTERVAL not set or set to a too large value

* Push Job not defined or is broken

* Push job's run interval is too large

* DB Links are down

Manually running the push job will speed up the execution of deferred admin requests. Query the user_jobs view to get the job number, and do a EXEC DBMS_JOB.RUN(jobno);.

What happens if two or more sites change the same data?

If two sites change the same data within the data replication interval, you have an update conflict. The more frequently you propagate your changes (that is, the smaller your replication interval), the fewer update conflicts will occur. Oracle detects update conflicts by comparing the old values for a row from the remote (or propagating) site with the current values at the local (or receiving) site. If they are the same, no conflict has occurred and the new values are applied at the local site. If they are different, a conflict is detected.

Oracle attempts to resolve the conflict by using the conflict resolution method that you specified when you first replicated the table. You can choose from among several Oracle-supplied conflict resolution methods (such as, latest timestamp, site priority, additive, etc.) or you can write your own. If you do not supply a method, or if the method that you supply cannot successfully resolve the conflict, Oracle logs the conflicting transaction in an error queue at the local site. The replication administrator can then decide how best to resolve the conflict.

[edit] What happens if one of the sites is unavailable?

In an asynchronous (store-and-forward) replication environment, local updates are stored in a deferred transaction queue until the remote site becomes available. When the remote site comes back up, these transactions are propagated and applied at the remote site in the order that they were originally applied at the local site. You can continue to make updates at any remaining replication sites.

In addition to replicating the objects that you specify as part of your replication group, Oracle also replicates the replication catalog to each site. The replication catalog is a set of tables that determine which objects are being replicated, where they are being replicated, how often their changes are propagated, and so on. Replicating the replication catalog ensures that there is no single point of failure in a replicated environment.

How does one change the definition of a replicated table?

Oracle advanced replication will replicate DML changes (inserts, updates, and deletes) and all DDL (create, alter, delete) changes made to all master sites for a replication group. So, if you decide to add a column to a replicated table, this column will be added at all of your master sites. Additionally, you can change the members of a replication group, for example, if you decide to add an index to a table, you can choose to have this index replicated to all of your master sites.

How does one resolve replication conflicts?

Use the following supplied packages:

* DBMS_REPCAT.ADD_UPDATE_RESOLUTION - NO_DATA_FOUND

* DBMS_REPCAT.ADD_DELETE_RESOLUTION - DUP_VAL_ON_INDEX

* DBMS_REPCAT.ADD_UNIQUE_RESOLUTION - TOO_MANY_ROWS

* Add user defined conflict resolution

How does one relocate the master definition site to a different location?

Use DBMS_REPCAT.RELOCATE_MASTERDEF. Example:

Code:

execute DBMS_REPCAT.RELOCATE_MASTERDEF( -

        GNAME                 => 'BANKING',

        OLD_MASTERDEF         => 'NYBANK.WORLD',

        NEW_MASTERDEF         => 'HKBANK.WORLD',

        NOTIFY_MASTERS        => TRUE,

        INCLUDE_OLD_MASTERDEF => TRUE);

Note: There are 12 conflict resolution methods. None of them will be enabled by default.

Can sequences be replicated?

No, the best way to handle sequences, assuming you are using them as primary key values, is to concatenate then with something unique to the site. For example, use a sequence number concatenated with the database name, site name or something similar. One could also start the sequences at one site as odd numbers (1, 3, 5, etc) and the other site as even numbers (2, 4, 6 etc).

Can one turn off replication for the current session?

One can switch off replication for a session. This is handy when one needs to fix replication problems.

Syntax:

Code:

SQL> exec DBMS_REPUTIL.REPLICATION_OFF;

... do bulk insert/update/delete ...

SQL> exec DBMS_REPUTIL.REPLICATION_ON;

Look at this example:

Code:

SQL> exec DBMS_REPUTIL.REPLICATION_OFF;

SQL> insert into tabX select * from tabX@remote MINUS select * from tabX;

SQL> commit;

SQL> exec DBMS_REPUTIL.REPLICATION_ON;

How does one replicate a TRUNCATE TABLE statement?

TRUNCATE is a DDL statement, and should thus not be issued directly against tables belonging to a replication group. Instead, use the supplied DBMS_REPCAT.EXECUTE_DDL procedure. Here is an example:

Code:

BEGIN

dbms_repcat.execute_ddl(

  gname       => 'group1', -- Query dba_repobject for the gname

  master_list =>  NULL,    -- NULL = all masters

  ddl_text    => 'TRUNCATE TABLE scott.emp');

END;

/

You don't need to quiesce the replication group before runing the above operation.

How does one delete all local def errors?

Before one indiscriminately deletes all errors, understand why they occurred first. It might be better to re-apply all failed transactions. If you still wants to delete all of them, execute the following statements from SQL*Plus:

Code:

SQL> connect repadmin/repadmin

SQL> execute dbms_defer_sys.delete_error(NULL,NULL);

SQL> commit;  

Also look at this slightly more complicated example. This example will first try to re-apply the error before deleting it:

SQL> spool apply_errors.sql

SQL> select 'exec dbms_defer_sys.execute_error( || deferred_tran_id ||,||

SQL>        destination || )'

SQL> from   deferror;

SQL> spool off

SQL> @apply_errors

SQL> commit;

SQL> spool delete_errors.sql

SQL> select 'exec dbms_defer_sys.delete_error( || deferred_tran_id ||,||

SQL>        destination || )'

SQL> from   deferror;

SQL> spool off

SQL> @delete_errors

SQL> commit;

Can one avoid a loop when remote tables are updating each other via triggers?

Triggers can easily cause replication errors and even infinite loops in a replicated environment. This happens as the trigger fire more than once on the same data. For example, consider a trigger that inserts a row into a table. When this row is replicated to a remote database(s), the trigger will fire again on the same data, and re-insert a duplicate row into the table.

This problem can be solved by using the DBMS_REPUTIL.FROM_REMOTE function. Look at this example:

Code:

IF dbms_reputil.from_remote = true THEN  -- Trigger was already fired on a remote DB

   return;                               -- Exit the trigger

END IF;

Note: Ensure triggers don't fire during replication by testing DBMS_SNAPSHOT.I_AM_A_REFRESH before executing a trigger body.

I get "NO DATA FOUND" errors. How does one handle this?

An ORA-1403 (No Data Found) error is signalled within a replicated environment when there is a conflict in data. Here is an illustration of what happens in most scenarios featuring this error:

Let's say we need to update a table and set column x=1 where x=9. When the first change occurs on the source database, Oracle checks to ensure that x=9. Once this transaction commits, it is queued for execution on the destination site(s). When the transaction is applied to the destinations, Oracle first check to verify that x=9 (the old value); if it does, then Oracle applies the update (update table set x = 1 where x = 9); if it does not, an entry to the DEFERROR table is logged indicating ORA-1403.

If the application is updating primary key values, one can also expect plenty "NO DATA FOUND" errors. Primary keys should NEVER be updated. If one updates primary key values, conflict resolution also becomes extremely difficult.

How does one fix ORA-25207 errors?

Problem:

ORA-25207: enqueue failed, queue SYSTEM.DEF$_AQCALL is disabled from enqueueing

Solution:

Code:

CONNECT / AS SYSDBA

EXEC DBMS_AQADM.START_QUEUE('SYSTEM.DEF$_AQERROR', TRUE, TRUE);

EXEC DBMS_AQADM.START_QUEUE('SYSTEM.DEF$_AQCALL', TRUE, TRUE);

Oracle Reports Interview Questions and Answers

I switched the page size to 11x8.5, but the printer still prints in portrait

Even though you set the page size in the report properties, there is a another variable in the system parameters section under the data model in the object navigator called orientation. This sets the printer orientation. Oracle starts by setting it to "default" which means that no matter how you set the page size, the user's default printer setup will be used. You can also set it to either "Landscape" or "Portrait" to force the printer orientation no matter what the user has set as default. These sorts of picky, minor details are the ones which are invariably forgotten when you are designing your report and are the reason I created our two report templates, reptmp_p and reptmp_l (portrait and landscape). For anyone who wants a consistent look in their reports I strongly recommend building a similar pair to save yourself an ulcer, unless you actually like starting from scratch every time!?!

I've moved field into a repeating frame, but still get a "frequency below it's group" error

Moving fields around does not change what enclosing object is considered it's parent group. Oracle carefully remembers what repeating frame a field was originally placed in and assigns that as it's parent. If you then reference a column further down the line of the query structure it will return that error. If you are not exactly sure which repeating frame a field belongs to, try dragging it out of all of them. Whichever frame will not allow it to escape is it's parent. To change a field's parent, first click on the lock button on the speedbutton bar. It should now look like an unlocked padlock. Now all of the fields on the layout can be repositioned regardless of their original parent items. When you are satisfied with the repositioning click the lock button again to lock the layout. Oracle will parse the layout and assumes that any item fully enclosed in a repeating frame is a child object of that frame. This can be confirmed again by trying to drag an object out of it's parent. (Cntrl - Z or edit..undo will put it back where it came from)

Sometimes, for unknown and mysterious reasons, this method does not work. The alternative in this case is to highlight the field (or fields), cut it (cntrl-x), and then paste it into the desired frame. The paste does not initially set it into the right frame, but if you drag and drop it there before clicking on any other objects, and then click on something else, Oracle will usually figure what your intent was and assign the object(s) as a child of that frame. This is my preferred method of changing a field's parent as it works much more consistently then the unlock/lock method. One note though, if you are reassigning a group of fields, make sure the frame you are going to move them into is large enough to accept the whole group at once before you do the cut/paste. If you do the paste and then try to grow the frame to fit, you will have to cut and paste again. Once you de-select an object that has just been pasted, Oracle will assign it as a child of whatever it is in at the time.

If this technique also fails, you are probably going to have to delete and then recreate the objects within the desired frame. If the object has triggers attached, save yourself some typing by creating the new object in the right frame, copying over the trigger code, and then deleting the old object.

Why does part of a row sometimes get shifted to the next page, but not all of it?

This is due to the way the scan works when Oracle is parsing the layout. If the tops of all the fields in a row are aligned and the fields are all of the same height and font, they should all stay together. I suspect, however, that Reports bases it's decision on the printed size rather than the field size you define to determine which objects are too large and must be shifted to the next page. This means that even if you set two fields top-aligned with the same height and font but one of them is bolded, the bolded field could get shifted to the next page due to it's bigger footprint. The solution is to put the whole row into a regular frame which is page protected.

What does the "Print Condition" do?

The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.

As an example, assume we have created a field inside a repeating frame with Print Condition Object set to 'anchoring object', and Print Condition Type set to 'All But First'. On every instance of that repeating frame which is printed entirely within a single page, our object will not print. However, if an instance of that frame spans more than one page then our object will print on the second and every subsequent page that this instance of the repeating frame spans.

For most objects you will not have to play with this print condition setting as the default setting is pretty good at determining what pages to print on, even though it only chooses between 'first' and 'last'. Only such things as heading objects you want reprinted on multiple pages are normally candidates for fooling around with this setting.

Create dynamic 'where' which the user can input on the parameter form for my select statement

While setting a simple parameter for use in defining the select statement, such as a date, bill_period_id etc. is simple, there are times when you may wish to allow a user to add any "where" statement they wish. However, if you create a varchar user variable and try to reference it as an SQL condition ( e.g. Select * from account where :usercondition) you will get an error. The secret is that the variable must be initialized to a valid SQL condition before the Data Model will accept it. This is done in the "Initial Value" spot on the variables' properties form. The usual default is "1 = 1" which simply means all rows meeting whatever other conditions are included in the select statement will pass this condition if the user does not change it in the parameter form.

How do I change a user parameter at runtime from a layout object trigger?

Quite simply, you can't. Once the BeforeReport trigger has fired, Reports locks down the user parameters until the report is finished. Oh, I know you can put a statement into a layout trigger at design time and the compiler will accept it, but the moment you run the report you will get a nasty error and the report will die. Why they couldn't catch those problems at compile time I have no idea, except that it probably uses the same PL/SQL compiler as Forms which uses that same syntax for the perfectly acceptable function of changing field values.

That being said, there is valid technique to mimic having a user variable which can be changed over the course of the report execution. What you have to do is create a PL/SQL package that contains a variable as well as the functions to read and write to that variable. Since variables inside a package are both local to that package and persistent over the duration of the run, you use this to save and change your variable value. I know that this seems like overkill, but it is the most efficient way of handling an issue that is very rarely encountered. As you can probably guess, this technique is a last resort to finding an SQL work around if one exists.

How do I set the initial values of parameters for the parameter form at runtime?

This is what the BeforeForm trigger is primarily used for. Even if you have used a select statement to create a lookup list for the parameter, this statement is fully parsed before the parameter form is opened. Simply setting the parameter to a given value in the BeforeForm trigger will select that option as the default value displayed to the user. For example, assume you have a parameter called p_input_date which is intended to hold an invoice date. The following example will select the most recent invoice date as the default, and note that it properly handles exceptions to ensure that the report does not arbitrarily die if this default setting fails. Note also that like all report triggers, it must return a true or false value.

function BeforePForm return boolean is

Code:

begin

select max(bill_period_end_date + 1)

  into :p_input_date

  from billing_period

where bill_period_end_date <= (select trunc(sysdate)

                                  from dual);

  return (TRUE);

exception

  when others then

     :p_input_date := null;

     return true;

end;

Why can't I highlight fields and change all their format masks or print conditions at once?

You can. If you highlight a bunch of objects and then right click and select "properties..", Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some things, it requires changing values individually for each object. However, instead you can select the group of fields and then select "Common properties" from the "Tools" menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.

How do I change the printed value of a field at runtime?

Triggers are intended to simply provide a true or false return value to determine whether an object should be printed. It is generally not allowed to change any values held in the cursor, make changes to the database, or change the value of it's objects value. That being said, there is a highly unpublicized method of doing just that using the SRW.Set_Field_Char procedure. The syntax is SRW.Set_Field_char(0,) and the output of the object that the current trigger is attached to will be replaced by . There are also SRW.set_fileld_num, and SRW.set_field_date for numeric or date fields.

While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so can not be used for evaluating summary totals.

Oracle Performance Tuning Interview Questions

What are the major focuses of Performance tuning?

Performance tuning focuses primarily on writing efficient SQL, allocating appropriate computing resources, and analyzing wait events and contention in a system.

How does Oracle aid performance tuning?

Oracle provides several options to aid performance tuning, such as partitoning lar tables, using materialized views, storing plan outlines, using tools like Automatic Optimizer statistics collection feature, ckages like DBMS_STATS, SQL Tuning Advisor to tune SQL statements,etc.

Why is performance tuning a menancing area for DBA's?

Like many other features of Oracle like exp/imp,backu recovery this field can't be automated. This is one area that requires a lot of detective work on the part of application programmers and DBA's to see w some process is running slower than expected, why can't we scale applications to a larger number of users without problems like performance degradation etc.This is a area where our technical knowledge must be used along with constant experimentation and observation.

What are the approaches towards performance tuning?

We can follow either a systematic approach or a reactive approach for performance tuning.

What is a systematic approach to performance tuning?

It is mandatory to design the database properly at initial stages to avoid potential problems. It is mandatory to know the nature of application that a database is going to support. With a clear idea on the application's nature database can be optimally created by allocating appropriate resources to avoid problems when the application is moved to production. Most production moves cause problem because of the scalability problems with the applications.So, oracle recommends to tune database at inception stage. this is systematic approach to performance tuning.

What are the Oracle's suggestions towards systematic tuning?

Oracle suggests a specific design approach with the following steps.This is a top down approach:

1) Design the application correctly

2) Tune the application SQL code

3) Tune memory

4) Tune I/O

5) Tune contention and other issues

What are the effects of poor database design?

A poor database design results in poor application performance. We have to tune the application code and some database resources such as memory,CPU,I/O owing to performance degradation. An applications performs well in development and testing.

Will there be any performance problem when it is moved to production?

Production moves may cause problems due to scalability.We can't simulate the original load in test and development. So problems may crop up at times as the application may be performing poor due to scalability problems.

What is reactive performace tuning?

Performance tuning is an iterative process. We as a DBA may have to tune applications which is designed and implemented in production.The performance tuning at htis stage is referred to as reactive performance tuning.

Which is useful - systematic or reactive tuning?

The performance tuning steps to improve the performance of a database depends on the stage at which we get the input and on the nature of the application. DBA's can assist the developers to write optimal code that is scalable based on systematic approach. Mostly the real life problems that are encountered after production moves have to be solved by reactive performance tuning.

We have an application whose code can't be changed.Can we improve its performance?

We can improve the application performance without changing base SQL code by optimizing the SQL performance. Oracle has come up with SQL Advisor tool that helps SQL performance. We can make use of SQL Advisor tools' SQL Profiles to improve performance,though we can't touch the underlying SQL.

What is the use of SQL over procedural languages?

SQL isn't a procedural language in which we have to specify the steps to be followed to achieve the statement goal.We don't have to specify how to accomplish a task(say data retrival) using SQL,rather we can specify as to what needs to be done.

What is query processing?

When a user starts a data retrieval operation, the user's SQL statement goes through several sequential steps that together constitute query processing.Query processing is the transformation of the SQL statement into efficient execution plan to return the requested data from the database.

What is query optimization?

Query optimization is the process of choosing the most efficient execution plan.The goal is to achieve the result with least cost in terms of resource usage.Resources include I/O and CPU usage on the server where the dat

database is running.This is a means to reduce the execution times of the query,which is the sum of the execution times of the all component operations of the query.

What are the techniques used for query optimization?

Cost-based optimization, heuristic strategy are used for query optimization.

What are the phases of a SQL statement processing?

An user's SQL statement goes through the parsing,optimizing, and execution stages.If the SQL statement is a query(SELECT),data has to be retrived so there's an additional fetch stage before

the SQL processing is complete.

What is Parsing?

Parsing primarily consists of checking the syntax and semantics of the SQL statements. The end product of the parse stage of query compilation is the creation of a parse tree,which represents the query structure.The parse tree is then sent to the logical query plan generation stage.

Mention the steps in the creation of a parse tree:-

1) The SQL statement is decomposed into relational algebra query that 's analyzed to see whether it's syntactically correct.

2) The query then undergoes semantic checking.

3) The data dictionary is consulted to ensure that the tables and the individual columns that are referenced in the query do exist,as well as all the object privileges.

4) The column types are checked to ensure that the data matches the column definition.

5) The statement is normalized so that it can be processed more efficiently

6) The query is rejected if it is incorrectly formulated

7) Once the parse tree passes all the syntactic and semantic checks,it is considered a valid parse tree,and it's sent to the logical query plan generation stage.

Where does the parse tree generation take place?

The parse tree generation takes place in the library cahce portion of the SGA(system global Area).

What is Optimization/what happens during optimization phase?

During the optimization phase,Oracle uses its optimizer(CBO(cost-based optimizer)) to choose the best access method for retriving data for the tables and indexes referred to in the query.

How does a CBO generate an optimal execution plan for the SQL statement?

Using the statistics we provide and the hints specified in the SQL queries, the CBO produces an optimal execution plan for the SQL statement.

What are the parts of an optimizer phase?

An optimizer phase can be divided into two distinct parts: the query rewrite phase and the physical execution plan generation phase.

What is query rewrite phase?

In this phase ,the parse tree is converted into an abstract logical query plan. This is an initial pass at an actual query plan, and it contains only a general algebraic reformulation of the initial query. The various nodes and branches of the parse tree are replaced by operators of relational algebra.

What is execution plan generation phase/physical execution plan execution plan generation phase?

During this phase,Oracle transforms the logical query plan into a physical query plan.

The optimizer may be faced with a choice of several algorithms to solve a query. It needs to choose the most efficient algorithm to answer a query,and it needs to determine the most efficient way to implement the operations.The optimizer determines the order in which it will perform the steps.

What are the factors considered by a physical query/execution plan?

Following factors are considered by a physical query or an execution plan:

1) The various operations(eg:joins) to be performed during the query

2) The order in which the operations are performed

3) The algorithm to be used for performing each operation

4) The best way to retrieve data from disk or memory

5) The best way to pass data from one operation to another during the query

Which generates the query plan/what is generated by optimizer?

The optimizer generates several valid physical query plans.All the physical query plans are potential execution plans.

How does the optimizer choose the query plan/what is cost-based query optimization?

The optimizer generates several physical query plans that are potential execution plans. The optimizer then chooses among them by estimating the cost of each possible physical plan based on the table and index statistics available to it,and selecting the plan with the lowest estimated cost. This evaluation of the possible physical query plans is called cost-based query optimization.

What are the factors affecting the cost of a execution plan?

The cost of executing a plan is directly proportional to the amount of resources such as I/O,memory and CPU necessary to execute the proposed plan.

What happens after choosing the low-cost physical query plan?

The optimizer passes the low-cost physical query plan to the Oracle's query execution engine.

What is a heuristic strategy?

The database uses a less systematic query optimiation technique known as the heuristic strategy.

What are unary and binary operations?

A join operation is called a binary operation, an operation like selection is called a unary operation.

What is an optimal operation processing strategy?

In general an optimal strategy is to perform unary operations first so the more complex and time-consuming binary operations use smaller operands. Performing as many of the possible unary operations first reduces the row sources of the join operations.

What are the heuristic-processing strategies?

1) Perform selection operation early so that we can eliminate a majority of the candidate rows early in the operation. If we leave most rows in until the end, we're going to do needless comparisons with the rows we're going to get rid of later

2) Perform projection operations early so that we limit the number of columns we have to deal with

3) If we need to perform consecutive join operation,perform the operations that produce the smaller join first

4) Compute common expressions once and save the results

What is query execution?

During the final stage of a query processing, the optimized query(the physical query plan that has been selected) is executed. If it's a SELECT statement the rows are returned to the user.If it's an INSERT,UPDATE or DELETE statement ,the rows are modified. The SQL execution engine takes the execution plan provided by the optimization phase and executes it.

What is the crucial step in SQL statement processing?

Of the three steps involved in the SQL statement processing, the optimization process is the crucial one because it determines the all important question of how fast our data will be retrived.

What is the job of an optimizer?

The job of an optimizer is to find the optimal/best plan to execute our DML statements such as SELECT,INSERT,UPDATE and DELETE.Oracle uses CBO to help determine efficient methods to execute queries.

What is an index?

An index is a data structure that takes the value of one or more columns of a table(the key) and returns all rows/requested-columns in a row quickly.

Why is an index efficient?

The efficiency of an index comes from the fact that it lets us find necessary rows without having to scan all the rows of a table.They need a fewer disk I/O's than if we had to scan the table and hence are efficient.

When do we need to index tables?

We need to index tables only when the queries will be selecting a small portion of the table.If our query is retriving rows that are greater than 10 or 15 percent of the total rows in the table,we may not need an index.

Why does an index traverses a table's row faster?

Indexes prevent a full table scan,so it is inherently a faster means to traverse a table's row.

Oracle Database Resource Manager Interview Questions and Answers

What is a database resource manager?

Database resource manager allows us to create resource plans,which specify how much of our resources should go to various consumer groups.We can group users based on their resource requirement and we can have the database resource manager allocate a preset amount of resources to these groups.We can easily prioritize our users and jobs.

What are the uses of a database resource manager?

1) The database resource manager enables us to limit the length of time a user session can stay idle and to automatically terminate long-running SQL statements and user sessions.

2) Using the database resource manager we can set initial login priorities for various consumer groups

3) By using the concept of active session pool,we can specify the maximum number of concurrent active sessions for a consumer group-the Database resource manager will automatically queue all the subsequent requests until the currently running sessions complete.

Can we switch users between resource consumer groups/consumer groups?

DBA's can automatically switch users from one resource group to another ,based on preset resource usage criteria,and can limit the amount of undo space a resource group can use.

What are the four elements of a Database resource Manager?

Database resource manager is composed of the following four elements : Resource consumer group, resource plan,resource allocation method,resource plan directive.

What is a resource consumer group?

A resource consumer group is used to group together similar users based on their resource needs.

What is a database resource manager?

Database resource manager allows us to create resource plans,which specify how much of our resources should go to various consumer groups.We can group users based on their resource requirement and we can have the database resource manager allocate a preset amount of resources to these groups.We can easily prioritize our users and jobs.

What are the uses of a database resource manager?

1) The database resource manager enables us to limit the length of time a user session can stay idle and to automatically terminate long-running SQL statements and user sessions.

2) Using the database resource manager we can set initial login priorities for various consumer groups

3) By using the concept of active session pool,we can specify the maximum number of concurrent active sessions for a consumer group-the Database resource manager will automatically queue all the subsequent requests until the currently running sessions complete.

Can we switch users between resource consumer groups/consumer groups?

DBA's can automatically switch users from one resource group to another ,based on preset resource usage criteria,and can limit the amount of undo space a resource group can use.

What are the four elements of a Database resource Manager?

Database resource manager is composed of the following four elements : Resource consumer group, resource plan,resource allocation method,resource plan directive.

What is a resource consumer group?

A resource consumer group is used to group together similar users based on their resource needs.

What is a resource plan?

The resource plan lays o how resource consumer groups are allocated resources. Each resource plan contains a set of resource consumer groups that belong to this plan, together with instructions as to how resources are to be allocated among these groups.

For instance,a resource plan may dictate CPU resources be allocated among three resource consumer groups so that the first group gets 60 percent and the remaining two groups get 20 percent each of the total CPU time.

What is a subplan?

A subplan is a part of the resource plan that enables the allocation of resources in greater detailamong the resource consumer groups.

What is a resource allocation method?

The resource allocation method dictates the specific method we choose to use to allocate resources like the CPU.The available methods of allocating the database resources are CPU method, Idle time, Execution time limit,Undo pool,Active session pool,automatic consumer group switching,canelling SQL and terminating sessions,parallel degree limit.

What is a CPU method?

Oracle uses multiple levels of CPU allocation to prioritize and allocate CPU usage among the competing user sessions.It is a type of resource allocation method.

What is an idle time?

It is a type of resource allocation method.We can direct that a user's session be terminated after it has been idle for a specified period of time.We can also specify that only idle sessions blocking other sessions be terminated.

What is an execution time limit?

It is a type of resource allocation method.We can control resource usageby setting a limit on the maximum execution time of an opration.

What is an undo pool?

It is a type of resource allocation method.By setting an undo pool directive,we can limit the total amount of undos that can be generated by a consumer resource group.

What is an active session pool?

It is a type of resource allcation method. We can set a maximum allowable number of concurrent sessions within any consumer resource group.All sessions that are beyond the maximum limit are queued for execution after the freeing up of current active sessions.

What is an Automatic consumer group switching?

It is a type of resource allocation method.Using this method,we can specify that a user session be automatically switched to a different group after it runs more than a specified number of seconds.The group that the session should switch to is called as switch group,and the time limit is the switch time.The session can revert to its original consumer group after e end of the top call,whih is defined as an entire PL/SQL block or a seperate SQL statement.

What is cancelling SQL and terminating sessions?

It is a type of resource allocation method.By using CANCEL_SQL or KILL_SESSION as the switch group,we can direct a long-running SQL sement or even an entire session to be cancelled or terminated.

What is a Parallel Degree Limit?

It is a type of resource allocation method.We can use this method to specify the limit of the degree of parallelism for an operation.

What is a resource plan directive?

It is an element of database resource manager.It links a resource plan to a specific resource consumer group.

OCRCHECK Utility Interview Questions

What is an OCRCHECK utility?

An ocrcheck utility is a diagnostic tool used for diagnosing OC(Oracle Cluster Registry) Problems.This is used to verify the Oracle Cluster Registry(OCR) integrity.

What does an ocrcheck display?

The OCRCHECK utility displays the version of the OCR’s block format, total space available and used space, OCRID, and the OCR locations that we have configured.

How does ocrcheck perform integrity check?

OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that we have configured. It also returns an individual status for each file as well as a result for the overall OCR integrity check.

Give a sample output of ocrcheck utility:-

Sample of the OCRCHECK utility output:

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 262144

Used space (kbytes) : 16256

Available space (kbytes) : 245888

ID : 1918913332

Device/File Name : /dev/raw/raw1

Device/File integrity check succeeded

Device/File Name : /dev/raw/raw2

Device/File integrity check succeeded

Cluster registry integrity check succeeded

Where does an ocrcheck utility create a log file?

OCRCHECK creates a log file in the directory CRS_home/log/hostname/client.

How can we change the amount of logging?

To change amount of logging, edit the file CRS_home/srvm/admin/ocrlog.ini.

Oracle Memory Management Interview Questions

How does the System table space work?

What is the purpose of ROWS option in IMP command?

Why is Inctype option important in EXP Command?

Explain the function of Record Length optionn in exp command.

Explain the function of Tables option in exp command.

What is the function of INDEXES option in IMP command?

How does the grant option in imp command help?

How does the ignore option in imp command help?

What is the function of Analyse optioin in exp command?

How is Parfile option important to us in exp command?

What for do we have Record option in EXP Command?

Explain the function of owner option in exp command.

Explain the function of Full option in exp command

What is SHOW option used for?

For what purpose is File option used in imp comand?

What for do we use LOG option in exp command?

Why is Consistent option important in exp command?

Explain the function of Constraints option used in exp command.

Explain the function of Rows option used in EXP Command.

Explain the function of Indexes option used in exp command.

Describe the purpose of Compress Option used in Exp Command.

Describe the purpose of File option used in EXP Command.

Explain the various types of export backups.

Describe cold backup.

Various elements of cold backup.

What do you understand by logical backup?

Explain the function of Grant option used in EXP COmmand.

How do we take hot backup?

Explain the various ways of backing up oracle database.

How can we increase or decrease accuracy of estimations?

Various demerits of raw devices.

How is disk shadowing and mirroring useful to us?

Explain the process of Redo log file mirroring. How is it done?

Method for implementation of multiple control files for a database.

What do you mean by control file?

The right moment to release a segment.

What do you mean by hit ratio?

Describe a Rollback segment entry.

How is Rollback segment useful in database?

Can we have a raw device as data files?

How can we determine the space needed by a non clustered table?

Storage space required for large tables.

How are various rollback segments built in a database?

What do you mean by a data segment?

Various causes of reparsing SQL statements in SGA.

Why do we use Database buffers?

Describe the Dictionary cache.

What do you mean by Recursive hints?

How can we identify rollback segment status?

How can we find space allocation?

Why doesn’t a query succeed sometimes?

How do we

allocate space within rollback segments?

What is the process of swapping objects into another table space for a current database?

Explain OFA of Oracla database. Describe Optional Flexible Architecture.

How can we make the database use any rollback segment?

Difference between SGA in ver 6.0 and 7.0

How is the performance of estima affected.

How is a space allocation table created in a block?

Describe the benefits of PCTFREE Parameter.

What is use of System table space?

What is the functionality of Optimal parameter?

Describe free extent.

Question regarding management of free extents in ver 6.0 and 7.0

How can we decrease the number of rows per block in storage clause?

Importance of a storage clause.

Define the Program Global Area or PGA.

Features of shared SQL pool

Various Memory Management Interview Questions and Practice Test Questions for Oracle Database Administrator Job Exams.

Oracle User Management interview questions and Answers

Why is it needed to create a default tablespace for every user?

If we dont assign a default tablespace System tablespace becomes the default tablespace. If a user creates a very large object in the System tablespace,they might take up all the space in it and make it impossible for the SYS superuser to create any new objects in it,causing the database to come to a grinding halt.This is the main reason why we should always create a default tablespace for every user.

Give the statement to create a usr:-

Code:

SQL>CREATE USER (username) IDENTIFIED BY (password);

In case of Oracle database 11g use the following sattement:

Code:

SQL>CREATE USER (username) identified by "(password)";

The above query creates a user and password for the user. It is good practice to assign default temporary and permanent tablespace to the users. It is a good practice to ass them at the time of database creation.

How do we get the default tablespaces of a user?

The following query gives the default temporary and permanent tablespace for a user:

Code:

SQL>SELECT default_tablespace,temporary_tablespace from dba_users where username='(username)';

Can a user created new connect to the database?

A new user can't connect directly to database,because the user doesn't have any privileges to do so.When a user tries to connect he gets the following error at the SQL prompt:

ERROR:

Ora-01045: user lacks CREATE SESSION privilege;

logon denied

How do we rectify the bove error/ORA:01045 error?

In order for a user to connect and start communicating with the database ,he must be granted CREATE SESSION system privilege.

The following statement is used:

Code:

SQL>GRANT CREATE SESSION to (username);

How to create a user with CONNECT system privilege?

Code:

SQL>GRANT CONNECT to (username) identified by (password);

Can a newly created user create database

objects(tables,indexes,views,synonyms,sequences etc) in a database?

No,a newly created user won't be able to create database objects directly.Even if the user is assigned default temporary and permanent tablespace at the creation time,it is mandatory to allocate quota on a tablespace to users.

How do you allocate quota on a tablespace to users?

The following command is used to allocate quota on a tablespace:

Code:

SQL>ALTER USER (username) QUOTA ON (tablespacename);

What is fine-grained access control?

It is a new security mechanism from Oracle that provides low-level data security.

What is the advantage of fine-grained access control?

Traditional access control techniques like granting roles,privileges,views are broad-based that resulted in unnecessarily restricting users.As a solution Oracle came up with fine-grained access control where control on data access is at low-level.

What are the mechanisms used in fine-grained access control?

Oracle uses two related mechanisms to enforce fine-grained security within the database.They are:

1) Application context

2) Fine-grained access control Policy