Q1. SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;

What is the effect of this command?

A. One audit record is created for every successful DROP TABLE command executed in the session of SCOTT

B. One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users

C. One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session

D. One audit record is created for every session of any other user in which a table owned by SCOTT is dropped successfully

E. One audit record is created for every successful DROP TABLE command executed by any user to drop tables owned by SCOTT

View Answer

Q2. You execute the following command to change the status of the SALES tablespace:

SQL> ALTER TABLESPACE sales OFFLINE;

Which statements describe the effect of the command? (Choose all that apply.)

A. The tablespace would require recovery to go back online.

B. A checkpoint is taken on all data files that are associated with the SALES tablespace.

C. The sessions that subsequently try to access the objects in the SALES tablespace receivean error.

D. The new status of the SALES tablespace is recorded in the control file when the database instance is closed.

View Answer

Q3. Which two statements are true regarding transactions in an Oracle database? (Choose two.)

A. Multiple transactions can use the same undo segment.

B. A transaction is assigned an undo segment when it is started.

C. More than one transaction cannot share the same extent in the undo tablespace.

D. The transactions use system undo segment to store undo data if all the segments in the undo tablespace are used.

View Answer

Q4. You execute this command to drop the ITEM table, which has the primary key referred in the ORDERS table:

SQL> DROP TABLE scott.item CASCADE CONSTRAINTS PURGE;

Which two statements are true about the effect of the command? (Choose two.)

A. No flashback is possible to bring back the ITEM table.

B. The ORDERS table is dropped along with the ITEM table.

C. The dependent referential integrity constraints in the ORDERS table are disabled.

D. The dependent referential integrity constraints in the ORDERS table are removed.

E. The table definition of the ITEM table and associated indexes are placed in the recycle bin.

View Answer

Q5. View the Exhibit and examine the setting for a table.

Which statement is true about the PCTFREE setting for the table?

A. It sets the minimum percentage of a data block to be reserved to contain chained rows from other blocks.

B. It sets the minimum percentage of a data block to be reserved as free space before the server prevents inserts into the block.

C. It sets the minimum percentage of a data block to be reserved to contain the bitmap used to maintain the free block information.

D. It sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block.

View Answer

Q6. You execute a command to resize a data file, sales.dbf, of size 200 MB in your database:

ALTER DATABASE DATAFILE '/remorse/sales.dbf' RESIZE 150M;

Which statement is true about this command?

A. The command must be issued in MOUNT state.

B. The command succeeds only if the data file is offline.

C. The command must be issued when the database is in the archivelog mode.

D. The command succeeds if no extent in the tablespace is currently allocated above the 150 MB boundary of the file.

View Answer

Q7. You used the IMMEDIATE option to shutdown your database instance. Consider the steps that will be performed later when you open the database:

1. SGA is allocated.

2. Control file is read.

3. Redo log files are read.

4. Instance recovery is started.

5. Background processes are started.

6. Data files are checked for consistency.

7. Server parameter file or the initialization parameter file is read.

Which option has the correct order in which these steps occur?

A. 7, 1, 5, 2, 3, 6, 4

B. 1, 5, 7, 2, 3, 6; step 4 is not required

C. 7, 1, 5, 2, 3, 6 step 4 is not required

D. 1, 2, 3, 5, 6, 4; step 7 is not required

View Answer

Q8. View the Exhibit to examine the output produced by the following query at three different times since the database instance started and has experienced workloads of different capacities:

SQL> SELECT substr(component, 0, 10) COMP, current_size CS, user_specified_size US

FROM v$memory_dynamic_components

WHERE current_size!=0;

What do you infer from this?

Exhibit:

A. The database instance is running with manual PGA management.

B. The database instance is running with manual shared memory management.

C. The database instance has the MEMORY_TARGET value set to a nonzero value.

D. All sessions are connected to the database instance in dedicated mode, and no RMAN or parallel query operations have been performed.

View Answer

Q9. Identify two situations in which you can use Data Recovery Advisor for recovery. (Choose two.)

A. The database files are corrupted when the database is open

B. The user has dropped an important table that needs to be recovered

C. The archived redo log files are missing for which the backup is not available

D. The database is not opening because the required database files are missing

View Answer

Q10. You configured the Flash Recovery Area for your database. The database instance has been started in ARCHIVELOG mode and the LOG_ARCHIVE_DEST_1 parameter is not set.

What will be the implications on the archiving and the location of archive redo log files?

A. Archiving will be disabled because the destination for the redo log files is missing

B. The database instance will shut down and the error details will be logged in the alert log file

C. Archiving will be enabled and the destination for the archived redo log file will be set to the Flash Recovery Area implicitly

D. Archiving will be enabled and the location for the archive redo log file will be created in the default location $ORACLE_HOME/log

View Answer