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!

Check OS Free Space by Toad / SQL Developer

1. Create a Databae Directory

create or replace directory DATA_PUMP_DIR as '/oracle/app/admin/e2edb/dpdump/'

2. Make Script

cd /oracle/app/admin/e2edb/dpdump/

vi run_df.sh

#/bin/bash

/bin/df -Pl

chmod 775 run_df.sh

3. Create External Table

CREATE TABLE df

   (

     "FILESYSTEM" VARCHAR2(100),

     "BLOCKS" NUMBER,

     "USED" NUMBER,

     "AVAILABLE" NUMBER,

     "CAPACITY" VARCHAR2(10),

     "MOUNT" VARCHAR2(100)

   )

   ORGANIZATION external

   (

     TYPE oracle_loader

     DEFAULT DIRECTORY DATA_PUMP_DIR

     ACCESS PARAMETERS

     (

       RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

           preprocessor  DATA_PUMP_DIR:'run_df.sh'

       READSIZE 1048576

       SKIP 1

       FIELDS TERMINATED BY WHITESPACE LDRTRIM

       REJECT ROWS WITH ALL NULL FIELDS

       (

         "FILESYSTEM" CHAR(255)

           TERMINATED BY WHITESPACE,

         "BLOCKS" CHAR(255)

           TERMINATED BY WHITESPACE,

         "USED" CHAR(255)

           TERMINATED BY WHITESPACE,

         "AVAILABLE" CHAR(255)

           TERMINATED BY WHITESPACE,

         "CAPACITY" CHAR(255)

           TERMINATED BY WHITESPACE,

         "MOUNT" CHAR(255)

           TERMINATED BY WHITESPACE

       )

     )

     location

     (

       DATA_PUMP_DIR:'run_df.sh'

     )

   )

   /

alter table df reject limit unlimited;

   

   

4. Check Functionality 

select * from df;

5. Create View

create or replace view dfv as

select Mount,blocks/1024/1024 as "Allocated_GB",available/1024/1024 as "Free_GB",Used/1024/1024 as "Total_Used",capacity as "%Used"from df order by 5 desc

select * from dfv

For AIX

1. Create a Databae Directory

create or replace directory DATA_PUMP_DIR as '/oracle/11.2.0/admin/treasury/dpdump/'

2. Make Script

cd /oracle/11.2.0/admin/treasury/dpdump/

vi run_df.sh

#/bin/bash

/bin/df -k

chmod 775 run_df.sh

3. Create External Table

CREATE TABLE df

   (

     "FILESYSTEM" VARCHAR2(100),

     "GB blocks" NUMBER,

     "Free" NUMBER,

     "%Used" VARCHAR2(1000),

     "Iused" VARCHAR2(10),

     "%Iused" VARCHAR2(100),

     "Mounted on" VARCHAR2(1000)

   )

   ORGANIZATION external

   (

     TYPE oracle_loader

     DEFAULT DIRECTORY DATA_PUMP_DIR

     ACCESS PARAMETERS

     (

       RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

           preprocessor  DATA_PUMP_DIR:'run_df.sh'

       READSIZE 1048576

       SKIP 1

       FIELDS TERMINATED BY WHITESPACE LDRTRIM

       REJECT ROWS WITH ALL NULL FIELDS

       (

         "FILESYSTEM" CHAR(255)

           TERMINATED BY WHITESPACE,

         "GB blocks" CHAR(255)

           TERMINATED BY WHITESPACE,

         "Free" CHAR(255)

           TERMINATED BY WHITESPACE,

         "%Used" CHAR(255)

           TERMINATED BY WHITESPACE,

         "Iused" CHAR(255)

           TERMINATED BY WHITESPACE,

         "%Iused" CHAR(255)

           TERMINATED BY WHITESPACE,

            "Mounted on" CHAR(500)

           TERMINATED BY WHITESPACE

           

       )

     )

     location

     (

       DATA_PUMP_DIR:'run_df.sh'

     )

   )

   /

alter table df reject limit unlimited;

select * from df;

create or replace view dfv as select "Mounted on" as "Mount","GB blocks"/1024/1024 as "Allocated_GB","Free"/1024/1024 as "Free_GB",("GB blocks"-"Free")/1024/1024 as "Total_Used","%Used" as "%Used" from df

order by 5 desc;

select * from dfv

Referece:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5088536900346242095