Q1. 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

Q2. User SCOTT wants to perform a bulk insert operation in the EMP_DEP table. SCOTT receives the following error after the INSERT statement is issued and few rows are inserted:

INSERT INTO EMP_DEP (emp_id,name,salary,dep_name,mgr_id)

*

ERROR at line 1:

ORA-01653: unable to extend table SCOTT.EMP_DEP by 128 in tablespace USERS

Identify two actions either of which will help you resolve this problem. (Choose two.)

A. Grant the RESOURCE role to SCOTT.

B. Add data files to the USERS tablespace.

C. Grant the CREATE ANY TABLE privilege to SCOTT.

D. Increase the space for SCOTT on the USERS tablespace.

E. Increase the size of the data file associated with the USERS tablespace.

View Answer

Q3. Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.)

A. Shared SQL Area will be allocated in the shared pool

B. Shared SQL Area will be allocated when a session starts

C. Shared SQL Area will be allocated in the large pool always

D. The whole of Private SQL Area will be allocated in the Program Global Area (PGA) always

E. Shared SQL Area and Private SQL Area will be allocated in the PGA or large pool

F. The number of Private SQL Area allocations is dependent on the OPEN_CURSORS parameter

View Answer

Q4. Which tablespaces are mandatory in an Oracle database for it to be operational? (Choose all that apply.)

A. Undo tablespace

B. USERS tablespace 

C. SYSAUX tablespace 

D. SYSTEM tablespace 

E. Temporary tablespace

View Answer

Q5. View the Exhibit and examine the privileges granted to the MGR_ROLE role.

The user SKD has been granted the CONNECT and RESOURCE roles only. The database administrator (DBA) grants MGR_ROLE to the user SKD by executing the command:

SQL> GRANT MGR_ROLE TO SKD WITH ADMIN OPTION;

Which statement is true about the user SKD after he/she is granted this role?

Exhibit:

A. The user SKD can grant only the MGR_ROLE role to other users, but not the privileges in it

B. The user SKD can revoke the MGR_ROLE only from the users for whom he/she is the grantor

C. The user SKD can grant the privileges in the MGR_ROLE role to other users but not with ADMIN OPTION

D. The user SKD can grant the privileges in the MGR_ROLE role to other users, but cannot revoke privileges from them

View Answer

Q6. The instance abnormally terminates because of a power outage.

Which statement is true about redo log files during instance recovery?

A. Inactive and current redo log files are required to accomplish recovery

B. Online and archived redo files are required to accomplish instance recovery

C. All redo log entries after the last checkpoint are applied from redo log files to data files

D. All redo log entries recorded in the current log file until the checkpoint position are applied to data files

View Answer

Q7. You are managing an Oracle Database 11g database running in ARCHIVELOG mode. The Flash Recovery Area is specified as the destination for the archived redo log files. You notice this warning in the alert log file:

ORA19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available.

What would you do to reclaim the used space in the Flash Recovery Area? (Choose two.)

A. Back up the Flash Recovery Area.

B. Decrease the retention time for the database backup and flashback log files.

C. Manually delete all the archived log files from the Flash Recovery Area by using operating system (OS) commands.

D. Manually delete all the expired backup sets from the Flash Recovery Area by using operating system (OS) commands.

View Answer

Q8. You receive complaints from users regarding the high waiting time for their transactions. On investigation, you find that some users are not committing their transactions though they are not performing any activity for a long time. As a result, SQL statements need to wait for rowlevel locks.

Which two actions could you take to prevent this locking problem in the future? (Choose two.)

A. Decrease the IDLE_TIME resource limit in the profile assigned to the blocking users.

B. Use Database Resource Manager to automatically log out sessions that block others and are idle

C. Set the limit in the profile of blocking users to control the number of blocks to be accessed in a session.

D. Decrease the maximum number of interested transaction list (ITL) slots for the segments on which the blocking user performs the transaction.

View Answer

Q9. The instance abnormally terminates because of a power outage.

Which statement is true about redo log files during instance recovery?

A. Inactive and current redo log files are required to accomplish recovery

B. Online and archived redo files are required to accomplish instance recovery

C. All redo log entries after the last checkpoint are applied from redo log files to data files

D. All redo log entries recorded in the current log file until the checkpoint position are applied to data files

View Answer

Q10. 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