Q1. Which three statements are correct about temporary tables? (Choose three.)

A. Indexes and views can be created on temporary tables

B. Both the data and structure of temporary tables can be exported

C. Temporary tables are always created in a user's temporary tablespace

D. The data inserted into a temporary table in a session is available to other sessions

E. Data Manipulation Language (DML) locks are never acquired on the data of temporary tables

View Answer

Q2. A database user SMITH tries to query the V$SESSION view and fails to access it as follows:

Which are the two possible solutions to enable SMITH to query the data in V$SESSION? (Choose two.)

A. granting SELECT privilege to SMITH on V$SESSION

B. granting SELECT privilege to SMITH on V_$SESSION

C. asking the user SMITH to run the catalog.sql script

D. granting SELECT privilege to SMITH on V$FIXED_TABLES

E. setting the O7_DICTIONARY_ACCESSIBILITY parameter to TRUE

F. creating a view based on V$SESSION and granting SELECT privilege to SMITH on the view that was created

View Answer

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

Q4. View the Exhibit and examine the output of the query.

What do you infer from this?

A. The SGA_TARGET is a static parameter.

B. The instance is started, but the database is not yet open.

C. The server parameter file (SPFILE) was used to start the instance.

D. The SGA_TARGET parameter does not have any effect on the database instance until the SGA_MAX_SIZE parameter is specified.

View Answer

Q5. You plan to move data from a flat file to a table in your database. You decide to use SQL*Loader direct path load method to perform this task. The table in which you plan to load data in an important table having various integrity constraint defined on it.

Which constraints will remain enabled by default during this operation? (Choose all that apply.)

A. CHECK

B. UNIQUE

C. NOT NULL

D. PRIMARY KEY

E. FOREIGN KEY

View Answer

Q6. All the database users are presently connected to the database instance and working. The HR user has opened three database sessions and executed the following command in one of his sessions:

SQL> UPDATE persons SET ccode='U031' WHERE ccode='U029'

123 rows updated.

SQL> DELETE FROM persons WHERE exp='Y'

3 rows deleted.

The SYS user opens a new session after HR executed the above commands.

Which sessions can see the effect of the UPDATE and DELETE commands?

A. All sessions of the HR user only

B. All sessions of the HR user and the SYS user

C. The session of the HR user that executed the commands

D. All the sessions for which the database users have access privilege to the PERSONS table

View Answer

Q7. The tnsnames.ora file has an entry for the service alias ORCL as follows:

ORCL =

( DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.24.216)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.oracle.com)

)

)

The TNSPING command executes successfully when tested with ORCL, but you are not able to connect to the database instance with the following command:

SQL> CONNECT scott/tiger@orcl

What could be the reason for this?

A. The listener is not running on the database node.

B. The TNS_ADMIN environmental variable is set to a wrong value.

C. The orcl.oracle.com database service is not registered with the listener.

D. The DEFAULT_DOMAIN parameter is set to a wrong value in the sqlnet.ora file.

View Answer

Q8. Examine the command:

SQL>DBMS_STATS.SET_TABLE_PREFS('SH','CUSTOMERS','PUBLISH','false');

Which statement describes the effect of the above command?

A. Automatic statistics collection is stopped for the CUSTOMERS table

B. Statistics for the CUSTOMERS table are locked and cannot be overwritten

C. Existing statistics for the CUSTOMERS table become unusable for the query optimizer

D. Subsequently, statistics gathered on the CUSTOMERS table are stored as pending statistics

View Answer

Q9. The HR user creates a stand-alone procedure as follows and grants the EXECUTE privilege on the procedure to many database users:

CREATE OR REPLACE PROCEDURE create_dept ( v_deptno NUMBER, v_dname VARCHAR2,

v_mgr NUMBER, v_loc NUMBER)

BEGIN

INSERT INTO hr.departments VALUES (v_deptno, v_dname, v_mgr, v_loc);

END;

The users having permission to execute the procedure are able to insert records into the

DEPARTMENTS table even though they do not have the INSERT privilege on the table. You want only those users who have privileges on the DEPARTMENTS table to be able to execute the procedure successfully.

What would you suggest to the PL/SQL developers to achieve this?

A. Create the procedure with definer's right.

B. Create the procedure with invoker's right.

C. Grant the EXECUTE privilege with GRANT OPTION on the procedure to selected users.

D. Create the procedure as part of a PL/SQL package and grant the EXECUTE privilege on the package to selected users.

View Answer

Q10. You notice that the performance of the database has degraded because of frequent checkpoints.

Which two actions resolve the issue? (Choose two.)

A. Disable automatic checkpoint tuning

B. Check the size of the redo log file size and increase the size if it is small

C. Set the FAST_START_MTTR_TARGET parameter as per the advice given by the MTTR Advisor

D. Decrease the number of redo log members if there are more than one redo log members available in each redo log group

View Answer