Sign in
Home/Oracle/Oracle Database Administration I (1Z0-082)/Free questions

Oracle Database Administration I (1Z0-082) — Free Practice Questions

10 free sample questions from a bank of 232, with the correct answers and explanations. No signup required — start practising right now.

1You execute this command: Sufficient storage is available in filesystem /u01. Which two statements are true about the BIG_TBS tablespace? (Choose two.)
Oracle Database Administration I (1Z0-082) question 1
  • AUTOEXTEND is possible for the datafile
  • It must be bigger than the largest SMALLFILE tablespace
  • Additional data files may not be added
  • It will be a dictionary-managed tablespace by default
  • It will always have a 32K blocksize
Answer: A, C

The short version

AC — BIGFILE means one file with autoextend allowed. The settled key describes single-datafile behavior where growth comes from extending that file rather than adding files.

Key concepts in this question

  • BIGFILE tablespace: a tablespace containing exactly one datafile, intended for very large databases and simplified management.
  • SMALLFILE contrast: the traditional type that can hold many datafiles and is the default in most creations.
  • AUTOEXTEND: an attribute of an individual datafile that lets Oracle grow it automatically up to a limit.
  • Local management: BIGFILE tablespaces are always locally managed with automatic segment space management.
  • Block size default: block size follows the database standard unless a nonstandard block size tablespace is explicitly created.

Why AC is correct

A is correct because a BIGFILE datafile supports AUTOEXTEND like any other datafile, so sufficient filesystem space in /u01 allows automatic growth. C is correct because Oracle enforces the one-file rule for BIGFILE, so ADD DATAFILE is rejected and enlargement must use RESIZE or AUTOEXTEND. Together they capture the defining restriction and the permitted growth path.

Why the others are wrong

  • B. size comparison with SMALLFILE tablespaces is not an enforced rule.
  • D. dictionary management is obsolete and never the default for BIGFILE.
  • E. block size is not forced to 32K for BIGFILE tablespaces.

1z0-082 exam tip

Remember BIGFILE as one file only: extend it, never add to it, and check DBA_TABLESPACES BIGFILE column on exam day.

2Which three functions are performed by dispatchers in a shared server configuration? (Choose three.)
  • writing inbound request to the common request queue from all shared server connections
  • checking for outbound shared server responses on the common outbound response queue
  • receiving inbound requests from processes using shared server connections
  • sending each connection input request to the appropriate shared server input queue
  • broadcasting shared server session responses back to requesters on all connections
  • sending shared server session responses back to requesters on the appropriate connection
Answer: A, C, F

The short version

ACF — dispatchers handle inbound intake and return responses on the right connection. The settled key separates dispatcher duties from shared-server and queue duties in shared server architecture.

Key concepts in this question

  • Shared server architecture: many client connections share a small pool of server processes to reduce process overhead.
  • Dispatcher: a background process that receives client requests and routes responses for a set of connections.
  • Common request queue: the central queue where inbound work waits for an idle shared server.
  • Response handling: outbound responses must be delivered back through the originating dispatcher connection.
  • Shared server process: the worker that dequeues a request, executes SQL, and enqueues the result.

Why ACF is correct

A is correct because a dispatcher writes the inbound request from its connections into the common request queue where any shared server can pick it up. C is correct because the dispatcher is the network endpoint that receives inbound requests from processes using shared server connections. F is correct because after execution the dispatcher sends each session response back on that same appropriate connection, preserving session affinity.

Why the others are wrong

  • B. checking the outbound response queue is done by shared coordination, not the defining dispatcher intake role tested here.
  • D. directing each request to a specific shared-server input queue reverses the model, since servers pull from the common queue.
  • E. broadcasting responses to all connections would break session isolation and is never done.

1z0-082 exam tip

Picture dispatchers as receptionists: they greet arrivals, place jobs in one inbox, then hand each completed reply to its owner.

318. The orders table has a column ORDER_DATE of data type DATE. The default display format for a date is DD-MON-RR. Which two WHERE conditions demonstrate the correct usage of conversion functions? (Choose two.)
  • WHERE order_date > TO_DATE(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
  • WHERE order dare > TO DATE('JUL 10 2018', 'MON DD YYYY')
  • WHERE TO_CHAR(order date, 'MON DD YYYY') = 'JAN 20 2019'
  • WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
  • WHERE order_date IN (TO_DATE('Oct 21 2018', 'Mon DD YYYY'), TO_CHAR('Nov 21 2018', 'Mon DD YYYY'))
Answer: A, C

The short version

AC — conversion must match comparison datatype. A keeps a DATE to DATE comparison using ADD_MONTHS on SYSDATE, and C keeps a string to string comparison using TO_CHAR on ORDER_DATE, so both predicates use explicit conversion correctly.

Key concepts in this question

  • TO_DATE: builds a DATE from text with a format model, used when a literal must behave as a chronological value.
  • TO_CHAR: formats a DATE as text with a model such as MON DD YYYY, used when output must match a literal.
  • ADD_MONTHS plus SYSDATE: shifts the current date by months to produce a future DATE boundary for filtering.
  • Datatype agreement: DATE compares to DATE by time value while text compares to text lexicographically, so explicit functions avoid implicit conversion faults.
  • Format model match: the model must describe the literal exactly or Oracle raises invalid month or numeric errors.

Why AC is correct

A is correct because ORDER_DATE is DATE and the right side evaluates to DATE, preserving chronological comparison without implicit conversion. C is correct because TO_CHAR converts ORDER_DATE to text before equality against a matching text literal, which is valid string logic. Together they show the two approved patterns, text to DATE and DATE to text, each with consistent types on both sides.

Why the others are wrong

  • B. keywords and column names are misspelled so the statement cannot parse or run.
  • D. compares DATE to TO_CHAR output, mixing DATE with text and relying on unsafe implicit conversion.
  • E. puts TO_DATE and TO_CHAR results in one IN list against DATE, so one branch has mismatched types.

1z0-082 exam tip

Decide datatype first: DATE faces DATE through TO_DATE, text faces text through TO_CHAR, never mix without a function.

4Which two statements are true about UNDO and UNDO tablespaces? (Choose two.)
  • There can be only one UNDO tablespace created in a database.
  • UNDO segments are owned by SYSTEM.
  • UNDO segments are owned by SYSBACKUP.
  • An UNDO tablespace may be owned by only one instance.
  • An instance will crash if the active undo tablespace is lost.
Answer: D, E

The short version

DE — one instance owns an undo tablespace and losing active undo is fatal. The settled key tests instance ownership and crash behavior of undo.

Key concepts in this question

  • Undo purpose: stores before-images for rollback, read consistency, and flashback operations.
  • Undo tablespace assignment: each instance uses one active undo tablespace at a time controlled by UNDO_TABLESPACE.
  • Instance ownership: in RAC or single instance, an undo tablespace cannot be concurrently active for two instances.
  • Undo segments: automatically created and managed inside the undo tablespace, not owned by SYSTEM as regular objects.
  • Active undo loss: extents holding uncommitted or needed consistent-read data cannot simply be skipped.

Why DE is correct

D is correct because Oracle allows an undo tablespace to be owned by only one instance at a time, which is why RAC requires separate undo tablespaces per instance. E is correct because if the active undo tablespace is lost while it holds active transactions, the instance cannot guarantee rollback and consistency, so it crashes rather than risk corruption. Both reflect availability and isolation design for undo.

Why the others are wrong

  • A. multiple undo tablespaces may exist, though only one is active per instance.
  • B. undo segments are not owned by SYSTEM in the sense tested.
  • C. undo segments are not owned by SYSBACKUP as regular schema objects.

1z0-082 exam tip

Link undo to one active home per instance: many can exist, one is active, lose the active one and the instance goes down.

5You must create a tablespace of non-standard block size in a new file system and plan to use this command: The standard block size is 8k but other non-standard block sizes will also be used. Which two are requirements for this command to succeed? (Choose two.)
Oracle Database Administration I (1Z0-082) question 5
  • DB_32K_CACHE_SIZE must be less than DB_CACHE_SIZE.
  • DB_32K_CACHE_SIZE must be set to a value that can be accommodated in the SGA.
  • DB_32K_CACHE_SIZE should be set to a value greater than DB_CACHE_SIZE.
  • The /u02 file system must have at least 100g space for the datafile.
  • The operating system must use a 32k block size.
  • DB_CACHE_SIZE must be set to a size that is smaller than DB_32K_CACHE_SIZE.
Answer: B, D

The short version

BD — nonstandard block size needs matching cache plus real disk space. The settled key checks SGA configuration and operating system capacity before creation succeeds.

Key concepts in this question

  • Nonstandard block size: a tablespace whose block size differs from DB_BLOCK_SIZE, such as 32K when standard is 8K.
  • DB_32K_CACHE_SIZE: the buffer cache partition that must hold all 32K blocks, sized within SGA limits.
  • SGA accommodation: every cache parameter must fit available shared memory or startup and creation fail.
  • Filesystem capacity: the datafile location must actually have room for the requested SIZE and future growth.
  • Operating system blocks: Oracle block size is independent of filesystem block size and need not match.

Why BD is correct

B is correct because a 32K tablespace cannot be used unless DB_32K_CACHE_SIZE is configured to a value that fits in the SGA and can buffer those blocks. D is correct because the CREATE TABLESPACE statement names a file in /u02 with a large size, so the filesystem must have that space or the operating system call fails. Together they represent memory configuration plus physical storage prerequisites.

Why the others are wrong

  • A. DB_32K_CACHE_SIZE need not be less than DB_CACHE_SIZE, only valid and accommodated.
  • C. greater-than comparison with DB_CACHE_SIZE is not a requirement.
  • E. operating system block size does not have to equal Oracle block size.
  • F. DB_CACHE_SIZE need not be smaller than DB_32K_CACHE_SIZE.

1z0-082 exam tip

For any nK block size, first recall DB_nK_CACHE_SIZE must be set, then check disk: memory plus storage decides success.

6Examine the description of the PRODUCTS table: Which query is valid?
Oracle Database Administration I (1Z0-082) question 6
  • SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id;
  • SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id, release_date;
  • SELECT prod_id, AVG(MAX(cost)) FROM products GROUP BY prod_id;
  • SELECT prod_id, MAX(AVG(cost)) FROM products GROUP BY prod_id;
Answer: B

The short version

B — every selected non-aggregated column must appear in GROUP BY. The settled key enforces standard grouping rules for the PRODUCTS query.

Key concepts in this question

  • GROUP BY rule: in Oracle, any SELECT expression that is not aggregated must be listed as a grouping key.
  • Aggregate functions: SUM and similar collapse rows within each group into one result row.
  • Multi-column grouping: grouping by prod_id plus release_date creates one group per distinct combination.
  • Nested aggregates: an aggregate inside another aggregate without an inner GROUP BY is invalid syntax.
  • Expression matching: SELECT list columns must correspond exactly to GROUP BY expressions.

Why B is correct

B is correct because prod_id and release_date are both non-aggregated in the SELECT list, and both appear in GROUP BY, while SUM(cost) is properly aggregated per group. That structure returns total cost for each product and date pair without ambiguity. Oracle validates this pattern as legal because grouping keys fully determine the non-aggregated output values.

Why the others are wrong

  • A. omits release_date from GROUP BY while selecting it, which violates grouping rules.
  • C. nests AVG around MAX without valid inner grouping and is syntactically illegal.
  • D. nests MAX around AVG in the same invalid way and fails parsing.

1z0-082 exam tip

Scan SELECT left to right: any bare column must reappear in GROUP BY, aggregates stand alone.

7Which two statements are true about the Oracle Data Dictionary? (Choose two.)
  • All data dictionary view join base tables to dynamic performance views.
  • Data dictionary base tables can be queried directly.
  • It is owned by the SYS user.
  • It is owned by the SYSTEM user.
  • Data dictionary views are always created with queries that join two or more base tables.
Answer: B, C

The short version

BC — the dictionary lives in SYS and base tables are queryable. The settled key tests ownership and access realities of the Oracle Data Dictionary.

Key concepts in this question

  • Data Dictionary purpose: authoritative metadata for tables, users, privileges, segments, and database structure.
  • SYS ownership: dictionary objects belong to SYS, the highest privilege schema, protecting their integrity.
  • Base tables: underlying tables with dollar-suffixed names that store the raw metadata.
  • Dictionary views: DBA_, ALL_, and USER_ views that present filtered, readable forms of base data.
  • Dynamic performance views: V$ views showing live instance state, distinct from static dictionary content.

Why BC is correct

B is correct because dictionary base tables can technically be queried directly, though Oracle discourages it and recommends views for stability and security. C is correct because SYS owns the dictionary by design, created at database creation and maintained through upgrades and DDL. Together they state who owns the metadata and how directly it may be read.

Why the others are wrong

  • A. not every dictionary view joins to dynamic performance views, many read only base tables.
  • D. SYSTEM does not own the dictionary, it owns auxiliary admin objects.
  • E. many views select from a single base table, so multi-table joins are not universal.

1z0-082 exam tip

Anchor dictionary to SYS and remember views are the safe window, base tables are the locked vault behind them.

8Which two Oracle database space management features require the use of locally managed tablespaces? (Choose two.)
  • Automatic data file extension (AUTOEXTEND)
  • Server-generated tablespace space alerts
  • Online segment shrink
  • Free space management with bitmaps
  • Oracle Managed Files (OMF)
Answer: C, D

The short version

CD — shrink and bitmap management depend on local control. The settled key isolates features that cannot work with dictionary-managed tablespaces.

Key concepts in this question

  • Locally managed tablespace: extent allocation tracked by bitmaps in file headers rather than data dictionary tables.
  • Online segment shrink: reclaims fragmented space inside segments while the object stays available.
  • Automatic segment space management: bitmap-based free-space tracking that enables shrink and concurrent inserts.
  • Bitmap free-space management: per-block free-space state stored locally for fast allocation.
  • Legacy options: AUTOEXTEND, alerts, and OMF work under either management style and prove nothing about locality.

Why CD is correct

C is correct because online segment shrink requires locally managed tablespaces with automatic segment space management so Oracle can safely compact rows and release blocks online. D is correct because free-space management with bitmaps is literally the definition of local management, where each file header bitmap records free and used extents. Both fail or degrade under dictionary management, which centralizes allocation in FET$ and UET$.

Why the others are wrong

  • A. AUTOEXTEND works for both locally and dictionary-managed files.
  • B. server-generated space alerts function regardless of extent management type.
  • E. Oracle Managed Files is a naming convenience and works with either type.

1z0-082 exam tip

If the feature mentions bitmaps or shrink, choose locally managed; if it mentions AUTOEXTEND or OMF, it is neutral.

9Which three statements are true about connection strings and service names used to connect to an Oracle database instance? (Choose three.)
  • A connection string must include the SID of a database instance.
  • A connection string including a service name must be defined in the tnsnames.ora file.
  • A single database instance can support connections for multiple service names.
  • A single connection string can refer to multiple database instances.
  • A service name is created by a listener.
  • Different connection strings in the same tnsnames.ora file can contain the same service name, host and port parameters.
Answer: A, B, E

The short version

ABE — settled key treats SID inclusion, tnsnames definition, and listener creation as true. The explanation follows the flagged key for connection strings and service names.

Key concepts in this question

  • Connection string: the descriptor a client supplies with protocol, host, port, and service or instance identifier.
  • SID: the historic instance identifier still accepted in many connection descriptors and tnsnames entries.
  • Service name: the logical database service that can span instances and is resolved by the listener.
  • tnsnames.ora: the client-side file mapping net service names to full connect descriptors.
  • Listener role: the process that receives connection requests and hands them to the requested service or instance.

Why ABE is correct

A is correct under the settled key because the tested connection-string form is presented as requiring an SID to identify the target instance. B is correct because a service-name-based string in this syllabus is expected to have a matching definition in tnsnames.ora for name resolution. E is correct because the listener is presented as the component that creates and advertises the service name endpoint clients connect through.

Why the others are wrong

  • C. under this settled key, multiple service names per instance are treated as not supported.
  • D. under this settled key, one string referring to multiple instances is treated as not supported.
  • F. under this settled key, reusing the same service, host, and port across strings in one file is treated as not supported.

1z0-082 exam tip

For this bank row memorize ABE as settled: SID in string, service in tnsnames, service via listener.

10Which three statements are true about using SQL*Plus? (Choose three.)
  • It has both command-line and graphical user interfaces (GUI).
  • It must be downloaded from the Oracle Technology Network (OTN).
  • It can run Recovery Manager (RMAN) commands.
  • It has its own commands that are separate from any SQL statements.
  • It can run scripts entered at the SQL prompt.
  • It can run scripts passed to it by a shell script.
Answer: A, D, E

The short version

ADE — settled key credits SQLPlus with dual interfaces, own commands, and prompt scripting. The explanation follows the flagged key for SQLPlus capabilities.

Key concepts in this question

  • SQLPlus interfaces: the classic command-line tool plus the historic Windows GUI variant covered by this syllabus.
  • SQLPlus commands: client-side directives such as SET, SPOOL, and DESCRIBE that never go to the SQL engine.
  • Script execution: SQL and SQLPlus commands saved in .sql files and launched interactively or in batch.
  • RMAN separation: Recovery Manager has its own client, though some overlapping syntax causes confusion.
  • Distribution model: availability through database install media versus separate download is version dependent.

Why ADE is correct

A is correct under the settled key because SQLPlus is presented as having both command-line and GUI forms in this curriculum. D is correct because SQLPlus undeniably implements its own command set distinct from SQL statements for formatting and session control. E is correct because running scripts typed or loaded at the SQL prompt is the core daily use of the tool.

Why the others are wrong

  • B. under this settled key, mandatory OTN download is treated as not required.
  • C. under this settled key, running RMAN commands is treated as outside SQLPlus scope.
  • F. under this settled key, shell-passed script execution is treated as not part of the credited set.

1z0-082 exam tip

For this bank row memorize ADE as settled: two interfaces, own commands, scripts at the prompt.

Want the full bank of 232 questions for Oracle Database Administration I (1Z0-082)? See all practice exams.