Oracle Test Bobby

Question 1

Which three statements are true about a job chain? (Choose three.)

A. It can contain a nested chain of jobs.

B. It can be used to implement dependency-based scheduling.

C. It cannot invoke the same program or nested chain in multiple steps in the chain.

D. It cannot have more than one dependency.

E. It can be executed using event-based or time-based schedules.

Answer 1

A. It can contain a nested chain of jobs.

B. It can be used to implement dependency-based scheduling.

E. It can be executed using event-based or time-based schedules.

Question 2

Examine the parameters for your database instance:

You execute the following command:

SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

Which statement is true in this scenario?

A. Undo data is written to flashback logs after 1200 seconds.

B. Inactive undo data is retained for 1200 seconds even if subsequent transactions fail due to lack of space
in the undo tablespace.

C. You can perform a Flashback Database operation only within the duration of 1200 seconds.

D. An attempt is made to keep inactive undo for 1200 seconds but transactions may overwrite the undo
before that time has elapsed.

Answer 2

D. An attempt is made to keep inactive undo for 1200 seconds but transactions may overwrite the undo before that time has elapsed.

Question 3

As a user of the ORCL database, you establish a database link to the remote HQ database such that all users in the ORCL database may access tables only from the SCOTT schema in the HQ database.

SCOTT’s password is TIGER.

The service mane “HQ” is used to connect to the remote HQ database.

Which command would you execute to create the database link?

A. CREATE DATABASE LINK HQ USING ‘HQ’;

B. CREATE DATABASE LINK HQ CONNECT TO CURRENT_USER USING `HQ’;

C. CREATE PUBLIC DATABASE LINK HQ CONNECT TO scott IDENTIFIED BY tiger USING ‘HQ’;

D. CREATE DATABASE LINK HQ CONNECT TO scott IDENTIFIED BY tiger USING ‘HQ’;

Answer 3

C. CREATE PUBLIC DATABASE LINK HQ CONNECT TO scott IDENTIFIED BY tiger USING ‘HQ’;

Question 4

You plan to create a database by using the Database Configuration Assistant (DBCA), with the following specifications:

• Applications will connect to the database via a middle tier.
• The number of concurrent user connections will be high.
• The database will have mixed workload, with the execution of complex BI queries scheduled at night.

Which DBCA option must you choose to create the database?

A. a General Purpose database template with default memory allocation

B. a Data Warehouse database template, with the dedicated server mode option and AMM enabled

C. a General Purpose database template, with the shared server mode option and Automatic Memory Management (AMM) enabled

D. a default database configuration

Answer 4

C. a General Purpose database template, with the shared server mode option and Automatic Memory Management (AMM) enabled

Question 5

Which two statements correctly describe the relationship between data files and logical database structures? (Choose two.)

A. A segment cannot span data files.

B. A data file can belong to only one tablespace.

C. An extent cannot span data files.

D. The size of an Oracle data block in a data file should be the same as the size of an OS block.

Answer 5

B. A data file can belong to only one tablespace.

C. An extent cannot span data files.

Question 6

Which two tasks can be performed on an external table? (Choose two.)

A. partitioning the table

B. creating an invisible index

C. updating the table by using an update statement

D. creating a public synonym

E. creating a view

Answer 6

D. creating a public synonym

E. creating a view

Question 7

Which three factors influence the optimizer’s choice of an execution plan? (Choose three.)

A. the optimizer_mode initialization parameter

B. operating system (OS) statistics

C. cardinality estimates

D. object statistics in the data dictionary

E. fixed baselines

Answer 7

A. the optimizer_mode initialization parameter

C. cardinality estimates

D. object statistics in the data dictionary

Question 8

Examine the resources consumed by a database instance whose current Resource Manager plan is displayed.

Which two statements are true? (Choose two.)

A. An attempt to start a new session by a user belonging to DSS_QUERIES fails with an error.

B. An attempt to start a new session by a user belonging to OTHER_GROUPS fails with an error.

C. The CPU_WAIT_TIME column indicates the total time that sessions in the consumer group waited for the CPU due to resource management.

D. The CPU_WAIT_TIME column indicates the total time that sessions in the consumer group waited for the CPU due to I/O waits and latch or enqueue contention.

E. A user belonging to the DSS__QUERIES resource consumer group can create a new session but the session will be queued.

Answer 8

C. The CPU_WAIT_TIME column indicates the total time that sessions in the consumer group waited for the CPU due to resource management.

E. A user belonging to the DSS__QUERIES resource consumer group can create a new session but the session will be queued.

Question 9

Which action takes place when a file checkpoint occurs?

A. The checkpoint position is advanced in the checkpoint queue.

B. All buffers for a checkpointed file that were modified before a specific SCN are written to disk by DBWn and the SCN is stored in the control file.

C. The Database Writer process (DBWn) writes all dirty buffers in the buffer cache to data files.

D. The Log Writer process (LGWR) writes all redo entries in the log buffer to online redo log files.

Answer 9

C. The Database Writer process (DBWn) writes all dirty buffers in the buffer cache to data files.

Question 10

Examine the structure of the sales table, which is stored in a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled.

You want to perform online segment shrink to reclaim fragmented free space below the high water mark.

What should you ensure before the start of the operation?

A. Row movement is enabled.

B. Referential integrity constraints for the table are disabled.

C. No queries are running on this table.

D. Extra disk space equivalent to the size of the segment is available in the tablespace.

E. No pending transaction exists on the table.

Answer 10

A. Row movement is enabled.

Question 11

Which statement is true regarding the startup of a database instance?

A. The instance does not start up normally and requires manual media recovery after a shutdown using the abort option.

B. Uncommitted transactions are rolled back during the startup of the database instance after a shutdown using the immediate option.

C. There is no difference in the underlying mechanics of the startup whether the database is shut down by using the immediate option or the abort option.

D. Media recovery is required when the database is shut down by using either the immediate option or the abort option.

E. Instance recovery is not required if the database instance was shut down by using SHUTDOWN IMMEDIATE.

Answer 11

E. Instance recovery is not required if the database instance was shut down by using SHUTDOWN IMMEDIATE.

Question 12

Examine the memory-related parameters set in the SPFILE of an Oracle database:

Which statement is true?

A. Only SGA components are sized automatically.

B. Memory is dynamically re-allocated between the SGA and PGA as needed.

C. The size of the PGA cannot grow automatically beyond 500 MB.

D. The value of the MEMORY_TARGET parameter cannot be changed dynamically.

Answer 12

B. Memory is dynamically re-allocated between the SGA and PGA as needed.

Question 13

Which two statements are true about extents? (Choose two.)

A. Blocks belonging to an extent can be spread across multiple data files.

B. Data blocks in an extent are logically contiguous but can be non-contiguous on disk.

C. The blocks of a newly allocated extent, although free, may have been used before.

D. Data blocks in an extent are automatically reclaimed for use by other objects in a tablespace when all the rows in a table are deleted.

Answer 13

B. Data blocks in an extent are logically contiguous but can be non-contiguous on disk.

C. The blocks of a newly allocated extent, although free, may have been used before.

Question 14

You execute the commands:

Which two statements are true? (Choose two.)

A. The create user command fails if any role with the name Sidney exists in the database.

B. The user Sidney can connect to the database instance but cannot perform sort operations because no space quota is specified for the temp tablespace.

C. The user Sidney is created but cannot connect to the database instance because no profile is default.

D. The user Sidney can connect to the database instance but requires relevant privileges to create objects in the users tablespace.

E. The user Sidney is created and authenticated by the operating system.

Answer 14

A. The create user command fails if any role with the name Sidney exists in the database.

D. The user Sidney can connect to the database instance but requires relevant privileges to create objects in the users tablespace.

Question 15

Your database supports a DSS workload that involves the execution of complex queries:

Currently, the library cache contains the ideal workload for analysis.

You want to analyze some of the queries for an application that are cached in the library cache.

What must you do to receive recommendations about the efficient use of indexes and materialized views to improve query performance?

A. Create a SQL Tuning Set (STS) that contains the queries cached in the library cache and run the SQL Tuning Advisor (STA) on the workload captured in the STS.

B. Run the Automatic Workload Repository Monitor (ADDM).

C. Create an STS that contains the queries cached in the library cache and run the SQL Performance Analyzer (SPA) on the workload captured in the STS.

D. Create an STS that contains the queries cached in the library cache and run the SQL Access Advisor on the workload captured in the STS.

Answer 15

D. Create an STS that contains the queries cached in the library cache and run the SQL Access Advisor on the workload captured in the STS.

Question 16

Examine this command:

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

Which three statements are true about the effect of this command? (Choose three.)

A. Statistics collection is not done for the CUSTOMERS table when schema stats are gathered.

B. Statistics collection is not done for the CUSTOMERS table when database stats are gathered.

C. Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.

D. Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics.

E. Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.

Answer 16

C. Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.

D. Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics.

E. Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.

Question 17

Examine the following impdp command to import a database over the network from a pre-12c Oracle database (source):

Which three are prerequisites for successful execution of the command? (Choose three.)

A. The import operation must be performed by a user on the target database by a user with the
DATAPUMP_IMP_FULL_DATABASE role, and the database link must connect to a user with the
DATAPUMP_EXP_FULL_DATABASE role on the source database.

B. All the user-defined tablespaces must be in read-only mode on the source database.

C. The export dump file must be created before starting the import on the target database.

D. The source and target database must be running on the same operating system (OS) with the same endianness.

E. The impdp operation must be performed by the same user that performed the expdp operation.

Answer 17

A. The import operation must be performed by a user on the target database by a user with the
DATAPUMP_IMP_FULL_DATABASE role, and the database link must connect to a user with the
DATAPUMP_EXP_FULL_DATABASE role on the source database.

B. All the user-defined tablespaces must be in read-only mode on the source database.

D. The source and target database must be running on the same operating system (OS) with the same endianness.

Question 18

Which two are true concerning a multitenant container database with three pluggable database? (Choose two.)

A. All administration tasks must be done to a specific pluggable database.

B. The pluggable databases increase patching time.

C. The pluggable databases reduce administration effort.

D. The pluggable databases are patched together.

E. Pluggable databases are only used for database consolidation.

Answer 18

C. The pluggable databases reduce administration effort.

D. The pluggable databases are patched together.

Question 19

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.

Answer 19

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

Question 20

Which three statements are true concerning unplugging a pluggable database (PDB)? (Choose three.)

A. The PDB must be open in read only mode.

B. The PDB must be dosed.

C. The unplugged PDB becomes a non-CDB.

D. The unplugged PDB can be plugged into the same multitenant container database (CDB)

E. The unplugged PDB can be plugged into another CDB.

F. The PDB data files are automatically removed from disk.

Answer 20

B. The PDB must be dosed.

D. The unplugged PDB can be plugged into the same multitenant container database (CDB)

E. The unplugged PDB can be plugged into another CDB.

Question 21

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

Answer 21

B. All users except SCOTT

Question 22

On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command:
$> sqlldr hr/hr@pdb table=employees

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

A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.

B. It fails because no SQL *Loader data file location is specified.

C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.

D. It fails because no SQL *Loader control file location is specified.

Answer 22

A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.

C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.

Question 23

After implementing full Oracle Data Redaction, you change the default value for the NUMBER data type as follows:

After changing the value, you notice that FULL redaction continues to redact numeric data with zero.

What must you do to activate the new default value for numeric full redaction?

A. Re-enable redaction policies that use FULL data redaction.

B. Re-create redaction policies that use FULL data redaction.

C. Re-connect the sessions that access objects with redaction policies defined on them.

D. Flush the shared pool.

E. Restart the database instance.

Answer 23

E. Restart the database instance.

Question 24

You must track all transactions that modify certain tables in the sales schema for at least three years.

Automatic undo management is enabled for the database with a retention of one day.

Which two must you do to track the transactions? (Choose two.)

A. Enable supplemental logging for the database.

B. Specify undo retention guarantee for the database.

C. Create a Flashback Data Archive in the tablespace where the tables are stored.

D. Create a Flashback Data Archive in any suitable tablespace.

E. Enable Flashback Data Archiving for the tables that require tracking.

Answer 24

D. Create a Flashback Data Archive in any suitable tablespace.

E. Enable Flashback Data Archiving for the tables that require tracking.

Question 25

You are the DBA supporting an Oracle 11g Release 2 database and wish to move a table containing several DATE, CHAR, VARCHAR2, and NUMBER data types, and the table’s indexes, to another tablespace.

The table does not have a primary key and is used by an OLTP application.

Which technique will move the table and indexes while maintaining the highest level of availability to the application?

A. Oracle Data Pump.

B. An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD to move the indexes.

C. An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD ONLINE to move the indexes.

D. Online Table Redefinition.

E. Edition-Based Table Redefinition.

Answer 25

D. Online Table Redefinition.

Question 26

To implement Automatic Management (AMM), you set the following parameters:

When you try to start the database instance with these parameter settings, you receive the following error message:

SQL > startup
ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings, see alert log for more information.

Identify the reason the instance failed to start.

A. The PGA_AGGREGATE_TARGET parameter is set to zero.

B. The STATISTICS_LEVEL parameter is set to BASIC.

C. Both the SGA_TARGET and MEMORY_TARGET parameters are set.

D. The SGA_MAX_SIZE and SGA_TARGET parameter values are not equal.

Answer 26

B. The STATISTICS_LEVEL parameter is set to BASIC.

Question 27

You upgrade your Oracle database in a multiprocessor environment. As a recommended you execute the following script:

SQL > @utlrp.sql

Which two actions does the script perform? (Choose two.)

A. Parallel compilation of only the stored PL/SQL code

B. Sequential recompilation of only the stored PL/SQL code

C. Parallel recompilation of any stored PL/SQL code

D. Sequential recompilation of any stored PL/SQL code

E. Parallel recompilation of Java code

F. Sequential recompilation of Java code

Answer 27

C. Parallel recompilation of any stored PL/SQL code

E. Parallel recompilation of Java code

Question 28

Which two statements are true concerning dropping a pluggable database (PDB)? (Choose two.)

A. The PDB must be open in read-only mode.

B. The PDB must be in mount state.

C. The PDB must be unplugged.

D. The PDB data files are always removed from disk.

E. A dropped PDB can never be plugged back into a multitenant container database (CDB).

Answer 28

B. The PDB must be in mount state.

C. The PDB must be unplugged.

Question 29

You notice a performance change in your production Oracle 12c database.

You want to know which change caused this performance difference.

Which method or feature should you use?

A. Compare Period ADDM report

B. AWR Compare Period report

C. Active Session History (ASH) report

D. Taking a new snapshot and comparing it with a preserved snapshot

Answer 29

A. Compare Period ADDM report

Question 30

You want to capture column group usage and gather extended statistics for better cardinality estimates for the CUSTOMERS table in the SH schema.

Examine the following steps:

1. Issue the SELECT DBMS_STATS.CREATE_EXTENDED_STATS (SH', CUSTOMERS’) FROM dual statement.
2. Execute the DBMS_STATS.SEED_COL_USAGE (null, `SH’, 500) procedure.
3. Execute the required queries on the CUSTOMERS table.
4. Issue the SELECT DBMS_STATS.REPORT_COL_USAGE (SH', CUSTOMERS’) FROM dual statement.

Identify the correct sequence of steps.

A. 3, 2, 1, 4

B. 2, 3, 4, 1

C. 4, 1, 3, 2

D. 3, 2, 4, 1

Answer 30

B. 2, 3, 4, 1

Question 31

An application accesses a small lookup table frequently.
You notice that the required data blocks are getting aged out of the default buffer cache.

How would you guarantee that the blocks for the table never age out?

A. Configure the KEEP buffer pool and alter the table with the corresponding storage clause.

B. Increase the database buffer cache size.

C. Configure the RECYCLE buffer pool and alter the table with the corresponding storage clause.

D. Configure Automata Shared Memory Management.

E. Configure Automatic Memory Management.

Answer 31

A. Configure the KEEP buffer pool and alter the table with the corresponding storage clause.

Question 32

You conned using SQL Plus to the root container of a multitenant container database (CDB) with SYSDBA privilege.

The CDB has several pluggable databases (PDBs) open in the read/write mode.

There are ongoing transactions in both the CDB and PDBs.
What happens alter issuing the SHUTDOWN TRANSACTIONAL statement?

A. The shutdown proceeds immediately. The shutdown proceeds as soon as all transactions in the PDBs are either committed or rolled hack.

B. The shutdown proceeds as soon as all transactions in the CDB are either committed or rolled back.

C. The shutdown proceeds as soon as all transactions in both the CDB and PDBs are either committed or rolled back.

D. The statement results in an error because there are open PDBs.

Answer 32

B. The shutdown proceeds as soon as all transactions in the CDB are either committed or rolled back.

Question 33

Your database supports an online transaction processing (OLTP) application.

The application is undergoing some major schema changes, such as addition of new indexes and materialized views.

You want to check the impact of these changes on workload performance.

What should you use to achieve this?

A. Database replay

B. SQL Tuning Advisor

C. SQL Access Advisor

D. SQL Performance Analyzer

E. Automatic Workload Repository compare reports

Answer 33

D. SQL Performance Analyzer

Question 34

An administrator account is granted the CREATE SESSION and SET CONTAINER system privileges.

A multitenant container database (CDB) instant has the following parameter set:
THREADED_EXECUTION = FALSE

Which four statements are true about this administrator establishing connections to root in a CDB that has been opened in read only mode? (Choose four.)

A. You can connect as a common user by using the connect statement.

B. You can connect as a local user by using the connect statement.

C. You can connect by using easy connect.

D. You can connect by using OS authentication.

E. You can connect by using a Net Service name.

F. You can connect as a local user by using the SET CONTAINER statement.

Answer 34

A. You can connect as a common user by using the connect statement.

C. You can connect by using easy connect.

D. You can connect by using OS authentication.

E. You can connect by using a Net Service name.

Question 35

You notice a performance change in your production Oracle database and you want to know which change has made this performance difference.

You generate the Compare Period Automatic Database Diagnostic Monitor (ADDM) report to further investigation.

Which three findings would you get from the report? (Choose three.)

A. It detects any configuration change that caused a performance difference in both time periods.

B. It identifies any workload change that caused a performance difference in both time periods.

C. It detects the top wait events causing performance degradation.

D. It shows the resource usage for CPU, memory, and I/O in both time periods.

E. It shows the difference in the size of memory pools in both time periods.

F. It gives information about statistics collection in both time periods.

Answer 35

A. It detects any configuration change that caused a performance difference in both time periods.

B. It identifies any workload change that caused a performance difference in both time periods.

D. It shows the resource usage for CPU, memory, and I/O in both time periods.

Question 36

Which three statements are true about adaptive SQL plan management? (Choose three.)

A. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.

B. The optimizer always uses the fixed plan, if the fixed plan exists in the plan baseline.

C. It adds new, bettor plans automatically as fixed plans to the baseline.

D. The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.

E. The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.

Answer 36

A. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.

D. The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.

E. The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.

Question 37

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.

Answer 37

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

Question 38

You created a new database using the “create database” statement without specifying the “ENABLE PLUGGABLE” clause.

What are two effects of not using the “ENABLE PLUGGABLE database” clause?

A. The database is created as a non-CDB and can never contain a PDB.

B. The database is treated as a PDB and must be plugged into an existing multitenant container database (CDB).

C. The database is created as a non-CDB and can never be plugged into a CDB.

D. The database is created as a non-CDB but can be plugged into an existing CDB.

E. The database is created as a non-CDB but will become a CDB whenever the first PDB is plugged in. and then create PDBs within this CDB.

Answer 38

A. The database is created as a non-CDB and can never contain a PDB.

D. The database is created as a non-CDB but can be plugged into an existing CDB.

Question 39

What is the effect of specifying the “ENABLE PLUGGABLE DATABASE” clause in a “CREATE DATABASE” statement?

A. It will create a multitenant container database (CDB) with only the root opened.

B. It will create a CDB with root opened and seed read only.

C. It will create a CDB with root and seed opened and one PDB mounted.

D. It will create a CDB that must be plugged into an existing CDB.

E. It will create a CDB with root opened and seed mounted.

Answer 39

B. It will create a CDB with root opened and seed read only.

Question 40

Examine the following parameters for a database instance:

MEMORY_MAX_TARGET=0
MEMORY_TARGET=0
SGA_TARGET=0
PGA_AGGREGATE_TARGET=500m

Which three initialization parameters are not controlled by Automatic Shared Memory Management (ASMM)? (Choose three.)

A. LOG_BUFFER

B. SORT_AREA_SIZE

C. JAVA_POOL_SIZE

D. STREAMS_POOL_SIZE

E. DB_16K_CACHE_SZIE

F. DB_KEEP_CACHE_SIZE

Answer 40

A. LOG_BUFFER

E. DB_16K_CACHE_SZIE

F. DB_KEEP_CACHE_SIZE

Question 41

In your multitenant container database (CDB) containing pluggable database (PDBs), you granted the CREATE TABLE privilege to the common user C # # A_ADMIN in root and all PDBs.

You execute the following command from the root container:

SQL > REVOKE create table FROM C # # A_ADMIN;
What is the result?

A. It executes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in root only.

B. It fails and reports an error because the CONTAINER=ALL clause is not used.

C. It excludes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in root and all PDBs.

D. It fails and reports an error because the CONTAINER=CURRENT clause is not used.

E. It executes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in all PDBs.

Answer 41

A. It executes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in root only.

Question 42

Which three statements are true concerning the multitenant architecture? (Choose three.)

A. Each pluggable database (PDB) has its own set of background processes.

B. A PDB can have a private temp tablespace.

C. PDBs can share the sysaux tablespace.

D. Log switches occur only at the multitenant container database (CDB) level.

E. Different PDBs can have different default block sizes.

F. PDBs share a common system tablespace.

G. Instance recovery is always performed at the CDB level.

Answer 42

B. A PDB can have a private temp tablespace.

D. Log switches occur only at the multitenant container database (CDB) level.

G. Instance recovery is always performed at the CDB level.

Question 43

You notice that the elapsed time for an important database scheduler Job is unacceptably long.

The job belongs to a scheduler job class and window.
Which two actions would reduce the job's elapsed time? (Choose two.)

A. Increasing the priority of the job class to which the job belongs

B. Increasing the job’s relative priority within the Job class to which it belongs

C. Increasing the resource allocation for the consumer group mapped to the scheduler job’s job class within the plan mapped to the scheduler window

D. Moving the job to an existing higher priority scheduler window with the same schedule and duration

E. Increasing the value of the JOB_QUEUE_PROCESSES parameter

F. Increasing the priority of the scheduler window to which the job belongs

Answer 43

B. Increasing the job’s relative priority within the Job class to which it belongs

C. Increasing the resource allocation for the consumer group mapped to the scheduler job’s job class within the plan mapped to the scheduler window

Question 44

You plan to migrate your database from a File system to Automata Storage Management (ASM) on same platform.

Which two methods or commands would you use to accomplish this task? (Choose two.)

A. RMAN CONVERT command

B. Data Pump Export and import

C. Conventional Export and Import

D. The BACKUP AS COPY DATABASE . . . command of RMAN

E. DBMS_FILE_TRANSFER with transportable tablespace

Answer 44

A. RMAN CONVERT command

D. The BACKUP AS COPY DATABASE . . . command of RMAN

Question 45

You run a script that completes successfully using SQL*Plus that performs these actions:

1. Creates a multitenant container database (CDB)
2. Plugs in three pluggable databases (PDBs)
3. Shuts down the CDB instance
4. Starts up the CDB instance using STARTUP OPEN READ WRITE

Which two statements are true about the outcome after running the script? (Choose two.)

A. The seed will be in mount state.

B. The seed will be opened read-only.

C. The seed will be opened read/write.

D. The other PDBs will be in mount state.

E. The other PDBs will be opened read-only.

F. The PDBs will be opened read/write.

Answer 45

B. The seed will be opened read-only.

D. The other PDBs will be in mount state.

Question 46

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.

Answer 46

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

Question 47

You execute the following PL/SQL:
Which two statements are true? (Choose two.)

A. Fine-Grained Auditing (FGA) is enabled for the PRICE column in the PRODUCTS table for SELECT statements only when a row with PRICE > 10000 is accessed.

B. FGA is enabled for the PRODUCTS.PRICE column and an audit record is written whenever a row with PRICE > 10000 is accessed.

C. FGA is enabled for all DML operations by JIM on the PRODUCTS.PRICE column.

D. FGA is enabled for the PRICE column of the PRODUCTS table and the SQL statements is captured in the FGA audit trial.

Answer 47

A. Fine-Grained Auditing (FGA) is enabled for the PRICE column in the PRODUCTS table for SELECT statements only when a row with PRICE > 10000 is accessed.

B. FGA is enabled for the PRODUCTS.PRICE column and an audit record is written whenever a row with PRICE > 10000 is accessed.

Question 48

You execute the following commands to audit database activities:

SQL > ALTER SYSTEM SET AUDIT_TRIAL=DB, EXTENDED SCOPE=SPFILE;

SQL > AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY JOHN By SESSION WHENEVER SUCCESSFUL;

Which statement is true about the audit record that generated when auditing after instance restarts?

A. One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command on a table, and contains the SQL text for the SQL Statements.

B. One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command, and contains the execution plan for the SQL statements.

C. One audit record is created for the whole session if john successfully executes a SELECT, INSERT, or DELETE command, and contains the execution plan for the SQL statements.

D. One audit record is created for the whole session if JOHN successfully executes a select command, and contains the SQL text and bind variables used.

E. One audit record is created for the whole session if john successfully executes a SELECT, INSERT, or DELETE command on a table, and contains the execution plan, SQL text, and bind variables used.

Answer 48

A. One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command on a table, and contains the SQL text for the SQL Statements.

Question 49

To enable the Database Smart Flash Cache, you configure the following parameters:

DB_FLASH_CACHE_FILE = /dev/flash_device_1' , /dev/flash_device_2’

DB_FLASH_CACHE_SIZE=64G

What is the result when you start up the database instance?

A. It results in an error because these parameter settings are invalid.

B. One 64G flash cache file will be used.

C. Two 64G flash cache files will be used.

D. Two 32G flash cache files will be used.

Answer 49

A. It results in an error because these parameter settings are invalid.

Question 50

You are administering a database and you receive a requirement to apply the following restrictions:

1. A connection must be terminated after four unsuccessful login attempts by user.
2. A user should not be able to create more than four simultaneous sessions.
3. User session must be terminated after 15 minutes of inactivity.
4. Users must be prompted to change their passwords every 15 days.

How would you accomplish these requirements?

A. by granting a secure application role to the users

B. by creating and assigning a profile to the users and setting the REMOTE_OS_AUTHENT parameter to FALSE

C. By creating and assigning a profile to the users and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameter to 4

D. By Implementing Fine-Grained Auditing (FGA) and setting the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE.

E. By implementing the database resource Manager plan and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameters to 4.

Answer 50

C. By creating and assigning a profile to the users and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameter to 4

Question 51

A senior DBA asked you to execute the following command to improve performance:

SQL> ALTER TABLE subscribe log STORAGE (BUFFER_POOL recycle);

You checked the data in the SUBSCRIBE_LOG table and found that it is a large table containing one million rows.

What could be a reason for this recommendation?

A. The keep pool is not configured.

B. Automatic Workarea Management is not configured.

C. Automatic Shared Memory Management is not enabled.

D. The data blocks in the SUBSCRIBE_LOG table are rarely accessed.

E. All the queries on the SUBSCRIBE_LOG table are rewritten to a materialized view.

Answer 51

D. The data blocks in the SUBSCRIBE_LOG table are rarely accessed.

Question 52

Which three tasks can be automatically performed by the Automatic Data Optimization feature of Information lifecycle Management (ILM)? (Choose three.)

A. Tracking the most recent read time for a table segment in a user tablespace

B. Tracking the most recent write time for a table segment in a user tablespace

C. Tracking insert time by row for table rows

D. Tracking the most recent write time for a table block

E. Tracking the most recent read time for a table segment in the SYSAUX tablespace

F. Tracking the most recent write time for a table segment in the SYSAUX tablespace

Answer 52

A. Tracking the most recent read time for a table segment in a user tablespace

B. Tracking the most recent write time for a table segment in a user tablespace

D. Tracking the most recent write time for a table block

Question 53

You configure your database Instance to support shared server connections.

Which two memory areas that are part of PGA are stored in SGA instead, for shared server connection? (Choose two.)

A. User session data

B. Stack space

C. Private SQL area

D. Location of the runtime area for DML and DDL Statements

E. Location of a part of the runtime area for SELECT statements

Answer 53

A. User session data

C. Private SQL area

Question 54

Which two statements are true about Oracle Managed Files (OMF)? (Choose two.)

A. OMF cannot be used in a database that already has data files created with user-specified directions.

B. The file system directions that are specified by OMF parameters are created automatically.

C. OMF can be used with ASM disk groups, as well as with raw devices, for better file management.

D. OMF automatically creates unique file names for table spaces and control files.

E. OMF may affect the location of the redo log files and archived log files.

Answer 54

D. OMF automatically creates unique file names for table spaces and control files.

E. OMF may affect the location of the redo log files and archived log files.

Question 55

Identify three scenarios in which you would recommend the use of SQL Performance Analyzer to analyze impact on the performance of SQL statements.

A. Change in the Oracle Database version

B. Change in your network infrastructure

C. Change in the hardware configuration of the database server

D. Migration of database storage from non-ASM to ASM storage

E. Database and operating system upgrade

Answer 55

A. Change in the Oracle Database version

C. Change in the hardware configuration of the database server

E. Database and operating system upgrade

Question 56

Which two statements are true about the RMAN validate database command? (Choose two.)

A. It checks the database for intrablock corruptions.

B. It can detect corrupt pfiles.

C. It can detect corrupt spfiles.

D. It checks the database for interblock corruptions.

E. It can detect corrupt block change tracking files.

Answer 56

A. It checks the database for intrablock corruptions.

C. It can detect corrupt spfiles.

Question 57

You are required to migrate your 11.2.0.3 database as a pluggable database (PDB) to a multitenant container database (CDB).

The following are the possible steps to accomplish this task:

1. Place all the user-defined tablespace in read-only mode on the source database.
2. Upgrade the source database to a 12c version.
3. Create a new PDB in the target container database.
4. Perform a full transportable export on the source database with the VERSION parameter set to 12 using the expdp utility.
5. Copy the associated data files and export the dump file to the desired location in the target database.
6. Invoke the Data Pump import utility on the new PDB database as a user with the DATAPUMP_IMP_FULL_DATABASE role and specify the full transportable import options.
7. Synchronize the PDB on the target container database by using the DBMS_PDS.SYNC_ODB function.

Identify the correct order of the required steps.

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

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

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

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

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

Answer 57

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

Question 58

Which two statements are true about the Oracle Direct Network File system (DNFS)? (Choose two.)

A. It utilizes the OS file system cache.

B. A traditional NFS mount is not required when using Direct NFS.

C. Oracle Disk Manager can manage NFS on its own, without using the operating kernel NFS driver.

D. Direct NFS is available only in UNIX platforms.

E. Direct NFS can load-balance I/O traffic across multiple network adapters.

Answer 58

C. Oracle Disk Manager can manage NFS on its own, without using the operating kernel NFS driver.

E. Direct NFS can load-balance I/O traffic across multiple network adapters.

Question 59

Which three operations can be performed as multipartition operations in Oracle? (Choose three.)

A. Merge partitions of a list partitioned table

B. Drop partitions of a list partitioned table

C. Coalesce partitions of a hash-partitioned global index.

D. Move partitions of a range-partitioned table

E. Rename partitions of a range partitioned table

F. Merge partitions of a reference partitioned index

Answer 59

A. Merge partitions of a list partitioned table

B. Drop partitions of a list partitioned table

F. Merge partitions of a reference partitioned index

Question 60

A redaction policy was added to the SAL column of the SCOTT.EMP table:

All users have their default set of system privileges.

For which three situations will data not be redacted? (Choose three.)

A. SYS sessions, regardless of the roles that are set in the session

B. SYSTEM sessions, regardless of the roles that are set in the session

C. SCOTT sessions, only if the MGR role is set in the session

D. SCOTT sessions, only if the MGR role is granted to SCOTT

E. SCOTT sessions, because he is the owner of the table

F. SYSTEM session, only if the MGR role is set in the session

Answer 60

A. SYS sessions, regardless of the roles that are set in the session

B. SYSTEM sessions, regardless of the roles that are set in the session

D. SCOTT sessions, only if the MGR role is granted to SCOTT

Question 61

What is the result of executing a TRUNCATE TABLE command on a table that has Flashback Archiving enabled?

A. It fails with the ORA-665610 Invalid DDL statement on history-tracked message

B. The rows in the table are truncated without being archived.

C. The rows in the table are archived, and then truncated.

D. The rows in both the table and the archive are truncated.

Answer 61

C. The rows in the table are archived, and then truncated.

Question 62

You create a table with the PERIOD FOR clause to enable the use of the Temporal Validity feature of Oracle database 12c.

Examine the table definition:

Which three statements are true concerning the use of the Valid Time Temporal feature for the EMPLOYEES table? (Choose three.)

A. The valid time columns employee_time_start and employee_time_end are automatically created.

B. The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.

C. The valid time columns are not populated by the Oracle Server automatically.

D. The valid time columns are visible by default when the table is described.

E. Setting the session valid time using DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME sets the visibility for data manipulation language (DML), data definition language (DDL), and queries
performed by the session.

Answer 62

A. The valid time columns employee_time_start and employee_time_end are automatically created.

B. The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.

C. The valid time columns are not populated by the Oracle Server automatically.

Question 63

Which three statements are true regarding the use of the Database Migration Assistant for Unicode (DMU)? (Choose three.)

A. A DBA can check specific tables with the DMU

B. The database to be migrated must be opened read-only.

C. The release of the database to be converted can be any release since 9.2.0.8.

D. The DMU can report columns that are too long in the converted characterset.

E. The DMU can report columns that are not represented in the converted characterset.

Answer 63

A. A DBA can check specific tables with the DMU

D. The DMU can report columns that are too long in the converted characterset.

E. The DMU can report columns that are not represented in the converted characterset.

Question 64

Oracle Grid Infrastructure for a stand-alone server is installed on your production host before installing the Oracle Database server.

The database and listener are configured by using Oracle Restart. Examine the following command and its output:

$ crsctl config has
CRS-4622: Oracle High Availability Services auto start is enabled.

What does this imply?

A. When you start an instance on a high with SQL *Plus dependent listeners and ASM disk groups are automatically started.

B. When a database instance is started by using the SRVCTL utility and listener startup fails, the instance is still started.

C. When a database is created by using SQL* Plus, it is automatically added to the Oracle Restart configuration.

D. When you create a database service by modifying the SERVICE_NAMES initialization parameter, it is automatically added to the Oracle Restart configuration.

Answer 64

B. When a database instance is started by using the SRVCTL utility and listener startup fails, the instance is still started.

Question 65

You performed an incremental level 0 backup of a database:

RMAN > BACKUP INCREMENTAL LEVEL 0 DATABASE;

To enable block change tracking after the incremental level 0 backup, you issued this command:

SQL > ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ` /mydir/rman_change_track.f’;

To perform an incremental level 1 cumulative backup, you issued this command:
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Which three statements are true? (Choose three.)

A. Backup change tracking will sometimes reduce I/O performed during cumulative incremental backups.

B. The change tracking file must always be backed up when you perform a full database backup.

C. Block change tracking will always reduce I/O performed during cumulative incremental backups.

D. More than one database block may be read by an incremental backup for a change made to a single block.

E. The incremental level 1 backup that immediately follows the enabling of block change tracking will not read the change tracking file to discover changed blocks.

Answer 65

A. Backup change tracking will sometimes reduce I/O performed during cumulative incremental backups.

D. More than one database block may be read by an incremental backup for a change made to a single block.

E. The incremental level 1 backup that immediately follows the enabling of block change tracking will not read the change tracking file to discover changed blocks.

Question 66

Which three resources might be prioritized between competing pluggable databases when creating a multitenant container database plan (CDB plan) using Oracle Database Resource Manager? (Choose three.)

A. Maximum Undo per consumer group

B. Maximum Idle time

C. Parallel server limit

D. CPU

E. Exadata I/O

F. Local file system I/O

Answer 66

C. Parallel server limit

D. CPU

E. Exadata I/O

Question 67

Which three activities are supported by the Data Recovery Advisor? (Choose three.)

A. Advising on block checksum failures

B. Advising on inaccessible control files

C. Advising on inaccessible block change tracking files

D. Advising on empty password files

E. Advising on invalid block header field values

Answer 67

A. Advising on block checksum failures

B. Advising on inaccessible control files

E. Advising on invalid block header field values

Question 68

You created an encrypted tablespace:

You then closed the encryption wallet because you were advised that this is secure.

Later in the day, you attempt to create the EMPLOYEES table in the SECURESPACE tablespace with the SALT option on the EMPLOYEE column.

Which is true about the result?

A. It creates the table successfully but does not encrypt any inserted data in the EMPNAME column because the wallet must be opened to encrypt columns with SALT.

B. It generates an error when creating the table because the wallet is closed.

C. It creates the table successfully, and encrypts any inserted data in the EMPNAME column because the wallet needs to be open only for tablespace creation.

D. It generates error when creating the table, because the salt option cannot be used with encrypted tablespaces.

Answer 68

B. It generates an error when creating the table because the wallet is closed.

Question 69

Which two statements are true when row archival management is enabled? (Choose two.)

A. The ORA_ARCHIVE_STATE column visibility is controlled by the ROW ARCHIVAL VISIBILITY session parameter.

B. The ORA_ARCHIVE_STATE column is updated manually or by a program that could reference activity tracking columns, to indicate that a row is no longer considered active.

C. The ROW ARCHIVAL VISIBILITY session parameter defaults to active rows only.

D. The ORA_ARCHIVE_STATE column is visible if referenced in the select list of a query.

E. The ORA_ARCHIVE_STATE column is updated automatically by the Oracle Server based on activity tracking columns, to Indicate that a row is no longer considered active.

Answer 69

C. The ROW ARCHIVAL VISIBILITY session parameter defaults to active rows only.

D. The ORA_ARCHIVE_STATE column is visible if referenced in the select list of a query.

Question 70

A warehouse fact table in your Oracle 12c Database is range-partitioned by month and accessed frequently with queries that span multiple partitions

The table has a local prefixed, range partitioned index.

Some of these queries access very few rows in some partitions and all the rows in other partitions, but these queries still perform a full scan for all accessed partitions.

This commonly occurs when the range of dates begins at the end of a month or ends close to the start of a month.
You want an execution plan to be generated that uses indexed access when only a few rows are accessed from a segment, while still allowing full scans for segments where many rows are returned.

Which three methods could transparently help to achieve this result? (Choose three.)

A. Using a partial local Index on the warehouse fact table month column with indexing disabled to the table partitions that return most of their rows to the queries.

B. Using a partial local Index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.

C. Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, which retains the existing local partitioned column.

D. Converting the partitioned table to a partitioned view that does a UNION ALL query on the monthly tables, which retains the existing local partitioned column.

E. Using a partial global index on the warehouse fact table month column with indexing disabling for the table partitions that return most of their rows to the queries.

F. Using a partial global index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.

Answer 70

A. Using a partial local Index on the warehouse fact table month column with indexing disabled to the table partitions that return most of their rows to the queries.

C. Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, which retains the existing local partitioned column.

E. Using a partial global index on the warehouse fact table month column with indexing disabling for the table partitions that return most of their rows to the queries.

Question 71

You use the segment advisor to help determine objects for which space may be reclaimed.

Which three statements are true about the advisor given by the segment advisor? (Choose three.)

A. It may advise the use of online table redefinition for tables in dictionary managed tablespace.

B. It may advise the use of segment shrink for tables in dictionary managed tablespaces it the no chained rows.

C. It may advise the use of online table redefinition for tables in locally managed tablespaces

D. It will detect and advise about chained rows.

E. It may advise the use of segment shrink for free list managed tables.

Answer 71

A. It may advise the use of online table redefinition for tables in dictionary managed tablespace.

C. It may advise the use of online table redefinition for tables in locally managed tablespaces

D. It will detect and advise about chained rows.

Question 72

In your multitenant container database (CDB) containing same pluggable databases (PDBs), you execute the following commands in the root container:

Which two statements are true? (Choose two.)

A. The C # # ROLE1 role is created in the root database and all the PDBs.

B. The C # # ROLE1 role is created only in the root database because the container clause is not used.

C. Privileges are granted to the C##A_ADMIN user only in the root database.

D. Privileges are granted to the C##A_ADMIN user in the root database and all PDBs.

E. The statement for granting a role to a user fails because the CONTAINER clause is not used.

Answer 72

A. The C # # ROLE1 role is created in the root database and all the PDBs.

C. Privileges are granted to the C##A_ADMIN user only in the root database.

Question 73

Flashback is enabled for your multitenant container database (CDB), which contains two pluggable database (PDBs).

A local user was accidently dropped from one of the PDBs.
You want to flash back the PDB to the time before the local user was dropped.

You connect to the CDB and execute the following commands:
SQL > SHUTDOWN IMMEDIATE
SQL > STARTUP MOUNT
SQL > FLASHBACK DATABASE to TIME “TO_DATE (08/20/12' , MM/DD/YY’)”;

Examine following commands:

1. ALTER PLUGGABLE DATABASE ALL OPEN;
2. ALTER DATABASE OPEN;
3. ALTER DATABASE OPEN RESETLOGS;

Which command or commands should you execute next to allow updates to the flashback back schema?

A. Only 1

B. Only 2

C. Only 3

D. 3 and 1

E. 1 and 2

Answer 73

D. 3 and 1

Question 74

Examine the commands executed to monitor database operations:
$> conn sys oracle/oracle@prod as sysdba

SQL > VAR eid NUMBER
SQL > EXEC: eid := DBMS_SQL_MONITOR.BEGIN_OPERATION (batch_job' , FORCED_TRACKING =\> Y’);

Which two statements are true? (Choose two.)

A. Database operations will be monitored only when they consume a significant amount of resource.

B. Database operations for all sessions will be monitored.

C. Database operations will be monitored only if the STATISTICS_LEVEL parameter is set to TYPICAL
and CONTROL_MANAGEMENT_PACK_ACCESS is set DIAGNISTIC + TUNING.

D. Only DML and DDL statements will be monitored for the session.

E. All subsequent statements in the session will be treated as one database operation and will be monitored.

Answer 74

C. Database operations will be monitored only if the STATISTICS_LEVEL parameter is set to TYPICAL
and CONTROL_MANAGEMENT_PACK_ACCESS is set DIAGNISTIC + TUNING.

E. All subsequent statements in the session will be treated as one database operation and will be monitored.

Question 75

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

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

Answer 75

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

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

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

Question 76

You are about to plug a multi-terabyte non-CDB into an existing multitenant container database (CDB) as a pluggable database (PDB).

The characteristics of the non-CDB are as follows:
Version: Oracle Database 12c Releases 1 64-bit
Character set: WE8ISO8859P15
National character set: AL16UTF16
O/S: Oracle Linux6 64-bit

The characteristics of the CDB are as follows:
Version: Oracle Database 12c Release 1 64-bit
Character set: AL32UTF8
O/S: Oracle Linux 6 64-bit

Which technique should you use to minimize down time while plugging this non-CDB into the CDB?

A. Transportable database

B. Transportable tablespace

C. Data Pump full export / import

D. The DBMS_PDB package

E. RMAN

Answer 76

D. The DBMS_PDB package

Question 77

Which three statements are true about SQL plan directives? (Choose three.)

A. They are tied to a specific statement or SQL ID.

B. They instruct the maintenance job to collect missing statistics or perform dynamic sampling to generate a more optimal plan.

C. They are used to gather only missing statistics.

D. They are created for a query expression where statistics are missing or the cardinality estimates by the optimizer are incorrect.

E. They instruct the optimizer to create only column group statistics.

F. Improve plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace.

Answer 77

B. They instruct the maintenance job to collect missing statistics or perform dynamic sampling to generate a more optimal plan.

D. They are created for a query expression where statistics are missing or the cardinality estimates by the optimizer are incorrect.

F. Improve plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace.

Question 78

Examine these two statements:

Which three are true about the MRKT tablespace? (Choose three.)

A. The MRKT tablespace is created as a small file tablespace, because the file size is less than the minimum required for big file files.

B. The MRKT tablespace may be dropped if it has no contents.

C. Users who were using the old default tablespace will have their default tablespaces changed to the MRKT tablespace.

D. No more data files can be added to the tablespace.

E. The relative file number of the tablespace is not stored in rowids for the table rows that are stored in the MRKT tablespace.

Answer 78

C. Users who were using the old default tablespace will have their default tablespaces changed to the MRKT tablespace.

D. No more data files can be added to the tablespace.

E. The relative file number of the tablespace is not stored in rowids for the table rows that are stored in the MRKT tablespace.

Question 79

In your database, you want to ensure that idle sessions that are blocking active are automatically terminated after a specified period of time.

How would you accomplish this?

A. Setting a metric threshold

B. Implementing Database Resource Manager

C. Enabling resumable timeout for user sessions

D. Decreasing the value of the IDLE_TIME resource limit in the default profile

Answer 79

B. Implementing Database Resource Manager

Question 80

You Execute the Following command to create a password file in the database server:

$ orapwd file = `+DATA/PROD/orapwprod entries = 5 ignorecase = N format = 12’

Which two statements are true about the password file? (Choose two.)

A. It records the usernames and passwords of users when granted the DBA role.

B. It contains the usernames and passwords of users for whom auditing is enabled.

C. Is used by Oracle to authenticate users for remote database administration.

D. It records the usernames and passwords of all users when they are added to the OSDBA or OSOPER operating system groups.

E. It supports the SYSBACKUP, SYSDG, and SYSKM system privileges.

Answer 80

C. Is used by Oracle to authenticate users for remote database administration.

E. It supports the SYSBACKUP, SYSDG, and SYSKM system privileges.

Question 81

Identify two situations in which the alert log file is updated.

A. Running a query on a table returns ORA-600: Internal Error.

B. Inserting a value into a table returns ORA-01722: invalid number.

C. Creating a table returns ORA-00955: name us already in used by an existing object.

D. Inserting a value into a table returns ORA-00001: unique constraint (SYS.OK_TECHP) violated.

E. Rebuilding an index using ALTER INDEX . . . REBUILD fails with an ORA-01578: ORACLE data block corrupted (file # 14, block # 50) error.

Answer 81

A. Running a query on a table returns ORA-600: Internal Error.

E. Rebuilding an index using ALTER INDEX . . . REBUILD fails with an ORA-01578: ORACLE data block corrupted (file # 14, block # 50) error.

Question 82

Examine the query and its output executed In an RDBMS Instance:

Which three statements are true about the users (other than sys) in the output? (Choose three.)

A. The C # # B_ADMIN user can perform all backup and recovery operations using RMAN only.

B. The C # # C_ADMIN user can perform the data guard operation with Data Guard Broker.

C. The C # # A_ADMIN user can perform wallet operations.

D. The C # # D_ADMIN user can perform backup and recovery operations for Automatic Storage Management (ASM).

E. The C # # B_ADMIN user can perform all backup and recovery operations using RMAN or SQL* Plus.

Answer 82

B. The C # # C_ADMIN user can perform the data guard operation with Data Guard Broker.

D. The C # # D_ADMIN user can perform backup and recovery operations for Automatic Storage Management (ASM).

E. The C # # B_ADMIN user can perform all backup and recovery operations using RMAN or SQL* Plus.

Question 83

In your Database, the TBS PERCENT USED parameter is set to 60 and the TBS PERCENT FREE parameter is set to 20.

Which two storage-tiering actions might be automated when using information Lifecycle Management (ILM) to automate data movement? (Choose two.)

A. The movement of all segments to a target tablespace with a higher degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED

B. Setting the target tablespace to read-only

C. The movement of some segments to a target tablespace with a higher degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED

D. Setting the target tablespace offline

E. The movement of some blocks to a target tablespace with a lower degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED

Answer 83

B. Setting the target tablespace to read-only

C. The movement of some segments to a target tablespace with a higher degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED

Question 84

Which three statements are true about Flashback Database? (Choose three.)

A. Flashback logs are written sequentially, and are archived.

B. Flashback Database uses a restored control file to recover a database.

C. The Oracle database automatically creates, deletes, and resides flashback logs in the Fast Recovery Area.

D. Flashback Database can recover a database to the state that it was in before a reset logs operation.

E. Flashback Database can recover a data file that was dropped during the span of time of the flashback.

F. Flashback logs are used to restore to the blocks before images, and then the redo data may be used to roll forward to the desired flashback time.

Answer 84

C. The Oracle database automatically creates, deletes, and resides flashback logs in the Fast Recovery Area.

D. Flashback Database can recover a database to the state that it was in before a reset logs operation.

F. Flashback logs are used to restore to the blocks before images, and then the redo data may be used to roll forward to the desired

Question 85

Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?

A. By default, EM express is available for a database after database creation.

B. You can use EM express to manage multiple databases running on the same server.

C. You can perform basic administrative tasks for pluggable databases by using the EM express interface.

D. You cannot start up or shut down a database Instance by using EM express.

E. You can create and configure pluggable databases by using EM express.

Answer 85

D. You cannot start up or shut down a database Instance by using EM express.

Question 86

In which two scenarios do you use SQL* Loader to load data? (Choose two.)

A. Transform the data while it is being loaded into the database.

B. Use transparent parallel processing without having to split the external data first.

C. Load data into multiple tables during the same load statement.

D. Generate unique sequential key values in specified columns.

Answer 86

C. Load data into multiple tables during the same load statement.

D. Generate unique sequential key values in specified columns.

Question 87

You are connected to a pluggable database (PDB) as a common user with DBA privileges.

The STATISTICS_LEVEL parameter is PDB_MODIFIABLE.
You execute the following:

SQL > ALTER SYSTEM SET STATISTICS_LEVEL = ALL SID = `*’ SCOPE = SPFILE;

Which is true about the result of this command?

A. The STATISTICS_LEVEL parameter is set to all whenever this PDB is re-opened.

B. The STATISTICS_LEVEL parameter is set to ALL whenever any PDB is reopened.

C. The STATISTICS_LEVEL parameter is set to all whenever the multitenant container database (CDB) is restarted.

D. Nothing happens; because there is no SPFILE for each PDB, the statement is ignored.

Answer 87

A. The STATISTICS_LEVEL parameter is set to all whenever this PDB is re-opened.

Question 88

On your Oracle 12c database, you Issue the following commands to create indexes

SQL > CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customers_id, sales_rep_id) INVISIBLE;

SQL> CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customers_id, sales_rep_id);

Which two statements are correct? (Choose two.)

A. Both the indexes are created; however, only the ORD_COSTOMER index is visible.

B. The optimizer evaluates index access from both the Indexes before deciding on which index to use for query execution plan.

C. Only the ORD_CUSTOMER_IX1 index is created.

D. Only the ORD_CUSTOMER_IX2 index is created.

E. Both the indexes are updated when a new row is inserted, updated, or deleted In the orders table.

Answer 88

A. Both the indexes are created; however, only the ORD_COSTOMER index is visible.

E. Both the indexes are updated when a new row is inserted, updated, or deleted In the orders table.

Question 89

Identify three benefits of Unified Auditing.

A. Decreased use of storage to store audit trail rows in the database.

B. It improves overall auditing performance.

C. It guarantees zero-loss auditing.

D. The audit trail cannot be easily modified because it is read-only.

E. It automatically audits Recovery Manager (RMAN) events.

Answer 89

B. It improves overall auditing performance.

D. The audit trail cannot be easily modified because it is read-only.

E. It automatically audits Recovery Manager (RMAN) events.

Question 90

You upgraded from a previous Oracle database version to Oracle Database version to Oracle Database 12c. Your database supports a mixed workload.

During the day, lots of insert, update, and delete operations are performed. At night, Extract, Transform,
Load (ETL) and batch reporting jobs are run. The ETL jobs perform certain database operations using two or more concurrent sessions.

After the upgrade, you notice that the performance of ETL jobs has degraded.

To ascertain the cause of performance degradation, you want to collect basic statistics such as the level of parallelism, total database time, and the number of I/O requests for the ETL jobs.

How do you accomplish this?

A. Examine the Active Session History (ASH) reports for the time period of the ETL or batch reporting runs.

B. Enable SQL tracing for the queries in the ETL and batch reporting queries and gather diagnostic data from the trace file.

C. Enable real-time SQL monitoring for ETL jobs and gather diagnostic data from the V$SQL_MONITOR view.

D. Enable real-time database operation monitoring using the
DBMS_SQL_MONITOR.BEGIN_OPERATION function, and then use the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the required information.

Answer 90

D. Enable real-time database operation monitoring using the
DBMS_SQL_MONITOR.BEGIN_OPERATION function, and then use the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the required information.

Question 91

Examine the following commands for redefining a table with Virtual Private Database (VPD) policies:

Which two statements are true about redefining the table? (Choose two.)

A. All the triggers for the table are disabled without changing any of the column names or column types in the table.

B. The primary key constraint on the EMPLOYEES table is disabled during redefinition.

C. VPD policies are copied from the original table to the new table during online redefinition.

D. You must copy the VPD policies manually from the original table to the new table during online redefinition.

Answer 91

B. The primary key constraint on the EMPLOYEES table is disabled during redefinition.

C. VPD policies are copied from the original table to the new table during online redefinition.

Question 92

Which two statements are true about the use of the procedures listed in the

v$sysaux_occupants.move_procedure column? (Choose two.)

A. The procedure may be used for some components to relocate component data to the SYSAUX tablespace from its current tablespace.

B. The procedure may be used for some components to relocate component data from the SYSAUX tablespace to another tablespace.

C. All the components may be moved into SYSAUX tablespace.

D. All the components may be moved from the SYSAUX tablespace.

Answer 92

A. The procedure may be used for some components to relocate component data to the SYSAUX tablespace from its current tablespace.

B. The procedure may be used for some components to relocate component data from the SYSAUX tablespace to another tablespace.

Question 93

Which statement is true about Oracle Net Listener?

A. It acts as the listening endpoint for the Oracle database instance for all local and non-local user connections.

B. A single listener can service only one database instance and multiple remote client connections.

C. Service registration with the listener is performed by the listener registration process monitor (LREG) process of each database instance.

D. The listener.ora configuration file must be configured with one or more listening protocol addresses to allow remote users to connect to a database instance.

E. The listener.ora configuration file must be located in the ORACLE_HOME/network/admin directly.

Answer 93

C. Service registration with the listener is performed by the listener registration process monitor (LREG) process of each database instance.

Question 94

You are administering a database stored in Automatic Storage Management (ASM).

You use RMAN to back up the database and the MD_BACKUP command to back up the ASM metadata regularly. You lost an ASM disk group DG1 due to hardware failure.

In which three ways can you re-create the lost disk group and restore the data? (Choose three.)

A. Use the MD_RESTORE command to restore metadata for an existing disk group by passing the existing disk group name as an input parameter and use RMAN to restore the data.

B. Use the MKDG command to restore the disk group with the same configuration as the backed-up disk group and data on the disk group.

C. Use the MD_RESTORE command to restore the disk group with the changed disk group specification, failure group specification, name, and other attributes and use RMAN to restore the data.

D. Use the MKDG command to restore the disk group with the same configuration as the backed-up disk group name and same set of disks and failure group configuration, and use RMAN to restore the data.

E. Use the MD_RESTORE command to restore both the metadata and data for the failed disk group.

F. Use the MKDG command to add a new disk group DG1 with the same or different specifications for failure group and other attributes and use RMAN to restore the data.

Answer 94

A. Use the MD_RESTORE command to restore metadata for an existing disk group by passing the existing disk group name as an input parameter and use RMAN to restore the data.

C. Use the MD_RESTORE command to restore the disk group with the changed disk group specification, failure group specification, name, and other attributes and use RMAN to restore the data.

F. Use the MKDG command to add a new disk group DG1 with the same or different specifications for failure group and other attributes and use RMAN to restore the data.

Question 95

Which Oracle Database component is audited by default if the unified Auditing option is enabled?

A. Oracle Data Pump

B. Oracle Recovery Manager (RMAN)

C. Oracle Label Security

D. Oracle Database Vault

E. Oracle Real Application Security

Answer 95

B. Oracle Recovery Manager (RMAN)

Question 96

Your multitenant container (CDB) containing three pluggable databases (PDBs) is running in ARCHIVELOG mode.

You find that the SYSAUX tablespace is corrupted in the root container.

The steps to recover the tablespace are as follows:

1. Mount the CDB.
2. Close all the PDBs.
3. Open the database.
4. Apply the archive redo logs.
5. Restore the data file.
6. Take the SYSAUX tablespace offline.
7. Place the SYSAUX tablespace online.
8. Open all the PDBs with RESETLOGS.
9. Open the database with RESETLOGS.
10. Execute the command SHUTDOWN ABORT.

Which option identifies the correct sequence to recover the SYSAUX tablespace?

A. 6, 5, 4, 7

B. 10, 1, 2, 5, 8

C. 10, 1, 2, 5, 4, 9, 8

D. 10, 1, 5, 8, 10

Answer 96

A. 6, 5, 4, 7

Question 97

Which three are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled? (Choose three.)

A. Reduced logical I/O

B. Reduced virtual memory utilization

C. Improved parallel Execution performance

D. Improved Serial Execution performance

E. Reduced physical I/O

F. Reduced CPU utilization

Answer 97

B. Reduced virtual memory utilization

C. Improved parallel Execution performance

F. Reduced CPU utilization

Question 98

In order to exploit some new storage tiers that have been provisioned by a storage administrator, the partitions of a large heap table must be moved to other tablespaces in your Oracle 12c database?

Both local and global partitioned B-tree Indexes are defined on the table.

A high volume of transactions access the table during the day and a medium volume of transactions access it at night and during weekends. Minimal disrupt ion to availability is required.
Which three statements are true about this requirement? (Choose three.)

A. The partitions can be moved online to new tablespaces.

B. Global indexes must be rebuilt manually after moving the partitions.

C. The partitions can be compressed in the same tablespaces.

D. The partitions can be compressed in the new tablespaces.

E. Local indexes must be rebuilt manually after moving the partitions.

Answer 98

A. The partitions can be moved online to new tablespaces.

C. The partitions can be compressed in the same tablespaces.

D. The partitions can be compressed in the new tablespaces.

Question 99

In your production database, data manipulation language (DML) operations are executed on the SALES table.

You have noticed some dubious values in the SALES table during the last few days.

You are able to track users, actions taken, and the time of the action for this particular period but the changes in data are not tracked.
You decide to keep track of both the old data and new data in the table long with the user information.

What action would you take to achieve this task?

A. Apply fine-grained auditing.

B. Implement value-based auditing.

C. Impose standard database auditing to audit object privileges.

D. Impose standard database auditing to audit SQL statements.

Answer 99

B. Implement value-based auditing.

Question 100

The user SCOTT owns the CUST table that is placed in the SALES tablespace. The user SCOTT opens a session and executes commands as follows:

SQL> INSERT INTO cust VALUES(101, ‘JACK’);
1 row created.

SQL> INSERT INTO cust VALUES(102, ‘SMITH’);
1 row created.

As a DBA, you execute the following command from another session:
ALTER TABLESPACE sales READ ONLY;

Which statement is true regarding the effect of this command on the transaction in Scott’s session?

A. The command fails as a transaction is still pending.

B. The transaction in Scott’s session is rolled back and the tablespace becomes readonly.

C. The command waits and the user SCOTT can execute data manipulation language (DML) statements only as part of the current transaction.

D. The command hangs until all transactions on the objects in the tablespace commit or rollback, and then the tablespace is placed in readonly mode.

Answer 100

D. The command hangs until all transactions on the objects in the tablespace commit or rollback, and then the tablespace is placed in readonly mode.

Question 101

You plan to implement the distributed database system in your company.

You invoke Database Configuration Assistant (DBCA) to create a database on the server.

During the installation, DBCA prompts you to specify the Global Database Name.

What must this name be made up of?

A. It must be made up of a database name and a domain name.

B. It must be made up of the value in ORACLE_SID and HOSTNAME.

C. It must be made up of the value that you plan to assign for INSTANCE_NAME and HOSTNAME.

D. It must be made up of the value that you plan to assign for ORACLE_SID and SERVICE_NAMES.

Answer 101

A. It must be made up of a database name and a domain name.

Question 102

Which two statements are true about standard database auditing? (Choose two.)

A. DDL statements can be audited.

B. Statements that refer to standalone procedure can be audited.

C. Operations by the users logged on as SYSDBA cannot be audited.

D. Only one audit record is ever created for a session per audited statement even though it is executed more than once.

Answer 102

A. DDL statements can be audited.

B. Statements that refer to standalone procedure can be audited.

Question 103

You executed the following command to create a password file in the database server:

$ orapwd file = orapworcl entries = 5 ignorecase=N

Which statement describes the purpose of the above password file?

A. It records usernames and passwords of users when granted the DBA role

B. It contains usernames and passwords of users for whom auditing is enabled

C. It is used by Oracle to authenticate users for remote database administrator

D. It records usernames and passwords of all users when they are added to OSDBA or OSOPER operating groups

Answer 103

C. It is used by Oracle to authenticate users for remote database administrator

Question 104

Which three statements are true about Oracle Data Pump? (Choose three.)

A. IMPDP can be used to change target data file names, schemas, and tablespaces during import.

B. The DBMS_DATAPUMP PL/SQL package can be used independently of Data Pump clients to perform export and import operations.

C. EXPDP and IMPDP are the client components of Oracle Data Pump.

D. Oracle Data Pump export and import operations can be performed only by users with the SYSDBA privilege.

E. IMPDP always use the conventional path insert method to import data.

Answer 104

A. IMPDP can be used to change target data file names, schemas, and tablespaces during import.

B. The DBMS_DATAPUMP PL/SQL package can be used independently of Data Pump clients to perform export and import operations.

C. EXPDP and IMPDP are the client components of Oracle Data Pump.

Question 105

Your database instance has started using an SPFILE.
Examine the RMAN configuration settings:

You execute the command:
RMAN> BACKUP AS COPY TABLESPACE TEST;

Which three types of files are backed up by using this command? (Choose three.)

A. online redo log files

B. control file

C. SPFILE

D. archived redo log files

E. data file(s)

F. PFILE

Answer 105

B. control file

C. SPFILE

E. data file(s)

Question 106

Which three statements are true about Oracle Restart? (Choose three.)

A. It can be configured to automatically attempt to restart various components after a hardware or software failure.

B. While starting any components, it automatically attempts to start all dependencies first and in proper order.

C. It can be configured to automatically restart a database in case of normal shutdown of the database instance.

D. It can be used to only start Oracle components.

E. It runs periodic check operations to monitor the health of Oracle components.

Answer 106

B. While starting any components, it automatically attempts to start all dependencies first and in proper order.

D. It can be used to only start Oracle components.

E. It runs periodic check operations to monitor the health of Oracle components.

Question 107

You want to schedule a job to rebuild a table’s indexes after a bulk insert, which must be scheduled as soon as a file containing data arrives on the system.

What would you do to accomplish this?

A. Create a file watcher and an event-based job for bulk insert and then create another job to rebuild indexes on the table.

B. Create a file watcher for the bulk inserts and then create a job to rebuild indexes.

C. Create a job array and add a job for bulk insert and a job to rebuild indexes to the job array.

D. Create an event-based job for the file arrival event, then create a job for bulk insert, and then create a job to rebuild indexes.

Answer 107

A. Create a file watcher and an event-based job for bulk insert and then create another job to rebuild indexes on the table.

Question 108

You execute this command:
Which two statements are true about segment space management for segments in this tablespace? (Choose two.)

A. Space utilization inside segments is mapped by bitmaps.

B. Segments are automatically shrunk and compressed when rows are deleted.

C. The PCTFREE storage parameter has no effect on segments created in this tablespace.

D. The PCTUSED storage parameter has no effect on segments created in this tablespace.

Answer 108

A. Space utilization inside segments is mapped by bitmaps.

D. The PCTUSED storage parameter has no effect on segments created in this tablespace.

Question 109

You have successfully taken a database backup by using the command:

RMAN> BACKUP AS BACKUPSET DATABASE;

Now you execute this command:
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

What is the outcome?

A. It fails because an incremental level 1 backup always searches for an image copy as level 0 backup.

B. It fails because an incremental level 0 backup does not exist.

C. It takes a backup of blocks that have been formatted since the last full database backup.

D. It takes an incremental level 0 backup of the database.

E. It first takes an incremental level 0 backup and then an incremental level 1 backup.

Answer 109

E. It first takes an incremental level 0 backup and then an incremental level 1 backup.

Question 110

Which two actions does an incremental checkpoint perform? (Choose two.)

A. It signals CKPT to write the checkpoint position to the data file headers.

B. It writes the checkpoint position to the data file headers.

C. It advances the checkpoint position in the checkpoint queue.

D. It writes the checkpoint position to the control file.

Answer 110

C. It advances the checkpoint position in the checkpoint queue.

D. It writes the checkpoint position to the control file.