Free Oracle Database 23ai Administration Associate Exam 1Z0-182 Exam Practice Test

Which two AWR-based tools listed below are part of Oracle Database self-tuning components?

• Automatic capture of statistical information from the SGA and storing it in the AWR using Automatic Database Diagnostic.
• ADDM, a server-based expert that reviews database performance statistics captured by Snapshots to identify potential problems before system performance degrades noticeably.
• Automatic Diagnostic Collector used to capture and store database errors and hung analysis.
• Automatic population of performance views (V$ views) from statistical data stored in AWR repository and using Automatic Database Diagnostic.
• Automatic Application Tracing used to collect High-Load SQL statements and statistics.

Which two SQL Plan Management Advisor tasks are part of Automatic Maintenance Tasks?

• The Automatic SQL Access Advisor task, which is used to manage an application SQL load.
• The Automatic SQL Performance Analyzer task, which is used to provide details about impact of database changes to application SQL batch.
• The Automatic Index Advisor task, which is used to create and maintain table indexes based on the DML load and operations.
• The Automatic SQL Plan Management Evolve Advisor task, which evolves plans that have recentlybeen added as the SQL plan baseline for statements.
• The Automatic SQL Tuning Advisor tasks, which would examine the performance of high-load SQL statements and make recommendations for those statements.

Which two are benefits of external tables?

• They support DELETEs, which transparently deletes records in the file system as if they were table rows.
• They can be queried while the database is in the MOUNT state like dynamic performance views.
• They support UPDATEs, which transparently updates records in the file system as if they were table rows.
• They can be queried, transformed, and joined with other tables without having to load the data first.
• The results of a complex join or aggregating function or both can be unloaded to a file for transportation to other systems.

In one of your databases, the user HR has the password HRMGR. You want to connect to a database instance whose listener listens on port 1531 by using this statement: CONNECT HR/HRMGR@orcl. No name server is used. Which statement is true about ORCL?It must be the name of the database to whose instance HR wishes to connect.It must resolve to a valid connect descriptor in the client's tnsnames.ora file.It must be the name of the server running the database to whose instance HR wishes to connect.It must resolve to a valid connect descriptor in the server's tnsnames.ora file.It must be the value of the SERVICE_NAMES parameter on the client side.

• "

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?The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration.LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration.The LREG process registers services dynamically with the LISTENER_1 listener.Dynamic service registration cannot be used for this database instance.There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database in LISTENERS.

• 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.

Which two statements describe why Database Auditing is a security requirement?

• To monitor suspicious activity.
• To alert DBAs about system issues.
• To protect against data theft by a non-authorized user.
• To protect against data corruption.
• To monitor proper usage of the system and data by privileged users.

Examine this command: ALTER DATABASE MOVE DATAFILE '\u01/sales1.dbf' TO '\u01/sales01.dbf' REUSE; Which two statements are true?

• DML may be performed on tables with one or more extents in this data file during the execution of this command.
• If Oracle Managed Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST.
• The tablespace containing SALES1.DBF must be altered READ ONLY before executing the command.
• The file is renamed and stored in the same location.
• The tablespace containing SALES1.DBF must be altered OFFLINE before executing the command.

Which three statements are true about UNDO and REDO?

• REDO is used for read consistency.
• UNDO is used for some flashback operations.
• UNDO is used for read consistency.
• Both REDO and UNDO can be multiplexed.
• REDO is used for ROLLBACK.
• REDO is used for instance recovery.

Which two methods can be used to purge audit records of the Unified Audits?

• Only viewed audit records can be purged from Unified Audits.
• Use DBMS_AUDIT_MGMT.CREATE_PURGE_JOB as a privileged user to schedule an automaticpurge job.
• Only the owner of a Unified Audit Policy can purge audit records by resetting the policy.
• Use DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL as a privileged user to manually purge audit records.
• Use DBMS_AUDIT_MGMT.DELETE_AUDIT_RECORDS('POLICY_NAME') as a privileged user to manually purge audit records of a specified Unified Policy.

Which three statements are true about row chaining and row migration done by Oracle database block space management?

• A migrated row results in an update to any index on the table to point the row ID in the index to the row’s new location.
• Update statements can result in one or more migrated rows.
• Update statements can result in one or more chained rows.
• Row pieces of a chained row must always reside in different blocks.
• Insert statements can result in a migrated row.