Q1. Your database instance is configured with automatic undo management and the UNDO_RETENTION parameter is set to 900 seconds. You executed the following command to enable retention guarantee:

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

What affect would this command have on the database?

A. The extents in the undo tablespace retain data until the next full database backup

B. The extents containing committed undo in the undo tablespace are not overwritten for at least 15 minutes

C. The extents containing committed data in the undo tablespace are not overwritten until the instance is shut down

D. The extents containing committed undo in the undo tablepace are transferred to Flash Recovery Area before being overwritten

View Answer

Q2. In your database, the current status of a PL/SQL procedure has become INVALID. Which action would fix the problem and make the PL/SQL procedure usable?

A. Recompile the PL/SQL procedure.

B. Modify the procedure to run with a definer's right.

C. Modify the procedure to run with an invoker's right.

D. Grant the EXECUTE privilege on the PL/SQL procedure to PUBLIC.

View Answer

Q3. You are working on an instance started using the SPFILE. You want to move the Flash Recovery Area of your database to a new location. You want the Flashback log files to be stored in the new location. Given below are the steps to accomplish the task in random order:

1) Shut down the instance.

2) Change the value of the DB_RECOVERY_FILE_DEST initialization parameter to a new value.

3) Execute the ALTER DATABASE FLASHBACK OFF command.

4) Start up the instance and mount the database.

5) Execute the ALTER DATABASE FLASHBACK ON command.

6) Open the database.

Select the correct order in which these tasks need to be performed.

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

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

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

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

View Answer

Q4. View the Exhibit and examine the attributes of an undo tablespace. In an OLTP system, the user SCOTT has started a query on a large table in the peak transactional hour that performs bulk inserts. The query runs for more than 15 minutes and then SCOTT receives the following error:

ORA-01555: snapshot too old What could be the reason for this error?

Exhibit:

A. The query is unable to get a read-consistent image.

B. There is not enough space in Flash Recovery Area.

C. There is not enough free space in the flashback archive.

D. The query is unable to place data blocks in undo tablespace.

View Answer

Q5. You have executed this command to change the size of the database buffer cache:

SQL> ALTER SYSTEM SET DB_CACHE_SIZE=2516582;

System altered.

To verify the change in size, you executed this command:

Why is the value set to 4194304 and not to 2516582?

A. Because 4194304 is the granule size

B. Because 4194304 is the standard block size

C. Because 4194304 is the largest nonstandard block size defined in the database

D. Because 4194304 is the total size of data already available in the database buffer cache

View Answer

Q6. Examine the commands executed in the following sequence:

Which statement is true about the above commands?

A. The commands execute successfully.

B. Command 6 produces an error because of circular role grant.

C. Command 5 produces an error because a role cannot be granted to another role.

D. Command 3 produces an error because the MGRROLE role already contains system privileges. The table created by HR remains and HR still has the CREATE TABLE system privilege.

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

View Answer

Q7. You have a large amount of historical data in a flat file. Some analysts in your organization need to query this data in the file. The file is too large to load the data in it into your current database.

Which is the most effective method to access this data in your database?

A. Use the database link.

B. Use the SQL*Loader utility.

C. Use the Oracle Data Pump utility.

D. Create an external table and leave the data in the flat file.

View Answer

Q8. Examine the following statement that is used to modify the primary key constraint on the SALES table:

SQL> ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLE VALIDATE;

Which three statements are true regarding the above command? (Choose three.)

A. The constraint remains valid.

B. The index on the constraint is dropped.

C. It allows the loading of data into the table using SQL *Loader.

D. New data conforms to the constraint, but existing data is not checked.

E. It allows the data manipulation on the table using INSERT/UPDATE/DELETE SQL statements.

View Answer

Q9. You created a profile APP_USER and assigned it to the users. After a month, you decide to drop the profile. Some user sessions are currently connected to the database instance and are using the APP_USER profile.

This command is used to drop the profile:

SQL> DROP PROFILE app_user;

Which statement describes the result?

A. The command produces an error.

B. The profile is dropped and current user sessions use the DEFAULT profile immediately.

C. The profile is dropped and only the subsequent user sessions use the DEFAULT profile.

D. The profile is dropped, the sessions are terminated, and the subsequent user sessions use the DEFAULT profile.

View Answer

Q10. Which statement is true about a whole consistent database backup on a database running in ARCHIVELOG mode?

Exhibit:

A. The backup will consist of used data blocks only.

B. The database must be shut down to accomplish the backup.

C. The backup can be accomplished without shutting down the database.

D. The backup will contain all database files that have never been backed up.

View Answer