Explore topic-wise InterviewSolutions in .

Streams POOL is a part of System GLOBAL Area (SGA) from which MEMORY for Streams is allocated if it is configured. It can be configured by specifying initialization parameter, STREAMS_POOL_SIZE. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams pool is EQUAL to zero, then the memory used by Streams is allocated from the shared pool that may USE up to 10% of the shared pool.

Streams pool is a part of System Global Area (SGA) from which memory for Streams is allocated if it is configured. It can be configured by specifying initialization parameter, STREAMS_POOL_SIZE. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams pool is equal to zero, then the memory used by Streams is allocated from the shared pool that may use up to 10% of the shared pool.

Oracle Streams is a data replication and INTEGRATION feature. It ENABLES the propagation of data, transactions, and EVENTS in a data stream either within a DATABASE or from ONE database to another.

Oracle Streams is a data replication and integration feature. It enables the propagation of data, transactions, and events in a data stream either within a database or from one database to another.

Stream replication uses the information available in redo logs to synchronize DATA between two servers. The CAPTURE process captures the CHANGES at SOURCE, and the changes are transported to the target database, which generates the transactions and the apply process applies those transactions.

Stream replication uses the information available in redo logs to synchronize data between two servers. The capture process captures the changes at source, and the changes are transported to the target database, which generates the transactions and the apply process applies those transactions.

Database CHANGE NOTIFICATION is a SERVICE that is used to notify the registered CLIENTS WHENEVER a change is committed.

Database Change Notification is a service that is used to notify the registered clients whenever a change is committed.

ORACLE Streams are based on advance queues. The Streams PROVIDE a mechanism to synchronize data between two servers. Streams ENSURE reliable data synchronization between the servers based on the defined rules.
Both Oracle CDC and Streams are generally used for data synchronization between Oracle database servers. With Oracle CDC, you could write your own data export routines, which create FLAT files for the purpose of synchronization between two database servers; whereas, with Streams you must have a network link between the two servers.

Oracle Streams are based on advance queues. The Streams provide a mechanism to synchronize data between two servers. Streams ensure reliable data synchronization between the servers based on the defined rules.
Both Oracle CDC and Streams are generally used for data synchronization between Oracle database servers. With Oracle CDC, you could write your own data export routines, which create flat files for the purpose of synchronization between two database servers; whereas, with Streams you must have a network link between the two servers.

The Oracle Streams product from Oracle Corporation encourages users of Oracle databases to propagate information within and between databases. It provides tools to capture, PROCESS (stage), and manage DATABASE events through Advanced QUEUING queues.

The Oracle Streams product from Oracle Corporation encourages users of Oracle databases to propagate information within and between databases. It provides tools to capture, process (stage), and manage database events through Advanced Queuing queues.

CDC is a technique to capture changes in database TABLES. These changes are stored in special tables. CDC operates in two modes: asynchronous, which is based on Java, and synchronous, which is based on database triggers. Performance OVERHEAD is HIGHER in CASE of synchronous CDC.

CDC is a technique to capture changes in database tables. These changes are stored in special tables. CDC operates in two modes: asynchronous, which is based on Java, and synchronous, which is based on database triggers. Performance overhead is higher in case of synchronous CDC.

In CASE of full table scan, the optimizer ignores rowid of INDIVIDUAL migrated rows. THEREFORE, full table scans are not affected by row migration.
Index READ causes additional I/O's on migrated rows. In case of index read, rowids cannot be ignored. In such a case, the index scan will need to read at least two locations for a migrated row as compared to a single read for a non-migrated row. Therefore, index scans are ADVERSELY affected by row migration.

In case of full table scan, the optimizer ignores rowid of individual migrated rows. Therefore, full table scans are not affected by row migration.
Index read causes additional I/O's on migrated rows. In case of index read, rowids cannot be ignored. In such a case, the index scan will need to read at least two locations for a migrated row as compared to a single read for a non-migrated row. Therefore, index scans are adversely affected by row migration.

Whenever a row length increases, as a result of an update and the current block is unable to PROVIDE ADDITIONAL space, the row has to be moved to another block with sufficient space. In such situations, the original location of the row holds the ROWID of the NEW location.

Whenever a row length increases, as a result of an update and the current block is unable to provide additional space, the row has to be moved to another block with sufficient space. In such situations, the original location of the row holds the rowid of the new location.

Freelist is a BITMAP kind of STRUCTURE in the header of every SEGMENT in which every bit maps to a BLOCK in the segment. This list can be used to determine which BLOCKS are available to insert new rows.

Freelist is a bitmap kind of structure in the header of every segment in which every bit maps to a block in the segment. This list can be used to determine which blocks are available to insert new rows.

ASSM is a method used by ORACLE to manage SPACE INSIDE data blocks. It eliminates the need to specify parameters, such as PCTUSED, freelist, and freelist groups, for objects created in the tablespace.

ASSM is a method used by Oracle to manage space inside data blocks. It eliminates the need to specify parameters, such as PCTUSED, freelist, and freelist groups, for objects created in the tablespace.

PCTFREE is USED to determine the PORTION of a block that should be left un-used for FUTURE updates.

PCTFREE is used to determine the portion of a block that should be left un-used for future updates.

PCTUSED is a parameter that is used to SPECIFY the percentage required for a block to be considered as used. The default VALUE for this parameter is 40, which means that a block is considered to be used when it is more than 40% FULL and cannot be ADDED to a freelist.

PCTUSED is a parameter that is used to specify the percentage required for a block to be considered as used. The default value for this parameter is 40, which means that a block is considered to be used when it is more than 40% full and cannot be added to a freelist.

392K.

392K.

PCTINCREASE determines the SIZE of next EXTENT with RESPECT to the current extent.

PCTINCREASE determines the size of next extent with respect to the current extent.

Multiplexing is REQUIRED to improve recoverability of the database in case of disk FAILURE. You can multiplex redo log file by maintaining more than ONE member in each group.

Multiplexing is required to improve recoverability of the database in case of disk failure. You can multiplex redo log file by maintaining more than one member in each group.

Redo log group is a set of identical redo log FILES. Each log file in the group is referred to as a redo log member. A DATABASE NEEDS to maintain at least two redo log groups.

Redo log group is a set of identical redo log files. Each log file in the group is referred to as a redo log member. A database needs to maintain at least two redo log groups.

Redo log files are the disk resources to store data CHANGES. WHENEVER data is CHANGED, the information about the change is stored in the redo log file. It helps in recovery.

Redo log files are the disk resources to store data changes. Whenever data is changed, the information about the change is stored in the redo log file. It helps in recovery.

The DBA_DATA_FILES VIEW can be USED to LOOK at the SIZE of a DATAFILE.

The DBA_DATA_FILES view can be used to look at the size of a datafile.

You can RESIZE a datafile by using the ALTER DATABASE DATAFILE <file_name&GT; RESIZE STATEMENT.

You can resize a datafile by using the ALTER DATABASE DATAFILE <file_name> RESIZE statement.

Multiplexing is used to ENSURE AVAILABILITY of the CONTROL file. It MEANS CREATING multiple copies of the control file. Ideally, you should keep those copies in different physical locations so that in case of media failure, you have at least one copy of control file that can be used.

Multiplexing is used to ensure availability of the control file. It means creating multiple copies of the control file. Ideally, you should keep those copies in different physical locations so that in case of media failure, you have at least one copy of control file that can be used.

You cannot RESTORE the DATABASE if CONTROL FILE is DAMAGED.

You cannot restore the database if control file is damaged.

No. A BACKUP of CONTROL FILE GENERATES a script to CREATE a new control file

No. A backup of control file generates a script to create a new control file

Control file STORES information about LOG switches, checkpoints, and modification in DISK RESOURCES.

Control file stores information about log switches, checkpoints, and modification in disk resources.

Control FILE is REQUIRED to mount the database; therefore, the control file information should be available before MOUNTING, that is, at mount stage.

Control file is required to mount the database; therefore, the control file information should be available before mounting, that is, at mount stage.

Initialization parameter FILE or SERVER parameter file stores the INFORMATION about control file. The name of the parameter to store control file information is CONTROL_FILES.

Initialization parameter file or server parameter file stores the information about control file. The name of the parameter to store control file information is CONTROL_FILES.

Control file is a file that CONTAINS all the information about the physical structure of the DATABASE, such as the number of log files and their location.
ORACLE database server uses control file to find its physical component.

Control file is a file that contains all the information about the physical structure of the database, such as the number of log files and their location.
Oracle database server uses control file to find its physical component.

You can use the SKIP PARAMETER to skip number of RECORDS. In addition, you can use SKIP=1 to skip the header record.

You can use the SKIP parameter to skip number of records. In addition, you can use SKIP=1 to skip the header record.

You can specify the character set WE8EBCDIC500 for the EBCDIC data. The following code SNIPPET shows the SQL*Loader control file to load a fixed LENGTH EBCDIC RECORD into the Oracle database:

You can specify the character set WE8EBCDIC500 for the EBCDIC data. The following code snippet shows the SQL*Loader control file to load a fixed length EBCDIC record into the Oracle database:

The DIRECT path load loads data directly into datafiles while CONVENTIONAL path load uses standard insert statements.
There are a few RESTRICTIONS with direct path loads. The data LOADED using direct path load does not replicates.

The direct path load loads data directly into datafiles while conventional path load uses standard insert statements.
There are a few restrictions with direct path loads. The data loaded using direct path load does not replicates.

You can use DIRECT PATH load to improve the performance.
Indexes and constraints make inserts slow. REMOVING indexes and constraints improve performance of inserts; and therefore, of SQL*Loader.

You can use direct path load to improve the performance.
Indexes and constraints make inserts slow. Removing indexes and constraints improve performance of inserts; and therefore, of SQL*Loader.

You cannot ensure a COMMIT only at the end of the LOAD file but you can increase the VALUE of ROWS parameter to ensure the commit after certain NUMBER of rows.

You cannot ensure a commit only at the end of the load file but you can increase the value of ROWS parameter to ensure the commit after certain number of rows.

You can define RECORD delimiter by USING STREAM record format. It allows you to have \n as part of a field. The record delimiter is defined after the infile clause, as SHOWN in the following code SNIPPET:

You can define record delimiter by using stream record format. It allows you to have \n as part of a field. The record delimiter is defined after the infile clause, as shown in the following code snippet:

You can USE the CONCATENATE or CONTINUEIF function to join multiple physical RECORDS to form a single logical record. CONCATENATE is used when SQL*Loader should combine the same number of physical records together to form ONE logical record.
However, CONTINUEIF is used if a condition INDICATES that multiple records should be treated as one. For example, a # CHARACTER in the first column.

You can use the CONCATENATE or CONTINUEIF function to join multiple physical records to form a single logical record. CONCATENATE is used when SQL*Loader should combine the same number of physical records together to form one logical record.
However, CONTINUEIF is used if a condition indicates that multiple records should be treated as one. For example, a # character in the first column.

Yes, you can USE the WHEN clause to SPECIFY the selection criteria. However, it does not ALLOW you to use the OR clause; INSTEAD, you can only use the AND clause.

Yes, you can use the WHEN clause to specify the selection criteria. However, it does not allow you to use the OR clause; instead, you can only use the AND clause.

SQL*Loader expects a record to be in a SINGLE line; therefore, whenever it ENCOUNTERS a newline character in a record, it treats the record as a new record and either THROWS an error BASED on the constraints of a table or inserts erroneous records without throwing any error.

SQL*Loader expects a record to be in a single line; therefore, whenever it encounters a newline character in a record, it treats the record as a new record and either throws an error based on the constraints of a table or inserts erroneous records without throwing any error.

You can SAVE the data in TEXT file with proper SEPARATORS from Microsoft EXCEL. After that, SQL* Loader can be used to load the data from any other text file.

You can save the data in text file with proper separators from Microsoft Excel. After that, SQL* Loader can be used to load the data from any other text file.

You can use the following CODE SNIPPET to LOAD the FIXED length RECORDS:

You can use the following code snippet to load the fixed length records:

YES. If the datafile was in Unicode, you can USE the following CODE SNIPPET to the CONTROL file:

Yes. If the datafile was in Unicode, you can use the following code snippet to the control file:

No. You can also USE tabulation-delimited FILES by USING one of the FOLLOWING statements:

No. You can also use tabulation-delimited files by using one of the following statements:

SQL*Loader is a TOOL to LOAD DATA from file to a DATABASE table.

SQL*Loader is a tool to load data from file to a database table.

To recreate a NEW parameter FILE, you need to STORE the value of non-default PARAMETERS in the alert LOG. It can be used to get the value of non-default parameters.

To recreate a new parameter file, you need to store the value of non-default parameters in the alert log. It can be used to get the value of non-default parameters.

You can use the consistent parameter and set the value Y for this parameter to ENSURE that all the changes to the DATA are captured. However, you can use the N value to ensure that there is no transactional activity on the DATABASE during export. You NEED LARGER undo space if the Y value is being used.

You can use the consistent parameter and set the value Y for this parameter to ensure that all the changes to the data are captured. However, you can use the N value to ensure that there is no transactional activity on the database during export. You need larger undo space if the Y value is being used.

You can manage storage OPTIONS during export or IMPORT by USING the COMPRESS option. It ensures that the storage is compressed whenever the parameter value is Y.

You can manage storage options during export or import by using the compress option. It ensures that the storage is compressed whenever the parameter value is Y.

You can CREATE a cron JOB to execute the SCRIPT on a pre¬ DETERMINED schedule.

You can create a cron job to execute the script on a pre¬ determined schedule.

Cancel-based RECOVERY is a user managed incomplete recovery where a user can apply the UNTIL CANCEL clause to perform recovery until the user manually cancels the recovery process. The process can be cancelled when the user is SURE that no more recovery is possible. Cancel-based recovery is PERFORMED when there is a requirement to recover up to a particular archived redo log file. It allows a user to control the recovery process because the user can provide names of archive log files to be used for recovery. Cancel-based recovery can be performed USING the following statement:
SQL>RECOVER DATABASE UNTIL CANCEL

Cancel-based recovery is a user managed incomplete recovery where a user can apply the UNTIL CANCEL clause to perform recovery until the user manually cancels the recovery process. The process can be cancelled when the user is sure that no more recovery is possible. Cancel-based recovery is performed when there is a requirement to recover up to a particular archived redo log file. It allows a user to control the recovery process because the user can provide names of archive log files to be used for recovery. Cancel-based recovery can be performed using the following statement:
SQL>RECOVER DATABASE UNTIL CANCEL

Complete recovery uses redo data or incremental backups combined with a BACKUP of a database, datafile, or tablespace to update it to the most CURRENT point in TIME. Oracle applies all the redo changes contained in the archived and online logs to the backup.
During a complete recovery, all the changes made to the restored file SINCE the time of the backup are re-done.

Complete recovery uses redo data or incremental backups combined with a backup of a database, datafile, or tablespace to update it to the most current point in time. Oracle applies all the redo changes contained in the archived and online logs to the backup.
During a complete recovery, all the changes made to the restored file since the time of the backup are re-done.

YES, the DAMAGED BLOCKS can be RECOVERED.

Yes, the damaged blocks can be recovered.

You need to perform the FOLLOWING steps to recover the current log FILE:

•  MOUNT the database
•  Create NEW log file and DROP the damaged file
•  Recover the database
•  Open the database

You need to perform the following steps to recover the current log file:

You need to perform the following steps to RECOVER the control file:

•  START the database in the nomount mode
•  Create the control file from the control file backup and PLACE it in the correct LOCATION
•  Mount the database
•  Recover the database
•  OPEN the database

You need to perform the following steps to recover the control file: