Oracle PL/SQL interview questions

1. what is the difference between database trigger and application trigger?


2. what are the file utilit comands used in PL/SQL procedures?


3. what is a cluster and what is the real time use and business reasons to use Clustering


4. In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?


5. what is dense_rank function and it's usage ?


6. What is HIGH WATERMARK?I got to know that it is reset when the TRUNCATE command is executed on a table.


7. explian rowid,rownum?what are the psoducolumns we have?


8. 1)what is the starting "oracle error number"?2)what is meant by forward declaration in functions?


9. what is the difference between database server and data dictionary


10. Hi,Can anyone tell me the difference between instead of trigger, database trigger, and schema trigger?Thanks.




11. what is difference b/w stored procedures and application procedures,stored function and application function..


12. State the difference between implict and explict cursor's


13. what is pragma? can any one give me the example of autonomous Transaction ?can we change the order of procedure parameter while calling procedure?


14. What is the data type of Null?


15. What is autonomous Transaction? Where are they used?


16. Details about FORCE VIEW why and we can use


17. How can one view all the procedures,functions,triggers and packages created by the user


18. What is the difference between User-level, Statement-level and System-level Rollback? Can you please give me example of each?


19. What happens when DML Statement fails?A.User level rollbackB.Statement Level RollbackC.Sustem Level Rollback


20. Why Functions are used in oracle ?Can Functions Return more than 1 values?Why Procedures are used in oracle ?What are the Disadvantages of packages?What are the Global Variables in Packages?
21. Is there any limitation on no. of triggers that can be created on a table?


22. what is p-code and sourcecode ?


23. What are ref cursors ?


24. Which type of binding does PL/SQL use?


25. Talk about "Exception Handling" in PL/SQL?


26. What are the return values of functions SQLCODE and SQLERRM ?


27. What are advantages fo Stored Procedures / Extensibility,Modularity, Reusability,&
What are advantages fo Stored Procedures / Extensibility,Modularity, Reusability, Maintainability and one time compilation.
28. What are two parts of package ?


29. What is Overloading of procedures ?


30. What are the modes of parameters that can be passed to a procedure ?
31. What is difference between a PROCEDURE & FUNCTION ?


32. What is a stored procedure ?


33. Where the Pre_defined_exceptions are stored ?


34. What is Raise_application_error ?


35. What is Pragma EXECPTION_INIT ? Explain the usage ?


36. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?


37. Explain the usage of WHERE CURRENT OF clause in cursors ?


38. What is a cursor for loop ?


39. What is nested table in Oracle and and difference between table and nested table


40. What are the components of a PL/SQL block ?
41. What do you mean by OCI, Data guard and Advance queue responsibilities for a Oracle developers?


42. How do you encrypt the function to prevent accessing from users without specific permission. ?


43. HI,What is Flashback query in Oracle9i...?


44. Oracle refcursor and procedure


45. Convert SQL to Oracle Procedure using cursor


46. I want to insert the following information in userAction table:Which user execute which query on which date?the userAction table contains the foolowing attributes:USER DATE QUERYplease write to me how to resolve this problem?


47. what are purity rules for functions? why they use ? what effects if not follow these rules?


48. In function and procedure the parameter pass is "call by value" or "call by referenc
In function and procedure the parameter pass is "call by value" or "call by reference"?


49. What can be the Maximum size of a plsql block?


50. Compare EXISTS and IN Usage with advantages and disadvantages.
51. Which two statements are true?A. A function must return a value.B. A procedure must return a value.C. A function executes a PL/SQL statement.D. A function is invoked as part of an expression.E. A procedure must have a return Data


52. Oracle extract records from temporary table


53. Pragma Init Exception


54. What are the disadvantages of Packages and triggers??


55. Hi, How do we display the column values of a table using cursors without knowing the column names inside the loop?


56. What will happen after commit statement ?


57. What is PL/SQL ?


58. 1.How to display current Date & Time in Pl/Sql2.How to use DML in Procedure?


59. How do you call procedure have a DDL or commit/rollback statement from a trigger?


60. Oracle Cursor types
61. Suppose I have 2 triggers on table T, tr1- a before insert trigger & tr2- a before update trigger.tr1 has update (T) statement inside body of tr1andtr2 has insert (T) statement inside body of tr2Now, I'm tring to insert a row into T.What will hppn??


62. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?


63. what is diffrence between IS and AS in procedure?


64. Hi Friends!! Can anybody answer what are the constraints on Mutating tables? How to remove the mutating errors in triggers?


65. What are mutating tables?
66. Write sample code that can create a hierachical set of data without using a start with and connect by clause in PL/SQL


67. how can i import .dmp file in lower version of oracle from higher version ?
68. how can i get set identity for last coloumn of the table.


69. What is the basic structure of PL/SQL ?


70. you have compiled some PL/SQL packages in your schema, and found aome errors in one procedure.how do you find which procedure produced the error?how do you find which section of the code produced the error and look at?
71. char(20) = 'name' varchar2(20)='name' When comparing these two values, are
char(20) = 'name' varchar2(20)='name' When comparing these two values, are the spaces padded in char are considered or not? Are both values equal?


72. What is the difference between right join and right outer join..


73. What is the disadvantage of out paramter in functions


74. What is the need for using function purity in pl/sql


75. What is the difference between using IS and AS while creating a procedure, function package and package body?


76. What are the restrictions on Functions ?


77. What is PL/SQL table? SNO MARK ------- ------------------1 592 403 ‘A’4 60 Write a single query to I) Sorted Marks II)First mark III) replace the mark ‘A’ with 0(zero)?


78. Without closing the cursor, If you want to open it what will happen. If error, get what is the error?


79. What are the components of a PL/SQL Block ?


80. Is it possible to use commit or rollback in exception section.
81. Why DUAL table is not visible?


82. What are the PL/SQL Statements used in cursor processing ?


83. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?


84. how can we avoid duplicate rows. without using distinct command
85. can procedures have parameters


86. How to return more than one value from a function?What are the types of triggers?What are the featu
How to return more than one value from a function?What are the types of triggers?What are the features of oracle 9i


87. How can I speed up the execution of query when number of rows in the tables increased


88. 1.What is bulk collect?2.What is instead trigger3.What is the difference between Oracle table & PL/SQL table?4.What R built in Packages in Oracle?5.what is the difference between row migration & row changing?


89. what is diff between strong and weak ref cursors


90. 1)any one can tell me,suppose we have 1000 of records,ok.then we want to update only 500 records,how can we solve this problem?2)how many types of "explicit cursors" we have?
91. why do we need to create a force view?what is its purpose?give some examples?


92. What is a cursor ? Why Cursor is required ?


93. What is materialized view?


94. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?


95. What will happen to an anonymus block,if there is no statement inside the block?eg:-declarebeginend;


96. Can we have same trigger with different names for a table?eg: create trigger trig1after insert on tab1;andeg: create trigger trig2after insert on tab1;If yes,which trigger executes first.


97. difference between truncate and delete


98. What are the two parts of a procedure ?


99. How to reduce the the burden/main memory on database if i am using refcursor to hold large data to increase performance.


100. Name the tables where characteristics of Package, procedure and functions are stored ?
101. How packaged procedures and functions are called from the following?


102. Explain how procedures and functions are called in a PL/SQL block ?


103. How many types of database triggers can be specified on a table ? What are they ?


104. What will be the impact of replacing an API call with a stored PL/SQL call?


105. How PL SQL is different from T-SQL


106 > Select Count(*) from T1 where a=10 3> Select count(*) from T1 where b=20 11Now, What will b the O/P of the following..select count(*) from T1 where a=10 or b=20..............................


107. What is the purpose of FORCE while creating a VIEW


108. What is an Exception ? What are types of Exception ?


109. What is the output of the following pl/sql block ?declare v_empno emp.empno%type;begin select empno into v_empno from emp where empno = 10;exception when others then dbms_output.put_line ( 'no data found'); when no_data_found then dbms_output.put_line ( 'ther is no data found ');end;


110. Explain the two type of Cursors ?
111. What is the difference between private packages and public package . what is the difference in declaration of these 2 packages.


112. how to avoid the mutating error with sample program


113. Give the structure of the procedure ?


114. Explain about CURSOR and REF CURSUR with real time scenario where this can be used.


115.
When using a count(distinct) is it better to use a self-join or temp table to find redundant data, and provide an example?


116. How do you set table for read only access ?


117. what is the use of nocopy parameter in oracle procedure


118. What is CODEX function?
119. can we use commit in trigger and cursors?


120. Why we use instead of trigger. what is the basic structure of the instead of trigger. Explain specific business reason of it's use
121. how to create a constraint for a tablecolumn which is already created


122. How to disable a trigger for a particular table ?


123. Force View


124. How do you debug the PL/SQL ?


125. What is mutatinig trigger? How do you avoid mutating trigger?


126. What is a purity level? How it is should be taken into consideration when your writing any database objects i.e., trigger,function, procedure etc.,


127. how to trace the errors in pl/sql block code..


128. How to get the 25th row of a table.


129. if there is an index including three columns A, B and C. And if we issue a query in which where clause uses only column B....will the index be useful??and what if the where clause only has coulmn A..will the index b useful??


130. What is difference between PL/SQL tables and arrays?
131. What is the use of NOCOPY Compiler Hint while writing PL/SQL procedures/subprograms???


132. Select from A table through cursor and update B table. If it updates successfully then insert into another table. Handled every type of exception in the code?


133. Is it possible create table in procedure or function? If Not Why?


134. what are the advantages & disadvantages of packages ?


135. What is PRAGMA RESTRICT_REFERENCES:


136. Suppose thr are 10 DMLs(insert,update,delete ) in the main section of the PL/SQL block .The exception in them is handled as a whole in the exception handling section .....The error may occur in any of this DMLs ,so how can we understand that which DML has failed ??


137. What are the advantages and disadvantages of DBMS-SQL


138. how to insert a music file into the database


139. What is Atomic transaction?


140. what is the order of execution if there is a statement level and row level trigger on a same table?

141. Explain, Is it possible to have same name for package and the procedure in that package.


142. How to trace PL/SQL Package?How to trace PL/SQL procedures?How to trace SQL statement?what is DBMS_TRACE? How to use?SET AUTOTRACE ON; ?If anyone tell me how we can use trace and create log that would be great?


143i want to tune the below query for performance issue can u please help me the query is SELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_ITEM_ACTIVITY A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'UNIONSELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_PENDING_ITEM A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%&


144. HiWhile creating a table, what is the difference between VARCHAR2(80) and VARCHAR2(80 BYTE)?


145. How can i see the time of execution of a sql statement?


146. what happens when commit is given in executable section and an error occurs ?please tell me what happens if exception block is committed at the last?


147. What are the Limitations of Packages,views,procedures?What is the maximum number of subprograms inside a package?


148. what is difference between varray and nested table.can u explain in brief and clear my these concepts.also give a small and sweet example of both these.


149. Wheather a Cursor is a Pointer or Reference?


150. How to find the nth hightest record holder from a table
151. What is the difference between In, Out, InOut Parameters. Can we pass value or reference or both to the In Out Parameter.


152. What is a NOCOPY parameter? Where it is used?


153. What is PL/SQL table ?


155. Can we create a table using with Procedure or Function?wat is the Mutating trigger error?


156. Can e truncate some of the rows from the table instead of truncating the full table.


157. What are the Restrictions on Cursor Variables?Thanks Ramki, Hyd, TCS


158. How to change owner of a table?


159. Mention the differences between aggregate functions and analytical functions clearly with examples?


160. how can u create session variable in pakages?
161. How can I create a new table by using other two table's values.


162. what is the diff between %Rowtype and %type?


163. what is the difference between database trigger and schema trigger?


164. How to avoid using cursors? What to use instead of cursor and in what cases to do so?
165. How to disable multiple triggers of a table at at a time?

166 What will the Output for this Coding> Declare Cursor c1 is select * from emp FORUPDATE; Z c1%rowtype;Begin Open C1;Fetch c1 into Z;Commit;Fetch c1 in to Z;end;


167. Can we use commit or rollback command in the exception part of PL/SQL block?

168. Suppose, I've created a new database DB1 n i've created a table DB1.T1.Now, DESC T1 --> d
Suppose, I've created a new database DB1 n i've created a table DB1.T1.Now, DESC T1 --> desplaying the table structure butselect * from DB1.T1 ---->giving--> table or view does not exist.. Can any one explain possible reason behind this.


169. What are the datatypes a available in PL/SQL ?


170. can i change the elements of listitems at runtimes?
171. Give the structure of the function ?


172. pls send the interview qustions from pl/sql, sql, datawarehousing questions.


173. What is the difference between a reference cursor and normal cursor ?


174. How to view the contents of tables created by the following procedure after the Loop?CREATE OR REPLACE PROCEDURE A0_BULK_COLLECT_TEST IS TYPE EMPLOYEE_MRNO IS TABLE OF A_REGISTRATION_HEADER.ARH_MR_NUM%TYPE; TYPE EMPLOYEE_NAME IS TABLE OF VARCHAR2(255); MRNUMBERS EMPLOYEE_MRNO; NAMES EMPLOYEE_NAME; CURSOR crBulkCollect IS SELECT ARH_MR_NUM, ARH_FIRST_NAME||' '||ARH_MIDDLE_NAME||' '||ARH_LAST_NAME FROM A_REGISTRATION_HEADER WHERE ARH_CTGRY_CD='EMP';BEGIN


175. what is difference between Cursor and Ref Cursor. Please give example.


176. State the advatage and disadvantage of Cursor's


177. can we declare a column having number data type and its scale is larger than pricesionex: column_name NUMBER(10,100), column_name NUMBAER(10,-84)


178. what is datatype of x when we say define x in oracle


179. How we can create a table in PL/SQL block. insert records into it??? is it possible by some procedure or function?? please give example...


180. Can any one explain Perforance Tuning in PL/SQL
181. How to display the contents of a current record fetched in a ref cursor


182. How to handle exception in Bulk collector?


183. What is the DATATYPE of PRIMARY KEY?is it Binary integer..i'm not sure..1.Varchar22.Char3.Binary integer4.Number


184. In a Distributed Database System Can we execute two queries simultaneously ? Justify ?


185. #1 What are the advantages and disadvantages of using PL/SQL or JAVA as the primary programming tool for database automation.#2 Will JAVA replace PL/SQL?


186. Write the order of precedence for validation of a column in a table ?


187. 1) Why it is recommonded to use INOUT instead of OUT parameter type in a procedure?2) What happen if we will not assign anything in OUT parameter type in a procedure?


188. What is Mutation of a trigger? why and when does it oocur?


189. can anybody tell me a sample OCI function which will be able to call from Tourbo cthanx!!


190. we have a trigger on data base.in the trigger body we have created a body using dbms_output.put_line(********) ;this should be firedwhen ever trigger executed;
191. What is PL/Sql tables?Is cursor variable store in PL/SQL table?


192. What type of binding is PL/SQL?


193. What steps should a programmer should follow for better tunning of the PL/SQL blocks?Difference between procedure and function?What is the use of ref cursor return type?


194. Based on what conditions can we decide whether to use a table or a view or a materialized view ?


195. What is Data Concarency and Consistency?


196. What is bulk binding please explain me in brief ?


197. What is the difference between all_ and user_ tables ?


198. what is crosstab


199. can i write plsql block inside expection


200. What is a database trigger ? Name some usages of database trigger ? Subscribe
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications,&n
201. Describe in brief some of the featurs of oracle9i.What is LogMiner?


202. What happens when a package is initialized ?


203. What are the cursor attributes used in PL/SQL ?


204. What is difference between % ROWTYPE and TYPE RECORD ?


205. What are two virtual tables available during database trigger execution ?

Oracle SQL interview questions-1

Oracle Basic Interview Questions:

1, deleting the duplicate records from the table

1)delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); or

2)

delete from vasu where

rowid in(

select rid

from

(

select rowid rid,

row_number() over (partition by name order by rowid desc) top

from vasu )

where top<>1

)

Test Case 1 - Basic Delete

delete from artist_test

where SECOND_COLL='JAVA CLASS';

Test Case 2 - Delete with PLSQL and forall loop, committing only once

DECLARE

TYPE artist_value IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;

del_value artist_value;

rowkey PLS_INTEGER;

BEGIN

SELECT rowid BULK COLLECT INTO del_value FROM artist_test WHERE SECOND_COLL='JAVA CLASS';

IF del_value IS NOT NULL THEN

FORALL rowkey IN del_value.FIRST..del_value.LAST

DELETE artist_test WHERE rowid = del_value(rowkey);

commit;

END IF;

END;

/

Test Case 3 - Delete using anonymous PL/SQL block, looping through the cursor, and deleting one tow at a time. Loop has counter, programmed to trigger commit after each 1000 records.

SQL> declare

rec_read number(6) := 0;

rec_rest number(6) := 0;

vc_var_out varchar2(41) := 'Delete Archive Data';

cursor rec_sql is select SECOND_COLL,rowid from artist_test

where SECOND_COLL='JAVA CLASS' order by SECOND_COLL,rowid;

begin

for rec_sql_run in rec_sql loop

rec_read := rec_read + 1 ;

rec_rest := rec_rest + 1 ;

delete from artist_test A where A.rowid = rec_sql_run.rowid ;

if rec_rest > 1000 THEN

COMMIT;

rec_rest := 1;

dbms_output.put_line('COMMIT AT..'|| rec_read);

END IF;

end loop;

end;

/

Test Case 4
a.) Deleting using PL/SQL stored procedure, commit each 1000 records. ( exec delete_table(1000))
b.) Deleting using PL/SQL stored procedure, commit only once ( exec delete_table(100000))

CREATE OR REPLACE PROCEDURE delete_table (commit_size in number default 1000) is

sel_id INTEGER;

del_id INTEGER;

exec_sel INTEGER;

exec_del INTEGER;

cur_stmt VARCHAR2(2000);

del_rowid ROWID;

BEGIN

cur_stmt := 'SELECT rowid FROM artist_test where SECOND_COLL=''JAVA CLASS''';

sel_id := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(sel_id,cur_stmt,dbms_sql.v7);

DBMS_SQL.DEFINE_COLUMN_ROWID(sel_id,1,del_rowid);

exec_sel := DBMS_SQL.EXECUTE(sel_id);

del_id := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(del_id,'delete from artist_test where rowid = :del_rowid',dbms_sql.v7);

LOOP

IF DBMS_SQL.FETCH_ROWS(sel_id) >0 THEN

DBMS_SQL.COLUMN_VALUE(sel_id,1,del_rowid);

DBMS_SQL.BIND_VARIABLE(del_id,'del_rowid',del_rowid);

exec_del := DBMS_SQL.EXECUTE(del_id);

if mod(dbms_sql.last_row_count,commit_size) = 0

then

COMMIT;

end if;

ELSE

exit;

END IF;

END LOOP;

COMMIT;

DBMS_SQL.CLOSE_CURSOR(sel_id);

DBMS_SQL.CLOSE_CURSOR(del_id);

END;

/

Test Case 5 - Delete using standard anonymous PL/SQL block, using execute immediate function and committing only once.

SQL > declare

proc_rows number := 0;

begin

execute immediate 'begin

delete from artist_test where SECOND_COLL=''JAVA CLASS'';

:x :=sql%rowcount;

commit;

end;'

using OUT proc_rows;

dbms_output.put_line('Records processed........'|| proc_rows);

end;

/

Multiple Insert Statement :

insert all

into vasu values ('vasumathi',28)

into vasu values ('vasumathi',34)

into vasu values ('murugesh',32)

into vasu values ('murugesh',34)

select * from dual;

insert into tableX (F1, F2)

5 select 'A', 2 from dual

6 union all

7 select 'B', 2 from dual

8 union all

9 select 'C', 2 from dual;

10 commit;

2. Difference between Union and Union all statement

Union and union all are the set operators and union will eliminate the duplicate records and Union all display all the records

3. What is Index & there types and which is used

Index

Oracle9i uses indexes to avoid the need for large-table, full-table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query.

Types of Indexes:

1. normal/B-Tree (balanced/ binary tree) indexes;

2. Bitmap indexes (which literally use a map of bits to represent the whole index and are most often used in data warehouses);

3. function-based indexes (which are based
on the value of an expression instead of the value of a column);

4. Partitioned indexes (tables and indexes may be partitioned separately) and

Local Index

Global Index

5. Domain indexes (application-specific indexes created on complex data types such as spatial data). This article only discusses bitmap and normal (b-tree) indexes.

4. Referential Integrity and their purpose:

Referential integrity (RI) rules ensure that one-to-many and many-to-many relationships are enforced within the relational schema

This is the foreign key constraint as implemented by Oracle. References constraint is only applied at SQL insert and delete times. For example, assume a one-to-many relationship between the Consumer_account_cur EMPLOYEE and consumer_account_prods_cur DEPENDENT tables; each consumer employee may have many products dependents, yet each product dependent belongs to only one consumer employee. The references constraint tells Oracle at insert time that the value in DEPENDENT.emp_num must match the EMPLOYEE.emp_num in the employee row, thereby ensuring that a valid consumer employee exists before the product dependent row is added. At SQL delete time, the references constraint can be used to ensure that an consumer employee is not deleted, if rows still exist in the product DEPENDENT table

This foreign key column establishes a direct relationship with a primary key or unique key column (referenced key) usually in another table. The table containing the foreign key is referred to as the child, and the table containing the referenced key is the parent table. The foreign key and referenced key can be in the same table (parent and child are the same table); otherwise, the parent and child tables must be in the same database.

5. What is merge statement? Where it’s used?

Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

Optional and conditional

MERGE INTO test1 a

USING all_objects b

ON (a.object_id = b.object_id)

WHEN MATCHED THEN

UPDATE SET a.status = b.status

WHEN NOT MATCHED THEN

INSERT (object_id, status)

VALUES (b.object_id, b.status);

MERGE INTO test1 a

USING all_objects b

ON (a.object_id = b.object_id)

WHEN MATCHED THEN

UPDATE SET a.status = b.status

WHERE b.status != 'VALID'

WHEN NOT MATCHED THEN

INSERT (object_id, status)

VALUES (b.object_id, b.status)

WHERE b.status != 'VALID';

MERGE INTO test1 a

USING all_objects b

ON (a.object_id = b.object_id)

WHEN MATCHED THEN

UPDATE SET a.status = b.status

WHERE b.status != 'VALID'

DELETE WHERE (b.status = 'VALID');

6. Partition and their concepts

Oracle can partition along multiple dimensions, such as partitioning by date

along one dimension, and by region along another dimension. Oracle provides

several other partitioning schemes, to accommodate all types of business

scenarios.

Oracle database capabilities. For example, in addition to storing older data on less

expensive storage, the older data could also be compressed thus further reducing

storage costs. The newer data (in separate partitions) remains uncompressed to

optimize access and updates

7. Difference between Oracle 9i & 10g and 11g

1.Simplified and improved automatic memory management
2.New fault diagnosability infrastructure to prevent, detect, diagnose, and help
resolve critical database errors
3.Invisible Indexes
4.Virtual columns
5.Enhanced security for password-based authentication by enabling use of mixed
case in passwords.
6.Tablespace-level encryption
7.Ability to online redefine tables that have materialized view logs

8. How to execute the SQL in Unix

To execute sqlplus via Unix shell scripts the following parameters need to be initialized.

(1.) ORACLE_HOME

export ORACLE_HOME Path to

(2.) ORACLE_SID

export ORACLE_SID Actual ORACLE_SID

After these initializations have been done use sqlplus -v to login into the database non interactively and execute the SQL or PL/SQL Code.

sqlplus -v ${USERNAME}/${PASSWORD}@${ORACLE_SID} <<>
@mysqlfile.sql
EOF

9. What are the Analytical Functions do u used?

Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [] )

ROW_NUMBER, RANK and DENSE_RANK

10. What are constraints?

• Oracle "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to the table definition to ensure the validity of the region column.
  
• Not Null Constraint: This Oracle constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time.
  
• Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key.
  
• References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times. At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.
  
• Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.

11. Difference between primary key and unique key?

• Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key.
  
• Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.

12. What is temporary table?

Private to a session

Retain data only for the duration or session

No DML Locks

No redo generation

CREATE GLOBAL TEMPORARY TABLE emp_temp

AS SELECT * FROM emp;

ON COMMIT {PRESERVE|DELETE} ROWS;

13. What is Clustered Table?

Clustered Table

Group of tables sharing common columns

Cluster key is used to identify the rows needed to store together

Cluster key is independent of primary key

14. What is decode statement and when it’s used?

SELECT supplier_name,
decode(supplier_id,	10000,	'IBM',
	10001,	'Microsoft',
	10002,	'Hewlett Packard',
		'Gateway') result
FROM suppliers;

IF supplier_id = 10000 THEN
result := 'IBM';

ELSIF supplier_id = 10001 THEN
result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';

ELSE
result := 'Gateway';

END IF;

14. What is the difference between count (*) and count (1)?

There is difference, Count(*) is slower than count(1) query. using Count(1) improves performance and its substantial in case of tables > 20K rows

15. Difference between Truncate and Delete?

[1] DELETE - is a DML Command & TRUNCATE - is a DDL Command

[2] After DELETE - can rollback the Records & After TRUNATE - cannot rollback the records

[3] In DELETE Command you can give the conditions in WHERE Clause & In TRUNCATE you cannot give conditions

[4] After using DELETE Command The memory will be occupied till the user does not give ROLLBACK or COMMIT & After using TRUNCATE Command The memory realeased immediately

16. What is a pseudo column? Give some examples?

pseudo column is a column that
is not present in the database.
it is used for unique identification of rows like
ROWNUM,ROWID,LEVEL,NEXTVAL,CURVAL,NULL etc.

17. What is analyze and their purpose?

18.How do u insert multiple inserts?

19. Flat file loading process?

20. What is transaction statement?

A transaction is a sequence of SQL statements that Oracle treats as a single unit of work. Once the transaction begins, every SQL DML (Data Manipulation Language) statement you issue subsequently becomes a part of this transaction. A transaction ends when you disconnect from the database, or when you issue a COMMIT or ROLLBACK command.

COMMIT makes permanent any database changes you made during the current transaction. Until you commit your changes, other users cannot see them. ROLLBACK ends the current transaction and undoes any changes made since the transaction began

A database transaction is a group of SQL statements that are a logical unit of work. You can think of a transaction as an inseparable set of SQL statements that should be made permanent in the database (or undone) as a whole. An example would be a transfer of money between bank accounts: One UPDATE statement would subtract from the total amount of money from one account, and another UPDATE would add money to the other account. Both the subtraction and the addition must either be permanently recorded in the database, or they both must be undone—otherwise money will be lost. This simple example uses only two UPDATE statements, but a more realistic transaction may consist of many INSERT, UPDATE, and DELETE statements.

To permanently record the results of the SQL statements in a transaction, you perform a commit with the COMMIT statement. To undo the results of the SQL statements, you perform a rollback with the ROLLBACK statement, which resets all the rows back to what they were originally. Any changes you make prior to performing a rollback will be undone, as long as you haven't disconnected from the database beforehand. You can also set a savepoint that may be used to rollback a transaction to a specific point, while leaving the other statements in the transaction intact.

21. What is dead lock and how do u overcome this?

deadlock refers to a specific condition when two or more processes are each waiting for each other to release a resource, or more than two processes are waiting for resources in a circular chain (see Necessary conditions). Deadlock is a common problem in multiprocessing where many processes share a specific type of mutually exclusive resource known as a software, or soft, lock.

we can avoid deadlock by avoiding the following conditions:

1. Mutual Exclusion

2. Hold and wait.

3. No preemption.

4. Circular wait.

22. What is the difference between rownum,rowid

rowid has a physical significance i.e you can read a row if you know rowid. It is complete physical address of a row.

While rownum is temporary serial number allocated to each returned row during query execution.

Rownum is numeric and rowid is 16 bit hexadecimal no.

23. What is synapshot?

A snapshot is a read-only copy of a master table / output of a query

based on multiple tables located on a remote location.Once these are read only so can not have DML statments. Yes they can be refeshed periodically based the condition defined while creating the snapshot. Advance version of snapshot is materialized views.

A snapshot is a read-only copy of a master table located on a remote node. Snapshots can be queried but not updated; only the master table can be updated. Snapshots are periodically refreshed to reflect changes made to the master table.

24. Write a query that displays the difference between the highest and lowest salaries. Label the column

select max(sal)-min(sal) difference from emp;

25.What are various joins used while writing SUBQUERIES

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

26. What are various constraints used in SQL

NULL
NOT NULL
CHECK
DEFAULT

What is a CO-RELATED SUBQUERY

A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery.
2. eg
3. SELECT field1 from table1 X
4. WHERE field2>(select avg(field2) from table1 Y
5. where
field1=X.field1);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)

What are various joins used while writing SUBQUERIES

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

What are various constraints used in SQL

NULL
NOT NULL
CHECK
DEFAULT

What are different Oracle database objects

TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES etc

What is difference between Rename and Alias

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

What is a view

A view is stored procedure based on one or more tables, its a virtual table.

What are various privileges that a user can grant to another user

SELECT
CONNECT
RESOURCE

What is difference between UNIQUE and PRIMARY KEY constraints

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

Can a primary key contain more than one columns

Yes

How you will avoid duplicating records in a query

By using DISTINCT

What is difference between SQL and SQL*PLUS

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

Which datatype is used for storing graphics and images

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

How will you delete duplicating rows from a base table

CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;
DROP old_table RENAME new_table TO old_table

DELETE FROM table_name A WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column_name)

What is difference between SUBSTR and INSTR

SUBSTR returns a specified portion of a string eg SUBSTR(’BCDEF’,4) output BCDE INSTR provides character position in which a pattern is found in a string. eg INSTR(’ABC-DC-F’,'-’,2) output 7 (2nd occurence of ‘-’ )