Automating Datafile Addition to Oracle Tablespaces

Managing datafiles in Oracle databases, especially in an ASM (Automatic Storage Management) environment, can be streamlined through automation. This guide provides a SQL script to efficiently add datafiles to a specified tablespace.

Overview

When a tablespace runs low on space, it’s essential to add new datafiles to accommodate future growth. This script will help you:

1. Retrieve information about the existing datafiles in a specified tablespace.
2. Check available ASM disk sizes.
3. Prompt for necessary inputs to add a new datafile.

Script Overview

1. Prompt for Tablespace Name: The script begins by asking for the tablespace name where the datafile will be added.
2. Retrieve Existing Datafiles: It then displays the last five datafiles in the specified tablespace, helping you understand the current usage.
3. Show Available ASM Disk Sizes: The script lists available ASM disks and their sizes, ensuring you choose a suitable disk for the new datafile.
4. User Inputs for Tablespace, Disk, and Size: Prompts are provided for entering the tablespace name, disk name and the size (in GB) for the new datafile.
5. Add the Datafile: Finally, it executes the ALTER TABLESPACE command to add the new datafile with the specified size.

Step 1: Create the SQL Script (add_datafile.sql)

Open a terminal and create the add_datafile.sql file using vi:

vi add_datafile.sql

Press i to enter insert mode, and then paste the following SQL code:

PROMPT #### Datafile Addition to Tablespace #####
PROMPT
PROMPT
PROMPT Query to retrieve the last 5 datafiles belonging to the specified tablespace on ASM disk
PROMPT
PROMPT Enter the tablespace name:
ACCEPT ts_name PROMPT 'Tablespace Name: '
set pages 500 lines 300
COLUMN FILE_NAME for a70
COLUMN TABLESPACE_NAME for a30
select FILE_NAME, TABLESPACE_NAME FROM dba_data_files where tablespace_name='&ts_name' AND rownum <=5;
PROMPT Available ASM Disk Sizes (in GB):
set pages 500 lines 300
COLUMN name FORMAT A30
COLUMN total_gb FORMAT 999999
SELECT NAME, STATE, TYPE,
     ROUND(TOTAL_MB / 1024, 2) "SIZE_GB",
     ROUND(FREE_MB / 1024, 2) "AVAILABLE_GB",
     ROUND((total_mb - free_mb) / total_mb * 100, 2) AS "USED%"
FROM v$asm_diskgroup;
PROMPT Enter the tablespace name:
ACCEPT ts_name PROMPT 'Tablespace Name: '
PROMPT Enter the disk name:
ACCEPT disk_name PROMPT 'Disk Name: '
PROMPT Enter the size in GB:
ACCEPT size_gb PROMPT 'Size in GB: '
ALTER TABLESPACE &ts_name ADD DATAFILE '+&disk_name' SIZE &size_gb G;

Save and Exit: Type :wq and press Enter to save the file and exit vi

Running the SQL File

Once the add_datafile.sql file is ready, connect to your Oracle database and execute the script:

sqlplus / as sysdba

Then run the script:

@add_datafile.sql

Sample Output:

You can refer to the image below for a visual representation of the output:

Alert: The information provided on this website is for educational purposes only. It has been tested internally, but there is no guarantee it will work in every environment. It is strongly recommended to first test the process in a non-production environment, such as a User Acceptance Testing (UAT) setup. This allows you to verify configurations, identify any issues, and ensure a smooth transition to production, minimizing the risk of disruptions in your live environment. Always conduct thorough testing before applying changes to production systems.

I hope you found this blog helpful! We’d love to hear about your experiences or any questions you might have, so please share your thoughts in the comments. Your feedback is greatly appreciated. Stay tuned for more tips and advanced topics on managing Oracle Database!