python创建数据库表空间_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中看到临时表空间创建脚本。