1. Which two statements are true about the Automatic Diagnostic Repository (ADR)?

It supports diagnostics for Oracle Clusterware.

It supports diagnostics for Automatic Storage Management (ASM)

2. Which three resource limits can be configured using Oracle profiles?

The idle time for user sessions

The connect time for user sessions

The CPU used by a session

3. TBS1 is a permanent user-defined tablespace.

Oracle Managed Files (OMF) is not used in this database.

Examine this command:

DROP TABLESPACE tbs1;

Which two will ensure that TBS1 is dropped and its datafiles are deleted from the file system?

The INCLUDING CONTENTS AND DATAFILES clause must be added to the command.

There must be no uncommitted transactions involving segments stored in TBS1.

4. What are AWR Snapshots?

AWR Snapshots are used to capture database statistical information from the SGA periodically,
which are stored in the AWR.

5. Which three files are used by conventional path SQL*Loader when the TABLE Option is not
specified?

Input files

Control files

Bad files

6. What is the main purpose of a database instance?

To allocate a shared memory area and start background processes

7. The SALES_Q1 and USERS tablespaces exist in one of your databases, and TEMP is a temporary
tablespace.

Segment creation is not deferred.

You execute this command:

CREATE USER sales

IDENTIFIED BY sales 123

DEFAULT TABLESPACE sales_qi

TEMPORARY TABLESPACE temp

QUOTA SM ON users;

Which three statements must be true so that the sales user can create tables in SALES_Q1?

-The sales user must have been granted the CREATE SESSION privilege.

-The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extents of all
tables they plan to create in their schema.

-The sales user must have been granted the CREATE TABLE privilege.

8. At which three levels can FGA policies be created?

At a table or view level

At a row level of a table or view

At a column level of a table or view

9. Which two shutdown modes result in database inconsistency?

Shutdown abort

Startup force

10. Which three functions are performed by dispatchers in a shared server configuration?

Sending shared server session responses back to requesters on the appropriate connection

Receiving inbound requests from processes using shared server connections

Writing inbound requests to the common request queue from all shared server connections

11. As the Database Administrator, you issued the following command:

Grant INSERT ANY TABLE, SELECT ANY TABLE on schema HR to app_dev WITH ADMIN OPTION;

Which two items are true?

The INSERT ANY TABLE privilege was revoked from the app_dev user, cascading the revoke to all
users app-dev granted the privilege to.

User app_dev can grant INSERT ANY TABLE, SELECT ANY TABLE to other users for only the HR
schema.

12. The SCOTT/TIGER user exists in two databases, BOSTON_DB and DALLAS_DB, in two different
locations.

Each database has a tnsnames.ora file defining DALLAS_DB as a service name.

Examine this command:

CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING 'dallas_db';
How do you execute the command so that only SCOTT in BOSTON_DB can access the SCOTT
schema in DALLAS_DB?

as SCOTT in BOSTON_DB

13. How can you view or set thresholds for database metric alerts?

You can use the DBMS_SERVER_ALERT P/L SQL package GET_THRESHOLD and SET_THRESHOLD
procedures to view or modify metric thresholds.

14. No SAVE STATE was previously used on "PDB1. Which of these scenarios is the correct required
sequence of operations for a PDB1 to open automatically after a CDB restart?

Ensure PDB1 is open and issue the ALTER PLUGGABLE DATABASE PDB1 SAVE STATE.

15. Which two privileges or roles can be granted to a user for viewing the audit records stored in
the consolidated Unified Audit location?

The AUDIT_ADMIN role

The AUDIT_VIEWER role

16. What is a SQL Plan Directive?

A SQL Plan Directive is additional information and instructions that the Optimizer can use to
generate a more optimal plan.

17. How does Oracle Database execute SQL statements?

Oracle Database uses an execution plan, which is a series of operations performed in sequence to
execute a SQL statement.

18. What is the Oracle Database Optimizer?

The Optimizer is a part of the Oracle Database server that creates the execution plan for a SQL
statement.

19. Which of the following is NOT a requirement for the remote CDB to clone a PDB using DBCA?

The network administrator must configure the firewall to allow incoming connections to the
listener port.

20. Which two statements are true about Oracle Data Dictionary?

It is owned by the SYS user.

Data dictionary base tables can be queried directly.

21. Which two are benefits of using Oracle Unified Auditing?

Consolidated location for all database auditing

Unified Auditing enabled by default in Oracle Database 23ai

22. Which one of the following operations is part of database mandatory audits?

Mandatory audits are required to monitor audit policy changes.

23. Which three are types of segments in an Oracle Database?

Tables

Undo

Clusters

24. Examine this command and some partial output:

LSNRCTL> start LISTENER_1

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=host01.abc.com) (PORT=1562)))

Services Summary...

Service " DB01.abc.com has 1 instance(s). "

Instance DB01 ", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Why does the DB01.abc.comservice show unknown status?

The listener is not listening on the default port 1521.

25. In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:

1. A size of 100 MB

2. AUTOEXTEND is off

3. UNDO RETENTION is set to 15 minutes

4. It has RETENTION GUARANTEE

UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens.

What will happen when the next update is attempted by any transaction?

It fails and returns the error message "ORA-30036: unable to extend segment by 8 in undo
tablespace 'UNDOTBS1'".
26. How do you monitor database performance proactively?

Automated AWR Snapshot is executed periodically to collect database performance statistics.

27. A database is configured to use automatic undo management with temporary undo enabled.

An UPDATE is executed on a temporary table.

Where is the UNDO stored?

In the temporary tablespace

28. Which two statements about managing a pluggable database (PDB) are true?

You can modify the settings of each PDB without changing the mode of the PDB.

During the PDB creation, you can specify the tablespace where the PDB data files will be stored.

29. The ORCL database has RESUMABLE_TIMEOUT 7200 and DEFERRED_SEGMENT_CREATION =

FALSE

User Ul has a 1 MB quota in tablespace DATA.

U1 executes this command:

SQL> CREATE TABLE 1 AS

(SELECT object_name, sharing, created

FROM dba_objects);

Ul complains that the command is taking too long to execute.

In the alert log, the database administrator (DBA) finds this:

2017-03-06T12:15:17.183438+05:30

statement in resumable session 'User U1 (136), Session 1, Instance 1' was suspended due to ORA-
01536: space quota exceeded for tablespace 'DATA'

Which are three actions any one of which the DBA could take to resume the session?

Drop other U1 objects in DATA.

Grant UNLIMITED TABLESPACE to U1.

Increase Ul's quota sufficiently in DATA.

30. Which two statements are true about UNDO and REDO?

DML modifies Oracle database objects and generates UNDO and REDO.

The generation of UNDO generates REDO.

31. Which option is used to implement basic backup and recovery procedures?

a) System Global Area

32.Which command can connect to an Oracle database instance using operating system
authentication?

a) SQL> CONNECT / AS SYSDBA

1. In which open mode is an instance first associated with a specific database at startup?

mount mode

2. Which pattern does Sharding follow to divide data in independent databases?

Horizontal

3. Which container in a Multitenant Container Database (CDB) includes the root and all PDBs?

System container

4. Which memory area is allocated when a database instance is started?

System Global Area

5. Which option contains data files to store metadata and common users?

CDB$ROOT

6. Which relationship is the goal of a multitenant architecture?

One PDB to one application

1. Which Enterprise Manager Cloud Control 13c feature consolidates Automatic Workload
Repository (AWR) reports using the AWR warehouse?

Chargeback and Capacity Planning

2. Which mode of DBCA uses a response file?

Silent

3. Which command can connect to an Oracle database instance using operating system
authentication?

SQL> CONNECT / AS SYSDBA

4. You must perform database administration tasks on multiple database instances and listeners.

Which tool allows you to do this?

Oracle Enterprise Manager Cloud Control (EM Cloud Control)

1. Which two statements are true about a DBCA template?

A template is used by DBCA to create new databases and duplicate existing databases.

A template is an XML file that contains information required to create a database.

2. Examine this command:

$ cat CrCDBTEST.sh

$ORACLE_HOME/bin/dbca -silent -createDatabase -templateName

General_Purpose.dbc -gdbname CDBTEST -sid CDBTEST

-createAsContainer Database true -numberOfPDBs 0 -useLocal UndoForPDBs true

-responseFile NO_VALUE -totalMemory 1800 -sysPassword password

-systemPassword password -pdbAdminPassword password

-enableArchive true -recoveryArea Destination/u01/app/oracle/fast_recovery_area

-recoveryArea Size 15000 -datafileDestination/u01/app/oracle/oradata

How many databases are created when the CrCDBTEST.sh script is executed?

One CDB and zero PDBs

3. Which is a Variable-Width Multibyte scheme?

Unicode (ALI16UTF16)

4. Which two configuration changes can be done to an existing database using DBCA?

Add new database options.

Change default security settings.

5. Which command checks if a database instance is running?

pgrep -If orclcdb

CORRECT

1. Which tablespace in a CDB contains the Automatic Workload Repository (AWR)?

SYSAUX

CORRECT

2. Examine this command and output:

$ cat initCDBDEV.ora

DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'

ENABLE_PLUGGABLE_DATABASE=true

db_name='CDBDEV'

audit_file_dest= '/u01/app/oracle/admin/CDBDEV/adump'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=CDBDEVXDB)'

control_files = (/u01/app/oracle/oradata/ora_controll,
/u01/app/oracle/fast_recovery_area/ora_control2)

compatible ='19.0.0.0'

Which parameter specifies the Oracle Managed Files (OMF) directory?

DB_CREATE_FILE_DEST

3. Examine this command:

CREATE DATABASE cdb1

USER SYS IDENTIFIED BY p1 USER SYSTEM IDENTIFIED BY p2

LOGFILE GROUP 1 ('/u01/app/oradata/CDB1/redola.log',

'/u02/app/oradata/CDB1/redolb.log') SIZE 100M,

GROUP 2 ('/u01/app/oradata/CDB1/redo2a.log',

'/u02/app/oradata/CDB1/redo2b.log') SIZE 100M

CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL DATAFILE

'/u01/app/oradata/CDB1/system01.dbf' SIZE 325M

SYSAUX DATAFILE '/u01/app/oradata/CDB1/sysaux01.dbf' SIZE 325M

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/u01/app/oradata/CDB1/temp01.dbf' SIZE 20M

UNDO TABLESPACE undotbs

DATAFILE '/u01/app/oradata/CDB1/undotbs01.dbf' SIZE 200M

ENABLE PLUGGABLE DATABASE

SEED FILE_NAME_CONVERT=('/u01/app/oradata/CDB1','/u01/app/oradata/CDB1/seed');

Which two statements must be true to generate the names of PDBSSEED files using the names of
the CDB root files?

The/u01/app/oradata/CDB1/seed seed directory must exist.

The/u01/app/oradata/CDB1 root directory must exist.

4. Examine this command that executes successfully:

CREATE DATABASE PDBDEV

USER SYS IDENTIFIED BY password

USER SYSTEM IDENTIFIED BY password

EXTENT MANAGEMENT LOCAL

DEFAULT TEMPORARY TABLESPACE temp

DEFAULT TABLESPACE users

UNDO TABLESPACE undotbs1

ENABLE PLUGGABLE DATABASE;

Which clause causes Oracle to maintain bitmap in each data file to keep track of the free or used
status of blocks in it?

=EXTENT MANAGEMENT LOCAL

5. Which initialization parameter maps names of existing files to new file names when creating a
CDB?

DB_FILE_NAME_CONVERT

1. Which tablespace in a CDB contains the Automatic Workload Repository (AWR)?

SYSAUX

2. You create a CDB using the CREATE DATABASE command.

Entering which database state for the CDB causes the creation of redo log files and CDBSROOT data
files?

OPEN

1. In which instance state can a DBA rename data files and perform full database recovery?

MOUNT

2. Which command locates, opens, and reads all of a database's control files?

ALTER DATABASE MOUNT;

3. Which command is used to shut down a PDB named orclpdb3?

ALTER PLUGGABLE DATABASE orclpdb3 CLOSE;

CORRECT

1. You must be able to investigate problems, view health check reports, and package and upload
first-failure data to Oracle Support?

Which utility does this?

Automatic Diagnostic Repository command line

2. Which view displays parameter values in effect for the querying session?

V$PARAMETER2

3. Which two statements are true about the Automatic Diagnostic Repository (ADR)?

It is a system-wide central tracing and logging repository.

It is a file-based repository.

It supports diagnostics for Automatic Storage Management (ASM).

4. An incident number is assigned to an internal error detected by a process while managing a

database.

The diagnostic data is immediately captured and tagged with the incident number.

In which file or files would you find the diagnostic data?

Trace files

5. Examine this command and output:

ALTER SYSTEM SET sec_max_failed_login_attempts = 2 SCOPE=BOTH;

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

What is the reason for this output?

The parameter cannot be updated in memory.

6. The date format for displaying the HIRE_DATE column is altered from dd-mm-yy to mm-dd-yy by
modifying the NLS_DATE_FORMAT parameter.

When the database instance is restarted, the previous date format is reflected.

Which statement is true?

NLS_DATE_FORMAT is a session-level parameter.

1. Which configuration file contains server-side network configuration parameters?

listener.ora

2. Which statement is true about the Oracle Net Configuration Assistant?

It automatically configures the default listener during a typical installation of Oracle Database.

3. Which is a feature of a shared server configuration?

Each client process is connected to a dispatcher.

4. Which statement is true about the functionality of Oracle Net service?

A session starts when a user successfully connects to a database instance.

5. Which statement is true about a dedicated server configuration?

It uses one server process to handle requests for a single client process.
6. Which two statements are true about listeners?

The default listener is automatically populated with available database services by dynamic service
registration.

A listener s configuration is stored in the listener.ora file.

1. Which command creates the Oracle Net Service on your system?

Netmgr

2. Which two statements are true about the local naming method?

It adds net service names and connect descriptors to the tnsnames.ora file.

It supports load balancing.

3. Which naming method uses a tnsnames.ora file?

Local naming method

4. Question needs to be added

/u01/app/oracle/product/19.3.0/dbhome_1 identifies the home directory for the database.

CDBTEST is the name of a database instance.

5. Which naming method stores net service names in Distributed Computing Environment (DCE)
Cell Directory Services (CDS)?

External naming

6. What is the responsibility of the server process?

Validation of authorization credentials

1. Questions need to be added

LSNRCTL> status

2. You have created a new database 'findb' and used the listener.ora file to associate a new listener
'finlist' with it. Which listener control utility command will you use to enable finlist to listen to the
services associated with findb?

LSNRCTL> start finlist

3. Which command is used to initiate service registration immediately after a listener starts?

SQL> ALTER SYSTEM REGISTER

4. Which two statements are true about listener dynamic service registration?

It provides connect-time failover.

It provides connection load balancing.

5. Which two are advantages of using static service registration?

It enables creation of a listener for a particular PDB.

It allows a time lag between database instance startup and the users logging in to the instance.

6. Which two statements are true about the Listener Registration (LREG) process?

It polls listeners to check if they are running.

It registers the location and load of database instances with listeners.

1. Question needs to be added

Shared server is configured for Oracle XML DB.

2. What is the correct flow of control when shared server configuration is used?

Client-->Listener-->Dispatcher-->Server Process-->Server

3. For which situation is Oracle shared server configuration suitable?

Single dispatcher managing hundreds of user connections

4. Which two statements are true about Oracle shared server configuration?

Cursor state contains runtime memory values for the SQL statement.

5. Which parameter can be used to limit the amount of memory allocated to a session?

PRIVATE_SGA

6. Which initialization parameter specifies the number of shared servers when database instance
starts?

SHARED_SERVERS

1. Which command changes the status of a newly created PDB from new to normal?

ALTER PLUGGABLE DATABASE orclpdb3 OPEN;

2. Which method must be used to provision a PDB to test a patch on a production application to
enhance the application to a new version?

Clone a PDB from another PDB in the same CDB.

3. Which command verifies the default service created for a PDB created from PDB$SEED?

SELECT name FROM v$services;

4. Examine this statement that executes successfully:CREATE PLUGGABLE DATABASE EMP_PDB

ADMIN USER admin1 IDENTIFIED BY p1 ROLES=(CONNECT) FILE_NAME_CONVERT =
('PDB$SEEDdir', 'PDB1dir');=====A PDB is created from the seed PDB.
5. Which three commands can be used to create a new PDB from the PDB$SEED?

FILE_NAME_CONVERT

DB_CREATE_FILE_DEST

PDB_FILE_NAME_CONVERT

6. What is automatically created by Oracle when a new PDB is created from PDB$SEED?

A user with the PDB_DBA role

1. Which statement is true about cloning refreshable copy PDBs in hot mode?

Refreshable copy PDB must be opened only in read-only mode.

2. Which two statements are true about Proxy PDBs?

They are used to execute SQL statements in a remote PDB as if it were a local PDB in the same
CDB.

They are used to query data contained in PDBs in different CDBs.

3. What is required for near-zero downtime PDB relocation?

Enable local undo mode and ARCHIVELOG mode in both CDBs.

4. Examine this command executed on CDBTEST instance and its output:

CREATE PLUGGABLE DATABASE pdb_relocated

FROM orclpdb3@link_ORCLCDB RELOCATE

FILE_NAME_CONVERT= ('u01/app/oracle/oradata/ORCLCDB/orclpdb3',
'/u01/app/oracle/oradata/CDBTEST/pdb_relocated');

ERROR:

ORA-17628: Oracle error 1031 returned by remote Oracle server

ORA-01031: insufficient privileges

Which command can resolve these errors?

GRANT sysoper TO system CONTAINER=all;

5. Which procedure generates an XML file to plug a non-CDB into a CDB as a PDB for migrating
data?

DBMS_PDB.DESCRIBE
Correct.

6. Which two conditions must be satisfied for proxying a PDB between CDB1 and CDB2?

A proxy PDB is created in the application root in the local CDB to reference the application root
replica in the remote CDB.

An application root replica must be created in the remote CDB to replicate the application root.

1. What must be true to permit a parameter to be changed on a per PDB basis?

ISPDB_MODIFIABLE=TRUE

2. Which command must be used to open a PDB to change the global database name?

ALTER PLUGGABLE DATABASE OPEN RESTRICTED;

3. Which PDB cannot be dropped?

A PDB seed

4. Which statement can be executed while connected to a PDB but will affect the whole CDB?

ALTER SYSTEM CHECKPΟΙΝΤ

5. Which PDB parameter can be modified only in restricted mode?

GLOBAL_NAME

6. Which statement is true regarding dropping PDBs?

Dropping the proxied PDB of a proxy PDB does not drop the proxy PDB.

1. Which tablespace is used when a SQL command performs sorts and joins on heavily populated
tables?

TEMP

2. Which two statements are true about tablespaces?

They are associated with a specific block size.

They group related logical structures.

3. Which two statements are true about the SYSAUX tablespace?

It reduces the load on the SYSTEM tablespace.

It exists in the root container and each PDB.

4. Which two tablespaces CANNOT be made read-only?

SYSTEM/SYSAUX

5. Which Oracle database files are used to store metadata related to backups?

Control files
6. Consider this statement:

DEFERRED_SEGMENT_CREATION = FALSE

Which statement is true?

A segment is created when a table is created.

1. Examine these commands to create a tablespace in a CDB:

SQL> CREATE TABLESPACE DATA1 DATAFILE '/u01/app/oracle/oradata/ORCLCDB/data_01.dbf' SIZE

10M; SQL> ALTER DATABASE DEFAULT TABLESPACE DATA1;

Which two results do these commands accomplish?

They make DATA1 the default tablespace for all objects created in the CDB root.

A tablespace is created in the root container.

2. Which two are valid operations using the ALTER TABLESPACE command?

Place the tablespace in transition read-only mode.

Bring the tablespace online.

3. Which CREATE TABLESPACE clause is used to manage free space in the segment with free lists?

SEGMENT MANAGEMENT MANUAL

4. Consider this command and its output:

SQL> SELECT status, contents, logging, plugged_in, bigfile, extent_management, allocation_type

FROM dba_tablespaces where tablespace_name='SYSAUX';

STATUS CONTENTS

LOGGING PLU BIG

EXTENT_MAN ALLOCA

ONLINE PERMANENT

SQL>

Which statement is true?

DML operations are logged in the redo log file.

5. Which statement is true when a data file is moved to another location, disk, or storage system
using the ALTER DATABASE MOVE DATAFILE command?

The TO clause can be omitted only when an Oracle-managed file is used.

1. What value should be set for the CLIENT_NAME parameter when performing a segment shrink
task using the DBMS_AUTO_TASK_ADMIN. ENABLE procedure?

auto space advisor

2. Which tool recommends changes to the way statistics are gathered based on rules consistent
with the current release?

Optimizer Statistics Advisor

3. Which two statements apply to SQL plan directives?

They are defined on query expressions.

They have additional information that the optimizer can use to generate better plans.

4. Which two statements are true about SQL Performance Analyzer?

It predicts the impact of system changes on SQL workload response times.

It builds different versions of SQL workload performance.

5. Which Optimizer Statistics Advisor report section contains PL/SQL statements or commands that
can be executed on the command line to solve performance problems?

Actions

6. Which type of analysis done by the SQL Tuning Advisor verifies its own estimates and collects
auxiliary information to remove estimation errors?

SQL Profiling

1. What is the relationship between the STATISTICS_LEVEL and TIMED_STATISTICS parameters?

When STATISTICS_LEVEL is set to BASIC, the value of TIMED_STATISTICS defaults to FALSE.

2. Which view displays performance data for all active sessions?

V$SESSMETRIC

3. Querying which view displays the total wait time for events that have had at least one wait by
one session occur since instance startup?

V$SYSTEM_EVENT

4. Which two can be used to view and change metric threshold settings?

Enterprise Manager Cloud Control

DBMS_SERVER_ALERT

5. Which two views are used to monitor sessions grouped by service name in a multitier
environment? V$SERVICE_WAIT_CLASS

V$SERVICE_EVENT
6. Which two statements are true about threshold alerts?

They are also called stateful alerts.

They are displayed when querying DBA_OUTSTANDING_ALERTS and when cleared, are displayed
when querying DBA_ALERT_HISTORY.

1. Which statement is true when Automatic Workload Repository (AWR) snapshots are enabled for
Automatic Database Diagnostic Monitor (ADDM) analysis in a PDB?

They are stored in the PDB.

2. Which advisor looks for tables and indexes that consume more space than they require?

Segment Advisor

3. Which two statements are true about the Automatic Workload Repository (AWR)?

It uses statistics for problem detection and self-tuning.

It is a data warehouse for database statistics.

4. Which process stores snapshots on disk?

Manageability Monitor (MMON)

5. Which tablespace contains Automatic Workload Repository (AWR) tables?

SYSAUX

6. What must be true for information generated by the Automatic Database Diagnostic Monitor
(ADDM) to be valid?

The instance must not have been shut down between two snapshots.

1. What is the maximum CPU utilization percentage that can be allocated to ORASAUTOTASK?

90%

2. Which resource plan is used by all predefined maintenance windows?

DEFAULT_MAINTENANCE_PLAN

3. Examine this command:

BEGIN

dbms_auto_task_admin.enable( client_name => 'sql tuning advisor', operation => NULL,

window_name => NULL);

END;

Which statement is true?

It enables maintenance tasks for all maintenance windows.

4. You plan to increase the duration of the Sunday maintenance window by two hours.Which
procedure must you use?>DBMS_SCHEDULER.SET_ATTRIBUTE
5. Which two tools or procedures can be used to configure and manage automated maintenance
tasks?

Enterprise Manager

DBMS_AUTO_TASK_ADMIN

6. Which statement is true when using DBMS_SCHEDULER.SET_ATTRIBUTE to modify attributes of

an open maintenance window?

Changes only take effect the next time the window is opened.

1. How does a database server identify proactive database maintenance tasks?

By analyzing information stored in the Automatic Workload Repository (AWR)

INCORRECT

2. Which automated maintenance task cannot be restarted every four hours in the case of a very
long maintenance window?

Automatic SQL Tuning Advisor

3. Which predefined automated maintenance task must be used to identify reclaimable space and
make defragmenting recommendations?

Automatic Segment Advisor

4. Which statement is true about MAINTENANCE_WINDOW_GROUP?

It consists of seven predefined maintenance windows, each representing a day of the week by
default.

5. Which two statements are true about the Oracle Scheduler?

It creates a job for each maintenance task scheduled to run in a maintenance window.

It is configured using procedures and functions in the DBMS_SCHEDULER package.

6. How can the impact of automated maintenance tasks on normal database operations be
limited?

By associating a Database Resource Manager plan to each maintenance window

1. Examine this command that executes successfully:

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'resumable insert';

An INSERT INTO... SELECT FROM statement now gets suspended.

Which two statements are true?

Information related to the suspended statement is displayed in the STATUS column in the
DBA_RESUMABLE view.

If the issue is unresolved within one hour of being suspended, the transaction is canceled and an
error is encountered.
2. Which two statements are true about private temporary tables?

The table definition is dropped at the end of a transaction.

Two or more concurrent sessions can create private temporary tables with the same name but
with a different number of columns.

3. Examine this command:

SQL> exec DBMS_SERVER_ALERT.SET_THRESHOLD-

> (DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,-

> DBMS_SERVER_ALERT.OPERATOR_GE,'60', DBMS_SERVER_ALERT.OPERATOR_GE, '80',-

> 1, 1, 'ORCL', DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, 'TBSALERT');

What does it do?

It configures the database server to issue a warning to users when the pctfree value for the
TBSALERT tablespace reaches 60 percent.

4. Which statement is true regarding migrated rows?

Migration only occurs as a result of update statements.

5. Which two statements are true about segment shrink operations?

They can only be performed for segments in an online tablespace.

In the first phase, rows are moved to the left part of the segment.

6. Which two statements are true about basic table compression?

It is valid for bulk data loading using SQL*Loader.

It is enabled by using the COMPRESS command in a CREATE or ALTER TABLE statement.

1. Which two statements are true about undo segments?

They are automatically assigned when transactions start.

They are managed in a circular fashion.

2. Which two statements are true about redo data?

It is stored in log files.

It is used to roll forward database changes.

3. Which two are benefits of enabling temporary undo?

It reduces the amount of redo data written to the redo log.

It reduces the amount of undo data stored in the undo tablespaces.

4. UNDO_RETENTION is set to 500 in one of your databases and it is GUARANTEED.

Which statement is true?>Undo is retained for 500 seconds even if transactions fail.
5. Which two are features of temporary undo?

It reduces the data volume written to online redo logs.

It can be enabled at the database or session level.

6. Which two statements are true regarding local undo mode?

Switching to local undo mode requires that database be restarted in UPGRADE mode.

It is required when hot cloning a PDB.

1. Which authentication method is also referred to as authentication by the Oracle database

server?

Password authentication

2. What is true about local users in a multitenant environment?

They are created in a single PDB only.

3. Which two statements are true about quotas?

They are replenished when objects are dropped with the PURGE clause.

They are assigned to database accounts that own database objects.

4. Which two statements are true about schema-only accounts?

They enforce data access through an application.

They are created with the NO AUTHENTICATION clause.

5. Which Oracle-supplied administrator account is used by Oracle Enterprise Manager

management agents to monitor and manage databases?

DBSNMP

6. Which two are true about common users in a multitenant environment?

Their names must be case sensitive..

They must be given a name that starts with C## or c## by default.

They exist in the root container and all PDBs in the same CDB.

1. Which two statements are true about roles assigned to users?

They are granted to and revoked from users or other roles as though they were system privileges.

They can be granted both system and object privileges.

2. Which two users can revoke object privileges from another user?

The same user who originally granted the privilege

A user with the GRANT ANY OBJECT privilege

3. A CDB contains two PDBs, named PDB1 and PDB2.

The c##CDB_ADMIN common role and c##cdb_admin1 common user are created in all containers.

The PDB1_ADMIN local role is created in PDB1.

The user Tsmith and a local role PDB2_USER is created in PDB2.

You grant Tsmith the CREATE TABLE privilege with ADMIN OPTION.

Tsmith grants the CREATE TABLE privilege to c##cdb_admin1 user in PDB2.

Which two statements are true?

Tsmith can be granted a c##CDB_ADMIN role in PDB2.

c##cdb_admin1 can be granted a PDB1_ADMIN role in PDB1.

4. Examine this scenario:

1. The DBA grants the CREATE TABLE privilege to the user Joe with ADMIN OPTION.

2. Joe creates a table A.

3. Joe grants the CREATE TABLE privilege to the user Emily.

4. Emily creates a table B.

5. The DBA revokes the CREATE TABLE privilege from Joe.

Which two statements are true?

Joe can access table A but he can't create new tables.

Emily can access table B and still retains the CREATE TABLE privilege.

5. Which system privilege allows queries to access any object in the SYS schema?

SELECT ANY DICTIONARY

6. Which two privileges enable a user to start up and shut down database instances?

SYSDBA

SYSOPER

1. What is true about password complexity verification?

It checks on the password to verify that it meets certain rules using password verification
functions.

2. For which Oracle user is password complexity checking not enforced?

SYS

3. Which two statements are true about profiles?

Profiles manage the account status and place limitations on users' passwords.

Profiles impose resource limitations when RESOURCE_LIMIT is set to TRUE.

4. Examine these parameters set for a profile that is assigned to a user:

PASSWORD_REUSE_MAX is set to 10.

PASSWORD_REUSE_TIME is set to 30.

Which statement is true for the user?

The user cannot reuse a password until it has been changed 10 times and at least 30 days have
elapsed since that password was last used.

5. Examine these parameters set for a profile that is assigned to a user:

PASSWORD_REUSE_TIME 20

PASSWORD_REUSE_MAX UNLIMITED

Which statement is true for the user?

The user can never reuse a password.

CORRECT

6. Examine this command and output:

SQL>ALTER PROFILE hrprofile LIMIT INACTIVE_ACCOUNT_TIME 10;

ALTER PROFILE hrprofile LIMIT INACTIVE_ACCOUNT_TIME 10

ERROR at line 1:

ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIME

Which command will help resolve the error?

SQL>ALTER PROFILE hrprofile LIMIT INACTIVE_ACCOUNT_TIME 15;

1. What should you query to ensure that a database has been migrated to unified auditing?

VSOPTION

2. Which role enables users to create unified and fine-grained audit policies?

AUDIT_ADMIN

3. Which two statements are true about fine-grained auditing (FGA)?

It audits DELETE statements regardless of columns specified.

It is created only if the table or view exist when audit policy is created.

4. Which type of auditing captures the actual SQL statement rather than the events occurred?

Fine-grained auditing

5. Which statements are audited when a CREATE AUDIT POLICY statement includes the ONLY
TOPLEVEL clause?

It audits SQL statements issued directly by users.

6. Examine this command:

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB

(AUDIT_TRAIL_TYPE=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

AUDIT_TRAIL_PURGE_INTERVAL => 12,

AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_PJ',

USE_LAST_ARCH_TIMESTAMP => TRUE,

CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);

Which two statements are true?

It deletes audit records created before the last archived time stamp.

It deletes audit trail records for the current pluggable database.

1. Which format is used by SQL*Loader to process data when no record format is specified in the
control file?

Stream record format

2. Which parameter in control file of SQL*Loader is used for loading data into empty tables?

INSERT

3. Which SQL*Loader file provides information about execution errors?

The Log file

4. You must limit the number of execution threads that can operate on behalf of a Data Pump
export job.

Which parameter does this?

PARALLEL

5. To which file are records rejected by SQL*Loader written when their input format is invalid?

Bad file

6. Which records are written to the discard file by SQL*Loader?

Records that don't match any record-selection criteria specified in the control file

1. Which result can be accomplished by using the ORACLE_LOADER access driver?

Load data from an external table into internal tables.

2. Which two statements are true about the ORACLE_DATAPUMP access driver?

Its default behavior can be modified using the ACCESS PARAMETERS clause.

It can perform both load and unload operations.

3. Which two are valid operations that can be performed on external tables?

Querying a record/Partitioning data in the table

4. Which two statements are true about external tables?

They support partitioning.

They can be joined with other database tables.

5. Examine this statement that executes successfully:

SQL> CREATE TABLE dept_ext

ORGANIZATION EXTERNAL

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY def_dir1

LOCATION('dept_xt1.dmp','dept_xt2.dmp','dept_xt3.dmp')

PARALLEL 2

AS

SELECT * FROM oe.department;

The DEGREE_OF_PARALLELISM parameter is not set.

Which statement is true?

The files specified in the LOCATION clause are present in the directory pointed by the def_dirl
object.

6. Examine this statement:

CREATE TABLE emp_ext

(empno

number(7),

deptno

number(5),

ename varchar2(20),

sal number(10))

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY tmp_dir1

ACCESS PARAMETERS
(records delimited by newline badfile 'sh%a_%p.bad' logfile 'sh%a_%p.log' fields terminated by','
missing field values are null (empno, deptno, ename, sal) LOCATION ('empxt1.dat', 'empxt2.dat'))
PARALLEL REJECT LIMIT UNLIMITED;

Which statement is true?

There is no limit on the number of errors that can occur during query of external data.

1. Which two are advantages of using Recovery Manager (RMAN) for transporting databases?

It allows cross-platform inconsistent tablespace backups.

It allows the cross-platform transport of databases with different endian formats.

2. You must import all objects from the OE schema in database ORCLPDB1 into database
ORCLPDB2 under a new schema, OETEST.

Examine this command and output:

$ impdp SYSTEM/password@orclpdb2 REMAP_SCHEMA=oe:oetest DIRECTORY= dp_for_oe

DUMPFILE=expoe.dmp

ORA-39002: invalid operation

ORA-39070: Unable to open the log file

ORA-39087: directory name DP_FOR_OE is invalid

What is the reason?

The directory dp_for_oe does not exist in ORCLPDB2.

3. While performing a full transportable export using Oracle Data Pump, which two steps are
performed in the source database?

Set all user-defined tablespaces in the source database to read-only.

Invoke Data Pump export as a user with the DATAPUMP_EXP_FULL_DATABASE role and the FULLY
and TRANSPORTABLE ALWAYS options.

4. You must import all objects from the OE schema in database ORCLPDB1 into database
ORCLPDB2 in a new schema called OETEST.

Which DATAPUMP command executed on ORCLPDB2 will import the OE schema through a
database link?

$ impdp SYSTEM/password@orclpdb2 SCHEMAS=oe REMAP_SCHEMA=oe:oetest

NETWORK_LINK=link_orclpdb1

5. Which Data Pump import transformation provides the capability to change object ownership?

REMAP_SCHEMA

6. Which RMAN backup clause is used to back up one or more tablespaces that are not set to read-
only? > ALLOW INCONSISTENT
1. Examine this command that executes successfully:

$sqlldr sh/password@orclpdb1 TABLE=products

Which two statements are true?

Data is loaded using Express mode.

SQL*Loader will create a temporary external table to load data.

2. Examine this snippet from a SQL*Loader control file:

LOAD DATA

infile '$ORACLE_HOME/labs/DBModLoadTrans/DP_inventories.dat'

REPLACE

INTO TABLE SH.INVENTORIES

FIELDS TERMINATED BY ','

(warehouse_id,

product_id,

quantity_on_hand)

Which statement is true?

It deletes all table rows and inserts records from the DP_inventories.dat file.

3. You plan to import data using SQL*Loader.

One input file record's key column value matches a key value in one of the rows in the table to
which the data is imported.

What will be the result?

The record will be written to the BAD file, and the load operation will continue.

4. Which two statements are true about SQL*Loader direct path load?

It does not fire INSERT triggers.

It merges new index entries at the end of the load if the target table is indexed.

5. Which two statements are true about SQL*Loader conventional path load?

It enables the user to load data into clustered tables.

It maintains the index entry for each insert operation if the target table has one or more indexes.
6. Examine this command that executes successfully:

$ sqlldr userid=sh/****@orclpdb1 control=DP_inventories.ctl log-inventories.log

data=DP_inventories.dat ROWS=10 DIRECT=TRUE

Which two statements are true?

The NOT NULL constraint is enforced.

A data save occurs after reading 10 records from the data file.

6. Which CREATE TABLESPACE clause specifies the creation of a locally managed tablespace?

EXTENT MANAGEMENT