oracle 1z0-062 practice test

Question 1

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

• A. Creating and dropping tables in the data file being moved
• B. Performing file shrink of the data file being moved
• C. Querying tables in the data file being moved
• D. Performing Block Media Recovery for a data block in the data file being moved
• E. Flashing back the database
• F. Executing DML statements on objects stored in the data file being moved

Answer:

A C E F

Explanation:
- You can now move On line Datafile without hove to stop Monoged Recovery and manually copy and rename Files. This
can even be used to move Datafiles from or to ASM.
- New in Oracle Database 12c: FROM METAUNK. Physical Standby Database is in Active Data Guard Mode (opened READ
ONLY and Managed Recovery is running):
It is now possible to online move a Datafile while Managed Recovery is running, ie. the Physical Standby Database is in
Active Data Guard Mode. You can use this Command to move the
Datafile
- A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one
location to another and later flash back the database to a point in time
before the move, then the Data file remains in the new location, but the contents of the Data file ore changed to the contents
at the time specified in the flashback. Oracle0 Database
Administrator's Guide 12c Release 1 (12.1)

Question 2

You ran this command on a source database:
$> expdp hr/hr DIRECTORY=dumpdir DUMPFILE=emp1.dmp VIEWS_AS_TABLES=emp_dept
On the target database, you run this command:
$> impdp hr/hr DIRECTORY=dumpdir DUMPFILE=emp1.dmp VIEWS_AS_TABLES=emp_dept
Which two statements are true? (Choose two.)

• A. The expdp operation exports all rows for tables contained in the defining query of the EMP_DEPT view
• B. The impdp operation creates separate tables for each table contained in the defining query of the EMP_DEPT view
• C. The expdp operation exports all rows that are displayed when querying the EMP_DEPT view with no filter
• D. The impdp operation creates EMP_DEPT as a table
• E. The expdp operation exports the table definitions for tables that are queried in the EMP_DEPT view.
• F. The impdp operation creates EMP_DEPT as a view

Answer:

D E

Question 3

Automatic Shared Memory Management is enabled for your database instance. You notice that there are SQL statements
performing poorly because of repeated parsing activity.
Which action generates recommendations to overcome the performance issues?

• A. running the Memory Advisor for the buffer cache
• B. running the Memory Advisor for the library cache
• C. running the Memory Advisor for the SGA
• D. running the Memory Advisor for the PGA

Answer:

B

Question 4

Your database is configured in ARCHIVELOG mode, and a daily full database backup is taken by using RMAN. Control file
autobackup is configured.
Loss of which three database files can lead to an incomplete recovery? (Choose three.)

• A. inactive online redo log file group
• B. a data file belonging to the default temporary tablespace
• C. a data file belonging to the SYSAUX tablespace
• D. server parameter file (SPFILE)
• E. active online redo log file group
• F. all the control flies

Answer:

A E F

Question 5

You plan to install the Oracle Database 12c software. You want to use Oracle Automatic Storage Management (ASM) for
storage and Oracle Restart for managing components. Oracle Grid Infrastructure for a Standalone Server is already installed
on the server.
Which three statements must be true for successful installation of the Oracle Database 12c software? (Choose three.)

• A. The OSBACKUPDBA, OSDGDBA, and OSKMDBA OS groups must be created.
• B. An operating system OSDBA group must be created for users with the SYSDBA system privilege.
• C. The Oracle Database 12c software owner and the Oracle Grid Infrastructure owner must belong to the same Oracle Inventory Group.
• D. The Oracle Database 12c installation must have a separate Oracle inventory directory.
• E. An operating system OSOPER group be created for users with the SYSOPER system privilege.

Answer:

B C E

Question 6

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

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

• A. All the triggers for the table are disabled without changing any of the column names or column types in the table.
• B. The primary key constraint on the EMPLOYEES table is disabled during redefinition.
• C. VPD policies are copied from the original table to the new table during online redefinition.
• D. You must copy the VPD policies manually from the original table to the new table during online redefinition.

Answer:

B C

Explanation:
C (not D): CONS_VPD_AUTO
Used to indicate to copy VPD policies automatically
* DBMS_RLS.ADD_POLICY
/ The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement
Virtual Private Database (VPD).DBMS_RLS is available with the Enterprise Edition only.
Note:
* CONS_USE_PK and CONS_USE_ROWID are constants used as input to the "options_flag" parameter in both the
START_REDEF_TABLE Procedure and CAN_REDEF_TABLE Procedure.CONS_USE_ROWID is used to indicate that the
redefinition should be done using rowids while CONS_USE_PK implies that the redefinition should be done using primary
keys or pseudoprimary keys (which are unique keys with all component columns having NOT NULL constraints). *
DBMS_REDEFINITION.START_REDEF_TABLE
To achieve online redefinition, incrementally maintainable local materialized views are used. These logs keep track of the
changes to the master tables and are used by the materialized views during refresh synchronization.
* START_REDEF_TABLE Procedure
Prior to calling this procedure, you must manually create an empty interim table (in the same schema as the table to be
redefined) with the desired attributes of the post-redefinition table, and then call this procedure to initiate the redefinition.

Question 7

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

Which statement is true?

• A. Only SGA components are sized automatically.
• B. Memory is dynamically re-allocated between the SGA and PGA as needed.
• C. The size of the PGA cannot grow automatically beyond 500 MB.
• D. The value of the MEMORY_TARGET parameter cannot be changed dynamically.

Answer:

B

Question 8

You want to capture column group usage and gather extended statistics for better cardinality estimates for the CUSTOMERS
table in the SH schema.
Examine the following steps:
1. Issue the SELECT DBMS_STATS.CREATE_EXTENDED_STATS (SH, CUSTOMERS) FROM dual statement.
2. Execute the DBMS_STATS.SEED_COL_USAGE (null, SH, 500) procedure.
3. Execute the required queries on the CUSTOMERS table.
4. Issue the SELECT DBMS_STATS.REPORT_COL_USAGE (SH, CUSTOMERS) FROM dual statement.
Identify the correct sequence of steps.

• A. 3, 2, 1, 4
• B. 2, 3, 4, 1
• C. 4, 1, 3, 2
• D. 3, 2, 4, 1

Answer:

B

Explanation:
Step 1 (2). Seed column usage
Oracle must observe a representative workload, in order to determine the appropriate column groups. Using the new
procedure DBMS_STATS.SEED_COL_USAGE, you tell Oracle how long it should observe the workload.
Step 2: (3) You don't need to execute all of the queries in your work during this window. You can simply run explain plan for
some of your longer running queries to ensure column group information is recorded for these queries.
Step 3. (1) Create the column groups
At this point you can get Oracle to automatically create the column groups for each of the tables based on the usage
information captured during the monitoring window. You simply have to call the
DBMS_STATS.CREATE_EXTENDED_STATS function for each table.This function requires just two arguments, the schema
name and the table name. From then on, statistics will be maintained for each column group whenever statistics are
gathered on the table.
Note:
* DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the
database has processed for a given object.
* The Oracle SQL optimizer has always been ignorant of the implied relationships between data columns within the same
table. While the optimizer has traditionally analyzed the distribution ofvalues within a column, he does not collect value-
based relationships between columns.
* Creating extended statisticsHere are the steps to create extended statistics for related table columns
withdbms_stats.created_extended_stats:
1 - The first step is to create column histograms for the related columns.2 Next, we run dbms_stats.create_extended_stats
to relate the columns together. Unlike a traditional procedure that is invoked via an execute (exec) statement, Oracle
extended statistics are created via a select statement.

Question 9

Your database is in ARCHIVELOG mode and you want to automate the backup scheduling for your database.
Which two tools or utilities would you use to achieve this? (Choose two.)

• A. Oracle Enterprise Manager Database Express (EM Express)
• B. Oracle Enterprise Manager Cloud Control
• C. Database Configuration Assistant (DBCA)
• D. Recovery Manager (RMAN) script invoked by using scheduler

Answer:

B D

Question 10

In your database, USERS is the default permanent tablespace.
Examine the commands and their outcome:

You plan to execute the commands:

Which two statements are true? (Choose two.)

• A. The MYTAB table is created in the SYSTEM tablespace but no rows can be inserted into the table by USER02.
• B. The MYTAB table is created in the SYSTEM tablespace and rows can be inserted into the table by USER02.
• C. The MYTAB table is created in the USERS tablespace but no rows can be inserted into the table by USER02.
• D. The CREATE TABLE statement generates an error because the SYSDBA privilege does not provide any space quota on the SYSTEM tablespace by default.
• E. The MYTAB table is owned by the SYS user.

Answer:

B E