Free Oracle 1Z0-082 practice questions

10 free Oracle 1Z0-082 practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 178 questions. Work through them, then open each answer to check your reasoning.

Get all 178 questions (US$39) · Download these 10 as a PDF

Question 1

You execute this command:

Sufficient storage is available in filesystem /u01.

Which two statements are true about the BIG_TBS tablespace? (Choose two.)

Exhibit for question 1

  1. AUTOEXTEND is possible for the datafile
  2. It must be bigger than the largest SMALLFILE tablespace
  3. Additional data files may not be added
  4. It will be a dictionary-managed tablespace by default
  5. It will always have a 32K blocksize
Show answer and explanation

Correct answer: A, C

A. AUTOEXTEND is possible for the datafile C. Additional data files may not be added A bigfile tablespace is built from exactly one data file, so no additional data files can ever be added to it (C). To let it grow, that single file supports AUTOEXTEND, which is fully permitted on a bigfile data file (A). The one file can address up to 4G blocks, which is 32TB with an 8K block size, so a single file is enough. A smallfile tablespace, by contrast, can hold many data files.

Why the other options are wrong

  • B. There is no rule tying a bigfile tablespace's size to any smallfile tablespace; it can be created at any supported size.
  • D. Oracle 10g and later default to locally-managed tablespaces, not dictionary-managed tablespaces.
  • E. The blocksize comes from DB_BLOCK_SIZE unless BLOCKSIZE is specified, so it is not required to be 32K; it defaults to whatever the database blocksize is configured to be.

Question 2

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

  1. Multiple INTERSECT operators are not possible in the same SQL statement
  2. It processes NULLs in the selected columns
  3. INTERSECT is of lower precedence than UNION or UNION ALL
  4. It ignores NULLs
Show answer and explanation

Correct answer: B. It processes NULLs in the selected columns

The INTERSECT operator processes NULLs in the selected columns. When rows from the two result sets are compared, a NULL in a column of one row is treated as matching a NULL in the same column of the other row, so such rows are returned. NULLs are not skipped or discarded, which is the defining characteristic of how INTERSECT handles NULL values in compound queries.

Why the other options are wrong

  • A. Multiple INTERSECT operators are possible in the same SQL statement; you can chain multiple INTERSECT operations together.
  • C. INTERSECT has higher precedence than UNION, UNION ALL and MINUS, so it is evaluated first.
  • D. INTERSECT does not ignore NULLs; it actively processes and matches them as comparable values.

Question 3

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

(Choose three.)

  1. Connect Time Failover requires the use of Transparent Application Failover (TAF)
  2. Source Routing requires the use of a name server
  3. Source Routing enables the use of Connection Manager (CMAN) which enables network traffic to be routed through a firewall
  4. Load Balancing can balance the number of connections to dispatchers when using a Shared Server configuration
  5. Load Balancing requires the use of a name server
  6. Connect Time Failover requires the connect string to have two or more listener addresses configured
Show answer and explanation

Correct answer: C, D, F

C. Source Routing enables the use of Connection Manager (CMAN) which enables network traffic to be routed through a firewall D. Load Balancing can balance the number of connections to dispatchers when using a Shared Server configuration F. Connect Time Failover requires the connect string to have two or more listener addresses configured Source routing lets a connection be routed through one or more intermediate points such as Connection Manager, which allows traffic to pass through a firewall (C). Load balancing can spread client connections across the dispatchers of a Shared Server configuration (D). Connect time failover requires the connect string to list two or more listener addresses so the client can try the next address when one fails (F).

Why the other options are wrong

  • A. Connect Time Failover operates independently and does not require Transparent Application Failover (TAF).
  • B. Source routing needs only SOURCE_ROUTE=YES with a list of addresses in the connect descriptor; no name server is involved.
  • E. Load Balancing does not require a name server; it can function with local connect descriptors and multiple addresses.

Question 4

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 of fractional seconds
  2. SYSDATE can be queried only from the DUAL table
  3. CURRENT_DATE returns the current date and time as per the session time zone
  4. SYSDATE can be used in expressions only if the default date format is DD-MON- RR
  5. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server
  6. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE
Show answer and explanation

Correct answer: C, F

C. CURRENT_DATE returns the current date and time as per the session time zone F. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE CURRENT_DATE returns a DATE value holding both the date and the time, taken from the session time zone rather than the database server's time zone (C). CURRENT_TIMESTAMP returns that same date and time, but as a TIMESTAMP WITH TIME ZONE that also carries fractional seconds (F). SYSDATE and SYSTIMESTAMP instead use the operating system time of the database server.

Why the other options are wrong

  • A. SYSDATE uses the database server time zone while CURRENT_TIMESTAMP uses the session time zone, so the values need not match.
  • B. SYSDATE can be queried from any table or expression, not exclusively from DUAL.
  • D. SYSDATE can be used in expressions regardless of the default date format setting.
  • E. CURRENT_DATE returns values based on the session time zone, not the database server's operating system time zone.

Question 5

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 undo tablespace
  2. in the SYSAUX tablespace
  3. in the SGA
  4. in the PGA
  5. in the temporary tablespace
Show answer and explanation

Correct answer: E. in the temporary tablespace

With temporary undo enabled, undo generated by DML on temporary tables is written to the temporary tablespace instead of the undo tablespace. This keeps undo tablespace usage down and avoids redo for that undo, because temporary table data is session specific and need not survive an instance restart.

Why the other options are wrong

  • A. Bypassing the undo tablespace for temporary objects is exactly what temporary undo does.
  • B. SYSAUX is used for auxiliary data, not temporary undo storage.
  • C. The SGA is shared memory used for caching blocks; undo records are not stored there.
  • D. The PGA holds session work areas such as sort space, not undo records.

Question 6

You have been tasked to create a table for a banking application.

One of the columns must meet three requirements:

1. Be stored in a format supporting date arithmetic without using conversion functions

2. Store a loan period of up to 10 years

3. Be used for calculating interest for the number of days the loan remains unpaid

Which data type should you use?

  1. INTERVAL YEAR TO MONTH
  2. INTERVAL DAY TO SECOND
  3. TIMESTAMP WITH LOCAL TIMEZONE
  4. TIMESTAMP
  5. TIMESTAMP WITH TIMEZONE
Show answer and explanation

Correct answer: B. INTERVAL DAY TO SECOND

INTERVAL DAY TO SECOND stores a duration in days, hours, minutes and seconds, so it supports date arithmetic directly without conversion functions and yields the number of days used for interest. Declaring a leading precision such as INTERVAL DAY(4) TO SECOND covers a loan period of up to 10 years easily.

Why the other options are wrong

  • A. INTERVAL YEAR TO MONTH stores years and months but cannot directly represent days needed for daily interest calculations.
  • C. TIMESTAMP WITH LOCAL TIMEZONE stores a point in time, not a duration or interval suitable for period calculations.
  • D. TIMESTAMP stores a point in time and requires conversion functions for arithmetic with periods.
  • E. TIMESTAMP WITH TIMEZONE stores a point in time with timezone information, not a duration for period calculations.

Question 7

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?

Exhibit for question 7

  1. Dynamic service registration cannot be used for this database instance
  2. The LREG process registers services dynamically with the LISTENER_1 listener
  3. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration
  4. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database instances
  5. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration
Show answer and explanation

Correct answer: B. The LREG process registers services dynamically with the LISTENER_1 listener

with the LISTENER_1 listener When LOCAL_LISTENER is set to LISTENER_1 in the spfile, the LREG (Listener Registration) process uses this parameter to determine which listener to register services with. The TNSNAMES.ORA entry shown defines LISTENER_1 with ADDRESS parameters (PROTOCOL=TCP, HOST, PORT), which is sufficient for the LREG process to locate and register database services dynamically. The LREG process reads the LOCAL_LISTENER parameter and uses the net service name definition to establish a connection for dynamic service registration.

Why the other options are wrong

  • A. Dynamic service registration can be used; the LOCAL_LISTENER parameter explicitly enables it by pointing LREG to LISTENER_1.
  • C. LISTENER_1 only needs to be defined in TNSNAMES.ORA for dynamic registration; LISTENER.ORA is not required for dynamic service registration to function.
  • D. The configuration does not require two listeners or mandate they run simultaneously on the same port; only LISTENER_1 is referenced.
  • E. CONNECT_DATA is used for client connections in TNSNAMES.ORA, not for listener definitions; dynamic registration works with address definitions alone.

Question 8

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

  1. All tablespaces may have one or more data files
  2. The extents of a segment must always reside in the same datafile
  3. A smallfile tablespace might be bigger than a bigfile tablespace
  4. A segment can span multiple data files in some tablespaces
  5. A segment's blocks can be of different sizes
  6. A segment might have only one extent
  7. Segments can span multiple tablespaces
Show answer and explanation

Correct answer: A, D, F

A. All tablespaces may have one or more data files D. A segment can span multiple data files in some tablespaces F. A segment might have only one extent Every tablespace is built from data files: exactly one for a bigfile tablespace and one or more for a smallfile tablespace (A). Within a smallfile tablespace the extents of a segment can be allocated in different data files, so a segment can span data files (D). A segment always gets an initial extent and may never need a second one, so a single extent is possible (F).

Why the other options are wrong

  • B. Extents of a segment can reside in different data files of the same smallfile tablespace.
  • C. Size is not the distinction; a bigfile tablespace supports a far larger maximum size than a smallfile tablespace.
  • E. All blocks in a segment are the same size; block size is set at tablespace creation and cannot vary within a segment.
  • G. Segments cannot span multiple tablespaces; each segment belongs entirely to one tablespace.

Question 9

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. A SAVEPOINT does not issue a COMMIT
  5. Rolling back to a SAVEPOINT can undo a DELETE statement
Show answer and explanation

Correct answer: D, E

D. A SAVEPOINT does not issue a COMMIT E. Rolling back to a SAVEPOINT can undo a DELETE statement Rolling back to a SAVEPOINT can undo a DELETE statement because DELETE is a DML operation that can be rolled back (E). A SAVEPOINT does not issue a COMMIT; it is a rollback point within a transaction that allows partial rollback without committing the entire transaction (D). CREATE and TRUNCATE are DDL operations that cannot be rolled back to a SAVEPOINT.

Why the other options are wrong

  • A. CREATE INDEX is a DDL statement and cannot be undone by rolling back to a SAVEPOINT.
  • B. TRUNCATE is a DDL statement and cannot be undone by rolling back to a SAVEPOINT.
  • C. Multiple SAVEPOINTs can be issued in a single transaction; you can create and rollback to multiple named savepoints.

Question 10

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

  1. writing inbound request to the common request queue from all shared server connections
  2. checking for outbound shared server responses on the common outbound response queue
  3. receiving inbound requests from processes using shared server connections
  4. sending each connection input request to the appropriate shared server input queue
  5. broadcasting shared server session responses back to requesters on all connections
  6. sending shared server session responses back to requesters on the appropriate connection ✅Correct Answer: A, C, F
  7. writing inbound request to the common request queue from all shared server connections
  8. receiving inbound requests from processes using shared server connections
  9. sending shared server session responses back to requesters on the appropriate connection Dispatchers receive inbound requests from processes using shared server connections (C) and write those requests into the single common request queue that all shared server processes read (A). When the work is done, each dispatcher takes the result from its own response queue and returns it to the requester on the correct connection (F).
Show answer and explanation

Answer and explanation for question 10

That was 10 of 178.

The full Oracle 1Z0-082 pack has all 178 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.

Get the full pack