Tablespaces

The ALTER DATABASE MOVE DATAFILE command relocates a data fil e 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 d ata 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 c lause 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 spec ified 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 do es not alter the logical structure or compression state of objects within it. Compressed data remain s 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 (S ELECT statements) to proceed without interruption, leveraging Oracle’s multi -version consistency model.

A . Free space is managed using freelists.False. The SEGMEN T SPACE MANAGEMENT AUTO clause specifies Automatic Segment Space Management (ASSM), wh ich uses bitmaps to track free space, not freelists (used in Manual Segment Space Management). B . It uses the database default block size.True. T he BLOCKSIZE clause is not specified in the command, so the tablespace inherits the database’s default block size (typically 8K unless altered via DB_BLOCK_SIZE). C . It must be smaller than the smallest BIGFILE tablespace.Fal se. There’s no such restriction; SMALLFILE and BIGFILE tablespaces differ in structure (multiple vs. single data file), not mandat ed size relationships. D . It is a locally managed tablespace.True. In Oracle 23ai, all tablespaces created without an explicit EXTENT MANAGEMENT DICTIONARY clause are locally managed by def ault, using extent allocation bitmaps in the data file headers. E . Any data files added to the tablespace must have a size of 5 gigabytes.False. The initial data file is 5G, but additional data files can have different sizes when added us ing ALTER TABLESPACE ... ADD DATAFILE.

With OMF enabled, Oracle automatically manages file cr eation. The command ALTER TABLESPACE sales_q1 ADD DATAFILE without a file specification relies on i nitialization parameters: A . Specify a path in the DATAFILE clause ... with at least 100 MB of available space.False. With OMF, explicitly specifying a path overrides OMF behavior, but it’s n ot required for success if OMF parameters are set correctly. B . Add the AUTOEXTEND ON clause with NEXT set to 100M.Fals e. AUTOEXTEND is optional and affects file growth, not the initial creation succes s, which depends on available space in the OMF location. C . Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE _DEST each specify locations with at least 50 MB of available space.True. If both parameters are set ,Oracle may use either for data files (depending on context), and sufficient space (e.g., 50 MB minimum for a smallfile) ensures success. D . Ensure that DB_CREATE_FILE_DEST specifies a location with a t least 100 MB of available space.True. This is the primary OMF parameter for data files; sufficient space (typically 100 MB minimum for a new file) guarantees the command succ eeds. E . Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE _DEST each specify locations with at least 50 MB of available space.False. This is redundant wi th C; only one needs sufficient space, though C’s phrasing makes it a valid independent action.

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-on ly.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 tabl espace. 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.

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 correspo nding 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.

A .False. IOTs require immediate segment creation. B .False. SYS-owned tables don’t use deferred creation. C .True. Default since 11gR2, continued in 23ai. D .True. Indexes inherit this attribute unless overridden. E .True. Sessions can alter this via DEFERRED_SEGME NT_CREATION.

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.

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.

Logical structures (e.g., segments, extents) map to physical structures (e.g., data files, blocks). Let’s dissect each option: A . A segment can span multiple data files in some tablespaces. True. A segment (e.g., a table or index) is a logic al entity stored in a tablespace. In a smallfile tablespace (default in Oracle), a segment’s extents can span multiple data files if the tablespace has multiple files and space allocation requires it. This is c ommon in large tables or when autoextend adds new files. Mechanics:Oracle allocates extents across available d ata files in a round-robin fashion (with ASSM) or as needed, ensuring the segment’s data is distributed. This doesn’t apply to bigfile tablespaces, which use a single data file. Example:A 10GB table in a tablespace with two 5GB data files will span both. B . Segments can span multiple tablespaces. False. A segment is confined to a single tablespace. Oracle enf orces this to maintain logical separation (e.g., a table’s data stays in its assigned tablespace). Partitioned tables can have p artitions in different tablespaces, but each partition is a separate segment. Why Not:The segment header and extent map reside in one tablespace, preventing cross-tablespace spanning for a single segment. C . A segment might have only one extent. True. A segment starts with one extent upon creation (e.g. , a small table or index). If no further growth occurs, it remains a single-extent segment. This i s common with small objects or when INITIAL extent size suffices. Mechanics:In locally managed tablespaces (default), th e initial extent is allocated based on INITIAL or tablespace defaults (e.g., 64KB), and additional extents are added only as needed. D . All tablespaces may have one or more data files. False. Bigfile tablespaces are restricted to one data file (up to 128TB). Smallfile tablespaces (traditional) can have multiple data files (up to 1022), but the “all” phrasing makes this false due to bigfile exceptions. Clarification:The question’s intent may assume smallfile tablespaces, but Oracle 23ai supports both types. E. A segment’s blocks can be of different sizes. False. All blocks in a segment use the tablespace’s block size (e.g., 8KB). While a database can have tablespaces with different block sizes (e.g., 8KB, 32KB), a s ingle segment’s blocks are uniform, as it resides in one tablespace.

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 exi sting DBs.

D .True. The .pdb file (archive) contains the manifest a nd data files; all must be copied to the target CDB for plugging. A-C .False. The archive alone isn’t enough; all components are needed.

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.

A .False. Undo is stored in a dedicated undo tablespace, not SYSTEM. B .True. Large transactions can span multiple undo seg ments. C .False. Undo isn’t stored in SYSAUX. D .True. Undo segments extend as needed, space permitting. E .False. Undo doesn’t “wrap”; new extents are allocated.

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.

Dictionary-managed tablespaces (DMTs) use the data dictionary for extent management, whilelocally managed tablespaces (LMTs) use bitmaps. Let’s evaluate compatibility: A . Capacity planning growth reports based on historical data in the Automatic Workload Repository (AWR). True. AWR tracks space usage (e.g., DBA_HIST_TBSPC_SPACE_USAGE) regardless of tablespace type, enabling growth reports for both DMTs and LMTs. Mechanics:MMON collects metrics like segment growth, sto red in SYSAUX, accessible via EM or scripts. Practical Use:Helps predict when to add data files, universal across management types. B . Online table segment shrink. False. ALTER TABLE ... SHRINK SPACE requires LMTs with Automatic Segment Space Management (ASSM), unavailable in DMTs, which lack bitmap-based free space tracking. Why Incorrect:DMTs use freelists, incompatible with shrink operations. C . Online index segment shrink. False. Like tables, ALTER INDEX ... SHRINK SPACE requ ires LMTs with ASSM, not supported in DMTs. Why Incorrect:Same limitation as B; DMTs can’t compact online. D . Oracle Managed Files (OMF). True. OMF automates file naming and placement (via DB_C REATE_FILE_DEST) for both DMTs and LMTs, agnostic to extent management. Mechanics:Example: CREATE TABLESPACE ts1; creates an OMF file in either type. Edge Case:DMTs are rare in 23ai, but OMF still applies. E . Automatic data file extension (AUTOEXTEND). True. AUTOEXTEND ON allows data files to grow as needed, supported in both DMTs and LMTs since early versions. Mechanics:ALTER DATABASE DATAFILE ... AUTOEXTEND ON NEXT 100M; works universally.

A .False. LMT is default in 23ai. B .True. Bigfile tablespaces have one data file only. C .True. AUTOEXTEND works with bigfile data files. D .False. Block size defaults to DB_BLOCK_SIZE (e.g., 8K). E .False. No size comparison requirement.

A .True. Grants unlimited space across all tablespaces. B .True. Explicit quotas override the privilege. C .False. Not limited to default tablespace. D .False. Roles can’t have this privilege directly. E .False. Default is zero quota unless specified.

This command moves/renames a data file online. Let’s evaluate: A . DML may be performed on tables with one or more extents in this data file during the execution of this command. True. Introduced in 12c and refined in 23ai, MOVE DATAFILE is an online operation, allowing DML (e.g., INSERT, UPDATE) on tables within the file. Oracle ensures consistency via redo and undo. Mechanics:The file is copied to the new location/name while tracking changes, then s witched atomically. Practical Use:Minimizes downtime in production systems. B . If Oracle Managed Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST. False. With OMF, omitting the TO clause would place the file in DB_CREATE_FILE_DEST, but here, TO '\u01/sales01.dbf' explicitly specifies the target, overriding OM F defaults. Mechanics:OMF only applies if the destination is unspecified (e.g., MOVE DATAFILE ... without TO). Why Incorrect:Explicit path trumps OMF behavior. C . The tablespace containing SALES1.DBF must be altered READ ONLY before executing the command. False. READ ONLY isn’t required; the operation is online and supports active use. Why Incorrect:Conflicts with Oracle’s online move feature. D . The file is renamed and stored in the same location. True. The command renames/u01/sales1.dbf to/u01/sales01.dbf (correcting \u01 to/u01 as a Windows/Unix typo), keeping it in/u01/. REUSE allows overwrit ing if sales01.dbf exists. Mechanics:File is copied and renamed in -place within the same filesystem directory. E . The tablespace containing SALES1.DBF must be altered OFFLINE before executing the command. False. Ofline mode isn’t needed; the online move handles activetablespaces.

A .False. Not real-time; collected periodically. B .False. Not about datafile distribution. C .True. Includes table, column, index stats for plan g eneration. D .False. Stored in data dictionary, not AWR.

A .False. Works with DMTs too. B .True. LMTs use bitmaps, unlike DMT freelists. C .False. OMF is independent of management type. D .False. Alerts work with both. E .True. Shrink requires LMTs with ASSM.