Oracle数据获取表空间创建脚本

摘要：以下脚本在源数据库上运行，获取表空间的创建脚本，然后只需对路径相应修改即可使用。临时表空间创建脚本获取临时表空间生成脚本运行完成之后就可以在中看到临时表空间创建脚本。

此脚本的使用场景是需要使用datapump方式进行数据迁移时，需要在目标数据库上创建对应的表空间，这时对于表空间数量比较多的系统，比如peoplesoft来说，手工多带带创建表空间会是一个比较麻烦的事情。

以下脚本在源数据库上运行，获取表空间的创建脚本，然后只需对路径相应修改即可使用。

------------------------
-- 获取表空间生成脚本 --
------------------------

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;

运行完成之后就可以在dbms_output中看到表空间创建脚本。

------------------------
-- 获取临时表空间生成脚本 --
------------------------

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;

运行完成之后就可以在dbms_output中看到临时表空间创建脚本。