【Oracle】Generate the tablespace creation in a time

 

此脚本的使用场景是需要使用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;