Pl/Sql script to generate n number of datafile in oracle database.

 we have scenario ,used to add 100 plus datafiles to a tablespace  and we dont need to create it manually,I have created pl/sql script to generate n number of datafiles for this table space.

This is really helped us.

DECLARE

    v_tablespace_name VARCHAR2(30) := 'USER'; -- Specify your tablespace name

    v_datafile_prefix VARCHAR2(50) := '/path/to/datafile'; -- Specify the base path for datafiles

    v_datafile_size_mb NUMBER := 100; -- Specify the size of each datafile in MB

    v_num_files NUMBER := 100; -- Specify the number of datafiles to add

    

    v_sql VARCHAR2(1000);

BEGIN

    FOR i IN 1..v_num_files LOOP

        -- Generate SQL to add datafile

        v_sql := 'ALTER TABLESPACE ' || v_tablespace_name ||

                 ' ADD DATAFILE ''' || v_datafile_prefix || i || '.dbf''' ||

                 ' SIZE ' || v_datafile_size_mb || 'M AUTOEXTEND OFF NEXT 100M MAXSIZE UNLIMITED;';

        

        -- Output SQL statement

        DBMS_OUTPUT.PUT_LINE(v_sql);

    END LOOP;

END;

/