PLSQL interview questions - part4

oracle pl sql interview questions for 3+ years experience

51.What is “Check Constraints” and “with check options” and “Default Specification”?

Ans: CHECK Integrity Constraints: A CHECK integrity constraint on a column or a set of columns requires that a specified condition be true or unknown (ie. Not false) for every row of the table. If a DML statement is issued so that the condition of the CHECK constraint evaluates to false, the statement is rolled back. With check Option: With Check option restricts inserts and updates performed through the view to prevent them from creating rows that the view cannot itself select .based on where clause of the create view statement. For eg: Create or replace view Women As select name from Employee Where Sex= ‘Female’ With Check Option; Default Specification It supplies a default value if column value is not specified on INSERT It can contain literals (constants) and SQL functions, USER, SYSDATE, sequence It cannot include references to any columns.

52.What is the maximum no. Of columns a table can have ?

Ans: 254(Oracle)

53.Can a trigger written for a view ?

Ans: No

Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be ?

Ans: 3

54.Can you create index on view ?

Ans: No

55.What is the difference between alias and synonym ?

Ans: Alias is temporary and used with one query. Synonym is permanent and not used as alias.

What’s the length of SQL integer ?

Ans: 32 bit length

56.What is tkprof and how is it used?

Ans: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool . This can also be used to generate explain plan output.

57.What is explain plan and how is it used?

Ans: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.

58.What is The Dynamic Performance Tables?

Ans: Throughout its operation, ORACLE maintains a set of "virtual" tables that record current database activity. These tables are called Dynamic performance tables. Because dynamic performance tables are not true tables, they should not be accessed by most users. However, database administrators can query these tables and can create views on the tables and grant access to those views to other users. The dynamic performance tables are owned by SYS and their names all begin with V_$. Views are created on these tables, and then synonyms are created for the views. The synonym names begin with V$.

59.What is Savepoint ?

Ans: Savepoints are intermediate markers that can be declared in long transactions that contain many SQL statements. By using savepoints, you can arbitrarily mark your work at any point within a long transaction. This allows you the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.

60.What is Deadlocks?

Ans: A deadlock is a situation that can occur in multi-user systems that causes some number of transactions to be unable to continue work. A deadlock can occur when two or more users are waiting for data locked by each other. It typically happens when each of two or more users are waiting to access a resource that another user has already locked. This creates a deadlock situation because each user is waiting for resources held by the other user. Eg Transaction 1 Time Point Transaction 2 UPDATE emp 1 UPDATE emp SET sal = sal*1.1 SET sal = 1342 WHERE empno = 1000; WHERE empno = 2000; UPDATE emp 2 UPDATE emp SET sal = sal*1.1 SET sal = 1342 WHERE empno = 2000; WHERE empno = 1000; ORA-00060 3 deadlock detected while waiting for resource

61.What is Privilege ?

Ans: A privilege is a right to execute a particular type of SQL statement or to access another user's object. Types of privileges : • system privileges • object privileges System Privileges : System privileges allow users to perform a particular systemwide action, or to perform a particular action on a particular type of object. E.g. Create Tablespace, Delete the row of any table, etc. Object Privileges : Object privileges allow users to perform a particular action on a specific object. E.g. Delete row of specific table, etc. Roles : Roles are named groups of related privileges that are granted to users or other roles. Advantages of Roles : 1. Reduced granting of privileges 2. Dynamic privilege management (Changing of privileges) 3. Selective availability of privileges (Enalbling/Disabling roles) 4. Application awareness (Enalbling/Disabling of roles by application)

62.What is Two Phase Commit ?

Ans: Two Phase Commit is a mechanism wherein ORACLE automatically controls and monitors the commit or rollback of a distributed transaction and maintains the integrity of the global database. The Phases of the Two-Phase Commit Mechanism :
• Prepare phase : The global co-ordinator (initiating node) asks participants to prepare (to promise to commit or rollback the transaction, even if there is a failure).
• Commit phase : If all participants respond to the co-ordinator that they are prepared, the co-ordinator asks all nodes to commit the transaction; if all participants cannot prepare, the co-ordinator asks all nodes to roll back the transaction.

63.Explain about snapshots in detail?

Ans: Snapshots are read-only copies of a master table (or multiple tables) located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. A snapshot is a full copy of a table or a subset of a table that reflects a recent state of the master table. A snapshot is defined by a distributed query that references one or more master tables, view, or other snapshots. Simple vs. Complex Snapshots : Each row in a simple snapshot is based on a single row in a single remote table. Therefore, a simple snapshot's defining query has no GROUP BY or CONNECT BY clauses, or subqueries, joins, or set operations. If a snapshot's defining query contains any of these clauses or operations, it is referred to as a complex snapshot. Internals of Snapshot Creation: When a snapshot is created, several operations are performed internally by ORACLE: • ORACLE (at the snapshot node) creates a table to store the rows retrieved by the snapshot's defining query; this is the snapshot's base table. • ORACLE creates a read-only view on the SNAP$ table (base table) for queries issued against the snapshot. • ORACLE creates a second local view on the remote master table. It uses this view when it refreshes the snapshot. • Additionally, if the snapshot is a simple snapshot, ORACLE creates an index on the SNAP$ table. All of these internal objects are created in the schema of the snapshot. Do not alter, change data in, or delete these objects manually.

64.What is Ref Cursor?

Ans: A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

65.What is row chaining, how does it happen?

Ans: Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won’t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.

               Part1                     Part2                     Part3                     Part4                     Part5