Oracle Oracle Database 1Z0-060 Questions & Answers

• Question 181:

  Examine the following command:

  

  Which three statements are true about using an invisible column in the PRODUCTS table?

  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.

  Correct Answer: ABE

  AB: You can make individual table columns invisible. Any generic access of a table does not show the invisible columns in the table. For example, the following operations do not display invisible columns in the output:

  *

  SELECT * FROM statements in SQL

  *

  DESCRIBE commands in SQL*Plus

  *

  %ROWTYPE attribute declarations in PL/SQL

  *

  Describes in Oracle Call Interface (OCI)

  Incorrect Answers:

  D: You can make invisible columns visible.

  You can make a column invisible during table creation or when you add a column to a table, and you can

  later alter the table to make the same column visible.

• Question 182:

  You wish to enable an audit policy for all database users, except SYS, SYSTEM, and SCOTT.

  You issue the following statements:

  SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SYS; SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SYSTEM; SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SCOTT;

  For which database users is the audit policy now active?

  A. All users except SYS

  B. All users except SCOTT

  C. All users except SYS and SCOTT

  D. All users except SYS, SYSTEM, and SCOTT

  Correct Answer: B

  If you run multiple AUDIT statements on the same unified audit policy but specify different EXCEPT users, then Oracle Database uses the last exception user list, not any of the users from the preceding lists. This means the effect of the earlier AUDIT POLICY ... EXCEPT statements are overridden by the latest AUDIT POLICY ... EXCEPT statement.

  Note:

  *

  The ORA_DATABASE_PARAMETER policy audits commonly used Oracle Database parameter settings. By default, this policy is not enabled.

  *

  You can use the keyword ALL to audit all actions. The following example shows how to audit all actions on the HR.EMPLOYEES table, except actions by user pmulligan.

  Example Auditing All Actions on a Table

  CREATE AUDIT POLICY all_actions_on_hr_emp_pol ACTIONS ALL ON HR.EMPLOYEES;

  AUDIT POLICY all_actions_on_hr_emp_pol EXCEPT pmulligan;

• Question 183:

  On your Oracle 12c database, you invoked SQL*Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command:

  $> sqlldr hr/hr@pdb table=employees

  Which two statements are true regarding the command? (Choose two.)

  A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.

  B. It fails because no SQL *Loader data file location is specified.

  C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.

  D. It fails because no SQL *Loader control file location is specified.

  Correct Answer: AC

  Note:

  * SQL*Loader is invoked when you specify the sqlldr command and, optionally, parameters that establish session characteristics.

• Question 184:

  After implementing full Oracle Data Redaction, you change the default value for the NUMBER data type as follows:

  

  After changing the value, you notice that FULL redaction continues to redact numeric data with a zero.

  What must you do to activate the new default value for numeric full redaction?

  A. Re-enable redaction policies that use FULL data redaction.

  B. Re-create redaction policies that use FULL data redaction.

  C. Re-connect the sessions that access objects with redaction policies defined on them.

  D. Flush the shared pool.

  E. Restart the database instance.

  Correct Answer: E

  About Altering the Default Full Data Redaction Value You can alter the default displayed values for full Data Redaction polices. By default, 0 is the redacted value when Oracle Database performs full redaction (DBMS_REDACT.FULL) on a column of the NUMBER data type. If you want to change it to another value (for example, 7), then you can run the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to modify this value. The modification applies to all of the Data Redaction policies in the current database instance. After you modify a value, you must restart the database for it to take effect.

  Note:

  *

  The DBMS_REDACT package provides an interface to Oracle Data Redaction, which enables you to mask (redact) data that is returned from queries issued by low-privileged users or an application.

  *

  UPDATE_FULL_REDACTION_VALUES Procedure

  This procedure modifies the default displayed values for a Data Redaction policy for full redaction.

  *

  After you create the Data Redaction policy, it is automatically enabled and ready to redact data.

  *

  Oracle Data Redaction enables you to mask (redact) data that is returned from queries issued by low-privileged users or applications. You can redact column data by using one of the following methods:

  / Full redaction. / Partial redaction. / Regular expressions. / Random redaction. / No redaction.

• Question 185:

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

  

  Which three are prerequisites for successful execution of the command? (Choose three.)

  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_EXP_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

  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 186:

  Which two are true concerning a multitenant container database with three pluggable database?

  A. All administration tasks must be done to a specific pluggable database.

  B. The pluggable databases increase patching time.

  C. The pluggable databases reduce administration effort.

  D. The pluggable databases are patched together.

  E. Pluggable databases are only used for database consolidation.

  Correct Answer: CE

  The benefits of Oracle Multitenant are brought by implementing a pure deployment choice. The following list calls out the most compelling examples.

  *

  High consolidation density. (E)

  The many pluggable databases in a single multitenant container database share its memory and

  background processes, letting you operate many more pluggable databases on a particular platform than

  you can single databases that use the old architecture. This is the same benefit that schema-based

  consolidation brings.

  *

  Rapid provisioning and cloning using SQL.

  *

  New paradigms for rapid patching and upgrades. (D, not B)

  The investment of time and effort to patch one multitenant container database results in patching all of its

  many pluggable databases. To patch a single pluggable database, you simply unplug/plug to a multitenant

  container database at a different Oracle Database software version.

  *

  (C, not A) Manage many databases as one.

  By consolidating existing databases as pluggable databases, administrators can manage many databases

  as one. For example, tasks like backup and disaster recovery are performed at the multitenant container

  database level.

  *

  Dynamic between pluggable database resource management. In Oracle Database 12c, Resource

  Manager is extended with specific functionality to control the competition for resources between the

  pluggable databases within a multitenant container database.

  Note:

  *

  Oracle Multitenant is a new option for Oracle Database 12c Enterprise Edition that helps customers

  reduce IT costs by simplifying consolidation, provisioning, upgrades, and more. It is supported by a new

  architecture that allows a multitenant container database to hold many pluggable databases. And it fully

  complements other options, including Oracle Real Application Clusters and Oracle Active Data Guard. An

  existing database can be simply adopted, with no change, as a pluggable database; and no changes are

  needed in the other tiers of the application.

• Question 187:

  Examine the current values 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: B

• Question 188:

  Which three statements are true concerning unplugging a pluggable database (PDB)?

  A. The PDB must be open in read only mode.

  B. The PDB must be closed.

  C. The unplugged PDB becomes a non-CDB.

  D. The unplugged PDB can be plugged into the same multitenant container database (CDB)

  E. The unplugged PDB can be plugged into another CDB.

  F. The PDB data files are automatically removed from disk.

  Correct Answer: BDE

  D: An unplugged PDB contains data dictionary tables, and some of the columns in these encode information in an endianness-sensitive way. There is no supported way to handle the conversion of such columns automatically. This means, quite simply, that an unplugged PDB cannot be moved across an endianness difference.

  E (not F): To exploit the new unplug/plug paradigm for patching the Oracle version most effectively, the source and destination CDBs should share a filesystem so that the PDB's datafiles can remain in place. The PDB must be closed before it can be unplugged. When you unplug a PDB from a CDB, the unplugged PDB is in mounted mode. The unplug operation makes some changes in the PDB's data files to record, for example, that the PDB was successfully unplugged. Because it is still part of the CDB, the unplugged PDB is included in an RMAN backup of the entire CDB. Such a backup provides a convenient way to archive the unplugged PDB in case it is needed in the future.

• Question 189:

  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

  Correct Answer: D

  *

  Automatic segment space management (ASSM) is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the pctused,freelists, and freelist groups storage parameters for schema objects created in the tablespace. If any of these attributes are specified, they are ignored.

  *

  Oracle introduced Automatic Segment Storage Management (ASSM) as a replacement for traditional freelists management which used one-way linked-lists to manage free blocks with tables and indexes. ASSM is commonly called "bitmap freelists" because that is how Oracle implement the internal data structures for free block management.

  Note:

  *

  Buffer busy waits are most commonly associated with segment header contention onside the data buffer pool (db_cache_size, etc.).

  *

  The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists (or ASSM), and adding missing indexes.

• Question 190:

  Examine this command:

  SQL > exec DBMS_STATS.SET_TABLE_PREFS ('SH', 'CUSTOMERS', 'PUBLISH', 'false');

  Which three statements are true about the effect of this command? (Choose three.)

  A. Statistics collection is not done for the CUSTOMERS table when schema stats are gathered.

  B. Statistics collection is not done for the CUSTOMERS table when database stats are gathered.

  C. Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.

  D. Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics.

  E. Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.

  Correct Answer: CDE

  *

  SET_TABLE_PREFS Procedure

  This procedure is used to set the statistics preferences of the specified table in the specified schema.

  *

  Example: Using Pending Statistics

  Assume many modifications have been made to the employees table since the last time statistics were gathered. To ensure that the cost-based optimizer is still picking the best plan, statistics should be gathered once again; however, the user is concerned that new statistics will cause the optimizer to choose bad plans when the current ones are acceptable. The user can do the following:

  EXEC DBMS_STATS.SET_TABLE_PREFS('hr', 'employees', 'PUBLISH', 'false');

  By setting the employees tables publish preference to FALSE, any statistics gather from now on will not be automatically published. The newly gathered statistics will be marked as pending.