取得所有用户表空间信息的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;
------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24620620/viewspace-1052059/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24620620/viewspace-1052059/