取得所有使用者表空間資訊的sql

取得所有使用者表空間資訊的sql[@more@]DECLARE
v_tmp_tp_name VARCHAR2(30):='TEMP'; --臨時表空間名稱變數
v_undo_tp_name VARCHAR2(30):='UNDOTBS1';
v_block_size varchar2(20):='8'; --以k為單位
CURSOR v_tp_name IS
SELECT DISTINCT tablespace_name FROM DBA_data_files
UNION
SELECT v_tmp_tp_name tablespace_name FROM dual;
CURSOR v_tablespace(v_tablespace_name VARCHAR2) IS
SELECT file_name,ceil(bytes/1024) ts_size,autoextensible,increment_by*to_number(v_block_size) increment_by
FROM DBA_data_files WHERE tablespace_name=v_tablespace_name
UNION ALL --將臨時表空間加入
SELECT file_name,ceil(bytes/1024) ts_size,autoextensible,increment_by*to_number(v_block_size) increment_by
FROM dba_temp_files WHERE tablespace_name=v_tablespace_name;
CURSOR v_tp_info(v_tablespace_name VARCHAR2) IS --得到表空間資訊
SELECT
logging,status,contents,extent_management,allocation_type,
next_extent,segment_space_management
FROM dba_tablespaces WHERE tablespace_name=v_tablespace_name;
v_autoextend VARCHAR2(100);
v_file_name VARCHAR2(100);
v_ts_size VARCHAR2(100);
v_autoextensible VARCHAR2(100);
v_increment_by VARCHAR2(100);
v_tmp VARCHAR2(100);
v_i INTEGER;--用來判斷是否在資料檔案後加逗號
v_logging varchar2(20); --獲取表空間資訊變數
v_status varchar2(20);
v_contents varchar2(20);
v_extent_management varchar2(20);
v_allocation_type varchar2(20);
v_next_extent varchar2(20);
v_segment_space_management varchar2(20);

v_tp_script VARCHAR2(200);

BEGIN
FOR v_tp IN v_tp_name LOOP
v_i:=0;
v_tp_script:='';
dbms_output.put_line('----------------------------------------------------------');
IF(v_tp.tablespace_name=v_tmp_tp_name) THEN
dbms_output.put_line('CREATE TEMPORARY TABLESPACE '||v_tp.tablespace_name||' TEMPFILE');
ELSIF(v_tp.tablespace_name=v_undo_tp_name) THEN
dbms_output.put_line('CREATE UNDO TABLESPACE '||v_tp.tablespace_name||' DATAFILE');
ELSE
dbms_output.put_line('CREATE TABLESPACE '||v_tp.tablespace_name||' DATAFILE');
END IF;
OPEN v_tablespace(v_tp.tablespace_name);
LOOP
FETCH v_tablespace INTO v_file_name,v_ts_size,v_autoextensible,v_increment_by;
EXIT WHEN v_tablespace%NOTFOUND;
IF(v_i=1) THEN
dbms_output.put_line(',');
END IF;
IF(v_i=0) THEN
v_i:=1;
END IF;
SELECT
CASE WHEN v_autoextensible='YES'
THEN --即使表空間的大小大於1M，如果在除以1024不為整的情況下，Oracle選擇K為單位
CASE WHEN ceil(v_increment_by/1024)=v_increment_by/1024 THEN
'autoextend on next '||ceil(v_increment_by/1024)||'M MAXSIZE UNLIMITED'
ELSE
'autoextend on next '||v_increment_by||'K MAXSIZE UNLIMITED'
END
--是否是unlimited不好判斷,直接寫為unlimited
ELSE
'autoextend off'
END INTO v_tmp FROM dual;
IF (v_ts_size/1024=ceil(v_ts_size/1024)) THEN --即使表空間的大小大於1M，如果在除以1024不為整的情況下，Oracle選擇K為單位
dbms_output.put_line(''''||v_file_name||''' size '||v_ts_size/1024||'M '||v_tmp||'');
ELSE
dbms_output.put_line(''''||v_file_name||''' size '||v_ts_size||'K '||v_tmp||'');
END IF;
v_tmp:='';
END LOOP;
CLOSE v_tablespace;
OPEN v_tp_info(v_tp.tablespace_name);
LOOP
FETCH v_tp_info INTO v_logging,v_status,v_contents,v_extent_management,
v_allocation_type,v_next_extent,v_segment_space_management;
EXIT WHEN v_tp_info%NOTFOUND;
IF (v_logging='LOGGING' AND v_tp.tablespace_name<>v_undo_tp_name) THEN
v_tp_script:=v_tp_script||v_logging||' ';
END IF;
IF (v_tp.tablespace_name<>v_tmp_tp_name) THEN
v_tp_script:=v_tp_script||v_status||' ';
END IF;
IF(v_contents NOT IN ('UNDO','TEMPORARY') AND v_tp.tablespace_name<>v_undo_tp_name) THEN
v_tp_script:=v_tp_script||v_contents||' ';
END IF;
IF(v_contents <>'UNDO') THEN
v_tp_script:=v_tp_script||'EXTENT MANAGEMENT LOCAL'||' ';
END IF;
IF(v_allocation_type<>'UNIFORM' AND v_tp.tablespace_name<>v_tmp_tp_name AND v_tp.tablespace_name<>v_undo_tp_name) THEN
v_tp_script:=v_tp_script||'AUTOALLOCATE'||' ';
END IF;
IF (v_allocation_type='UNIFORM' AND v_tp.tablespace_name<>v_undo_tp_name) THEN
IF(ceil(v_next_extent/1024/1024)=v_next_extent/1024/1024) THEN
v_tp_script:=v_tp_script||'UNIFORM SIZE '||to_char(ceil(v_next_extent/1024/1024))||'M ';
ELSE
v_tp_script:=v_tp_script||'UNIFORM SIZE '||to_char(ceil(v_next_extent/1024))||'K ';
END IF;
END IF;
IF(v_contents <>'TEMPORARY') THEN
v_tp_script:=v_tp_script||'BLOCKSIZE '||v_block_size||'K ';
END IF;
IF(v_segment_space_management='MANUAL' AND
v_tp.tablespace_name<>'SYSTEM' AND
v_tp.tablespace_name<>v_tmp_tp_name AND
v_tp.tablespace_name<>v_undo_tp_name) THEN
v_tp_script:=v_tp_script||'SEGMENT SPACE MANAGEMENT MANUAL ';
END IF;
IF(v_segment_space_management='AUTO' AND v_tp.tablespace_name<>v_undo_tp_name) THEN
v_tp_script:=v_tp_script||'SEGMENT SPACE MANAGEMENT AUTO ';
END IF;
v_tp_script:=v_tp_script||';';
dbms_output.put_line(v_tp_script);
END LOOP;
CLOSE v_tp_info;
END LOOP;
END;
------------------------------------------------------------------------