Oracle Test Bobby Flashcards
1
Q
A user establishes a connection to a database instance by using an Oracle Net connection. You want to ensure the following:
- The user account must be locked after five unsuccessful login attempts.
- Data read per session must be limited for the user.
- The user cannot have more than three simultaneous sessions.
- 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
A
D. by assigning a profile to the user
2
Q
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.
A
D. The job is terminated and statistics for the remaining objects are collected the next time the maintenance window opens.
3
Q
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.
A
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.
4
Q
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.
A
D. It writes when a user commits a transaction.
5
Q
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
A
D. connecting by using the local naming method with the server = dedicated parameter set in the
tnsnames.ora file for the net service
6
Q
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
A
A. Altering the data file associated with the USERS tablespace to extend automatically
B. Adding a data file to the USERS tablespace
7
Q
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
A
C. running the Pre-Upgrade Information Tool
8
Q
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.
A
C. The sessions are not affected and continue to function normally.
9
Q
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
A
A. The threshold alerts were cleared and transferred to DBA_ALERT_HISTORY.
10
Q
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
A
B. A role can be granted to other roles.
C. A role can contain both system and object privileges.
11
Q
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.
A
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.
12
Q
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:
- Set the evolve task parameters.
- Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.
- Implement the recommendations in the task by using the DBMS_SPM.IMPLEMENT_EVOLVE_TASK
function. - Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.
- 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
A
B. 2, 1, 4, 3, 5
13
Q
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
A
D. Using Automatic Segment Space Management (ASSM)
14
Q
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.
A
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.
15
Q
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
A
A. Effectively implements role separation
C. Automatically registers the database with Oracle Restart.
16
Q
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.
A
B. Multiple pluggable databases (PDBs) share certain multitenant container database (CDB) resources.
E. Patches are always applied at the CDB level.
17
Q
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
A
A. Missing or stale histogram statistics
C. High clustering factor for the indexes
D. High value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter
18
Q
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
A
A. Bind Variable Peeking
C. Adaptive Cursor Sharing
D. Bind variable used in a SQL statement
19
Q
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.
A
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.
20
Q
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.
A
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.
21
Q
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).
A
A. Use Oracle Managed Files (OMF).
B. Specify the SEED FILE_NAME_CONVERT clause.
C. Specify the PDB_FILE_NAME_CONVERT initialization parameter.
22
Q
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
A
B. Transportable tablespace
23
Q
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.
A
C. Only CREATE TABLE and ALTER TABLE statements generated by the import are logged.
24
Q
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.
A
A. The optimizer used a dynamic plan for the query.
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
A. SYSTEM
B. SYSAUX
E. TEMP
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.
A. The metadata used to track extents in SGA is reduced.
C. An ASM Instance automatically allocates an appropriate extent size.
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.
C. Create the CREATE\_TEST procedure with invoker’s rights.
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.
B. Set DB\_FLASH\_CACHE\_ZISE = 64G, 64G
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.
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.
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
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
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.
B. Checks are performed for physical corruptions.
D. Checks are performed to confirm whether all database files exist in correct locations
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.
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.
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.)
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.
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.
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.
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.
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.
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
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
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.
A. Increase the value of the ASM\_POWER\_LIMIT parameter.
D. Increase the number of ASMB processes.
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
C. ALTER TABLE TRUNCATE PARTITION
E. ALTER TABLE DROP PARTITION
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
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
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.
A. Place the root container in ARHCHIVELOG mode.
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.
D. The operating system group that is specified must have permission to write to the inventory directory.
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.
A. The CDB must be open.
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.
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.
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.
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.
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).
C. It fails because local users are unable to use the SET CONTAINER statement.
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.
A. The size of the shared pool is too small.
B. Cursors are not being shared.
D. There are frequent logons and logoffs.
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
A. SYS, SYSTEM
E. Both successful and failed executions
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.
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.
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
C. A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.
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.
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.
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.
A. The index remains invisible.
E. The index is updated by the DML on the table.
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;
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;
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
B. 2 and 4
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.
E. It executes successfully but neither tablespace nor the data file is created
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.
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.
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.
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.
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.
E. All DDL commands are logged in XML format in the alert directory under the Automatic Diagnostic Repository (ADR) home.
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.
C. EXECUTE privilege on the DBMS\_FLASHBACK package must be granted to the user flashing back transaction.
D. Supplemental logging must be enabled.
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.
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.
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
A. BACKUP PLUGGABLE DATABASE PDB1 while connected to the root container
C. BACKUP DATABASE while connected to the PDB1 container
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.
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.
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.
D. Take the USERDATA tablespace offline in ACCOUNTS\_PDB.
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
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)
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.
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.
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.
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.
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.
C. It kills sessions that exceed idle time.
E. It frees resources held by abnormally terminated processes.
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.
D. Implement a Database Resource Manager plan.
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
A. 1, 3, 2, 6, 7, 4, 5
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
B. SQL profile recommendations
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.
D. Run utlrp.sql to recompile stored PL/SQL and Java code and check the DBA\_REGISTRY view.
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.
B. Create a lightweight job.
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
B. assigning oinstall as the primary group for the oracle user
D. creating OS groups associated with the OSBACKUPDBA, OSDGDBA, and OSKMDBA system
privileges
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
C. SHRINK SPACE COMPACT
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.
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.
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.
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.
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.
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.
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
D. by using RMAN
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.
C. It creates a locally managed tablespace with automatic segment space management enabled.
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.
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.
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.
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.
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
B. disabling Oracle Flashback Database if enabled
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.
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.
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.
C. The database instance remains open and the control file can be restored without shutting down the
database.
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.
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.
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
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
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
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
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 .dat in the current directory and uses it.
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.
A. The DEGREE\_OF\_PARALLELISM parameter is set to AUTO.
C. If no data file is specified, it assumes the data file to be .dat in the current directory and uses it.
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
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.
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.
A. The unified audit trail, by default, resides in a read-only table in the AUDSYS schema in the SYSAUX
tablespace.
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.
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.
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
A. Configure Label Security
B. Database Vault in ORACLE\_HOME
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
C. 1 and 3
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.
C. ASM does not mirror any data and newly allocated primary allocation units (AU) are stored in the FAILGRP2 failure group.
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.
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.
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
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
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
C. 2, 3, 6, and 4
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.
D. Only the rows that are deleted in the DEPARTMENTS and EMPLOYEES tables are locked until the
transactions on the DEPARTMENTS table is complete.
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
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
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
C. when the undo tablespace is of a fixed size and retention guarantee is not enabled
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.
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.
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.
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.
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
D. 1, 2, and 4
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
A. Create a custom ADDM task for the period defined by the snapshots taken between 11:00 AM and 3:00 PM.
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
B. running the Memory Advisor for the library cache
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.
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.
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.
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.
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.
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.
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.
B. Avoid coding long-running transactions.
C. Allow the database to handle locks ln default locking mode.
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.
B. The database is put in MOUNT state and requires recovery to be opened.
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
C. 2, 3, 1, 6
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.
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.
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
D. Copying online redo log files to offline storage
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.
A. Change is applied to the current instance, but does not persist after instance restart.
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)"
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)"
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.
A. When there is an online redo log switch.
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.
D. Configure the database to run in ARCHIVELOG mode.
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
D. ROW STORE COMPRESS ADVANCED
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.
C. Allocated and free extents are tracked using bitmaps.
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.
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.
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
A. performing Flashback Database
C. enabling or disabling ARCHIVELOG mode
E. performing full database recovery
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
C. Compute Cloud Service
D. Database Cloud Service
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.
A. The remote database job must run as a user that is valid on the target remote database.
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.
D. Execute this command as HR user in the BOSTON database.
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
A. opc and oracle users
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.
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.
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
C. Enable row movement is enabled.
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
A. ALTER TABLESPACE users OFFLINE
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).
D. A DBaaS instance can support multiple Oracle database instances.
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
A. It cancels all pending disk drops within the disk group
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
D. Configure the channel to adjust the tape buffer size with the BLKSIZE option
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.)
A. Backup retention policy
B. Default backup device type
C. Default destinations for backups
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
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
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
B. Recompile all invalid objects
E. Empty all user recycle bins
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
A. Oracle Net Manager
B. Oracle Enterprise Manager Cloud Control
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
C. multiplexed copies of the current control file
D. archived log files
F. Flashback logs
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
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
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
A. Create a file watcher
D. Create an event-based job for bulk insert
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
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
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
B. The APEX\_PUBLIC\_USER role must be granted to SYSMAN
D. At least one TCP/IP dispatcher must be configured
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.)
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
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
A. They may issue COMMIT or ROLLBACK statements
C. They may continue to issue DML statements
E. They may continue to issue queries
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.)
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.
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
A. a row level lock on the row being updated
C. a row shared lock on the EMPLOYEES table
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.
B. Create an SPFILE, start the database instance by using the SPFILE, and then execute the ALTER
SYSTEM SET PROCESSES=400 SCOPE=SPFILE command
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.
B. Install the Scheduler agent on the remote system and register the agent with the database on the local
system.
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.
E. Execute the command as HR in BOSTON and SYS in DENVER.
