Wednesday, 30 October 2013

Oracle Interview Questions 10

1. What is PL/SQL ?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

2. What is the basic structure of PL/SQL ?
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

3. What are the components of a PL/SQL block ?
A set of related declarations and procedural statements is called block.

4. What are the components of a PL/SQL Block ?
Declarative part, Executable part and Exception part.
Datatypes PL/SQL

5. What are the datatypes a available in PL/SQL ?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.
6. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are : I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

7. What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different
table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type
);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.

8. What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.

9. What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.


10. Explain the two type of Cursors ?
There are two types of cursors, Implicit Cursor and Explicit Cursor.
PL/SQL uses Implicit Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.

11. What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

12. What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if no rows are fetched.
These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

13. What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes
when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;

14. What will happen after commit statement ?
Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;

The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

15. Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE, DELETE statement refers to the latest row fetched from a cursor.

16. What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

17. How many types of database triggers can be specified on a table ? What are they ?
Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the returned boolean value.

18. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

19. What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.

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

21. Write the order of precedence for validation of a column in a table ?
I. done using Database triggers.
ii. done using Integrity Constraints.
I & ii.

22. What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined exceptions are.
 
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.

23. What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.
e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

24. What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

25. What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.

26. Where the Pre_defined_exceptions are stored ?
In the standard package.

27. What is a stored procedure ?
A stored procedure is a sequence of statements that perform specific function.

28. What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.

29. What are advantages fo Stored Procedures /
Extensibility, Modularity, Reusability, Maintainability and one time compilation.

30. What are the modes of parameters that can be passed to a procedure ?
IN, OUT, IN-OUT parameters.

31. What are the two parts of a procedure ?
Procedure Specification and Procedure Body.

32. Give the structure of the procedure ?
PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;

33. Give the structure of the function ?
FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;

34. Explain how procedures and functions are called in a PL/SQL block ?
Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');

35. What is Overloading of procedures ?
The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line

36. What is a package ? What are the advantages of packages ?
Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Application Design, Information. Hiding,. reusability and Better Performance.

37.What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.

38. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

39. How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any
out/in-out parameters. A function can not be called.

40. Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.

Tuesday, 29 October 2013

Oracle Interview Questions 09



101. Will the Optimizer always use COST-based approach if OPTIMIZER_MODE is set to "Cost'?
Presence of statistics in the data dictionary for atleast one of the tables accessed by the SQL statements is necessary for the OPTIMIZER to use COST-based approach. Otherwise OPTIMIZER chooses RULE-based approach.

102. What is the effect of setting the value of OPTIMIZER_MODE to 'RULE' ?
This value causes the optimizer to choose the rule_based approach for all SQL statements issued to the instance regardless of the presence of statistics.

103. What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command ?
CHOOSE,ALL_ROWS,FIRST_ROWS and RULE.

104. What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.

105. What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ?
This value causes the optimizer to the cost-based approach for all SQL statements in the session regardless of the presence of statistics and to optimize with a goal of best throughput.

106. What is the effect of setting the value 'FIRST_ROWS' for OPTIMIZER_GOAL parameter of the ALTER SESSION command ?
This value causes the optimizer to use the cost-based approach for all SQL statements in the session regardless of the presence of statistics and to optimize with a goal of best response time.

107. What is the effect of setting the 'RULE' for OPTIMIER_GOAL parameter of the ALTER SESSION Command ?
This value causes the optimizer to choose the rule-based approach for all SQL statements in a session regardless of the presence of statistics.

108. What is RULE-based approach to optimization ?
Choosing an executing planbased on the access paths available and the ranks of these access paths.

109. What is COST-based approach to optimization ?
Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.


PROGRAMMATIC CONSTRUCTS

110. What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?
Procedures and Functions,Packages and Database Triggers.

111. What is a Procedure ?
A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

112. What is difference between Procedures and Functions ?
A Function returns a value to the caller where as a Procedure does not.

113. What is a Package ?
A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database.

114. What are the advantages of having a Package ?
Increased functionality (for example,global package variables can be declared and used by any proecdure in the package) and performance (for example all objects of the package are parsed compiled, and loaded into memory once)

115. What is Database Trigger ?
A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in,update to, or delete from a table.

116. What are the uses of Database Trigger ?
Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.

117. What are the differences between Database Trigger and Integrity constraints ?
A declarative integrity constraint is a statement about the database that is always true. A constraint applies to existing data in the table and any statement that manipulates the table.
A trigger does not apply to data loaded before the definition of the trigger, therefore, it does not guarantee all data in a table conforms to the rules established by an associated trigger.
A trigger can be used to enforce transitional constraints where as a declarative integrity constraint cannot be used.

DATABASE SECURITY

118. What are Roles ?
Roles are named groups of related privileges that are granted to users or other roles.

119. What are the use of Roles ?
REDUCED GRANTING OF PRIVILEGES - Rather than explicitly granting the same set of privileges to many users a database administrator can grant the privileges for a group of related users granted to a role and then grant only the role to each member of the group.
DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.
SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a user can be selectively enable (available for use) or disabled (not available for use). This allows specific control of a user's privileges in any given situation.
APPLICATION AWARENESS - A database application can be designed to automatically enable and disable selective roles when a user attempts to use the application.

120. How to prevent unauthorized use of privileges granted to a Role ?
By creating a Role with a password.

121. What is default tablespace ?
The Tablespace to contain schema objects created without specifying a tablespace name.

122. What is Tablespace Quota ?
The collective amount of disk space available to the objects in a schema on a particular tablespace.

123. What is a profile ?
Each database user is assigned a Profile that specifies limitations on various system resources available to the user.

124. What are the system resources that can be controlled through Profile ?
The number of concurrent sessions the user can establish the CPU processing time available to the user's session the CPU processing time available to a single call to ORACLE made by a SQL statement the amount of logical I/O available to the user's session the amout of logical I/O available to a single call to ORACLE made by a SQL statement the allowed amount of idle time for the user's session the allowed amount of connect time for the user's session.

125. What is Auditing ?
Monitoring of user access to aid in the investigation of database use.

126. What are the different Levels of Auditing ?
Statement Auditing, Privilege Auditing and Object Auditing.

127. What is Statement Auditing ?
Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects.

128. What is Privilege Auditing ?
Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.

129. What is Object Auditing ?
Object auditing is the auditing of accesses to specific schema objects without regard to user.

DISTRIBUTED PROCESSING AND DISTRIBUTED DATABASES

130. What is Distributed database ?
A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.

131. What is Two-Phase Commit ?
Two-phase commit is mechanism that guarantees a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phase, a Prepare Phase and a Commit Phase.

132. Describe two phases of Two-phase commit ?
Prepare phase - The global coordinator (initiating node) ask a 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 coordinator that they are prepared, the coordinator asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all nodes to roll back the transaction.

133. What is the mechanism provided by ORACLE for table replication ?
Snapshots and SNAPSHOT LOGs

134. What is a SNAPSHOT ?
Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.

135. What is a SNAPSHOT LOG ?
A snapshot log is a table in the master database that is associated with the master table. ORACLE uses a snapshot log to track the rows that have been updated in the master table. Snapshot logs are used in updating the snapshots based on the master table.

136. What is a SQL * NET?
SQL *NET is ORACLE's mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. It is used in Clint-Server and Server-Server communications.

DATABASE OPERATION, BACKUP AND RECOVERY

137. What are the steps involved in Database Startup ?
Start an instance, Mount the Database and Open the Database.

138. What are the steps involved in Database Shutdown ?
Close the Database, Dismount the Database and Shutdown the Instance.

139. What is Restricted Mode of Instance Startup ?
An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.

140. What are the different modes of mounting a Database with the Parallel Server ?
Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database.
Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.

141. What is Full Backup ?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.

142. Can Full Backup be performed when the database is open ?
No.

143. What is Partial Backup ?
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

144.WhatisOn-lineRedoLog?
The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.

145. What is Mirrored on-line Redo Log ?
A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

146. What is Archived Redo Log ?
Archived Redo Log consists of Redo Log files that have archived before being reused.

147. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ?
Complete database recovery from disk failure is possible only in ARCHIVELOG mode.
Online database backup is possible only in ARCHIVELOG mode.

148. What is Log Switch ?
The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.

149. What are the steps involved in Instance Recovery ?
R_olling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments.
Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. 
Releasing any resources (locks) held by transactions in process at the time of the failure.
Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.

Monday, 28 October 2013

Oracle Interview Questions 08



51. What is a Data Dictionary ?
The data dictionary of an ORACLE database is a set of tables and views that are used as a read-only reference about the database.
It stores information about both the logical and physical structure of the database, the valid users of an ORACLE database, integrity constraints defined for tables in the database and space allocated for a schema object and how much of it is being used.

52. What is an Integrity Constrains ?
An integrity constraint is a declarative way to define a business rule for a column of a table.

53. Can an Integrity Constraint be enforced on a table if some existing table data does not satisfy the constraint ?
No.

54. Describe the different type of Integrity Constraints supported by ORACLE ? 
NOT NULL Constraint - Disallows NULLs in a table's column.
UNIQUE Constraint - Disallows duplicate values in a column or set of columns.
PRIMARY KEY Constraint - Disallows duplicate values and NULLs in a column or set of columns.
FOREIGN KEY Constrain - Require each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY KEY.
CHECK Constraint - Disallows values that do not satisfy the logical expression of the constraint.

55. What is difference between UNIQUE constraint and PRIMARY KEY constraint ?
A column defined as UNIQUE can contain NULLs while a column defined as PRIMARY KEY can't contain Nulls.

56. Describe Referential Integrity ?
A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.

57. What are the Referential actions supported by FOREIGN KEY integrity constraint ?
UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data.

DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.

58. What is self-referential integrity constraint ?
If a foreign key reference a parent key of the same table is called self-referential integrity constraint.

59. What are the Limitations of a CHECK Constraint ?
The condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't contain subqueries, sequence, the SYSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

60. What is the maximum number of CHECK constraints that can be defined on a column ?
No Limit.


SYSTEM ARCHITECTURE :

61. What constitute an ORACLE Instance ?
SGA and ORACLE background processes constitute an ORACLE instance. (or) Combination of memory structure and background process.

62. What is SGA ?
The System Global Area (SGA) is a shared memory region allocated by ORACLE that contains data and control information for one ORACLE instance.

63. What are the components of SGA ?
Database buffers, Redo Log Buffer the Shared Pool and Cursors.

64. What do Database Buffers contain ?
Database buffers store the most recently used blocks of database data. It can also contain modified data that has not yet been permanently written to disk.

65. What do Redo Log Buffers contain ?
Redo Log Buffer stores redo entries a log of changes made to the database.

66. What is Shared Pool ?
Shared Pool is a portion of the SGA that contains shared memory constructs such as shared SQL areas.

67. What is Shared SQL Area ?
A Shared SQL area is required to process every unique SQL statement submitted to a database and contains information such as the parse tree and execution plan for the corresponding statement.

68. What is Cursor ?
A Cursor is a handle ( a name or pointer) for the memory associated with a specific statement.

69. What is PGA ?
Program Global Area (PGA) is a memory buffer that contains data and control information for a server process.

70. What is User Process ?
A user process is created and maintained to execute the software code of an application program. It is a shadow process created automatically to facilitate communication between the user and the server process.

71. What is Server Process ?
Server Process handle requests from connected user process. A server process is in charge of communicating with the user process and interacting with ORACLE carry out requests of the associated user process.

72. What are the two types of Server Configurations ?
Dedicated Server Configuration and Multi-threaded Server Configuration.

73. What is Dedicated Server Configuration ?
In a Dedicated Server Configuration a Server Process handles requests for a Single User Process.

74. What is a Multi-threaded Server Configuration ?
In a Multi-threaded Server Configuration many user processes share a group of server process.

75. What is a Parallel Server option in ORACLE ?
A configuration for loosely coupled systems where multiple instance share a single physical database is called Parallel Server.

76. Name the ORACLE Background Process ?
DBWR - Database Writer.
LGWR - Log Writer
CKPT - Check Point
SMON - System Monitor
PMON - Process Monitor
ARCH - Archiver
RECO - Recover
Dnnn - Dispatcher, and
LCKn - Lock
Snnn - Server.

77. What Does DBWR do ?
Database writer writes modified blocks from the database buffer cache to the data files.

78.When Does DBWR write to the database ?
DBWR writes when more data needs to be read into the SGA and too few database buffers are free. The least recently used data is written to the data files first. DBWR also writes when CheckPoint occurs.

79. What does LGWR do ?
Log Writer (LGWR) writes redo log entries generated in the redo log buffer of the SGA to on-line Redo Log File.

80. When does LGWR write to the database?
LGWR writes redo log entries into an on-line redo log file when transactions commit and the log buffer files are full.

81. What is the function of checkpoint CKPT)?
The Checkpoint (CKPT) process is responsible for signaling DBWR at checkpoints and updating all the data files and control files of the database.

82. What are the functions of SMON ?
System Monitor (SMON) performs instance recovery at instance start-up. In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instance that have failed SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or off-line errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back on-line SMON also coalesces free extents within the database to make free space contiguous and easier to allocate.

83. What are functions of PMON ?
Process Monitor (PMON) performs process recovery when a user process fails PMON is responsible for cleaning up the cache and Freeing resources that the process was using PMON also checks on dispatcher and server processes and restarts them if they have failed.

84. What is the function of ARCH ?
Archiver (ARCH) copies the on-line redo log files to archival storage when they are full. ARCH is active only when a database's redo log is used in ARCHIVELOG mode.

85. What is function of RECO ?
RECOver (RECO) is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals,the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

86. What is the function of Dispatcher (Dnnn) ?
Dispatcher (Dnnn) process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.

87. How many Dispatcher Processes are created ?
Atleast one Dispatcher process is created for every communication protocol in use.

88. What is the function of Lock (LCKn) Process ?
Lock (LCKn) are used for inter-instance locking when the ORACLE Parallel Server option is used.

89. What is the maximum number of Lock Processes used ?
Though a single LCK process is sufficient for most Parallel Server systems
upto Ten Locks (LCK0,....LCK9) are used for inter-instance locking.


DATA ACCESS

90. Define Transaction ?
A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.

91. When does a Transaction end ?
When it is committed or Rollbacked.

92. What does COMMIT do ?
COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.

93. What does ROLLBACK do ?
ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

94. What is SAVE POINT ?
For long transactions that contain many SQL statements, intermediate markers or savepoints can be declared which can be used to divide a transaction into smaller parts. This allows the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.

95. What is Read-Only Transaction ?
A Read-Only transaction ensures that the results of each query executed in the transaction are consistant with respect to the same point in time.

96. What is the function of Optimizer ?
The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

97. What is Execution Plan ?
The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.

98. What are the different approaches used by Optimizer in choosing an execution plan ?
Rule-based and Cost-based.

99. What are the factors that affect OPTIMIZER in choosing an Optimization approach ?
The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

100. What are the values that can be specified for OPTIMIZER MODE Parameter ?
COST and RULE.

Sunday, 27 October 2013

Oracle Interview Questions 07

                                                 All Type Of Database Questions


1. What are the components of Physical database structure of Oracle Database?
ORACLE database is comprised of three types of files. One or more Data files, two are more Redo Log files, and one or more Control files.

2. What are the components of Logical database structure of ORACLE database?
Tablespaces and the Database's Schema Objects.

3. What is a Tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

4. What is SYSTEM tablespace and When is it Created?
Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

5. Explain the relationship among Database, Tablespace and Data file
Each databases logically divided into one or more tablespaces One or more data files are explicitly created for each tablespace.

6. What is schema?
A schema is collection of database objects of a User.
7. What are Schema Objects ?
Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

8. Can objects of the same Schema reside in different tablespaces.?
Yes.

9. Can a Tablespace hold objects from different Schemes ?
Yes.

10. what is Table ?
A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

11. What is a View ?
A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

12. Do View contain Data ?
Views do not contain or store data.

13. Can a View based on another View ?
Yes.

14. What are the advantages of Views ?
Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
Hide data complexity.
Simplify commands for the user.
Present the data in a different perpecetive from that of the base table.
Store complex queries.

15. What is a Sequence ?
A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

16. What is a Synonym ?
A synonym is an alias for a table, view, sequence or program unit.

17. What are the type of Synonyms?
There are two types of Synonyms Private and Public.

18. What is a Private Synonyms ?
A Private Synonyms can be accessed only by the owner.

19. What is a Public Synonyms ?
A Public synonyms can be accessed by any user on the database.

20. What are synonyms used for ?
Synonyms are used to: Mask the real name and owner of an object.
Provide public access to an object
Provide location transparency for tables, views or program units of a remote database.
Simplify the SQL statements for database users.

21. What is an Index ?
An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

22. How are Indexes Update ?
Indexes are automatically maintained and used by ORACLE. Changes to table data are automatically incorporated into all relevant indexes.

23. What are Clusters?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

24. What is cluster Key?
The related columns of the tables in a cluster are called the Cluster Key.

25. What is Index Cluster ?
A Cluster with an index on the Cluster Key.

26. What is Hash Cluster ?
A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

27. When can Hash Cluster used ?

Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

28. What is Database Link ?
A database link is a named object that describes a "path" from one database to another.

29. What are the types of Database Links ?

Private Database Link, Public Database Link & Network Database Link.

30. What is Private Database Link ?
Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

31. What is Public Database Link ?
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

32. What is Network Database link ?
Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

33. What is Data Block ?
ORACLE database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

34. How to define Data Block size ?
A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and cann't be changed latter.

35. What is Row Chaining ?
In Circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs , the data for the row is stored in a chain of data block (one or more) reserved for that segment.

36. What is an Extent ?
An Extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.

37. What is a Segment ?
A segment is a set of extents allocated for a certain logical structure.

38. What are the different type of Segments ?
Data Segment, Index Segment, Rollback Segment and Temporary Segment.

39. What is a Data Segment ?
Each Non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

40. What is an Index Segment ?
Each Index has an Index segment that stores all of its data.

41. What is Rollback Segment ?
A Database contains one or more Rollback Segments to temporarily store "undo" information.

42. What are the uses of Rollback Segment ?
Rollback Segments are used :
To generate read-consistent database information during database recovery to rollback uncommitted transactions for users.

43. What is a Temporary Segment ?
Temporary segments are created by ORACLE when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

44. What is a Data File ?
Every ORACLE database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database.

45. What are the Characteristics of Data Files ?
A data file can be associated with only one database. Once created a data file can't change size.
One or more data files form a logical unit of database storage called a table space.

46. What is a Redo Log?
The set of Redo Log files for a database is collectively known as the database's redo log.

47. What is the function of Redo Log ?
The Primary function of the redo log is to record all changes made to data.

48. What is the use of Redo Log Information ?
The Information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database's data files.

49. What does a Control file Contain ?
A Control file records the physical structure of the database. It contains the following information.
Database Name
Names and locations of a database's files and redolog files.
Time stamp of database creation.

50. What is the use of Control File ?
When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Saturday, 26 October 2013

Oracle Interview Questions 06

                                          Experienced PL/SQL programmer Question




Technical Interview Round

1.       Tell me about yourself?

2.      What is a partition in Oracle?

3.      What is a trigger in SQL?

4.      What are the differences between a procedure and function in SQL?

5.       What is an Explain Plan?
        
6.      Write a SQL query to find second maximum salary in each department of a Company.

7.       Write a SQL query to delete all duplicate records in a table

8.      What is mutating table?

Project Manager Interview Round:

1.       Tell me about yourself?

2.      Which technologies have you worked on in your previous companies?

3.      Would you like to work on Business Objects(BO)?

4.      Tell me the difference between Business Objects(BO) and Cognos?

HR Discussion Round:

1.       Why did you want to change your current job?

2.      Why do you think should you be selected for this position?

3.      How much salary are you expecting?

Oracle Interview Questions 05

                            All Type Of Sql Question And Ans

1.         WHAT IS DATA OR INFORMATION?
Ans: The Matter that we feed into the Computer is called Data or Information.

2.         WHAT IS DATABASE?
Ans: The Collection of Interrelated Data is called Data Base.

3.         WHAT IS A DATABASE MANAGEMENT SYSTEM (DBMS) PACKAGE?
Ans: The Collection of Interrelated Data and some Programs to access
the Data is Called Data Base Management System (DBMS).

4.         WHEN CAN WE SAY A DBMS PACKAGE AS RDBMS?
Ans: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system,
it must use its RELATIONAL facilities to MANAGE the DATABASE.

5.         WHAT IS ORDBMS?
Ans: Object (oriented) Relational Data Base Management System is one
that can store data, the relationship of the data, and the behavior of the data
(i.e., the way it interacts with other data).

6.         NAME SOME CODD'S RULES.
Ans: Dr. E.F. Codd presented 12 rules that a database must obey if it
is to be considered truly relational. Out those,  some are as follows
a)         The rules stem from a single rule- the ‘zero rule’: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities
to MANAGE the DATABASE.
b)         Information Rule: Tabular Representation of Information.
c)         Guaranteed Access Rule: Uniqueness of tuples for guaranteed accessibility.
d)         Missing Information Rule: Systematic representation of missing information as NULL   Values.
e)         Comprehensive Data Sub-Language Rule: QL to support Data definition,
View definition, Data manipulation, Integrity, Authorization and Security.

7.         WHAT ARE HIERARCHICAL, NETWORK, AND RELATIONAL DATABASE MODELS?
Ans: a) Hierarchical Model: The Hierarchical Model was introduced in
the Information Management System (IMS) developed by IBM in 1968. In this data is organized as a tree structure. Each tree is made of nodes and branches.
The nodes of the tree represent the record types and it is a collection
of data attributes entity at that point. The topmost node in the structure is called the root. Nodes succeeding lower levels are called children.

b) Network Model: The Network Model, also called as the CODSYL database
structure, is an improvement over the Hierarchical mode, in this model concept of parent and child is expanded to have multiple parent-child relationships, i.e. any child can be subordinate to many different parents (or nodes). Data is represented by
collection of records, and relationships among data are represented by
links. A link is an association between precisely two records. Many-to-many relationships can exists between the parent and child.

c) Relational Model: The Relational Database Model eliminates the need
for explicit parent-child relationships. In RDBMS, data is organized in two-dimensional tables consisting of relational, i.e. no pointers are maintained between tables.

8.         WHAT IS DATA MODELING?
Ans: Data Modeling describes relationship between the data objects. The

relationships between the collections of data in a system may be graphically represented using data modeling.
9.         DEFINE ENTITY, ATTRIBUTE AND RELATIONSHIP.
Ans: Entity: An Entity is a thing, which can be easily identified. An entity is any object, place, person, concept or activity about which an enterprise records data.
Attribute: An attribute is the property of a given entity.
Relationship: Relationship is an association among entities.

10.       WHAT IS ER-MODELING?
Ans: The E-R modeling technique is the Top Down Approach. Entity
relationship is technique for analysis and logical modeling of a system’s data requirements. It is the most widely used and has gained acceptance as the ideal database design. It uses three basic units: entities, their attributes and the relationship that exists between
 the entities. It uses a graphical notation for representing these.

11.       WHAT IS NORMALIZATION?
Ans: Normalization is a step-by-step decomposition of complex records
into simple records.

12.       WHAT ARE VARIOUS NORMAL FORMS OF DATA?
Ans: The First Normal Form   1NF,
The Second Normal Form       2NF,
The Third Normal Form          3NF,
The Boyce and Codd Normal Form     BC NF.

13.       WHAT IS DENORMALIZATION?
Ans: The intentional introduction of redundancy to a table to improve
performance is called DENORMALIZATION.

14.       WHAT ARE 1-TIER, 2-TIER, 3-TIER OR N-TIER DATABASE ARCHITECTURES?
Ans: 1-Tier Database Architecture is based on single system, which acts
as both server and client.
2-Tier Architecture is based on one server and client.
3-Tier Architecture is based on one server and client out that on
client act as a remote system.
N-Tier Architecture is based on N no. Of servers and N no. Of clients.

15.       WHAT ARE A TABLE, COLUMN, AND RECORD?
Ans: Table:  A Table is a database object that holds your data. It is
made up of many columns. Each of these columns has a data type associated with it.
Column: A column, referred to as an attribute, is similar to a field in
the file system.
Record: A row, usually referred to as tuple, is similar to record in
the file system.

16.       WHAT IS DIFFERENCE BETWEEN A PROCEDURAL LANGUAGE AND A
NON-PROCEDURAL LANGUAGE?
Ans:
Procedural Language  NON-Procedural Language
A program in this implements a step-by-step algorithm to solve the
problem. It contains what to do but not how to do .

17.WHAT TYPE OF LANGUAGE  "SQL" IS?
Ans: SQL is a Non-procedural, 4th generation Language,/ which concerts
what to do rather than how to do any process.

18.       CLASSIFICATION OF SQL COMMANDS?
Ans:
DDL (Data Definition Language)         DQL [Data Querying Lnaguage ]
DML (Data Manipulating Language)  DCL (Data Control Language)
TCL(Data Transaction Language)
Create  Alter Drop Truncate Rename, Select , Insert  Update Delete Merge , Grant Revoke , Rollback Commit savepoint

19.       WHAT IS DIFFERENCE BETWEEN DDL AND DML COMMANDS?
Ans: For DDL commands autocommit is ON implicitly whereas For DML
commands autocommit is to be turned ON explicitly.

20.       WHAT IS DIFFERENCE BETWEEN A TRANSACTION AND A QUERY?
Ans: A Transaction is unit of some commands where as Query is a single
line request for the information from the  database.

21.       WHAT IS DIFFERENCE BETWEEN TRUNCATE AND DELETE COMMANDS?
Ans: Truncate Command will delete all the records where as Delete
Command will delete specified or all the records depending only on the condition given.

22.       WHAT IS DIFFERENCE BETWEEN UPDATE AND ALTER COMMANDS?
Ans: Alter command is used to modify the database objects where as the
Update command is used to modify the values of a data base objects.

23.       WHAT ARE COMMANDS OF DCL CATEGORY?
Ans: Grant and Revoke are the two commands belong to the DCL Category.

24.       WHICH IS AN EFFICIENT COMMAND - TRUNCATE OR DELETE? WHY?
Ans: Delete is the efficient command because using this command we can
delete only those records that are not really required.

25.       WHAT ARE RULES FOR NAMING A TABLE OR COLUMN?
Ans: 1) Names must be from 1 to 30 bytes long.
2) Names cannot contain quotation marks.
3) Names are not case sensitive.
4) A name must begin with an alphabetic character from your database
character set and the characters $ and #.
 But these characters are discouraged.
5) A name cannot be ORACLE reserved word.
6) A name must be unique across its namespace. Objects in the name
space must have different names.
7) A name can be enclosed in double quotes.

26.       HOW MANY COLUMNS CAN A TABLE HAVE?
Ans: A Table can have 1000 columns.

27.       WHAT ARE DIFFERENT DATATYPES SUPPORTED BY SQL?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data
types for character values,
Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values, Date data type for date values,
Long, Raw (size), Long Raw, Clob, Blob, Nclob, Bfile for large objects.

28.       WHAT IS DIFFERENCE BETWEEN LONG AND LOB DATATYPES?
Ans:
LOB     LONG
1) The maximum size is 4GB.
2) LOBs (except NCLOB) can be attributes of an object type.
3) LOBs support random access to data.
4) Multiple LOB columns per table or LOB attributes in an object type.      
1) The maximum size is 2GB.  2) LONGs cannot.    3) LONGs support only
sequential access.
4) Only one LONG column was allowed in a table
29.       WHAT IS DIFFERENCE BETWEEN CHAR AND VARCHAR2 DATATYPES?
Ans: Varchar2 is similar to Char but can store variable no. Of
characters and while querying the table varchar2  trims the extra spaces from the column and fetches the rows that exactly match the criteria.

30.  HOW MUCH MEMORY IS ALLOCATED FOR DATE DATATYPE? WHAT IS DEFAULT
DATE  FORMAT IN ORACLE?

Ans: For Date data type oracle allocates 7 bytes Memory.
  Default Date Format is: DD-MON-YY.

31.       WHAT IS RANGE FOR EACH DATATYPE OF SQL?
Ans:
Datatype          Range
Char   Varchar2  Number    Float     LONG, RAW, LONGRAW  Large Objects
(LOB’s) 2000 bytes  4000 bytes 
Precision 1 to 38 Scale -84 to 127  Precision 38 decimals Or 122 binary
precision   2 GB  4GB

32.       HOW TO RENAME A COLUMN?
Ans: We can’t rename a Column of a table directly. So we follow the
following steps.
To Rename a Column:
a)         Alter the table specifying new column name to be given and data type.
b)         Then copy the values in the column to be renamed into new column.
c)         drop the old column.

33.       HOW TO DECREASE SIZE OR CHANGE DATATYPE OF A COLUMN?
Ans: To Decrease the size of a Data type of a column
i.          Truncate the table first.
ii.         Alter the table column whose size is to be decreased using the same
name and data type but new size.

34.       WHAT IS A CONSTRAINT? WHAT ARE ITS VARIOUS LEVELS?
Ans: Constraint: Constraints are representators of the column to
enforce data entity and consistency.There r two levels
1)Column-level constraints 2)Table-level constraints.

35.       LIST OUT ALL THE CONSTRAINTS SUPPORTED BY SQL.
Ans: Not Null, Unique, Check, Primary Key and Foreign Key or Referential Integrity.

36.       WHAT IS DIFFERENCE BETWEEN UNIQUE+NOT NULL AND PRIMARY KEY?
Ans: Unique and Not Null is a combination of two Constraints that can be present any number of times in a table and can’t be a referential key to any column of an another table where as Primary Key is single Constraint that can be only once for table and can be a referential key to a column of another table becoming a referential integrity.

37.       WHAT IS A COMPOSITE PRIMARY KEY?
Ans: A Primary key created on combination of columns is called Composite Primary Key.

38.       WHAT IS A CANDIDATE COLUMN? HOW MANY CANDIDATE COLUMNS CAN BE
POSSIBLE PER COMPOSITE PRIMARY KEY?
Ans: It is a part of composite primary key.  Maximum 32 candidate key can be there in composite primary key.

39.       HOW TO DEFINE A NULL VALUE?
Ans: A NULL value is something which is unavailable, it is neither zero
nor a space and any mathematical calculation with NULL is always NULL.
 40. WHAT IS NULL?  A CONSTRAINT OR DEFAULT VALUE?
Ans: It is a default value.

41. WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?
Ans: NULL.

42. WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY
COLUMNS?
Ans: Index.

43. WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?
Ans: In this we can't specify Pseudo Columns like sysdate etc.

44. WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?
Ans: References is used as column level key word where as foreign key
is used as table level constraint.

45. WHAT IS "ON DELETE CASCADE"?
Ans: when this key word is included in the definition of a child table
then     whenever the records from the parent table is deleted automatically the respective values in the child table will be deleted.

46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?
Ans: A table which references a column of another table(using References)is called  as a child table(detail table) and a table  which is being referred  is called Parent (Master) Table .

47. HOW TO DROP A PARENT TABLE WHEN IT’S CHILD TABLE EXISTS?
Ans: Using "on delete cascade".

48. IS ORACLE CASE SENSITIVE?
Ans: NO

49. HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?
Ans: By Creating indexes and reference IDs.

50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?
Ans: Columns that are not created explicitly by the user and can be
used explicitly in queries  are called Pseudo-Columns.
Ex:currval,nextval,sysdate,new,old,sqlcode,sqlerrm,rownum,rowid,level

51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?
Ans: To arrange the query result in a specified
Order (ascending,descending) by default it takes ascending order.

52. WHAT IS "GROUP BY" QUERIES?
Ans: To group the query results based on condition.

53. NAME SOME AGGREGATE FUNCTIONS OF SQL?
Ans: AVG, MAX, SUM, MIN,COUNT.

54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?
Ans: Count () will count the specified column whereas count (*) will
count total no. of rows in a table.

55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?
Ans: To get subtotals and grand total of values of a column.

56. WHAT IS A SUB-QUERY?
Ans: A query within a query  is called a sub query where the result of
inner query will be used by the  outer query.



57. WHAT ARE SQL OPERATORS?
Ans: Value (), Ref () is SQL operator. ( Used with Objects )

 58. EXPLAIN "ANY","SOME","ALL","EXISTS" OPERATORS?
  Ans: Any: The Any (or it’s synonym SOME) operator computes the lowest
value from the set and compares a value to each returned by a sub query.

All: ALL compares a value to every value returned by SQL.
Exists: This operator produces a BOOLWAN results. If a sub query
produces any result then it evaluates it to TRUE else it evaluates it to FALSE.

59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A NORMAL
SUB QUERY?
Ans: A correlated subquery is a nested subquery, which is executed once
for each ‘Candidate row’ by the main query, which on execution uses a value from a column in the outer query. In normal sub query the result of inner query is dynamically substituted in the condition of the outer query where as in a correlated subquery, the column
value used in inner query refers to the column value present in the
outer query forming a correlated subquery.

 60. WHAT IS A JOIN - TYPES OF JOINS?
Ans: A join is used to combine two or more tables logically to get
query results.

    There are four types of Joins namely
     EQUI Join
     NON-EQUI Join
     SELF Join
     OUTER Join.

  61. WHAT ARE MINIMUM REQUIREMENTS FOR AN EQUI-JOIN?
Ans: There shold be atleast one common column between the joining tables.

  62. WHAT IS DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right)  based on the requirement. 

  63. WHAT IS DIFFERENCE BETWEEN EQUI AND SELF JOINS?
Ans: SELF JOIN is made within the table whereas
     EQUI JOIN is made between  different tables having common column.

  64. WHAT ARE "SET" OPERATORS?
Ans: UNION ALL,UNION, INTERSECT ,MINUS are SET OPERATORS.

  65. WHAT IS DIFFERENCE BETWEEN "UNION" AND "UNION ALL"
OPERATORS?
Ans: UNION will return the values distinctly whereas UNION ALL will
return even duplicate values.

  66. NAME SOME NUMBER, CHARACTER, DATE, CONVERSION, OTHER
FUNCTIONS.
Ans: Number Functions:
                Round (m, [n]),  Trunc (m, [n]),  Power (m, n),  Sqrt(n),
                Abs (m), Ceil (m),  Floor (m), Mod (m, n) ,sign(n)                                 

      Character Functions:
                Chr (x), Concat (string1, string2), Lower (string)
                      Upper (string), Substr (string, from_str, to_str), ASCII (string)
                      Length (string), Initcap (string). 

   

  Date Functions:
                 Sysdate, Months between (d1, d2), To_char (d, format)
                 Last day (d), Next_day (d, day).add_months(d,n), Extract
      Conversion Functions:     To_char,  To_date, To_number

  67. WHAT IS DIFFERENCE BETWEEN MAX () AND GREATEST () FUNCTIONS?
Ans: MAX is an aggregate function which takes only one column name of a table as parameter whereas Greatest is a general function which can take any number of values and column names from dual and table respectively.

  68. WHAT FOR NVL () FUNCTION IS?
Ans: NVL Function helps in substituting a value in place of a NULL.

  69. WHAT FOR DECODE () FUNCTION IS?
Ans: It is substitutes value basis and it actually does an
'if-then-else' test.

  70. WHAT IS DIFFERENCE BETWEEN TRANSLATE () AND REPLACE () FUNCTIONS?
Ans: Translate()   is a superset of functionality provided by Replace().

  71. WHAT IS DIFFERENCE BETWEEN SUBSTR () AND INSTR () FUNCTIONS?
Ans: Substr() will return the specified part of a string whereas
  
  Instr() return the position of the specified part of the string.

  72. WHAT IS A JULIAN DAY NUMBER?
Ans: It will return count of the no. Of days between January 1, 4712 BC
and the given date.

  73. HOW TO DISPLAY TIME FROM A DATE DATA?
Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.

  74. HOW TO INSERT DATE AND TIME INTO A DATE COLUMN?
Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date() function.

  75. WHAT IS DIFFERENCE BETWEEN TO_DATE () AND TO_CHAR () CONVERSION
FUNCTIONS?
Ans: To_date converts character date to date format whereas
     To_char function converts date or numerical values to characters.

  76. WHAT IS A VIEW? HOW IT IS DIFFERENT FROM A TABLE?
Ans: View is database object, which exists logically but contains no
physical data and manipulates the base table.
View is saved as a select statement in the database and contains no
physical data whereas Table exists physically.

  77. WHAT IS DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?
Ans: Simple views can be modified whereas Complex views (created based
on more than one table) cannot be modified.

  78. WHAT IS AN INLINE VIEW?
Ans: Inline view is basically a subquery with an alias that u can use
like a view inside a SQL statement. It is not a schema object like SQL-object.

  79. HOW TO UPDATE A COMPLEX VIEW?
Ans: Using  'INSTEAD OF' TRIGGERS Complex views can be Updated.

  80. WHAT FOR "WITH CHECK OPTION" FOR A VIEW?
Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r performed through the view r not allowed to create rows  which the view cannot select and therefore allows integrity constraints and data validation checks to be enforced on data being inserted or updated.

81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX
Ans: An Index is a database object used n Oracle to provide quick
access to rows in a table. An Index increases the performance of the database.

  82. WHAT IS A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?
Ans: Sequence is a Database Object used to generate unique integers  to use as primary keys. Nextval, Currval are the Pseudo Columns associated with the sequence.

**83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER
WHEN CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the user. Clustering is a
method of storing tables that are intimately related and are often joined together into the same area on the disk.
When cluster table exists then to drop cluster we have to drop the table first then only cluster is to be dropped.

  84. WHAT IS A SNAPSHOT OR MATERIALIZED VIEW?
Ans: Materialized views can be used to replicate data. Earlier the data
was replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym for CREATE SNAPSHOT. Query performance is improved using the materialized view as these views pre calculate expensive joins and aggregate operations on the table.

85. WHAT IS A SYNONYM?
Ans:  A Synonym is a database object that allows you to create alternate names for Oracle tables and views. It is an alias for a table, view, snapshot, sequence, procedure, function or
package.

  86. WHAT IS DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?
Ans: Only the user or table owner can reference Private synonym whereas
any user can reference the Public synonym.

  87. WHAT IS DIFFERENCE BETWEEN "SQL" AND "SQL*PLUS" COMMANDS?
Ans:  SQL commands are stored in the buffer whereas SQL*PLUS are not.

**88. NAME SOME SQL*PLUS COMMANDS?
Ans:     DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.

  89. WHAT ARE "SQL*PLUS REPORTING" COMMANDS?
Ans: SPOOL file-name, SPOOL OFF, TTITLE, BTITLE, BREAK ON, COMPUTE <any
aggregate function> OF <column name> [break] ON <column name> etc are SQL*PLUS REPORTING COMMANDS.

  90. WHAT ARE SYSTEM AND OBJECT PRIVILEGES?
Ans: Connect and Resource etc are System Privileges.
Create <object>, Select, Insert, Alter etc are Object Privileges.
           
  91. WHAT FOR DCL COMMANDS ARE?
Ans: Commit, Rollback are DCL commands.

  92. WHAT FOR GRANT COMMAND WITH "WITH GRANT OPTION"?
Ans: “With Grant Option” with Grant Command gives privileges to the
user to grant privileges to other user(s)
among the privileges he/she has.

  93. HOW TO CHANGE PASSWORD OF A USER?
Ans: Using Password command or
Using ALTER USER <user name> IDENTIFIED BY <new password> COMAND.

94. WHAT IS A SCHEMA AND SCHEMA OBJECTS?
Ans: A schema is a collection of logical structures of data, or schema


objects. A schema is owned by the database user and has the same name as that of user. Each user owns a single schema. Schema objects include following
type of objects Clusters, Database Links, Functions, Indexes, Packages,
Procedures, Sequences, Synonyms, Tables, Database Triggers, Views.

  **95. HOW TO STARTUP AND SHUTDOWN ORACLE DATABASE?
Ans: Startup and Shutdown Oracle database can be done by only the
administator. Startup is done by using STARTUP command and Shutdown is done by SHUTDOWN command

  96. WHAT IS A SESSION?
Ans: The period between Login and Logoff on schema.

  97. WHAT IS A CLIENT PROCESS? WHAT IS A SERVER PROCESS?
Ans: ref: 172 Q & A.

  98. HOW TO MAKE EVERY DML OPERATION AS AUTO COMMIT?
Ans: By using SET AUTOCOMMIT ON command.

  99. HOW TO DISPLAY DATA PAGE WISE IN SQL?
Ans: By using SET PAUSE ON command.

100. HOW TO CHANGE LINE SIZE, PAGE SIZE AND SQL PROMPT?
Ans: By using SET LINESIZE <value>, SET PAGESIZE <value>,
     SET SQLPROMPT <new prompt>.

101. HOW PL/SQL IS DIFFERENT FROM SQL?
Ans: SQL is non-procedural language whereas PL/SQL is procedural
language that includes features and design of programming language.

102. WHAT IS ARCHITECTURE OF PL/SQL?
Ans:   Give picture & Explain

103. WHAT IS A PL/SQL BLOCK?
Ans:     DECLARE
               <declarations>
            BEGIN
               <Exececutable Statements>
            EXCEPTION
               <Exception Handler(s)>
            END;

104. WHAT ARE DIFFERENT TYPES OF PL/SQL BLOCKS?
Ans: DECLARE BLOCK: In this block all the declarations of the variable
used in the program is made. If no variables are used this block will become optional.
          BEGIN BLOCK: In this block all the executable statements are
          placed. This block is Mandatory.
          EXCEPTION BLOCK: In this block all the exceptions are handled.
                     This block is also very optional.
          END: Every begin must be ended with this END; statement.

  Anonymous & Named Blocks


105. WHAT ARE COMPOSITE DATA TYPES?
Ans: Records, Tables are two Composite data types.

106. WHAT IS SCOPE OF A VARIABLE IN PL/SQL BLOCK?
Ans: The visuability and accessibility of a variable within the
block(s) is called scope of a variable.

107. WHAT IS A NESTED BLOCK?
Ans: A block within a block is called Nested Block.


108. WHAT IS A PL/SQL ENGINE?
Ans:  The PL/SQL engine accepts any valid PL/SQL block as input, executes the procedural part of the statements and sends the SQL statements to the SQL statement executor in the Oracle server.

109. WHAT IS DEFAULT VALUE FOR A NUMERIC PL/SQL VARIABLE?
Ans: NULL

110. WHAT IS DIFFERENCE BETWEEN SIMPLE LOOP AND A FOR LOOP?
Ans: Simple requires declaration of variables used in it and exit
condition but For Loop doesn’t require this.

111. WHAT IS A CURSOR? STEPS TO USE A CURSOR?
Ans: Cursor is Private SQL area in PL/SQL.
     Declare the Cursor,
     Open the Cursor,
     Fetch values from SQL into the local Variables,
     Close the Cursor.

112. HOW MANY TYPES OF CURSORS ARE SUPPORTED BY ORACLE?
Ans:  There are two types of cursors namely Implicit Cursor, Explicit Cursor.

113. WHAT IS A CURSOR FOR LOOP?
Ans: Cursor For Loop is shortcut process for Explicit Cursors because
the Cursor is Open, Rows are fetched once for each iteration and the cursor is closed automatically when all the rows have been processed.

114. WHAT ARE CURSOR ATTRIBUTES?
Ans: %Found
     %NotFound
     %IsOpen
     %RowCount are the cursor attributes.

115. WHAT IS USE OF CURSOR WITH "FOR UPDATE OF" CLAUSE?
Ans: This Clause stop accessing of other users on the particular
columns used by the cursor until the COMMIT is issued.

116. WHAT IS AN EXCEPTION? HOW IT IS DIFFERENT FROM ERROR?
Ans: Whenever an error occurs Exception raises. Error is a bug whereas the Exception is a warning or error condition.

117. NAME SOME BUILT-IN EXCEPTIONS.
Ans: Too_Many_Rows,  No_Data_Found,   Zero_Divide,    Not_Logged_On
     Storage_Error,    Value_Error etc.

118. HOW TO CREATE A USER-DEFINED EXCEPTION?
Ans: User-Defined Exception is created as follows:
      DECLARE
            <exception name> EXCEPTION;
            - - - - - - - - - ;
            - - - - - - - - -;
            BEGIN
            - - - - - - - - -;
            - - - - - - - - -;
            RAISE <exception name>;
            EXCEPTION
            WHEN <exception name> THEN
            - - - - - - - - -;
            - - - - - - - - -;
            END;


119. WHAT IS "OTHERS" EXCEPTION?
Ans: It is used to along with one or more exception handlers.
     This will handle all the errors not already handled in the block.

120. WHAT IS SCOPE OF EXCEPTION HANDLING IN NESTED BLOCKS?
Ans: Exception scope will be with in that block in which exception
handler is written.

121. WHAT IS A SUB-PROGRAM?
Ans: A SUBPROGRAM IS A PL/SQL BLOCK, WHICH WILL BE INVOKED BY TAKING
PARAMATERS.

122. WHAT ARE DIFFERENT TYPES OF SUB-PROGRAMS?
Ans: THEY R TWO TYPES: 1) PROCEDURE 2) FUNCION.

123. HOW A PROCEDURE IS DIFFERENT FROM A FUNCTION?
Ans: Function has return key word and returns a value whereas a
Procedure doesn’t return any value.

124. WHAT ARE TYPES OF PARAMETERS THAT CAN BE PASSED TO FUNCTION OR
PROCEDURE?
Ans: IN, IN OUT, OUT.

125. WHAT IS "IN OUT" PARAMETER?
Ans: A parameter, which gets value into the Procedure or Function and
takes the value out of the Procedure or
Function area, is called IN OUT parameter.

126. DOES ORACLE SUPPORTS PROCEDURE OVERLOADING?
Ans:  NO.

127. WHAT IS A PACKAGE AND PACKAGE BODY?
Ans: Package is declarative part of the functions and procedures stored
in that package and package body is
the definition part of the functions and procedures of that package.

128. WHAT IS ADVANTAGE OF PACKAGE OVER PROCEDURE OR FUNCTION?
Ans: Packages provides Functions or Procedures Overloading facility and
security to those Functions or
Procedures.

129. IS IT POSSIBLE TO HAVE A PROCEDURE AND A FUNCTION WITH THE SAME
NAME?
Ans: NO if it is out side a Package, YES if it is within a Package.

130. DOES ORACLE SUPPORTS RECURSIVE FUNCTION CALLS?
Ans: YES.

131. WHAT IS A TRIGGER? HOW IT IS DIFFERENT FROM A PROCEDURE?
Ans: Trigger:  A Trigger is a stored PL/SQL program unit associated
with a specific database table.
     Procedure: A Procedure is to be explicitly called by the user
whereas Triggers are automatically called implicitly
    by Oracle itself whenever event Occurs.

132. WHAT IS DIFFERENCE BETWEEN A TRIGGER AND A CONSTRAINT?
Ans: Constraints are always TRUE whereas Triggers are NOT always TRUE
and Constraints has some limitations whereas Trigger has no limitations.

133. WHAT ARE DIFFERENT EVENTS FOR A TRIGGER AND THEIR SCOPES?
Ans: Insert, Update or Delete.
  
134. WHAT IS DIFFERENCE BETWEEN TABLE LEVEL AND ROW LEVEL TRIGGERS?
Ans: Table level Triggers execute once for each table based transaction
whereas Row level Triggers will execute once FOR EACH ROW.

** 135. WHAT ARE AUTONOMOUS TRIGGERS?
Ans: Supports to provide Commit statement in Triggers. Triggers a declared as independent
         Transactions.

136. WHAT IS AN "INSTEAD OF" TRIGGER?
Ans: These Triggers are used with the Complex Views only to make
possible of Insert, Update and Delete on those Views.

** 137. HOW MANY TRIGGERS CAN BE CONFIGURED ON A TABLE AND VIEW?
Ans: 18 Triggers

138. WHAT IS "TABLE MUTATING" ERROR? HOW TO SOLVE IT?
Ans: ORA-04091:         Table name is mutating, trigger/function may not see it
Cause : A trigger or a user-defined PL/SQL function that is referenced
in the statement attempted to query or modify a table that was in the middle of being modified by the statement that fired the trigger.
Action : Rewrite the trigger or function so it does not read the table.

139. WHEN TO USE ":NEW" AND ":OLD" SPECIFIERS?
Ans:  The prefix :old is used to refer to values already present in the
table. The prefix :new is a correlation name that refers to the new value that  is inserted / updated.

** 141. HOW TO CREATE A USER-DEFINED VARIABLE IN PL/SQL?
Ans:  Define variable in declaration section

142. HOW TO CREATE AN ARRAY VARIABLE IN PL/SQL?
Ans: Using CREATE [OR REPLACE] TYPE <type name>
AS VARRAY (size) OF ELEMENT_TYPE (NOT NULL) Command; 

**143. HOW TO MAKE A USER-DEFINED DATA TYPE GLOBAL IN PL/SQL?
Ans: Declare the variable in a Package

144. HOW TO CREATE AN OBJECT IN ORACLE?
Ans: Using CREATE [OR REPLACE] TYPE <type name> AS OBJECT (ATTRIBUTE
NAME DATA TYPE,..) Command  

145. WHAT IS A TRANSIENT AND PERSISTENT OBJECT?
Ans: The Object created in a table is called Persistent Object.
     Object created on execution of PL/SQL block is called Transient Object.

**146. WHAT IS A COLUMN OBJECT AND TABLE OBJECT?
Ans: A Column Object is only a Column of a table.
  
147. HOW TO GRANT PERMISSION ON AN OBJECT TO OTHER USER?
Ans: GRANT <permission> ON <object name> TO <user name>.

148. WHAT IS A COLLECTION OF ORACLE?
Ans: Varray, Nested Table is a collection of Oracle.

149. WHAT IS DIFFERENCE BETWEEN VARRAY AND NESTED TABLE?
Ans: Varray has a fixed size.
     Nested tables can carry any number of values

150. HOW TO MODIFY CONTENTS OF A VARRAY IN ORACLE?
Ans: To modify a stored VARRAY it has to selected into a
     PL/SQL variable and then inserted back into the table.

151. WHAT IS USE OF "THE" OPERATOR FOR NESTED TABLE?
Ans: THE operator allows nested tables to be manipulated using DML when
it is stored in a Table.

152. WHICH PACKAGE IS USED FOR FILE INPUT/OUTPUT IN ORACLE?
Ans: UTL_FILE Package is used for File input/output in Oracle.

153. NAME SOME METHODS AND PROCEDURES OF FILE I/O PACKAGE?
Ans: FOPEN, FCLOSE,  FFLUSH, IS_OPEN, GET_LINE, PUT_LINE, PUTF, NEW_LINE

**154. WHAT IS SQLJ? HOW IT IS DIFFERENT FROM JDBC CONNECTIVITY?
Ans: SQLJ is basically a Java program containing embedded static SQL
statements that are compatible with Java design philosophy.

155. WHAT IS AN ITERATOR? Name some TYPES OF ITERATORS?
Ans: SQLJ Iterators are basically record groups generated during
transaction, which requires manipulation of more than one records from one or more tables. There are two types Iterators namely Named Iterator and Positional Iterator.

** 156. WHAT ARE DIFFERENT STEPS TO WRITE A DYNAMIC SQL PROGRAM?
Ans:  Eg: char c_sqlstring[]={“DELETE FROM sailors WHERE rating>5”};
EXEC SQL PREPARE readytogo FROM :c_sqlstring;
EXEC SQL EXECUTE readytogo;

157. WHAT IS TABLE PARTITIONING AND INDEX PARTITIONING?
Ans: Oracle8 allows tables and Indexes to be partitioned or broken up into smaller parts based on range of key values. Partitioning is a “divide and conquer” strategy that improves administration and performance in data warehouse and OLTP systems.

159. WHAT IS PHYSICAL MEMORY STRUCTURE OF ORACLE?
Ans: The basic oracle memory structure associated with Oracle includes:
Software Code Areas The System Global Area (SGA) ,The Database Buffer Cache
The shared Pool, The Program Global Areas (PGA), Stack Areas ,Data Areas, Sort Areas

160. WHAT IS LOGICAL MEMORY STRUCTURE OF ORACLE?
Ans: Database, Tablespace , DB Object, Segment, Extents

161. WHAT IS SGA?
Ans: A System Global Area is a group of shared memory allocated by
Oracle that contains data and control information for one Oracle database instance. IF the multiple users are concurrently connected to the same instance, the data in the instance’s SGA is “shared” among the users.
Consequently, the SGA is often referred to as either the “system Global Area” or the “Shared
Global Area”.

162. WHAT IS PGA?
Ans: The Program Global Area is a memory buffer that contains data and
control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the configuration of Oracle.

163. WHAT IS AN ORACLE INSTANCE?
Ans: Every time a database is started, an SGA is allocated and Oracle
background processes are started. The combination of these processes and memory buffers is called an Oracle instance.

164. WHAT ARE DIFFERENT ORACLE PROCESSES?
Ans:  A process is a “thread of control” or a mechanism in an operating
system that can be execute a series of steps. Some operating systems use terms jobs or

task. A process normally has its own private memory area in which it runs. An Oracle database system has general types of process: User Processes and Oracle Processes.

**165. WHAT IS DIFFERENCE BETWEEN PMON AND SMON?
Ans: SMON (System Monitor) performs instance recovery at instance of
startup. In a multiple instance system (one that uses the parallel server), SMON of one instance can also perform instance recovery other instance that have failed whereas The PMON (Process Monitor) performs process recovery when a user process fails.

**166. WHAT IS DIFFERENCE BETWEEN DATABASE AND TABLESPACE?
Ans:  Database is a physical Component
          Tablespace is a Logical component

167. WHAT IS JOB OF DATABASE WRITER (DBWR) PROCESS?
Ans: The Data Base Writer writes modified blocks from the database
buffer cache to the data files.

168. WHAT IS JOB OF LOG WRITER (LGWR) PROC*SS?
Ans: The Log Writer writes redo log files to disk. Redo log data is
generated in the redo log buffer of the SGA. As transactions commit and log buffer fills, LGWR writes redo entries into an online redo log file.

169. WHAT IS RECOVERER?
Ans: The Recover (RECO) is used to resolve distributed transactions that are pending due to network or system failure in a distributed database. At timed intervals, the local RECO attempts to concept to remote database and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

170. WHAT IS ARCHIVER?
Ans:  The Archiver (ARCH) copies the online redo log files to archival storage when they are full. ,ARCH is active only when a database’s redo log is used ARCHILOG mode.

** 171. WHAT IS A STORED QUERY?
Ans: VIEW

172. WHAT IS USER PROCESS AND SERVER PROCESS?
Ans: A User process is created and maintained to execute the software
code of an application program (such as PRO * Program) or an ORACLE tool (such as SQL * DBA). The User process also manages the communication
with server processes. User processes communication with the server
Processes through the program interface.

 Other processes call ORACLE processes. In a dedicated server
configuration, a server Process handles requests for a single user process. A multithread
server configuration allows many user processes to share a small number of server processes, minimizing the utilization of available system resources.

**173. WHAT IS A SELF REFERENTIAL INTEGRITY?
Ans: Table related to itself .Foreign key of the table links to primary  key of the same table.

174. WHAT IS A "RAISE" STATEMENT?
Ans: It is used to Raise Exceptions.

175. WHAT IS ROWID? HOW IT IS DIFFERENT FROM ROWNUM?
Ans: Rowid is the address of the row at where it is stored in the
database. Rownum is count of records whereas Rowid is identification of the each row.