Explore topic-wise InterviewSolutions in .

We can plan the storage for a table as how much initial extents are REQUIRED, how much can be extended next, how much % should leave FREE for managing ROW UPDATING, etc.,

We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updating, etc.,

Each block CONTAINS entries as FOLLOWS
Fixed block header
Variable block header
ROW Header, row date (multiple rows may EXISTS)
PCTEREE (% of free space for row UPDATING in future)

Each block contains entries as follows
Fixed block header
Variable block header
Row Header, row date (multiple rows may exists)
PCTEREE (% of free space for row updating in future)

This is USED to RESERVE certain AMOUNT of space in a BLOCK for expansion of ROWS.

This is used to reserve certain amount of space in a block for expansion of rows.

It is USED to SET the optimal LENGTH of a ROLLBACK segment.

It is used to set the optimal length of a rollback segment.

To manage the database level TRANSACTIONS such as MODIFICATIONS of the DATA dictionary table that record information about the FREE space usage.

To manage the database level transactions such as modifications of the data dictionary table that record information about the free space usage.

- Create a database, which implicitly creates a SYSTEM ROLLBACK segment in a SYSTEM tablespace.
- Create a SECOND rollback segment NAME R0 in the SYSTEM tablespace.
- Make new rollback segment available (after SHUTDOWN, modify init.ora file and start database)
- Create other tablespaces (RBS) for rollback segments.
- DEACTIVATE rollback segment R0 and activate the newly created rollback segments.

- Create a database, which implicitly creates a SYSTEM rollback segment in a SYSTEM tablespace.
- Create a second rollback segment name R0 in the SYSTEM tablespace.
- Make new rollback segment available (after shutdown, modify init.ora file and start database)
- Create other tablespaces (RBS) for rollback segments.
- Deactivate rollback segment R0 and activate the newly created rollback segments.

It will TRY to fit the transaction in a CYCLIC FASHION to all existing extents. Once it found an extent is in USE then it forced to acquire a new extent (number of extents is BASED on the optimal size)

It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent (number of extents is based on the optimal size)

ROLLBACK segment dynamically extent to HANDLE larger transactions entry loads.
A single transaction may WIPEOUT all available free space in the rollback segment TABLESPACE. This prevents other user USING rollback segments.

Rollback segment dynamically extent to handle larger transactions entry loads.
A single transaction may wipeout all available free space in the rollback segment tablespace. This prevents other user using rollback segments.

By QUERYING DBA_SEGMENT table/view

By querying DBA_SEGMENT table/view

• LIMIT the NUMBER of extents in the table
• SEPARATE table from its indexes.
• ALLOCATE sufficient temporary STORAGE.

The ADVANTAGES over file system files are that I/O will be IMPROVED because Oracle is bye-passing the kernel which WRITING into disk. Disk CORRUPTION will be very less.

The advantages over file system files are that I/O will be improved because Oracle is bye-passing the kernel which writing into disk. Disk corruption will be very less.

SHUTDOWN the database
Copy ONE of the existing controlfile to NEW location
Edit Config ora file by ADDING new control filename
Restart the database.

Shutdown the database
Copy one of the existing controlfile to new location
Edit Config ora file by adding new control filename
Restart the database.

PROCESS of having a copy of redo log files is called MIRRORING.
This can be achieved by creating group of log files TOGETHER, so that LGWR will AUTOMATICALLY writes them to all the members of the current on-line redo log group. If any one group fails then DATABASE automatically switch over to next group. It degrades performance.

Process of having a copy of redo log files is called mirroring.
This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group. It degrades performance.

Shadow set of disks save as a BACKUP in the event of disk failure. In most operating systems if any disk failure occurs it automatically switchover to place of failed disk.

Improved performance because most OS support VOLUME shadowing can direct file I/O request to USE the shadow set of files instead of the MAIN set of files. This reduces I/O load on the main set of disks.

Shadow set of disks save as a backup in the event of disk failure. In most operating systems if any disk failure occurs it automatically switchover to place of failed disk.

Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.

They allow the database to MAINTAIN READ CONSISTENCY between multiple TRANSACTIONS.

They allow the database to maintain read consistency between multiple transactions.

It is the SET of before image data blocks that contain rows that are modified by a TRANSACTION.
Each rollback SEGMENT entry MUST be completed within one rollback segment.
A single rollback segment can have MULTIPLE rollback segment entries.

It is the set of before image data blocks that contain rows that are modified by a transaction.
Each rollback segment entry must be completed within one rollback segment.
A single rollback segment can have multiple rollback segment entries.

It is a measure of well the data cache buffer is handling REQUESTS for data.
Hit RATIO = (LOGICAL READS – PHYSICAL Reads – Hits Misses)/ Logical Reads.

It is a measure of well the data cache buffer is handling requests for data.
Hit Ratio = (Logical Reads – Physical Reads – Hits Misses)/ Logical Reads.

When Segment is DROPPED.
When Shrink (RBS only)
When TRUNCATED (TRUNCATE USED with DROP storage option)

When Segment is dropped.
When Shrink (RBS only)
When truncated (TRUNCATE used with drop storage option)

We should depend on export/import utility for backup/recovery (FULLY reliable)
The tar command cannot be used for PHYSICAL file backup, instead we can use dd command, which is LESS flexible and has limited RECOVERIES.

We should depend on export/import utility for backup/recovery (fully reliable)
The tar command cannot be used for physical file backup, instead we can use dd command, which is less flexible and has limited recoveries.

- The space used transaction entries and DELETED records, does not BECOME free immediately after completion due to delayed cleanout.
- Trailing nulls and length BYTES are not stored.
- Inserts of, updates to and deletes of rows as well as columns LARGER than a single data block, can cause FRAGMENTATION a chained row pieces.

- The space used transaction entries and deleted records, does not become free immediately after completion due to delayed cleanout.
- Trailing nulls and length bytes are not stored.
- Inserts of, updates to and deletes of rows as well as columns larger than a single data block, can cause fragmentation a chained row pieces.

A USER account is not a physical STRUCTURE in database but it is having important relationship to the objects in the database and will be having certain PRIVILEGES.

A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.

Don’t grant user ACCESS DIRECTLY to tables within the application.
Instead grant the ability to access the procedures that access the tables.
When PROCEDURE executed it will execute the privilege of procedures owner. USERS cannot access tables except via the procedure.

Don’t grant user access directly to tables within the application.
Instead grant the ability to access the procedures that access the tables.
When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.
SQL*PLUS STATEMENTS

DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.
SQL*Plus Statements

Data Definition LANGUAGE: CREATE, ALTER, DROP, TRUNCATE, REVOKE, NO AUDIT & COMMIT.
Data Manipulation Language: INSERT, UPDATE, DELETE, LOCK TABLE, EXPLAIN PLAN & SELECT.

Transactional CONTROL: COMMIT & ROLLBACK.

Session Control: ALTERSESSION & SET ROLE.

SYSTEM Control: ALTER SYSTEM.

Data Definition Language: CREATE, ALTER, DROP, TRUNCATE, REVOKE, NO AUDIT & COMMIT.
Data Manipulation Language: INSERT, UPDATE, DELETE, LOCK TABLE, EXPLAIN PLAN & SELECT.

Transactional Control: COMMIT & ROLLBACK.

Session Control: ALTERSESSION & SET ROLE.

System Control: ALTER SYSTEM.

TRUNCATE commits after DELETING entire table i.e., cannot be rolled back.
Database triggers do not FIRE on TRUNCATE
DELETE 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., cannot be rolled back.
Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted records can be rolled back or committed.
Database triggers fire on DELETE.

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.

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.

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.

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.

RETRIEVES ROWS in HIERARCHICAL order

EG: select EMPNO, ename from emp where.

Retrieves rows in hierarchical order

eg: select empno, ename from emp where.

INSTR (STRING1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the STRING 2 in string1. The SEARCH begins from nth position of string1.

SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, STARTING from n-th position of string1.

INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.

SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position of string1.

INTERSECT - returns all distinct rows SELECTED by both queries.
MINUS - returns all distinct rows selected by the first query but not by the SECOND.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query, including all duplicates.

INTERSECT - returns all distinct rows selected by both queries.
MINUS - returns all distinct rows selected by the first query but not by the second.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query, including all duplicates.

INTEGRITY CONSTRAINT is a rule that restricts values to a COLUMN in a table.

Integrity constraint is a rule that restricts values to a column in a 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.

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.

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.

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.

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

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

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

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.

- To MODIFY the datatype of a column the column MUST be EMPTY.
- To add a column with NOT NULL constrain, the table must be empty.

- To modify the datatype of a column the column must be empty.
- To add a column with NOT NULL constrain, the table must be empty.

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.

It won’t, Because SYSDATE FORMAT CONTAINS TIME ATTACHED with it.

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

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.

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.

CYCLE specifies that the sequence continue 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 continue 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.

- To protect some of the COLUMNS of a table from other users.
- To HIDE COMPLEXITY of a QUERY.
- To hide complexity of CALCULATIONS.

- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.

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.

If changes are MADE to the TABLES and these tables are the base tables of a view, then the changes will be REFERENCE on the view.

If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view.

SQL> SHOW USER;

Sql> show user;

SQL> HOST

SQL> host