Q1. You create a table with the PERIOD FOR clause to enable the use of the Temporal Validity feature of Oracle Database 12c.
Examine the table definition:
Which three statements are true concerning the use of the Valid Time Temporal feature for the EMPLOYEES table?
A. The valid time columns employee_time_start and employee_time_end are automatically created.
B. The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.
C. The valid time columns are not populated by the Oracle Server automatically.
D. The valid time columns are visible by default when the table is described.
E. Setting the session valid time using DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME sets the visibility for data manipulation language (DML), data definition language (DDL), and queries performed by the session.
Answer: A,B,E
Explanation: A: To implement Temporal Validity(TV), 12c offers the option to have two date columns in that table which is having TV enabled using the new clause Period For in the Create Table for the newly created tables or in the Alter Table for the existing ones. The columns that are used can be defined while creating the table itself and will be used in the Period For clause or you can skip having them in the table’s definition in the case of which, the Period For clause would be creating them internally.
E: ENABLE_AT_VALID_TIME Procedure
This procedure enables session level valid time flashback.
Q2. 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 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.
Answer: E
Explanation: 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.
Reference: Oracle Database Advanced Security Guide 12c, About Altering the Default Full Data Redaction Value
Q3. You created a new database using the "create database" statement without specifying the "ENABLE PLUGGABLE" clause.
What are two effects of not using the "ENABLE PLUGGABLE database" clause?
A. The database is created as a non-CDB and can never contain a PDB.
B. The database is treated as a PDB and must be plugged into an existing multitenant container database (CDB).
C. The database is created as a non-CDB and can never be plugged into a CDB.
D. The database is created as a non-CDB but can be plugged into an existing CDB.
E. The database is created as a non-CDB but will become a CDB whenever the first PDB is plugged in.
Answer: A,D
Explanation: A (not B,not E): The CREATE DATABASE ... ENABLE PLUGGABLE DATABASE SQL statement creates a new CDB. If you do not specify the ENABLE PLUGGABLE DATABASE clause, then the newly created database is a non-CDB and can never contain PDBs.
D: You can create a PDB by plugging in a Non-CDB as a PDB. The following graphic depicts the options for creating a PDB:
Description of cncpt358.png follows
Incorrect:
Not E: For the duration of its existence, a database is either a CDB or a non-CDB. You cannot transform a non-CDB into a CDB or vice versa. You must define a database as a CDB at creation, and then create PDBs within this CDB.
Q4. Examine the query and its output executed In an RDBMS Instance:
Which three statements are true about the users (other than sys) in the output?
A. The C # # B_ADMIN user can perform all backup and recovery operations using RMAN only.
B. The C # # C_ADMIN user can perform the data guard operation with Data Guard Broker.
C. The C # # A_ADMIN user can perform wallet operations.
D. The C # # D_ADMIN user can perform backup and recovery operations for Automatic Storage Management (ASM).
E. The C # # B_ADMIN user can perform all backup and recovery operations using RMAN or SQL* Plus.
Answer: B,D,E Explanation:
B: SYSDG administrative privilege has ability to perform Data Guard operations (including startup and shutdown) using Data Guard Broker or dgmgrl.
D: SYSASM The new (introduced in 11g) SYSASM role to manage the ASM instance, variable extent sizes to reduce shared pool usage, and the ability of an instance to read from a specific disk of a diskgroup
E (Not A): SYSDBA is like a role in the sense that it is granted, but SYSDBA is a special built-in privilege to allow the DBA full control over the database
Incorrect:
Not C: SYSKM. SYSKM administrative privilege has ability to perform transparent data encryption wallet operations.
Note:
Use the V$PWFILE_USERS view to see the users who have been granted administrative privileges.
Q5. 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.
Q6. 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.
Q7. Which three features work together, to allow a SQL statement to have different cursors for the same statement based on different selectivity ranges?
A. Bind Variable Peeking
B. SQL Plan Baselines
C. Adaptive Cursor Sharing
D. Bind variable used in a SQL statement
E. Literals in a SQL statement
Answer: A,C,E
Explanation: * In bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable when the database performs a hard parse of a statement.
When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. By peeking at bind values the optimizer can determine the selectivity of a WHERE clause condition as if literals had been used, thereby improving the plan.
C: Oracle 11g/12g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration.
Q8. Which two statements are true about the Oracle Direct Network File system (DNFS)?
A. It utilizes the OS file system cache.
B. A traditional NFS mount is not required when using Direct NFS.
C. Oracle Disk Manager can manage NFS on its own, without using the operating kernel NFS driver.
D. Direct NFS is available only in UNIX platforms.
E. Direct NFS can load-balance I/O traffic across multiple network adapters.
Answer: C,E
Explanation: E: Performance is improved by load balancing across multiple network interfaces (if available).
Note:
* To enable Direct NFS Client, you must replace the standard Oracle Disk Manager (ODM) library with one that supports Direct NFS Client.
Incorrect: Not A: Direct NFS Client is capable of performing concurrent direct I/O, which bypasses any operating system level caches and eliminates any operating system write-ordering locks Not B:
* To use Direct NFS Client, the NFS file systems must first be mounted and available over regular NFS mounts.
* Oracle Direct NFS (dNFS) is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Not D: Direct NFS is provided as part of the database kernel, and is thus available on all supported database platforms - even those that don't support NFS natively, like Windows.
Note:
* Oracle Direct NFS (dNFS) is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Direct NFS is built directly into the database kernel - just like ASM which is mainly used when using DAS or SAN storage.
* Oracle Direct NFS (dNFS) is an internal I/O layer that provides faster access to large NFS files than traditional NFS clients.
Q9. You notice that the elapsed time for an important database scheduler Job is unacceptably long.
The job belongs to a scheduler job class and window.
Which two actions would reduce the job's elapsed time?
A. Increasing the priority of the job class to which the job belongs
B. Increasing the job's relative priority within the Job class to which it belongs
C. Increasing the resource allocation for the consumer group mapped to the scheduler job's job class within the plan mapped to the scheduler window
D. Moving the job to an existing higher priority scheduler window with the same schedule and duration
E. Increasing the value of the JOB_QUEUE_PROCESSES parameter
F. Increasing the priority of the scheduler window to which the job belongs
Answer: B,C
Explanation: B: Job priorities are used only to prioritize among jobs in the same class.
Note: Group jobs for prioritization Within the same job class, you can assign priority values of 1-5 to individual jobs so that if two jobs in the class are scheduled to start at the same time, the one with the higher priority takes precedence. This ensures that you do not have a less important job preventing the timely completion of a more important one.
C: Set resource allocation for member jobs Job classes provide the link between the Database Resource Manager and the Scheduler, because each job class can specify a resource consumer group as an attribute. Member jobs then belong to the specified consumer group and are assigned resources according to settings in the current resource plan.
Q10. You upgraded your database from pre-12c to a multitenant container database (CDB) containing pluggable databases (PDBs).
Examine the query and its output:
Which two tasks must you perform to add users with SYSBACKUP, SYSDG, and SYSKM privilege to the password file?
A. Assign the appropriate operating system groups to SYSBACKUP, SYSDG, SYSKM.
B. Grant SYSBACKUP, SYSDG, and SYSKM privileges to the intended users.
C. Re-create the password file with SYSBACKUP, SYSDG, and SYSKM privilege and the FORCE argument set to No.
D. Re-create the password file with SYSBACKUP, SYSDG, and SYSKM privilege, and FORCE arguments set to Yes.
E. Re-create the password file in the Oracle Database 12c format.
Answer: B,D
Explanation:
* orapwd
/ You can create a database password file using the password file creation utility,
ORAPWD.
The syntax of the ORAPWD command is as follows:
orapwd FILE=filename [ENTRIES=numusers] [FORCE={y|n}] [ASM={y|n}]
[DBUNIQUENAME=dbname] [FORMAT={12|legacy}] [SYSBACKUP={y|n}] [SYSDG={y|n}]
[SYSKM={y|n}] [DELETE={y|n}] [INPUT_FILE=input-fname]
force - whether to overwrite existing file (optional),
* v$PWFILE_users / 12c: V$PWFILE_USERS lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM privileges.
/ 10c: sts users who have been granted SYSDBA and SYSOPER privileges as derived from the password file.
ColumnDatatypeDescription USERNAMEVARCHAR2(30)The name of the user that is contained in the password file SYSDBAVARCHAR2(5)If TRUE, the user can connect with SYSDBA privileges SYSOPERVARCHAR2(5)If TRUE, the user can connect with SYSOPER privileges
Incorrect:
not E: The format of the v$PWFILE_users file is already in 12c format.