-
You are creating a SQL Tuning Set to allow a group of SQL statements to be passed into the SQL Tuning Advisor. Which packages will you use to create the SQL Tuning Set？（）
A . DBMS_WORKLOAD_REPOSITORY
B . DBMS_RESOURCE_MANAGER
C . DBMS_SQLTUNE
D . DBMS_ADVISOR
-
The SQL Tuning Advisor configuration has default settings in your database instance. Which recommendation is automatically implemented after the SQL Tuning Advisor is run as part of the automatic maintenance task?（）
A . statistics recommendations
B . SQL Profile recommendations
C . Index-related recommendations
D . restructuring of SQL recommendatio
-
Evaluate the SQL statement:
https://assets.asklib.com/images/image2/2018080115100724106.jpg
What is the result of the statement?（）
A . The statement produces an error at line 1.
B . The statement produces an error at line 3.
C . The statement produces an error at line 6.
D . The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary then the maximum salary paid in the company.
E . The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
-
Evaluate this SQL statement:
https://assets.asklib.com/images/image2/2018080116554090144.jpg
In the statement, which capabilities of a SELECT statement are performed?（）
A . Selection, projection, join
B . Difference, projection, join
C . Selection, intersection, join
D . Intersection, projection, join
E . Difference, projection, product
-
Evaluate the SQL statement DROP TABLE DEPT: Which four statements are true of the SQL statement? （）
A . You cannot roll back this statement.
B . All pending transactions are committed.
C . All views based on the DEPT table are deleted.
D . All indexes based on the DEPT table are dropped.
E . All data in the table is deleted, and the table structure is also deleted.
F . All data in the table is deleted, but the structure of the table is retained.
G . All synonyms based on the DEPT table are deleted.
-
Evaluate the following SQL statements that are issued in the given order：
https://assets.asklib.com/images/image2/2018080114465944231.jpg
What would be the status of the foreign key EMP_MGR_FK？（）
A . It would be automatically enabled and deferred.
B . It would be automatically enabled and immediate.
C . It would remain disabled and has to be enabled manually using the ALTER TABLE command.
D . It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
-
When creating a SQL tuning set，which of the following steps allows the DBA to reduce the size of the SQL set by selecting specific operators and values？（）
A . Filter versions
B . Filter loads
C . Filter tasks
D . Filter optio
-
You plan to use SQL Performance Analyzer to analyze the SQL workload. You created a SQL Tuning Set as a part of the workload capturing. What information is captured as part of this process？（）
A . the SQL text
B . the execution plan
C . the execution context
D . the execution frequency
E . the system change number （SCN）
-
Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set?（）
A . The DESCRIBE DEPT statement displays the structure of the DEPT table.
B . The ROLLBACK statement frees the storage space occupied by the DEPT table.
C . The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
D . The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
-
You want to analyze a SQL Tuning Set （STS） using SQL Performance Analyzer in a test database. Which two statements are true regarding the activities performed during the test execution of SQLs in a SQL Tuning Set？（）
A . Every SQL statement in the STS is considered only once for execution.
B . The SQL statements in the STS are executed concurrently to produce the execution plan and execution statistics.
C . The execution plan and execution statistics are computed for each SQL statement in the STS.
D . The effects of DDL and DML are considered to produce the execution plan and execution statistics.
-
Evaluate the following SQL statements that are issued in the given order：
https://assets.asklib.com/images/image2/2018080114351530846.jpg
What would be the status of the foreign key EMP_MGR_FK？（）
A . It would be automatically enabled and deferred.
B . It would be automatically enabled and immediate.
C . It would remain disabled and has to be enabled manually using the ALTER TABLE command.
D . It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
-
Evaluate the SQL statement: TRUNCATE TABLE DEPT; Which three are true about the SQL statement? （）
A . It releases the storage space used by the table.
B . It does not release the storage space used by the table.
C . You can roll back the deletion of rows after the statement executes.
D . You can NOT roll back the deletion of rows after the statement executes.
E . An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
F . You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table
-
Evaluate the following command: SQL>ALTER SYSTEM SET db_securefile = 'IGNORE'; What is the impact of this setting on the usage of SecureFiles?（）
A . It forces BasicFiles to be created even if the SECUREFILE option is specified to create the LOB
B . It forces SecureFiles to be created even if the BASICFILE option is specified to create the LOB
C . It does not allow the creation of SecureFiles and generates an error if the SECUREFILE option is specified to create the LOB
D . It ignores the SECUREFILE option only if a Manual Segment Space Management tablespace is used and creates a BasicFile
-
Examine the command： SQL> DBMS_STATS.SET_TABLE_PREFS（’SH’， ’CUSTOMERS’， ’PUBLISH’， ’false’）； Which statement describes the effect of the above command（）
A . Automatic statistics collection is stopped for the CUSTOMERS table.
B . Statistics for the CUSTOMERS table are locked and cannot be overwritten.
C . Existing statistics for the CUSTOMERS table become unusable for the query optimizer.
D . Subsequently， statistics gathered on the CUSTOMERS table are stored as pending statistics.
-
Evaluate the following command： SQL>ALTER SYSTEM SET db_securefile = ’IGNORE’； What is the impact of this setting on the usage of SecureFiles？（）
A . It forces BasicFiles to be created even if the SECUREFILE option is specified to create the LOB
B . It forces SecureFiles to be created even if the BASICFILE option is specified to create the LOB
C . It does not allow the creation of SecureFiles and generates an error if the SECUREFILE option is specified to create the LOB
D . It ignores the SECUREFILE option only if a Manual Segment Space Management tablespace is used and creates a BasicFile
-
The SQL Tuning Advisor configuration has default settings in your database instance. Which recommendation is automatically implemented after the SQL Tuning Advisor is run as part of the automatic maintenance task？（）
A . statistics recommendations
B . SQL Profile recommendations
C . Index-related recommendations
D . restructuring of SQL recommendatio
-
Evaluate the SQL statement: SELECT LPAD (salary,10,*) FROM EMP WHERE EMP _ ID = 1001; If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?（）
A . 17000.00
B . 17000*****
C . ****170.00
D . **17000.00
E . an error statement
-
You plan to use SQL Performance Analyzer to analyze the SQL workload. You created a SQL Tuning Set as a part of the workload capturing. What information is captured as part of this process?（）
A . the SQL text SQL
B . the execution plan
C . the execution context
D . the execution frequency
E . the system change number (SCN)
-
You are maintaining the PROD database of NetFx Corporation. You set the value of the SQL_TRACE parameter to TRUE. What will be the impact of this setting？（）
A . The SQL trace facility will generate the performance statistics for all the SQL statements for an instance and write them in the USER_DUMP_DEST directory.
B . The SQL trace facility will generate the performance statistics for all the SQL statements for a session and write them in the USER_DUMP_DEST directory.
C . The SQL trace facility will generate the performance statistics for all the SQL statements for an instance and write them in the BACKGROUND_DUMP_DEST directory.
D . The SQL trace facility will generate the performance statistics for all the SQL statements for a session and write them in the BACKGROUND_DUMP_DEST directory.
-
Evaluate the set of SQL statements:
https://assets.asklib.com/images/image2/201808011518065034.jpg
What is true about the set?（）
A . The DESCRIBE DEPT statement displays the structure of the DEPT table.
B . The ROLLBACK statement frees the storage space occupies by the DEPT table.
C . The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
D . The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
-
Examine the command： SQL> DBMS_STATS.SET_TABLE_PREFS（’SH’， ’CUSTOMERS’， ’PUBLISH’， ’false’）； Which statementdescribes the effect of the above command（）
A . Automatic statistics collection is stopped for the CUSTOMERS table.
B . Statistics for the CUSTOMERS table are locked and cannot be overwritten.
C . Existing statistics for the CUSTOMERS table become unusable for the query optimizer.
D . Subsequently， statistics gathered on the CUSTOMERS table are stored as pending statistic
-
Evaluate the set of SQL statements:CREATE TABLE dept（deptno NUMBER（2),dname VARCNAR2（14),1oc VARCNAR2 （13));ROLLBACK;DESCRIBE DEPTWhat is true about the set？（）
A. The DESCRIBE DEPT statement displays the structure of the DEPT table.
B. The ROLLBACK statement frees the storage space occupies by the DEPT table.
C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
-
Which of the following is the right setting of the Basic Recording Period （BRP) for ST
A．A.20or25minutes
B．B.10or20minutes
C．C.5or15minutes
-
Examine the data of the EMPLOYEES table.EMPLOYEES （EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)Evaluate this SQL statement:What is its output？（）
<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/6bce46904716f2e8357f4849d2a3423d.jpg' />
<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/c5c76fa6bab26c6735c868df586306cf.jpg' />
A.<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/0f921b8c15106e9d6cc8e36a50134106.jpg' />
B.<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/d072515564553d90b433296aa71e021a.jpg' />
C.<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/b4721c6b94a9b61c92cdbe82aabc3ae1.jpg' />
D.<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/b27c35e83798de4bbbccaef22fa31b48.jpg' />
E. The SQL statement produces an error.
