[practice] Oracle Database Administration I 1Z0-082 - Notes vu vơ những thứ đã học

Copy - Exam 1Z0-082 Oracle Database Administration I

Oracle Database Administration I 1z0-082

Number of Questions: 72
Passing Score: 60%

1 / 229

1.

Which two statements are true about the Oracle Data Dictionary?

1. All data dictionary view joins base tables to dynamic performance views.

2. It is owned by the system user.

3. Data dictionary views are always created with queries that join two or more base tables.

4. It is owned by the sys user.

5. Data dictionary base tables can be queried directly.

2 / 229

2.

The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE. NLS_DATE_FORMAT is set to DD-MON-RR. Which two are true about data type conversions involving these columns in query expressions? (Choose two.)

1. invoice_date = '15-march-2019' : uses implicit conversion

2. invoie_date > '01-02-2019' : uses implicit conversion

3. CONCAT (qty_sold, invoice_date) : requires explicit conversion

4. qty_sold = '0554982' uses implicit conversion

5. qty_sold BETWEEN '101' AND '110' : uses implicit conversion

3 / 229

3.

View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks require subqueries? (Choose two.)

1. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE

2. Display the total number of products supplied by supplier 102 which have a product status of obsolete

3. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable

4. Display suppliers whose PROD_LIST_PRICE is less than 1000

5. Display the minimum PROD_LIST_PRICE for each product status

4 / 229

4.

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

1. sequences

2. undo

3. clusters

4. stored procedures

5. index

6. tables

5 / 229

5.

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

1. A block will always be eligible for inserts if the row is short enough to fit into the block

2. Update operations always attempt to find blocks with free space appropriate to the length of therow being updated

3. Insert operations always attempt to find blocks with free space appropriate to the length of therow being inserted

4. ASSM assigns blocks to one of four fullness categories based on what percentage of the block isallocated for rows

5. PCTFREE defaults to 10% for all blocks in all segments for all compression methods

6 / 229

6.

View the Exhibit and examine the description of the tables.

1. The statement will execute successfully and a new row will be inserted into the SALES table

2. ) The statement will fail if a row already exists in the SALES table for product 23.

3. The statement will fail because a subquery may not be contained in a VALUES clause.

4. A customer can exist in many countries.

5. A product can have a different unit price at different times.

6. The SALES table has five foreign keys.

7 / 229

7.

Examine the description of the MEMBERS table:

1. ORDER BY 1, LNAME DESC

2. WHERE city IN (‘%AN%’)

3. WHERE city LIKE ‘%AN%’

4. Order BY last_name DESC, city ASC

5. WHERE city = ‘%AN%’

6. ORDER BY 1, 2

8 / 229

8.

Examine this command:

Which two statements are true?

1. The "to" clause containing the new file name must be specified even if Oracle Managed Files(OMF) is used.

2. Compressed objects in SALES01.DBF will be uncompressed In SALES02.DBF after the move.

3. Tables with one or more extents in this data file may be queried during the execution of thiscommand.

4. DML may be performed on tables with one or more extents in this data file during the executionof this command.

5. It overwrites any existing file with the name SALES02.DBF.dbf in /u02 by default.

9 / 229

9.

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

1. Oracle Net Manager can be used to centrally configure listeners on any database server target

2. Enterprise Manager Cloud Control can be used to centrally configure listeners on any manageddatabase server

3. Oracle Net Manager can be used to locally configure naming methods on a database server

4. Enterprise Manager Cloud Control can be used to centrally configure net service names for anydatabase server target

5. The lsnrctl utility requires a listener.ora file to exist before it is started

6. The Oracle Net Configuration Assistant is only used when running the Oracle installer

10 / 229

10.

Which two statements are true about single row functions?

1. MOD : returns the remainder of a division operation

2. TRUNC : can be used only with NUMBER values

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

4. CEIL : can be used for positive and negative numbers

5. FLOOR : returns the smallest integer greater than or equal to a specified number

11 / 229

11.

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

1. It ignores NULLs

2. INTERSECT is of lower precedence than UNION or UNION ALL

3. Multiple INTERSECT operators are not possible in the same SQL statement

4. It processes NULLs in the selected columns

12 / 229

12.

Which two Oracle database space management features require the use of locally managed tablespaces?

1. Automatic data file extension (AUTOEXTEND)

2. Server-generated tablespace space alerts.

3. Oracle Managed Files (OMF)

4. Free space management with bitmaps.

5. Online segment shrink

13 / 229

13.

Which are two of the account management capabilities that can be configured using Oracle profiles? (Choose two.)

1. the ability to prevent a password from ever being reused

2. the maximum number of sessions permitted for a user before the password must be changed.

3. the maximum amount of CPU time allowed for a user’s sessions before their account is locked

4. the number of days for which an account may be logged in to one or more sessions before it is locked.

5. the number of days for which an account may be inactive before it is locked.

14 / 229

14.

Which three statements are true about Deferred Segment Creation in Oracle databases?

1. Indexes inherit the DEFERRED or IMMEDIATE segment creation attribute from their parent table

2. It is the default behavior for tables and indexes

3. Sessions may dynamically switch back and forth from DEFERRED to IMMEDIATE segment creation

4. It is supported for Index Organized Tables (IOTs) contained in locally managed tablespaces

5. It is supported for SYS-owned tables contained in locally managed tablespaces

15 / 229

15.

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?

1. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHEREcust_income_level != NULLAND due_amount != NULL;

2. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHEREcust_income_level IS NOT NULLAND due_amount IS NOT NULL;

3. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHEREcust_income_level NULLAND due_amount NULL;

4. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHEREIT Certification Guaranteed, The Easy Way!6cust_income_level != NULLAND cust_credit_level !=NULL;

5. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM customersWHEREcust_income_level IS NOT NULLAND cust_credit_limit IS NOT NULL;

16 / 229

16.

Examine this command:
ALTER DATABSE

MOVE DATAFILE ‘/u01/sales01.dbf’

TO ‘/u02/sales02.dbf’;

Which two statements are true?

1. Compressed objects in SALES01.DBF will be uncompressed in SALES02.DBF after the move

2. It overwrites any existing file with the name SALES02.DBF in /u02 by default

3. DML may be performed on tables with one or more extents in this data file during the execution of this command

4. The “TO” clause containing the new file name must be specified even if Oracle Managed Files (OMF) is used

5. Tables with one or more extents in this data file may be queried during the execution of this command

17 / 229

17.

Your database instance is started with an SPFILE. A PFILE is also available. You execute this command: ALTER SYSTEM SET DB_CACHE_SIZE=100K; Where Is the value changed?

1. in the SPFILE and in memory

2. in the SPFILE, SPFILE, and memory

3. in the SPFILE and SPFILEc

4. only In the SPFILE

5. only in memory

18 / 229

18.

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

1. Update operations always relocate rows into blocks with free space appropriate to the length of the row being updated

2. PCTFREE defaults to 10% for all blocks in all segments for all compression methods

3. The first block with enough free space to accommodate a row being inserted will always be used for that row

4. ASSM assigns blocks to one of four fullness categories based on what percentage of the block is allocated for rows

5. Insert operations always insert new rows into blocks with free space appropriate to the length of the row being inserted

19 / 229

19.

Which two statements are true about INTERVAL data types?

1. The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.

2. INTERVAL DAY TO SECOND columns support fractions of seconds.

3. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.

4. INTERVAL YEAR TO MONTH columns support yearly intervals.

5. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.

6. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.

20 / 229

20.

Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.)

1. The Oracle join syntax only supports right outer joins

2. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables

3. The SQL:1999 compliant ANSI join syntax supports natural joins

4. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax

5. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax

6. The Oracle join syntax supports natural joins

7. The Oracle join syntax supports creation of a Cartesian product of two tables

21 / 229

21.

Which three statements are true about the Automatic Diagnostic Repository (ADR)?

1. It is a file-based repository held outside any database.

2. It is held inside an Oracle database schema.

3. The ADR base is specified in the DIAGNOSTIC_DEST database parameter.

4. It is only used for Oracle Database diagnostic Information.

5. It can be used for problem diagnosis of a database when that database’s instance is down.

22 / 229

22.

Examine this command: SQL> ALTER TABLE ORDERS SHRINK SPACE COMPACT Which two statements are true?

1. Only queries are allowed on ORDERS while the shrink is executing.

2. Queries and DML statements are allowed on ORDERS while the shrink is executing.

3. Dependent indexes become UNUSABLE.

4. The high-water mark (HWM) of orders is adjusted.

5. The shrink operation causes rows to be moved to empty space starting from the beginning of theorders segment.

6. The shrink operation causes rows to be moved to empty space starting toward the end of theorders segment.

23 / 229

23.

Which three statements are true about data block storage in an Oracle Database?

1. Row data is stored starting at the end of the block.

2. An index block can contain row data.

3. A data block header is of a fixed length.

4. A block header contains a row directory pointing to all rows in the block.

5. A table block must always contain row data.

24 / 229

24.

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?

1. as SCOTT in BOSTON_DB and SYS in DALLAS_DB

2. as SCOTT in both the databases

3. as SCOTT in BOSTON_DB

4. as SCOTT in DALLAS_DB

5. as SYS in both the databases

25 / 229

25.

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 two types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package? (Choose two.)

1. analysis of privileges that a user has on their own schema objects

2. analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role

3. analysis of privileges that a user has on other schema’s objects

4. analysis of all privileges used by SYS user.

5. analysis of privileges granted directly to a role that are then used by a user who has been granted that role

26 / 229

26.

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

1. CEIL : can be used for positive and negative numbers

2. TRUNC : can be used with NUMBER and DATE values

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

4. MOD : returns the quotient of a division operation

5. FLOOR : returns the smallest integer greater than or equal to a specified number

27 / 229

27.

Which three statements are true regarding indexes? (Choose three.)

1. A UNIQUE index can be altered to be non-unique

2. An update to a table can result in updates to any or all of the table's indexes

3. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped

4. A table belonging to one user can have an index that belongs to a different user

5. An update to a table can result in no updates to any of the table's indexes

6. A SELECT statement can access one or more indices without accessing any tables

28 / 229

28.

View the Exhibit and examine the description of the tables. You execute this SQL statement:

Which three statements are true?

1. The sales table has five foreign keys.

2. The statement will fail because a subquery may not be contained in a values clause.

3. The statement will fall if a row already exists in the sales table for product 23.

4. The statement will execute successfully and a new row will be inserted into the sales table.

5. A product can have a different unit price at different times.

6. A customer can exist in many countries.

29 / 229

29.

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.)

1. The second ROLLBACK command replays the delete

2. The second ROLLBACK command undoes the insert

3. The second ROLLBACK command does nothing

4. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row

5. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed

30 / 229

30.

Examine the description of the product_details table:

Which two statements are true?

1. PRODUCT _price can be used in an arithmetic expression even if it has no value stored in it.

2. PRODUCT _ic can be assigned the PRIMARY KEY constraint.

3. PRODUCT_NAKE cannot contain duplicate values.

4. EXPIRY_ DATE contains the sysdate by default if no date is assigned to it.

5. PRODUCT _price contains the value zero by default if no value is assigned to it.

6. EXPIRY_ DATE cannot be used in arithmetic expressions.

31 / 229

31.

Which two statements are true about the SET VERIFY ON command?

1. It displays values for variables created by the DEFINE command

2. It can be used only in SQL*Plus

3. It displays values for variables prefixed with &&

4. It can be used in SQL Developer and SQL*Plus

5. It displays values for variables used only in the WHERE clause of a query

32 / 229

32.

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

1. two or more instances on separate servers all associated with one database

2. one instance on one server associated with two or more databases on the same server

3. one instance on one server not associated with any database

4. one instance on one server associated with two or more databases on separate servers

5. one instance on one server associated with one database

33 / 229

33.

The orders table has a column ORDER_DATE of data type date.
The default display format for a date Is DD-MON-RR.
Which two where conditions demonstrate the correct usage of conversion functions?

1. WHERE TO_CHAR(order_date, 'MON DD YYYY') = 'JAN 20 2019'

2. WHERE order_date > TO_DATE<ADD_MONTHS(SYSDATE, c) , 'MON DD YYYY')

3. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')

4. WHERE order date > TO DATE('JUL 10 2018', *MON DD YYYY')

5. WHERE order_date IN (TO_DATE ('Oct 21 2018', 'Mon DD YYYY'), TC_CHAR('Nov 21 2018', 'Mon DD YYYY'))

34 / 229

34.

Which three are types of segments in a Oracle database?

1. stored procedures.

2. Sequences

3. views

4. clusters

5. tables

6. Undo

35 / 229

35.

The customers table has a cust_last_name column of data type varchar2.
The table has two rows whose "jst_last_name values are Andersen and Ausson.
Which query produces output for cust_last_xame containing Oder for the first row and Aus for the
second?

1. SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name), 'An', 'O*)) FROM customers;

2. SELECT REPLACE(SUBSTR(cust_last_name, -3), 'An', 'O') FROM customers;

3. SELECT REPLACE(REPLACE(cust_last_name, 'son', ''), 'An', 'O'> FROM customers;

4. SELECT REPLACE<TRIM FROM customers;

36 / 229

36.

The ORCL database has RESUMABLE_TIMEOUT = 7200 and DEFERED_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.)

1. Set DEFERRED_SEGMENT_CREATION to TRUE

2. Set AUTOEXTEND ON for data files in DATA

3. Grant UNLIMITED TABLESPACE to U1

4. Increase U1’s quota sufficiently in DATA

5. Drop other U1 objects in DATA

6. Add a data file to DATA

37 / 229

37.

Which three statements are true about Deferred Segment Creation in Oracle databases?

1. Indexes inherit the deferred or immediate segment creation attribute from their parent table

2. Sessions may dynamically switch back and forth from deferred to immediate segment creation.

3. It Is supported for Index Organized Tables (IOTs) contained in locally managed tablespaces.

4. It Is the default behavior for tables and indexes.

5. It is supported for SYS-owned tables contained in locally managed tablespaces.

38 / 229

38.

Which three activities are recorded in the database alert log? (Choose three.)

1. deadlock errors

2. block corruption errors

3. Data Definition Language (DDL) statements

4. non-default database parameters

5. session logins and logouts

39 / 229

39.

You start your database instance in NOMOUNT state Which two actions are performed?

1. SYS can access the database

2. The consistency of the database is checked

3. All required background processes are started

4. Memory is allocated for the SGA

5. The control files are opened

40 / 229

40.

Which Three activities are recorded in the database alert log?

1. block corruption errors

2. Data Definition Language (DDL) statements

3. deadlock errors

4. session logins and logouts

5. non-default database parameters

41 / 229

41.

Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER.
Examine these SQL statements:Statement 1:
SELECT MAX(unit_price * quantity) “Maximum Order” FROM order_items;
Statement 2:
SELECT MAX(unit_price * quantity) “Maximum Order” FROM order_items
GROUP BY order_id;
Which two statements are true?

1. Statement 1 returns only one row of output.

2. Both the statements give the same output.

3. Statement 2 may return multiple rows of output.

4. Statement 2 returns only one row of output.

5. Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.

42 / 229

42.

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

1. Undo segments can be stored in the SYSTEM tablespace

2. Undo segments can extend when a transaction fills the last extent of the undo segment

3. Undo segments can be stored in the SYSAUX tablespace

4. Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment

5. A single transaction may use multiple undo segments simultaneously

43 / 229

43.

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

1. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.

2. Partition key columns cannot be dropped.

3. A column that is set to UNUSED still counts towards the limit of 1000 columns per table

4. A DROP COLUMN command can be rolled back

5. An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.

6. An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.

44 / 229

44.

Examine these commands:

Which two statements are true about the sqlldr execution?

1. It overwrites the data for Alan and adds data for Curl and Bob

2. It overwrites all data in EMP with data from EMP.DAT.

3. It appends data from EMP.DAT to EMP.

4. It generates a sql script that it uses to load data from EMP.DAT to EMP

5. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations

45 / 229

45.

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

1. the maximum number of sessions permitted for a user before the account is locked

2. the number of days for which an account may be inactive before it is locked

3. the number of hours for which an account is locked after the configured number of login attempts has been reached

4. the number of password changes required within a period of time before a password can be reused

5. the number of days for which an account is locked after the configured number of login attempts has been reached

6. the ability to prevent a password from ever being reused

7. the maximum amount of CPU time allowed for a user's sessions before their account is locked

46 / 229

46.

Which three statements are true about inner and outer joins?

1. A full outer join must use Oracle syntax

2. Outer joins can only be used between two tables per query

3. A left or right outer join returns only unmatched rows

4. Outer joins can be used when there are multiple join conditions on two tables

5. A full outer join returns matched and unmatched rows

6. An inner join returns matched rows

47 / 229

47.

Examine the description of the employees table: Which query requires explicit data type conversion?

1. SELECT join_date FROM employees WHERE join_date > '10-02-2018';

2. SELECT join_date + '20' FROM employees; SELECT salary * '120.50' FROM employees;

3. SELECT join_date I I ' ' II salary FROM employees;

4. SELECT SUBSTR<join_date, 1, 2) - 10 FROM employees;

48 / 229

48.

In the PROMOTIONS table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR.

Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?

1. TO_NUMBER(PROMO_BEGIN_DATE) – 5 will return a number.

2. PROMO_BEGIN_DATE – 5 will return a date.

3. PROMO_BEGIN_DATE – SYSDATE will return a number

4. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date.

5. PROMO_BEGIN_DATE – SYSDATE will return an error.

49 / 229

49.

You execute this command:

1. It continues to run in the background

2. You can reattach to it but not monitor it.

3. It is paused and can be resumed.

4. It terminates.

5. You can reattach to it and monitor it

50 / 229

50.

Which two statements are true about UNDO and REDO?

1. The generation of UNDO generates REDO

2. The generation of REDO generates UNDO

3. DML modifies Oracle database objects and only generates REDO

4. DML modifies Oracle database objects and only generates UNDO

5. DML modifies Oracle database objects and generates UNDO and REDO

51 / 229

51.

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

1. It must be an inner join

2. The query must use two different aliases for the table

3. It must be an equijoin

4. It can be an outer join

5. The ON clause must be used

6. The ON clause can be used

52 / 229

52.

Which statement is true about aggregate functions?

1. Aggregate functions can be used in any clause of a SELECT statement

2. The MAX and MIN functions can be used on columns with character data types

3. The AVG function implicitly converts NULLS to zero

4. Aggregate functions can be nested to any number of levels

53 / 229

53.

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.)

1. The initialization parameter file is read

2. The Oracle background processes are started

3. The online redo logs are opened

4. The alert log records the execution details

5. The control file is read

6. The online data files are opened

54 / 229

54.

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

1. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session

2. when issuing a DML statement after a DML statement failed in the same session

3. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session

4. when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session

5. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session

6. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session

55 / 229

55.

Which two statements are true about views used for viewing tablespace and datafile information?

1. V$TABLESPACE displays information about tablespaces contained in the data dictionary

2. A datafile can be renamed when the database is in MOUNT state and the new file name is displayed when querying DBA_DATA_FILES after the database is opened

3. Tablespace free space can be viewed in V$TABLESPACE

4. Tablespace free space can be viewed in DBA_TABLESPACES

5. V$TABLESPACE displays information that is contained in the controlfile about tablespaces

56 / 229

56.

Which three statements are true about the Automatic Diagnostic Repository (ADR)?

1. It can be used for problem diagnosis of a database when that database's instance is down.

2. It Is held Inside an Oracle database schema.

3. It is a file-based repository held outside any database.

4. It is only used for Oracle Database diagnostic information.

5. The ADR base is specified In the diagnostic_dest database parameter.

57 / 229

57.

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.)

1. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details offractional seconds

2. SYSDATE and CURRENT_DATE return the current date and time set for the operating system ofthe database server

3. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE

4. SYSDATE can be queried only from the DUAL table

5. CURRENT_DATE returns the current date and time as per the session time zone

6. SYSDATE can be used in expressions only if the default date format is DD-MON-RR

58 / 229

58.

Which three statements are true about views in an Oracle database? (Choose three.)

1. Deleting one or more rows using a view whose defining query contains a GROUP BY clause willcause an error

2. Tables in the defining query of a view must always exist in order to create the view

3. Data Manipulation Language (DML) can always be used on views

4. The WITH CHECK clause prevents certain rows from being updated or inserted

5. The WITH CHECK clause prevents certain rows from being displayed when querying the view

6. Views can be updated without the need to re-grant privileges on the view

7. Inserting one or more rows using a view whose defining query contains a GROUP BY clause willcause an error

59 / 229

59.

In one of your databases, you create a user, HR, and then execute this command:
GRANT CREATE SESSION TO hr WITH ADMIN OPTION;

Which three actions can HR perform?

1. Execute DML statements in the HR schema

2. Execute DDL statements in the HR schema

3. Revoke the CREATE SESSION privilege from user HR

4. Revoke the CREATE SESSION privilege from other users

5. Log in to the database instance

6. Grant the CREATE SESSION privilege with ADMIN OPTION to other users

60 / 229

60.

The SALES_Q1 and USERS tablespaces exist in one of your databases and TEMP is a temporary tablespace.

Segment creation is not deferred. You execute this command:

Which three statements must be true so that the SALES user can create tables in SALES_Q1? (Choose three.)

1. The sales user must have been granted the CREATE TABLE privilege

2. The sales user must have been granted the CREATE SESSION privilege

3. The sales user must have a quota on the TEMP tablespace

4. The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of all tables they plan to create in their schema

5. The sales user must have their quota on the users tablespace removed

6. The sales user must have a quota on the SALES_Q1 tablespace to hold all the rows to be inserted into any table in their schema

61 / 229

61.

Examine the description of the EMPLOYEES table:

Which query is valid?

1. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

2. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;

3. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;

4. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;

62 / 229

62.

Which three statements are true about external tables in Oracle 18c and later releases? (Choose three.)

1. They cannot be partitioned

2. External table files can be used for other external tables in a different database

3. The ORACLE_DATAPUMP access driver can be used to load data into a database from an externaltable

4. The ORACLE_LOADER access driver can be used to unload data from a database into an externaltable

5. The ORACLE_DATAPUMP access driver can be used to unload data from a database into anexternal table

6. They support UPDATEs but not INSERTs and DELETEs

63 / 229

63.

The sales table has columns prod_id and quantity_sold of data type number In two queries
execute successfully?

1. SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(-) > 10;

2. SELECT COUNT|prod_id> FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;

3. SELECT prod_id FROM sales WHERE quantity_sold > 5S000 AND COUNT(-) > 10 GROUP BY prod_id HAVING COUNT(-) > 10;

4. SELECT COUNTlprod_id> FROM sales GROUP BY prod_id WHERE quantity_Sold > 55000;

5. SELECT prod_id FROM sales WHERE quantity_scld > 55000 AND COUNT(-) > 10 GROUP BY COUNT(-) > 10;

64 / 229

64.

Examine this SQL statement:
SELECT cust_id, cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses, any one of which can complete the query successfully. (Choose
three.)

1. ORDER BY 2, cust_id

2. ORDER BY CUST_NO

3. ORDER BY "Last Name"

4. ORDER BY "CUST_NO"

5. ORDER BY 2, 1

65 / 229

65.

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

1. Undo segments can extend when a transaction fills the last extent of the undo segment

2. Undo segments must be stored in a SMALLFILE tablespace

3. Undo segments have a minimum of three extents

4. Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment

5. An undo segment may be used by multiple transactions simultaneously

6. A single transaction may use multiple undo segments simultaneously

7. Undo segments must be stored in a BIGFILE tablespace

66 / 229

66.

Which two statements are true about Database Instances and Real Application Clusters (RAC)?

1. Two RAC databases can share their instances.

2. A RAC database must have two or more instances.

3. A RAC database can have one instance

4. A RAC database must have three or more instances.

5. A RAC database can have Instances on separate servers.

67 / 229

67.

Examine this description of the books table containing 100 rows:

Now examine this sequence of statements Issued In a new session;

Examine this description of the books table containing 100 rows:
Which three statements are true?

1. The first rollback command leaves the inserted row locked.

2. The first rollback command leaves the table's 100 original rows locked.

3. The first RollBack command restores the row that was inserted.

4. The first rollback command restores the 100 rows that were in the table originally.

5. The second rollback command restores the 100 rows that were in the table originally.

6. The second rollback command rolls back the rollback to savepoint a command.

7. The second rollback command restores the row that was inserted.

68 / 229

68.

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/3/6 14:45:17

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.)

1. Drop other U1 objects in DATA

2. Increase U1's quota sufficiently in DATA

3. Grant UNLIMITED TABLESPACE to U1

4. Set AUTOEXTEND ON for data files in DATA

5. Add a data file to DATA

6. Set DEFERRED_SEGMENT_CREATION to TRUE

69 / 229

69.

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.)

1. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

2. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;

3. SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;

4. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;

5. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;

70 / 229

70.

Examine these commands:

Which two statements are true about the sqiidr execution?

1. It generates a log that contains control file entries, which can be used with normal SQL'Loader operations.

2. It appends data from EMP.DAT TO EMP.

3. It overwrites the data for Alan and adds data for Curl and Bob

4. It generates a sqi script that it uses to load data from EMP.DAT TO EMP.

5. It overwrites all data in emp with data from emp.dat.

71 / 229

71.

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?

1. The value is changed only in the PFILE and takes effect at the next instance startup

2. It fails because the SCOPE clause is missing

3. The value is changed for the current instance and in the PFILE

4. Change is applied to the current instance, but does not persist after instance restart

72 / 229

72.

The STORES table has a column START_DATE of data type DATE, containing the date the row was inserted.
You only want to display details of rows where START_DATE is within the last 25 months.
Which WHERE clause can be used?

1. WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25

2. WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25

3. WHERE ADD_MONTHS(start_date, 25) <= SYSDATE

4. WHERE TO_NUMBER(start_date – SYSDATE) <= 25

73 / 229

73.

Which two statements are true about the configuration and use of UNDO_RETENTION with GURANTEED RETENTION? (Choose two.)

1. Active UNDO is always retained.

2. UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired UNDO.

3. UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.

4. Unexpired UNDO is always retained.IT Certification Guaranteed, The Easy Way!5

5. UNDO_RETENTION specifies how long all types of UNDO are retained.

74 / 229

74.

Which two statements are true about views?

1. The WITH CHECK clause prevents certain rows from being displayed when querying the view

2. Views can be indexed

3. Views can be updated without the need to re-grant privileges on the view

4. A view must only refer to tables in its defining query

5. The WITH CHECK clause prevents certain rows from being updated or inserted in the underlying table through the view

75 / 229

75.

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?

1. T1 only

2. T1 and an index segment created for the primary key only

3. T1, an index segment for the primary key, a LOB segment, and a lobindex segment

4. T1, an index segment for the primary key, and a LOB segment only

5. no segments are created

76 / 229

76.

Which three files are used by conventional path SQL*Loader when the TABLE option is not specified? (Choose three.)

1. password files

2. input files

3. dump files

4. control files

5. bad files

77 / 229

77.

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

1. DML modifies Oracle database objects and generates UNDO and REDO

2. The generation of UNDO generates REDO

3. DML modifies Oracle database objects and only generates REDO

4. DML modifies Oracle database objects and only generates UNDO

5. The generation of REDO generates UNDO

78 / 229

78.

Examine this command:
SQL> ALTER TABLE ORDERS SHRINK SPACE COMPACT

Which two statements are true?

1. The high-water mark (HWM) of ORDERS is adjusted

2. The SHRINK operation causes rows to be moved to empty space starting toward the end of the ORDERS segment

3. Dependent indexes become UNUSABLE

4. Only queries are allowed on ORDERS while the SHRINK is executing

5. The SHRINK operation causes rows to be moved to empty space starting from the beginning of the ORDERS segment

6. Queries and DML statements are allowed on ORDERS while the SHRINK is executing

79 / 229

79.

Examine this description of the TRANSACTIONS table:

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

1. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 "DUESAMOUNT" FROM transactions;

2. SELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount + 100 'DUES AMOUNT' FROM transactions;

3. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;

4. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROMtransactions;

5. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROMtransactions;

80 / 229

80.

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?

1. It must resolve to a valid connect descriptor in the client's tnsnames.ora file

2. It must be the value of the SERVICE_NAMES parameter on the client side

3. It must be the name of the database to whose instance HR wishes to connect

4. It must resolve to a valid connect descriptor in the server's tnsnames.ora file

5. It must be the name of the server running the database to whose instance HR wishes to connect

81 / 229

81.

Examine this SQL statement:
SELECT cust_id, cust_last_name "Last Name" FROM customers
WHERE country_id = 10 UNION
SELECT cust_id CUST_NO, cust_last_name FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses, any one of which can complete the query successfully. (Choose three.)

1. ORDER BY "CUST_NO"

2. ORDER BY CUST_NO

3. ORDER BY 2, cust_id

4. ORDER BY 2, 1

5. ORDER BY "Last Name"

82 / 229

82.

You currently have an active transaction in your session and have been granted select access
to vstransaction.

In which three situations will re-executing this query still return a row but with a different XID,
indicating a new transaction has started?

1. after successfully executing a truncate statement followed by a DML statement

2. after successfully executing a DML statement following a failed DML statement

3. after successfully executing a commit or rollback followed by a select statement

4. after successfully executing a create table as select statement followed by a select for update statement

5. after successfully executing a commit or rollback followed by a DML statement

6. after successfully executing a create table statement followed by a create index statement

83 / 229

83.

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

1. It displays values for variables created by the DEFINE command

2. It can be used in SQL Developer and SQL*Plus

3. It displays values for variables prefixed with &&

4. It can be used only in SQL*Plus

5. It displays values for variables used only in the WHERE clause of a query

84 / 229

84.

Examine the description of the EMPLOYEES table:

Name Null? Type

============================ ========== =======================

EMP_ID NOT NULL NUMBER

EMP_NAME VARCHAR2(10)

DEPT_ID NUMBER(2)

SALARY NUMBER(8,2)

JOIN_DATE DATE

NLS_DATE_FORMAT is set to DD-MON-YY

Which query requires explicit data type conversion?

1. SELECT join_date + ‘20’ FROM employees;

2. SELECT salary + ‘120.50’ FROM employees;

3. SELECT join_date || ‘ ‘ || salary FROM employees;

4. SELECT join_date FROM employees WHERE join_date > ’10-02-2018’;

5. SELECT SUBSTR(join_date, 1, 2) – 10 FROM employees;

85 / 229

85.

Which two statements are true about the DUAL table? (Choose two.)

1. It can be accessed by any user who has the SELECT privilege in any schema

2. It can be accessed only by the SYS user

3. It can display multiple rows but only a single column

4. It can be used to display only constants or pseudo columns

5. It consists of a single row and single column of VARCHAR2 data type

6. It can display multiple rows and columns

86 / 229

86.

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

1. A synonym can be available to all users

2. Any user can drop a PUBLIC synonym

3. A synonym cannot be created for a PL/SQL package

4. A synonym created by one user can refer to an object belonging to another user

5. A SEQUENCE can have a synonym

87 / 229

87.

Which two statements are true about Enterprise Manager (EM) Express?

1. You can shut down a database instance using EM Express.

2. You cannot start up a database instance using EM Express.

3. EM Express uses a separate repository database to store target database metadata.

4. By default, EM express is available for a database after database creation using DBCA.

5. You can use a single instance of EM Express to manage multiple database running on the same server

88 / 229

88.

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

1. Table aliases can improve performance

2. The BETWEEN condition always performs better than using the >= and <= conditions

3. The BETWEEN condition always performs less well than using the >= and <= conditions

4. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax

5. The join syntax used makes no difference to performance

89 / 229

89.

Examine the description of the SALES1 table:

1. INTERSECT

2. UNION

3. SUBTRACT

4. MINUS

5. UNION ALL

90 / 229

90.

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

1. The + binary operator has the highest precedence in an expression in a SQL statement

2. NULLS influence the precedence of operators in an expression

3. Multiple parentheses can be used to override the default precedence of operators in anexpression

4. The concatenation operator | | is always evaluated before addition and subtraction in anexpression

5. Arithmetic operators with equal precedence are evaluated from left to right within an expression

91 / 229

91.

Which two statements are true about the configuration and use of UNDO_RETENTION with no GUATANTEED RETENTION?

1. UNDO_RETENTION specifies how long all types of UNDO are retained.

2. Active UNDO is always retained.

3. Unexpired UNDO is always retained.

4. UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired UNDO.

5. UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.

92 / 229

92.

Examine the description of the BOOKS_TRANSACTIONS table:

1. WHERE borrowed_date = SYSDATE AND {transaction_type = 'RM* AND (raeraber_id = 'A101' OR member_id = 'A102));

2. WHERE borrowed_date = SYSDATE AND (transaction_ype = 'RM' AND .-neraber_id = 'A101' OR member_id = 'A102');

3. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM* OR member_id IN ('A101', 'A102'));

4. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM*) OR member_id IN ('A101', 'A102');

93 / 229

93.

The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully? (Choose two.)

1. SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers;

2. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;

3. SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers;

4. SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;

5. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;

94 / 229

94.

Which is the default column or columns for sorting output from compound queries using SET operators such as intersect In a SQL statement?

1. the first number or varchar2 column in the last select of the compound query

2. the first number column in the first select of the compound query

3. the first varchab2 column in the first select of the compound query

4. the first column in the last select of the compound queryIT Certification Guaranteed, The Easy Way!4

5. the first column in the first select of the compound query

95 / 229

95.

You execute this command:

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

1. It will always have a 32K blocksize

2. AUTOEXTEND is possible for the datafile

3. It will be a dictionary-managed tablespace by default

4. Additional data files may not be added

5. It must be bigger than the largest SMALLFILE tablespace

96 / 229

96.

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

1. It can be used only from SQL*Plus

2. It displays all constraints that are defined for each column

3. It can be used from SQL Developer

4. It can be used to display the structure of an existing view

5. It displays the NOT NULL constraint for any columns that have that constraint

6. It displays the PRIMARY KEY constraint for any column or columns that have that constraint

97 / 229

97.

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?

1. UNION

2. SUBTRACT

3. INTERSECT

4. UNION ALL

5. MINUS

98 / 229

98.

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

1. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement

2. Rolling back to a SAVEPOINT can undo a TRUNCATE statement

3. Only one SAVEPOINT may be issued in a transaction

4. Rolling back to a SAVEPOINT can undo a DELETE statement

5. A SAVEPOINT does not issue a COMMIT

99 / 229

99.

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

1. Execute DDL statements in the HR schema

2. Execute DML statements in the HR schema

3. Log in to the database instance

4. Revoke the CREATE SESSION privilege from user HR

5. Revoke the CREATE SESSION privilege from other users

6. Grant the CREATE SESSION privilege with ADMIN OPTION to other users

100 / 229

100.

Which two statements are true about the Oracle join and ANSI join syntax?

1. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.

2. The Oracle join syntax lacks the ability to do outer joins.

3. The Oracle join syntax supports creation of a Catesian product of two tables.

4. The Oracle join syntax perform better than the SQL:1999 compliant ANSI join syntax.

5. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian prodcut of two tables.

101 / 229

101.

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

1. Load Balancing requires the use of a name server

2. Source Routing requires the use of a name server

3. Load Balancing can balance the number of connections to dispatchers when using a Shared Server configuration

4. Source Routing enables the use of Connection Manager (CMAN) which enables network traffic to be routed through a firewall

5. Connect Time Failover requires the connect string to have two or more listener addresses configured

6. Connect Time Failover requires the use of Transparent Application Failover (TAF)

102 / 229

102.

What is true about non-equijoin statement performance?

1. The BETWEEN condition used with an non-equijoin sometimes performs better than using the >= and <= conditions.

2. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.

3. The BETWEEN condition used with an non-equijoin always performs better than when using the >= and <= conditions.

4. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.

5. The join syntax used makes no difference to performance.

103 / 229

103.

Which two statements are true about interval data types?

1. INTERVAL year to month columns only support monthly Intervals within a single year.

2. INTERVAL year to month columns only support monthly intervals within a range of years.

3. INTERVAL day to second columns support fractions of seconds.

4. The year field in an interval year to month column must be a positive value.

5. INTERVAL year TO month columns support yearly intervals.

6. The value in an interval day to SECOND column can be copied into an interval year to month column.

104 / 229

104.

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

1. It supports diagnostics for Oracle Clusterware

2. The ADR base defaults to $ORACLE_HOME/rdbms/admin ifneither DIAGNOSTIC_DEST nor ORACLE_BASE is set

3. It supports diagnostics for Automatic Storage Management (ASM)

4. The ADR base defaults to $ORACLE_HOME/dbs if the DIAGNOSTIC_DEST parameter and the ORACLE_BASE environment variable are not set

5. It is held inside an Oracle database schema

105 / 229

105.

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

1. To shrink a table it must have row movement enabled

2. It always eliminates all migrated rows if any exist in the table

3. To shrink a table it must have a PRIMARY KEY constraint

4. To shrink a table it must have a UNIQUE KEY constraint

5. It must be in a tablespace that uses Automatic Segment Space Management (ASSM)

6. It is not possible to shrink either indexes or Index Organized Tables (IOTs)

106 / 229

106.

Examine the description of the PRODUCT_DETAILS table:

1. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.

2. EXPIRY_DATE cannot be used in arithmetic expressions.

3. PRODUCT_NAME cannot contain duplicate values.

4. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.

5. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.

6. PRODUCT_ID can be assigned the PRIMARY KEY constraint.

107 / 229

107.

Examine these commands:

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

1. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations

2. It generates a sql script that it uses to load data from EMP.DAT to EMP

3. It uses the database buffer cache to load data

4. It overwrites data in EMP with data in EMP.DAT

5. It appends data from EMP.DAT to EMP

108 / 229

108.

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

1. Enable flashback database for an existing database

2. Configure a nonstandard block size for a new database

3. Change the standard block size of an existing database

4. Register a new database with an available Enterprise Manager Management server

5. Configure incremental backups for a new database

109 / 229

109.

Which two statements are true regarding indexes?

1. An update to a table can result in no updates to any of the table’s indexes

2. A table belonging to one user cannot have an index that belongs to a different user

3. An update to a table can result in updates to any or all of the table’s indexes

4. The RECYCLE BIN never contains indexes

5. A non-unique index can be altered to be unique

110 / 229

110.

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?

1. Set OS_AUTHENT_PREFIX to OPS$

2. Alter user KING to be IDENTIFIED EXTERNALLY

3. Have the OS administrator add KING to the OSDBA group

4. Unset REMOTE_LOGIN_PASSWORDFILE

5. Grant DBA to KING

111 / 229

111.

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

1. sending shared server session responses back to requesters on the appropriate connection

2. receiving inbound requests from processes using shared server connections

3. writing inbound request to the common request queue from all shared server connections

4. checking for outbound shared server responses on the common outbound response queue

5. broadcasting shared server session responses back to requesters on all connections

6. sending each connection input request to the appropriate shared server input queue

112 / 229

112.

Examine the data in the CUST_NAME column of the CUSTOMERS table:

CUST_NAME
----------------------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer

6 rows selected.
You want to display the CUST_NAME values where the last name starts with Mc or MC
Which two WHRE clauses give the required result?

1. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1 ) LIKE 'Mc%' OR 'MC%'

2. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1 ) LIKE 'Mc%'

3. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'Mc%'

4. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE UPPER('MC%')

5. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) IN ('MC%', 'Mc%')

113 / 229

113.

Which two statements are true about time zones, date data types, and timestamp data type in an Oracle database?

1. The DATE datatype returns data with time zone information

2. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

3. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row

4. The CURRENT_TIMESTAMP function returns data without time zone information

5. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

114 / 229

114.

Which two statements are true about space-saving features in an Oracle Database?

1. An index created with the UNUSABLE attribute has no segment

2. A table that is truncated will always have all of its extents removed

3. Private Temporary Tables (PTTs) when used, are always dropped at the next COMMIT statement

4. Private Temporary Tables (PTTs) store metadata in memory only

5. An index that is altered to be UNUSABLE will retain its segment

115 / 229

115.

Which two statements are true about Oracle synonyms?

1. A synonym has an object number

2. All private synonym names must be unique in the database

3. Any user can create a PUBLIC synonym

4. A synonym can have a synonym.

5. A synonym can be created on an object in a package

116 / 229

116.

Which three statements are true about roles?

1. Roles may be granted to roles.

2. The SET ROLE statement can disable one or more roles for a session.

3. Roles must be password protected.

4. The SET ROLE statement can enable one or more roles for a session.

5. Object privileges may not be granted to roles.

6. All roles granted to a user are set on by default when the user logs in,

117 / 229

117.

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

1. Column names in each SELECT in the compound query can be different

2. INTERSECT returns rows common to both sides of the compound query

3. INTERSECT ignores NULLs

4. The number of columns in each SELECT in the compound query can be different

5. Reversing the order of the intersected tables can sometimes affect the output

118 / 229

118.

Application PDBs, SALES_APP1 and SALES_APP2, must be created and they must access common tables f the
SALES_APP application
Examine these steps:
1 Install the SALES_APP application, including the common tables, in the application rt
2 Install the SALES_APP application in the application rt and the common tables in both the CDB rt and the
application rt
3 Create an application seed
4 Install the SALES_APP application in the application seed
5 Create the SALES_APP1 and SALES_APP2 application PDBs
6 Sync the SALES_APP1 and SALES_APP2 application PDBs with the application rt
7 Sync the SALES_APP1 and SALES_APP2 application PDBs with the application seed
8 Sync the application seed with the application rt

Which are the minimum required steps in the corrects sequence?

1. 1, 5, 6

2. 3, 4, 1, 6, 8

3. 1, 3, 5, 6, 7

4. 1, 3, 5, 7

5. 2, 5, 6

119 / 229

119.

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

1. The names and number of columns must be identical for all SELECT statements in the query.

2. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.

3. The number, but not names, of columns must be identical for all SELECT statements in the query.

4. The data type group of each column returned by the second query must match the data type of the corresponding column returned by the first query.

5. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.

120 / 229

120.

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.)

1. The first column in the first SELECT of the compound query with the UNION operator is used bydefault to sort output in the absence of an ORDER BY clause

2. Each SELECT statement in the compound query can have its own ORDER BY clause

3. Each SELECT statement in the compound query must have its own ORDER BY clause

4. Only column names from the first SELECT statement in the compound query are recognized

5. Column positions must be used in the ORDER BY clause

121 / 229

121.

. The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.
The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson.
Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

1. SELECT REPLACE(REPLACE(cust_last_name, 'son', ''), 'An', 'O') FROM customers;

2. SELECT INITCAP(REPLACE(TRIM('son' FROM cust_last_name), 'An', 'O')) FROM customers;

3. SELECT REPLACE(SUBSTR(cust_last_name, -3), 'An', 'O') from customers;

4. SELECT REPLACE(TRIM(TRAILING 'son' FROM cust_last_name), 'An', 'O') FROM customers;

122 / 229

122.

Which three statements are true about a self join?

1. The ON clause must be used

2. It must be an equijoin

3. The query must use two different aliases for the table

4. It must be an inner join

5. It can be an outer join

6. The ON clause can be used

123 / 229

123.

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

1. An index that is altered to be UNUSABLE will retain its segment

2. If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next COMMIT OR ROLLBACK statement

3. A table that is truncated will always have its segment removed

4. Private Temporary Tables (PTTS) store metadata in memory only

5. An index created with the UNUSABLE attribute has no segment

124 / 229

124.

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?

1. SELECT &&col1, &&col2FROM &tableWHERE &&condition;

2. SELECT &&col1, &&col2FROM &tableWHERE &&condition = &&cond;

3. SELECT &col1, &col2FROM &&tableWHERE &condition;

4. SELECT &col1, &col2FROM "&table"WHERE &condition;

5. SELECT '&&col1', '&&col2'FROM &tableWHERE '&&condition' = '&cond';

125 / 229

125.

Which two statements are true about GLOBAL TEMPORARY TABLES?

1. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

2. A GLOBAL TEMPORARY TABLE’s definition is available to multiple sessions.

3. GLOBAL TEMPORARY TABLE space allocation occurs at session start.

4. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

5. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

126 / 229

126.

Which three actions are ways to apply the principle of least privilege?

1. revoking execute privilege on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from the PUBLIC user

2. setting the REMOTE_OS_AUTHENT parameter to true

3. setting the 07_DICTIONARY_ACCESSBILITY parameter to true

4. enabling Unified Auditing

5. using Access Control Lists (ACLs)

6. ) revoking execute privilege on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from the SYSTEM user

127 / 229

127.

Which three statements are true about using SQL*Plus?

1. It has both command-line and graphical user interfaces (GUI)

2. It can run scripts entered at the SQL prompt

3. It can run scripts passed to it by a shell script

4. It must be downloaded from the Oracle Technology Network (OTN)

5. It can run Recovery Manager (RMAN) commands

6. It has its own commands that are separate from any SQL statements

128 / 229

128.

Which three statements are true about using SQL*Plus?

1. It can run scripts passed to it by a shell script.

2. It must be downloaded from the Oracle Technology Network (OTN).

3. It can run Recovery Manager (RMAN) commands.

4. It has its own commands that are separate from any SQL statements.

5. It has both command-line and graphical user interfaces (GUI).

6. It can run scripts entered at the SQL prompt.

129 / 229

129.

Which compression method is recommended for Direct-Path Insert operations

1. COLUMN STORE COMPRESS BASIC

2. COLUMN STORE COMPRESS ADVANCED

3. ROW STORE COMPRESS ADVANCED

4. ROW STORE COMPRESS BASIC

130 / 229

130.

Which two tasks can you perform using DBCA for databases?

1. Register a new database with an available Enterprise Manager Management server.

2. Configure a nonstandard block size for a new database.

3. Enable flashback database for an existing database.

4. Configure incremental backups for a new database.

5. Change the standard block size of an existing database.

131 / 229

131.

Which two actions can you perform using DBCA for an existing database?

1. Change the server mode from dedicated to shared, and vice versa.

2. Change the character set.

3. Create a template that can be used to clone the database.

4. Create nonstandard block size tablespaces.

5. Create an additional listener.

132 / 229

132.

Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)

1. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

2. A TIMESTAMP data type column contains information about year, month, and day

3. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

4. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the timezone of the session that inserted the row

5. The CURRENT_TIMESTAMP function returns data without time zone information

133 / 229

133.

Which two statements are true about substitution variables?

1. A substitution variable can be used only in a select statement.

2. A substitution variable used to prompt for a column name must be enclosed in double quotation marks.

3. A substitution variable used to prompt for a column name must be enclosed in single quotation marks.

4. A substitution variable prefixed with & always prompts only once for a value in a session.

5. A substitution variable can be used with any clause in a select statement.

6. A substitution variable prefixed with to prompts only once for a value in a session unless it is set to undefined in the session

134 / 229

134.

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

1. It displays values for variables created by the DEFINE command

2. It displays values for variables used only in the WHERE clause of a query

3. It can be used in SQL Developer and SQL*Plus

4. It can be used only in SQL*Plus

5. It displays values for variables prefixed with &&

135 / 229

135.

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

1. All tablespaces may have one or more data files

2. A smallfile tablespace might be bigger than a bigfile tablespace

3. The extents of a segment must always reside in the same datafile

4. A segment might have only one extent

5. A segment's blocks can be of different sizes

6. A segment can span multiple data files in some tablespaces

7. Segments can span multiple tablespaces

136 / 229

136.

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

1. COLUMN STORE COMPRESS FOR ARCHIVE LOW

2. ROW STORE COMPRESS BASIC

3. COLUMN STORE COMPRESS FOR ARCHIVE HIGH

4. COLUMN STORE COMPRESS FOR QUERY LOW

5. ROW STORE COMPRESS ADVANCED

137 / 229

137.

Which two statements are true about the tools used to configure Oracle Net Services

1. The lsnrctl utility requires a listener.ora file to exist before it is started.

2. Oracle Net Manager can be used to locally configure naming methods on a database server.

3. Oracle Net Manager can be used to centrally configure listeners on any database server target.

4. The Oracle Net Configuration Assistant is only used when running the Oracle Installer.

5. Enterprise Manager Cloud Control can be used to centrally configure net service names for any database server target

138 / 229

138.

Which two queries execute successfully?

1. SELECT NULLIF(100,100) FROM DUAL;

2. SELECT COALESCE (100, 'AM FROM DUAL;

3. SELECT NULLIF(NULL, 100) FROM DUAL;

4. SELECT 2TOLLIF(100, 'A') FROM DUAL;

5. SELECT COALESCE(100, NULL, 200) FROM DUAL;

139 / 229

139.

Which two statements are true about Enterprise Manager Database Express?

1. It can be used to switch a database into ARCHIVELOGMODE

2. It is available only when the database is open

3. The same port number can be used for Database Express configurations for databases on different hosts

4. It can be used to perform database recovery when database is mounted

5. It can be used to perform database recovery when database is open

6. The same port number can be used for multiple Database Express configurations for multiple databases on the same host

140 / 229

140.

Which two statements are true about User Authentication in an Oracle Database?

1. Password File authentication is supported for any type of database user

2. Password File authentication must be used for system-privileged administrative users

3. REMOTE_LOGIN_PASSWORDFILE must be set to exclusive to permit password changes for system-privileged administrative users

4. Operation System authentication may be used for system-privileged administrative users

5. Password authentication must be used for system-privileged administrative users

141 / 229

141.

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

1. It ignores NULLs

2. Multiple INTERSECT operators are not possible in the same SQL statement

3. INTERSECT is of lower precedence than UNION or UNION ALL

4. It processes NULLs in the selected columns

142 / 229

142.

Examine this command and some partial output:

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

1. The SID_LIST_LISTENER section is not contained in the LISTENER.ORA file

2. The service DB01.abc.com is statically registered

3. The service DB01.abc.com is dynamically registered

4. The LOCAL_LISTENER database parameter is not set to a service name that refers to LISTENER_1

5. The listener is not listening on the default port 1521

143 / 229

143.

Which three statements are true about sequences in a single instance Oracle database?

1. Sequences can always have gaps.

2. Two or more tables cannot have keys generated from the same sequence.

3. A sequence number that was allocated can be rolled back if a transaction fails.

4. A sequence's unallocated cached values are lost if the instance shuts down.

5. A sequence can only be dropped by a DBA.

6. A sequence can Issue duplicate values.

144 / 229

144.

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

1. The same port number can be used for Database Express configurations for databases ondifferent hosts

2. It is available only when the database is open

3. It can be used to switch a database into ARCHIVELOGMODE

4. The same port number can be used for multiple Database Express configurations for multipledatabases on the same host

5. It can be used to perform database recovery

145 / 229

145.

Which two statements are true about undo and undo tablespaces?

1. undo segments are owned by SYSTEM.

2. There can be only one undo tablespace created in a database.

3. An instance will crash if the active undo tablespace is lost.

4. An undo tablespace may be owned by only one instance.

5. undo segments are owned by SYSBACKUP.

146 / 229

146.

Which two statements are true about Oracle synonyms?

1. Any user can create a PUBLIC synonym.

2. A synonym can be created on an object in a package.

3. A synonym can have a synonym.

4. A synonym has an object number.

5. All private synonym names must be unique in the database.

147 / 229

147.

Which two statements are true about Database Instances and Real Application Clusters
(RAC)?

1. Two RAC databases can share their instances.

2. A RAC database can have one Instance.

3. A RAC database can have instances on separate servers.

4. A RAC database must have three or more Instances.

5. A RAC database must have two or more instances.

148 / 229

148.

Which two are true about transactions in the Oracle Database?

1. A DDL statement issued by a session with an uncommitted transaction automatically commits that transaction.

2. A session can see uncommitted updates made by the same user in a different session.

3. DML statements always start new transactions.

4. An uncommitted transaction is automatically committed when the user exits SQL*Plus.

5. DDL statements automatically commit only data dictionary updates caused by executing the DDL.

149 / 229

149.

Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

1. A sequence can issue duplicate values

2. Sequences can always have gaps

3. A sequence can only be dropped by a DBA

4. A sequence number that was allocated can be rolled back if a transaction fails

5. A sequence's unallocated cached value are lost if the instance shuts down

6. Two or more tables cannot have keys generated from the same sequence

150 / 229

150.

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

1. V$TABLESPACE displays information that is contained in the controlfile about tablespaces

2. A datafile can be renamed when the database is in MOUNT state and the new file name isdisplayed when querying DBA_DATA_FILES after the database is opened

3. V$TABLESPACE displays information about tablespaces contained in the data dictionary

4. Tablespace free space can be viewed in V$TABLESPACE

5. Tablespace free space can be viewed in DBA_TABLESPACES

151 / 229

151.

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 on the week.

Which query can be used?

1. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY('MONDAY') FROM employees;

2. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;

3. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;

4. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

152 / 229

152.

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

1. It frees resources held by abnormally terminated processes

2. It registers database services with all local and remote listeners known to the database instance

3. It records checkpoint information in the control file

4. It kills sessions that exceed idle time

5. It frees unused temporary segments

153 / 229

153.

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

1. It is possible for child rows that have a foreign key to remain in the child table at the time theparent row is deleted

2. Primary key and foreign key constraints can be defined at both the column and table level

3. Only the primary key can be defined at the column and table level

4. The foreign key columns and parent table primary key columns must have the same names

5. A table can have only one primary key but multiple foreign keys

6. A table can have only one primary key and one foreign key

7. It is possible for child rows that have a foreign key to be deleted automatically from the childtable at the time the parent row is deleted

154 / 229

154.

Examine this command:

Which two statements are true? (Choose two.)

1. The file is renamed and stored in the same location

2. The tablespace containing SALES1.DBF must be altered READ ONLY before executing thecommand.

3. If Oracle Managed Files (OMF) is used, then the file is renamed but moved toDB_CREATE_FILE_DEST.

4. DML may be performed on tables with one or more extents in this data file during the executionof this command.

5. The tablespace containing SALES1.DBF must be altered OFFLINE before executing the command.

155 / 229

155.

Examine the description of the BOOKS_TRANSACTIONS table:
Name Null? Type

=========================================== =========== ==========================

TRANSACTION_ID NOT NULL VARCHAR2(6)

TRANSACTION_TYPE VARCHAR2(3)

BORROWED_DATE DATE

BOOK_ID VARCHAR2(6)

MEMBER_ID VARCHAR2(6)

Examine this partial SQL statement:

SELECT * FROM books_transactions

Which two WHERE conditions give the same result?

1. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND member_id = ‘A101’ OR member_id = ‘A102’);

2. WHERE borrowed_date = SYSDATE AND transaction_type = ‘RM’ OR member_id IN (‘A101’,‘A102’);

3. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND (member_id = ‘A101’ OR member_id = ‘A102’));

4. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ OR member_id IN (‘A101’,‘A102’));

5. WHERE (borrowed_date = SYSDATE AND transaction_type = ‘RM’) OR member_id IN (‘A101’,‘A102’);

156 / 229

156.

The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NfUMBER
Which two queries execute successfully?

1. SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) >10;

2. SELECT prod_id FROM sales WHERE quantity_sold > 55000 and COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) >10;

3. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;

4. SELECT prod_id FROM sales WHERE quantity_sold > 55000 and COUNT(*) > 10 GROUP BY COUNT(*) >10;

5. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;

157 / 229

157.

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.)

1. Set UNDO_TABLESPACE to UNDOTBS01

2. Add the ONLINE clause

3. Add the SEGMENT SPACE MANAGEMENT AUTO clause

4. Make certain that the database operates in automatic undo management mode

5. Add the NOLOGGING clause

158 / 229

158.

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

1. They return a single result row per table

2. They can accept only one argument

3. The argument can be a column name, variable, literal or an expression

4. They can be nested to any level

5. They can be used only in the WHERE clause of a SELECT statement

6. The data type returned can be different from the data type of the argument

159 / 229

159.

Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY. Only the EMPLOYEE_ID column is indexed. Rows exist for employees 100 and 200.

Examine this statement:

Which two statements are true? (Choose two.)

1. Employee 200 will have SALARY set to the same value as the SALARY of employee 100

2. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

3. Employees 100 and 200 will have the same SALARY as before the update command

4. Employee 100 will have SALARY set to the same value as the SALARY of employee 200

5. Employees 100 and 200 will have the same JOB_ID as before the update command

6. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200

160 / 229

160.

You must create a tablespace of non-standard block size in a new file system and plan to use this command:

The standard block size is 8k but other non-standard block sizes will also be used. Which two are requirements for this command to succeed?

1. db_32k_cache_size must be set to a value that can be accommodated in the SGA.

2. db_cache_s:ze must be set to a size that is smaller than db_32k_cache_size.

3. The /u02 file system must have at least lOOg space for the datafile.

4. db_32r_cache_si2e must be less than db_ca=he_size.

5. d3_32k_cache_size should be set to a value greater than db_cache_size.

6. The operating system must use a 32k block size.

161 / 229

161.

While one of your databases was in mount state, the datafiles were renamed because they had been moved to a new file system. The database was then opened. Which two statements are true?

1. DBA_DATA _FILES displays both the new name and the old name for the data files.

2. DBA_DATA_FILES displays the original name for the data files.

3. DBA_DATA_FILES must be resynchronized manually with the control file an order to have itdisplay the new file names.

4. DBA_DATA_FILES displays the new name for the data files.

5. V$DATAFILE displays the new names for the data files.

162 / 229

162.

You must create a tablespace of non-standard block size in a new file system and plan to use the command:

1. DB_32K_CACHE_SIZE must be set to a value that can be accommodated in the SGA.

2. DB_32K_CACHE_SIZE should be set to a value greater than DB_CACHE_SIZE.

3. DB_32K_CACHE_SIZE must be less than DB_CACHE_SIZE.

4. DB_CACHE_SIZE must be set to a size that is smaller than DB_32K_CACHE_SIZE.

5. The /u02 file system must have at least 100g space for the datafile.

6. The operating system must use a 32k block size.

163 / 229

163.

Examine these SQL statements which execute successfully:

Which two statements are true after execution?

1. The primary key constraint will be enabled and DEFERRED.

2. The foreign key constraint will be enabled and IMMEDIATE.

3. The foreign key constraint will be disabled.

4. The foreign key constraint will be enabled and DEFERRED

5. The primary key constraint will be enabled and IMMEDIATE.

164 / 229

164.

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?

1. It succeeds and the generated undo is stored in SYSTEM.

2. It fails and returns the error message "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'".

3. It succeeds and the generated undo is stored in SYSAUX.

4. It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.

5. It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.

165 / 229

165.

You start your database instance in NOMOUNT state. Which two actions are performed?

1. The control files are opened.

2. Memory is allocated for the SG1IT Certification Guaranteed, The Easy Way!30

3. All required background processes are started.

4. SYS can access the database.

5. The consistency of the database is checked.

166 / 229

166.

Examine the description of the members table:

Examine the partial query:
SELECT city, last__name 1NAME FROM members
You want to display all cities that contain the string an. The cities must be returned in ascending
order, with the last names further sorted in descending order.
Which two clauses must you add to the query?

1. WHERE city LIKE *%AN%*

2. ORDER BY last_narae DESC, city ASC

3. ORDER BY 1, LNAME DESC

4. ORDER BY 1, 2

5. WHERE city = =%AN%'

6. WHERE city IN (*%AN%')

167 / 229

167.

Examine the description of the PROMOTIONS table:

1. It can be used to display only constants or pseudo columns

2. It can display multiple rows and columns

3. It can display multiple rows but only a single column

4. It can be accessed only by the SYS user

5. It consists of a single row and single column of VARCHAR2 data type

6. It can be accessed by any user who has the SELECT privilege in any schema

168 / 229

168.

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 on the week. Which query can be used?

1. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

2. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

3. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;

4. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;

169 / 229

169.

Which three statements are true about the Automatic Diagnostic Repository (ADR)?

1. It Is held Inside an Oracle database schema.

2. It is a file-based repository held outside any database.

3. It is only used for Oracle Database diagnostic information.

4. The ADR base is specified In the diagnostic_dest database parameter.

5. It can be used for problem diagnosis of a database when that database's instance is down.

170 / 229

170.

Which two are benefits of external tables?

1. They can be queried while the database is in the MOUNT state like dynamic performance views.

2. They can be queried, transformed, and joined with other tables without having to load the data first.

3. They support DELETE which transparently deletes records in the file system as if they were table rows.

4. They support UPDATES which transparently updates records in the file system as if they were table rows.

5. The results of a complex join or aggregating function or both can be unloaded to a file for transportation to another database.

171 / 229

171.

An Oracle database session has an uncommitted transaction in progress which updated 5000
rows in a table.
Which three situations does the transaction complete thereby committing the updates?

1. when a COMMIT statement is issued by the same user from another session in the same database instance

2. when the session logs out successfully

3. when a CREATE INDEX statement is executed successfully in the same session

4. when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

5. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT

6. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT

172 / 229

172.

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.)

1. You can no longer monitor it

2. It is paused and can be resumed

3. It continues to run in the background

4. You can reattach to it and monitor it

5. It terminates

173 / 229

173.

Which three statements are true about Deferred Segment Creation in Oracle databases?

1. Sessions may dynamically switch back and forth from deferred to immediate segment creation.

2. It Is the default behavior for tables and indexes.

3. It Is supported for Index Organized Tables (IOTs) contained in locally managed tablespaces.

4. It is supported for SYS-owned tables contained in locally managed tablespaces.

5. Indexes inherit the deferred or immediate segment creation attribute from their parent table

174 / 229

174.

Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)

1. The number of columns selected in each SELECT statement must be identical

2. Duplicates are eliminated automatically by the UNION ALL operator

3. NULLS are not ignored during duplicate checking

4. The output is sorted by the UNION ALL operator

5. The names of columns selected in each SELECT statement must be identical

175 / 229

175.

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

1. INTERVAL DAY TO SECOND

2. TIMESTAMP WITH LOCAL TIMEZONE

3. INTERVAL YEAR TO MONTH

4. TIMESTAMP

5. TIMESTAMP WITH TIMEZONE

176 / 229

176.

Which two statements are true about a SQL statement using SET operators such as UNION?

1. The names and number of columns must be identical for all SELECT statements in the query.

2. The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.

3. The number, but not names, of columns must be identical for all SELECT statements in the query.

4. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.

5. The data type of each column returned by the second query must be automatically converted to the data type of the corresponding column returned by the first query

177 / 229

177.

Which three statements are true about table data storage in an Oracle Database? (Choose three.)

1. A table row piece can be chained across several database blocks

2. Data block free space is always contiguous in the middle of the block

3. Multiple row pieces from the same row may be stored in the same block

4. Index block free space is always contiguous in the middle of the block

5. Data block headers contain their own Data Block Address (DBA)

6. Multiple row pieces from the same row may be stored in different database blocks

178 / 229

178.

Which two statements are true about the DUAL table? (Choose two.)

1. It can display multiple rows and columns

2. It can be accessed only by the SYS user

3. It can display multiple rows but only a single column

4. It can be used to display only constants or pseudo columns

5. It consists of a single row and single column of VARCHAR2 data type

6. It can be accessed by any user who has the SELECT privilege in any schema

179 / 229

179.

Which three statements are true about connection strings and service names used to connect to an Oracle database instance?

1. A connection string including a service name must be defined in the tnsnames.ora file.

2. Different connection strings in the same tnsnames.ora file can contain the same service, host and port parameters.

3. A single connection string can refer to multiple database instances.

4. A service name is created by a listener.

5. A connection string must include the SID of a database instance.

6. A single database instance can support connections for multiple service names.

180 / 229

180.

Examine the description of the PRODUCTS table

Which three queries use valid expressions?

1. SELECT product_id, unit_price, 5 “Discount”, unit_price + surcharge – discount FROM products;

2. ) SELECT product_id, expiry_date * 2 FROM products;

3. SELECT product_id, unit_price || 5 “Discount”, unit_price + surcharge – discount FROM products;

4. SELECT product_id, unit_price, unit_price + surcharge FROM products;

5. SELECT product_id, (expiry_date – delivery_date) * 2 FROM products;

6. SELECT product_id, (unit_price * 0.15/ (4.75 + 552.25)) FROM products;

181 / 229

181.

Which two statements are true about table data storage in an Oracle database?

1. Multiple row pieces from the same row must be stored in different database blocks.

2. Data block free space is always contiguous in the middle of the block.

3. Multiple row pieces from the same row may be stored in different database blocks.

4. A table row can be chained across several database blocks.

5. Index block free space is always contiguous in the middle of the block.

182 / 229

182.

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

1. The data dictionary is created and maintained by the database administrator

2. Base tables can be queried directly

3. Usernames of all users including database administrators are stored in the data dictionary

4. Views with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference thesame base tables from the data dictionary

5. Data dictionary views are created by joins of dictionary base tables and DBA-defined tables

6. It is owned by the SYSTEM user

183 / 229

183.

Which two statements are true about segment types in an Oracle Database?

1. Table segments always have two or more extents.

2. Cluster segments may contain data from multiple tables.

3. Index segments always have two or more extents.

4. Undo segments are only stored in an undo tablespace.

5. Temporary segments are only stored in a temporary tablespace.

184 / 229

184.

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

1. It frees unused temporary segments

2. It registers database services with all local and remote listeners known to the database instance

3. It frees resources held by abnormally terminated processes

4. It records checkpoint information in the control file

5. It rolls back transaction when a process fails

185 / 229

185.

You need to calculate the number of days from 1st January 2019 until today. Dates are stored in the default format of DD-MON-RR. Which two queries give the required output? (Choose two.)

1. SELECT ROUND(SYSDATE - TO_DATE('01/JANUARY/2019')) FROM DUAL;

2. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY') - '01-JAN-2019' FROM DUAL;

3. SELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;

4. SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;

5. SELECT SYSDATE - TO_DATE('01-JANUARY-2019') FROM DUAL;

186 / 229

186.

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

1. They can be written by server processes

2. All trace files contain error information that require contacting Oracle Support

3. Trace files are written to the Fast Recovery Area (FRA)

4. They can be written by background processes

5. Trace file names are based on the database name concatenated with a sequential number

187 / 229

187.

Which three statements are true regarding single row subqueries?

1. They can be used in the WHERE clause.

2. They must be placed on the right side of the comparison operator or condition.

3. They must return a row to prevent errors in the SQL statement.

4. They must be placed on the left side of the comparison operator or condition

5. A SQL statement may have multiple single row subquery blocks.

6. They can be used in the HAVING clause.

188 / 229

188.

The stores table has a column START_DATE of data type DATE, containing the date the row was inserted. You only want to display details of rows where START_DATE is within the last 25 months. Which where clause can be used?

1. WHERE MON'THS_BETWEEN (start_date, SYSDATE) <= 25

2. WHERE TO_XUMBER(start_date - SYSDATE) <= 25

3. WHERE ADD_MONTHS<start_date, 25) <= SYSDATE

4. WHERE MON'THS_BETWEEN (SYSDATE, start_date) <= 25

189 / 229

189.

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?

1. in the temporary tablespace

2. in the SYSAUX tablespace

3. in the SGA

4. in the undo tablespace

5. in the PGA

190 / 229

190.

Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)

1. GLOBAL TEMPORARY TABLE space allocation occurs at session start.

2. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

3. A GLOBAL TEMPORARY TABLE'S definition is available to multiple sessions.

4. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

5. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

6. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

191 / 229

191.

Which compression method is recommended for Direct-Path Insert operations?

1. RON STORE COMPRESS BASIC

2. COLUMN STORE COMPRESS ADVANCED

3. COLUMN STORE COMPRESS BASIC

4. ROW STORE COMPRESS ADVANCED

192 / 229

192.

Your database instance is started with an SPFILE
A PFILE is also available

You execute this command:

ALTER SYSTEM SET DB_CACHE_SIZE=100K;

Where is the value changed?

1. Only in the SPFILE

2. In the SPFILE and PFILE

3. In the SPFILE and in memory

4. Only in memory

5. In the SPFILE, PFILE, and memory

193 / 229

193.

Examine these SQL statements which execute successfully:

Which two statements are true after execution?

1. The foreign key constraint will be disabled.

2. The foreign key constraint will be enabled and deferred.

3. The primary key constraint will be enabled and immediate.

4. The foreign key constraint will be enabled and immediate.

5. The primary key constraint will be enabled and deferred.

194 / 229

194.

In your data canter, 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?

1. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least 50 Mb of available space.

2. Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.

3. Add the AUTOEXTEND ON clause with NEXT set to 100M.

4. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 Mb of available space

5. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.

195 / 229

195.

Examine the description of the EMPLOYEES table:

Which query is valid?

1. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;

2. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

3. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;

4. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;

196 / 229

196.

You execute this query:
SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'), 'dd "Monday for" fmMonth rrrr') What is
the result?

1. It returns the date for the last Monday of the current month

2. It generates an error

3. It returns the date for the first Monday of the next month

4. It executes successfully but does not return any result

197 / 229

197.

Which statement is true about database links?

1. A public database link can be used by a user connected to the local database instance to connect to any schema in the remote database instance.

2. A database link created in a database allows a connection from that database's instance to the target database's Instance, but not vice versa.

3. A database link can be created only between two Oracle databases.

4. A public database link can be created only by sys.

5. Private database link creation requires the same user to exist in both the local and the remote databases.

198 / 229

198.

Examine the description of the CUSTOMERS table:

You want to display details of all customers who reside in cities starting with the letter D followed by at least two characters. Which query can be used?

1. SELECT * FROM customers WHERE city = '%D_';

2. SELECT * FROM customers WHERE city LIKE 'D_%';

3. SELECT * FROM customers WHERE city LIKE 'D_';

4. SELECT * FROM customers WHERE city = 'D_%';

199 / 229

199.

Examine this command and some partial output:

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

1. The service DB01.abc.com is statically registered

2. The LOCAL_LISTENER database parameter is not set to a service name that refers to LISTENER_1

3. The SID_LIST_LISTENER section is not contained in the LISTENER.ORA file

4. The listener is not listening on the default port 1521

5. The service DB01.abc.com is dynamically registered

200 / 229

200.

Evaluate these commands which execute successfully:

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

1. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL forrows inserted into ORD_ITEMS

2. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted intoORD_ITEMS and no explicit value is given for ORD_NO

3. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers

4. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

5. Any user inserting rows into table ORD_ITEMS must have been granted access to sequenceORD_SEQ

201 / 229

201.

Which two queries execute successfully?

1. SELECT COALESCE (100, NULL, 200) FROM DUAL;

2. SELECT NULLIF(100, 'A') FROM DUAL;

3. SELECT NULLIF(100, 100) FROM DUAL;

4. SELECT COALESCE(100, 'A') FROM DUAL;

5. SELECT NULLIF(NULL, 100) FROM DUAL;

202 / 229

202.

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

1. It can be used only from SQL*Plus

2. It can be used to display the structure of an existing view

3. It displays the PRIMARY KEY constraint for any column or columns that have that constraint

4. It can be used from SQL Developer

5. It displays the NOT NULL constraint for any columns that have that constraint

6. It displays all constraints that are defined for each column

203 / 229

203.

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

1. A table can have only one primary key but multiple foreign keys

2. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted

3. Only the primary key can be defined at the column and table level

4. Primary key and foreign key constraints can be defined at both the column and table level

5. A table can have only one primary key and one foreign key

6. The foreign key columns and parent table primary key columns must have the same names

7. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted.

204 / 229

204.

Which two statements are true?

1. SALES1 is created with 55,000 rows.

2. SALES1 is created with no rows.

3. SALES1 has not NULL constraints on any selected columns which had those constraints in the sales table.

4. SALES1 is created with 1 row.

5. SALES1 has primary key and unique constraints on any selected columns which had those constraints in the sales table.

205 / 229

205.

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?

1. The command succeeds and HR will be connected to the orcl database instance, and the abc.sqlscript will be executed

2. The command fails because the script must refer to the full path name

3. The command fails and reports an error because @ is used twice

4. The command succeeds and HR will be connected to the orcl and abc.sql databases

206 / 229

206.

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

1. Online table segment shrink

2. Oracle Managed Files (OMF)

3. Online index segment shrink

4. Capacity planning growth reports based on historical data in the Automatic Workload Repository(AWR)

5. Automatic data file extension (AUTOEXTEND)

207 / 229

207.

Which three statements are true regarding single row subqueries?

1. They can be used in the where clause.

2. They must be placed on the left side of the comparison operator or condition.

3. A SQL statement may have multiple single row subquery blocks.

4. They must be placed on the right side of the comparison operator or condition.

5. They must return a row to prevent errors in the SQL statement.

6. They can be used in the having clause.

208 / 229

208.

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.)

1. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least 50 Mb of available space.

2. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.

3. Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.

4. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 Mb of available space.

5. Add the AUTOEXTEND ON clause with NEXT set to 100M.

209 / 229

209.

Which three statements are true about the naming methods and their features supported by
Oracle database used to resolve connection information?

1. Local Naming requires setting the TNS_ADMIN environment variable on the client side.

2. Local naming can be used if Connect-Time Failover Is required.

3. Directory Naming requires setting the TNS_ADMIN environment variable on the client side.

4. Directory Naming can be used if Connect-Time Failover is required.

5. A client can connect to an Oracle database instance even If no client side network admin has been configured.

6. Easy Connect supports TCP/IP and SSL.

210 / 229

210.

View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.
You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a
promotion time interval.
Examine this SQL statement:

What will be the result?

1. It gives an error because the ALL keyword is not valid

2. It gives an error because the GROUP BY clause is not valid

3. It executes successfully and gives the required result

4. It executes successfully but does not give the required result

211 / 229

211.

The SALES_Q1 and USERS tablespaces exist in one of your databases and TEMP is a temporary tablespace. Segment creation is not deferred. You execute this command:

Which three statements must be true so that the SALES user can create tables in SALES_Q1? (Choose three.)

1. The sales user must have been granted the CREATE SESSION privilege

2. The sales user must have a quota on the TEMP tablespace

3. The sales user must have their quota on the users tablespace removed

4. The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of alltables they plan to create in their schema

5. The sales user must have been granted the CREATE TABLE privilege

6. The sales user must have a quota on the SALES_Q1 tablespace to hold all the rows to be insertedinto any table in their schema

212 / 229

212.

Which two statements are true about User Authentication in an Oracle Database?

1. Password authentication must be used for system-privileged administrative users.

2. REKOTE_LOGIN_PASSMOREFiLE must be set to exclusive to permit password changes for system- privileged administrative users.

3. Password File authentication must be used for system-privileged administrative users.

4. Operating System authentication may be used for system-privileged administrative users.

5. Password File authentication is supported for any type of database user.

213 / 229

213.

Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER. Examine these SQL statements:

Statement 1: SELECT MAX(unit_price * quantity) "Maximum Order" FROM order_items;

Statement 2: SELECT MAX(unit_price * quantity) "Maximum Order" FROM order_items GROUP BY order_id;

Which two statements are true?

1. Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.

2. Statement 2 returns only one row of output.

3. Statement 1 returns only one row of output.

4. Statement 2 may return multiple rows of output.

5. Both the statements give the same output.

214 / 229

214.

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

1. WHERE and HAVING clauses can be used in the same statement only if applied to different table columns

2. The HAVING clause can be used with aggregating functions in subqueries

3. The WHERE clause can be used to exclude rows before dividing them into groups

4. Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query

5. The WHERE clause can be used to exclude rows after dividing them into groups

215 / 229

215.

Which two statements are true concerning logical and physical database structure:

1. A segment might have only one extent.

2. A segment’s blocks can be of different sizes.

3. All tablespaces may have one or more data files.

4. Segments can span multiple tablespaces.

5. A segment can span multiple data files in some tablespaces.

216 / 229

216.

Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

1. the first VARCHAR2 column in the first SELECT of the compound query

2. the first NUMBER OF VARCHAR2 column in the last SELECT of the compound query

3. the first column in the first SELECT of the compound query.

4. the first column in the last SELECT of the compound query.

5. the first NUMBER column in the first SELECT of the compound query.

217 / 229

217.

Which three statements are true about connection strings and service names used to connect to an Oracle database instance?

1. A connection string must include the SID of a database Instance.

2. A connection string including a service name must be defined in the cnsnames.ora file.

3. A single database instance can support connections for multiple service names.

4. A single connection string can refer to multiple database instances.

5. Different connection strings in the same tnsnames.ora file can contain the same service name,host and port parameters.

6. A service name is created by a listener.

218 / 229

218.

Examine this description of the TRANSACTIONS table:

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

1. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;

2. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;

3. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 "DUES AMOUNT" FROM transactions;

4. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;

5. SELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount + 100 'DUES' FROM transactions;

219 / 229

219.

You currently have an active transaction in your session and have been granted SELECT access to V$TRANSACTION.
Executing:

SELECT xid, status FROM v$transaction;

In your session returns:

XID STATUS

================= ===============

0A0007000A070000 ACTIVE

In which three situations will re-executing this query still return a row but with a different XID, indicating a new transaction as started?

1. After successfully executing a CREATE TABLE statement followed by a CREATE INDEX statement

2. After successfully executing a DML statement following a failed DML statement

3. After successfully executing a TRUNCATE statement followed by a DML statement

4. After successfully executing a COMMIT or ROLLBACK followed by a DML statement

5. After successfully executing a CREATE TABLE AS SELECT statement followed by a SELECT FOR UPDATE statement

220 / 229

220.

You need to calculate the number of days from 1st January 2019 until today. Dates are stored in the default format of DD-MON-RR.

Which two queries give the required output? (Choose two.)

1. SELECT SYSDATE - TO_DATE ('01-JAN-2019') FROM DUAL;

2. SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') – '01-JAN-2019' FROM DUAL;

3. SELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;

4. SELECT TO_DATE (SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;

5. SELECT ROUND(SYSDATE - TO_DATE('01/JANUARY/2019')) FROM DUAL;

221 / 229

221.

Examine the description of the customers table:

You need to display last names and credit limits of all customers whose last name starts with A or B in
lower or upper case, and whose credit limit Is below 1000.
Examine this partial query:
SELECT cust_last_name, cust_credit_limit FROM customers
Which two where conditions give the required result?

1. Option E

2. Option D

3. Option C

4. Option A

5. Option B

222 / 229

222.

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?

1. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic serviceregistration

2. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521on the same host as the database instances

3. Dynamic service registration cannot be used for this database instance

4. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration

5. The LREG process registers services dynamically with the LISTENER_1 listener

223 / 229

223.

Which three statements are true about the naming methods and their features supported by Oracle database used to resolve connection information?

1. A client can connect to an Oracle database Instance even if no client side network admin has been configured.

2. Local naming can be used if Connect-Time Failover is required.

3. Directory Naming can be used if Connect-Time Failover is required.

4. Easy Connect supports TCP/IP and SSL

5. Local Naming requires setting the TNS_ADMIN environment variable on the client side.

6. Directory Naming requires setting the TNS_ADMIN environment variable on the client side.

224 / 229

224.

Which two statements are true about substitution variables?

1. A substitution variable used to prompt for a column name must be enclosed in single quotation marks

2. A substitution variable can be used only in a SELECT statement

3. A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session

4. A substitution variable prefixed with & always prompts only once for a value in a session

5. A substitution variable used to prompt for a column name must be enclosed in double quotation marks

6. A substitution variable can be used with any clause in a SELECT statement

225 / 229

225.

In the promotions table, the PROMO_BEGIN_DATE column is of data type date and the default date format Is DD-MON-RR. h two statements are true about expressions using PROMO_BEGIN_DATE contained In a query?

1. PROMO_ BEGIN_DATE - 5 will return a date.

2. PROMO_ BEGIN_DATE - sysdate will return an error.

3. to_numberipromo_begin_date) - s will return a number.

4. TO_date(PROMO_ BEGIN_DATE * 5| will return a date.

5. PROMO_ BEGIN_DATE - sysdate will return a number.

226 / 229

226.

Which three statements are true about inner and outer joins? (Choose three.)

1. Outer joins can be used when there are multiple join conditions on two tables

2. A full outer join returns matched and unmatched rows

3. A left or right outer join returns only unmatched rows

4. Outer joins can only be used between two tables per query

5. A full outer join must use Oracle syntax

6. An inner join returns matched rows

227 / 229

227.

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

1. All data dictionary view join base tables to dynamic performance views.

2. It is owned by the SYSTEM user

3. Data dictionary base tables can be queried directly

4. It is owned by the SYS user

5. Data Dictionary Views are always created with queries that join two or more base tables.

228 / 229

228.

Examine the description of the PRODUCT_STATUS table:

The status column contains the values 'in stock' or 'out of stock' for each row.
Which two queries will execute successfully?

1. SELECT prod_id II q'l's not available)' FROM product_status WHERE status = 'OUT OF STOCK';

2. SELECT prod_id Il q"'s not available" FROM product_status where status = 'OUT OF STOCK';

3. SELECT prod_id q's not available" FROM product_status WHERE status = 'OUT OF STOCK*;

4. SELECT prod_id I I q' (*s not available)' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK

5. SELECT prod_id "CURRENT AVAILABILITY" II q'<'s not available)' FROM product_status WHERE status = 'OUT OF STOCK

6. SELECT prod_id I I q' ('s not available) * 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK

229 / 229

229.

Which statement is true about database links?

1. A database link can be created only between two Oracle databases.

2. Private database link creation requires the same user to exist in both the local and the remote databases

3. A public database link can be used by a user connected to the local database instance to connect to any schema in the remote database instance

4. A database link created in a database allows a connection from that database’s instance to the target database’s instance, but not vice versa.

5. A public database link can be created only by SYS.

Your score is

0%

Please rate this quiz

Related Posts

Oracle Database Administration I 1Z0-082 exam

10: Kiến trúc Multitenant trong Oracle

1.1: [thực hành] Cài đặt Database oracle 19c trên ec2 (aws)

9: Flashback query database

8: Undo và Redo trong Oracle

7: Tablespace trong Oracle Database