Set - 9

Question 36 :

What is SAVE POINT ?

Answer :

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.
What are the values that can be specified for OPTIMIZER MODE Parameter ?
COST and RULE.


Question 37 :

Can one rename a tablespace? (for DBA)

Answer :

No, this is listed as Enhancement Request 148742. Workaround:
Export all of the objects from the tablespace
Drop the tablespace including contents
Recreate the tablespace
Import the objects


Question 38 :

What is RULE-based approach to optimization ?

Answer :

Choosing an executing planbased on the access paths available and the ranks of these access paths.


Question 39 :

What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command ?

Answer :

CHOOSE,ALL_ROWS,FIRST_ROWS and RULE.


Question 40 :

How does one create a standby database? (for DBA)

Answer :

While your production database is running, take an (image copy) backup and restore it on duplicate hardware. Note that an export will not work!!!
On your standby database, issue the following commands:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename';
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER STANDBY DATABASE;
On systems prior to Oracle 8i, write a job to copy archived redo log files from the primary database to the standby system, and apply the redo log files to the standby database (pipe it). Remember the database is recovering and will prompt you for the next log file to apply.
Oracle 8i onwards provide an "Automated Standby Database" feature, which will send archived, log files to the remote site via NET8, and apply then to the standby database.
When one needs to activate the standby database, stop the recovery process and activate it:
ALTER DATABASE ACTIVATE STANDBY DATABASE;