Explore topic-wise InterviewSolutions in .

The finest level of granularity of the data base are the data blocks.
Data Block :: One Data Block correspond to SPECIFIC number of physical database space.
Extent :: Extent is the number of specific number of contigious data blocks.
Segments :: SET of Extents allocated for Extents. There are three types of Segments
a) Data SEGMENT :: Non CLUSTERED Table has data segment data of every table is stored in cluster data segment.
b) Index Segment :: Each Index has index segment that STORES data.
c) Roll Back Segment :: Temporarily store 'undo' information

The finest level of granularity of the data base are the data blocks.
Data Block :: One Data Block correspond to specific number of physical database space.
Extent :: Extent is the number of specific number of contigious data blocks.
Segments :: Set of Extents allocated for Extents. There are three types of Segments
a) Data Segment :: Non Clustered Table has data segment data of every table is stored in cluster data segment.
b) Index Segment :: Each Index has index segment that stores data.
c) Roll Back Segment :: Temporarily store 'undo' information

Previleges are the RIGHT to EXECUTE a particulare type of SQL statements.

e.g :: Right to Connect, Right to create, Right to resource Grants are given to the objects so that the object MIGHT be accessed ACCORDINGLY.

The grant has to be given by the owner of the object.

Previleges are the right to execute a particulare type of SQL statements.

e.g :: Right to Connect, Right to create, Right to resource Grants are given to the objects so that the object might be accessed accordingly.

The grant has to be given by the owner of the object.

Concurrency is allowing simultaneous ACCESS of same data by DIFFERENT users. Locks useful for accesing the DATABASE are

• EXCLUSIVE: The exclusive lock is useful for locking the row when an insert,UPDATE or delete is being done.This lock should not be applied when we do only select from the row.
• Share lock: We can do the table as Share_Lock as many share_locks can be put on the same resource.

Concurrency is allowing simultaneous access of same data by different users. Locks useful for accesing the database are

CANDIDATE keys are the COLUMNS in the table that could be the primary keys and the primary key is the key that has been selected to identify the ROWS. Unique key is ALSO useful for identifying the distinct rows in the table.

Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.

Oracle does implement the OOPS concepts. The best example is the PROPERTY CLASSES. We can CATEGORIZE the properties by setting the visual attributes and then ATTACH the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects.

Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorize the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects.

Snapshots are mirror or replicas of TABLES. Views are BUILT USING the columns from one or more tables. The SINGLE Table View can be updated but the view with multi table cannot be updated.

Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated.

The various Block Coordination Properties are

• Immediate Default SETTING. The Detail records are shown when the Master Record are shown.
• Deffered with AUTO Query Oracle Forms DEFER fetching the detail records until the operator navigates to the detail block.
• Deffered with No Auto Query The operator must navigate to the detail block and EXPLICITLY EXECUTE a query.

The various Block Coordination Properties are

The various Master and Detail Relationship are

• NonIsolated :: The Master cannot be DELETED when a CHILD is exisiting
• ISOLATED :: The Master can be deleted when the child is exisiting
• CASCADING :: The child gets deleted when the Master is deleted.

The various Master and Detail Relationship are

There are Three Integrity Rules. They are as follows ::

• ENTITY Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null
• Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child TABLES the MASTER tables cannot be deleted.
• Business Integrity Rules :: The Third Intigrity rule is about the complex business processes which cannot be IMPLEMENTED by the above 2 rules.

There are Three Integrity Rules. They are as follows ::

PROCEDURES and functions CONSIST of set of PL/SQL statements that are GROUPED together as a unit to solve a specific problem or perform set of related tasks. Procedures do not Return values while Functions return ONE.

One Value Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, VARIABLES and other Package Contents

Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks. Procedures do not Return values while Functions return one.

One Value Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

A transaction is a Logical unit of WORK that compromises one or more SQL Statements executed by a single USER. According to ANSI, a transaction begins with FIRST executable statement and ends when it is explicitly COMMITTED or rolled back.

A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.

There are BASICALLY 6 TYPES of sql statments.They are

• Data Definition Language(DDL) :: The DDL statements define and maintain objects and drop objects.
• Data Manipulation Language(DML) :: The DML statements manipulate database data.
• TRANSACTION CONTROL Statements :: Manage change by DML
• Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g. :: Alter Statements, Set Role
• System Control Statements :: Change Properties of ORACLE Instance .e.g.:: Alter System
• Embedded Sql :: Incorporate DDL, DML and T.C.S in Programming Language.e.g:: Using the Sql Statements in languages such as 'C', Open, Fetch, execute and close

There are basically 6 types of sql statments.They are

Either RMAN or alter tablespace begin BACKUP will do.

Either rman or alter tablespace begin backup will do.

SELECT customer_no, payments from customer C1
WHERE 3<=(SELECT COUNT(*) from customer C2
WHERE C1.payment <= C2.payment)

SELECT customer_no, payments from customer C1
WHERE 3<=(SELECT COUNT(*) from customer C2
WHERE C1.payment <= C2.payment)

SELECT DISTINCT (a.sal) FROM EMP A WHERE &AMP;N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
For E.g.:-
Enter value for n: 2
SAL
---------
3700

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
For E.g.:-
Enter value for n: 2
SAL
---------
3700

• POPULATE_GROUP(FUNCTION)
• POPULATE_GROUP_WITH_QUERY(function)
• SET_GROUP_CHAR_CELL(PROCEDURE)
• SET_GROUPCELL(procedure)
• SET_GROUP_NUMBER_CELL(procedure)

A View can be updated/deleted/inserted if it has only ONE base table if the view is based on columns from one or more TABLES then insert, update and DELETE is not possible.

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

CHAR PADS blank spaces to the MAXIMUM LENGTH. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.

CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.

CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.

CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.

CORRELATED SUB QUERY is a sub query which has REFERENCE to the MAIN query.

Correlated sub query is a sub query which has reference to the main query.

CYCLE specifies that the sequence continues to GENERATE VALUES after reaching either maximum or MINIMUM value. After PAN ascending sequence reaches its maximum value, it GENERATES its minimum value. After a descending sequence reaches its minimum, it generates its maximum.NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

CYCLE specifies that the sequence continues to generate values after reaching either maximum or minimum value. After pan ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

Database LINK is a NAMED path through which a remote database can be accessed.

Database Link is a named path through which a remote database can be accessed.

For OEM v2 and above, start the ORACLE Enterprise Manager Configuration ASSISTANT (emca on UNIX) to create and configure the management server and REPOSITORY. REMEMBER to setup a backup for the repository database after creating it.

For OEM v2 and above, start the Oracle Enterprise Manager Configuration Assistant (emca on Unix) to create and configure the management server and repository. Remember to setup a backup for the repository database after creating it.

It won't, Because SYSDATE FORMAT CONTAINS time attached with it.

It won't, Because SYSDATE format contains time attached with it.

Join is a query which retrieves related columns or rows from MULTIPLE tables.

• Self Join - Joining the table with itself.
• Equi Join - Joining TWO tables by EQUATING two common columns.
• Non-Equi Join - Joining two tables by equating two common columns.
• Outer Join - Joining two tables in such a way that query can also RETRIEVE rows that do not have corresponding join VALUE in the other table.

Join is a query which retrieves related columns or rows from multiple tables.

Maintaining DATA integrity through a set of rules that restrict the VALUES of one or more columns of the tables based on the values of primary KEY or unique key of the referenced table.

Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

Only ONE LONG column is allowed. It is not POSSIBLE to use LONG column in WHERE or ORDER BY CLAUSE.

Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

RETRIEVES rows in hierarchical order.e.g. SELECT empno, ENAME from EMP where.

Retrieves rows in hierarchical order.e.g. select empno, ename from emp where.

ROWID is a PSEUDO column attached to each ROW of a table. It is 18 CHARACTER long, blockno, ROWNUMBER are the components of ROWID.

ROWID is a pseudo column attached to each row of a table. It is 18 character long, blockno, rownumber are the components of ROWID.

SAVEPOINTS are used to SUBDIVIDE a transaction into SMALLER parts. It ENABLES rolling back part of a transaction. Maximum of FIVE save points are allowed.

SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

SEQUENCE name CURRVAL, Sequence name NEXTVAL.It is not POSSIBLE. Only if you ACCESS next value in the session, current value can be accessed.

Sequence name CURRVAL, Sequence name NEXTVAL.It is not possible. Only if you access next value in the session, current value can be accessed.

Sub QUERY is a query WHOSE return values are used in FILTERING CONDITIONS of the main query.

Sub query is a query whose return values are used in filtering conditions of the main query.

The INTEGRITY CONSTRAINTS are STORED in USER_CONSTRAINTS.

The integrity constraints are stored in USER_CONSTRAINTS.

The INTEGRITY constraints can be enabled or DISABLED by ALTER TABLE ENABLE CONSTRAINT/DISABLE constraint.

The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE constraint/DISABLE constraint.

• To PROTECT some of the COLUMNS of a table from other users.
• To hide complexity of a query.
• To hide complexity of CALCULATIONS.

TRUNCATE commits after deleting entire table i.e., can not be rolled back. Database TRIGGERS do not fire on TRUNCATEDELETE allows the filtered DELETION. DELETED RECORDS can be rolled back or COMMITTED. Database triggers fire on DELETE.

TRUNCATE commits after deleting entire table i.e., can not be rolled back. Database triggers do not fire on TRUNCATEDELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.

USING ROWID.CONSTRAINTS.

Using ROWID.CONSTRAINTS.

When ON DELETE CASCADE is specified ORACLE maintains referential INTEGRITY by automatically removing dependent FOREIGN key values if a referenced primary or unique key value is REMOVED.

When ON DELETE CASCADE is specified ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

Should be USED before '%'.

Should be used before '%'.

• %ROWCOUNT
• %NOTFOUND
• %FOUND
• %ISOPEN

1,000,00.

1,000,00.

A cursor variable is associated with DIFFERENT statements at RUN TIME, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type (like a pointer in C).

Declaring a cursor variable:
TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will EVENTUALLY be RETURNED by the cursor variable.

A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type (like a pointer in C).

Declaring a cursor variable:
TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.

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.

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.

• Data base trigger(DBT) fires when a DML OPERATION is performed on a data base table. Form trigger(FT) Fires when user presses a key or navigates between fields on the screen.
• Can be row LEVEL or statement level No distinction between row level and statement level.
• Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as WELL as variables in forms.
• Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger.
• Can CAUSE other database triggers to fire. Can cause other database triggers to fire, but not other form triggers.

• DROP PROCEDURE procedure_name.
• Drop FUNCTION function_name.

Functions are named PL/SQL blocks that return a VALUE and can be called with ARGUMENTS PROCEDURE a named block that can be called with parameter.

A procedure all is a PL/SQL statement by itself, while a Function CALL is called as PART of an expression.

Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter.

A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

HAVING clause is USED when you WANT to specify a condition for a group function and it is written after GROUP BY clause. The WHERE clause is used when you want to specify a condition for columns, SINGLE row functions EXCEPT group functions and it is written before GROUP BY clause if it is used.

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause. The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

In PL/SQL 2.2 cursor VARIABLES cannot be declared in a package.This is because the STORAGE for a cursor variable has to be allocated using Pro*C or OCI with version 2.2, the only MEANS of passing a cursor variable to a PL/SQL block is via bind variable or a procedure PARAMETER.

In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2, the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.

• IN
• OUT
• INOUT

INSTR('120000 12 0 .125',1,'.')
OUTPUT 13

INSTR('120000 12 0 .125',1,'.')
output 13