此脚本的使用场景是需要使用datapump方式进行数据迁移时，需要在目标数据库上创建对应的表空间，这时对于表空间数量比较多的系统，
比如peoplesoft来说，手工单独创建表空间会是一个比较麻烦的事情。
以下脚本在源数据库上运行，获取表空间的创建脚本，然后只需对路径相应修改即可使用。
------------------------
-- 获取表空间生成脚本 --
------------------------
-- WX:DBAJOE399 --
set serverout on;
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;
临时表空间创建脚本
------------------------
-- 获取临时表空间生成脚本 --
------------------------
DECLARE
CURSOR c_dbf IS
SELECT tablespace_name,
file_name,
bytes,
maxbytes,
increment_by,
autoextensible
FROM dba_temp_files
ORDER BY tablespace_name;
v_tbname_temp dba_temp_files.TABLESPACE_NAME%type;
v_tbname dba_temp_files.TABLESPACE_NAME%type;
v_dbfname dba_temp_files.file_name%type;
v_bytes dba_temp_files.bytes%type;
v_maxbytes dba_temp_files.maxbytes%type;
v_inc dba_temp_files.increment_by%type;
v_autoext dba_temp_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 temporary tablespace '|| v_tbname || ' tempfile '||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 temporary tablespace ' || v_tbname ||
' tempfile ' || 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 tempfile ' || 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 temporary tablespace ' || v_tbname ||
' tempfile ' || chr(39) || v_dbfname ||
chr(39) || ' size ' || v_bytes || ';');
ELSE
DBMS_OUTPUT.PUT_LINE('alter tablespace ' || v_tbname ||
' add tempfile ' || chr(39) || v_dbfname ||
chr(39) || ' size ' || v_bytes || ';');
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;