Oracle 1Z0-082 Practice Questions with Explanations
Free Oracle 1Z0-082 practice questions. 10 of them, each with the correct answer, a full explanation, and the reason every other option is wrong. These are real questions from the 1Z0-082 exam, not paraphrases, and every explanation is written out rather than just marking the right letter.
They are drawn from the same bank as the full 1Z0-082 pack, which has 178 questions in total.
Get the full 1Z0-082 question bank (178 questions) →
1Z0-082 practice questions
Question 1
Which statement is true about the INTERSECT operator used in compound queries?
- A. Multiple INTERSECT operators are not possible in the same SQL statement
- B. It processes NULLs in the selected columns
- C. INTERSECT is of lower precedence than UNION or UNION ALL
- D. It ignores NULLs
Show answer and explanation ▾
Correct answer: B
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 2
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?
- A. in the undo tablespace
- B. in the SYSAUX tablespace
- C. in the SGA
- D. in the PGA
- E. in the temporary tablespace
Show answer and explanation ▾
Correct answer: D
When temporary undo is enabled in automatic undo management, undo data for DML operations on temporary tables is stored in the PGA (Program Global Area) of the session. This is more efficient than storing it in the undo tablespace because temporary table data is session-specific and does not need to be persisted across transactions or instances.
Why the other options are wrong:
- A. Undo for temporary tables with temporary undo enabled is not stored in the undo tablespace.
- B. SYSAUX is used for auxiliary data, not temporary undo storage.
- C. SGA is shared memory, while temporary undo is session-specific and stored in PGA.
- E. The temporary tablespace stores temporary table data itself, not the undo for that data.
Question 3
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?
- A. INTERVAL YEAR TO MONTH
- B. INTERVAL DAY TO SECOND
- C. TIMESTAMP WITH LOCAL TIMEZONE
- D. TIMESTAMP
- E. TIMESTAMP WITH TIMEZONE
Show answer and explanation ▾
Correct answer: B
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 4
In the spfile of a single instance database, LOCAL_LISTENER is set to LISTENER_1. The TNSNAMES.ORA file in $ORACLE_HOME/network/admin in the database home contains: Which statement is true?
- A. Dynamic service registration cannot be used for this database instance
- B. The LREG process registers services dynamically with the LISTENER_1 listener
- C. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration
- D. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database instances
- E. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration
Show answer and explanation ▾
Correct answer: B
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 5
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?
- A. SELECT &&col1, &&col2 FROM &table WHERE &&condition = &&cond;
- B. SELECT &col1, &col2 FROM &&table WHERE &condition;
- C. SELECT &col1, &col2 FROM "&table" WHERE &condition;
- D. SELECT '&&col1', '&&col2' FROM &table WHERE '&&condition' = '&cond';
- E. SELECT &&col1, &&col2 FROM &table WHERE &&condition;
Show answer and explanation ▾
Correct answer: B
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 6
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?
- A. SELECT * FROM customers WHERE city LIKE 'D_%';
- B. SELECT * FROM customers WHERE city = '%D_';
- C. SELECT * FROM customers WHERE city LIKE 'D_';
- D. SELECT * FROM customers WHERE city = 'D_%';
Show answer and explanation ▾
Correct answer: A
To find cities starting with 'D' followed by at least two characters, we need the LIKE operator with the pattern 'D_%'. The underscore (_) wildcard matches exactly one character, so 'D_%' means: D followed by at least one character (the first _) followed by zero or more characters (the %). This requires at least two characters after D. Option A uses LIKE correctly with the proper pattern 'D_%'.
Why the other options are wrong:
- B. Uses the equality operator (=) instead of LIKE; pattern matching requires LIKE, not =
- C. The pattern 'D_' only matches cities with exactly two characters (D plus one character), missing cities with more than two characters
- D. Uses the equality operator (=) instead of LIKE; equality compares literal strings, not patterns
Question 7
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?
- A. COLUMN STORE COMPRESS FOR QUERY LOW
- B. ROW STORE COMPRESS BASIC
- C. COLUMN STORE COMPRESS FOR ARCHIVE LOW
- D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH
- E. ROW STORE COMPRESS ADVANCED
Show answer and explanation ▾
Correct answer: E
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 8
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?
- A. INTERSECT
- B. UNION ALL
- C. UNION
- D. SUBTRACT
- E. MINUS
Show answer and explanation ▾
Correct answer: E
The MINUS operator (also called EXCEPT in some SQL dialects) returns rows from the first query that do not appear in the second query. In this case, SALES1 MINUS SALES2 returns all rows present in SALES1 that are not present in SALES2, which is exactly what the question requires. This operator removes duplicates by default and effectively shows the difference between the two sets.
Why the other options are wrong:
- A. INTERSECT returns only rows that appear in both tables, not rows unique to SALES1.
- B. UNION ALL combines all rows from both tables including duplicates, not showing the difference.
- C. UNION combines rows from both tables and removes duplicates, not showing what is unique to SALES1.
- D. SUBTRACT is not a standard SQL set operator; the correct term is MINUS.
Question 9
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?
- A. The value is changed only in the PFILE and takes effect at the next instance startup
- B. The value is changed for the current instance and in the PFILE
- C. It fails because the SCOPE clause is missing
- D. Change is applied to the current instance, but does not persist after instance restart
Show answer and explanation ▾
Correct answer: D
When using ALTER SYSTEM SET without the SCOPE clause on an instance started with a PFILE, the default behavior is SCOPE=BOTH. However, since the instance is using a PFILE (not an SPFILE), the change cannot be persisted to the PFILE file itself-only the current instance memory is affected. The change applies immediately to the running instance but is lost upon restart because PFILE is a static text file that cannot be modified by Oracle. To persist changes with a PFILE, you must manually edit the file.
Why the other options are wrong:
- A. Incorrect because ALTER SYSTEM changes affect the current instance immediately when memory is available; changes are not deferred until startup.
- B. Incorrect because a PFILE cannot be modified by Oracle processes-only SPFILEs can be dynamically updated by ALTER SYSTEM commands.
- C. Incorrect because the SCOPE clause is optional and defaults to SCOPE=BOTH; the command succeeds without explicitly stating it.
Question 10
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?
- A. as SCOTT in DALLAS_DB
- B. as SCOTT in BOSTON_DB
- C. as SCOTT in BOSTON_DB and SYS in DALLAS_DB
- D. as SYS in both the databases
- E. as SCOTT in both the databases
Show answer and explanation ▾
Correct answer: B
A database link must be created in the source database where you want to originate the connection from. To allow only SCOTT in BOSTON_DB to access SCOTT schema in DALLAS_DB, the CREATE DATABASE LINK command must be executed as SCOTT in BOSTON_DB. The database link credentials (SCOTT/tiger) specify which user to connect as in the remote database, but the link itself is created in the current database where the command runs.
Why the other options are wrong:
- A. The link should be created in the source database (BOSTON_DB), not the target.
- C. Only SCOTT in BOSTON_DB is required; SYS privileges are not needed to create a private database link.
- D. SYS is not required; a private database link can be created by the user who will use it.
- E. The link only needs to be created in BOSTON_DB; it does not need to exist in both databases.
Get the complete 1Z0-082 bank
These 10 questions are roughly 29% of the bank. The full pack has 178 real 1Z0-082 questions, each with the same depth of explanation, plus a questions-only PDF for timed practice and free updates forever.
View the full Oracle 1Z0-082 question bank →