Oracle Interview Questions

Ref cursor is typically used to return a record set or cursor from a stored procedure.ref cursor is basically data type ref cursor can be changed dynamically at run time normal cursor is static cursor...

REF cursor is a dynamic cursor where as normal cursor is static cursor,In dynamic cursor single segment are process multiple SELECT statements dynamically at run time, where as in normal cursor we process only one select statement

Its a one of the oracle object,its physical storage on the disk then (dbms_refresh)

Materialized view is a database object that contains the results of a query..

Using for low cardinality purpose

When create primary constraints oracle automatically creates Unique Index .
Using "Create index" by default oracle will create B tree index .
To create other indexes like Bit map,IOT index you need mention the Index name while creating index
ex "create Bitmap index "

Index will only help you in searching the data but unique index will provide uniqueness to the indexed column. But in both the cases NULL values are allowed to insert.

Lister is Network Service to establish connection between client machine and server .

Lister are stored at server side .Each lister listen to client request and establish connection.

Oracle uses all three files (tnsnames.ora, sqlnet.ora, listener.ora) for network configuration.tnsnames.ora A tnsnames.ora file maps net service names to connect descriptors. In simple words, tnsnames...

Http://orcl11gdba.blogspot.in/2014/03/taking-hot-bakcup-database-in-begin.html

check this

In HOT backup mode users transaction/data is goes in their respective default tablespaces only since the datafile header will not update til the time you end the backup mode

TRUNCATE Query

Code
TRUNCATE table table_name;

Code
SELECT * FROM (SELECT COLUMN_NAME, ROWNUM RN FROM TABLE)
WHERE MOD (RN, 2) <> 0;

These are different types of upgraded tools

TRUNC applies to numeric and Dates values Syntax- ------- TRUNC( number, [ decimal_places ] ) TRUNC ( date, [ format ] ) Example - --------- TRUNC(125.815) would retur...

We cannot compare Truncate & Trim. Truncate is a DDL command which deletes the content of a table completely, without effecting the table structure.

Trim is a function which alters the output of one of the column output of a select query.

Truncate will be faster than delete. Because truncate wont make any backup of deleted rows where in delete it will take backup of deleted records for purpose of rollback. In other words Truncate will...

1) Delete is a DML statement and it generate redo log entry. Truncate is a DDL statement and it does not generate redo log entry. 2) Truncate reset the high water mark to release space consumed by ta...

Varrays are generally dense , that mean you cannot delete elements of the varray.But the elements of the Nested Table are generally sparse in nature and hence the elements can be deleted in between us...

Both are type of collections in Oracle. In Varray because its not stored in database columns so no DML operation can be implemented but on other hand as Nested tables we can perform DML operations

Conceptual modeling is the very first level of data modeling once gathering the Business requirements. It tells about only the Entity and attributes , not the relations(constraints) or physical tables...

When we talk about Conceptual Model , so we definitely mean the High Level of information. Unlike Physical Model, Conceptual model doesn't discuss the data, but it discuss the process. as we told its ...

To avoid use:

Code
DECLARE a variable
SELECT COUNT(*) INTO num WHERE <Condition OF your data retreival FOR SELECT INTO statement>;
IF num==1 THEN
EXECUTE your SELECT INTO statement
END IF

We can use explicit cursor to avoid Oracle exception - NO_DATA_FOUND and TOO_MANY_ROWS.

Code
SELECT* FROM (SELECT RANK() 
over(ORDER BY price DESC) 
odrd,products.* FROM products) 
WHERE odrd =5;

Use Case in update statement

It retrieve or fetch all data from Emp table,and show all data of table like Emp as result.

First it will check the syntax and semantics in library cache, after that it will create execution plan. Already data in the buffer cache it will directly return to the client. If not it write the fetched to the dB buffer cache after that it will send server. Server sends to the client.

Break and compute are required, but for spooling output you should be using all the another 3 functions.

Break on is compulsory for compute function.

There will not be any impact on the output even if we will insert (and commit) the data after cursor is opened. The output will be same as of the record set when cursor was opened and new row will not be selected in that operations.

There will not be any impact on the output even if we will insert (and commit) the data after cursor is opened. The output will be same as of the record set when cursor was opened and new row will not...

This can be done by analytical function using the LEAD function.

Code
SELECT empname, salarydate, salary, (case when salary < LAG(salary, 1, 0) OVER (ORDER BY empname, salarydate) then increase else decrease end) AS trend
FROM emp;

About performance, it depends upon the computations you will be performing inside that function. Also, if we are using (user-defined) function in SQL (rather than creating PL/SQL), then it will give better performance.

While using user-defined functions, we need to avoid Mutating functions error.

Yes we can pass but the function should return a value of type appropriate for the calling function

Example

round(trunc(100))