Ensurepass

QUESTION 101

Examine the parameters for your database instance:

 

 

Which three statements are true about the process of automatic optimization by using cardinality feedback?

 

A.	The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
B.	The optimizer can re optimize a query only once using cardinality feedback.
C.	The optimizer enables monitoring for cardinality feedback after the first execution of a query.
D.	The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.
E.	After the optimizer identifies a query as a re-optimization candidate, statistics collected by the collectors are submitted to the optimizer.

 

Correct Answer: ACD

Explanation:

C: During the first execution of a SQL statement, an execution plan is generated as usual.

D: if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on cardinality feedback.

(not B) Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve plans for repeated executions.

optimizer_dynamic_sampling

optimizer_features_enable

dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates.

Note:

OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer.

Range of values. 0 to 10

Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason for the misestimate, cardinality feedback may be able to help.

 

 

 

 

 

 

 

 

 

QUESTION 102

You execute the following piece of code with appropriate privileges:

 

 

User SCOTT has been granted the CREATE SESSION privilege and the MGR role.

 

Which two statements are true when a session logged in as SCOTT queries the SAL column in the view and the table?

 

A.	Data is redacted for the EMP.SAL column only if the SCOTT session does not have the MGR role set.
B.	Data is redacted for EMP.SAL column only if the SCOTT session has the MGR role set.
C.	Data is never redacted for the EMP_V.SAL column.
D.	Data is redacted for the EMP_V.SAL column only if the SCOTT session has the MGR role set.
E.	Data is redacted for the EMP_V.SAL column only if the SCOTT session does not have the MGR role set.

 

Correct Answer: AC

Explanation:

Note:

DBMS_REDACT.FULL completely redacts the column data.

DBMS_REDACT.NONE applies no redaction on the column data. Use this function for development testing purposes. LOB columns are not supported.

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.

If you create a view chain (that is, a view based on another view), then the Data Redaction policy also applies throughout this view chain. The policies remain in effect all of the way up through this view chain, but if another policy is created for one of these views, then for the columns affected in the subsequent views, this new policy takes precedence.

 

 

 

 

 

 

 

 

 

 

QUESTION 103

The hr user receiver, the following error while inserting data into the sales table:

 

ERROR at line 1:

ORA-01653; unable to extend table HR.SALES by 128 in tablespace USERS

 

On investigation, you find that the users tablespace uses Automnrif Segment Space Management (ASSM). It is the default tablespace for the HR user with an unlimited quota on it.

 

Which two methods would you use to resolve this error?

 

A.	Altering the data life associated with the USERS tablespace to ex automatically
B.	Adding a data life to the USERS tablespace
C.	Changing segment space management for the USERS tablespace to manual
D.	Creating a new tablespace with autoextend enabled and changing the default tablespace of the HR user to the new tablespace
E.	Enabling resumable space allocation by setting the RESUMABLE_TIMEOUT parameter to a nonzero value

 

Correct Answer: AD

 

 

QUESTION 104

In your database, you want to ensure that idle sessions that are blocking active are automatically terminated after a specified period of time.How would you accomplish this?

 

A.	Setting a metric threshold
B.	Implementing Database Resource Manager
C.	Enabling resumable timeout for user sessions
D.	Decreasing the value of the IDLE_TIME resource limit in the default profile

 

Correct Answer: D

Explanation:

An Oracle session is sniped when you set the idle_time parameter to disconnect inactive sessions. (It’s only like sniping on ebay in that a time is set for an action to occur.)

Oracle has several ways to disconnect inactive or idle sessions, both from within SQL*Plus via resources profiles (connect_time, idle_time), and with the SQL*net expire time parameter. Here are two ways to disconnect an idle session:

Set the idle_time parameter in the user profile

Set the sqlnet.ora parameter expire_time

 

 

QUESTION 105

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.

Correct Answer: AD

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:

 

 

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.

 

 

QUESTION 106

Your database is open and the LISTENER listener running. You stopped the wrong listener LISTENER by issuing the following command:

 

1snrctl > STOP

 

What happens to the sessions that are presently connected to the database Instance?

 

A.	They are able to perform only queries.
B.	They are not affected and continue to function normally.
C.	They are terminated and the active transactions are rolled back.
D.	They are not allowed to perform any operations until the listener LISTENER is started.

 

Correct Answer: B

Explanation:

The listener is used when the connection is established. The immediate impact of stopping the listener will be that no new session can be established from a remote host. Existing sessions are not compromised.

 

 

 

 

 

 

QUESTION 107

In which two scenarios do you use SQL* Loader to load data?

 

A.	Transform the data while it is being loaded into the database.
B.	Use transparent parallel processing without having to split the external data first.
C.	Load data into multiple tables during the same load statement.
D.	Generate unique sequential key values in specified columns.

 

Correct Answer: AD

Explanation:

You can use SQL*Loader to do the following:

(A) Manipulate the data before loading it, using SQL functions.

(D) Generate unique sequential key values in specified columns.

etc:

Load data into multiple tables during the same load session.

Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.

Load data from multiple datafiles during the same load session.

Specify the character set of the data.

Selectively load data (you can load records based on the records’ values).

Use the operating system’s file system to access the datafiles.

Load data from disk, tape, or named pipe.

Generate sophisticated error reports, which greatly aid troubleshooting.

Load arbitrarily complex object-relational data.

Use secondary datafiles for loading LOBs and collections.

Use either conventional or direct path loading. While conventional path loading is very flexible, direct path loading provides superior loading performance.

Note:

SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile.

 

 

QUESTION 108

Which three statements are true about a job chain?

 

A.	It can contain a nested chain of jobs.
B.	It can be used to implement dependency-based scheduling.
C.	It cannot invoke the same program or nested chain in multiple steps in the chain.
D.	It cannot have more than one dependency.
E.	It can be executed using event-based or time-based schedules.

 

Correct Answer: ABE

Explanaton:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse009.htm#ADMIN12459

 

 

QUESTION 109

You are administering a database and you receive a requirement to apply the following restrictions:

 

1. A connection must be terminated after four unsuccessful login attempts by user.

2. A user should not be able to create more than four simultaneous sessions.

3. User session must be terminated after 15 minutes of inactivity.

4. Users must be prompted to change their passwords every 15 days.

How would you accomplish these requirements?

 

A.	by granting a secure application role to the users
B.	by creating and assigning a profile to the users and setting the REMOTE_OS_AUTHENT parameter to FALSE
C.	By creating and assigning a profile to the users and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameter to 4
D.	By Implementing Fine-Grained Auditing (FGA) and setting the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE.

 

Correct Answer: D

Explanation:

You can design your applications to automatically grant a role to the user who is trying to log in, provided the user meets criteria that you specify. To do so, you create a secure application role, which is a role that is associated with a PL/SQL procedure (or PL/SQL package that contains multiple procedures). The procedure validates the user:

if the user fails the validation, then the user cannot log in. If the user passes the validation, then the procedure grants the user a role so that he or she can use the application. The user has this role only as long as he or she is logged in to the application. When the user logs out, the role is revoked.

Incorrect:

Not B: REMOTE_OS_AUTHENT specifies whether remote clients will be authenticated with the value of the OS_AUTHENT_PREFIX parameter.

Not C, not E: SEC_MAX_FAILED_LOGIN_ATTEMPTS specifies the number of authentication attempts that can be made by a client on a connection to the server process. After the specified number of failure attempts, the connection will be automatically dropped by the server process.

Not D: REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file.

Values:

shared

One or more databases can use the password file. The password file can contain SYS as well as non-SYS users.

exclusive

The password file can be used by only one database. The password file can contain SYS as well as non-SYS users.

none

Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.

Note:

The REMOTE_OS_AUTHENT parameter is deprecated. It is retained for backward compatibility only.

 

 

QUESTION 110

Which three statements are true when the listener handles connection requests to an Oracle 12c database instance with multithreaded architecture enabled In UNIX?

 

A.	Thread creation must be routed through a dispatcher process
B.	The local listener may spawn a now process and have that new process create a thread
C.	Each Oracle process runs an SCMN thread.
D.	Each multithreaded Oracle process has an SCMN thread.
E.	The local listener may pass the request to an existing process which in turn will create a thread.

 

Correct Answer: ADE

 

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 …