oracle 1z0-062 practice test
Oracle Database 12c: Installation and Administration Exam
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
Question 11
You use a recovery catalog for maintaining your database backups.
You execute the following command:
$rman TARGET / CATALOG rman / cat@catdb
RMAN > BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
Which two statements are true? (Choose two.)
- A. Corrupted blocks, if any, are repaired.
- B. Checks are performed for physical corruptions.
- C. Checks are performed for logical corruptions.
- D. Checks are performed to confirm whether all database files exist in correct locations
- E. Backup sets containing both data files and archive logs are created.
Answer:
B D
Explanation:
B (not C): You can validate that all database files and archived redo logs can be backed up by running a command as
follows:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
This form of the command would check for physical corruption. To check for logical corruption,
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
D: You can use the VALIDATE keyword of the BACKUP command to do the following:
Check datafiles for physical and logical corruption
Confirm that all database files exist and are in the correct locations.
Note:
You can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct
locations (D), and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When
performing a BACKUP...VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup.
It does not, however, actually produce any backup sets or image copies (Not A, not E).
Question 12
Examine the current value for the following parameters in your database instance:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 700M
DB_8K_CACHE_SIZE = 124M LOG_BUFFER = 200M
You issue the following command to increase the value of DB_8K_CACHE_SIZE:
SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M;
Which statement is true?
- A. It fails because the DB_8K_CACHE_SIZE parameter cannot be changed dynamically.
- B. It succeeds only if memory is available from the autotuned components if SGA.
- C. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_TARGET.
- D. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE.
Answer:
D
Explanation:
* The SGA_TARGET parameter can be dynamically increased up to the value specified for the SGA_MAX_SIZE parameter,
and it can also be reduced.
* Example:
For example, suppose you have an environment with the following configuration:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, the value of SGA_TARGET can be resized up to 1024M and can also be reduced until one or more of the
automatically sized components reaches its minimum size. The exact value depends on environmental factors such as the
number of CPUs on the system. However, the value of DB_8K_CACHE_SIZE remains fixed at all times at 128M
* DB_8K_CACHE_SIZE
Size of cache for 8K buffers
* For example, consider this configuration:
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, increasing DB_8K_CACHE_SIZE by 16 M to 144M means that the 16M is taken away from the
automatically sized components. Likewise, reducing DB_8K_CACHE_SIZE by 16M to 112M means that the 16M is given to
the automatically sized components.
Question 13
Examine these steps:
CONNECT / AS SYSDBA
CREATE DIRECTORY dumpdir AS '/u01/app/'; Directory created.
GRANT READ, WRITE ON DIRECTORY dumpdir TO PUBLIC; Grant succeeded.
CREATE USER TEST IDENTIFIED BY test; User created.
GRANT CREATE SESSION, RESOURCE, UNLIMITED TABLESPACE TO test; Grant succeeded.
CONN test/test
Connected.
CREATE TABLE test_employees (id NUMBER(3), name VARCHAR2(20), salary NUMBER(7)); Table created.
SQL> CREATE SYNONYM emp FOR test_employees;
Synonym created.
Now examine this command:
$ expdp test/test DIRECTORY=dumpdir DUMPFILE=test_emp.dmp LOGFILE=test.log TABLES=emp CONTENT=data_only
query='EMP:"WHERE salary=12000"'
What is true about the execution of this command?
- A. It throws an error as the QUERY parameter is not supported on an empty table.
- B. It throws an error as the table test_employees is empty and CONTENT=data_only is specified.
- C. It performs the export successfully.
- D. It throws an error as Data Pump cannot find a table called TEST.EMP.
Answer:
C
Question 14
Examine the following query output:
You issue the following command to import tables into the hr schema:
$ > impdp hr/hr directory = dumpdir dumpfile = hr_new.dmp schemas=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING: Y
Which statement is true?
- A. All database operations performed by the impdp command are logged.
- B. Only CREATE INDEX and CREATE TABLE statements generated by the import are logged.
- C. Only CREATE TABLE and ALTER TABLE statements generated by the import are logged.
- D. None of the operations against the master table used by Oracle Data Pump to coordinate its activities are logged.
Answer:
C
Explanation:
Oracle Data Pump disable redo logging when loading data into tables and when creating indexes.
The new TRANSFORM option introduced in data pumps import provides the flexibility to turn off the redo generation for the
objects during the course of import. The Master Table is used to track the detailed progress information of a Data Pump job.
The Master Table is created in the schema of the current user running the Pump Dump export or import, and it keeps tracks
of lots of detailed information.
Question 15
Examine the command:
SQL> CONNECT hr/hr@orcl
Which two configurations allow this command to execute successfully? (Choose two.)
- A. In the tnsnames.ora file, the SERVICE_NAME value of CONNECT_DATA should be explicitly suffixed with the domain name.
- B. The SERVICE_NAMES initialization parameter should contain the name orcl in the database host.
- C. The orcl TNS alias should be defined such that it is resolvable by a client running on the database host.
- D. The orcl TNS alias should be defined in the tnsnames.ora file on both the client and the database host.
- E. The TNS_ADMIN environment variable should be set to orcl on the client.
Answer:
B C