Q1. You executed a DROP USER CASCADE on an Oracle 11g release 1 database and immediately realized that you forgot to copy the OCA. EXAM_RESULTS table to the OCP schema.

The RECYCLE_BIN enabled before the DROP USER was executed and the OCP user has been granted the FLASHBACK ANY TABLE system privilege.

What is the quickest way to recover the contents of the OCA. EXAM_RESULTS table to the OCP schema?

A. Execute FLASHBACK TABLE OCA. EXAM_RESULTS TO BEFORE DROP RENAME TO OCP.EXAM_RESULTS; connected as SYSTEM.

B. Recover the table using traditional Tablespace Point In Time Recovery.

C. Recover the table using Automated Tablespace Point In Time Recovery.

D. Recovery the table using Database Point In Time Recovery.

E. Execute FLASHBACK TABLE OCA. EXAM_RESULTS TO BEFORE DROP RENAME TO EXAM_RESULTS; connected as the OCP user.

View Answer

Q2. Examine the current value for the following parameters in your database instance:

SGA_MAX_SIZE = 1024M 

SGA_TARGET = 700M 

DB_8K_CACHE_SIZE = 124M 

LOG_BUFFER = 200M

You issue the following command to increase the value of DB_8K_CACHE_SIZE: 

SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M;

Which statement is true?

A. It fails because the DB_8K_CACHE_SIZE parameter cannot be changed dynamically.

B. It succeeds only if memory is available from the autotuned components if SGA. 

C. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_TARGET.

D. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE.

View Answer

Q3. You execute the following piece of code with appropriate privileges:

User SCOTT has been granted the CREATE SESSION privilege and the MGR role. 

Which two statements are true when a session logged in as SCOTT queries the SAL column in the view and the table?

A. Data is redacted for the EMP.SAL column only if the SCOTT session does not have the MGR role set.

B. Data is redacted for EMP.SAL column only if the SCOTT session has the MGR role set.

C. Data is never redacted for the EMP_V.SAL column.

D. Data is redacted for the EMP_V.SAL column only if the SCOTT session has the MGR role set.

E. Data is redacted for the EMP_V.SAL column only if the SCOTT session does not have the MGR role set.

View Answer

Q4. In your multitenant container database (CDB) containing pluggable database (PDBs), the HR user executes the following commands to create and grant privileges on a procedure:

CREATE OR REPLACE PROCEDURE create_test_v (v_emp_id NUMBER, v_ename

VARCHAR2, v_SALARY NUMBER, v_dept_id NUMBER)

BEGIN

INSERT INTO hr.test VALUES (V_emp_id, V_ename, V_salary, V_dept_id); 

END;

/

GRANT EXECUTE ON CREATE_TEST TO john, jim, smith, king;

How can you prevent users having the EXECUTE privilege on the CREATE_TEST procedure from inserting values into tables on which they do not have any privileges?

A. Create the CREATE_TEST procedure with definer’s rights.

B. Grant the EXECUTE privilege to users with GRANT OPTION on the CREATE_TEST procedure.

C. Create the CREATE_TEST procedure with invoker’s rights.

D. Create the CREATE_TEST procedure as part of a package and grant users the EXECUTE privilege the package.

View Answer

Q5. Which three statements are true about the working of system privileges in a multitenant control database (CDB) that has pluggable databases (PDBs)?

A. System privileges apply only to the PDB in which they are used. 

B. Local users cannot use local system privileges on the schema of a common user.

C. The granter of system privileges must possess the set container privilege.

D. Common users connected to a PDB can exercise privileges across other PDBs.

E. System privileges with the with grant option container all clause must be granted to a common user before the common user can grant privileges to other users.

View Answer

Q6. You notice that the performance of your production 24/7 Oracle database significantly degradeD.  Sometimes you are not able to connect to the instance because it hangs. You do not want to restart the database instance.

How can you detect the cause of the degraded performance?

A. Enable Memory Access Mode, which reads performance data from SGA. 

B. Use emergency monitoring to fetch data directly from SGA analysis.

C. Run Automatic Database Diagnostic Monitor (ADDM) to fetch information from the latest Automatic Workload Repository (AWR) snapshots.

D. Use Active Session History (ASH) data and hang analysis in regular performance monitoring.

E. Run ADDM in diagnostic mode.

View Answer

Q7. You wish to enable an audit policy for all database users, except SYS, SYSTEM, and SCOTT.

You issue the following statements:

SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SYS; 

SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SYSTEM; 

SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SCOTT;

For which database users is the audit policy now active?

A. All users except SYS

B. All users except SCOTT

C. All users except sys and SCOTT

D. All users except sys, system, and SCOTT

View Answer

Q8. Examine the following command:

ALTER SYSTEM SET enable_ddl_logging=FALSE; 

Which statement is true?

A. None of the data definition language (DDL) statements are logged in the trace file.

B. Only DDL commands that resulted in errors are logged in the alert log file.

C. A new log.xml file that contains the DDL statements is created, and the DDL command details are removed from the alert log file.

D. Only DDL commands that resulted in the creation of new database files are logged. 

View Answer

Q9. Examine the following command;

ALTER SYSTEM SET enable_ddl_logging = TRUE;

Which statement is true?

A. Only the data definition language (DDL) commands that resulted in errors are logged in the alert log file.

B. All DDL commands are logged in the alert log file.

C. All DDL commands are logged in a different log file that contains DDL statements and their execution dates.

D. Only DDL commands that resulted in the creation of new segments are logged. 

E. All DDL commands are logged in XML format in the alert directory under the Automatic Diagnostic Repository (ADR) home.

View Answer

Q10. Your database is open and the LISTENER listener running. You stopped the wrong listener LISTENER by issuing the following command:

1snrctl > STOP

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

A. They are able to perform only queries.

B. They are not affected and continue to function normally.

C. They are terminated and the active transactions are rolled back.

D. They are not allowed to perform any operations until the listener LISTENER is started. 

View Answer