Get New 2026 Valid Practice To your 1Z1-182 Exam (Updated 96 Questions)
Oracle Database 23ai 1Z1-182 Exam Practice Test Questions Dumps Bundle!
NEW QUESTION # 46
Which advisor is used to detect missing or stale object statistics?
- A. SQL Performance Analyzer
- B. SQL Tuning Advisor
- C. SQL Access Advisor
- D. SQL Statistics Advisor
Answer: D
Explanation:
B .True. The SQL Statistics Advisor (new in 23ai) identifies stale or missing statistics. Others focus on tuning, performance analysis, or access paths.
NEW QUESTION # 47
You must create a tablespace of nonstandard block size in a new file system and plan to use this command: CREATE TABLESPACE ns_tbs DATAFILE '/u02/oracle/data/nstbs_f01.dbf' SIZE 100G BLOCKSIZE 32K; The standard block size is 8K, but other nonstandard block sizes will also be used. Which two are requirements for this command to succeed?
- A. The operating system must use a 32K block size.
- B. DB_32K_CACHE_SIZE should be set to a value greater than DB_CACHE_SIZE.
- C. DB_32K_CACHE_SIZE must be set to a value that can be accommodated in the SGA.
- D. The /u02 file system must have at least 100G space for the datafile.
- E. DB_32K_CACHE_SIZE must be less than DB_CACHE_SIZE.
Answer: C,D
Explanation:
A .False. No such restriction exists; DB_32K_CACHE_SIZE is independent of DB_CACHE_SIZE.
B .True. A nonstandard block size (32K) requires a corresponding cache (DB_32K_CACHE_SIZE) set to a non-zero value within SGA limits.
C .False. OS block size is irrelevant; Oracle manages its own block sizes.
D .False. No requirement for it to exceed DB_CACHE_SIZE.
E .True. The file system must have 100G available for the datafile.
NEW QUESTION # 48
You have connected to the CDB root as a common user with the CREATE PLUGGABLE DATABASE system privilege and issued the following command: SQL> CREATE PLUGGABLE DATABASE pdb1 ADMIN USER admin1 IDENTIFIED BY p1 ROLES = (CONNECT) FILE_NAME_CONVERT = ('PDB$SEEDdir', 'PDB1dir'); Which three are results of the CREATE command?
- A. After the PDB is created, it is automatically opened Read/Write.
- B. The PDB must be opened Read Only to complete the integration of the PDB into the CDB.
- C. It creates tablespaces to store metadata.
- D. It creates new default schemas for the PDB.
- E. It creates a new local user ADMIN with restricted privileges.
- F. It creates a new local user ADMIN with SYSDBA privileges.
Answer: C,D,E
Explanation:
A .True. PDBs inherit default schemas from the seed.
B .True. Metadata tablespaces (e.g., SYSTEM, SYSAUX) are created.
C .False. ADMIN1 (not ADMIN) isn't granted SYSDBA.
D .False. No read-only requirement post-creation.
E .False. PDBs start in MOUNTED state, not open.
F .True. ADMIN1 is a local user with CONNECT role only.
NEW QUESTION # 49
Examine this command: ALTER DATABASE MOVE DATAFILE '/u01/sales01.dbf' TO '/u02/sales02.dbf'; Which two statements are true?
- A. Tables with one or more extents in this data file may be queried during the execution of this command.
- B. DML may be performed on tables with one or more extents in this data file during the execution of this command.
- C. The "TO" clause containing the new file name must be specified even if Oracle Managed Files (OMF) is used.
- D. Compressed objects in sales01.dbf will be uncompressed in sales02.dbf after the move.
- E. It overwrites any existing file with the name sales02.dbf in /u02 by default.
Answer: A,B
Explanation:
The ALTER DATABASE MOVE DATAFILE command relocates a data file to a new location while the database remains online, introduced in Oracle 12c and enhanced in subsequent releases like 23ai. Let's evaluate each option:
A . DML may be performed on tables with one or more extents in this data file during the execution of this command.True. The move operation is online by default in Oracle 23ai, allowing DML (INSERT, UPDATE, DELETE) operations on tables within the data file being moved. The database ensures consistency using redo and undo mechanisms.
B . It overwrites any existing file with the name sales02.dbf in /u02 by default.False. By default, the command does not overwrite an existing file unless the REUSE clause is specified (e.g., ALTER DATABASE MOVE DATAFILE ... REUSE). Without it, the command fails if the target file exists.
C . The "TO" clause containing the new file name must be specified even if Oracle Managed Files (OMF) is used.False. When OMF is enabled (via DB_CREATE_FILE_DEST), the TO clause is optional. If omitted, Oracle automatically generates a file name and places it in the OMF destination.
D . Compressed objects in sales01.dbf will be uncompressed in sales02.dbf after the move.False. The move operation is a physical relocation of the data file; it does not alter the logical structure or compression state of objects within it. Compressed data remains compressed.
E . Tables with one or more extents in this data file may be queried during the execution of this command.True. The online nature of the move allows queries (SELECT statements) to proceed without interruption, leveraging Oracle's multi-version consistency model.
NEW QUESTION # 50
One of your database instances was shut down normally and then started in NOMOUNT state. You then executed this command: ALTER DATABASE MOUNT; Which two of these actions are performed?
- A. The alert log has instance startup details written to it.
- B. Online data files are opened.
- C. Online redo logs are opened.
- D. Control files are read.
- E. Oracle shared memory structures are allocated.
- F. The initialization parameter file is read.
Answer: A,D
Explanation:
A .False. Data files open in OPEN.
B .False. Redo logs open in OPEN.
C .False. PFILE/SPFILE is read at NOMOUNT.
D .False. SGA is allocated at NOMOUNT.
E .True. Alert log records mount event.
F .True. Control files are read in MOUNT.
NEW QUESTION # 51
Which three statements are true about resumable space allocation in Oracle databases?
- A. Resumable space allocation may be enabled for some sessions and not others.
- B. A user's session may be suspended and resumed multiple times.
- C. All sessions must have the same timeout value when waiting for resumable space allocations.
- D. A user's session may be suspended even if the user has the UNLIMITED TABLESPACE system privilege.
- E. The AFTER SUSPEND event trigger can itself be suspended due to space conditions.
- F. Resumable space allocation is only possible with locally managed tablespaces.
Answer: A,B,D
Explanation:
A .True. Enabled per session with ALTER SESSION ENABLE RESUMABLE.
B .True. Multiple suspensions can occur in one session.
C .False. Works with dictionary-managed tablespaces too.
D .False. Timeout is session-specific.
E .True. Privilege doesn't prevent suspension; quota limits do.
F .False. Triggers execute but can't suspend themselves.
NEW QUESTION # 52
Which two actions can you perform using DBCA for an existing database?
- A. Create nonstandard block size tablespaces.
- B. Change the character set.
- C. Create a template that can be used to clone the database.
- D. Change the server mode from dedicated to shared, and vice versa.
- E. Create an additional listener.
Answer: C,D
Explanation:
A .False. DBCA can't change character sets post-creation.
B .False. Listeners are managed via NetCA or lsnrctl.
C .True. DBCA can switch server modes for existing DBs.
D .False. Tablespaces are created via SQL, not DBCA for existing DBs.
E .True. DBCA can generate clone templates from existing DBs.
NEW QUESTION # 53
Which two statements are true about the PMON background process?
- A. It rolls back transactions when a process fails.
- B. It registers database services with all local and remote listeners known to the database instance.
- C. It frees resources held by abnormally terminated processes.
- D. It frees unused temporary segments.
- E. It records checkpoint information in the control file.
Answer: A,C
Explanation:
A .False. LREG handles registration in 23ai, not PMON.
B .False. CKPT (Checkpoint) process updates the control file.
C .True. PMON cleans up after failed processes (e.g., releases locks).
D .True. PMON rolls back uncommitted transactions of failed processes.
E .False. SMON (System Monitor) manages temporary segment cleanup.
NEW QUESTION # 54
Which two statements are true about the configuration and use of UNDO?
- A. UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.
- B. UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unconsumed UNDO.
- C. Active UNDO is always retained.
- D. UNDO_RETENTION specifies how long all types of UNDO are retained.
- E. Unexpired UNDO is always retained.
Answer: A,C
Explanation:
A .True. Active (in-use) UNDO is never overwritten.
B .False. Unexpired UNDO can be reused if space is needed.
C .False. Applies to unexpired, not expired UNDO.
D .False. Only unexpired UNDO is targeted.
E .True. UNDO_RETENTION sets the retention goal for unexpired UNDO.
NEW QUESTION # 55
Which three statements are true about Enterprise Manager Cloud Control?
- A. It is integrated with My Oracle Support.
- B. It provides management for Oracle-engineered systems.
- C. It uses a web-based console built into the Oracle database using XML DB.
- D. It provides management for Oracle middleware.
- E. It is available to manage a database only when that database is open.
Answer: A,B,D
Explanation:
A .True. Integrates with MOS for patches and support.
B .True. Manages Exadata, etc.
C .True. Covers WebLogic and other middleware.
D .False. Web-based, but not built into the DB.
E .False. Can manage stopped instances via agents.
NEW QUESTION # 56
Which statement is true about the PDB_DBA role granted to a local user during the creation of a new PDB?
- A. No privileges are provided with the PDB_DBA role.
- B. The role has the privileges to perform DDL operations on all local schema objects.
- C. The role has the privileges to create and manage users within the PDB.
- D. The role has the privileges to administer the PDB.
Answer: D
Explanation:
A .True. PDB_DBA grants admin rights (e.g., CREATE SESSION, ALTER SYSTEM) within the PDB.
B .False. DDL on objects requires ownership or explicit grants.
C .False. User management needs CREATE USER privilege, not inherent.
D .False. It includes multiple privileges, not none.
NEW QUESTION # 57
Which two statements describe how Optimizer Statistics are collected?
- A. Optimizer Statistics are collected automatically by an automatic maintenance job that runsduring predefined maintenance windows.
- B. Optimizer Statistics are collected in real-time as data is inserted, deleted, or updated.
- C. Optimizer Statistics can be manually collected at multiple levels using DBMS_STATS.GATHER_*_STATS PL/SQL procedures.
- D. Optimizer Statistics are collected by the Statistics Advisor.
- E. Optimizer Statistics are collected automatically by Automatic Workload Repository (AWR) Snapshot.
Answer: A,C
Explanation:
Optimizer Statistics drive the cost-based optimizer's query plans. Let's dissect each option:
A . Optimizer Statistics are collected automatically by an automatic maintenance job that runs during predefined maintenance windows.
True. Oracle 23ai uses the AutoTask framework to gather stats automatically during maintenance windows (e.g., nightly 10 PM-2 AM). The GATHER_STATS_PROG job, managed by DBMS_AUTO_TASK_ADMIN, collects stats for stale or missing objects.
Mechanics:Controlled by STATISTICS_LEVEL=TYPICAL (default) and the DEFAULT_MAINTENANCE_PLAN. It prioritizes objects with >10% changes (stale stats) or no stats.
Practical Use:Ensures stats are current without manual intervention, critical for dynamic workloads.
Edge Case:Disabled if STATISTICS_LEVEL=BASIC or the job is manually disabled via DBMS_AUTO_TASK_ADMIN.DISABLE.
B . Optimizer Statistics are collected in real-time as data is inserted, deleted, or updated.
False. Stats aren't updated in real-time; this would be too resource-intensive. Instead, Oracle tracks changes (e.g., via DBA_TAB_MODIFICATIONS) and updates stats periodically via AutoTask or manually. Real-time stats exist in 23ai for specific cases (e.g., GATHER_TABLE_STATS with REAL_TIME_STATS), but it's not the default.
Why Incorrect:Real-time collection would degrade performance for OLTP systems, contradicting Oracle's batch approach.
C . Optimizer Statistics can be manually collected at multiple levels using DBMS_STATS.GATHER_*_STATS PL/SQL procedures.
True. The DBMS_STATS package offers granular control: GATHER_TABLE_STATS, GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, etc., allowing stats collection for tables, schemas, or the entire database.
Mechanics:Example: BEGIN DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES'); END;. Options like ESTIMATE_PERCENT and DEGREE fine-tune the process.
Practical Use:Used for immediate stats updates post-DML or for custom schedules outside maintenance windows.
Edge Case:Overuse can lock stats (e.g., FORCE=TRUE), requiring careful management.
D . Optimizer Statistics are collected by the Statistics Advisor.
False. The Statistics Advisor (new in 23ai) analyzes and recommends stats improvements but doesn't collect them. Collection is still via DBMS_STATS or AutoTask.
Why Incorrect:It's a diagnostic tool, not an executor.
E . Optimizer Statistics are collected automatically by Automatic Workload Repository (AWR) Snapshot.
False. AWR snapshots capture performance metrics (e.g., wait times), not optimizer stats. Stats collection is a separate process via AutoTask or manual commands.
Why Incorrect:AWR and stats collection serve distinct purposes-monitoring vs. optimization.
NEW QUESTION # 58
What are Optimizer Statistics?
- A. Optimizer Statistics are a set of data distribution statistics collected in real time as data is inserted, deleted, or updated, which are stored in AWR and used for generating SQL execution plans.
- B. Optimizer Statistics are table, column, index, and system statistics used for generating efficient SQL execution plans.
- C. Optimizer Statistics are statistics about data distribution within Oracle Datafiles.
- D. Optimizer Statistics are part of system performance statistics stored in AWR required for calculating SQL execution plans.
Answer: B
Explanation:
A .False. Not real-time; collected periodically.
B .False. Not about datafile distribution.
C .True. Includes table, column, index stats for plan generation.
D .False. Stored in data dictionary, not AWR.
NEW QUESTION # 59
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: LISTENER_1 = (ADDRESS = (PROTOCOL = TCP)(HOST = host1.abc.com)(PORT = 1521)). Which statement is true?
- A. The LREG process registers services dynamically with the LISTENER_1 listener.
- B. Dynamic service registration cannot be used for this database instance.
- C. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database in LISTENERS.
- D. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration.
- E. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration.
Answer: A
Explanation:
Dynamic service registration allows a database to automatically register its services with a listener without manual configuration in LISTENER.ORA. Let's analyze each option:
A . The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration.
False. The CONNECT_DATA section is part of a client-side TNSNAMES.ORA entry for connecting to a service, not for listener registration. Dynamic registration is handled by the database's LREG (Listener Registration) process, which uses the LOCAL_LISTENER parameter to locate the listener's address (e.g., host1.abc.com:1521). No CONNECT_DATA is needed in the listener address definition itself. This option confuses client connection syntax with listener configuration.
Mechanics:The listener address in TNSNAMES.ORA (LISTENER_1) is sufficient for LREG to find and register with it, as long as the listener is running at that address.
B . LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration.
False. Dynamic registration doesn't require the listener to be explicitly defined in LISTENER.ORA. The LOCAL_LISTENER parameter pointing to LISTENER_1 (resolved via TNSNAMES.ORA) tells LREG where to register services. If the listener is running on host1.abc.com:1521, LREG will find it without a LISTENER.ORA entry. However, LISTENER.ORA is needed tostartthe listener process, but that's separate from dynamic registration.
Practical Note:If LISTENER.ORA isn't configured, a default listener might run on port 1521, but the question implies LISTENER_1 is operational.
C . The LREG process registers services dynamically with the LISTENER_1 listener.
True. In Oracle 23ai, the LREG background process (replacing PMON's registration role in earlier versions) dynamically registers database services with listeners specified by LOCAL_LISTENER. Here, LOCAL_LISTENER=LISTENER_1 resolves to host1.abc.com:1521 via TNSNAMES.ORA. LREG periodically sends service information (e.g., service names, instance details) to the listener, enabling clients to connect without static configuration.
Mechanics:LREG uses the TNS alias (LISTENER_1) to locate the listener's IP and port, registers services like orcl or orclpdb, and updates the listener's service table. This happens automatically every 60 seconds or on significant events (e.g., instance startup).
D . Dynamic service registration cannot be used for this database instance.
False. The setup (LOCAL_LISTENER set and a valid TNSNAMES.ORA entry) explicitly supports dynamic registration. No blockers (e.g., REGISTRATION_EXCLUDED_LISTENERS) are mentioned, so LREG can function normally.
E . There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database in LISTENERS.
False. The question mentions only LISTENER_1 in the SPFILE and TNSNAMES.ORA. There's no evidence of a second listener (LISTENER) or a LISTENERS configuration (possibly a typo). Two listeners can't share the same port (1521) on the same host due to port conflicts unless explicitly configured with different IPs, which isn't indicated here.
NEW QUESTION # 60
You start your database instance in NOMOUNT state. Which two actions are performed?
- A. All required background processes are started.
- B. The consistency of the database is checked.
- C. Memory is allocated for the SGA.
- D. The control files are opened.
- E. SYS can access the database.
Answer: A,C
Explanation:
A .True. Background processes (e.g., PMON) start.
B .False. Consistency checks require MOUNT.
C .False. Control files open in MOUNT.
D .True. SGA is allocated at startup.
E .False. SYS access requires OPEN.
NEW QUESTION # 61
Which two statements are true about User Authentication in an Oracle Database?
- A. Password File authentication is supported for any type of database user.
- B. Operating System authentication may be used for system-privileged administrative users.
- C. Password File authentication must be used for system-privileged administrative users.
- D. Password authentication must be used for system-privileged administrative users.
- E. REMOTE_LOGIN_PASSWORDFILE must be set to EXCLUSIVE to permit password changes for system-privileged administrative users.
Answer: B,C
Explanation:
User authentication in Oracle 23ai determines how users (especially administrative ones) connect to the database. Let's analyze each option with extensive detail:
A . Operating System authentication may be used for system-privileged administrative users.
True. OS authentication allows users mapped to OS accounts (e.g., ops$oracle) to connect without a password, often used for administrative users like SYS or SYSTEM. This is configured by creating an externally authenticated user (e.g., CREATE USER "OPS$ORACLE" IDENTIFIED EXTERNALLY) and relies on the OS to verify identity.
Mechanics:When a user logs in via sqlplus / as sysdba, Oracle checks the OS user against the dba group (Unix) or ORA_DBA (Windows). If matched, no password is needed, leveraging OS security.
Practical Use:Common for DBAs managing local instances, reducing password management overhead.
Edge Case:Requires REMOTE_LOGIN_PASSWORDFILE=NONE for exclusive OS auth, but this isn't mandatory if a password file exists alongside.
Historical Note:Introduced in early Oracle versions, this remains a robust option in 23ai for local admin access.
B . Password authentication must be used for system-privileged administrative users.
False. "Must" is incorrect; password authentication (e.g., sqlplus sys/password) is an option, not a requirement. OS authentication or password file authentication can also be used for users like SYS. This option overstates the necessity of password-based login.
Why Incorrect:Oracle's flexibility allows multiple methods, contradicting the absolute phrasing here.
C . Password File authentication is supported for any type of database user.
False. Password file authentication is restricted to users with SYSDBA, SYSOPER, or similar system privileges (e.g., SYSBACKUP). Regular users (e.g., HR) can't use the password file (orapw<sid>); they rely on database authentication (passwords stored in the DB) or external methods.
Mechanics:The password file stores hashed credentials for privileged users, checked during remote AS SYSDBA logins.
Why Incorrect:Extending this to "any user" ignores Oracle's security model limiting password file usage.
D . REMOTE_LOGIN_PASSWORDFILE must be set to EXCLUSIVE to permit password changes for system-privileged administrative users.
False. REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE allows a dedicated password file for one instance, enabling password changes via ALTER USER SYS IDENTIFIED BY newpass. However, SHARED mode also permits changes for SYS, though not for other users added to the file. The "must" overstates the requirement; it's sufficient, not necessary.
Mechanics:EXCLUSIVE locks the file to one DB, while SHARED allows multiple DBs to use it, with restrictions on non-SYS users.
E . Password File authentication must be used for system-privileged administrative users.
True. For remote administrative access (e.g., sqlplus sys/password@orcl as sysdba), a password file is mandatory when REMOTE_LOGIN_PASSWORDFILE is EXCLUSIVE or SHARED. Local OS authentication is an alternative, but for network-based admin tasks, the password file is required, making this statement true in that context.
Mechanics:Set via orapwd (e.g., orapwd file=orapworcl password=oracle entries=10), enabling remote SYSDBA logins.
Edge Case:If REMOTE_LOGIN_PASSWORDFILE=NONE, only OS auth works locally, but this isn't the default or typical setup.
NEW QUESTION # 62
Which two are true about shrinking a segment online?
- A. To shrink a table it must have a PRIMARY KEY constraint.
- B. It must be in a tablespace that uses Automatic Segment Space Management (ASSM).
- C. To shrink a table it must have row movement enabled.
- D. To shrink a table it must have a UNIQUE KEY constraint.
- E. It is not possible to shrink either indexes or Index Organized Tables (IOTs).
Answer: B,C
Explanation:
A .True. ROW MOVEMENT must be enabled for SHRINK SPACE.
B .False. Indexes and IOTs can be shrunk with CASCADE.
C .False. No such constraint requirement.
D .True. ASSM is required for online shrinking.
E .False. No primary key requirement.
NEW QUESTION # 63
Which three tasks are performed by background processes in an Oracle database instance?
- A. Reading database blocks into the buffer cache.
- B. Reading database blocks into the buffer cache.
- C. Registering services with Oracle Net listeners.
- D. Writing dirty database block images from the buffer cache.
- E. Writing redo to log files.
- F. Creating dedicated server connections.
Answer: C,D,E
Explanation:
False. Server processes (foreground) read blocks into the buffer cache during user queries, not background processes.
Explanation:
Background processes manage core database operations. Let's break it down:
A : Creating dedicated server connections.
False. Dedicated server connections are created by the listener, not background processes. The listener hands off the connection to a server process (e.g., ora_s000), which isn't a background process.
B : Registering services with Oracle Net listeners.
True. The LREG process (Listener Registration, formerly PMON's role) registers services with listeners, using parameters like LOCAL_LISTENER.
Mechanics:LREG sends service details (e.g., orcl) to the listener every 60 seconds or on startup.
C : Writing redo to log files.
True. The LGWR (Log Writer) process writes redo entries from the redo log buffer to online redo log files, ensuring transaction durability.
Mechanics:Triggered by commits, log buffer fills, or checkpoints.
D : Writing dirty database block images from the buffer cache.
True. The DBWn (Database Writer) processes write modified (dirty) blocks from the buffer cache to data files, maintaining consistency.
Mechanics:Occurs at checkpoints or when free buffers are needed.
NEW QUESTION # 64
......
Fully Updated Dumps PDF - Latest 1Z1-182 Exam Questions and Answers: https://prep4sure.dumpsfree.com/1Z1-182-valid-exam.html