44 free Oracle 1Z0-082 practice questions from the CertStash pack of 178, each with the correct answer and a full explanation. The rule is the same for every exam: ten free on the product page, 44 free here, all 178 in the pack, and each step includes the one before it.
Get all 178 questions (US$39) · Download the first 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.)
Show answer and explanation
Correct answer: A, C. AUTOEXTEND is possible for the datafile; Additional data files may not be added
A BIGFILE tablespace can contain a single datafile that can grow up to 4GB (or larger depending on the block size), and this datafile supports AUTOEXTEND to enable automatic growth. However, additional datafiles cannot be added to a BIGFILE tablespace by definition, it is designed to have only one datafile. In contrast, SMALLFILE tablespaces can have multiple datafiles. Therefore, both A (AUTOEXTEND is possible) and C (additional datafiles may not be added) are correct statements about the BIG_TBS tablespace.
Why the other options are wrong
- B. A BIGFILE tablespace does not have size restrictions relative to SMALLFILE tablespaces; the relationship works the opposite way since BIGFILE can be much larger.
- D. Oracle 10g and later default to locally-managed tablespaces, not dictionary-managed tablespaces.
- E. The blocksize of a BIGFILE tablespace is determined by the DB_BLOCK_SIZE parameter and 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?
Show answer and explanation
Correct answer: B. It processes NULLs in the selected columns
The INTERSECT operator processes NULLs in the selected columns, treating NULL as a distinct value that can be matched. When comparing rows from two result sets, INTERSECT will return rows where NULL values in corresponding columns match NULL values from the other set. This is the defining characteristic of how INTERSECT handles NULL values differently from some other SQL operations.
Why the other options are wrong
- A. Multiple INTERSECT operators are absolutely possible in the same SQL statement; you can chain multiple INTERSECT operations together.
- C. INTERSECT has the same precedence as UNION and UNION ALL; they are evaluated left to right with equal priority.
- 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.)
Show answer and explanation
Correct answer: B, C, F. Source Routing requires the use of a name server; Source Routing enables the use of Connection Manager (CMAN) which enables network traffic to be routed through a firewall; Connect Time Failover requires the connect string to have two or more listener addresses configured
Source Routing enables the use of Connection Manager (CMAN) which routes network traffic through firewalls (B and C are both correct). Connect Time Failover requires the connect string to have two or more listener addresses so the client can automatically attempt connection to the next address if the first fails (F). TAF is not required for Connect Time Failover, TAF is a separate feature for maintaining connections after initial connection is established.
Why the other options are wrong
- A. Connect Time Failover operates independently and does not require Transparent Application Failover (TAF).
- D. Load Balancing can balance connections to instances or listeners, but the statement about dispatchers specifically is not accurate for standard load balancing.
- 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.)
Show answer and explanation
Correct answer: C, F. CURRENT_DATE returns the current date and time as per the session time zone; CURRENT_TIMESTAMP returns the same date as CURRENT_DATE
CURRENT_DATE returns the current date only (without time) as per the session time zone, not the database server's time zone (C). CURRENT_TIMESTAMP returns the same date as CURRENT_DATE but includes time and fractional seconds information (F). SYSDATE returns date and time from the database server and can be queried anywhere, not just DUAL. CURRENT_TIMESTAMP includes fractional seconds whereas SYSDATE does not.
Why the other options are wrong
- A. CURRENT_TIMESTAMP and SYSDATE do not return the same values; SYSDATE does not include fractional seconds.
- 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?
Show answer and explanation
Correct answer: E. in the temporary tablespace
With temporary undo enabled (TEMP_UNDO_ENABLED = TRUE), the undo generated by DML on a temporary table is written to temporary undo segments inside the temporary tablespace instead of the undo tablespace. Oracle documents that temporary undo records are stored in the database's temporary tablespaces and are therefore not logged in the redo log.
Why the other options are wrong
- A. The undo tablespace holds undo for temporary tables only when temporary undo is disabled, which is the default.
- B. SYSAUX holds auxiliary component data, not undo for temporary tables.
- C. The SGA is shared memory. Undo records are written to segments in a tablespace, not kept in the SGA.
- D. The PGA holds session work areas, but temporary undo is stored in the temporary tablespace, not in the PGA.
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?
Show answer and explanation
Correct answer: B. INTERVAL DAY TO SECOND
INTERVAL DAY TO SECOND is the appropriate data type because it supports date arithmetic natively without conversion functions, can represent periods up to 10 years (as a number of days and seconds), and is specifically designed for calculating differences in time intervals needed for interest calculations based on loan duration in days.
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
Which three statements are true concerning logical and physical database structures? (Choose three.)
Show answer and explanation
Correct answer: C, D, F. A smallfile tablespace might be bigger than a bigfile tablespace; A segment can span multiple data files in some tablespaces; A segment might have only one extent
A smallfile tablespace can actually be bigger than a bigfile tablespace since the distinction is about number of datafiles, not total size (C). A segment can span multiple datafiles in dictionary-managed or locally-managed tablespaces with multiple datafiles (D). A segment might have only one extent if it is small or newly created (F). These statements correctly reflect the flexibility and scalability of Oracle's storage architecture.
Why the other options are wrong
- A. The SYSTEM and SYSAUX tablespaces may have multiple datafiles, but not all tablespaces necessarily do; this is not universally true.
- B. Extents of a segment can reside in different datafiles, particularly in tablespaces with multiple datafiles.
- E. All blocks in a segment must be 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 8
Which two statements are true regarding a SAVEPOINT? (Choose two.)
Show answer and explanation
Correct answer: D, E. A SAVEPOINT does not issue a COMMIT; 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 9
Which three functions are performed by dispatchers in a shared server configuration? (Choose three.)
Show answer and explanation
Correct answer: A, C, F. writing inbound request to the common request queue from all shared server connections; receiving inbound requests from processes using shared server connections; sending shared server session responses back to requesters on the appropriate connection
Dispatchers receive inbound requests from processes using shared server connections (C). They write these requests to the common request queue for shared server processes to handle (A). Dispatchers send responses back to the appropriate connection on the outbound response queue (F). The dispatcher acts as an intermediary between client connections and shared server processes, managing request and response queuing.
Why the other options are wrong
- B. Dispatchers send responses from the response queue to clients; they do not check the outbound queue themselves.
- D. Dispatchers send requests to the common request queue, not to individual shared server input queues.
- E. Dispatchers send responses to the appropriate connection, not broadcast to all connections.
Question 10
Which three statements are true about inner and outer joins? (Choose three.)
Show answer and explanation
Correct answer: A, B, E. A full outer join returns matched and unmatched rows; An inner join returns matched rows; Outer joins can be used when there are multiple join conditions on two tables
(A) A full outer join returns both matched rows (where join conditions are met) and unmatched rows from both tables. (B) An inner join returns only the rows where the join condition is satisfied in both tables. (E) Outer joins can be used with multiple join conditions on two tables, allowing complex join logic while retaining unmatched rows. These statements accurately describe join behavior and capabilities.
Why the other options are wrong
- C. Outer joins can be used with more than two tables in a single query through chaining multiple join operations.
- D. Full outer joins can use both Oracle-specific syntax (using the (+) operator) and standard SQL syntax (FULL OUTER JOIN).
- F. A left or right outer join returns matched rows plus unmatched rows from the specified side; it does not return only unmatched rows.
What people say about the full 1Z0-082 pack 4.8 from 5
Question 11
You execute this query: SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'), 'dd `Monday for` fmMonth rrrr') What is the result?
Show answer and explanation
Correct answer: B. It returns the date for the first Monday of the next month
LAST_DAY(SYSDATE) returns the last day of the current month. NEXT_DAY(..., 'MON') returns the next Monday after that date, which is the first Monday of the following month. The TO_CHAR formatting then displays this date as 'dd Monday for fmMonth rrrr'. This query executes successfully and returns the first Monday of the next month.
Why the other options are wrong
- A. The query does return a result; all three functions execute successfully.
- C. The syntax is valid and all functions are properly used.
- D. NEXT_DAY after the last day of the current month returns a Monday in the next month, not the last Monday of the current month.
Question 12
Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)
Show answer and explanation
Correct answer: C, D, E. A TIMESTAMP data type column contains information about year, month, and day; The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC); The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
A TIMESTAMP column holds year, month and day (plus time), DBTIMEZONE can return a UTC offset such as +00:00, and SESSIONTIMEZONE can also return a UTC offset. A TIMESTAMP WITH LOCAL TIME ZONE value is not stored with the session time zone: it is normalized to the database time zone and shown in the session time zone on retrieval.
Why the other options are wrong
- A. CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value, so it includes time zone information.
- B. A TIMESTAMP WITH LOCAL TIME ZONE column is stored normalized to the database time zone, not the time zone of the inserting session.
Question 13
Which statement is true about aggregate functions?
Show answer and explanation
Correct answer: D. The MAX and MIN functions can be used on columns with character data types
The MAX and MIN functions can be used on columns with character data types to find the maximum and minimum values in alphabetical or collation-based order. This is a true statement about aggregate function capabilities. MAX and MIN work on any data type that has a natural ordering, including character strings.
Why the other options are wrong
- A. Aggregate functions cannot be nested to any level; nesting is limited and depends on the specific function and context.
- B. The AVG function does not implicitly convert NULLs to zero; it ignores NULL values and calculates the average of non-NULL values.
- C. Aggregate functions cannot be used in all clauses of a SELECT statement; for example, they cannot be used in the WHERE clause without a subquery.
Question 14
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.)
Show answer and explanation
Correct answer: A, E. You can no longer monitor it; It terminates
When you execute STOP_JOB=immediate in Data Pump, it immediately terminates the job. Once stopped with the immediate option, the job is permanently ended and cannot be reattached or resumed. The job ceases execution and does not continue running in the background. After termination, you can no longer monitor the job interactively, though the dump file and log file will contain information about the work completed before the stop command was issued.
Why the other options are wrong
- B. You cannot reattach to a job that has been stopped with STOP_JOB=immediate; it is terminated, not suspended.
- C. STOP_JOB=immediate terminates the job rather than pausing it; paused jobs can be resumed, but terminated jobs cannot.
- D. The immediate parameter causes the job to stop immediately and not continue running in the background.
Question 15
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?
Show answer and explanation
Correct answer: B. SELECT &col1, &col2 FROM &&table WHERE &condition;
In SQL*Plus, a single ampersand (&) prompts for input each time it is encountered, while a double ampersand (&&) prompts only the first time and reuses the value thereafter within a session. The requirement is to prompt once for the table name (use &table) but multiple times for column names and condition (use &&col1, &&col2, &&condition). Option B correctly uses &table for one-time prompting and &&col1, &&col2, &&condition for repeated prompting. Options A, D, and E use && for table, which would prompt multiple times. Option C uses quoted literals, which would not trigger substitution properly.
Why the other options are wrong
- A. Uses && for table, which prompts every time the query is executed, violating the requirement to prompt only once.
- C. Quotes around variables prevent proper substitution behavior, and & table still prompts multiple times.
- D. Uses && for table (prompts multiple times) and includes incorrect comparison syntax with two different variables.
- E. Uses && for table, which prompts every time the query is executed instead of just the first time.
Question 16
Which three are types of segments in an Oracle Database? (Choose three.)
Show answer and explanation
Correct answer: A, B, E. undo; index; tables
In Oracle, a segment is a space allocation unit. Table segments, index segments, and cluster segments are all valid segment types that allocate space in the database. These objects physically store data and consume space in tablespaces. Undo segments also exist to store undo information. Stored procedures and sequences are schema objects but do not create their own segments in the traditional sense, sequences are stored in the data dictionary, and stored procedures are stored as code.
Why the other options are wrong
- C. Stored procedures are schema objects but do not create segments; they are stored as code.
- D. Sequences are schema objects but do not create segments; they are managed by the data dictionary.
- F. While clusters can create segments, the primary segment types are tables, indexes, and undo segments.
Question 17
You need to calculate the number of days from 1 January 2019 until today, where dates are stored in the default format of DD-MON-RR. Which two queries give the required output?
Show answer and explanation
Correct answer: B, E. SELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;; SELECT SYSDATE - TO_DATE('01-JANUARY-2019') FROM DUAL;
To calculate days between dates, you subtract one date from another, which returns a numeric value representing days. Option B uses ROUND(SYSDATE - TO_DATE('01-JAN-2019')) which correctly subtracts the date value and rounds the result. Option E uses SYSDATE - TO_DATE('01-JANUARY-2019') which also correctly performs date subtraction. Both work because Oracle recognizes the default date format DD-MON-RR and can parse the date strings appropriately.
Why the other options are wrong
- A. TO_CHAR converts SYSDATE to a string, which cannot be subtracted from another string to get a numeric day count.
- C. TO_DATE requires a format mask as the second parameter; using 'DD/MONTH/YYYY' with a slash-separated date string is a format mismatch.
- D. TO_DATE converts SYSDATE (which is already a date) using a format mask, which is incorrect; SYSDATE is not a string needing conversion.
Question 18
Which two statements are true about views used for viewing tablespace and datafile information? (Choose two.)
Show answer and explanation
Correct answer: B, E. V$TABLESPACE displays information that is contained in the controlfile about tablespaces; 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
The correct statements are: (B) V$TABLESPACE displays information stored in the controlfile about tablespaces, including name and ID; (E) a datafile can be renamed when the database is in MOUNT state using an ALTER DATABASE RENAME FILE command, and the new name will be reflected in DBA_DATA_FILES after reopening. Option A is wrong because V$TABLESPACE does not show free space, that requires DBA_TABLESPACES or DBA_FREE_SPACE. Option C incorrectly claims V$TABLESPACE contains data dictionary information when it actually contains controlfile information. Option D is false; free space is not directly visible in DBA_TABLESPACES.
Why the other options are wrong
- A. V$TABLESPACE does not display free space information; use DBA_TABLESPACES or DBA_FREE_SPACE for that.
- C. V$TABLESPACE displays controlfile information, not data dictionary information.
- D. Free space information is not directly available in DBA_TABLESPACES; use DBA_FREE_SPACE instead.
Question 19
You want to use table compression suitable for OLTP that will: 1. Compress rows for all DML statements on that table 2. Minimize the overheads associated with compression Which compression option is best suited for this?
Show answer and explanation
Correct answer: E. ROW STORE COMPRESS ADVANCED
ROW STORE COMPRESS ADVANCED is the best choice for OLTP environments because it compresses rows for all DML statements (INSERT, UPDATE, DELETE, SELECT) while being optimized for transactional workloads. Although it has higher compression overhead than ROW STORE COMPRESS BASIC, it still maintains acceptable performance for OLTP by minimizing overall storage overhead across all DML operations. COLUMN STORE options are designed for analytical (OLAP) workloads and perform poorly on OLTP. ROW STORE COMPRESS BASIC only compresses during bulk loads, not for all DML statements.
Why the other options are wrong
- A. COLUMN STORE COMPRESS FOR QUERY LOW is designed for OLAP query workloads, not OLTP, and creates unfavorable performance for transactional operations.
- B. ROW STORE COMPRESS BASIC only compresses during bulk load operations, not for all DML statements as required.
- C. COLUMN STORE COMPRESS FOR ARCHIVE LOW is designed for archival and analytical workloads, not suitable for active OLTP systems.
- D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH is designed for archival purposes with maximum compression, unsuitable for OLTP performance requirements.
Question 20
Which three statements are true about undo segments and the use of undo by transactions in an Oracle database instance? (Choose three.)
Show answer and explanation
Correct answer: A, B, D. An undo segment may be used by multiple transactions simultaneously; Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment; Undo segments can extend when a transaction fills the last extent of the undo segment
Undo segments have three key characteristics: (A) An undo segment may be used by multiple transactions simultaneously, allowing efficient resource sharing; (B) Undo segments can wrap around to the first extent when a transaction fills the last extent, reusing space in a circular fashion; and (D) Undo segments can extend automatically when a transaction fills the last extent and more space is needed. These mechanisms ensure undo space is managed dynamically without requiring manual intervention.
Why the other options are wrong
- C. Undo segments do not have a fixed minimum number of extents; they grow as needed.
- E. A single transaction uses only one undo segment, not multiple.
- F. Undo segments can be stored in either BIGFILE or SMALLFILE tablespaces; there is no requirement for BIGFILE.
- G. Undo segments can be stored in either BIGFILE or SMALLFILE tablespaces; there is no requirement for SMALLFILE.
Question 21
Which four account management capabilities can be configured using Oracle profiles? (Choose four.)
Show answer and explanation
Correct answer: B, D, E, F. the number of days for which an account may be inactive before it is locked; the ability to prevent a password from ever being reused; the number of password changes required within a period of time before a password can be reused; the number of days for which an account is locked after the configured number of login attempts has been reached
Oracle profiles can configure the number of days an account may be inactive before it is locked (B). The number of days an account is locked after exceeding login attempts (F). The maximum amount of CPU time allowed before account lockout (D is not standard, this is typically session termination, not account locking, but E is correct: the number of password changes required before reuse (E). Option A is incorrect because profiles do not configure hours of lockout; they configure days. Option C is incorrect because CPU time limits typically terminate sessions, not lock accounts. Option G is incorrect because exceeding session limits typically terminates sessions, not locks accounts.
Why the other options are wrong
- A. Profiles configure days of account lockout after login attempts, not hours.
- C. CPU time limits cause session termination, not account locking.
- G. Exceeding session limits terminates sessions, it does not lock the account itself.
Question 22
The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER. Which two queries execute successfully? (Choose two.)
Show answer and explanation
Correct answer: C, E. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;; SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
(C) SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') executes successfully because the numeric calculation is converted to character before NVL evaluates it, ensuring both arguments are character type. (E) SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') executes successfully because NVL2 checks if cust_credit_limit is not NULL and returns the character result of TO_CHAR, or the character string 'Not Available'. Both queries properly handle type compatibility.
Why the other options are wrong
- A. NVL(cust_credit_limit * .15, 'Not Available') fails because the first argument is numeric and the second is character; NVL requires compatible types.
- B. NVL2(cust_credit_limit * .15, 'Not Available') fails because NVL2 expects a column or expression to test for NULL as the first argument, not a calculation.
- D. TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) fails because NVL receives a numeric calculation and a character string, causing a type mismatch error.
Question 23
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.)
Show answer and explanation
Correct answer: C, D. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed; The second ROLLBACK command undoes the insert
The sequence begins with an INSERT that adds a new row (101 total), then creates SAVEPOINT a. The DELETE removes all 101 rows. ROLLBACK TO SAVEPOINT a then rolls back only to the savepoint, undoing the DELETE and restoring the 101 rows while keeping the INSERT intact (savepoints don't affect uncommitted transactions before them). The final ROLLBACK has no transaction to roll back since ROLLBACK TO SAVEPOINT a implicitly committed or finalized the state to that point within the transaction, making the second ROLLBACK a no-op. Therefore, the first ROLLBACK restores the 101 deleted rows while the inserted row remains, and the second ROLLBACK does nothing.
Why the other options are wrong
- A. The second ROLLBACK does perform an action, not nothing, it attempts to rollback but there is no active transaction state to rollback after the ROLLBACK TO SAVEPOINT.
- B. The second ROLLBACK does not replay the delete; it has no delete to replay since the first ROLLBACK TO SAVEPOINT a already reversed it.
- E. The first ROLLBACK command does not commit the inserted row; it leaves the entire transaction (both the INSERT and the restored rows) in an uncommitted state awaiting a final COMMIT or ROLLBACK.
Question 24
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.)
Show answer and explanation
Correct answer: B, D. Set UNDO_TABLESPACE to UNDOTBS01; Make certain that the database operates in automatic undo management mode
To use UNDOTBS01 as the default UNDO tablespace, you must set the initialization parameter UNDO_TABLESPACE=UNDOTBS01 to point the database to use this tablespace. Additionally, the database must be operating in automatic undo management mode (controlled by UNDO_MANAGEMENT=AUTO) for UNDO_TABLESPACE to have effect. These two conditions work together to designate the tablespace as the default for automatic undo management.
Why the other options are wrong
- A. SEGMENT SPACE MANAGEMENT AUTO is for segment-level free space management and is not required for UNDO tablespace designation.
- C. The NOLOGGING clause is not applicable to tablespace creation and would not affect UNDO tablespace designation.
- E. While ONLINE may be a valid clause, it is not a required action to designate UNDOTBS01 as the default UNDO tablespace.
Question 25
The ORCL database has RESUMABLE__TIMEOUT = 7200 and DEFERRED_SEGMENT_CREATION = FALSE User U1 has a 1 MB quota in tablespace DATA. U1 executes this command: SQL> CREATE TABLE t1 AS - (SELECT object_name, sharing, created FROM dba_objects); U1 complains that the command is taking too long to execute. In the alert log, the database administrator (DBA) finds this: 2017-03-06T12:15:17.183438+05:30 statement in resumable session 'User U1(136), Session 1, Instance 1' was suspended due to ORA-01536: space quota exceeded for tablespace 'DATA' Which are three actions any one of which the DBA could take to resume the session? (Choose three.)
Show answer and explanation
Correct answer: B, C, E. Drop other U1 objects in DATA; Increase U1's quota sufficiently in DATA; Grant UNLIMITED TABLESPACE to U1
When a resumable session is suspended due to space quota exceeded, the DBA can resume it by: (B) dropping other objects owned by U1 in DATA to free space within the existing quota, (C) increasing U1's tablespace quota in DATA to provide more allocation, or (E) granting UNLIMITED TABLESPACE to U1 which overrides quota restrictions. Option A (adding a datafile) doesn't help since the issue is U1's quota, not physical space. Option D (DEFERRED_SEGMENT_CREATION) won't help an already-suspended statement. Option F (AUTOEXTEND) doesn't affect user quotas.
Why the other options are wrong
- A. Adding a datafile increases physical space but does not increase U1's quota allocation in the tablespace.
- D. DEFERRED_SEGMENT_CREATION affects future table creation, not an already-suspended resumable statement.
- F. AUTOEXTEND expands datafile size but does not change the user's quota limit in the tablespace.
Question 26
Which two statements are true about the DUAL table? (Choose two.)
Show answer and explanation
Correct answer: B, F. It consists of a single row and single column of VARCHAR2 data type; It can display multiple rows and columns
The DUAL table is a special Oracle system table with a single row containing a single column named DUMMY of VARCHAR2 data type. However, when used with set operations or certain queries, multiple rows can be produced from multiple SELECT statements. The DUAL table can be accessed by any user with SELECT privilege and can display constants, pseudo columns, and function results. It is not restricted to SYS-only access and is not limited to displaying only constants.
Why the other options are wrong
- A. DUAL can be accessed by any user with SELECT privilege, not only by SYS.
- C. DUAL consists of a single column, and multiple rows can be displayed through UNION operations, but the question asks about DUAL itself.
- D. DUAL can display table data and function results in addition to constants and pseudo columns.
- E. DUAL can be accessed by any user in any schema who has SELECT privilege on it, not limited by schema ownership.
Question 27
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?
Show answer and explanation
Correct answer: C. It must resolve to a valid connect descriptor in the client's tnsnames.ora file
When no name server is used in a CONNECT statement, ORCL must resolve to a valid connect descriptor in the client's tnsnames.ora file. The client reads its local tnsnames.ora to translate the net service name (ORCL) into a connect descriptor containing the hostname, port, and service name needed to reach the listener. Option B is incorrect because the server does not use tnsnames.ora to resolve incoming connection requests. Options A, D, and E describe other valid Oracle concepts but are not what ORCL represents in this client-side connection scenario.
Why the other options are wrong
- A. SERVICE_NAMES is a database parameter; ORCL is a net service name that must be resolved client-side.
- B. The server does not use tnsnames.ora; the client uses its own tnsnames.ora to resolve the connection string.
- D. ORCL is a net service name alias, not the actual database name.
- E. ORCL is not the server name; it resolves to a descriptor containing the server hostname and connection details.
Question 28
Which three statements are true about the DESCRIBE command? (Choose three.)
Show answer and explanation
Correct answer: B, C, D. It can be used from SQL Developer; It displays the NOT NULL constraint for any columns that have that constraint; It can be used to display the structure of an existing view
The DESCRIBE command displays column definitions including data types and the NOT NULL constraint. It can be used from multiple tools including SQL Developer and SQL*Plus (not just SQL*Plus). DESCRIBE can show the structure of various objects including views, tables, and procedures. However, DESCRIBE does not show all constraints (like PRIMARY KEY constraints or foreign keys) nor does it display every constraint type defined on columns; it primarily shows NOT NULL constraints and basic column information.
Why the other options are wrong
- A. DESCRIBE does not display PRIMARY KEY constraint information for columns.
- E. DESCRIBE does not display all constraints; it primarily shows NOT NULL constraints and column definitions.
- F. DESCRIBE can be used from multiple tools, not just SQL*Plus (it works in SQL Developer and other tools).
Question 29
Which two are true about a SQL statement using SET operators such as UNION? (Choose two.)
Show answer and explanation
Correct answer: A, B. 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; The number, but not names, of columns must be identical for all SELECT statements in the query.
(A) The data type group of each column returned by the second query must match the data type group of the corresponding column in the first query, allowing for implicit conversions within compatible type groups (e.g., different numeric types). (B) The number, but not names, of columns must be identical for all SELECT statements in the query; column names are determined by the first query and do not need to match in subsequent queries. These requirements ensure UNION can properly combine result sets.
Why the other options are wrong
- C. Data types do not need to match exactly; they only need to be compatible or convertible, allowing flexibility in queries.
- D. Column names do not need to be identical across all SELECT statements; only the first query's column names are used in the result set.
- E. While implicit conversion is possible, the requirement is that types must be compatible within the same data type group, not that they must be implicitly convertible in all cases.
Question 30
Which two statements are true regarding Oracle database space management within blocks managed by Automatic Segment Space Management (ASSM)? (Choose two.)
Show answer and explanation
Correct answer: B, D. ASSM assigns blocks to one of four fullness categories based on what percentage of the block is allocated for rows; Insert operations always attempt to find blocks with free space appropriate to the length of the row being inserted
With Automatic Segment Space Management (ASSM), blocks are assigned to one of four fullness categories (0-25%, 25-50%, 50-75%, 75-100%) based on allocated space for rows (B is true). Insert and update operations attempt to find blocks with appropriate free space for the row being inserted or updated (D is true). Option A is incorrect because PCTFREE varies by compression method and segment type, not a default of 10% for all. Option C is incorrect because operations attempt but do not always find appropriate blocks; this is not guaranteed. Option E is incorrect because a block may not be eligible for inserts even if the row fits, depending on its fullness category and PCTFREE settings.
Why the other options are wrong
- A. PCTFREE defaults vary based on compression method and segment type, not uniformly 10% for all cases.
- C. While operations attempt to find appropriate blocks, this is not guaranteed, it depends on available space and fullness categories.
- E. Even if a row fits physically in a block, the block may not be eligible for inserts based on its fullness category and PCTFREE settings.
Question 31
Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)
Show answer and explanation
Correct answer: B, E. The number of columns selected in each SELECT statement must be identical; NULLS are not ignored during duplicate checking
Option B is correct: the number of columns in each SELECT statement must be identical for both UNION and UNION ALL operators to work. Option E is correct: NULL values are not ignored during duplicate checking, UNION considers two rows with NULL in the same position as duplicates and removes one copy. UNION ALL does not remove duplicates regardless of NULL values.
Why the other options are wrong
- A. UNION ALL does not eliminate duplicates; UNION eliminates them.
- C. Column names do not need to be identical; the data types and number of columns matter, and the result uses the column names from the first SELECT.
- D. UNION ALL does not sort output; UNION may sort, but UNION ALL preserves the original order.
Question 32
Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)
Show answer and explanation
Correct answer: A, D, E. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted; Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted; A GLOBAL TEMPORARY TABLE'S definition is available to multiple sessions.
The correct statements are: (A) a TRUNCATE command in a session deletes all rows in the GTT for that session only; (D) any GTT rows existing at session termination are automatically deleted by Oracle; (E) a GTT definition is shared and available to multiple sessions, though data is private per session. Option B is false; GTT data inserted by one session is isolated and not visible to other sessions, each session has its own private copy. Option C is incorrect; space is allocated per session when needed, not automatically at session start. Option F is wrong; DELETE on a GTT can be rolled back like any other DML.
Why the other options are wrong
- B. Each session has private data in a GTT; rows inserted by one session are not visible to other sessions.
- C. Space allocation occurs on demand as rows are inserted, not at session start.
- F. DELETE operations on GTTs are standard DML and can be rolled back via ROLLBACK.
Question 33
Which two statements are true about space-saving features in an Oracle Database? (Choose two.)
Show answer and explanation
Correct answer: B, C. An index created with the UNUSABLE attribute has no segment; If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next COMMIT OR ROLLBACK statement
An index created with the UNUSABLE attribute has no segment allocated, saving space immediately upon creation (B is correct). Private Temporary Tables (PTTs) are dropped at the next COMMIT or ROLLBACK statement if they exist for a session, automatically cleaning up temporary data (C is correct). These statements accurately describe Oracle's space management capabilities. PTTs store both metadata and data in the temporary tablespace, not memory only. An index altered to UNUSABLE retains its segment until explicitly dropped. A truncated table may retain its segment depending on the DROP STORAGE clause used.
Why the other options are wrong
- A. Private Temporary Tables store both metadata and data in the database's temporary tablespace, not in memory only.
- D. An index altered to UNUSABLE will retain its segment; the segment is only removed if the index is dropped.
- E. A truncated table's segment is retained by default; it is only removed if the TRUNCATE command includes DROP STORAGE.
Question 34
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.)
Show answer and explanation
Correct answer: B, C. Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space; Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.
When using Oracle Managed Files (OMF), adding a datafile without explicitly specifying a path requires DB_CREATE_FILE_DEST to be set with sufficient space. Option B allows explicit path specification with 100MB space, bypassing OMF defaults. Option C specifies that DB_CREATE_FILE_DEST needs 100MB of available space when using OMF without explicit paths. Since the command uses ADD DATAFILE without a path clause, DB_CREATE_FILE_DEST must be configured with adequate space.
Why the other options are wrong
- A. DB_RECOVERY_FILE_DEST is for recovery-related files and flash recovery area, not for datafile creation via OMF.
- D. AUTOEXTEND is for extending existing datafiles, not for creating new ones in response to ADD DATAFILE.
- E. DB_RECOVERY_FILE_DEST is not relevant to this command; only DB_CREATE_FILE_DEST matters.
Question 35
Which three statements are true about the tools used to configure Oracle Net Services? (Choose three.)
Show answer and explanation
Correct answer: D, E, F. Oracle Net Manager can be used to locally configure naming methods on a database server; Enterprise Manager Cloud Control can be used to centrally configure listeners on any managed database server; Enterprise Manager Cloud Control can be used to centrally configure net service names for any database server target
Oracle Net Manager (Option B, though community says it's not correct) can be used locally to configure naming methods, but Enterprise Manager Cloud Control (Option E) centralizes listener configuration across database servers, and Option F enables centralized net service name configuration. Option D states Oracle Net Manager configures naming methods locally on a database server, which is true. Enterprise Manager Cloud Control (Option E) centrally manages listeners and (Option F) centrally manages net service names for any managed database server in the infrastructure.
Why the other options are wrong
- A. Oracle Net Configuration Assistant is used during installation but also for post-installation reconfiguration.
- B. Oracle Net Manager is primarily a local configuration tool for a single server, not centralized for multiple servers; OEMCC provides centralized management.
- C. The lsnrctl utility can start a listener without a listener.ora file if parameters are provided inline.
Question 36
Which three instance situations are possible with the Oracle Database server without multi-tenant? (Choose three.)
Show answer and explanation
Correct answer: A, B, D. two or more instances on separate servers all associated with one database; one instance on one server associated with one database; one instance on one server not associated with any database
In a non-multitenant Oracle Database, multiple instances on separate servers can be associated with one database through Real Application Clusters (RAC) (A is true). One instance on one server can be associated with one database (B is true). One instance can exist without being associated with any database (D is true), an instance is just a set of processes and memory structures that can be started independently. Options C and E are incorrect because a single instance cannot be associated with multiple databases; each database requires its own instance (or shared instances in RAC, but each instance is dedicated to one database at a time).
Why the other options are wrong
- C. A single instance is associated with one database, not multiple databases on the same server.
- E. A single instance cannot be associated with multiple databases on separate servers; it is tied to one database.
Question 37
Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)
Show answer and explanation
Correct answer: C, D. The HAVING clause can be used with aggregating functions in subqueries; The WHERE clause can be used to exclude rows before dividing them into groups
The HAVING clause can be used with aggregating functions in subqueries, allowing filtered aggregation results to be used as input to outer queries (C is correct). The WHERE clause filters rows before they are divided into groups during the GROUP BY operation, providing pre-grouping filtering (D is correct). These statements correctly describe the distinct purposes of WHERE and HAVING. Aggregating functions and columns in HAVING do not need to appear in the SELECT list. WHERE and HAVING can be used in the same statement and are not restricted to different columns. The WHERE clause operates before grouping, not after.
Why the other options are wrong
- A. Aggregating functions and columns used in HAVING clauses do not need to be specified in the SELECT list; they can be used solely for filtering grouped results.
- B. WHERE and HAVING clauses can be used in the same SELECT statement without restriction to different columns; they serve different filtering purposes.
- E. The WHERE clause filters rows before grouping, not after; row filtering after grouping is the purpose of the HAVING clause.
Question 38
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?
Show answer and explanation
Correct answer: E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
The query must display customers whose income level has a value (CUST_INCOME_LEVEL IS NOT NULL) and calculate due amount as 5% of credit limit. The critical issue is that DUE_AMOUNT is a calculated column (cust_credit_limit * .05 AS DUE_AMOUNT), not an actual table column, so it cannot be referenced in the WHERE clause. Instead, we must ensure CUST_CREDIT_LIMIT itself is not null. The correct syntax for NULL comparisons in SQL is IS NOT NULL (or IS NULL), never != NULL, <> NULL, or != NULL. Option E correctly uses IS NOT NULL for both conditions and checks the actual table columns that need validation.
Why the other options are wrong
- A. Attempts to filter on 'due_amount IS NOT NULL' in the WHERE clause, but due_amount is a calculated alias that cannot be referenced in WHERE; should check CUST_CREDIT_LIMIT instead.
- B. Uses != NULL syntax which is incorrect; NULL comparisons require IS NOT NULL or IS NULL operators, not inequality operators.
- C. Uses <> NULL syntax which is incorrect; NULL comparisons require IS NOT NULL or IS NULL operators, and references non-existent 'due_amount' column in WHERE clause.
- D. Uses != NULL syntax which is incorrect; NULL comparisons require IS NOT NULL or IS NULL operators, and attempts to filter on 'due_amount' which is a calculated alias not available in WHERE.
Question 39
Which three statements are true about table data storage in an Oracle Database? (Choose three.)
Show answer and explanation
Correct answer: A, C, D. Data block headers contain their own Data Block Address (DBA); Multiple row pieces from the same row may be stored in different database blocks; Multiple row pieces from the same row may be stored in the same block
Data block headers contain their own Data Block Address (DBA) which identifies the block's location (A). A table row piece can be chained across several database blocks when the row is too large for a single block (C). Multiple row pieces from the same row may be stored in different database blocks due to row chaining or migration (D). Row migration occurs when a row is updated and grows too large, moving to another block while leaving a forwarding pointer. Free space in data blocks is not necessarily contiguous, it can be fragmented. External tables cannot be partitioned and do not support row movement in the traditional sense.
Why the other options are wrong
- B. When rows exceed block size, they chain across blocks, but a single row piece occupies a contiguous section within its block; multiple pieces of the same row exist across blocks, not multiple pieces in one block storing the same piece.
- E. Data block free space is not always contiguous; it becomes fragmented as rows are inserted and deleted.
- F. Index block free space is not always contiguous; it also becomes fragmented over time.
Question 40
What is true about non-equijoin statement performance? (Choose two.)
Show answer and explanation
Correct answer: A, C. Table aliases can improve performance; The join syntax used makes no difference to performance
Table aliases improve performance by reducing the length of references in the query and making queries more readable and efficient for the optimizer (Option A). The join syntax itself (Oracle syntax versus ANSI SQL:1999 syntax) does not inherently affect performance, modern Oracle optimizers treat both the same way, making Option C correct. BETWEEN and >= combined with <= typically perform equivalently in Oracle's cost-based optimizer.
Why the other options are wrong
- B. BETWEEN and >= with <= perform similarly; neither always performs better.
- D. BETWEEN and >= with <= have equivalent performance in modern optimizers.
- E. Both Oracle and ANSI join syntax have equivalent performance with modern query optimizers.
Question 41
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.)
Show answer and explanation
Correct answer: B, D. invoice_date = '15-march-2019' : uses implicit conversion; qty_sold BETWEEN '101' AND '110' : uses implicit conversion
In the expression invoice_date = '15-march-2019', the string is implicitly converted to DATE format because it matches the NLS_DATE_FORMAT pattern, allowing the comparison. In qty_sold BETWEEN '101' AND '110', the string values '101' and '110' are implicitly converted to NUMBER to match the NUMBER column type. Both involve implicit conversion where Oracle automatically converts one data type to another without explicit conversion functions.
Why the other options are wrong
- A. CONCAT requires explicit conversion because mixing NUMBER and DATE types cannot be implicitly handled by CONCAT.
- C. The expression '01-02-2019' is ambiguous and would need explicit conversion to avoid interpretation issues with the date format.
- E. While implicit conversion could occur here, the leading zeros may cause unexpected results, making this less reliable than the other options.
Question 42
Which two statements are true about the rules of precedence for operators? (Choose two.)
Show answer and explanation
Correct answer: D, E. Arithmetic operators with equal precedence are evaluated from left to right within an expression; Multiple parentheses can be used to override the default precedence of operators in an expression
The correct statements are: (D) arithmetic operators with equal precedence (such as + and - or * and /) are evaluated left to right in an expression, following standard mathematical convention; (E) multiple parentheses can be used to override and control the default precedence of operators. Option A is false; the concatenation operator || does not have guaranteed precedence over arithmetic operators, it actually has lower precedence. Option B is incorrect; NULL values affect result values, not operator precedence rules. Option C is wrong; the + operator does not have the highest precedence, parentheses, exponentiation, and multiplication/division have higher precedence.
Why the other options are wrong
- A. The concatenation operator || has lower precedence than arithmetic operators, not higher.
- B. NULL values affect expression results but do not change the rules of operator precedence.
- C. Multiplication and division have higher precedence than the + operator; parentheses have the highest precedence.
Question 43
In one of your databases, you create a user, HR, and then execute this command: GRANT CREATE SESSION TO hr WITH ADMIN OPTION; Which four actions can HR perform? (Choose four.)
Show answer and explanation
Correct answer: A, B, C, D. Revoke the CREATE SESSION privilege from other users; Revoke the CREATE SESSION privilege from user HR; Log in to the database instance; Grant the CREATE SESSION privilege with ADMIN OPTION to other users
When a privilege is granted WITH ADMIN OPTION, the grantee gains administrative control over that privilege. User HR can log in to the database with CREATE SESSION (C is correct). HR can revoke CREATE SESSION from other users because ADMIN OPTION allows administrative revocation (A is correct). HR can revoke CREATE SESSION from itself for the same reason (B is correct). HR can grant CREATE SESSION with ADMIN OPTION to other users, passing along the same administrative capability (D is correct). However, DDL and DML execution in the HR schema depend on specific privileges (CREATE TABLE, INSERT, UPDATE, etc.), not on the CREATE SESSION privilege alone, so E and F are not guaranteed.
Why the other options are wrong
- E. The CREATE SESSION privilege only allows login; executing DDL statements requires specific privileges like CREATE TABLE, CREATE PROCEDURE, etc.
- F. The CREATE SESSION privilege only allows login; executing DML statements requires specific privileges like INSERT, UPDATE, DELETE, and SELECT on the relevant objects.
Question 44
Which three statements are true about a self join? (Choose three.)
Show answer and explanation
Correct answer: B, E, F. The query must use two different aliases for the table; The ON clause can be used; It can be an outer join
A self join is a join where a table is joined to itself. Two different aliases must be used to distinguish the two instances of the table (B is true). The ON clause can be used but is not required, you can use WHERE instead (E is true). A self join can be an outer join as well as an inner join (F is true). Option A is incorrect because ON is not mandatory; you can use WHERE. Option C is incorrect because a self join does not have to be an equijoin; it can use any join condition. Option D is incorrect because self joins can be outer joins, not just inner joins.
Why the other options are wrong
- A. The ON clause is optional; you can use a WHERE clause instead to specify the join condition.
- C. A self join does not have to be an equijoin; you can use any type of join condition such as inequality comparisons.
- D. A self join can be an outer join (LEFT, RIGHT, or FULL OUTER JOIN), not limited to inner joins.
That was 44 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.
Not ready yet? Get the first 89 free as one PDF by email. Get the Half Pack →