Explore topic-wise InterviewSolutions in .

1. MMAN – memory MANAGER which helps in maintaining ASMM
2. RVWR – helps in generating FLASHBACK logs INCASE flashback is turned on
3. CTWR – hepls INCREMENTAL BACKUP in RMAN
4. MMNL – memory monitor light, which helps in AWR statistics collection
5. MMON – managebility monitor, which helps in automatic problem detection and self-tuning.

SCN is a unique number assigned to a transaction which modifies data in the DATABASE. CKPT process will ALWAYS update this SCN to both datafiles HEADER and control FILE.

SCN is a unique number assigned to a transaction which modifies data in the database. CKPT process will always update this SCN to both datafiles header and control file.

To AVOID any SPACE CONSTRAINTS.

To avoid any space constraints.

It will do instance recovery, coalesces the tablespace and will RELEASE the TEMP SEGMENTS OCCUPIED by a transaction when it is COMPLETED.

It will do instance recovery, coalesces the tablespace and will release the temp segments occupied by a transaction when it is completed.

INSTANCE is a combination of memory STRUCTURES and background processes which helps in reading/writing the DATA. DATABASE is mix of PHYSICAL and logical structures which helps in STORING user data.

INSTANCE is a combination of memory structures and background processes which helps in reading/writing the data. DATABASE is mix of physical and logical structures which helps in storing user data.

Both. If the data which to be sorted is more than SORT area SIZE of PGA, then temp tablespace will be used.

Both. If the data which to be sorted is more than sort area size of PGA, then temp tablespace will be used.

In 8i, we need to set DIFFERENT INDIVIDUAL parameters where as in 9i SETTING only ONE parameter PGA_AGGREGATE_TARGET WOULD be enough.

In 8i, we need to set different individual parameters where as in 9i setting only one parameter PGA_AGGREGATE_TARGET would be enough.

YES. Reason is EVEN tough it is a DDL externally, it will be DML to BASE tables and to rollback them always oracle requires undo.

Yes. Reason is even tough it is a DDL externally, it will be DML to base tables and to rollback them always oracle requires undo.

LGWR WRITING before DBWR is CALLED write-ahead PROTOCOL.

LGWR writing before DBWR is called write-ahead protocol.

• UNUSED
• FREE
• PINNED
• DIRTY.

A single atomic CHANCE HAPPENED to the database is called REDO entry. They will be created when we RUN any DML or DDL COMMANDS or when any changes are done in the database. They will be created in PGA.

A single atomic chance happened to the database is called redo entry. They will be created when we run any DML or DDL commands or when any changes are done in the database. They will be created in PGA.

Block is a BASIC storage unit where as EXTENT is a SMALL MEMORY area allocated to a table and segment is a object which occupies space.

Block is a basic storage unit where as extent is a small memory area allocated to a table and segment is a object which occupies space.

LOGICAL structures are defined to PROVIDE the easiness in MAINTENANCE.

Logical structures are defined to provide the easiness in maintenance.

YES, but LOAD will be more on SYSTEM TABLESPACE.

Yes, but load will be more on system tablespace.

1. SYSTEM.
2. SYSAUX.
3. TEMP.
4. UNDO.

TABLESPACE, SEGMENT, extent and oracle data BLOCK are logical structures.

Tablespace, segment, extent and oracle data block are logical structures.

If SERVER PROCESS starts searching at MRU end, there is a CHANCE that data MAY get flush from buffer cache by the IME it reaches LRU end. In that case again to fetch data, we need to do an I/O which is costly. So oracle designed its architecture that server process will search only from LRU end.

If server process starts searching at MRU end, there is a chance that data may get flush from buffer cache by the ime it reaches LRU end. In that case again to fetch data, we need to do an I/O which is costly. So oracle designed its architecture that server process will search only from LRU end.

When the same query is ran by same or different user, data can be PICKED from BUFFER CACHE THUS AVOIDING I/O and improving performance.

When the same query is ran by same or different user, data can be picked from buffer cache thus avoiding I/O and improving performance.

If we fetch DATA from database buffer cache, then its CALLED logical READ. If we fetch it from database, its called physical read as it includes an I/O operation.

If we fetch data from database buffer cache, then its called logical read. If we fetch it from database, its called physical read as it includes an I/O operation.

It is a PROCESS created to HELP the USER process EITHER in reading/writing the data in the database.

It is a process created to help the user process either in reading/writing the data in the database.

Semantic CHECKING means checking for the privileges for the USER or in other WORDS authorizing the user. Base tables or dictionary will HELP in doing this.

Semantic checking means checking for the privileges for the user or in other words authorizing the user. Base tables or dictionary will help in doing this.

SQL EXECUTION contains 3 phases:

1. Parsing – in which syntax checking, semantic checking and dividing the STATEMENT into LITERALS will be done
2. Execution – in which parsed statement will get converted into ASCII format and will be executed
3. Fetch – in which data will be fetched either from database buffer cache or database.

SQL execution contains 3 phases:

It will create necessary PACKAGES and procedures which DBA use for CERTAIN actions.

It will create necessary packages and procedures which DBA use for certain actions.

We use data dictionary views to LOOK into PERMANENT information about the DATABASE and dynamic performance views to get ongoing ACTIONS in the database. Both the views will be created after database creation USING catalog.sql script.

We use data dictionary views to look into permanent information about the database and dynamic performance views to get ongoing actions in the database. Both the views will be created after database creation using catalog.sql script.

BASE tables are dictionary information of the database. They will be created at the TIME of database creation USING SQL.BSQ script.

Base tables are dictionary information of the database. They will be created at the time of database creation using SQL.BSQ script.

PMON.

PMON.

Oracle database architecture is a combination of INSTANCE and database. Instance is a combination of MEMORY STRUCTURES and background PROCESSES which helps in reading and writing the DATA to/from the database .

Oracle database architecture is a combination of instance and database. Instance is a combination of memory structures and background processes which helps in reading and writing the data to/from the database .