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;
/
