Q1. Automatic Shared Memory Management is disabled for your database instance. You realize that there are cases of SQL statements performing poorly because of repeated parsing activity, resulting in degradation of performance.

What would be your next step to improve performance?

A. Run the SQL Access Advisor

B. Run the memory Advisor for the SGA

C. Run the memory Advisor for the PGA

D. Run the memory advisor for the shared pool

E. Run the memory advisor for the buffer cache

View Answer

Q2. The job to gather optimizer statistics for objects runs as part of the automatic maintenance window in your database instance. At a certain point of time, the maintenance window closes before the statistics are gathered for all objects.

Which statement is true in this scenario?

A. The statistics collection continues until all objects are processed.

B. The job is terminated and the statistics collected are restored to a time before the job started.

C. This produces an error and the statistics collected are locked until the next time that the maintenance window is opened.

D. The job is terminated and the statistics for the remaining objects are collected the next time that the maintenance window is opened.

View Answer

Q3. Examine the following steps performed on a database instance:

1. The DBA grants the CREATE TABLE system privilege to the SKD user with ADMIN OPTION

2. The SKD user creates a table

3. The SKD user grants the CREATE TABLE system privilege to the HR user

4. The HR user creates a table

5. The DBA revokes the CREATE TABLE system privilege from SKD

Which statement is true after step 5 is performed?

A. The table created by SKD is not accessible and SKD cannot create new tables

B. The tables created by SKD and HR remain, but both cannot create new tables

C. The table created by HR remains and HR still has the CREATE TABLE system privilege

D. The table created by HR remains and HR can grant the CREATE TABLE system privilege to other users

View Answer

Q4. Your database is open and the LISTENER listener is running. The new DBA of the system stops the listener by using the command: LSNRCTL> STOP

What happens to the sessions that are presently connected to the database instance?

A. The sessions are able to perform only queries

B. The sessions are not affected and continue to function normally

C. The sessions are terminated and the active transactions are rolled back

D. The sessions are not allowed to perform any operations till the listener is started

View Answer

Q5. Examine the following output:

Which two statements about the above index are true? (Choose two.)

A. It is ignored by the query optimizer.

B. It is not used while the index is being rebuilt.

C. The index cannot be rebuilt, and has to be re-created.

D. The index is automatically rebuilt when used the next time.

View Answer

Q6. Which two statements are true regarding the usage of the SQL*LOADER utility? (Choose two.)

A. You can load data into multiple tables during the same load session

B. You can load data from multiple files to a table during the same load session

C. You cannot perform selective data loading based on the values available in the records

D. You can use an export file generated by the EXPDP utility as an input data file to load the data

E. You can load data only if the input file is available on the disk and tape but not a named pipes

View Answer

Q7. You executed the following command to create a tablespace called SALES_DATA:

SQL> CREATE TABLESPACE sales_data

DATAFILE SIZE 100M

SEGMENT SPACE MANAGEMENT AUTO;

Which two statements are true about the SALES_DATA tablespace? (Choose two)

A. The database automatically determines the extent-sizing policy for the tablespace.

B. The segments are automatically shrunk when the contents are removed from them.

C. The allocation of extents within the tablespace is managed through the dictionary tables.

D. The space utilization description of the data blocks in segments is recorded in bitmap blocks.

E. The space utilization description of the data blocks in segments is managed through free lists.

View Answer

Q8. Identify the memory component from which memory may be allocated for:

Session memory for the shared serverBuffers for I/O slavesOracle Database Recovery Manager (RMAN) backup and restore operations

A. Large Pool

B. Redo Log Buffer

C. Database Buffer Cache

D. Program Global Area (PGA)

View Answer

Q9. You have two tables with referential integrity enforced between them. You need to insert data to the child table first because it is going to be a long transaction and data for the parent table will be available in a later stage, which can be inserted as part of the same transaction.

View the Exhibit to examine the commands used to create tables.

Which action would you take to delay the referential integrity checking until the end of the transaction?

Exhibit:

A. Set the constraint to deferred before starting the transaction

B. Alter the constraint to NOVALIDATE state before starting the transaction

C. Enable the resumable mode for the session before starting the transaction

D. Set the COMMIT_WAIT parameter to FORCE_WAIT for the session before starting the transaction

View Answer

Q10. The database instance is currently using SPFILE. View the Exhibit and examine the error that you received while running the DB Structure Integrity check. Given below are the steps to recover from the error in random order:

1. Shut down the instance, if not already done.

2. Copy one of the remaining control files to a new location.

3. Change the value of the CONTROL_FILES initialization parameter to correspond to the new location of the control files.

4. Start up the database instance to the NOMOUNT stage.

5. Recover the database to the point of failure of the control file.

6. Open the database.

Identify the correct sequence of steps?

Exhibit:

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

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

C. 4, 5, 6, 2, 3; 1 not required

D. 5, 2, 3, 4; 1 and 6 not required

View Answer