A .False. V$TABLESPACE doesn’t show free space directly.

B .False. It’s from the control file, not the data dictionary.

C .True. Renaming in MOUNT state updates DBA_DATA_FILES post-open.

D .False. DBA_TABLESPACES shows attributes, not free space directly (use DBA_FREE_SPACE).

E .True. V$TABLESPACE reflects control file data.

Reference:Oracle Database Reference 23ai, "V$TABLESPACE, DBA_DATA_FILES."

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.

Reference:Oracle Database Administrator’s Guide 23ai, Chapter "Managing Data Files and Temp

Files," Section "Moving Data Files Online."

Roles in Oracle manage privileges efficiently. Let’s dive into each option:

A . Roles must be password protected.

False. Roles can be password-protected (e.g., CREATE ROLE mgr IDENTIFIED BY secret), but it’s

optional. Non-protected roles (default) are enabled automatically if granted, requiring no password.

Mechanics:Password-protected roles need SET ROLE mgr IDENTIFIED BY secret, enhancing security

for sensitive privileges.

B . Roles may be granted to other roles.

True. Roles can form hierarchies (e.g., GRANT clerk TO mgr), allowing nested privilege management.

Mechanics:A user with mgr inherits clerk privileges indirectly. Revoking clerk from mgr cascades

appropriately.

Practical Use:Simplifies complex privilege structures in large organizations.

C . The SET ROLE statement can enable one or more roles for a session.

True. SET ROLE role1, role2; activates specified roles for the session, assuming they’re granted and

not password-protected (or password is provided).

Mechanics:Enabled roles grant their privileges immediately within the session scope.

D . Object privileges may not be granted to roles.

False. Object privileges (e.g., GRANT SELECT ON emp TO clerk) are a primary use of roles, making this

statement incorrect.

Why Incorrect:Roles are designed for this purpose, contradicting the option.

E . All roles granted to a user are set on default when the user logs in.

False. Only roles marked as DEFAULT ROLE (via ALTER USER ... DEFAULT ROLE role1) are enabled at

login. Non-default roles require SET ROLE.

Mechanics:Check via SELECT * FROM DBA_ROLE_PRIVS WHERE DEFAULT_ROLE='YES'.

F . The SET ROLE statement can disable one or more roles for a session.

True. SET ROLE NONE disables all roles, or SET ROLE role1 implicitly disables others not listed,

providing granular control.

Practical Use:Useful for testing or restricting privileges temporarily.

Reference:Oracle Database Security Guide 23ai, "Managing Roles"; Oracle Database SQL Language

Reference 23ai, "SET ROLE."

A .True. ASSM categorizes blocks (e.g., 0-25%, 25-50%) for efficient space use.

B .False. Updates may cause chaining/migration, not always relocation.

C .True. ASSM optimizes inserts into suitable blocks.

D .False. ASSM uses a bitmap, not necessarily the first block.

E .False. PCTFREE is segment-specific, not universally 10%.

Reference:Oracle Database Concepts 23ai, "Automatic Segment Space Management."

A .False. Recovery happens on startup, not shutdown.

B .False. TRANSACTIONAL waits for commits or rollbacks, then disconnects.

C .True. New connections are blocked during shutdown.

D .True. Inactive sessions persist until shutdown completes.

E .True. Active transactions can commit before disconnection.

Reference:Oracle Database Administrator’s Guide 23ai, "Shutting Down a Database."

A .False. Backups are configured via RMAN, not DBCA.

B .True. DBCA allows nonstandard block sizes during DB creation.

C .True. DBCA can register new DBs with EM.

D .False. Block size is fixed post-creation.

E .False. Flashback is enabled via SQL, not DBCA for existing DBs.

Reference:Oracle Database Administrator’s Guide 23ai, "DBCA Features."

Temp UNDO, introduced in Oracle 12c and refined in 23ai, stores undo for global temporary tables

(GTTs) in temporary tablespaces:

A . It permits DML on GTTs even if the database is opened read-only.False. In read-only mode, DML

on GTTs is allowed regardless of temp UNDO, as GTT data is session-private, but temp UNDO doesn’t

specifically enable this.

B . It reduces the amount of UNDO stored in the UNDO tablespace.True. Temp UNDO stores undo in

the temporary tablespace, reducing usage of the permanent UNDO tablespace.

C . It reduces I/Os to the SYSTEM tablespace.True. By avoiding permanent undo, it reduces metadata

updates in the SYSTEM tablespace related to undo management.

D . It reduces the amount of redo generated.True. Temp UNDO changes are not redo-logged to the

same extent as permanent undo, minimizing redo generation.

E . It reduces I/Os to the SYSAUX tablespace.False. SYSAUX is unrelated to undo management; temp

UNDO affects temporary and SYSTEM tablespaces.

Reference:Oracle Database Concepts 23ai, "Temporary Undo."

B .True. Without SCOPE, ALTER SYSTEM defaults to BOTH (memory and SPFILE); PFILE isn’t updated

unless manually recreated.

Reference:Oracle Database Administrator’s Guide 23ai, "Managing Initialization Parameters."

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); 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.

Reference:Oracle Database Security Guide 23ai, "Authentication Methods"; Oracle Database

Administrator’s Guide 23ai, "Managing the Password File."

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.

Reference:Oracle Database Net Services Administrator’s Guide 23ai, "Understanding Dynamic Service

Registration"; Oracle Database Reference 23ai, "LOCAL_LISTENER Parameter."