Managing Oracle Data Files – Resizing and Adding

In Oracle databases, proper management of data files is essential to ensure performance, availability, and scalability. This guide explains how to view, resize, and add data files using SQL*Plus, SQL Developer, TOAD, and command-line tools.

🔍 When to Resize Data Files?

• Resize a data file when available free space is less than 500MB.
• Use caution: only resize files that are not already at their maximum size (typically 32GB for most configurations).
• Always connect using a privileged user like SYS or SYSTEM.

🔧 Monitor via Alert Log

You should always monitor changes in the alert log:

        # Connect as Oracle OS user

            su - oracle

        # Monitor the alert log in real-time

            tail -900f /u01/.../trace/alert_<SID>.log

📘 Using SQL*Plus or SQL Developer

View Existing Data Files

        -- Connect as SYSDBA

            sqlplus / as sysdba

        -- Format output

            COLUMN tablespace_name FORMAT A20 

            COLUMN file_name FORMAT A50

            COLUMN USER_BYTES FORMAT 999,999,990.999 

            COLUMN MAXBYTES FORMAT 999,999,990.999 

            COLUMN autoextensible FORMAT A14

        -- Query existing data files

            SELECT 

                tablespace_name, 

                file_name, 

                USER_BYTES/1024/1024 AS "Used MB", 

                MAXBYTES/1024/1024 AS "Max MB", 

                autoextensible 

            FROM dba_data_files;

To check data files for a specific tablespace:

            SELECT tablespace_name, file_name, USER_BYTES/1024/1024, MAXBYTES/1024/1024,                    autoextensible 

            FROM dba_data_files 

            WHERE tablespace_name = 'APPS_TS_QUEUES';

Resize an Existing Data File

Example – Resize a data file to 15GB:

            ALTER DATABASE DATAFILE '/u02/PROD/data/a_queue28.dbf' RESIZE 15G;

Example – Resize to 8GB:

            ALTER DATABASE DATAFILE '/u01/PROD/db/data/a_txn_ind09.dbf' RESIZE 8G;

✅ If successful, you'll see:

            Database altered.

Confirm the change by refreshing SQL Developer or rerunning the SELECT query above.

Add a New Data File

To extend an existing tablespace:

            ALTER TABLESPACE users1 ADD DATAFILE SIZE 20M;

With more detailed control:

            ALTER TABLESPACE UNDOTBS1 ADD DATAFILE 

            '/oradata/PRODCDB/PROD/undotbs02.dbf' 

            SIZE 1G 

            AUTOEXTEND ON 

            NEXT 250M 

            MAXSIZE 32767M;

Example for ASM:

            ALTER TABLESPACE SYSAUX ADD DATAFILE '+DATA' 

            SIZE 2G 

            AUTOEXTEND ON 

            NEXT 1G 

            MAXSIZE 32767M;

Add a TEMPFILE

            ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' 

            SIZE 1G 

            AUTOEXTEND ON 

            NEXT 250M 

            MAXSIZE 32767M;

🛠️ Using TOAD

View Tablespaces and Data Files

1. Log in with DB credentials.

2. Navigate to: Database > Administrator > Tablespaces.

3. Double-click a tablespace name to see data files.

Resize via TOAD

1. Double-click the data file name.

2. Enter the new size in GB.

3. Click OK to apply.

📊 View Used and Free Space of Data Files

Run this SQL to get a breakdown of size, usage, and max size:

            SELECT 
               file_name, 
               tablespace_name AS tbs, 
               status,
               ROUND(bytes/1024/1024/1024, 3) AS "size_gb",
               ROUND((bytes - user_bytes)/1024/1024/1024, 3) AS "used_gb", 
               ROUND(user_bytes/1024/1024/1024, 3) AS "available_gb",
               ROUND(maxbytes/1024/1024/1024, 3) AS "max_gb", 
               autoextensible 
        FROM dba_data_files 
        WHERE tablespace_name = 'USERS';

🧠 Pro Tip

Be cautious when resizing or adding data files in a production environment. Monitor alert logs for errors and refer to Oracle documentation or internal SOPs before performing changes.

🧵 Further Reading

• ORA-01274: Cannot add data file that was originally created as… (dbaclass.com)