Free Oracle 1Z0-183 Practice Test Questions and Answers (2026)

B: Oracle 23ai Annotations are designed to store structured metadata as key-value pairs directly in the data dictionary. This metadata is queryable through new dictionary views (e.g., USERANNOTATIONS, DBAANNOTATIONSUSAGE), allowing applications and tools to programmatically access schema object metadata.

C: This statement demonstrates the correct DDL syntax for applying annotations to a table column during its creation. The ANNOTATIONS keyword is followed by a parenthesized, comma-separated list of key-value pairs, where the key is an identifier and the value is a string literal.

A. A COMMENT stores a single, unstructured string. While it could contain JSON text, the database does not parse or validate it, unlike annotations which are inherently structured.

D. SEGMENT CREATION DEFERRED is a physical storage clause that postpones the creation of a table's segment until the first row is inserted. It is unrelated to metadata management.

1. Oracle Database Development Guide, 23ai. Chapter 27, "Using Annotations". This chapter describes annotations as "key-value properties that you can associate with database objects" and mentions they are "stored centrally in the data dictionary and can be discovered by querying dictionary views."

2. Oracle Database SQL Language Reference, 23ai. Chapter 7, "SQL Statements: C to D", Section: "CREATE TABLE". The syntax diagram for columndefinition includes the annotationsclause, showing the exact syntax ANNOTATIONS ( 'key' 'value' [, 'key' 'value']... ).

3. Oracle Database Reference, 23ai. Chapter 3, "Static Data Dictionary Views". This reference lists the new views for annotations, such as DBAANNOTATIONS and USERANNOTATIONS, confirming their queryable nature.

In Oracle Database 23ai, the DBMSVECTOR package is the primary PL/SQL interface for managing AI Vector Search components. To import a pre-trained ONNX model from a file system location (represented by a directory object) into the database, the DBMSVECTOR.LOADONNXMODEL procedure is used. This procedure takes the directory object name and the model file name as arguments, loading the model into the data dictionary so it can be used by vector generation functions like VECTOREMBEDDING.

A. DBMSDATAMINING.IMPORTMODEL is part of the legacy Oracle Data Mining feature and is not used for the new AI Vector Search functionality.

B. DBMSNETWORKACLADMIN is a package used for managing network Access Control Lists (ACLs) and is unrelated to machine learning models.

C. DBMSVECTORCHAIN is a package for building Retrieval-Augmented Generation (RAG) applications and does not handle the initial import of embedding models.

1. Oracle Database AI Vector Search User's Guide, 23ai. Chapter 5, "Using Pre-trained Models for Vector Generation", Section "Loading an ONNX Model". The guide explicitly documents the DBMSVECTOR.LOADONNXMODEL procedure for this purpose.

2. Oracle Database PL/SQL Packages and Types Reference, 23ai. Chapter 218, "DBMSVECTOR". The reference details the LOADONNXMODEL procedure's signature and purpose.

The most efficient and native method to create standardized Application PDBs is by using an application seed. An application seed is a user-created PDB within an application root that serves as a template. When a new Application PDB is created by cloning the application seed, the process is significantly faster because it uses storage-level snapshot or copy-on-write technologies. This approach ensures that all new PDBs start with the identical pre-configured data and application version, directly addressing the requirement for accelerated and consistent provisioning.

B. This is a generic, slower method that does not leverage the specific optimizations of Application Containers for rapid provisioning.

C. A Snapshot Standby is a disaster recovery or testing feature and is not used for provisioning new PDBs in a primary environment.

D. While a standard read-only PDB can be cloned, the AS SEED clause creates a specially optimized template for much faster cloning operations.

1. Oracle Database Multitenant Administrator's Guide, 19c. Chapter 11, "Using Application Containers with a Multitenant Environment". Section: "About Creating Application PDBs in an Application Container". This section explicitly states, "To create application PDBs quickly, you can create an application seed."

2. Oracle Database SQL Language Reference, 19c. "CREATE PLUGGABLE DATABASE". The documentation for the AS SEED clause states it creates an application seed, which can be used as a template to create new application PDBs.

To grant a privilege to a common user across all existing and future Pluggable Databases (PDBs), the GRANT statement must be executed from the root container (CDB$ROOT) with the CONTAINER=ALL clause. This clause ensures the grant is propagated to the root, all current PDBs, and any PDBs created later. The user C##SECMGR is a common user, as indicated by the C## prefix, making this operation valid.

B: CONTAINER=CURRENT would only grant the privilege in the current container (the root), not in any PDBs.

C: The CONTAINER=ALL clause is only valid when executed from the root container; running it in a PDB would result in an error.

D: ALTER USER is used to modify user properties like password or default tablespace, not to grant system or object privileges.

Oracle Database 23c Administrator's Guide, "Managing Common Privileges in a CDB", Section 18.7.3. The documentation states, "To grant a commonly granted system privilege... you must be connected to the root, and you must include the CONTAINER=ALL clause in the GRANT statement."

Oracle Database 23c SQL Language Reference, "GRANT", "Commonly Granted Privileges". This section details the usage of the CONTAINER clause, specifying that CONTAINER=ALL applies the grant to all containers in the CDB.

The DBMSMONITOR.SERVMODACTTRACEENABLE procedure is designed specifically for this requirement. It enables SQL tracing for database sessions based on a combination of service name, module, and action. This configuration is persistent, meaning it is stored in the data dictionary and remains active across session disconnects and database instance restarts. New sessions matching the specified criteria will automatically have tracing enabled. This method provides a granular and persistent way to trace specific application components without manual intervention.

A. DBMSSESSION.SESSIONTRACEENABLE enables tracing only for the current session and is not persistent; it ends when the session disconnects.

C. DBMSAPPLICATIONINFO.SETACTION is used by an application to set the action name for its current session; it does not enable tracing.

D. DBMSSYSTEM.SETEV is an older, undocumented procedure for setting events in another session and is not the standard or supported method for persistent tracing.

1. Oracle Database PL/SQL Packages and Types Reference, 23ai. "DBMSMONITOR Subprograms". The documentation for SERVMODACTTRACEENABLE Procedure states it "Enables SQL tracing for a given service name, module, and action."

2. Oracle Database Performance Tuning Guide, 23ai. Chapter 12, "SQL Trace and TKPROF". Section "Enabling and Disabling SQL Trace for a Specific Service, Module, and Action". This section details the usage and persistence of this procedure.

Database Replay workload capture is configured using the DBMSWORKLOADCAPTURE package. To exclude specific activity, filters must be defined before the capture begins. The ADDFILTER procedure within this package is used for this purpose. By calling DBMSWORKLOADCAPTURE.ADDFILTER with the attribute parameter set to 'USER' and the value parameter set to 'BACKUPADMIN', all database calls made by this user will be excluded from the workload capture files. This ensures that irrelevant maintenance activity is not part of the workload to be replayed later.

A. DBMSWORKLOADREPLAY is the package used for replaying a captured workload, not for configuring the capture process itself.

B. DBMSSQLPA is the package for SQL Performance Analyzer, a different tool used for analyzing the impact of changes on SQL workloads, not for workload capture.

C. The STARTCAPTURE procedure initiates the capture process but does not have an exclusion parameter; filtering is configured separately using ADDFILTER.

1. Oracle Database Testing Guide, 23c, Chapter 3, "Capturing the Production Workload". Section: "Filtering the Workload During Capture". It states, "You can filter a workload that is being captured by using the ADDFILTER procedure in the DBMSWORKLOADCAPTURE package."

2. Oracle Database PL/SQL Packages and Types Reference, 23c, Chapter 291, "DBMSWORKLOADCAPTURE". The documentation for the ADDFILTER procedure shows the parameters name (the filter name), attribute (e.g., 'USER'), and value (e.g., 'BACKUPADMIN').

Active Session History (ASH) is a core component of Oracle's performance diagnostics framework. It samples the state of all active database sessions every second. An active session is one that is not idle; it is either working on the CPU or waiting for a non-idle wait event. This sampled data, including wait events, SQL ID, and session state, is written to a circular buffer in the System Global Area (SGA). This in-memory data provides a detailed, recent history of database activity for real-time performance analysis.

A. ASH samples only active sessions, not all sessions. The sampling interval is one second, not ten, and data is written to the SGA, not the control file.

C. The ASH buffer in the SGA is circular and data is periodically flushed to the on-disk Automatic Workload Repository (AWR) by the MMON process for long-term storage.

D. ASH functions correctly with STATISTICSLEVEL set to TYPICAL, which is the default value. Setting it to ALL is not a requirement for ASH to operate.

1. Oracle Database Concepts, 23ai. Chapter 5, "Memory Architecture". Section "Active Session History (ASH) Buffer". This section states, "This buffer is in the SGA and stores recent session activity. The database samples active sessions every second..."

2. Oracle Database Performance Tuning Guide, 23ai. Chapter 6, "Automatic Performance Diagnostics". Section "Active Session History (ASH)". This section details that ASH samples active sessions each second and stores the data in a circular buffer in the SGA.

During a hard parse, after the database confirms the SQL statement's syntax is correct (syntax check) and that all referenced objects exist and the user has permissions (semantic check), the next critical step is optimization. The Optimizer evaluates various possible execution plans by analyzing access paths (e.g., full table scan vs. index scan) and join methods, using statistics to estimate the cost of each plan. It then selects the plan with the lowest estimated cost.

B. The Row Source Generator acts on the final execution plan chosen by the optimizer; it does not precede optimization.

C. The database does not simply use the "first available index." The Optimizer makes a cost-based decision on which index, if any, is optimal.

D. Hashing occurs earlier to check the library cache for an existing identical statement. The Result Cache stores results after execution, not during parsing.

- Oracle Database Concepts, 23ai. "SQL Processing". Section "SQL Parsing". The guide outlines the steps: "During the optimization phase, the database must perform a hard parse for a SQL statement at least once... The optimizer determines the most efficient execution plan for a SQL statement."

- Oracle Database SQL Tuning Guide, 23ai. "Optimizer Concepts". Section "Stages of SQL Processing". This section details that after the parser performs syntax and semantic checks, the optimizer generates and selects an execution plan.

Renaming a Pluggable Database (PDB) is a significant metadata operation that cannot be performed while the PDB is open and potentially in use. To ensure data consistency and prevent conflicts with active transactions or sessions, the PDB must first be closed. The ALTER PLUGGABLE DATABASE ... RENAME command will only succeed if the PDB is in a MOUNTED state. Attempting to run the command on a PDB in READ WRITE or READ ONLY mode will result in an error.

A. The command will fail, not succeed. Active sessions prevent the rename operation from executing.

B. The rename operation is transactional; it either succeeds immediately or fails. It is not deferred until a restart.

C. The command does not hang or wait for sessions to close; it fails immediately with an error message indicating the PDB is in an invalid state.

1. Oracle Database Administrator's Guide, 23ai, "Modifying a PDB". In the section "Renaming a PDB," the prerequisite is stated: "Close the PDB that you want to rename." The example shows closing the PDB before issuing the ALTER PLUGGABLE DATABASE ... RENAME command.

2. Oracle Database SQL Language Reference, 23ai, "ALTER PLUGGABLE DATABASE". The usage notes for the renamepdbclause specify the PDB must not be open in the current container. An open PDB is in READ WRITE or READ ONLY mode.

To grant a system privilege to a common user (C##...) that applies to the CDB root and all present and future Pluggable Databases (PDBs), the GRANT statement must be issued from the CDB root and include the CONTAINER=ALL clause. This clause explicitly extends the scope of the grant to every container within the multitenant environment. Omitting this clause or using CONTAINER=CURRENT would restrict the privilege to the container in which the command is executed (the CDB root in this scenario).

A. The WITH ADMIN OPTION clause allows the grantee to grant the privilege to others but does not control the container scope of the grant itself.

C. CONTAINER=CURRENT is the default behavior and would only grant the privilege in the CDB root, not in any PDBs.

D. ALTER USER ... GRANT ... is not a valid SQL syntax for granting system privileges to a user.

1. Oracle Database SQL Language Reference 23ai, "GRANT". The documentation for the grantobjectprivileges and grantsystemprivileges sections specifies the use of the CONTAINER clause. It states, "Specify CONTAINER = ALL to grant the privilege to the user in all containers in the CDB."

2. Oracle Database Administrator's Guide 23ai, Chapter 40, "Managing a Multitenant Environment", Section "Granting Privileges to a Common User". This section provides examples and explains, "To grant a system privilege... to a common user in all PDBs, you must connect to the root and specify CONTAINER=ALL in the GRANT statement."