数据库表空间利用率及得到表空间创建相关

来源：互联网发布：乌鲁木齐网站排名seo 编辑：程序博客网时间：2024/06/07 12:35

查看表空间利用率

select
a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2/1024/1024 表空间大小M,
(b.b2-a.a2)/1024/1024 已使用M,
substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1;

查看表空间对应的物理文件

select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name

得到当前表空间存储脚本
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;

0 0