[Free] Download Latest (March 2016) Oracle 1Z0-062 Real Exam 41-50

Ensurepass

QUESTION 41

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?

 

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.

 

Correct Answer: BF

Explanation:

Not A: Both indexes are created fine.

B: The invisible index ORD_CUSTOMERS_IX1 and the bitmap index are both updated by DML operations on the Orders table.

F: Since ORD_CUSTOMERS_IX1 is invisible only ORD_CUSTOMERS_IX2 is used by the query optimizer.

Not C,Not D,Not E:

* ord_customer_ix1 is an invisible index and is therefore not used by the optimizer.

* VISIBLE | INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

Note: Specify BITMAP to indicate that index is to be created with a bitmap for each distinct key, rather than indexing each row separately. Bitmap indexes store the rowids associated with a key value as a bitmap. Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. The internal representation of bitmaps is best suited for applications with low levels of concurrent transactions, such as data warehousing.

 

 

QUESTION 42

Examine the following impdp command to import a database over the network from a pre-12c Oracle database (source):

 

clip_image002

 

Which three are prerequisites for successful execution of the command?

 

A.

The import operation must be performed by a user on the target database with the DATAPUMP_IMP_FULL_DATABASE role, and the database link must connect to a user on the source database with the DATAPUMP_EXD_FULL_DATABASE role.

B.

All the user-defined tablespaces must be in read-only mode on the source database.

C.

The export dump file must be created before starting the import on the target database.

D.

The source and target database must be running on the same platform with the same endianness.

E.

The path of data files on the target database must be the same as that on the source database.

F.

The impdp operation must be performed by the same user that performed the expdp operation.

 

Correct Answer: ABD

Explanation:

In this case we have run the impdp without performing any conversion if endian format is different then we have to first perform conversion.

 

 

QUESTION 43

The user SCOTT owns the CUST table that is placed in the SALES tablespace. The user SCOTT opens a session and executes commands as follows:

 

SQL> INSERT INTO cust VALUES(101, ‘JACK’);

1 row created.

SQL> INSERT INTO cust VALUES(102, ‘SMITH’);

1 row created.

 

As a DBA, you execute the following command from another session:

 

ALTER TABLESPACE sales READ ONLY;

Which statement is true regarding the effect of this command on the transaction in Scott’s session?

 

A.

The command fails as a transaction is still pending.

B.

The transaction in Scott’s session is rolled back and the tablespace becomes readonly.

C.

The command waits and the user SCOTT can execute data manipulation language (DML) statements only as part of the current transaction.

D.

The command hangs until all transactions on the objects in the tablespace commit or rollback, and then the tablespace is placed in readonly mode.

 

Correct Answer: D

 

 

QUESTION 44

Identify two situations in which the alert log file is updated.

 

A.

Running a query on a table returns ORA-600: Internal Error.

B.

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

C.

Creating a table returns ORA-00955: name us already in used by an existing objects.

D.

Inserting a value into a table returns ORA-00001: unique constraint (SYS.OK_TECHP) violated.

E.

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

 

Correct Answer: AE

Explanation:

The alert log is a chronological log of messages and errors, and includes the following items:

All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur

Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements

Messages and errors relating to the functions of shared server and dispatcher processes

Errors occurring during the automatic refresh of a materialized view

The values of all initialization parameters that had nondefault values at the time the database and instance start

Note:

The alert log file (also referred to as the ALERT.LOG) is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is:

database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruptions.

 

 

QUESTION 45

You upgrade your Oracle database in a multiprocessor environment. As a recommended you execute the following script:

 

SQL > @utlrp.sql

 

Which two actions does the script perform?

 

A.

Parallel compilation of only the stored PL/SQL code

B.

Sequential recompilation of only the stored PL/SQL code

C.

Parallel recompilation of any stored PL/SQL code

D.

Sequential recompilation of any stored PL/SQL code

E.

Parallel recompilation of Java code

F.

Sequential recompilation of Java code

 

Correct Answer: CE

Explanation:

utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of “0”. The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

0 – The level of parallelism is derived based on the CPU_COUNT parameter.

1 – The recompilation is run serially, one object at a time.

N – The recompilation is run in parallel with “N” number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

Reference: Recompiling Invalid Schema Objects

 

 

QUESTION 46

Which three statements are true about the working of system privileges in a multitenant control database (CDB) that has pluggable databases (PDBs)?

 

A.

System privileges apply only to the PDB in which they are used.

B.

Local users cannot use local system privileges on the schema of a common user.

C.

The granter of system privileges must possess the set container privilege.

D.

Common users connected to a PDB can exercise privileges across other PDBs.

E.

System privileges with the with grant option container all clause must be granted to a common user before the common user can grant privileges to other users.

 

Correct Answer: ACE

Explanation:

A, Not D: In a CDB, PUBLIC is a common role. In a PDB, privileges granted locally to PUBLIC enable all local and common users to exercise these privileges in this PDB only.

C: A user can only perform common operations on a common role, for example, granting privileges commonly to the role, when the following criteria are met:

The user is a common user whose current container is root.

The user has the SET CONTAINER privilege granted commonly, which means that the privilege applies in all containers.

The user has privilege controlling the ability to perform the specified operation, and this privilege has been granted commonly

Note: Every privilege and role granted to Oracle-supplied users and roles is granted commonly except for system privileges granted to PUBLIC, which are granted locally.

 

 

QUESTION 47

Identify three scenarios in which you would recommend the use of SQL Performance Analyzer to analyze impact on the performance of SQL statements.

 

A.

Change in the Oracle Database version

B.

Change in your network infrastructure

C.

Change in the hardware configuration of the database server

D.

Migration of database storage from non-ASM to ASM storage

E.

Database and operating system upgrade

 

Correct Answer: ACE

Explanation:

Oracle 11g/12c makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:

(E) Database, operating system, or hardware upgrades.

(A,C) Database, operating system, or hardware configuration changes.

Database initialization parameter changes.

Schema changes, such as adding indexes or materialized views.

Refreshing optimizer statistics.

Creating or changing SQL profiles.

 

 

QUESTION 48

Your multitenant container (CDB) containing three pluggable databases (PDBs) is running in ARCHIVELOG mode. You find that the SYSAUX tablespace is corrupted in the root container.

 

The steps to recover the tablespace are as follows:

 

1. Mount the CDB.

2. Close all the PDBs.

3. Open the database.

4. Apply the archive redo logs.

5. Restore the data file.

6. Take the SYSAUX tablespace offline.

7. Place the SYSAUX tablespace online.

8. Open all the PDBs with RESETLOGS.

9. Open the database with RESETLOGS.

10. Execute the command SHUTDOWN ABORT.

 

Which option identifies the correct sequence to recover the SYSAUX tablespace?

 

A.

6, 5, 4, 7

B.

10, 1, 2, 5, 8

C.

10, 1, 2, 5, 4, 9, 8

D.

10, 1, 5, 8, 10

 

Correct Answer: A

Explanation:

RMAN> ALTER TABLESPACE sysaux OFFLINE IMMEDIATE;

RMAN> RESTORE TABLESPACE sysaux;

RMAN> RECOVER TABLESPACE sysaux;

RMAN> ALTER TABLESPACE sysaux ONLINE;

Example:

While evaluating the 12c beta3 I was not able to do the recover while testing “all pdb files lost”.

Cannot close the pdb as the system datafile was missing…

So only option to recover was:

Shutdown cdb (10)

startup mount; (1)

restore pluggable database

recover pluggable database

alter database open;

alter pluggable database name open;

Oracle support says: You should be able to close the pdb and restore/recover the system tablespace of PDB.

Inconsistent backups are usually created by taking online database backups. You can also make an inconsistent backup by backing up data files while a database is closed, either:

Immediately after the crash of an Oracle instance (or, in an Oracle RAC configuration, all instances)

After shutting down the database using SHUTDOWN ABORT

Inconsistent backups are only useful if the database is in ARCHIVELOG mode and all archived redo logs created since the backup are available.

Open the database with the RESETLOGS option after finishing recovery:

SQL> ALTER DATABASE OPEN RESETLOGS;

 

 

QUESTION 49

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

 

clip_image004

 

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

 

Correct Answer: C

 

 

QUESTION 50

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.

 

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

 

Free VCE & PDF File for Oracle 1Z0-062 Real Exam

Instant Access to Free VCE Files: CompTIA | VMware | SAP …
Instant Access to Free PDF Files: CompTIA | VMware | SAP …

This entry was posted in 1Z0-062 Real Exam (March 2016) and tagged , , , , , , , . Bookmark the permalink.