1. Which three statements are true about single-row functions? (Choose three.)

2. Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? (Choose two.)

3. In one of your databases, you create a user, HR, and then execute this command:
GRANT CREATE SESSION TO hr WITH ADMIN OPTION;
Which four actions can HR perform? (Choose four.)

4. In the spfile of a single instance database, LOCAL_LISTENER is set to LISTENER_1.
The TNSNAMES.ORA file in $ORACLE_HOME/network/admin in the database home contains:

Which statement is true?

5. Which two are true about shrinking a segment online? (Choose two.)

6.

Examine the description of the PROMOTIONS table:

You want to display the unique promotion costs in each promotion category.
Which two queries can be used? (Choose two.)

7.

Examine this description of the TRANSACTIONS table:

Which two SQL statements execute successfully? (Choose two.)

8. Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

9. Which three statements are true about the Oracle Data Dictionary? (Choose three.)

10. Which two statements are true about the rules of precedence for operators? (Choose two.)

11. Which two statements are true about the PMON background process? (Choose two.)

12. Which three statements are true about advanced connection options supported by Oracle Net for connection to Oracle Database instances? (Choose three.)

13. The SCOTT/TIGER user exists in two databases, BOSTON_DB and DALLAS_DB, in two different locations.
Each database has a tnsnames.ora file defining DALLAS_DB as a service name.
Examine this command:
CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING 'dallas_db';
How do you execute the command so that only SCOTT in BOSTON_DB can access the SCOTT schema in DALLAS_DB?

14. Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? (Choose two.)

15. Which three statements are true concerning logical and physical database structures? (Choose three.)

16. Your database instance is started with a PFILE.
Examine these parameters:

You want to increase the size of the buffer cache.
Free memory is available to increase the size of the buffer cache.
You execute the command:
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1024M;
What is the outcome?

17. A script abc.sql must be executed to perform a job.
A database user HR, who is defined in this database, executes this command:
$ sqlplus hr/hr@orcl @abc.sql
What will happen upon execution?

18. In one of your databases, the user HR has the password HRMGR.
You want to connect to a database instance whose listener listens on port 1531 by using this statement:

CONNECT HR/HRMGR@orcl -
No name server is used.
Which statement is true about ORCL?

19. A database is configured to use automatic undo management with temporary undo enabled.
An UPDATE is executed on a temporary table.
Where is the UNDO stored?

20. What is true about non-equijoin statement performance? (Choose two.)

21. Which two statements are true about views used for viewing tablespace and datafile information? (Choose two.)

22. Which four account management capabilities can be configured using Oracle profiles? (Choose four.)

23. Which three statements are true about a self join? (Choose three.)

24.

Examine the description of the SALES1 table:

SALES2 is a table with the same description as SALES1.
Some sales data is duplicated in both tables.
You want to display the rows from the SALES1 table which are not present in the SALES2 table.
Which set operator generates the required output?

25. You want to use table compression suitable for OLTP that will:
1. Compress rows for all DML statements on that table
2. Minimize the overheads associated with compression
Which compression option is best suited for this?

26. Which two statements are true about the Automatic Diagnostic Repository (ADR)? (Choose two.)

27. In your data center, Oracle Managed Files (OMF) is used for all databases.
All tablespaces are smallfile tablespaces.
SALES_Q1 is a permanent user-defined tablespace in the SALES database.
Examine this command which is about to be issued by a DBA logged in to the SALES database:
ALTER TABLESPACE sales_q1 ADD DATAFILE;
Which are two actions, either one of which you could take to ensure that the command executes successfully? (Choose two.)

28. Which three statements are true about the DESCRIBE command? (Choose three.)

29. You want to apply the principle of Least Privilege in all your live databases.
One of your requirements is to revoke unnecessary privileges from all users who have them using Privilege Analysis.
Which three types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package? (Choose three.)

30. You have been tasked to create a table for a banking application.
One of the columns must meet three requirements:
1. Be stored in a format supporting date arithmetic without using conversion functions
2. Store a loan period of up to 10 years
3. Be used for calculating interest for the number of days the loan remains unpaid
Which data type should you use?

31. Which two statements are true about the results of using the INTERSECT operator in compound queries? (Choose two.)

32. The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE.
You want to display the date of the first Monday after the completion of six months since hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week.
Which query can be used?

33. Which three statements are true about the tools used to configure Oracle Net Services? (Choose three.)

34.

Examine the description of the CUSTOMERS table:

For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?

35. You want to write a query that prompts for two column names and the WHERE condition each time it is executed in a session but only prompts for the table name the first time it is executed.
The variables used in your query are never undefined in your session.
Which query can be used?

36. You execute this command:
CREATE SMALLFILE TABLESPACE sales
DATAFILE ‘/u01/app/oracle/sales01.dbf SIZE 5G
SEGMENT SPACE MANAGEMENT AUTO;
Which two actions must you take to ensure UNDOTBS01 is used as the default UNDO tablespace? (Choose two.)

37. Examine this command:

Which two statements are true? (Choose two.)

38. Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

39. In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE. Examine this command:
SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);
Which segment or segments, if any, are created as a result of executing the command?

40. Which three Oracle database space management features will work with both Dictionary and Locally managed tablespaces? (Choose three.)

41. Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)

42. Which two statements are true about the SET VERIFY ON command? (Choose two.)

43. In which three situations does a new transaction always start? (Choose three.)

44. Which two statements are true about Enterprise Manager Database Express? (Choose two.)

45. In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:
1. A size of 100 MB
2. AUTOEXTEND is off
3. UNDO_RETENTION is set to 15 minutes
4. It has RETENTION GUARANTEE
UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens.
What will happen when the next update is attempted by any transaction?

46. Which two tasks can you perform using DBCA for databases? (Choose two.)

47. Examine this command:
CREATE UNDO TABLESPACE undotbs01
DATAFILE ‘undotbs_01.dbf’ SIZE 100M
AUTOEXTEND ON;
Which two actions must you take to ensure UNDOTBS01 is used as the default UNDO tablespace? (Choose two.)

48. Which three functions are performed by dispatchers in a shared server configuration? (Choose three.)

49. Which three instance situations are possible with the Oracle Database server without multi-tenant? (Choose three.)

50. You execute this command:

During the export operation, you detach from the job by using CTRL+C and then execute this command:

Export> STOP_JOB=immediate -
Are you sure you wish to stop the job ([yes]/no): yes
Which two statements are true about the job? (Choose two.)

51. In one of your databases, user KING is:
1. Not a DBA user
2. An operating system (OS) user
Examine this command and its output:

What must you do so that KING is authenticated by the OS when connecting to the database instance?

52. Which two statements are true regarding Oracle database space management within blocks managed by Automatic Segment Space Management (ASSM)?
(Choose two.)

53. The ORCL database has RESUMABLE__TIMEOUT = 7200 and DEFERRED_SEGMENT_CREATION = FALSE
User U1 has a 1 MB quota in tablespace DATA.
U1 executes this command:

SQL> CREATE TABLE t1 AS -
(SELECT object_name, sharing, created
FROM dba_objects);
U1 complains that the command is taking too long to execute.
In the alert log, the database administrator (DBA) finds this:
2017-03-06T12:15:17.183438+05:30
statement in resumable session 'User U1(136), Session 1, Instance 1' was suspended due to ORA-01536: space quota exceeded for tablespace 'DATA'
Which are three actions any one of which the DBA could take to resume the session? (Choose three.)

54. Examine this command and some partial output:

Why does the DB01.abc.com service show unknown status?

55.

Evaluate these commands which execute successfully:

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence? (Choose two.)

56. Which statement is true about the INTERSECT operator used in compound queries?

57. Which two statements are true about UNDO and REDO? (Choose two.)

58.

Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

59. Which two statements are true about trace files produced by the Oracle Database server? (Choose two.)

60. You execute this command:

Sufficient storage is available in filesystem /u01.
Which two statements are true about the BIG_TBS tablespace? (Choose two.)

61. Which two statements are true regarding a SAVEPOINT? (Choose two.)

62. Which three are types of segments in an Oracle Database? (Choose three.)

63. Which three statements are true about undo segments and the use of undo by transactions in an Oracle database instance? (Choose three.)

64. Your database instance was shut down normally and then started in NOMOUNT state. You then execute this command:
ALTER DATABASE MOUNT;
Which two actions are performed? (Choose two.)

65. Which two statements are true about single row functions? (Choose two.)

66. Examine these commands:

Which two statements are true about the sqlldr execution? (Choose two.)

67. Which two statements are true about space-saving features in an Oracle Database? (Choose two.)

68. Examine the description of the BOOKS table:

The table has 100 rows.
Examine this sequence of statements issued in a new session:
INSERT INTO books VALUES ('ADV112', 'Adventures of Tom Sawyer', NULL, NULL);
SAVEPOINT a;
DELETE FROM books;
ROLLBACK TO SAVEPOINT a;
ROLLBACK;
Which two statements are true? (Choose two.)

69. Which three statements are true about Oracle synonyms? (Choose three.)

70.

Which two are true about a SQL statement using SET operators such as UNION? (Choose two.)

71.

Examine the description of the CUSTOMERS table:

For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?

72. Which statement is true about aggregate functions?