Tablespace automated script

This script will provide you the alter statement to add a datafile to tablespace.

Copy below script to a file Ex: test1sql

sqlplus / as sysdba
@test.sql


prompt
select name,open_mode from v$database
/
prompt

set pagesize 100
set linesize 132
set feedback off
set verify off
set wrap off

select a.tablespace_name name,
           round((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))/1024/1024)      Total_MB,
           round((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
           sum(a.bytes)/count( distinct b.file_id ))/1024/1024)                       Used_MB,
           round((sum(a.bytes)/count( distinct b.file_id ))/1024/1024)                       Free_MB,
           round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
                           (sum(a.bytes)/count( distinct b.file_id ) )) /
           (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )))  pct_used,
     round(100 - round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
                           (sum(a.bytes)/count( distinct b.file_id ) )) /
           (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))))  "PCT_FREE(%)"
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name
order by pct_used
/
prompt
prompt ********************************************************************************************
prompt
accept ts_name prompt 'Enter Tablespace name: '
prompt

set lines 200
set pages 200
col FILE_NAME for a50

select file_name, bytes/1024/1024 sizem,maxbytes/1024/1024 msizem,autoextensible,increment_by from dba_data_files where tablespace_name='&ts_name' order by file_name
/

prompt
prompt ********************************************************************************************
prompt
accept mount prompt 'Enter moount point name to Check avilable space(Ex: /d05): '
prompt

!df -h &mount

prompt
prompt ###############################################################################################
prompt
prompt ==> Now lets add a  datafile...
prompt -------------------------------

prompt
accept ts_name prompt 'Enter Tablespace name: '
prompt
accept new_data_file prompt 'Enter new datafile name(make sure no spaces befor and after of filename) : '
prompt
accept initial_size prompt 'Enter initial size(Ex: 2048M): '
prompt
accept ON_OFF prompt 'Enter AUTOEXTEND option ON or OFF: '
prompt
accept next_size prompt 'Enter next size(Ex: 1024M): '
prompt
accept max_size prompt 'Enter MAX size (Ex: 8192M): '
prompt
prompt ****************************************************************************************************
prompt
prompt ==> verify the below statement before execution...
prompt ---------------------------------------------

prompt
prompt ALTER TABLESPACE &ts_name ADD DATAFILE '&new_data_file' SIZE &initial_size AUTOEXTEND &ON_OFF NEXT &next_size  MAXSIZE &max_size
prompt
prompt #####################################################################################################
prompt


prompt