Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Your multitenant container database (CDB) contains some pluggable databases (PDBs), you execute the following command in the root container:
Which two statements are true?
A. Schema objects owned by the C# # A_ADMIN common user can be shared across all PDBs.
B. The C # # A_ADMIN user will be able to use the TEMP_TS temporary tablespace only in root.
C. The command will, create a common user whose description is contained in the root and each PDB.
D. The schema for the common user C # # A_ADMIN can be different in each container.
E. The command will create a user in the root container only because the container clause is not used.
Answer: C,D
Q2. What happens if a maintenance window closes before a job that collects optimizer statistics completes?
A. The job is terminated and the gathered statistics are not saved.
B. The job is terminated but the gathered statistics are not published.
C. The job continues to run until all statistics are gathered.
D. The job is terminated and statistics for the remaining objects are collected the next time the maintenance window opens.
Answer: D
Q3. You upgraded from a previous Oracle database version to Oracle Database version to Oracle Database 12c. Your database supports a mixed workload. During the day, lots of insert, update, and delete operations are performed. At night, Extract, Transform, Load (ETL) and batch reporting jobs are run. The ETL jobs perform certain database operations using two or more concurrent sessions.
After the upgrade, you notice that the performance of ETL jobs has degraded. To ascertain the cause of performance degradation, you want to collect basic statistics such as the level of parallelism, total database time, and the number of I/O requests for the ETL jobs.
How do you accomplish this?
A. Examine the Active Session History (ASH) reports for the time period of the ETL or batch reporting runs.
B. Enable SQL tracing for the queries in the ETL and batch reporting queries and gather diagnostic data from the trace file.
C. Enable real-time SQL monitoring for ETL jobs and gather diagnostic data from the V$SQL_MONITOR view.
D. Enable real-time database operation monitoring using the DBMS_SQL_MONITOR.BEGIN_OPERATION function, and then use the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the required information.
Answer: D
Explanation: * Monitoring database operations Real-Time Database Operations Monitoring enables you to monitor long running database tasks such as batch jobs, scheduler jobs, and Extraction, Transformation, and Loading (ETL) jobs as a composite business operation. This feature tracks the progress of SQL and PL/SQL queries associated with the business operation being monitored. As a DBA or developer, you can define business operations for monitoring by explicitly specifying the start and end of the operation or implicitly with tags that identify the operation.
Q4. Which two are prerequisites for performing a flashback transaction?
A. Flashback Database must be enabled.
B. Undo retention guarantee for the database must be configured.
C. EXECUTE privilege on the DBMS_FLASHBACK package must be granted to the user flashing back transaction.
D. Supplemental logging must be enabled.
E. Recycle bin must be enabled for the database.
F. Block change tracking must be enabled tor the database.
Answer: A,C
Reference: Oracle Database Advanced Application Developer's Guide 11g, Using Oracle Flashback Technology
Q5. A database is stored in an Automatic Storage Management (ASM) disk group, disk group, DGROUP1 with SQL:
There is enough free space in the disk group for mirroring to be done.
What happens if the CONTROLLER1 failure group becomes unavailable due to error of for maintenance?
A. Transactions and queries accessing database objects contained in any tablespace stored in DGROUP1 will fall.
B. Mirroring of allocation units will be done to ASM disks in the CONTROLLER2 failure group until the CONTROLLER1 for failure group is brought back online.
C. The data in the CONTROLLER1 failure group is copied to the controller2 failure group and rebalancing is initiated.
D. ASM does not mirror any data until the controller failure group is brought back online, and newly allocated primary allocation units (AU) are stored in the controller2 failure group, without mirroring.
E. Transactions accessing database objects contained in any tablespace stored in DGROUP1 will fail but queries will succeed.
Answer: B
Explanation: CREATE DISKGROUP NORMAL REDUNDANCY
* For Oracle ASM to mirror files, specify the redundancy level as NORMAL REDUNDANCY (2-way mirroring by default for most file types) or HIGH REDUNDANCY (3-way mirroring for all files).
Q6. You execute the following PL/SQL:
Which two statements are true?
A. Fine-Grained Auditing (FGA) is enabled for the PRICE column in the PRODUCTS table for SELECT statements only when a row with PRICE > 10000 is accessed.
B. FGA is enabled for the PRODUCTS.PRICE column and an audit record is written whenever a row with PRICE > 10000 is accessed.
C. FGA is enabled for all DML operations by JIM on the PRODUCTS.PRICE column.
D. FGA is enabled for the PRICE column of the PRODUCTS table and the SQL statements is captured in the FGA audit trial.
Answer: A,B
Explanation: DBMS_FGA.add_policy
* The DBMS_FGA package provides fine-grained security functions.
* ADD_POLICY Procedure
This procedure creates an audit policy using the supplied predicate as the audit condition.
Incorrect:
Not C: object_schema
The schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)
Q7. Which three activities are supported by the Data Recovery Advisor?
A. Advising on block checksum failures
B. Advising on inaccessible control files
C. Advising on inaccessible block change tracking files
D. Advising on empty password files
E. Advising on invalid block header field values
Answer: A,B,E
Explanation: * Data Recovery Advisor can diagnose failures such as the following:
/ (B) Components such as datafiles and control files that are not accessible because they do not exist, do not have the correct access permissions, have been taken offline, and so on
/ (A, E) Physical corruptions such as block checksum failures and invalid block header field values
/ Inconsistencies such as a datafile that is older than other database files / I/O failures such as hardware errors, operating system driver failures, and exceeding operating system resource limits (for example, the number of open files)
* The Data Recovery Advisor automatically diagnoses corruption or loss of persistent data on disk, determines the appropriate repair options, and executes repairs at the user's request. This reduces the complexity of recovery process, thereby reducing the Mean Time To Recover (MTTR).
Q8. Which three statements are true about adaptive SQL plan management?
A. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
B. The optimizer always uses the fixed plan, if the fixed plan exists in the plan baseline.
C. It adds new, bettor plans automatically as fixed plans to the baseline.
D. The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.
E. The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.
Answer: A,D,E
Explanation: With adaptive SQL plan management, DBAs no longer have to manually run the verification or evolve process for non-accepted plans. When automatic SQL tuning is in COMPREHENSIVE mode, it runs a verification or evolve process for all SQL statements that have non-accepted plans during the nightly maintenance window. If the non-accepted plan performs better than the existing accepted plan (or plans) in the SQL plan baseline, then the plan is automatically accepted and becomes usable by the optimizer. After the verification is complete, a persistent report is generated detailing how the non-accepted plan performs compared to the accepted plan performance. Because the evolve process is now an AUTOTASK, DBAs can also schedule their own evolve job at end time.
Note:
* The optimizer is able to adapt plans on the fly by predetermining multiple subplans for portions of the plan.
* Adaptive plans, introduced in Oracle Database 12c, enable the optimizer to defer the final plan decision for a statement until execution time. The optimizer instruments its chosen plan (the default plan) with statistics collectors so that it can detect at runtime, if its cardinality estimates differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it will be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement.
Reference: SQL Plan Management with Oracle Database 12c
Q9. In order to exploit some new storage tiers that have been provisioned by a storage administrator, the partitions of a large heap table must be moved to other tablespaces in your Oracle 12c database?
Both local and global partitioned B-tree Indexes are defined on the table.
A high volume of transactions access the table during the day and a medium volume of transactions access it at night and during weekends.
Minimal disrupt ion to availability is required.
Which three statements are true about this requirement?
A. The partitions can be moved online to new tablespaces.
B. Global indexes must be rebuilt manually after moving the partitions.
C. The partitions can be compressed in the same tablespaces.
D. The partitions can be compressed in the new tablespaces.
E. Local indexes must be rebuilt manually after moving the partitions.
Answer: A,C,D
Explanation: A: You can create and rebuild indexes online. Therefore, you can update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.
D: Moving (Rebuilding) Index-Organized Tables Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.
C: If a table can be compressed in the new tablespace, also it can be compressed in the same tablespace.
Incorrect:
Not B, not E: Local and Global indexes can be automatically rebuild with UPDATE
INDEXES when you move the table.
Q10. You execute the following commands to audit database activities:
SQL > ALTER SYSTEM SET AUDIT_TRIAL=DB, EXTENDED SCOPE=SPFILE;
SQL > AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY JOHN By SESSION WHENEVER SUCCESSFUL;
Which statement is true about the audit record that generated when auditing after instance restarts?
A. One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command on a table, and contains the SQL text for the SQL Statements.
B. One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command, and contains the execution plan for the SQL statements.
C. One audit record is created for the whole session if john successfully executes a SELECT, INSERT, or DELETE command, and contains the execution plan for the SQL statements.
D. One audit record is created for the whole session if JOHN successfully executes a select command, and contains the SQL text and bind variables used.
E. One audit record is created for the whole session if john successfully executes a SELECT, INSERT, or DELETE command on a table, and contains the execution plan, SQL text, and bind variables used.
Answer: A
Explanation:
Note:
* BY SESSION
In earlier releases, BY SESSION caused the database to write a single record for all SQL statements or operations of the same type executed on the same schema objects in the same session. Beginning with this release (11g) of Oracle Database, both BY SESSION and BY ACCESS cause Oracle Database to write one audit record for each audited statement and operation.
* BY ACCESS
Specify BY ACCESS if you want Oracle Database to write one record for each audited statement and operation.
Note:
If you specify either a SQL statement shortcut or a system privilege that audits a data definition language (DDL) statement, then the database always audits by access. In all other cases, the database honors the BY SESSION or BY ACCESS specification.
* For each audited operation, Oracle Database produces an audit record containing this information: / The user performing the operation / The type of operation / The object involved in the operation / The date and time of the operation
Reference: Oracle Database SQL Language Reference 12c
Q11. Identify two correct statements about multitenant architectures.
A. Multitenant architecture can be deployed only in a Real Application Clusters (RAC) configuration.
B. Multiple pluggable databases (PDBs) share certain multitenant container database (CDB) resources.
C. Multiple CDBs share certain PDB resources.
D. Multiple non-RAC CDB instances can mount the same PDB as long as they are on the same server.
E. Patches are always applied at the CDB level.
F. A PDB can have a private undo tablespace.
Answer: B,E
Explanation: B: Using 12c Resource manager you will be able control CPU, Exadata I/O, sessions and parallel servers. A new 12c CDB Resource Manager Plan will use so-called “Shares” (resource allocations) to specify how CPU is distributed between PDBs. A CDB Resource Manager Plan also can use “utilization limits” to limit the CPU usage for a PDB. With a default directive, you do not need to modify the resource plan for each PDB plug and unplug.
E: New paradigms for rapid patching and upgrades.
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.
Incorrect: Not A:
* The Oracle RAC documentation describes special considerations for a CDB in an Oracle RAC environment.
* 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 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. Not D: You can unplug a PDB from one CDB and plug it into a different CDB without altering your schemas or applications. A PDB can be plugged into only one CDB at a time.
not F:
* UNDO tablespace can NOT be local and stays on the CDB level.
* Redo and undo go hand in hand, and so the CDB as a whole has a single undo tablespace per RAC instance.
Q12. 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
Answer: B
Explanation: 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;
Reference: Oracle Database Security Guide 12c, About Enabling Unified Audit Policies
Q13. Examine the parameter for your database instance:
You generated the execution plan for the following query in the plan table and noticed that the nested loop join was done. After actual execution of the query, you notice that the hash join was done in the execution plan:
Identify the reason why the optimizer chose different execution plans.
A. The optimizer used a dynamic plan for the query.
B. The optimizer chose different plans because automatic dynamic sampling was enabled.
C. The optimizer used re-optimization cardinality feedback for the query.
D. The optimizer chose different plan because extended statistics were created for the columns used.
Answer: B
Explanation: * optimizer_dynamic_sampling OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics. Range of values0 to 11
Q14. A redaction policy was added to the SAL column of the SCOTT.EMP table:
All users have their default set of system privileges.
For which three situations will data not be redacted?
A. SYS sessions, regardless of the roles that are set in the session
B. SYSTEM sessions, regardless of the roles that are set in the session
C. SCOTT sessions, only if the MGR role is set in the session
D. SCOTT sessions, only if the MGR role is granted to SCOTT
E. SCOTT sessions, because he is the owner of the table
F. SYSTEM session, only if the MGR role is set in the session
Answer: A,D,F
Explanation:
* SYS_CONTEXT This is a twist on the SYS_CONTEXT function as it does not use USERENV. With this usage SYS_CONTEXT queries the list of the user's current default roles and returns TRUE if the role is granted.
Example:
SYS_CONTEXT('SYS_SESSION_ROLES', 'SUPERVISOR')
conn scott/tiger@pdborcl
SELECT sys_context('SYS_SESSION_ROLES', 'RESOURCE')
FROM dual;
SYS_CONTEXT('SYS_SESSION_ROLES','SUPERVISOR')
FALSE
conn sys@pdborcl as sysdba
GRANT resource TO scott;
conn scott/tiger@pdborcl SELECT sys_context('SYS_SESSION_ROLES', 'RESOURCE') FROM dual; SYS_CONTEXT('SYS_SESSION_ROLES','SUPERVISOR') TRUE
Q15. An application accesses a small lookup table frequently. You notice that the required data blocks are getting aged out of the default buffer cache.
How would you guarantee that the blocks for the table never age out?
A. Configure the KEEP buffer pool and alter the table with the corresponding storage clause.
B. Increase the database buffer cache size.
C. Configure the RECYCLE buffer pool and alter the table with the corresponding storage clause.
D. Configure Automata Shared Memory Management.
E. Configure Automatic Memory Management-
Answer: A
Explanation: Schema objects are referenced with varying usage patterns; therefore, their cache behavior may be quite different. Multiple buffer pools enable you to address these differences. You can use a KEEP buffer pool to maintain objects in the buffer cache and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. When an object is allocated to a cache, all blocks from that object are placed in that cache. Oracle maintains a DEFAULT buffer pool for objects that have not been assigned to one of the buffer pools.
Q16. A warehouse fact table in your Oracle 12c Database is range-partitioned by month and accessed frequently with queries that span multiple partitions
The table has a local prefixed, range partitioned index.
Some of these queries access very few rows in some partitions and all the rows in other partitions, but these queries still perform a full scan for all accessed partitions.
This commonly occurs when the range of dates begins at the end of a month or ends close to the start of a month.
You want an execution plan to be generated that uses indexed access when only a few rows are accessed from a segment, while still allowing full scans for segments where many rows are returned.
Which three methods could transparently help to achieve this result?
A. Using a partial local Index on the warehouse fact table month column with indexing disabled to the table partitions that return most of their rows to the queries.
B. Using a partial local Index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.
C. Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, which retains the existing local partitioned column.
D. Converting the partitioned table to a partitioned view that does a UNION ALL query on the monthly tables, which retains the existing local partitioned column.
E. Using a partial global index on the warehouse fact table month column with indexing disabling for the table partitions that return most of their rows to the queries.
F. Using a partial global index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.
Answer: B,C,E
Explanation:
Note:
* Oracle 12c now provides the ability to index a subset of partitions and to exclude the others.
Local and global indexes can now be created on a subset of the partitions of a table. Partial Global indexes provide more flexibility in index creation for partitioned tables. For example, index segments can be omitted for the most recent partitions to ensure maximum data ingest rates without impacting the overall data model and access for the partitioned object.
Partial Global Indexes save space and improve performance during loads and queries. This feature supports global indexes that include or index a certain subset of table partitions or subpartitions, and exclude the others. This operation is supported using a default table indexing property. When a table is created or altered, a default indexing property can be specified for the table or its partitions.