【Oracle】Generate the tablespace creation in a time

 

此脚本的使用场景是需要使用datapump方式进行数据迁移时，需要在目标数据库上创建对应的表空间，这时对于表空间数量比较多的系统，

比如peoplesoft来说，手工单独创建表空间会是一个比较麻烦的事情。

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

------------------------ -- 获取表空间生成脚本 -- ------------------------   -- 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;

　　

临时表空间创建脚本

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

------------------------ -- 获取临时表空间生成脚本 -- ------------------------   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;