nerdexam
Oracle

1Z0-052 · Question #131

You execute a command to resize a data file, sales.dbf, of size 200 MB in your database: ALTER DATABASE DATAFILE '/remorse/sales.dbf' RESIZE 150M; Which statement is true about this command?

The correct answer is D. The command succeeds if no extent in the tablespace is currently allocated above the 150 MB. A datafile can be resized online in an open database, but shrinking it succeeds only when no allocated extents exist beyond the new target size.

Managing Database Storage Structures

Question

You execute a command to resize a data file, sales.dbf, of size 200 MB in your database:

ALTER DATABASE DATAFILE '/remorse/sales.dbf' RESIZE 150M; Which statement is true about this command?

Options

  • AThe command must be issued in MOUNT state.
  • BThe command succeeds only if the data file is offline.
  • CThe command must be issued when the database is in the archivelog mode.
  • DThe command succeeds if no extent in the tablespace is currently allocated above the 150 MB

How the community answered

(26 responses)
  • A
    8% (2)
  • B
    4% (1)
  • D
    88% (23)

Why each option

A datafile can be resized online in an open database, but shrinking it succeeds only when no allocated extents exist beyond the new target size.

AThe command must be issued in MOUNT state.

ALTER DATABASE DATAFILE RESIZE is an online operation valid in OPEN state; MOUNT state is required only for operations such as renaming or relocating datafiles when the database cannot be open.

BThe command succeeds only if the data file is offline.

The datafile does not need to be offline; Oracle can resize an in-use online datafile as long as the extent boundary constraint is satisfied.

CThe command must be issued when the database is in the archivelog mode.

Archivelog mode governs whether archived redo logs are retained and has no effect on whether a datafile resize operation is permitted.

DThe command succeeds if no extent in the tablespace is currently allocated above the 150 MBCorrect

Oracle enforces that a datafile cannot be shrunk below the highest allocated extent boundary; if all used extents reside within the first 150 MB, the RESIZE command succeeds, but if any extent is allocated in the 150-200 MB range Oracle raises ORA-03297 and rejects the command. The operation runs against an online, open database and requires neither MOUNT state, an offline datafile, nor archivelog mode.

Concept tested: Online datafile resize and allocated extent boundary enforcement

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-data-files-and-temp-files.html

Topics

#datafile resize#ALTER DATABASE DATAFILE#extents#tablespace

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-052 Practice