Oracle Test Bobby

Question 1

A user establishes a connection to a database instance by using an Oracle Net connection. You want to ensure the following:

1. The user account must be locked after five unsuccessful login attempts.
2. Data read per session must be limited for the user.
3. The user cannot have more than three simultaneous sessions.
4. The user must have a maximum of 10 minutes session idle time before being logged off automatically.

How would you accomplish this?

A. by granting a secure application role to the user

B. by implementing Database Resource Manager

C. by using Oracle Label Security options

D. by assigning a profile to the user

Answer 1

D. by assigning a profile to the user

Question 2

What happens if a maintenance window closes before a job that collects optimizer statistics completes?

A. The job is terminated and the gathered statistics are not saved.

B. The job is terminated but the gathered statistics are not published.

C. The job continues to run until all statistics are gathered.

D. The job is terminated and statistics for the remaining objects are collected the next time the maintenance window opens.

Answer 2

D. The job is terminated and statistics for the remaining objects are collected the next time the maintenance window opens.

Question 3

Which two statements are true about the logical storage structure of an Oracle database? (Choose two.)

A. An extent contains data blocks that are always physically contiguous on disk.

B. An extent can span multiple segments.

C. Each data block always corresponds to one operating system block.

D. It is possible to have tablespaces of different block sizes.

E. A data block is the smallest unit of I/O in data files.

Answer 3

D. It is possible to have tablespaces of different block sizes.

E. A data block is the smallest unit of I/O in data files.

Question 4

Which statement is true about the Log Writer process?

A. It writes when it receives a signal from the checkpoint process (CKPT).

B. It writes concurrently to all members of multiplexed redo log groups.

C. It writes after the Database Writer process writes dirty buffers to disk.

D. It writes when a user commits a transaction.

Answer 4

D. It writes when a user commits a transaction.

Question 5

The ORCL database is configured to support shared server mode.
You want to ensure that a user connecting remotely to the database instance has a one-to-one ratio between client and server processes.

Which connection method guarantees that this requirement is met?

A. connecting by using an external naming method

B. connecting by using the easy connect method

C. creating a service in the database by using the dbms_service.create_service procedure and using this
service for creating a local naming service

D. connecting by using the local naming method with the server = dedicated parameter set in the
tnsnames.ora file for the net service

E. connecting by using a directory naming method

Answer 5

D. connecting by using the local naming method with the server = dedicated parameter set in the
tnsnames.ora file for the net service

Question 6

The HR user receives the following error while inserting data into the sales table:

ERROR at line 1:

ORA-01653: unable to extend table HR.SALES by 128 in tablespace USERS

On investigation, you find that the users tablespace uses Automatic Segment Space Management (ASSM).
It is the default tablespace for the HR user with an unlimited quota on it.

Which two methods would you use to resolve this error? (Choose two.)

A. Altering the data file associated with the USERS tablespace to extend automatically

B. Adding a data file to the USERS tablespace

C. Changing segment space management for the USERS tablespace to manual

D. Creating a new tablespace with autoextend enabled and changing the default tablespace of the HR user
to the new tablespace

E. Enabling resumable space allocation by setting the RESUMABLE_TIMEOUT parameter to a nonzero value

Answer 6

A. Altering the data file associated with the USERS tablespace to extend automatically

B. Adding a data file to the USERS tablespace

Question 7

Which task would you recommend before using the Database Upgrade Assistant (DBUA) to upgrade a single-instance Oracle 11g R2 database to Oracle Database 12c?

A. shutting down the database instance that is being upgraded

B. executing the catctl.pl script to run the upgrade processes in parallel

C. running the Pre-Upgrade Information Tool

D. copying the listener.ora file to the new ORACLE_HOME

Answer 7

C. running the Pre-Upgrade Information Tool

Question 8

Your database is open and the listener LISTENER is up. You issue the command:

LSNRCTL> RELOAD

What is the effect of reload on sessions that were originally established by listener?

A. Only sessions based on static listener registrations are disconnected

B. Existing connections are not disconnected; however, they cannot perform any operations until the
listener completes the re-registration of the database instance and service handlers.

C. The sessions are not affected and continue to function normally.

D. All the sessions are terminated and active transactions are rolled back.

Answer 8

C. The sessions are not affected and continue to function normally.

Question 9

Examine the query and its output:

What might have caused three of the alerts to disappear?

A. The threshold alerts were cleared and transferred to DBA_ALERT_HISTORY.

B. An Automatic Workload Repository (AWR) snapshot was taken before the execution of the second
query.

C. An Automatic Database Diagnostic Monitor (ADOM) report was generated before the execution of the
second query.

D. The database instance was restarted before the execution of the second query

Answer 9

A. The threshold alerts were cleared and transferred to DBA_ALERT_HISTORY.

Question 10

Which two statements are true? (Choose two.)

A. A role cannot be assigned external authentication.

B. A role can be granted to other roles.

C. A role can contain both system and object privileges.

D. The predefined resource role includes the unlimited_tablespace privilege.

E. All roles are owned by the sys user.

F. The predefined connect role is always automatically granted to all new users at the time of their creation

Answer 10

B. A role can be granted to other roles.

C. A role can contain both system and object privileges.

Question 11

Identify three valid options for adding a pluggable database (PDB) to an existing multitenant container
database (CDB).

A. Use the CREATE PLUGGABLE DATABASE statement to create a PDB using the files from the SEED.

B. Use the CREATE DATABASE . . . ENABLE PLUGGABLE DATABASE statement to provision a PDB by
copying file from the SEED.

C. Use the DBMS_PDB package to clone an existing PDB.

D. Use the DBMS_PDB package to plug an Oracle 12c non-CDB database into an existing CDB.

E. Use the DBMS_PDB package to plug an Oracle 11 g Release 2 (11.2.0.3.0) non-CDB database into an
existing CDB.

Answer 11

A. Use the CREATE PLUGGABLE DATABASE statement to create a PDB using the files from the SEED.

C. Use the DBMS_PDB package to clone an existing PDB.

D. Use the DBMS_PDB package to plug an Oracle 12c non-CDB database into an existing CDB.

Question 12

The following parameter are set for your Oracle 12c database instance:

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE

You want to manage the SQL plan evolution task manually.

Examine the following steps:

1. Set the evolve task parameters.
2. Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.
3. Implement the recommendations in the task by using the DBMS_SPM.IMPLEMENT_EVOLVE_TASK
   function.
4. Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.
5. Report the task outcome by using the DBMS_SPM.REPORT_EVOLVE_TASK function.
   Identify the correct sequence of steps:

A. 2, 4, 5

B. 2, 1, 4, 3, 5

C. 1, 2, 3, 4, 5

D. 1, 2, 4, 5

Answer 12

B. 2, 1, 4, 3, 5

Question 13

In a recent Automatic Workload Repository (AWR) report for your database, you notice a high number of
buffer busy waits.

The database consists of locally managed tablespaces with free list managed segments.

On further investigation, you find that buffer busy waits is caused by contention on data blocks.

Which option would you consider first to decrease the wait event immediately?

A. Decreasing PCTUSED

B. Decreasing PCTFREE

C. Increasing the number of DBWN process

D. Using Automatic Segment Space Management (ASSM)

E. Increasing db_buffer_cache based on the V$DB_CACHE_ADVICE recommendation

Answer 13

D. Using Automatic Segment Space Management (ASSM)

Question 14

Examine the following command:

CREATE TABLE (prod_id number(4),
Prod_name varchar2 (20),
Category_id number(30),
Quantity_on_hand number (3) INVISIBLE);

Which three statements are true about using an invisible column in the PRODUCTS table? (Choose three.)

A. The %ROWTYPE attribute declarations in PL/SQL to access a row will not display the invisible column
in the output.

B. The DESCRIBE commands in SQL *Plus will not display the invisible column in the output.

C. Referential integrity constraint cannot be set on the invisible column.

D. The invisible column cannot be made visible and can only be marked as unused

E. A primary key constraint can be added on the invisible column.

Answer 14

A. The %ROWTYPE attribute declarations in PL/SQL to access a row will not display the invisible column
in the output.

B. The DESCRIBE commands in SQL *Plus will not display the invisible column in the output.

E. A primary key constraint can be added on the invisible column.

Question 15

What are two benefits of installing Grid Infrastructure software for a stand-alone server before installing and
creating an Oracle database?

A. Effectively implements role separation

B. Enables you to take advantage of Oracle Managed Files.

C. Automatically registers the database with Oracle Restart.

D. Helps you to easily upgrade the database from a prior release.

E. Enables the Installation of Grid Infrastructure files on block or raw devices

Answer 15

A. Effectively implements role separation

C. Automatically registers the database with Oracle Restart.

Question 16

Identify two correct statements about multitenant architectures.

A. Multitenant architecture can be deployed only in a Real Application Clusters (RAC) configuration.

B. Multiple pluggable databases (PDBs) share certain multitenant container database (CDB) resources.

C. Multiple CDBs share certain PDB resources.

D. Multiple non-RAC CDB instances can mount the same PDB as long as they are on the same server

E. Patches are always applied at the CDB level.

F. A PDB can have a private undo tablespace.

Answer 16

B. Multiple pluggable databases (PDBs) share certain multitenant container database (CDB) resources.

E. Patches are always applied at the CDB level.

Question 17

You notice a high number of waits for the db file scattered read and db file sequential read events in the
recent Automatic Database Diagnostic Monitor (ADDM) report.

After further investigation, you find that queries are performing too many full table scans and indexes are
not being used even though the filter columns are indexed.

Identify three possible reasons for this.

A. Missing or stale histogram statistics

B. Undersized shared pool

C. High clustering factor for the indexes

D. High value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter

E. Oversized buffer cache

Answer 17

A. Missing or stale histogram statistics

C. High clustering factor for the indexes

D. High value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter

Question 18

Which three features work together, to allow a SQL statement to have different cursors for the same
statement based on different selectivity ranges? (Choose three.)

A. Bind Variable Peeking

B. SQL Plan Baselines

C. Adaptive Cursor Sharing

D. Bind variable used in a SQL statement

E. Literals in a SQL statement

Answer 18

A. Bind Variable Peeking

C. Adaptive Cursor Sharing

D. Bind variable used in a SQL statement

Question 19

Which three statements are true about Automatic Workload Repository (AWR)? (Choose three.)

A. All AWR tables belong to the SYSTEM schema.

B. The AWR data is stored in memory and in the database.

C. The snapshots collected by AWR are used by the self-tuning components in the database

D. AWR computes time model statistics based on time usage for activities, which are displayed in the
v$SYS time model and V$SESS_TIME_MODEL views.

E. AWR contains system wide tracing and logging information.

Answer 19

B. The AWR data is stored in memory and in the database.

C. The snapshots collected by AWR are used by the self-tuning components in the database

D. AWR computes time model statistics based on time usage for activities, which are displayed in the
v$SYS time model and V$SESS_TIME_MODEL views.

Question 20

You upgraded your database from pre-12c to a multitenant container database (CDB) containing pluggable
databases (PDBs).

Examine the query and its output:

Which two tasks must you perform to add users with SYSBACKUP, SYSDG, and SYSKM privilege to the
password file? (Choose two.)

A. Assign the appropriate operating system groups to SYSBACKUP, SYSDG, SYSKM.

B. Grant SYSBACKUP, SYSDG, and SYSKM privileges to the intended users.

C. Re-create the password file with SYSBACKUP, SYSDG, and SYSKM privilege and the FORCE argument set to No.

D. Re-create the password file with SYSBACKUP, SYSDG, and SYSKM privilege, and FORCE arguments
set to Yes.

E. Re-create the password file in the Oracle Database 12c format.

Answer 20

B. Grant SYSBACKUP, SYSDG, and SYSKM privileges to the intended users.

D. Re-create the password file with SYSBACKUP, SYSDG, and SYSKM privilege, and FORCE arguments
set to Yes.

Question 21

You are planning the creation of a new multitenant container database (CDB) and want to store the ROOT
and SEED container data files in separate directories.

You plan to create the database using SQL statements.
Which three techniques can you use to achieve this? (Choose three.)

A. Use Oracle Managed Files (OMF).

B. Specify the SEED FILE_NAME_CONVERT clause.

C. Specify the PDB_FILE_NAME_CONVERT initialization parameter.

D. Specify the DB_FILE_NAMECONVERT initialization parameter.

E. Specify all files in the CREATE DATABASE statement without using Oracle managed Files (OMF).

Answer 21

A. Use Oracle Managed Files (OMF).

B. Specify the SEED FILE_NAME_CONVERT clause.

C. Specify the PDB_FILE_NAME_CONVERT initialization parameter.

Question 22

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

The characteristics of the non-CDB are as follows:
Version: Oracle Database 11g Release 2 (11.2.0.2.0) 64-bit
Character set: AL32UTF8
National character set: AL16UTF16
O/S: Oracle Linux 6 64-bit

The characteristics of the CDB are as follows:
Version: Oracle Database 12c Release 1 64-bit
Character Set: AL32UTF8
National character set: AL16UTF16
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 22

B. Transportable tablespace

Question 23

Examine the following query output:
You issue the following command to import tables into the hr schema:

$ > impdp hr/hr directory = dumpdir dumpfile = hr_new.dmp schemas=hr
TRANSFORM=DISABLE_ARCHIVE_LOGGING: Y

Which statement is true?

A. All database operations performed by the impdp command are logged.

B. Only CREATE INDEX and CREATE TABLE statements generated by the import are logged.

C. Only CREATE TABLE and ALTER TABLE statements generated by the import are logged.

D. None of the operations against the master table used by Oracle Data Pump to coordinate its activities
are logged.

Answer 23

C. Only CREATE TABLE and ALTER TABLE statements generated by the import are logged.

Question 24

Examine the parameter for your database instance:

Identify the reason why the optimizer chose different execution plans.

A. The optimizer used a dynamic plan for the query.

B. The optimizer chose different plans because automatic dynamic sampling was enabled.

C. The optimizer used re-optimization cardinality feedback for the query.

D. The optimizer chose different plan because extended statistics were created for the columns used.

Answer 24

A. The optimizer used a dynamic plan for the query.

Question 25

You create a new pluggable database, HR_PDB, from the seed database. Which three tablespaces are
created by default in HR_PDB? (Choose three.)

A. SYSTEM

B. SYSAUX

C. EXAMPLE

D. UNDO

E. TEMP

F. USERS

Answer 25

A. SYSTEM

B. SYSAUX

E. TEMP

Question 26

Which two statements are true about variable extent size support for large ASM files? (Choose two.)

A. The metadata used to track extents in SGA is reduced.

B. Rebalance operations are completed faster than with a fixed extent size

C. An ASM Instance automatically allocates an appropriate extent size.

D. Resync operations are completed faster when a disk comes online after being taken offline.

E. Performance improves in a stretch cluster configuration by reading from a local copy of an extent.

Answer 26

A. The metadata used to track extents in SGA is reduced.

C. An ASM Instance automatically allocates an appropriate extent size.

Question 27

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.

Answer 27

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

Question 28

You have installed two 64G flash devices to support the Database Smart Flash Cache feature on your
database server that is running on Oracle Linux.

You have set the DB_SMART_FLASH_FILE parameter:
DB_FLASH_CACHE_FILE= /dev/flash_device_1 , /dev/flash_device_2

How should the DB_FLASH_CACHE_SIZE be configured to use both devices?

A. Set DB_FLASH_CACHE_ZISE = 64G.

B. Set DB_FLASH_CACHE_ZISE = 64G, 64G

C. Set DB_FLASH_CACHE_ZISE = 128G.

D. DB_FLASH_CACHE_SIZE is automatically configured by the instance at startup.

Answer 28

B. Set DB_FLASH_CACHE_ZISE = 64G, 64G

Question 29

Examine the contents of SQL loader control file:

Which three statements are true regarding the SQL* Loader operation performed using the control file?
(Choose three.)

A. An EMP table is created if a table does not exist. Otherwise, if the EMP table is appended with the
loaded data.

B. The SQL* Loader data file myfile1.dat has the column names for the EMP table.

C. The SQL* Loader operation fails because no record terminators are specified.

D. Field names should be the first line in the both the SQL* Loader data files.

E. The SQL* Loader operation assumes that the file must be a stream record format file with the normal
carriage return string as the record terminator.

Answer 29

A. An EMP table is created if a table does not exist. Otherwise, if the EMP table is appended with the
loaded data.

B. The SQL* Loader data file myfile1.dat has the column names for the EMP table.

E. The SQL* Loader operation assumes that the file must be a stream record format file with the normal
carriage return string as the record terminator.

Question 30

Which two statements are true concerning the Resource Manager plans for individual pluggable databases
(PDB plans) in a multitenant container database
(CDB)? (Choose two.)

A. If no PDB plan is enabled for a pluggable database, then all sessions for that PDB are treated to an
equal degree of the resource share of that PDB.

B. In a PDB plan, subplans may be used with up to eight consumer groups.

C. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups
across all PDBs in the CDB.

D. If no PDB plan is enabled for a pluggable database, then the PDB share in the CDB plan is dynamically
calculated.

E. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups
based on the shares provided to the PDB in the CDB plan

Answer 30

A. If no PDB plan is enabled for a pluggable database, then all sessions for that PDB are treated to an
equal degree of the resource share of that PDB.

E. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups
based on the shares provided to the PDB in the CDB plan

Question 31

You use a recovery catalog for maintaining your database backups.

You execute the following command:
$rman TARGET / CATALOG rman / cat@catdb
RMAN > BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Which two statements are true? (Choose two.)

A. Corrupted blocks, if any, are repaired.

B. Checks are performed for physical corruptions.

C. Checks are performed for logical corruptions.

D. Checks are performed to confirm whether all database files exist in correct locations

E. Backup sets containing both data files and archive logs are created.

Answer 31

B. Checks are performed for physical corruptions.

D. Checks are performed to confirm whether all database files exist in correct locations

Question 32

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? (Choose two.)

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.

Answer 32

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

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

Question 33

Which three statements are true about using flashback database in a multitenant container database

A. The root container can be flashed back without flashing back the pluggable databases (PDBs).

B. To enable flashback database, the CDB must be mounted.

C. Individual PDBs can be flashed back without flashing back the entire CDB.

D. The DB_FLASHBACK RETENTION_TARGET parameter must be set to enable flashback of the CDB.

E. A CDB can be flashed back specifying the desired target point in time or an SCN, but not a restore
point.

(CDB)? (Choose three.)

Answer 33

A. The root container can be flashed back without flashing back the pluggable databases (PDBs).

B. To enable flashback database, the CDB must be mounted.

D. The DB_FLASHBACK RETENTION_TARGET parameter must be set to enable flashback of the CDB.

Question 34

You support Oracle Database 12c Oracle Database 11g, and Oracle Database log on the same server.
All databases of all versions use Automatic Storage Management (ASM).

Which three statements are true about the ASM disk group compatibility attributes that are set for a disk
group? (Choose three.)

A. The ASM compatibility attribute controls the format of the disk group metadata.

B. RDBMS compatibility together with the database version determines whether a database Instance can
mount the ASM disk group.

C. The RDBMS compatibility setting allows only databases set to the same version as the compatibility
value, to mount the ASM disk group.

D. The ASM compatibility attribute determines some of the ASM features that may be used by the Oracle
disk group.

E. The ADVM compatibility attribute determines the ACFS features that may be used by the Oracle 10 g
database.

Answer 34

A. The ASM compatibility attribute controls the format of the disk group metadata.

B. RDBMS compatibility together with the database version determines whether a database Instance can
mount the ASM disk group.

D. The ASM compatibility attribute determines some of the ASM features that may be used by the Oracle
disk group.

Question 35

You executed this command to create a password file:

$ orapwd file = orapworcl entries = 10 ignorecase = N

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

A. It will permit the use of uppercase passwords for database users who have been granted the SYSOPER
role.

B. It contains username and passwords of database users who are members of the OSOPER operating
system group.

C. It contains usernames and passwords of database users who are members of the OSDBA operating
system group.

D. It will permit the use of lowercase passwords for database users who have granted the SYSDBA role.

E. It will not permit the use of mixed case passwords for the database users who have been granted the
SYSDBA role.

Answer 35

A. It will permit the use of uppercase passwords for database users who have been granted the SYSOPER
role.

D. It will permit the use of lowercase passwords for database users who have granted the SYSDBA role.

Question 36

Identify three valid methods of opening, pluggable databases (PDBs).

A. ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root

B. ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from a PDB

C. ALTER PLUGGABLE DATABASE PDB OPEN issued from the seed

D. ALTER DATABASE PDB OPEN issued from the root

E. ALTER DATABASE OPEN issued from that PDB

F. ALTER PLUGGABLE DATABASE PDB OPEN issued from another PDB

G. ALTER PLUGGABLE DATABASE OPEN issued from that PDB

Answer 36

A. ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root

E. ALTER DATABASE OPEN issued from that PDB

G. ALTER PLUGGABLE DATABASE OPEN issued from that PDB

Question 37

You administer an online transaction processing (OLTP) system whose database is stored in
Automatic Storage Management (ASM) and whose disk group use normal redundancy.

One of the ASM disks goes offline, and is then dropped because it was not brought online before
DISK_REPAIR_TIME elapsed.

When the disk is replaced and added back to the disk group, the ensuing rebalance operation is too slow.

Which two recommendations should you make to speed up the rebalance operation if this type of
failure happens again? (Choose two.)

A. Increase the value of the ASM_POWER_LIMIT parameter.

B. Set the DISK_REPAIR_TIME disk attribute to a lower value.

C. Specify the statement that adds the disk back to the disk group.

D. Increase the number of ASMB processes.

E. Increase the number of DBWR_IO_SLAVES in the ASM instance.

Answer 37

A. Increase the value of the ASM_POWER_LIMIT parameter.

D. Increase the number of ASMB processes.

Question 38

Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in
Oracle database 12c? (Choose two.)

A. ALTER TABLE SPLIT PARTITION

B. ALTER TABLE MERGE PARTITION

C. ALTER TABLE TRUNCATE PARTITION

D. ALTER TABLE ADD PARTITION

E. ALTER TABLE DROP PARTITION

F. ALTER TABLE MOVE PARTITION

Answer 38

C. ALTER TABLE TRUNCATE PARTITION

E. ALTER TABLE DROP PARTITION

Question 39

Which four actions are possible during an Online Data file Move operation? (Choose four.)

A. Creating and dropping tables in the data file being moved

B. Performing file shrink of the data file being moved

C. Querying tables in the data file being moved

D. Performing Block Media Recovery for a data block in the data file being moved

E. Flashing back the database

F. Executing DML statements on objects stored in the data file being moved

Answer 39

A. Creating and dropping tables in the data file being moved

C. Querying tables in the data file being moved

D. Performing Block Media Recovery for a data block in the data file being moved

F. Executing DML statements on objects stored in the data file being moved

Question 40

Your multitenant container database (CDB) contains a pluggable database, HR_PDB.

The default permanent tablespace in HR_PDB is USERDATA.

The container database (CDB) is open and you connect RMAN.

You want to issue the following RMAN command:
RMAN > BACKUP TABLESPACE hr_pdb:userdata;

Which task should you perform before issuing the command?

A. Place the root container in ARHCHIVELOG mode.

B. Take the user data tablespace offline.

C. Place the root container in the nomount stage.

D. Ensure that HR_PDB is open.

Answer 40

A. Place the root container in ARHCHIVELOG mode.

Question 41

You install a non-RAC Oracle Database.

During Installation, the Oracle Universal Installer (OUI) prompts you to enter the path of the Inventory directory and also to specify an operating system group name.

Which statement is true?

A. The ORACLE_BASE base parameter is not set.

B. The installation is being performed by the root user.

C. The operating system group that is specified should have the root user as its member.

D. The operating system group that is specified must have permission to write to the inventory directory.

Answer 41

D. The operating system group that is specified must have permission to write to the inventory directory.

Question 42

In your multitenant container database (CDB) with two pluggable database (PDBs).

You want to create a new PDB by using SQL Developer.

Which statement is true?

A. The CDB must be open.

B. The CDB must be in the mount stage.

C. The CDB must be in the nomount stage.

D. Alt existing PDBs must be closed.

Answer 42

A. The CDB must be open.

Question 43

Examine the parameters for your database instance:

Which three statements are true about the process of automatic optimization by using cardinality feedback?
(Choose three.)

A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is
a huge difference in optimizer estimates and execution statistics.

B. The optimizer can re optimize a query only once using cardinality feedback.

C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.

D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are
enabled.

E. After the optimizer identifies a query as a re-optimization candidate, statistics collected by the collectors are submitted to the optimizer.

Answer 43

A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is
a huge difference in optimizer estimates and execution statistics.

C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.

D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are
enabled.

Question 44

Which three statements are true when the listener handles connection requests to an Oracle 12c database instance with multithreaded architecture enabled In UNIX? (Choose three.)

A. Thread creation must be routed through a dispatcher process

B. The local listener may spawn a now process and have that new process create a thread

C. Each Oracle process runs an SCMN thread.

D. Each multithreaded Oracle process has an SCMN thread.

E. The local listener may pass the request to an existing process which in turn will create a thread.

Answer 44

A. Thread creation must be routed through a dispatcher process

D. Each multithreaded Oracle process has an SCMN thread.

E. The local listener may pass the request to an existing process which in turn will create a thread.

Question 45

You are connected using SQL* Plus to a multitenant container database (CDB) with SYSDBA privileges and
execute the following sequence statements:

What is the result of the last SET CONTAINER statement and why is it so?

A. It succeeds because the PDB_ADMIN user has the required privileges.

B. It fails because common users are unable to use the SET CONTAINER statement.

C. It fails because local users are unable to use the SET CONTAINER statement.

D. If fails because the SET CONTAINER statement cannot be used with PDB$SEED as the target pluggable database (PDB).

Answer 45

C. It fails because local users are unable to use the SET CONTAINER statement.

Question 46

Examine the details of the Top 5 Timed Events in the following Automatic Workloads Repository (AWR)
report:

What are three possible causes for the latch-related wait events?

A. The size of the shared pool is too small.

B. Cursors are not being shared.

C. A large number COMMITS are being performed.

D. There are frequent logons and logoffs.

E. The buffers are being read into the buffer cache, but some other session is changing the buffers.

Answer 46

A. The size of the shared pool is too small.

B. Cursors are not being shared.

D. There are frequent logons and logoffs.

Question 47

You enabled an audit policy by issuing the following statements:

SQL> AUDIT POLICY ORA_DATABASE_PARAMETER BY SCOTT;

SQL> AUDIT POLICY ORA_DATABASE_PARAMETER BY SYS, SYSTEM;

For which database users and for which executions is the audit policy now active? Select two.

A. SYS, SYSTEM

B. SCOTT

C. Only for successful executions

D. Only for failed executions

E. Both successful and failed executions

Answer 47

A. SYS, SYSTEM

E. Both successful and failed executions

Question 48

Your multitenant container database (CDB) contains some pluggable databases (PDBs), you execute the
following command in the root container:

Which two statements are true? (Choose two.)

A. Schema objects owned by the C# # A_ADMIN common user can be shared across all PDBs

B. The C # # A_ADMIN user will be able to use the TEMP_TS temporary tablespace only in root.

C. The command will, create a common user whose description is contained in the root and each PDB.

D. The schema for the common user C # # A_ADMIN can be different in each container.

E. The command will create a user in the root container only because the container clause is not used.

Answer 48

C. The command will, create a common user whose description is contained in the root and each PDB.

D. The schema for the common user C # # A_ADMIN can be different in each container.

Question 49

You find this query being used in your Oracle 12c database:

Which method a used by the optimizer to limit the rows being returned?

A. A filter is added to the table query dynamically using ROWNUM to limit the rows to 20 percent of the
total rows

B. All the rows are returned to the client or middle tier but only the first 20 percent are returned to the
screen or the application.

C. A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.

D. A TOP-N query is created to limit the rows to 20 percent of the total rows

Answer 49

C. A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.

Question 50

On your Oracle Database, you issue the following commands to create indexes:

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

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

Which two statements are true? (Choose two.)

A. Only the ORD_CUSTOMER_IX1 index created.

B. Both the indexes are updated when a row is inserted, updated, or deleted in the ORDERS table.

C. Both the indexes are created: however, only ORD_CUSTOMERS_IX1 is used by the optimizer for
queries on the ORDERS table.

D. The ORD_CUSTOMER_IX1 index is not used by the optimizer even when the
OPTIMIZER_USE_INVISIBLE_INDEXES parameters is set to true.

E. Both the indexes are created and used by the optimizer for queries on the ORDERS table.

F. Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer for
queries on the ORDERS table.

Answer 50

B. Both the indexes are updated when a row is inserted, updated, or deleted in the ORDERS table.

F. Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer for
queries on the ORDERS table.

Question 51

You have altered a non-unique index to be invisible to determine if queries execute within an
acceptable response time without using this index.

Which two are possible if table updates are performed which affect the invisible index columns?
(Choose two.)

A. The index remains invisible.

B. The index is not updated by the DML statements on the indexed table.

C. The index automatically becomes visible in order to have it updated by DML on the table.

D. The index becomes unusable but the table is updated by the DML.

E. The index is updated by the DML on the table.

Answer 51

A. The index remains invisible.

E. The index is updated by the DML on the table.

Question 52

The persistent configuration settings for RMAN have default for all parameters.
Identify four RMAN commands that produce a multi-section backup.

A. BACKUP TABLESPACE SYSTEM SECTION SIZE 100M;

B. BACKUP AS COPY TABLESPACE SYSTEM SECTION SIZE 100M;

C. BACKUP ARCHIVELOG ALL SECTION SIZE 25M;

D. BACKUP TABLESPACE “TEMP” SECTION SIZE 10M;

E. BACKUP TABLESPACE “UNDO” INCLUDE CURRENT CONTROLFILE SECTION SIZE 100M;

F. BACKUP SPFILE SECTION SIZE 1M;

G. BACKUP INCREMENTAL LEVEL 0 TABLESPACE SYSAUX SECTION SIZE 100M;

Answer 52

A. BACKUP TABLESPACE SYSTEM SECTION SIZE 100M;

B. BACKUP AS COPY TABLESPACE SYSTEM SECTION SIZE 100M;

E. BACKUP TABLESPACE “UNDO” INCLUDE CURRENT CONTROLFILE SECTION SIZE 100M;

G. BACKUP INCREMENTAL LEVEL 0 TABLESPACE SYSAUX SECTION SIZE 100M;

Question 53

Your database has the SRV1 service configured for an application that runs on middle-tier application
server.

The application has multiple modules.
You enable tracing at the service level by executing the following command:

SQL > exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (SRV1);

The possible outcome and actions to aggregate the trace files are as follows:

1. The command fails because a module name is not specified.
2. A trace file is created for each session that is running the SRV1 service.
3. An aggregated trace file is created for all the sessions that are running the SRV1 service.
4. The trace files may be aggregated by using the trcess utility.
5. The trace files be aggregated by using the tkprof utility.
   Identify the correct outcome and the step to aggregate by using tkprof utility?

A. 1

B. 2 and 4

C. 2 and 5

D. 3 and 4

E. 3 and 5

Answer 53

B. 2 and 4

Question 54

Your multitenant container database (CDB) contains pluggable databases (PDBs), you are connected to the
HR_PDB.

You execute the following command:

SQL > CREATE UNDO TABLESPACE undotb01
DATAFILE u01/oracle/rddb1/undotbs01.dbf SIZE 60M AUTOEXTEND ON;

What is the result?

A. It executes successfully and creates an UNDO tablespace in HR_PDB.

B. It falls and reports an error because there can be only one undo tablespace in a CDB.

C. It fails and reports an error because the CONTAINER=ALL clause is not specified in the command.

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

E. It executes successfully but neither tablespace nor the data file is created.

Answer 54

E. It executes successfully but neither tablespace nor the data file is created

Question 55

You want to flash back a test database by five hours.
You issue this command:

SQL > FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - 5/24);

Which two statements are true about this flashback scenario? (Choose two.)

A. The database must have multiplexed redo logs for the flashback to succeed.

B. The database must be MOUNTED for the flashback to succeed.

C. The database must use block change tracking for the flashback to succeed.

D. The database must be opened in restricted mode for the flashback to succeed.

E. The database must be opened with the RESETLOGS option after the flashback is complete.

F. The database must be opened in read-only mode to check if the database has been flashed back to the
correct SCN.

Answer 55

B. The database must be MOUNTED for the flashback to succeed.

E. The database must be opened with the RESETLOGS option after the flashback is complete.

Question 56

Which two statements are true about Oracle Data Pump export and import operations? (Choose two.)

A. You can detach from a data pump export job and reattach later.

B. Data pump uses parallel execution server processes to implement parallel import.

C. Data pump import requires the import file to be in a directory owned by the oracle owner.

D. The master table is the last object to be exported by the data pump.

E. You can detach from a data pump import job and reattach later.

Answer 56

A. You can detach from a data pump export job and reattach later.

E. You can detach from a data pump import job and reattach later.

Question 57

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.

Answer 57

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

Question 58

Which two are prerequisites for performing a flashback transaction? (Choose two.)

A. Flashback Database must be enabled.

B. Undo retention guarantee for the database must be configured.

C. EXECUTE privilege on the DBMS_FLASHBACK package must be granted to the user flashing back transaction.

D. Supplemental logging must be enabled.

E. Recycle bin must be enabled for the database.

F. Block change tracking must be enabled tor the database.

Answer 58

C. EXECUTE privilege on the DBMS_FLASHBACK package must be granted to the user flashing back transaction.

D. Supplemental logging must be enabled.

Question 59

A database is stored in an Automatic Storage Management (ASM) disk group, disk group, DGROUP1 with
SQL:

There is enough free space in the disk group for mirroring to be done.

What happens if the CONTROLLER1 failure group becomes unavailable due to error of for maintenance?

A. Transactions and queries accessing database objects contained in any tablespace stored in DGROUP1
will fall.

B. Mirroring of allocation units will be done to ASM disks in the CONTROLLER2 failure group until the
CONTROLLER1 for failure group is brought back online.

C. The data in the CONTROLLER1 failure group is copied to the controller2 failure group and rebalancing
is initiated.

D. ASM does not mirror any data until the controller failure group is brought back online, and newly
allocated primary allocation units (AU) are stored in the controller2 failure group, without mirroring.

E. Transactions accessing database objects contained in any tablespace stored in DGROUP1 will fail but
queries will succeed.

Answer 59

D. ASM does not mirror any data until the controller failure group is brought back online, and newly
allocated primary allocation units (AU) are stored in the controller2 failure group, without mirroring.

Question 60

Your multitenant container database has three pluggable databases (PDBs): PDB1, PDB2, and PDB3.

Which two RMAN commands may be; used to back up only the PDB1 pluggable database? (Choose two.)

A. BACKUP PLUGGABLE DATABASE PDB1 while connected to the root container

B. BACKUP PLUGGABLE DATABASE PDB1 while connected to the PDB1 container

C. BACKUP DATABASE while connected to the PDB1 container

D. BACKUP DATABASE while connected to the boot container

E. BACKUP PLUGGABLE database PDB1 while connected to PDB2

Answer 60

A. BACKUP PLUGGABLE DATABASE PDB1 while connected to the root container

C. BACKUP DATABASE while connected to the PDB1 container

Question 61

Your multitenant container (CDB) contains two pluggable databases (PDB), HR_PDB and ACCOUNTS_PDB, both of which use the CDB tablespace. The temp file is called temp01.tmp.

A user issues a query on a table on one of the PDBs and receives the following error:

ERROR at line 1:
ORA-01565: error in identifying file /u01/app/oracle/oradata/CDB1/temp01.tmp
ORA-27037: unable to obtain file status

Identify two ways to rectify the error.

A. Add a new temp file to the temporary tablespace and drop the temp file that that produced the error.

B. Shut down the database instance, restore the temp01.tmp file from the backup, and then restart the
database.

C. Take the temporary tablespace offline, recover the missing temp file by applying redo logs, and then
bring the temporary tablespace online.

D. Shutdown the database instance, restore and recover the temp file from the backup, and then open the
database with RESETLOGS.

E. Shut down the database instance and then restart the CDB and PDBs.

Answer 61

A. Add a new temp file to the temporary tablespace and drop the temp file that that produced the error.

E. Shut down the database instance and then restart the CDB and PDBs.

Question 62

Your multitenant container database, CDB1, is running in ARCHIVELOG mode and has two pluggable
databases, HR_PDB and ACCOUNTS_PDB.
An RMAN backup exists for the database.

You issue the command to open ACCOUNTS_PDB and find that the USERDATA.DBF data file for the default permanent tablespace USERDATA belonging to ACCOUNTS_PDB is corrupted.

What should you do before executing the commands to restore and recover the data file in
ACCOUNTS_PDB?

A. Place CDB1 in the mount stage and then the USERDATA tablespace offline in ACCOUNTS_PDB.

B. Place CDB1 in the mount stage and issue the ALTER PLUGGABLE DATABASE accounts_pdb CLOSE
IMMEDIATE command.

C. Issue the ALTER PLUGGABLE DATABASE accounts_pdb RESTRICTED command.

D. Take the USERDATA tablespace offline in ACCOUNTS_PDB.

Answer 62

D. Take the USERDATA tablespace offline in ACCOUNTS_PDB.

Question 63

Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose two.)

A. The ADDM requires at least four AWR snapshots for analysis

B. The ADDM runs after each AWR snapshot is collected automatically by MMON

C. The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR)

D. The ADDM analysis provides only diagnostics information but does not provide recommendations

E. The ADDM calls other advisors if required, but does not provide recommendations about the advisors

Answer 63

B. The ADDM runs after each AWR snapshot is collected automatically by MMON

C. The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR)

Question 64

Which three statements are true about space usage alerts? (Choose three.)

A. Alerts are issued only when the critical threshold for space available in a tablespace is breached.

B. The sum of active extents and allocated user quotas is considered to compute space usage for an undo
tablespace.

C. Database alerts can provide warnings about low space availability at both tablespace and segment levels.

D. Alerts are not issued for locally managed tablespaces that are offline or in read-only mode.

E. A newly created locally managed tablespace is automatically assigned the default threshold values
defined for a database.

Answer 64

C. Database alerts can provide warnings about low space availability at both tablespace and segment levels.

D. Alerts are not issued for locally managed tablespaces that are offline or in read-only mode.

E. A newly created locally managed tablespace is automatically assigned the default threshold values
defined for a database.

Question 65

You have the following entry in the tnsnames.ors of your hq.us.example.com host machine:

You issue the following command at the command prompt:
Sqlplus HR/HR@ORCL -

Which statement is true about the connection to the ORCL database instance?.

A. The connection succeeds, provided the NAMES.DEFAULT_DOMAIN parameter is set to
us.example.com in the sqlnet.ora file on the client side.

B. The connection fails because the net service name does not have the suffix us.example.com.

C. The connection succeeds, provided the SERVICE_NAMES initialization parameter is set to ORCL.

D. The connection succeeds, provided the ORCL.us.example.com database service is registered with a listener, the listener is up, and the database is open.

Answer 65

D. The connection succeeds, provided the ORCL.us.example.com database service is registered with a listener, the listener is up, and the database is open.

Question 66

Which two statements are true about the (PMON) background process in Oracle Database 12c? (Choose
two.)

A. It records checkpoint information in the control file.

B. It frees unused temporary segments.

C. It kills sessions that exceed idle time.

D. It registers database services with all local and remote listeners known to the database instance.

E. It frees resources held by abnormally terminated processes.

Answer 66

C. It kills sessions that exceed idle time.

E. It frees resources held by abnormally terminated processes.

Question 67

You want to prevent a group of users in your database from performing long-running transactions
that consume huge amounts of space in the undo tablespace.

If the quota for these users is exceeded during execution of a data manipulation language (DML)
statement, the operation should abort and return an error.

However, queries should still be allowed, even if users have exceeded the undo space limitation.
How would you achieve this?

A. Specify the maximum amount of quota a user can be allocated in the undo tablespace.

B. Decrease the number of Interested Transaction List (ITL) slots for the segments on which these users perform transactions.

C. Implement a profile for these users.

D. Implement a Database Resource Manager plan.

Answer 67

D. Implement a Database Resource Manager plan.

Question 68

A database instance is started by using an SPFILE.
The database is configured in ARCHIVELOG mode and the control file autobackup is configured.

Daily full database backups are performed by using RMAN.
You lost all control files due to media failure.
Given the steps to recover from the error in random order:

1. Shut down the instance, if it is not already down.
2. Restore the control file from autobackup to a new location.
3. Start the database instance to NOMOUNT state.
4. Recover the database to the point of failure of the control file.
5. Open the database with the RESETLOGS option.
6. Mount the database.
7. Update the SPFILE with the new location of the control file by using the ALTER SYSTEM command.

Identify the correct sequence of the required steps.

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

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

C. 1, 3, 2, 4, 5

D. 1, 2, 6, 4, 5

E. 1, 6, 2, 4, 5

Answer 68

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

Question 69

What can be automatically implemented after the SQL Tuning Advisor is run as part of the Automated
Maintenance Task?

A. statistics recommendations

B. SQL profile recommendations

C. SQL statement restructure recommendations

D. creation of materialized views to improve query performance

Answer 69

B. SQL profile recommendations

Question 70

You have just completed a manual upgrade of an Oracle 11g Database to Oracle Database 12c.
The Post-Upgrade Status Tool reports an INVALID status for some of the components after the upgrade.
What must you do first in this situation to attempt to fix this problem?

A. Run catuppst.sql to perform revalidation actions

B. Run utluiobj.sql to filter out objects that were invalidated by the upgrade process.

C. Run preupgrd.sql and then execute the generated “fix-up” scripts to resolve status issues.

D. Run utlrp.sql to recompile stored PL/SQL and Java code and check the DBA_REGISTRY view.

Answer 70

D. Run utlrp.sql to recompile stored PL/SQL and Java code and check the DBA_REGISTRY view.

Question 71

What should you do to ensure that a job stores minimal job metadata and runtime data on disk, and uses only existing PL/SQL programs?

A. Create an event-based job.

B. Create a lightweight job.

C. Specify the job as a member of a job class.

D. Use a job array.

Answer 71

B. Create a lightweight job.

Question 72

You plan to install the Oracle Database 12c software on a new server. The database will use Automatic
Storage Management (ASM) and Oracle Restart.

Oracle Grid Infrastructure for a standalone server is already installed on the server.

You want to configure job role separation. You create the following operating system users and groups:

• The user oracle as the owner of the Oracle database installation
• The user grid as the owner of Oracle Grid Infrastructure
• The group oinstall as an Oracle Inventory group
• The group dba as the OSDBA group for Oracle database
• The group asmdba as the OSDBA group for Oracle ASM
• The group asmadmin as the administration privileges group for Oracle ASM
• The group asmoper as the group for Oracle ASM

Which two additional tasks should you perform with regard to the OS-level owners and groups? (Choose
two.)

A. creating a separate central inventory group for the Oracle Database 12c installation

B. assigning oinstall as the primary group for the oracle user

C. assigning asmadmin and asmoper as primary groups for the oracle user

D. creating OS groups associated with the OSBACKUPDBA, OSDGDBA, and OSKMDBA system
privileges

E. assigning asmdba as the secondary group for the oracle user

Answer 72

B. assigning oinstall as the primary group for the oracle user

D. creating OS groups associated with the OSBACKUPDBA, OSDGDBA, and OSKMDBA system
privileges

Question 73

You want to reduce fragmentation and reclaim unused space for the SALES table but not its dependent
objects.

During this operation, you want to ensure the following:

• Long-running queries are not affected.
• No extra space is used.
• Data manipulation language (DML) operations on the table succeed at all times throughout the process.
• Unused space is reclaimed both above and below the high water mark.

Which ALTER TABLE option would you recommend?

A. DEALLOCATE UNUSED

B. SHRINK SPACE CASCADE

C. SHRINK SPACE COMPACT

D. ROW STORE COMPRESS BASIC

Answer 73

C. SHRINK SPACE COMPACT

Question 74

Examine the parameters for a database instance:
Which two statements are true? (Choose two.)

A. Undo records for temporary tables are stored in a temporary tablespace.

B. Undo records for temporary tables are stored in the undo tablespace and logged in the redo.

C. Undo records for temporary tables are stored in the undo tablespace and logged in the redo only for
those sessions where temporary undo is enabled.

D. No redo is generated for the undo records belonging to temporary tables.

E. No redo and undo records are generated for temporary tables.

Answer 74

A. Undo records for temporary tables are stored in a temporary tablespace.

D. No redo is generated for the undo records belonging to temporary tables.

Question 75

You set the following parameters in the parameter file and restart the database instance:

Which two statements are true? (Choose two.)

A. The MEMORY_MAX_TARGET parameter is automatically set to 500 MB.

B. The PGA_AGGREGATE_TARGET and SGA_TARGET parameters are automatically set to zero.

C. The value of the MEMORY_MAX_TARGET parameter remains zero for the database instance.

D. The lower limits of the PGA_AGGREGATE_TARGET and SGA_TARGET parameters are set to 90 MB
and 270 MB respectively.

E. The instance does not start up because Automatic Memory Management (AMM) is enabled but
PGA_AGGREGATE_TARGET and SGA_TARGET parameters are set to nonzero values.

Answer 75

A. The MEMORY_MAX_TARGET parameter is automatically set to 500 MB.

D. The lower limits of the PGA_AGGREGATE_TARGET and SGA_TARGET parameters are set to 90 MB
and 270 MB respectively.

Question 76

Your database is configured in ARCHIVELOG mode.
Examine the RMAN configuration parameters:

Examine the command:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

What is the outcome?

A. It fails because the DELETE INPUT option can be used only with the BACKUP AS BACKUPSET
command.

B. It executes successfully and creates a backup set of the database along with archived log files and then
deletes the original archived log files.

C. It executes successfully and creates an image copy of the database along with archive log files and then
deletes the original archived log files.

D. It fails because the DELETE INPUT option can be used only with the BACKUP AS COPY command.

Answer 76

B. It executes successfully and creates a backup set of the database along with archived log files and then
deletes the original archived log files.

Question 77

Your production database uses file system storage.
You want to move storage to Oracle Automatic Storage Management (ASM).

How would you achieve this?

A. by using a transportable database

B. by using the Database Upgrade Assistant (DBUA)

C. by using Data Pump

D. by using RMAN

Answer 77

D. by using RMAN

Question 78

In your Oracle 12c database, you plan to execute the command:

SQL> CREATE TABLESPACE tbs1 DATAFILE /u02/oracle/data/tbs01.dbf SIZE 50M;

The u02 file system has 1 GB of free space available.
What is the outcome?

A. It creates a locally managed tablespace with manual segment space management enabled.

B. It raises an error because extent management is not specified.

C. It creates a locally managed tablespace with automatic segment space management enabled.

D. It creates a dictionary-managed tablespace with manual segment space management enabled.

Answer 78

C. It creates a locally managed tablespace with automatic segment space management enabled.

Question 79

In your database, USERS is the default permanent tablespace.

Examine the commands and their outcome:

Which two statements are true? (Choose two.)

A. The MYTAB table is created in the SYSTEM tablespace but no rows can be inserted into the table by USER02.

B. The MYTAB table is created in the SYSTEM tablespace and rows can be inserted into the table by USER02.

C. The MYTAB table is created in the USERS tablespace but no rows can be inserted into the table by USER02.

D. The CREATE TABLE statement generates an error because the SYSDBA privilege does not provide
any space quota on the SYSTEM tablespace by default.

E. The MYTAB table is owned by the SYS user.

Answer 79

B. The MYTAB table is created in the SYSTEM tablespace and rows can be inserted into the table by USER02.

E. The MYTAB table is owned by the SYS user.

Question 80

You perform RMAN backups for your database and use a recovery catalog for managing the backups.
To free space, you execute this command:

RMAN> DELETE OBSOLETE;

Which three statements are true is this scenario? (Choose three.)

A. The backup sets marked as expired are deleted.

B. The information related to the backups is removed from the recovery catalog and the control file.

C. The physical files related to the backup need to be manually deleted.

D. The physical files related to the backup are deleted automatically.

E. The backups deleted are based on the backup retention policy.

Answer 80

B. The information related to the backups is removed from the recovery catalog and the control file.

D. The physical files related to the backup are deleted automatically.

E. The backups deleted are based on the backup retention policy.

Question 81

Your production database PROD uses file system storage.
You want to migrate storage including the Fast Recovery Area for the PROD database to Oracle Automatic Storage Management (ASM) by using RMAN.

You back up the entire database.
What should the next step be in this migration process?

A. enabling row movement for the database

B. disabling Oracle Flashback Database if enabled

C. opening the database in exclusive mode

D. placing all tablespaces in read-only mode

Answer 81

B. disabling Oracle Flashback Database if enabled

Question 82

Which two statements describe the relationship between a scheduler window, a resource manager plan,
and a job class? (Choose two.)

A. A scheduler window together with a job class, controls resource allocation for a job using that job class
in that scheduler window.

B. A job class specifies a scheduler window that will be open when that job class becomes active.

C. A scheduler window specifies a resource manager plan that will be activated when that scheduler window becomes active.

D. A scheduler window specifies a job class that will be activated when that scheduler window becomes
active.

E. A scheduler window can control resource allocation by itself.

Answer 82

A. A scheduler window together with a job class, controls resource allocation for a job using that job class in that scheduler window.

C. A scheduler window specifies a resource manager plan that will be activated when that scheduler window becomes active.

Question 83

Your database is configured in ARCHIVELOG mode, and daily full database backups are taken.

RMAN is configured to perform control file autobackups.
Which statement is true about the loss of a duplexed control file?

A. The database remains open but transactions are not permitted.

B. The database instance aborts, and media recovery is required after restoration of the control file to open
the database.

C. The database instance remains open and the control file can be restored without shutting down the
database.

D. The database instance aborts and a control file restore operation does not require media recovery.

Answer 83

C. The database instance remains open and the control file can be restored without shutting down the
database.

Question 84

Examine the parameter settings in your local ORCL database:

As the SYS user, you issue the following command on ORCL:

CREATE PUBLIC DATABASE LINK sales.us.example.com USING sales1;

The sales1 tnsnames entry points to the sales database instance.

Which two are requirements to ensure that you can successfully connect by using this database link?
(Choose two.)

A. The GLOBAL_NAMES initialization parameter should be set to FALSE in the ORCL database.

B. The SALES1 net service name should be known to both the local and remote databases.

C. Local database users who use this database link should have accounts and the required privileges in the remote database.

D. The REMOTE_OS_AUTHENT initialization parameter should be set to FALSE in the remote database.

E. Only the user creating the database link must have an account and the required privileges in the remote
database.

Answer 84

A. The GLOBAL_NAMES initialization parameter should be set to FALSE in the ORCL database.

C. Local database users who use this database link should have accounts and the required privileges in the remote database.

Question 85

Which three are activities performed by SMON? (Choose three.)

A. cleaning up the database buffer cache and freeing resources that a client process was using

B. applying online redo during instance recovery

C. cleaning up temporary segments that are no longer needed

D. performing database services registration with the default listener

E. restarting a server or a dispatcher process that terminated abnormally

F. recovering failed transactions that were skipped during instance recovery because of file-read or tablespace offline errors

Answer 85

B. applying online redo during instance recovery

C. cleaning up temporary segments that are no longer needed

F. recovering failed transactions that were skipped during instance recovery because of file-read or tablespace offline errors

Question 86

A user establishes a connection to a database instance by using an Oracle Net.

You want to ensure that:

1. The user account must be locked after five consecutive unsuccessful login attempts.
2. Data read per session must be limited for the user.
3. The user cannot have more than three simultaneous sessions.
4. The user must have a maximum of 10 minutes session idle time before being logged off automatically.

Which two would you do to implement this? (Choose two.)

A. by alerting the appropriate user attributes with an ALTER USER command

B. by using appropriate PASSWORD parameters set in the profile assigned to the user

C. by implementing Database Resource Manager and assign it a profile for the user

D. by implementing Database Resource Manager and assign it a role for the user

E. by using appropriate KERNEL parameters set in the profile assigned to the user

Answer 86

B. by using appropriate PASSWORD parameters set in the profile assigned to the user

E. by using appropriate KERNEL parameters set in the profile assigned to the user

Question 87

Which two statements are true about SQL *Loader Express Mode in an Oracle 12c database? (Choose two.)

A. The DEGREE_OF_PARALLELISM parameter is set to AUTO.

B. You cannot have multiple SQL *Loader data files.

C. If no data file is specified, it assumes the data file to be <table-name>.dat in the current directory and uses it.</table-name>

D. You can have multiple bad files created when loading in parallel.

E. You can selectively load rows into a table based on a filter.

Answer 87

A. The DEGREE_OF_PARALLELISM parameter is set to AUTO.

C. If no data file is specified, it assumes the data file to be <table-name>.dat in the current directory and uses it.</table-name>

Question 88

You install “Oracle Grid Infrastructure for a standalone server” on a host on which the ORCL1 and
ORCL2 databases both have their instances running.

Which two statements are true? (Choose two.)

A. All databases subsequently created by using the Database Configuration Assistant (DBCA) are
automatically added to the Oracle Restart configuration.

B. The srvctl add database command must be used to add ORCL1 and ORCL2 to the ORACLE Restart configuration.

C. Both ORCL1 and ORCL2 are automatically added to the Oracle Restart configuration.

D. All database listeners running from the database home are automatically added to the Oracle Restart
configuration.

E. The crsctl start has command must be used to start software services for Oracle Automatic Storage
Management (ASM) after the “Oracle Grid

Answer 88

A. All databases subsequently created by using the Database Configuration Assistant (DBCA) are
automatically added to the Oracle Restart configuration.

B. The srvctl add database command must be used to add ORCL1 and ORCL2 to the ORACLE Restart configuration.

Question 89

Which statement is true about unified auditing?

A. The unified audit trail, by default, resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace.

B. Only the CREATE, ALTER, and DROP statements are audited for all users, including SYS.

C. Unified auditing is enabled only if the AUDIT_TRAIL parameter is set to NONE.

D. The unified audit trail contains audit records only from unified audit policies and AUDIT settings.

Answer 89

A. The unified audit trail, by default, resides in a read-only table in the AUDSYS schema in the SYSAUX
tablespace.

Question 90

Which two statements are true about Oracle network connections? (Choose two.)

A. A listener may listen on behalf of only one database instance at a time.

B. A server process checks a user’s authentication credentials and creates a session if the credentials are
valid.

C. The listener continuously monitors a connection after the user process connects to a service handler.

D. The listener always spawns a new server process to deal with each new connection.

E. A connection request from a client is always first received by a listener running on the port that is used
for the connection request for the database server.

Answer 90

B. A server process checks a user’s authentication credentials and creates a session if the credentials are
valid.

E. A connection request from a client is always first received by a listener running on the port that is used
for the connection request for the database server.

Question 91

Which two options can be configured for an existing database by using the Database Configuration Assistant (DBCA)? (Choose two.)

A. Configure Label Security

B. Database Vault in ORACLE_HOME

C. Oracle Suggested Backup Strategy

D. Database Resident Connection Pooling

E. Nondefault blocksize tablespaces

Answer 91

A. Configure Label Security

B. Database Vault in ORACLE_HOME

Question 92

Which set of statements is true about data dictionary views?

1. They are stored in the SYSTEM tablespace.
2. They are based on virtual tables.
3. They are owned by the SYS user.
4. They can be queried by a normal user only if the 07_DICTIONARY_ACCESSIBILITY parameter is set to
   TRUE.
5. The V$FIXED_TABLE view can be queried to list the names of these views.
6. They are owned by the SYSTEM user

A. 2, 5, and 6

B. 1, 2, and 3

C. 1 and 3

D. 2, 3, 4, and 5

Answer 92

C. 1 and 3

Question 93

A database uses Automatic Storage Management (ASM) as database storage, which has a diskgroup, DATA1, which is created as follows:

What happens when the FAILGRP1 failure group is corrupted?

A. Mirroring of allocation units occurs within the FAILGRP2 failure group.

B. Transactions that are using the diskgroup fail.

C. ASM does not mirror any data and newly allocated primary allocation units (AU) are stored in the FAILGRP2 failure group.

D. Data in the FAILGRP1 failure group is moved to the FAILGRP2 failure group and rebalancing is started.

Answer 93

C. ASM does not mirror any data and newly allocated primary allocation units (AU) are stored in the FAILGRP2 failure group.

Question 94

You plan to install the Oracle Database 12c software. You want to use Oracle Automatic Storage Management (ASM) for storage and Oracle Restart for managing components.

Oracle Grid Infrastructure for a Standalone Server is already installed on the server.

Which three statements must be true for successful installation of the Oracle Database 12c software?
(Choose three.)

A. The OSBACKUPDBA, OSDGDBA, and OSKMDBA OS groups must be created.

B. An operating system OSDBA group must be created for users with the SYSDBA system privilege.

C. The Oracle Database 12c software owner and the Oracle Grid Infrastructure owner must belong to the
same Oracle Inventory Group.

D. The Oracle Database 12c installation must have a separate Oracle inventory directory.

E. An operating system OSOPER group be created for users with the SYSOPER system privilege.

Answer 94

B. An operating system OSDBA group must be created for users with the SYSDBA system privilege.

C. The Oracle Database 12c software owner and the Oracle Grid Infrastructure owner must belong to the
same Oracle Inventory Group.

E. An operating system OSOPER group be created for users with the SYSOPER system privilege.

Question 95

You want to upgrade an Oracle Database running Oracle Database 11g to Oracle Database 12c.

Which three tasks should be performed before a manual upgrade? (Choose three.)

A. running preupgrad.sql in Oracle Database 11g to generate fix-up scripts and a log file

B. running utlu121s.sql from the new Oracle home to display information about the required initialization
parameters

C. copying the initialization parameter file to the new Oracle home

D. copying the password file to the new Oracle home

E. copying net configuration files to the new Oracle home

Answer 95

A. running preupgrad.sql in Oracle Database 11g to generate fix-up scripts and a log file

C. copying the initialization parameter file to the new Oracle home

D. copying the password file to the new Oracle home

Question 96

You have a production Oracle 12c database running on a host.

You want to install and create databases across multiple new machines that do not have any Oracle
database software installed. You also want the new databases to have the same directory structure and
components as your existing 12c database.

The steps in random order:

1. Create directory structures similar to the production database on all new machines.
2. Create a response file for Oracle Universal Installer (OUI) with the same configurations as the production
   database.
3. Create a database clone template for the database.
4. Run the Database Configuration Assistant (DBCA) to create the database.
5. Run OUI in graphical mode on each machine.
6. Run OUI in silent mode using the OUI response file.

Identify the required steps in the correct sequence to achieve the requirement with minimal human
intervention.

A. 1, 5, and 4

B. 3, 1, 5, and 6

C. 2, 3, 6, and 4

D. 2, 1, 6, and 4

E. 2, 3, 1, and 6

Answer 96

C. 2, 3, 6, and 4

Question 97

The HR.DEPARTMENTS table is the parent of the HR.EMPLOYEES table.

The EMPLOYEES.DEPARTMENT_ID column has a foreign key constraint with the ON option that refers to the
DEPARTMENTS.DEPARTMENT_ID column.

An index exists on the DEPARTMENTS.DEPARTMENT_ID
column.

A DELETE CASCADE
transaction deletes a primary key in the DEPARTMENTS table, which has child rows in the EMPLOYEES
table.

Which statement is true?

A. The transaction acquires a table lock only on the DEPARTMENTS table until the transaction is
complete.

B. The transaction acquires a table lock on the DEPARTMENTS table. This lock enables other sessions to
query but not update the DEPARTMENTS table until the transaction on the DEPARTMENTS table is
complete.

C. The transaction acquires a table lock on the EMPLOYEES table. This lock enables other sessions to
query but not update the EMPLOYEES table until the transaction on the DEPARTMENTS table is
complete.

D. Only the rows that are deleted in the DEPARTMENTS and EMPLOYEES tables are locked until the
transactions on the DEPARTMENTS table is complete.

Answer 97

D. Only the rows that are deleted in the DEPARTMENTS and EMPLOYEES tables are locked until the
transactions on the DEPARTMENTS table is complete.

Question 98

SMD is a smallfile locally managed tablespace with manual segment space management.

The SH user receives the following error while inserting data into the sales table:

Which three actions can be taken to enable the user to insert data? (Choose three.)

A. altering the data file associated with the SMD tablespace to grow automatically

B. adding a data file to the SMD tablespace, provided the tablespace has not reached its maximum number of file

C. changing segment space management for the SMD tablespace to automatic

D. resizing the data file associated with the SMD tablespace to make it larger, provided all data files have
not yet reached their maximum size

E. increasing the space quota on the SMD tablespace for the SH user, provided the files have not yet
reached their maximum size

Answer 98

A. altering the data file associated with the SMD tablespace to grow automatically

B. adding a data file to the SMD tablespace, provided the tablespace has not reached its maximum number of file

D. resizing the data file associated with the SMD tablespace to make it larger, provided all data files have
not yet reached their maximum size

Question 99

When is the UNDO_RETENTION parameter value ignored by a transaction?

A. when there are multiple undo tablespaces available in a database

B. when the data file of the undo tablespace is autoextensible

C. when the undo tablespace is of a fixed size and retention guarantee is not enabled

D. when Flashback Database is enabled

Answer 99

C. when the undo tablespace is of a fixed size and retention guarantee is not enabled

Question 100

Which four statements are true about the components of the Oracle Scheduler? (Choose four.)

A. A schedule can be specified to a single job only.

B. A scheduler job can point to a chain instead of pointing to a single program object.

C. A job may get started automatically when a window opens.

D. A program and job can be specified as part of a schedule definition.

E. A job is specified as part of a program specification.

F. A program can be used in the definition of multiple jobs.

G. A program and schedule can be specified as part of a job definition.

Answer 100

B. A scheduler job can point to a chain instead of pointing to a single program object.

C. A job may get started automatically when a window opens.

F. A program can be used in the definition of multiple jobs.

G. A program and schedule can be specified as part of a job definition.

Question 101

Which three statements are true about checkpointing? (Choose three.)

A. It prompts the Checkpoint (CKPT) process to write data to the data files and redo information to the
online redo log files.

B. It ensures that all dirty buffers are written to data files during consistent shutdown.

C. It reduces the time required for recovery in case of an instance failure.

D. Frequent thread checkpoints can degrade database performance.

E. It prompts the Database Writer (DBWn) process to write checkpoint information into data file headers
and the control file.

Answer 101

B. It ensures that all dirty buffers are written to data files during consistent shutdown.

C. It reduces the time required for recovery in case of an instance failure.

D. Frequent thread checkpoints can degrade database performance.

Question 102

Your database instance has Automatic Memory Management enabled and supports shared server
connections.

Examine the following:

1. Parallel execution messages and control structures
2. Local variables for a process
3. Security and resource usage information
4. Runtime memory values, such as rows retrieved for a SQL statement using a serial execution plan
5. SQL execution work areas

Which option indicates what is allocated from the large pool in this instance?

A. only 1

B. 1, 2, and 5

C. 1, 2, 3, and 5

D. 1, 2, and 4

Answer 102

D. 1, 2, and 4

Question 103

The performance of your database degrades between 11:00 AM and 3:00 PM. Automatic Workload Repository (AWR) snapshots are collected on an hourly basis.

What is the most efficient way of diagnosing this problem?

A. Create a custom ADDM task for the period defined by the snapshots taken between 11:00 AM and 3:00 PM.

B. Analyze the latest Automatic Database Diagnostic Monitor (ADDM) report.

C. Analyze the hourly ADDM reports generated between 11:00 AM and 3:00 PM.

D. Create a SQL Tuning Set (STS) for the currently cached SQL statements in the shared pool and run SQL Performance Analyzer (SPA) to generate

Answer 103

A. Create a custom ADDM task for the period defined by the snapshots taken between 11:00 AM and 3:00 PM.

Question 104

Automatic Shared Memory Management is enabled for your database instance.

You notice that there are SQL statements performing poorly because of repeated parsing activity.

Which action generates recommendations to overcome the performance issues?

A. running the Memory Advisor for the buffer cache

B. running the Memory Advisor for the library cache

C. running the Memory Advisor for the SGA

D. running the Memory Advisor for the PGA

Answer 104

B. running the Memory Advisor for the library cache

Question 105

Which two statements are true about a server parameter file (SPFILE)? (Choose two.)

A. An SPFILE can be created from a PFILE or from memory.

B. A PFILE can be used to start up a database instance even if an SPFILE exists.

C. An SPFILE must reside in the ORACLE_HOME/dbs directory.

D. An SPFILE can be created only before a database instance is started.

E. An SPFILE contains only those dynamic parameters that can be changed without having to restart the
database instance.

Answer 105

A. An SPFILE can be created from a PFILE or from memory.

B. A PFILE can be used to start up a database instance even if an SPFILE exists.

Question 106

Which three statements are true about Automatic Workload Repository (AWR)? (Choose three.)

A. An AWR snapshot shows the SQL statements that are producing the highest load on the system, based
on criteria such as elapsed time and CPU time.

B. AWR data is stored in memory and in a database.

C. All AWR tables belong to the SYSTEM schema.

D. The manageability monitor (MMON) process gathers statistics and creates an AWR snapshot that is
used by the self-tuning components in a database.

E. An AWR snapshot contains system-wide tracing and logging information.

Answer 106

A. An AWR snapshot shows the SQL statements that are producing the highest load on the system, based
on criteria such as elapsed time and CPU time.

B. AWR data is stored in memory and in a database.

D. The manageability monitor (MMON) process gathers statistics and creates an AWR snapshot that is
used by the self-tuning components in a database.

Question 107

You are using RMAN to back up your database.
All the data files are in read/write mode.

Examine the RMAN configuration parameters:

Which two statements are true about a whole consistent backup of a database running in ARCHIVELOG
mode? (Choose two.)

A. The backup can be used as an incremental level 0 backup.

B. The database instance must be shut down to take the backup.

C. The database must be in MOUNT state to take the backup.

D. The backup consists of blocks that have been formatted.

E. The system Change Number (SCN) is the same for all the data files in the backup.

Answer 107

C. The database must be in MOUNT state to take the backup.

E. The system Change Number (SCN) is the same for all the data files in the backup.

Question 108

Which two would you recommend to an application developer for reducing locking conflicts on tables in an
Oracle database? (Choose two.)

A. Create objects in locally managed tablespaces with Automatic Segment Space Management (ASSM) enabled.

B. Avoid coding long-running transactions.

C. Allow the database to handle locks ln default locking mode.

D. Create objects in locally managed tablespaces with manual segment space management enabled.

E. Enable undo retention guarantee.

Answer 108

B. Avoid coding long-running transactions.

C. Allow the database to handle locks ln default locking mode.

Question 109

Which statement is true about the loss of a data file belonging to the default undo tablespace?

A. The database remains open in read-only mode.

B. The database is put in MOUNT state and requires recovery to be opened.

C. The database remains open for querying but no DML statements can be executed except by the users
with SYSDBA privilege.

D. All the noncommitted transactions are lost.

E. The database instance aborts.

Answer 109

B. The database is put in MOUNT state and requires recovery to be opened.

Question 110

Your database is in NOARCHIVELOG mode.

You want to enable archiving for the database.

Examine the steps:

1. Execute the ALTER DATABASE ARCHIVELOG command.
2. Execute SHUTDOWN IMMEDIATE.
3. Execute STARTUP MOUNT.
4. Set the DB_RECOVERY_FILE_DEST parameter to $ORACLE_HOME/dbs/.
5. Execute STARTUP NOMOUNT.
6. Open the database.

Identify the required steps in the correct sequence.

A. 4, 2, 5, 1, 6

B. 1, 2, 3, 4, 6

C. 2, 3, 1, 6

D. 2, 5, 1, 6

Answer 110

C. 2, 3, 1, 6

Question 111

Which three statements are true about naming methods? (Choose three.)

A. Local naming supports multiple protocols, but for any one connection, the client and server must use the
same protocol.

B. In the Easy Connect method, clients can connect to a database server by using the host name of the
database with an optional port and service name.

C. In the Easy Connect method, the listener port and IP address must be provided for the connection to be
successful.

D. The local naming method does not support connect-time failover and load-balancing options.

E. The directory naming method supports connect-time failover and load-balancing options.

Answer 111

A. Local naming supports multiple protocols, but for any one connection, the client and server must use the
same protocol.

B. In the Easy Connect method, clients can connect to a database server by using the host name of the
database with an optional port and service name.

E. The directory naming method supports connect-time failover and load-balancing options.

Question 112

Which task is performed by a background process in a database instance?

A. Connecting between a client process and a dispatcher

B. Executing PL/SQL code

C. Creating dedicated server connections

D. Copying online redo log files to offline storage

Answer 112

D. Copying online redo log files to offline storage

Question 113

Examine the parameters:

Your database instance is started with a PFILE.

You want to increase the size of the buffer cache.
Free memory is available to increase the size of the buffer cache.

You execute the command:
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1024M;

Which is the outcome?

A. Change is applied to the current instance, but does not persist after instance restart.

B. The value is changed only in the PFILE and takes effect at the next instance startup.

C. The value is chang

D. It fails because the SCOPE clause is missing.

Answer 113

A. Change is applied to the current instance, but does not persist after instance restart.

Question 114

Identify three situations in which messages are written to the alert log file. (Choose three.)

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

B. Creating a table returns “ORA-00955: name is already in used by an existing object”

C. Inserting a value into a table returns “ORA-01722: invalid number”

D. Updating a record in a table returns “ORA-00060: deadlock detected while waiting for resource”

E. Inserting a value into a table returns “ORA-00001: unique constraint (SYS.PK_XXXX) violated”

F. Running a query on a table returns “ORA-01578: ORACLE data block corrupted (file # 4, block # 131)”

Answer 114

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

D. Updating a record in a table returns “ORA-00060: deadlock detected while waiting for resource”

F. Running a query on a table returns “ORA-01578: ORACLE data block corrupted (file # 4, block # 131)”

Question 115

When does a database checkpoint occur?

A. When there is an online redo log switch.

B. When a user session terminates abnormally.

C. When a server process terminates abnormally.

D. When the SHUTDOWN ABORT command is issued.

Answer 115

A. When there is an online redo log switch.

Question 116

What action must you take to ensure complete database recovery till the point of failure?

A. Multiplex the control files

B. Duplex the RMAN backup sets.

C. Multiplex the online redo log files.

D. Configure the database to run in ARCHIVELOG mode.

Answer 116

D. Configure the database to run in ARCHIVELOG mode.

Question 117

You want to create a table, DAILY_ORDERS, for an OLTP application, where data should be compressed
during both direct-path INSERT and conventional DML.

The table will also be used for queries.

Which compression option should be used?

A. ROW STORE COMPRESS

B. COLUMN STORE COMPRESS FOR QUERY

C. COLUMN STORE COMPRESS FOR ARCHIVE LOW

D. ROW STORE COMPRESS ADVANCED

Answer 117

D. ROW STORE COMPRESS ADVANCED

Question 118

Examine the command:

SQL> CREATE TABLESPACE test1
DATAFILE ‘/u01/app/oracle/oradata/orc1/test01.dbf’ SIZE 5M
AUTOEXTEND ON UNIFORM;

Which statement is true?

A. The data file, TEST01.DBF, can be auto extended to a maximum size of 5M.

B. The tablespace, TEST1, can contain a maximum of one data file.

C. Allocated and free extents are tracked using bitmaps.

D. Segment free space is tracked in the data dictionary.

Answer 118

C. Allocated and free extents are tracked using bitmaps.

Question 119

Which three statements are true about user account administration? (Choose three.)

A. A user’s current session is not affected when the user’s profile is changed.

B. Only users with the SYSDBA privilege can change the tablespace quota for other users.

C. A new user account can be created only by SYS or SYSTEM users.

D. A user’s quota can be set for any permanent tablespace but not for the default temporary tablespace.

E. A user requires only the CREATE SESSION privilege to change his or her own password.

Answer 119

A. A user’s current session is not affected when the user’s profile is changed.

D. A user’s quota can be set for any permanent tablespace but not for the default temporary tablespace.

E. A user requires only the CREATE SESSION privilege to change his or her own password.

Question 120

Which three database operations can be performed only at MOUNT state? (Choose three.)

A. performing Flashback Database

B. renaming control files

C. enabling or disabling ARCHIVELOG mode

D. re-creating control files

E. performing full database recovery

Answer 120

A. performing Flashback Database

C. enabling or disabling ARCHIVELOG mode

E. performing full database recovery

Question 121

Which two services may you see on the My Service Dashboard page? (Choose two.)

A. Network Cloud Service

B. User Cloud Service

C. Compute Cloud Service

D. Database Cloud Service

Answer 121

C. Compute Cloud Service

D. Database Cloud Service

Question 122

What is a requirement for creating a remote database scheduler job?

A. The remote database job must run as a user that is valid on the target remote database.

B. A private database link must be created from the originating database to the target remote database.

C. The target remote database on which the job is scheduled must be Oracle Database 11g Release 2 or
later.

D. The target remote database must be on a different host form the originating scheduler database host.

Answer 122

A. The remote database job must run as a user that is valid on the target remote database.

Question 123

The HR schema exists in two databases, BOSTON and DENVER, and has the same password, HR.

You have the CREATE DATABASE LINK and CREATE SESSION privileges on both the database.

BOSTON is defined as a service name in the tnsnames.ora of both the databases.

You plan to use the command:
CREATE DATABASE LINK hr_link CONNECT to hr IDENTIFIED BY hr USING ‘denver’;

What must be done to ensure only the HR user in the BOSTON database can access the HR schema in
the DENVER database?

A. Execute this command as HR user in the BOSTON database and SYS user in the DENVER database.

B. Execute this command as SYS user in both the databases.

C. Execute this command as HR user in the DENVER database.

D. Execute this command as HR user in the BOSTON database.

Answer 123

D. Execute this command as HR user in the BOSTON database.

Question 124

Which users are created and can be used for database and host management of your DBaaS database servers?

A. opc and oracle users

B. root, oracle and cloud users

C. root and oracle users

D. root, opc and oracle users

E. cloud and oracle users

Answer 124

A. opc and oracle users

Question 125

Examine the command to perform a data pump export operation on a source database:

$> expdp hr/hr DIRECTORY=dumpdir DUMPFILE=emp1.dmp VIEWS_AS_TABLE=emp_dept

On the target database, you execute the data pump import command:

$> impdp hr/hr DIRECTORY=dumpdir DUMPFILE=emp1.dmp VIEWS_AS_TABLE=emp_dept
Which three statements are true? (Choose three.)

A. The expdp operation exports data that satisfies the condition of the defining query used to create the
EMP_DEPT view.

B. The impdp operation creates the view and dependent objects.

C. All rows from the dependent objects, along with the metadata required to create the EMP_DEPT view,
are exported.

D. Objects dependent on the EMP_DEPT view are exported.

E. The impdp operation creates EMP_DEPT as table and populates it with the data from the export dump
file.

Answer 125

A. The expdp operation exports data that satisfies the condition of the defining query used to create the
EMP_DEPT view.

D. Objects dependent on the EMP_DEPT view are exported.

E. The impdp operation creates EMP_DEPT as table and populates it with the data from the export dump
file.

Question 126

Examine the details of the uncompressed, non-partitioned heap table CITIES.

Examine the command:
SQL> ALTER TABLE cities SHRINK SPACE COMPACT;

What must you do before executing it?

A. Ensure free space that is approximately equal to the space used by the table should be available.

B. Ensure there are no pending transactions on the table

.
C. Enable row movement is enabled.

D. Disable all indexes on the table

Answer 126

C. Enable row movement is enabled.

Question 127

Your database has archive logging enabled and RMAN backups are taken at regular intervals.

A data file in the USERS tablespace is damaged.

Which command must you execute before starting the recovery of this tablespace?

A. ALTER TABLESPACE users OFFLINE

B. STARTUP FORCE NOMOUNT

C. STARTUP FORCE

D. SWITCH DATAFILE ALL

Answer 127

A. ALTER TABLESPACE users OFFLINE

Question 128

Which statement is true about the Database as a Service (DBaaS) instances and Database instances in Oracle Public Cloud

A. An Oracle database instance can support only one DBaaS instance.

B. A DBaaS instance can support only one Oracle database instance.

C. An Oracle database instance can support multiple DBaaS instances.

D. A DBaaS instance can support multiple Oracle database instances.

E. A DBaaS instance runs in a pluggable database (PDB), which is contained in a multi-tenant container
database (CDB).

Answer 128

D. A DBaaS instance can support multiple Oracle database instances.

Question 129

Examine the following ALTER command:
SQL> ALTER DISKGROUP dgroup1 UNDROP DISKS;

What is the purpose of the command?

A. It cancels all pending disk drops within the disk group

B. It restores disks that are being dropped as the result of a DROP DISKGROUP operation.

C. It mounts disks in the disk group for which the drop-disk operation has already been completed

D. It restores all the dropped disks in the disk group for which the drop-disk operation has already been
completed

E. It adds previously dropped disks back into the disk group

Answer 129

A. It cancels all pending disk drops within the disk group

Question 130

Tape streaming is not happening while performing RMAN tape backup.

On investigation, you find that it is not because of the incremental backup or the empty file backup and that
RMAN is sending data blocks to tape drive fast enough.

What could be a solution to make streaming happen during the backup?

A. Configure backup optimization

B. Configure the channel to increase MAXOPENFILES

C. Configure the channel to increase the capacity with the RATE parameter

D. Configure the channel to adjust the tape buffer size with the BLKSIZE option

Answer 130

D. Configure the channel to adjust the tape buffer size with the BLKSIZE option

Question 131

Identify the persistent configuration setting for the target database that can be set for the backup by using RMAN.

A. Backup retention policy

B. Default backup device type

C. Default destinations for backups

D. Multiple backup device types for single backup

E. Default section size for backups

(Choose all that apply.)

Answer 131

A. Backup retention policy

B. Default backup device type

C. Default destinations for backups

Question 132

Which three statements are true about windows? (Choose three.)

A. Only one window can be open at any given time

B. Consumer groups are associated with windows

C. Windows work with job classes to control resource allocation

D. The database service name must be provided during windows creation

E. Windows can automatically start job or change resource allocation among jobs for various time periods

Answer 132

A. Only one window can be open at any given time

C. Windows work with job classes to control resource allocation

E. Windows can automatically start job or change resource allocation among jobs for various time periods

Question 133

Which two tasks must you perform before you begin the upgrade process to Oracle Database 12c? (Choose two.)

A. Put all readonly tablespaces in read write mode

B. Recompile all invalid objects

C. Set the compatible parameter to 12.1.0.1

D. Gather dictionary statistics

E. Empty all user recycle bins

Answer 133

B. Recompile all invalid objects

E. Empty all user recycle bins

Question 134

Which two tools can be used to configure static service information in the listener.ora file? (Choose two.)

A. Oracle Net Manager

B. Oracle Enterprise Manager Cloud Control

C. Oracle Net Configuration Assistant

D. Listener Control Utility (LSNRCTL)

E. Oracle Enterprise Manager Database Express

Answer 134

A. Oracle Net Manager

B. Oracle Enterprise Manager Cloud Control

Question 135

Which three file types are stored in the Fast Recovery Area by default in a traditional nonOMF file system? (Choose three.)

A. online redo log files

B. parameter file

C. multiplexed copies of the current control file

D. archived log files

E. Flashback Data Archive files

F. Flashback logs

Answer 135

C. multiplexed copies of the current control file

D. archived log files

F. Flashback logs

Question 136

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

A. Frequent thread checkpoints can degrade database performance

B. Database Writer (DBWn) processes write checkpoint information to datafile headers and the control file

C. It reduces the recovery time from instance failures

D. Incremental checkpoints write some dirty buffers to the datafiles and unwritten redo to the online redo
logs.

E. Thread checkpoints ensure that all dirty buffers are written to data files during a normal shutdown

Answer 136

A. Frequent thread checkpoints can degrade database performance

C. It reduces the recovery time from instance failures

E. Thread checkpoints ensure that all dirty buffers are written to data files during a normal shutdown

Question 137

You want a job that performs a bulk insert as soon as the loader file arrives on the local file system.
Which two would you do to accomplish this? (Choose two.)

A. Create a file watcher

B. Create a bulk loader watcher

C. Create a light weight job for bulk insert

D. Create an event-based job for bulk insert

E. Create a job chain with a step for bulk insert

Answer 137

A. Create a file watcher

D. Create an event-based job for bulk insert

Question 138

Which four are true about creating and running a remote database scheduler jobs? (Choose four.)

A. A database destination must exist or be created for the remote database

B. It must run as a user that is defined on the remote database

C. Remote database jobs always run as the same user who submits the job on the local database

D. A credential is optional for a remote database job

E. A credential must be created to define the remote user

F. A database destination group must exist or be created for a job to run on multiple remote databases

G. A destination is optional for a remote database job because DB links can be used instead

Answer 138

A. A database destination must exist or be created for the remote database

B. It must run as a user that is defined on the remote database

D. A credential is optional for a remote database job

F. A database destination group must exist or be created for a job to run on multiple remote databases

Question 139

Which two must be installed or configured either manually or by DBCA in order to use Enterprise Manager Database Express (EM Express)? (Choose two.)

A. A port number for Oracle HTTP Server must be configured

B. The APEX_PUBLIC_USER role must be granted to SYSMAN

C. A SYSMAN user with SYSDBA privilege must be created

D. At least one TCP/IP dispatcher must be configured

E. The Oracle HTTP Server must be installed

Answer 139

B. The APEX_PUBLIC_USER role must be granted to SYSMAN

D. At least one TCP/IP dispatcher must be configured

Question 140

Which three statements are true PFILEs, SPFILEs or both?

A. SPFILEs and PFILEs may both be edited with an O/S editing utility

B. Some SPFILE parameters can be modified successfully with the SCOPE=MEMORY clause

C. A SPFILE can be created by an idle instance

D. A PFILE can be created by an idle instance

E. All SPFILE parameters can be modified successfully with the SCOPE=BOTH clause

F. All SPFILE parameters can be modified successfully with the SCOPE=MEMORY clause

(Choose three.)

Answer 140

B. Some SPFILE parameters can be modified successfully with the SCOPE=MEMORY clause

C. A SPFILE can be created by an idle instance

D. A PFILE can be created by an idle instance

Question 141

A database is open READ WRITE and the instance has multiple sessions some of which have active transactions.
You execute this command:

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

Which three are true about the active transactions? (Choose three.)

A. They may issue COMMIT or ROLLBACK statements

B. They are suspended and unable to issue any statements

C. They may continue to issue DML statements

D. They are rolled back automatically

E. They may continue to issue queries

F. They are terminated immediately

Answer 141

A. They may issue COMMIT or ROLLBACK statements

C. They may continue to issue DML statements

E. They may continue to issue queries

Question 142

Which three statements are true about a job chain?

A. The DBA role is required to create a job chain.

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

F. It can be executed using time-based schedules.

(Choose three.)

Answer 142

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

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

F. It can be executed using time-based schedules.

Question 143

An employee salary in the non-partitioned EMPLOYEES table, has been updated but the transaction is uncommitted.

Which two types of lock are held by the transaction?
(Choose two.)

A. a row level lock on the row being updated

B. a null lock on the row being updated

C. a row shared lock on the EMPLOYEES table

D. an exclusive lock on the EMPLOYEES table

E. a null lock on the EMPLOYEES table

Answer 143

A. a row level lock on the row being updated

C. a row shared lock on the EMPLOYEES table

Question 144

A PFILE exists for your database.
You want to increase the value of the PROCESSES parameter from 300 to 400 and make it persistent for
your database instance.

You plan to use the ALTER SYSTEM command.
Which two methods can be used? (Choose two.)

A. Edit PFILE manually, re-create the SPFILE from the edited PFILE, and restart the database instance by
using the SPFILE.

B. Create an SPFILE, start the database instance by using the SPFILE, and then execute the ALTER
SYSTEM SET PROCESSES=400 SCOPE=SPFILE command.

C. Create an SPFILE, start the database instance by using the SPFILE, and then execute the ALTER
SYSTEM SET PROCESSES=400 SCOPE=DEFERRED command.

D. Create an SPFILE, STARTUP MOUNT the database instance by using the SPFILE, and then execute
the ALTER SYSTEM SET PROCESSES=400 command.

E. Start the database instance by using the PFILE, and then execute the ALTER SYSTEM SET
PROCESSES=400 SCOPE=BOTH command.

F. Use the PFILE to STARTUP MOUNT the database instance, and then execute the ALTER SYSTEM
SET PROCESSES=400 command.

Answer 144

B. Create an SPFILE, start the database instance by using the SPFILE, and then execute the ALTER
SYSTEM SET PROCESSES=400 SCOPE=SPFILE command

Question 145

What should you do for a Scheduler to start a job when a file arrives on a remote system?

A. Start an Oracle database instance on the remote system and register the Scheduler agent with this database.

B. Install the Scheduler agent on the remote system and register the agent with the database on the local
system.

C. Ensure dedicated server is enabled on both the local and remote databases.

D. Shutdown the file watcher on the local system.

Answer 145

B. Install the Scheduler agent on the remote system and register the agent with the database on the local
system.

Question 146

The HR schema exists in databases, BOSTON and DENVER, with the same password, HR.

You have CREATE DATABASE LINK and CREATE SESSION privileges in both databases.
DENVER is defined as a service name in the tnsnames.ora of both databases.

You plan to use this command:
CREATE DATABASE LNK hr_link CONNECT TO hr IDENTIFIED BY hr USING “˜DENVER’;

What must be done to ensure that all users in BOSTON can access the HR schema in DENVER?

A. Change the command to create a public database link in BOSTON.

B. Change the command to create a public database link in DENVER.

C. Execute the command as SYS in DENVER.

D. Execute the command as SYS in BOSTON.

E. Execute the command as HR in BOSTON and SYS in DENVER.

F. Execute the command as SYS in both databases.

Answer 146

E. Execute the command as HR in BOSTON and SYS in DENVER.