1Z0-082

Free 1Z0-082 practice questions

10 questions from our 1Z0-082 practice tests, each with the correct answer and a written explanation. Read them with the answers shown, or switch to quiz mode and answer first.

Back to the 1Z0-082 practice exams

Question 1 of 10, Oracle Database Architecture and Instance Management

Users report that a query on V$SESSION from a PDB shows only sessions for that PDB, but the DBA wants to see all sessions in the CDB along with the container each belongs to. What should the DBA do?

  1. AQuery V$SESSION from the PDB with the CONTAINERS clause enabled for every session row in the CDB
  2. BConnect to PDB$SEED, because the seed exposes session rows for all pluggable databases in the CDB
  3. CSet the CONTAINER parameter to ALL in the PDB session and query DBA_USERS to list active sessions
  4. DConnect to CDB$ROOT as a common user with the needed privileges and query V$SESSION, using the CON_ID column Correct answer

Answer and explanation: When queried from the root, V$ views show rows for all containers, and the CON_ID column identifies each one. Within a PDB, the view is limited to that container. The CONTAINERS clause is used with tables and views in application or common contexts, not by enabling it per session row. There is no CONTAINER=ALL session setting, and DBA_USERS lists users, not sessions. PDB$SEED cannot be used for normal queries of this kind.

Question 2 of 10, Oracle Database Architecture and Instance Management. Choose 2.

Which two statements about the database writer process are true? (Choose two.)

  1. AIt must finish writing all dirty buffers before LGWR can write the redo
  2. BIt writes every modified buffer immediately at commit to guarantee durability
  3. CIt can write uncommitted changes to data files, because undo protects them Correct answer
  4. DIt only writes blocks that belong to tables, never index or undo blocks
  5. EIt writes dirty buffers when a checkpoint occurs or free buffers are scarce Correct answer

Answer and explanation: DBWn may write blocks containing uncommitted changes, because the related undo and redo allow them to be rolled back. It writes dirty buffers on checkpoints, when free buffers are scarce and at other triggers. Durability at commit comes from LGWR, not DBWn writing blocks. The write-ahead rule requires redo to be written before the matching data block, not the reverse. DBWn writes table, index and undo blocks alike.

Question 3 of 10, Oracle Net Services and Database Tools. Choose 2.

Which two statements about the shared server architecture in Oracle Database 19c are true? (Choose two.)

  1. ASetting SHARED_SERVERS to a value greater than zero enables shared server even if DISPATCHERS is not set Correct answer
  2. BEach dispatcher can serve only one client connection at a time, so one dispatcher is needed per session
  3. CEnabling shared server always requires an instance restart because SHARED_SERVERS is a static parameter
  4. DShared server and dedicated server connections can coexist in the same instance at the same time Correct answer
  5. ESYSDBA connections must go through a dispatcher once shared server has been enabled for the instance

Answer and explanation: Shared server is enabled by setting SHARED_SERVERS above zero, and Oracle creates a default TCP dispatcher if DISPATCHERS is not set. Dedicated and shared connections can run side by side. SHARED_SERVERS is dynamic, so no restart is needed. Administrative SYSDBA connections use dedicated servers. A dispatcher multiplexes many client connections, which is the point of the architecture.

Question 4 of 10, Users, Roles, Privileges and Profiles

Which statement about password-related profile limits in Oracle Database 19c is true?

  1. APASSWORD_GRACE_TIME sets how many days users may log in with a warning after expiry Correct answer
  2. BPASSWORD_LOCK_TIME sets the days before an unused password expires automatically
  3. CPASSWORD_LIFE_TIME sets how long an account stays locked after failed login attempts
  4. DPASSWORD_REUSE_MAX sets the maximum length allowed for a password in the database

Answer and explanation: PASSWORD_GRACE_TIME is the number of days after PASSWORD_LIFE_TIME expires during which login is still allowed with a warning. PASSWORD_LOCK_TIME is how long an account stays locked after exceeding FAILED_LOGIN_ATTEMPTS. PASSWORD_REUSE_MAX is the number of password changes before a password can be reused, not a length. PASSWORD_LIFE_TIME is the lifetime of a password, not a lock duration.

Question 5 of 10, Tablespaces, Storage, Space and Undo Management

A DBA wants unexpired undo never to be overwritten, even if this causes DML to fail for lack of undo space. Which setting achieves this?

  1. AALTER TABLESPACE undotbs1 RETENTION GUARANTEE Correct answer
  2. BALTER TABLESPACE undotbs1 AUTOEXTEND OFF
  3. CALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL
  4. DALTER SYSTEM SET UNDO_RETENTION=0 SCOPE=BOTH

Answer and explanation: RETENTION GUARANTEE on the undo tablespace makes Oracle keep unexpired undo for the full retention period, even if transactions then fail for lack of space. UNDO_RETENTION=0 removes the retention target rather than guaranteeing it. Disabling autoextend limits growth but does not guarantee retention. Manual undo management uses rollback segments and removes automatic retention entirely.

Question 6 of 10, Tablespaces, Storage, Space and Undo Management

After a large purge, the DBA wants to release unused space below the high-water mark of table ORDERS in an ASSM tablespace while users keep querying it. Which sequence is correct?

  1. AALTER TABLE orders ENABLE ROW MOVEMENT; then ALTER TABLE orders SHRINK SPACE Correct answer
  2. BALTER TABLE orders MOVE ONLINE; then ALTER TABLE orders ENABLE ROW MOVEMENT
  3. CALTER TABLE orders SHRINK SPACE; then ALTER TABLE orders ENABLE ROW MOVEMENT
  4. DALTER TABLE orders DEALLOCATE UNUSED; then ALTER TABLE orders SHRINK SPACE COMPACT

Answer and explanation: Segment shrink relocates rows, so row movement must be enabled first, then SHRINK SPACE compacts the rows and lowers the high-water mark online. DEALLOCATE UNUSED only frees space above the high-water mark. MOVE ONLINE also reorganizes the table but the order shown enables row movement afterward for no purpose and is not the shrink procedure. Running SHRINK before enabling row movement fails with ORA-10636.

Question 7 of 10, Moving Data with SQL*Loader, Data Pump and External Tables

A long Data Pump export job is running. The DBA's terminal disconnects. How can the DBA monitor and control the same job again?

  1. ARestart the database instance so that the job restarts from the beginning
  2. BQuery DBA_DATAPUMP_JOBS and run STOP_JOB from SQL*Plus for the job
  3. CRun expdp again with the same parameters, which joins the running job
  4. DRun expdp with ATTACH=job_name to reconnect in interactive-command mode Correct answer

Answer and explanation: The ATTACH parameter connects a new client to an existing Data Pump job, giving interactive commands like STATUS, STOP_JOB and START_JOB. Re-running the same command would start a new job or fail because of the existing job name. DBA_DATAPUMP_JOBS shows jobs but STOP_JOB is an interactive command, not a SQL statement. Restarting the instance is unnecessary and disruptive.

Question 8 of 10, SQL Retrieval, Restriction, Sorting and Single-Row Functions

A developer must select the literal text It's done without doubling the apostrophe. Which expression works?

  1. A'It\'s done' ESCAPE '\'
  2. Bq'[It's done]' Correct answer
  3. C'It' || CHR(34) || 's done'
  4. D"It's done"

Answer and explanation: The alternative quoting mechanism q'[...]' lets a string contain single quotes without escaping them. Double quotes denote identifiers, not string literals. Oracle SQL does not escape quotes with a backslash, and ESCAPE is only a LIKE clause. CHR(34) is a double quote character, so that expression produces the wrong text.

Question 9 of 10, Aggregation, Joins, Subqueries and SET Operators

A report must list every department, including departments that have no employees, with employee names where they exist. Which join returns this?

  1. Adepartments d RIGHT OUTER JOIN employees e ON d.department_id = e.department_id
  2. Bdepartments d CROSS JOIN employees e
  3. Cdepartments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id Correct answer
  4. Ddepartments d JOIN employees e ON d.department_id = e.department_id

Answer and explanation: A left outer join keeps all rows from the left table, departments, and shows NULL employee columns when there is no match. An inner join drops departments without employees. A right outer join keeps all employees instead, including those without departments. A cross join returns every combination, not matched rows.

Question 10 of 10, DML, Transactions, DDL and Schema Objects

A query returns SELECT FROM_TZ(TIMESTAMP '2026-06-01 09:00:00', 'America/New_York') AT TIME ZONE 'UTC' FROM dual. What does it return?

  1. A01-JUN-2026 14:00 UTC, because New York is always five hours behind UTC
  2. B01-JUN-2026 13:00 UTC, because New York is four hours behind UTC in June Correct answer
  3. C01-JUN-2026 05:00 UTC, because UTC is four hours behind New York in June
  4. D01-JUN-2026 09:00 UTC, because AT TIME ZONE only relabels the time zone

Answer and explanation: FROM_TZ attaches the New York zone to the timestamp, and AT TIME ZONE converts it; in June New York observes daylight time (UTC-4), so 09:00 becomes 13:00 UTC. AT TIME ZONE converts rather than relabels. UTC is ahead of New York, not behind. The five-hour offset applies only in winter.