DECLARE
CURSOR c_dbf IS
SELECT tablespace_name,
file_name,
bytes,
maxbytes,
increment_by,
AUTOEXTENSIBLE
FROM dba_data_files
where tablespace_name not in
('SYSTEM', 'SYSAUX', 'USERS','UNDOTBS1')
ORDER BY tablespace_name;
v_tbname_temp DBA_DATA_FILES.TABLESPACE_NAME%type;
v_tbname DBA_DATA_FILES.TABLESPACE_NAME%type;
v_dbfname DBA_DATA_FILES.FILE_NAME%type;
v_bytes dba_data_files.bytes%type;
v_maxbytes dba_data_files.maxbytes%type;
v_inc dba_data_files.increment_by%type;
v_autoext dba_data_files.AUTOEXTENSIBLE%type;
BEGIN
OPEN c_dbf;
FETCH c_dbf
INTO v_tbname, v_dbfname, v_bytes, v_maxbytes, v_inc, v_autoext;
-- DBMS_OUTPUT.PUT_LINE('create tablespace '|| v_tbname || ' datafile '||chr(39)||v_dbfname ||chr(39)|| ' size '||v_bytes ||' autoextend on ' ||' next '||v_inc||' maxsize ' ||v_maxbytes||';' );
v_tbname_temp := 'a';
WHILE c_dbf%FOUND LOOP
IF v_autoext = 'YES' THEN
IF v_tbname_temp <> v_tbname THEN
DBMS_OUTPUT.PUT_LINE('create tablespace ' || v_tbname ||
' datafile ' || chr(39) || v_dbfname ||
chr(39) || ' size ' || v_bytes ||
' autoextend on ' || ' next ' || v_inc ||
' maxsize ' || v_maxbytes || ';');
ELSE
DBMS_OUTPUT.PUT_LINE('alter tablespace ' || v_tbname ||
' add datafile ' || chr(39) || v_dbfname ||
chr(39) || ' size ' || v_bytes ||
' autoextend on next ' || v_inc ||
' maxsize ' || v_maxbytes || ';');
END IF;
ELSE
IF v_tbname_temp <> v_tbname THEN
DBMS_OUTPUT.PUT_LINE('create tablespace ' || v_tbname ||
' datafile ' || chr(39) || v_dbfname ||
chr(39) || ' size ' || v_bytes || ';');
ELSE
DBMS_OUTPUT.PUT_LINE('alter tablespace ' || v_tbname ||
' add datafile ' || chr(39) || v_dbfname ||
chr(39) || ' size ' || v_bytes ||
' autoextend on next ' || v_inc ||
' maxsize ' || v_maxbytes || ';');
END IF;
END IF;

v_tbname_temp := v_tbname;
FETCH c_dbf
INTO v_tbname, v_dbfname, v_bytes, v_maxbytes, v_inc, v_autoext;
END LOOP;
CLOSE c_dbf;
END;
[font=Monaco]/