Oracle Database

1z0-061 the Most Up to Date VCE And PDF Instant Download

Tens of thousands of competitors, pages of hard questions and unsatisfied exam preparation situations… Do not worried about all those annoying things! We, help you with your Oracle Database May 15,2022 Newest 1z0-061 QAs Oracle Database 12c: SQL Fundamentals exam. We will assist you clear the Hotest 1z0-061 free download exam with Oracle Database 1z0-061 real exam questions. We 1z0-061 dumps are the most comprehensive ones.

We Geekcert has our own expert team. They selected and published the latest 1z0-061 preparation materials from Official Exam-Center.

The following are the 1z0-061 free dumps. Go through and check the validity and accuracy of our 1z0-061 dumps.1z0-061 free dumps are questions from the latest full 1z0-061 dumps. Check 1z0-061 free questions to get a better understanding of 1z0-061 exams.

Question 1:

View the Exhibits and examine the structures of the products, sales, and customers tables.

You need to generate a report that gives details of the customer\’s last name, name of the product, and the quantity sold for a customers in \’Tokyo\’. Which two queries give the required result?

A. Option A

B. Option B

C. Option C

D. Option D

Correct Answer: AC


Question 2:

View the Exhibit and examine the structure of the CUSTOMERS table.

You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task?

A. Option A

B. Option B

C. Option C

D. Option D

Correct Answer: A


Question 3:

Which two statements are true regarding subqueries?

A. A subquery can retrieve zero or more rows.

B. Only two subqueries can be placed at one level.

C. A subquery can be used only in SQL query statements.

D. A subquery can appear on either side of a comparison operator.

E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Correct Answer: AD

Using a Subquery to Solve a Problem

Suppose you want to write a query to find out who earns a salary greater than Abel\’s salary.

To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the other query.

The inner query (or subquery) returns a value that is used by the outer query (or main query).

Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query.

Subquery Syntax

A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a

table with a condition that depends on the data in the table itself.

You can place the subquery in a number of SQL clauses, including the following:

WHERE clause

HAVING clause

FROM clause

In the syntax:

operator includes a comparison condition such as >, =, or IN Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, , <=) and multiple-row operators (IN, ANY, ALL, EXISTS). The subquery is often referred to

as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query.

Guidelines for Using Subqueries

Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row

subqueries and multiple- row operators with multiple-row subqueries.

Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.


Question 4:

View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.

Evaluate the following two queries:

SQL>SELECT DISTINCT promo_category to_char(promo_cost)”code” FROM promotions

ORDER BY code;

SQL>SELECT DISTINCT promo_category promo_cost “code”

FROM promotions ORDER BY 1;

Which statement is true regarding the execution of the above queries?

A. Only the first query executes successfully.

B. Only the second query executes successfully.

C. Both queries execute successfully but give different results.

D. Both queries execute successfully and give the same result.

Correct Answer: B


Question 5:

Examine the structure proposed for the transactions table:

Which two statements are true regarding the creation and storage of data in the above table structure?

A. The CUST_STATUS column would give an error.

B. The TRANS_VALIDITY column would give an error.

C. The CUST_STATUS column would store exactly one character.

D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.

E. The TRANS_VALIDITY column would have a maximum size of one character.

F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds

Correct Answer: BC

VARCHAR2(size)Variable-length character data (A maximum size must be specified:

minimum size is 1; maximum size is 4, 000.)

CHAR [(size)] Fixed-length character data of length size bytes (Default and minimum size is 1; maximum size is 2, 000.)

NUMBER [(p, s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from ?4 to 127.)

DATE Date and time values to the nearest second between January 1, 4712 B.C., and December 31, 9999 A.D.


Question 6:

There is a simple view SCOTT.DEPT_VIEW on the table SCOTT.DEPT.

This insert fails with an error:

SQL> insert into dept_view values(\’SUPPORT\’,\’OXFORD\’); insert into dept_view values(\’SUPPORT\’,\’OXFORD\’)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into (“SCOTT”.”DEPT”.”DEPTNO”)

What might be the problem?

A. The INSERT violates a constraint on the detail table.

B. The INSERT violates a constraint on the view.

C. The view was created as WITH READ ONLY.

D. The view was created as WITH CHECK OPTION.

Correct Answer: A

B is incorrect because constraints are enforced on detail tables, not on views. C and D are incorrect because the error message would be different.


Question 7:

You need to create a table for a banking application. One of the columns in the table has the following requirements:

1) You want a column in the table to store the duration of the credit period.

2) The data in the column should be stored in a format such that it can be easily added and subtracted with date data type without using conversion functions.

3) The maximum period of the credit provision in the application is 30 days.

4) The interest has to be calculated for the number of days an individual has taken a credit for.

Which data type would you use for such a column in the table?

A. DATE

B. NUMBER

C. TIMESTAMP

D. INTERVAL DAY TO SECOND

E. INTERVAL YEAR TO MONTH

Correct Answer: D

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. This data type is useful for representing the precise difference between two datetime values.

You can perform a number of arithmetic operations on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE) and interval (INTERVAL DAY TO SECOND and INTERVAL YEAR

TO MONTH) data.

?It stores duration of the credit as days

?The format stored is numeric format, and you know that numeric values can be easily added and subtracted with date data type without using conversion functions (i.e. SELECT SYSDATE ?1 FROM DUAL;)

?The interest has to be calculated for the number of days an individual has taken a credit for, so it will be easy to calculate interest by using the interest rate and duration of the the credit which is numeric format.

References:


Question 8:

You need to list the employees in DEPARTMENT_ID 30 in a single row, ordered by HIRE_DATE. Examine the sample output: Which query will provide the required output?

A. Option A

B. Option B

C. Option C

D. Option D

Correct Answer: B


Question 9:

View the Exhibit and examine the data in the employees table:

You want to display all the employee names and their corresponding manager names. Evaluate the following query:

Which join option can be used in the blank in the above query to get the required output?

A. INNER JOIN

B. FULL OUTER JOIN

C. LEFT OUTER JOIN

D. RIGHT OUTER JOIN

Correct Answer: C


Question 10:

View the Exhibit and examine the structures of the employees and departments tables.

You want to update the employees table as follows:

-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

-Set department_id for these employees to the department_id corresponding to London (location_id 2100).

-Set the employees\’ salary in iocation_id 2100 to 1.1 times the average salary of their department.

-Set the employees\’ commission in iocation_id 2100 to 1.5 times the average commission of their department.

You issue the following command:

What is the outcome?

A. It executes successfully and gives the correct result.

B. It executes successfully but does not give the correct result.

C. It generates an error because a subquery cannot have a join condition in an update statement.

D. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.

Correct Answer: B

Not that employees is used both in the first line (UPDATE employees) and later (FROM employees, departments). This would not cause the correct output. Instead aliases should be use.

The following would be the correct query:

UPDATE employees a

SET department_id =

(SELECT department_id

FROM departments

WHERE location_id = \’2100\’),

(salary, commission_pct) =

(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b

WHERE a.department_id = b.department_id)

WHERE department_id IN

(SELECT department_id

FROM departments

WHERE location_id = 2900

OR location_id = 2700);

References:


Question 11:

You want to create a sales table with the following column specifications and data types: SALESID: Number STOREID: Number ITEMID: Number QTY: Number, should be set to 1 when no value is specified SLSDATE: Date, should be set to current date when no value is specified PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified Which statement would create the table?

A. Option A

B. Option B

C. Option C

D. Option D

Correct Answer: B

To specify the default value of payment field you must use DEFAULT \’CASH\’.

References:


Question 12:

You issue the following command to drop the products table:

SQL> DROP TABLE products;

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

A. All data along with the table structure is deleted.

B. A pending transaction in the session is committed.

C. All indexes on the table remain but they are invalidated.

D. All views and synonyms remain but they are invalidated.

E. All data in the table is deleted but the table structure remains.

Correct Answer: ABD

A: The DROP TABLE statement moves a table or object table to the recycle bin.

B: If a user issues a DDL (CREATE, ALTER, or DROP) or DCL (GRANT or REVOKE) command, the transaction in progress (if any) will

Incorrect:

Not C: Dropping a table invalidates dependent objects, such as indexes and constraints.

References:


Question 13:

Which two statements are true regarding single row functions?

A. MOD: returns the quotient of a division

B. TRUNC: can be used with number and date values

C. CONCAT: can be used to combine any number of values

D. SYSDATE: returns the database server current date and time

E. INSTR: can be used to find only the first occurrence of a character in a string

F. TRIM: can be used to remove all the occurrences of a character from a string

Correct Answer: BD

ROUND: Rounds value to a specified decimal

TRUNC: Truncates value to a specified decimal

MOD: Returns remainder of division

SYSDATE is a date function that returns the current database server date and time.

Date-Manipulation Functions

Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value. MONTHS_BETWEEN(date1, date2): Finds the number of months between date1

and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative.

NEXT_DAY(date, \’char\’): Finds the date of the next specified day of the week (\’char\’) following date. The value of char may be a number representing a day or a character string.

LAST_DAY(date): Finds the date of the last day of the month that contains date The above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown

below:

ROUND(date[, \’fmt\’]): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, \’fmt\’]): Returns date with the time portion of the day

truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

The CONCAT Function

The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric

or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or

expressions resulting in character values. The INSTR(source string, search item, [start position], [nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start

position, where the nth occurrence of the search item begins:

instr(\’http://www.domain.com\’, \’.\’, 1, 2) = 18

The TRIM function literally trims off leading or trailing (or both) character strings from a given source string:


Question 14:

View the Exhibit and examine the structure of the promotions table.

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

A. It shows COST_REMARK for all the promos in the table.

B. It produces an error because the SUBQUERY gives an error.

C. It shows COST_REMARK for all the promos in the promo category \’TV\’

D. It produces an error because SUBQUERIES cannot be used with the case expression.

Correct Answer: A


Question 15:

You need to produce a report where each customer\’s credit limit has been incremented by $1000. In the output, the customer\’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.

Which statement would accomplish this requirement?

A. Option A

B. Option B

C. Option C

D. Option D

Correct Answer: C

A column alias:

-Renames a column heading

-Is useful with calculations

-Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)

-Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.


[PDF and VCE] Format for Free 1z0-144 Dumps With Exam Questions Download

Attention please! Here is the shortcut to pass your Apr 24,2022 Newest 1z0-144 pdf dumps exam! Get yourself well prepared for the Oracle Database Hotest 1z0-144 pdf Oracle Database 11g: Program with PL/SQL exam is really a hard job. But don’t worry! We We, provides the most update latest 1z0-144 dumps. With We latest 1z0-144 vce, you’ll pass the Oracle Database Newest 1z0-144 vce Oracle Database 11g: Program with PL/SQL exam in an easy way

We Geekcert has our own expert team. They selected and published the latest 1z0-144 preparation materials from Official Exam-Center.

The following are the 1z0-144 free dumps. Go through and check the validity and accuracy of our 1z0-144 dumps.Free sample questions of 1z0-144 free dumps are provided here. All the following questions are from the latest real 1z0-144 dumps.

Question 1:

Examine the following command:

SQL>ALTER SESSION SET plsql_warnings * \’enable: severe\’, \’enable: performance\’, \’ERROR: 05003\’;

What is the implication of the above command?

A. It issues a warning whenever ERROR: 05003 occur during compilation.

B. It causes the compilation to fail whenever the warning ERROR.05003 occurs.

C. It issues warnings whenever the code causes an unexpected action or wrong results performance problems.

D. It causes the compilation to fail whenever the code gives wrong results or contains statements that are never executed.

Correct Answer: B


Question 2:

View Exhibit 1 and examine the structure of the EMPLOYEES table.

View Exhibit 2 and examine the code.

What would be the outcome when the code is executed?

A. It executes successfully.

B. It gives an error because the SAL variable is not visible in the INCREASE function.

C. It gives an error because the INCREASE function cannot be called from the RAISE_SALARY procedure.

D. It gives an error because the INCREASE function and the RAISE_SALARY procedure should be declared at the beginning of the DECLARE section before all the other declarations.

Correct Answer: A


Question 3:

Which statements are true about PL/SQL procedures? (Choose two.)

A. Users with definer\’s rights who are granted access to a procedure that updates a table must be granted access to the table itself.

B. Reuse of parsed PL/SQL code that becomes available in the shared SQL area of the server avoids the parsing overhead of SQL statements at run time.

C. Depending on the number of calls, multiple copies of the procedure are loaded into memory for execution by multiple users to speed up performance.

D. A PL/SQL procedure executing on the Oracle database can call an external procedure or function that is written in a different programming language, such as C or Java.

Correct Answer: BD


Question 4:

The STRING_TAB table has the following structure:

View the Exhibit and examine the code.

What is the outcome on execution?

A. It displays Output buffer not long enough. This is my test string.-.

B. It displays only Output buffer not long enough. and exits the anonymous block.

C. It displays only This is my test string.

Correct Answer: C


Question 5:

Identify two situations where the DBMS_SQL package should be used. (Choose two.)

A. The SELECT list is not known until run time.

B. The dynamic SQL statement retrieves rows into records.

C. You do not know how many columns a SELECT statement will return, or what their data types will be.

D. You must use the % FOUND SQL cursor attribute after issuing a dynamic SQL statement that is an INSERT or UPDATE statement.

Correct Answer: AC


Question 6:

View the Exhibit and examine the code.

Why does the code give an error on execution?

A. because the WORD_LIST variable is not visible in PROCEDURE WORDING

B. because the LEXICON variable is not visible in PROCEDURE ADD_ENTRY

C. because the LEXICON variable is not initialized in PROCEDURE WORDING

D. because the WORD_LIST parameter in IN OUT mode cannot be of a RECORD data type

Correct Answer: A


Question 7:

View the Exhibit and examine the structure of the EMP table.

You want to create two procedures using the overloading feature to search for employee details based on either the employee name or employee number. Which two rules should you apply to ensure that the overloading feature is used successfully? (Choose two.)

A. The procedures can be either stand-alone or packaged.

B. The procedures should be created only as packaged subprograms.

C. The procedures should be created only as stand-alone subprograms.

D. Each subprogram\’s formal parameters should differ in both name and data type.

E. The formal parameters of each subprogram should differ in data type but can use the same names.

Correct Answer: BE


Question 8:

Which two statements are true about the INSTEAD OF triggers? (Choose two.)

A. DELETE operations cannot be performed using the INSTEAD OF triggers.

B. The INSTEAD OF triggers must be created to add or modify data through any view.

C. The INSTEAD OF triggers can be written only for views, and the BEFORE and AFTER timing options are not valid.

D. The CHECK option for views is not enforced when insertions or updates to the view are performed by using the INSTEAD OF triggers.

Correct Answer: CD


Question 9:

Which two statements are correct about the usage of parameters in functions? (Choose two.)

A. Functions can have only in mode parameters.

B. Functions called in SQL statements cannot have out or in out mode parameters.

C. Functions having in, out, or in out parameters can be called only in named PL/SQL subprograms

D. Functions having in, out, or in out parameters can be called in PL/SQL procedures and anonymous blocks.

Correct Answer: BD


Question 10:

View the Exhibit and examine the structure of the employees table.

Examine the following block of code:

What is the outcome when the above code is executed?

A. It executes successfully.

B. It gives an error because decode cannot be used in a PL/SQL block.

C. It gives an error because the AVG function cannot be used in a PL/SQL block

D. It gives an error because the MONTHS_BETWEEN function cannot be used in a PL/SQL block.

E. It gives an error because both the AVG and decode functions cannot be used in a PL/SQL block.

Correct Answer: A


Question 11:

/temp/my_files is an existing folder in the server, facultylist.txt is an existing text file in this folder.

Examine the following commands that are executed by the DBA:

SQL>CREATE DIRECTION my_dir AS `/temp/my_files\’:

SQL>GRANT READ ON DIRECTORY my_dir To public:

View the Exhibit and examine the procedure created by user SCOTT to read the list of faculty names from the text file.

SCOTT executes the procedure as follows:

SQL>SET SERVEROUTPUT ON

SQL>EXEC read_file (`MY_DIR\’, FACULTYLIST.TXT\’)

What is the outcome?

A. It goes into an infinite loop.

B. It executes successfully and displays only the list of faculty names.

C. It does not execute and displays an error message because the end-of-file condition is not taken care of.

D. It executes successfully and displays the list of faculty names followed by a “no data found” error message.

Correct Answer: D


Question 12:

View the Exhibit to examine the PL/SQL block.

Which two statements are true about the execution of the PL/SQL block? (Choose two.)

A. It executes successfully and gives the desired output.

B. It does not execute because the definition of type POPULATION is indexed by VARCHAR2.

C. It executes, and the string keys of an associative array are not stored in creation order, but in sorted order.

D. It does not execute because the value that is once assigned to the element of the associative array cannot be changed.

Correct Answer: AC


Question 13:

User SCOTT has been granted CREATE ANY TRIGGER and ALTER ANY TABLE by the DBA. HR is an existing schema in the database.

SCOTT creates the following trigger:

CREATE OR REPLACE TRIGGER drop_trigger

BEFORE DROP ON hr.SCHEMA

BEGIN

RAISE_APPLICATION_ERROR (-20000, `Cannot drop object\’);

END;

SCOTT does not grant the EXECUTE privilege on this trigger to any other users.

For which user(s) would this trigger fire by default when they drop an object in the HR schema?

A. Only HR

B. SCOTT and HR

C. Only SCOTT

D. SCOTT, HR, and SYS

Correct Answer: A


Question 14:

Which two statements are true about the continue statement? (Choose two.)

A. The PL/SQL block execution terminates immediately.

B. The CONTINUE statement cannot appear outside a loop.

C. The loop completes immediately and control passes to the statement after end loop.

D. The statements after the continue statement in the iteration are executed before terminating the LOOP.

E. The current iteration of the loop completes immediately and control passes to the next iteration of the loop.

Correct Answer: BE


Question 15:

View the Exhibit and examine the code and its outcome on execution: What would be the effect on the two procedures if the value of debug is set to FALSE? (Choose two.)

A. MY_PROC2 is not recompiled.

B. MY_PROC1 is recompiled but remains unchanged.

C. MY_PROC2 is recompiled but remains unchanged.

D. MY_PROC1 is recompiled without the debugging code.

Correct Answer: CD


Free Download the Most Update Geekcert Oracle 1Z0-060 Brain Dumps

Do not worry about your Oracle Database Hotest 1Z0-060 pdf exam preparation? Hand over your problems to Geekcert in change of the Oracle Database Jan 14,2022 Hotest 1Z0-060 pdf dumps Upgrade to Oracle Database 12c certifications! Geekcert provides the latest Oracle Oracle Database Latest 1Z0-060 vce exam preparation materials with PDF and VCEs. We Geekcert guarantees you passing Oracle Database Latest 1Z0-060 pdf exam for sure.

Geekcert 1Z0-060 certification dumps : oracle, ibm and many more. Geekcert provides you the easiest way to pass your 1Z0-060 certification exam. Geekcert free certification 1Z0-060 exam | Geekcert practice 1Z0-060 exams | Geekcert test 1Z0-060 questions. Geekcert- reliable 1Z0-060 certifications expert on 1Z0-060 exam study guide providing.

We Geekcert has our own expert team. They selected and published the latest 1Z0-060 preparation materials from Oracle Official Exam-Center: https://www.geekcert.com/1Z0-060.html

The following are the 1Z0-060 free dumps. Go through and check the validity and accuracy of our 1Z0-060 dumps.Real questions from 1Z0-060 free dumps. Download demo of 1Z0-060 dumps to check the validity.

Question 1:

Which two statements are true about the use of the procedures listed in the v$sysaux_occupants.move_procedure column? (Choose two.)

A. The procedures may be used for some components to relocate component data to the SYSAUX tablespace from its current tablespace.

B. The procedures may be used for some components to relocate component data from the SYSAUX tablespace to another tablespace.

C. All the components may be moved into SYSAUX tablespace.

D. All the components may be moved from the SYSAUX tablespace.

Correct Answer: AB

References: http://www.dba-oracle.com/t_v_sysaux_contents_tips.htm


Question 2:

Which statement is true about Oracle Net Listener?

A. It acts as the listening endpoint for the Oracle database instance for all local and non-local user connections.

B. A single listener can service only one database instance and multiple remote client connections.

C. Service registration with the listener is performed by the listener registration process (LREG) process of each database instance.

D. The listener.ora configuration file must be configured with one or more listening protocol addresses to allow remote users to connect to a database instance.

E. The listener.ora configuration file must be located in the ORACLE_HOME/network/admin directory.

Correct Answer: C


Question 3:

An Automatic Database Diagnostic Monitor (ADDM) finding in your production database reports that the shared pool is inadequately sized. You diagnose that this is due to the different kinds of workloads and this occurs only during peak hours. The following are the parameter settings for the database instance:

You want to balance the memory between the System Global Area (SGA) components depending on the workload. Which option would solve this problem?

A. setting the PGA_AGGREGATE_TARGET parameter to 200M and the SGA_MAX_SIZE parameter to 400M

B. setting the MEMORY_TARGET and SGA_MAX_SIZE parameters to 400M

C. setting the SGA_TARGET parameter to 300M

D. setting the SGA_MAX_SIZE parameter to 400M

Correct Answer: C


Question 4:

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

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

Which three are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled?

A. Reduced logical I/O

B. Reduced virtual memory utilization

C. Improved parallel Execution performance

D. Improved Serial Execution performance

E. Reduced physical I/O

F. Reduced CPU utilization

Correct Answer: BCF

* Multiprocess and Multithreaded Oracle Database Systems

Multiprocess Oracle Database (also called multiuser Oracle Database) uses several processes to run different parts of the Oracle Database code and additional Oracle processes for the users–either one process for each connected user or one or more processes shared by multiple users. Most databases are multiuser because a primary advantage of a database is managing data needed by multiple users simultaneously.

Each process in a database instance performs a specific job. By dividing the work of the database and applications into several processes, multiple users and applications can connect to an instance simultaneously while the system gives good performance.

* In previous releases, Oracle processes did not run as threads on UNIX and Linux systems. Starting in Oracle Database 12c, the multithreaded Oracle Database model enables Oracle processes to execute as operating system threads in separate address spaces.


Question 6:

You notice that the performance of your production 24/7 Oracle 12c database significantly degraded. Sometimes you are not able to connect to the instance because it hangs. You do not want to restart the database instance.

How can you detect the cause of the degraded performance?

A. Enable Memory Access Mode, which reads performance data from SGA.

B. Use emergency monitoring to fetch data directly from SGA analysis.

C. Run Automatic Database Diagnostic Monitor (ADDM) to fetch information from the latest Automatic Workload Repository (AWR) snapshots.

D. Use Active Session History (ASH) data and hang analysis in regular performance monitoring.

E. Run ADDM in diagnostic mode.

Correct Answer: B


Question 7:

In your multitenant container database (CDB) containing pluggable databases (PDB), users complain about performance degradation.

How does real-time Automatic Database Diagnostic Monitor (ADDM) check performance degradation and provide solutions?

A. It collects data from SGA and compares it with a preserved snapshot.

B. It collects data from SGA, analyzes it, and provides a report.

C. It collects data from SGA and compares it with the latest snapshot.

D. It collects data from both SGA and PGA, analyzes it, and provides a report.

Correct Answer: B

Note:

*

The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

*

The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM).

*

The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process.


Question 8:

Examine the following steps of privilege analysis for checking and revoking excessive, unused privileges granted to users:

1.

Create a policy to capture the privilege used by a user for privilege analysis.

2.

Generate a report with the data captured for a specified privilege capture.

3.

Start analyzing the data captured by the policy.

4.

Revoke the unused privileges.

5.

Compare the used and unused privileges\’ lists.

6.

Stop analyzing the data.

Identify the correct sequence of steps.

A. 1, 3, 5, 6, 2, 4

B. 1, 3, 6, 2, 5, 4

C. 1, 3, 2, 5, 6, 4

D. 1, 3, 2, 5, 6, 4

E. 1, 3, 5, 2, 6, 4

Correct Answer: B

1. Create a policy to capture the privilege used by a user for privilege analysis.

3. Start analyzing the data captured by the policy.

6. Stop analyzing the data.

2. Generate a report with the data captured for a specified privilege capture.

5. Compare the used and unused privileges\’ lists.

4. Revoke the unused privileges.


Question 9:

Your database is running an ARCHIVELOG mode.

The following parameters are set in your database instance:

Which statement is true about the archived redo log files?

A. They are created only in the location specified by the LOG_ARCHIVE_DEST_1 parameter.

B. They are created only in the Fast Recovery Area because configuring the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters automatically enables flashback for the database.

C. They are created in the location specified by the LOG_ARCHIVE_DEST_1 parameter and in the default location $ORACLE_HOME/dbs/arch.

D. They are created in the location specified by the LOG_ARCHIVE_DEST_1 parameter and in the location specified by the DB_RECOVERY_FILE_DEST parameter.

Correct Answer: A

You can choose to archive redo logs to a single destination or to multiple destinations.

Destinations can be local–within the local file system or an Oracle Automatic Storage Management (Oracle ASM) disk group–or remote (on a standby database). When you archive to multiple destinations, a copy of each filled redo log file is

written to each destination. These redundant copies help ensure that archived logs are always available in the event of a failure at one of the destinations. To archive to only a single destination, specify that destination using the

LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST initialization parameters.

ARCHIVE_DEST initialization parameter. To archive to multiple destinations, you can choose to archive to two or more locations using the LOG_ARCHIVE_DEST_n initialization parameters, or to archive only to a primary and secondary

destination using the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST initialization parameters.


Question 10:

Which three functions are performed by the SQL Tuning Advisor?

A. Building and implementing SQL profiles

B. Recommending the optimization of materialized views

C. Checking query objects for missing and stale statistics

D. Recommending bitmap, function-based, and B-tree indexes

E. Recommending the restructuring of SQL queries that are using bad plans

Correct Answer: ACE

The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects (C), creation of new indexes, restructuring of the SQL statement (E), or creation of a SQL profile (A). You can choose to accept the recommendation to complete the tuning of the SQL statements.


Question 11:

Examine the following command:

ALTER SYSTEM SET enable_ddl_logging=FALSE;

Which statement is true?

A. None of the data definition language (DDL) statements are logged in the trace file.

B. Only DDL commands that resulted in errors are logged in the alert log file.

C. A new log.xml file that contains the DDL statements is created, and the DDL command details are removed from the alert log file.

D. Only DDL commands that resulted in the creation of new database files are logged.

Correct Answer: A

ENABLE_DDL_LOGGING enables or disables the writing of a subset of data definition language (DDL) statements to a DDL alert log.

The DDL log is a file that has the same format and basic behavior as the alert log, but it only contains the DDL statements issued by the database. The DDL log is created only for the RDBMS component and only if the ENABLE_DDL_LOGGING initialization parameter is set to true. When this parameter is set to false, DDL statements are not included in any log.


Question 12:

Identify two valid options for adding a pluggable database (PDB) to an existing multitenant container database (CDB). (Choose two.)

A. Use the CREATE PLUGGABLE DATABASE statement to create a PDB using the files from the SEED.

B. Use the CREATE DATABASE … ENABLE PLUGGABLE DATABASE statement to provision a PDB by copying file from the SEED.

C. Use the DBMS_PDB package to clone an existing PDB.

D. Use the DBMS_PDB package to plug an Oracle 12c non-CDB database into an existing CDB.

E. Use the DBMS_PDB package to plug an Oracle 11g Release 2 (11.2.0.3.0) non-CDB database into an existing CDB.

Correct Answer: AD


Question 13:

Your database supports a DSS workload that involves the execution of complex queries: Currently, the library cache contains the ideal workload for analysis. You want to analyze some of the queries for an application that are cached in the library cache.

What must you do to receive recommendations about the efficient use of indexes and materialized views to improve query performance?

A. Create a SQL Tuning Set (STS) that contains the queries cached in the library cache and run the SQL Tuning Advisor (STA) on the workload captured in the STS.

B. Run the Automatic Workload Repository (AWR) report.

C. Create an STS that contains the queries cached in the library cache and run the SQL Performance Analyzer (SPA) on the workload captured in the STS.

D. Create an STS that contains the queries cached in the library cache and run the SQL Access Advisor on the workload captured in the STS.

E. Run the Automatic Database Diagnostic Monitor (ADDM).

Correct Answer: D

*

SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. SQL Tuning Advisor makes other types of recommendations, such as creating SQL profiles and restructuring SQL statements.

*

The query optimizer can also help you tune SQL statements. By using SQL Tuning Advisor and SQL Access Advisor, you can invoke the query optimizer in advisory mode to examine a SQL statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor and SQL Access Advisor can make various recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes or materialized views, and refreshing optimizer statistics.

Note:

*

Decision support system (DSS) workload

*

The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles.


Question 14:

The following parameters are set for your Oracle 12c database instance:

You want to manage the SQL plan evolution task manually. Examine the following steps:

1.

Set the evolve task parameters.

2.

Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.

3.

Implement the recommendations in the task by using the DBMS_SPM.IMPLEMENT_EVOLVE_TASK function.

4.

Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.

5.

Report the task outcome by using the DBMS_SPM.REPORT_EVOLVE_TASK function.

Identify the correct sequence of steps:

A. 2, 4, 5

B. 2, 1, 4, 3, 5

C. 1, 2, 3, 4, 5

D. 1, 2, 4, 5

Correct Answer: B

* Evolving SQL Plan Baselines

2. Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.

This function creates an advisor task to prepare the plan evolution of one or more plans for a specified SQL statement. The input parameters can be a SQL handle, plan name or a list of plan names, time limit, task name, and description.

1. Set the evolve task parameters.

SET_EVOLVE_TASK_PARAMETER

This function updates the value of an evolve task parameter. In this release, the only valid parameter is TIME_LIMIT.

4. Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.

This function executes an evolution task. The input parameters can be the task name, execution name, and execution description. If not specified, the advisor generates the name, which is returned by the function.

3: IMPLEMENT_EVOLVE_TASK

This function implements all recommendations for an evolve task. Essentially, this function is equivalent to using ACCEPT_SQL_PLAN_BASELINE for all recommended plans. Input parameters include task name, plan name, owner name,

and execution name.

5. Report the task outcome by using the DBMS_SPM_EVOLVE_TASK function.

This function displays the results of an evolve task as a CLOB. Input parameters include the task name and section of the report to include.


Question 15:

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.