Adding Datafile in Oracle Tablespace by Dynamic SQL

1. Check OS Free Space by Toad / SQL Developer

2. Add Datafile based on particular tablespace datafile number

3. Add Datafile based on overall database datafile number

Add Datafile based on particular tablespace datafile number

define v_path = '';

select 'ALTER TABLESPACE '|| TBS_NAME ||' ADD DATAFILE  ''&v_path'||lower(TBS_NAME)||'_'||max(df#+1)||'.dbf'''||

' SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 31G;' as cmd

FROM  (

select df.file# as df#,df.ts#,df.name,tbs.name as TBS_NAME from v$datafile df,v$tablespace tbs

where df.ts#=tbs.ts#  and tbs.name=upper('&name')) group by TBS_NAME;

Enter the datafile path and tablespace name.

Generated SQL:

ALTER TABLESPACE FCAT ADD DATAFILE  '/data1/oradata/fcat_73.dbf' SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 31G;

copy this generated SQL and run, datafile successfully added!

Add Datafile based on overall database datafile number

define v_path = '';

select 'ALTER TABLESPACE '|| TBS_NAME ||' ADD DATAFILE  ''&v_path'||lower(TBS_NAME)||'_'||(select max(file#+1)from v$datafile)||'.dbf'''||

' SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 31G;' as cmd

FROM  (

select df.file# as df#,df.name,tbs.name as TBS_NAME from v$datafile df,v$tablespace tbs

where df.ts#=tbs.ts#  and tbs.name=upper('&name')) group by TBS_NAME;

Enter the datafile path and tablespace name.

Generated SQL:

ALTER TABLESPACE FCAT ADD DATAFILE  '/data1/oradata/fcat_74.dbf' SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 31G;

copy this generated SQL and run, datafile successfully added!