Oracle RMAN REPORT SCHEMA Command: Display Database Files

calendar Feb 14, 2026 / · 5 min read · Oracle Database RMAN Database Administration Backup and Recovery Oracle Commands DBA Scripts Database Files Oracle RMAN Reporting  ·
Share on: linkedin copy

Oracle RMAN REPORT SCHEMA Command: Display Database Files

Purpose

The REPORT SCHEMA command is an essential Oracle RMAN (Recovery Manager) tool that displays comprehensive information about all database files in your Oracle database. This command provides database administrators with a quick view of the current database structure, including permanent datafiles, temporary files, tablespaces, and their associated details. Unlike SQL queries that target specific data dictionary views, REPORT SCHEMA retrieves information directly from the RMAN repository stored in the control file, making it a fast and reliable way to assess your database file structure before performing backup or recovery operations.

Code

1REPORT SCHEMA;

Basic Syntax

The simplest form of the command is:

1RMAN> REPORT SCHEMA;

This command connects to the target database and reads information from the control file to generate a report. You do not need to connect to a recovery catalog for basic usage, though a target database connection is required.

Advanced Syntax with AT Clause

When connected to a recovery catalog, you can view the database schema from a past point in time:

1RMAN> REPORT SCHEMA AT TIME 'SYSDATE-14';  -- Schema from 14 days ago
2RMAN> REPORT SCHEMA AT SCN 1000;           -- Schema at specific SCN
3RMAN> REPORT SCHEMA AT SEQUENCE 100 THREAD 1;  -- Schema at log sequence

Important Note: You must be connected to a recovery catalog when using the AT clause.

Output Structure

The command produces two main sections:

1. List of Permanent Datafiles

  • File number
  • Size in megabytes
  • Tablespace name
  • Rollback segments indicator (YES/NO)
  • Complete datafile path

2. List of Temporary Files

  • File number
  • Size in megabytes
  • Tablespace name
  • Maximum size in megabytes
  • Complete tempfile path

Key Points

  • No Recovery Catalog Required: The basic REPORT SCHEMA command works with just a target database connection and reads from the control file
  • Real-Time Information: The command shows the current state of database files, reflecting any recent changes to the database structure
  • Quick Alternative to SQL: Instead of querying dba_data_files or dba_temp_files views, you can get all file information in one command
  • Pre-Backup Verification: Database administrators commonly use this command to verify which files need backup before executing RMAN backup operations
  • Works with Pluggable Databases: The command functions correctly with Oracle multitenant architecture and individual PDBs

Insights and Explanations

When to Use REPORT SCHEMA

The REPORT SCHEMA command is particularly useful in several scenarios:

  1. Before Backup Operations: Verify all datafiles that will be included in your backup strategy
  2. After Database Changes: Confirm that newly added datafiles or tablespaces are visible in the database structure
  3. Recovery Planning: Understand the database layout before performing restore and recovery operations
  4. Database Documentation: Generate a quick snapshot of your database file structure for documentation purposes

Understanding the Term "Schema"

When you see "REPORT SCHEMA" in RMAN context, do not confuse it with database schemas containing user objects and data. In RMAN terminology, "schema" refers to the physical structure of the database—the files and tablespaces that make up the database storage architecture.

Control File vs Recovery Catalog

By default, RMAN uses the target database control file to retrieve schema information. The control file contains current metadata about all database files. When you see the message "using target database control file instead of recovery catalog," this indicates normal operation without a catalog connection.

However, if you need historical information about your database schema from a previous point in time, you must connect to a recovery catalog. The recovery catalog stores extended metadata and allows you to query past database states using the AT clause.

Practical Example

Here's a real-world example showing how the command updates dynamically:

 1RMAN> REPORT SCHEMA;
 2
 3Report of database schema for database with db_unique_name HAWK
 4
 5List of Permanent Datafiles
 6===========================
 7File Size(MB) Tablespace           RB segs Datafile Name
 8---- -------- -------------------- ------- ------------------------
 91    1040     SYSTEM               YES     /u01/oradata/hawk/system01.dbf
102    890      SYSAUX               NO      /u01/oradata/hawk/sysaux01.dbf
113    1025     UNDOTBS1             YES     /u01/oradata/hawk/undotbs01.dbf
124    10       USERS                NO      /u01/oradata/hawk/users01.dbf
13
14List of Temporary Files
15=======================
16File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
17---- -------- -------------------- ----------- --------------------
181    37       TEMP                 32767       /u01/oradata/hawk/temp01.dbf

After adding a new datafile to the USERS tablespace, running REPORT SCHEMA again immediately shows the new file.

Integration with Other RMAN Commands

The REPORT SCHEMA command is part of RMAN's broader reporting capabilities. Other related commands include:

  • REPORT OBSOLETE - Shows backups no longer needed based on retention policy
  • REPORT NEED BACKUP - Identifies files requiring backup
  • LIST BACKUP - Displays available backup sets

Best Practices

  1. Run Before Major Operations: Always check your schema before starting complex backup or recovery procedures
  2. Verify After Changes: After adding or modifying datafiles, confirm the changes are reflected
  3. Document Regularly: Save output periodically for historical reference and capacity planning
  4. Combine with Other Reports: Use alongside other RMAN report commands for comprehensive database assessment

References

Posts in this series