来自 服务器&运维 2020-01-17 05:36 的文章
当前位置: 澳门威尼斯人平台 > 服务器&运维 > 正文

Oracle中查看表空间使用率的SQL脚本分享

试验数据开头化:

复制代码 代码如下:/* Formatted on 2012/5/31 14:51:13 (QP5 v5.185.11230.41888) */SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS "SUM_BLOCKS", SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+卡塔尔UNION ALL --假如有一时表空间SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, USED_SPACE || 'M' "USED_SPACE(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)ORDER BY 1;

目录是抓好数据库查询性能的有力军械。未有索引,就好比教室未有图书标签同样,找一本书本人想要的书比登天还难。可是索引在选用的进程中,特别是在批量的DML的动静下会发生相应的零碎,以致B树中度会发出相应改变,因而能够对这几个生成比较大的目录进行重构以增进质量。N久此前Oracle提议我们依期重建那个中度为4,已去除的目录条款最少据有现存索引条目款项总量的四分之一的这么些表上的目录。但Oracle以往生硬提出不要依期重新建立索引。具体能够参照小说:Oracle 重新建立索引的要求性。固然如此重新创建索引依然有至关重要的,只是不提出定时。本文给出了重新建立索引的脚本供我们参照他事他说加以考察。

复制代码 代码如下:
create table t as select * from hr.employees;
create index inx_t1 on t(employee_id,first_name desc,last_name);
create index inx_t2 on t(job_id,hire_date);

效果如下:

1、重新建立索引shell脚本

呈现该表全部索引的新闻。

robin@SZDB:~/dba_scripts/custom/bin more rebuild_unbalanced_indices.sh # +-------------------------------------------------------+# + Rebulid unblanced indices |# + Author : Leshami | # + Parameter : No |# +-------------------------------------------------------+#!/bin/bash # --------------------# Define variable# --------------------if [ -f ~/.bash_profile ]; then. ~/.bash_profilefiDT=`date +%Y%m%d`; export DTRETENTION=1LOG_DIR=/tmpLOG=${LOG_DIR}/rebuild_unbalanced_indices_${DT}.logDBA=Leshami@12306.cn# ------------------------------------# Loop all instance in current server# -------------------------------------echo "Current date and time is : `/bin/date`"${LOG}for db in `ps -ef | grep pmon | grep -v grep |grep -v asm |awk '{print $8}'|cut -c 10-`do echo "$db" export ORACLE_SID=$db echo "Current DB is $db" ${LOG} echo "==============================================="${LOG} $ORACLE_HOME/bin/sqlplus -S /nolog @/users/robin/dba_scripts/custom/sql/rebuild_unbalanced_indices.sql${LOG}done;echo "End of rebuilding index for all instance at : `/bin/date`"${LOG}# -------------------------------------# Check log file # -------------------------------------status=`grep "ORA-" ${LOG}`if [ -z $status ];then mail -s "Succeeded rebuilding indices on `hostname` !!!" ${DBA} ${LOG}else mail -s "Failed rebuilding indices on `hostname` !!!" ${DBA} ${LOG}fi# ------------------------------------------------# Removing files older than $RETENTION parameter # ------------------------------------------------find ${LOG_DIR} -name "rebuild_unb*" -mtime +$RETENTION -exec rm {} ;exit

以dba登录

2、重新创立索引调用的SQL脚本

复制代码 代码如下:
set linesize 300;
set pagesize 100;
col c1 format a20;
col c2 format a20;
col c3 format a20;
col c4 format a20;
col c5 format a20;
col INDEX_NAME format a20;
select INDEX_NAME,
max(decode(COLUMN_POSITION,1,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c1,
max(decode(COLUMN_POSITION,2,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c2,
max(decode(COLUMN_POSITION,3,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c3,
max(decode(COLUMN_POSITION,4,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c4,
max(decode(COLUMN_POSITION,5,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c5
from (
select INDEX_NAME,COLUMN_NAME,COLUMN_LENGTH,COLUMN_POSITION,DESCEND
from dba_ind_columns
where table_owner='LIHUILIN'
AND table_name='T'
order by INDEX_NAME,column_position
) group by INDEX_NAME;

robin@SZDB:~/dba_scripts/custom/sql more rebuild_unbalanced_indices.sql conn / as sysdbaset serveroutput on;DECLARE resource_busy EXCEPTION; PRAGMA EXCEPTION_INIT (resource_busy, -54); c_max_trial CONSTANT PLS_INTEGER := 10; c_trial_interval CONSTANT PLS_INTEGER := 1; pmaxheight CONSTANT INTEGER := 3; pmaxleafsdeleted CONSTANT INTEGER := 20; CURSOR csrindexstats IS SELECT NAME, height, lf_rows AS leafrows, del_lf_rows AS leafrowsdeleted FROM index_stats; vindexstats csrindexstats%ROWTYPE; CURSOR csrglobalindexes IS SELECT owner,index_name, tablespace_name FROM dba_indexes WHERE partitioned = 'NO' AND owner IN ('GX_ADMIN'); CURSOR csrlocalindexes IS SELECT index_owner,index_name, partition_name, tablespace_name FROM dba_ind_partitions WHERE status = 'USABLE' AND index_owner IN ('GX_ADMIN'); trial PLS_INTEGER; vcount INTEGER := 0;BEGIN trial := 0; /* Global indexes */ FOR vindexrec IN csrglobalindexes LOOP EXECUTE IMMEDIATE 'analyze index ' || vindexrec.owner ||'.'|| vindexrec.index_name || ' validate structure'; OPEN csrindexstats; FETCH csrindexstats INTO vindexstats; IF csrindexstats%FOUND THEN IF (vindexstats.height  pmaxheight) OR ( vindexstats.leafrows  0 AND vindexstats.leafrowsdeleted  0 AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows)  pmaxleafsdeleted) THEN vcount := vcount + 1; DBMS_OUTPUT.PUT_LINE ( 'Rebuilding index ' || vindexrec.owner ||'.'|| vindexrec.index_name || '...'); alter_index BEGIN EXECUTE IMMEDIATE 'alter index ' || vindexrec.owner ||'.' || vindexrec.index_name || ' rebuild' || ' parallel nologging compute statistics' || ' tablespace ' || vindexrec.tablespace_name; EXCEPTION WHEN resource_busy OR TIMEOUT_ON_RESOURCE THEN DBMS_OUTPUT.PUT_LINE ( 'alter index - busy and wait for 1 sec'); DBMS_LOCK.sleep (c_trial_interval); IF trial = c_max_trial THEN GOTO alter_index; ELSE DBMS_OUTPUT.PUT_LINE ( 'alter index busy and waited - quit after ' || TO_CHAR (c_max_trial) || ' trials'); RAISE; END IF; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('alter index err ' || SQLERRM); RAISE; END; END IF; END IF; CLOSE csrindexstats; END LOOP; DBMS_OUTPUT.PUT_LINE ('Global indices rebuilt: ' || TO_CHAR (vcount)); vcount := 0; trial := 0; /* Local indexes */ FOR vindexrec IN csrlocalindexes LOOP EXECUTE IMMEDIATE 'analyze index ' || vindexrec.index_owner||'.' || vindexrec.index_name || ' partition (' || vindexrec.partition_name || ') validate structure'; OPEN csrindexstats; FETCH csrindexstats INTO vindexstats; IF csrindexstats%FOUND THEN IF (vindexstats.height  pmaxheight) OR ( vindexstats.leafrows  0 AND vindexstats.leafrowsdeleted  0 AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows)  pmaxleafsdeleted) THEN vcount := vcount + 1; DBMS_OUTPUT.PUT_LINE ( 'Rebuilding index ' || vindexrec.index_owner||'.'|| vindexrec.index_name || '...'); alter_partitioned_index BEGIN EXECUTE IMMEDIATE 'alter index ' || vindexrec.index_owner||'.' || vindexrec.index_name || ' rebuild' || ' partition ' || vindexrec.partition_name || ' parallel nologging compute statistics' || ' tablespace ' || vindexrec.tablespace_name; EXCEPTION WHEN resource_busy OR TIMEOUT_ON_RESOURCE THEN DBMS_OUTPUT.PUT_LINE ( 'alter partitioned index - busy and wait for 1 sec'); DBMS_LOCK.sleep (c_trial_interval); IF trial = c_max_trial THEN GOTO alter_partitioned_index; ELSE DBMS_OUTPUT.PUT_LINE ( 'alter partitioned index busy and waited - quit after ' || TO_CHAR (c_max_trial) || ' trials'); RAISE; END IF; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ( 'alter partitioned index err ' || SQLERRM); RAISE; END; END IF; END IF; CLOSE csrindexstats; END LOOP; DBMS_OUTPUT.PUT_LINE ('Local indices rebuilt: ' || TO_CHAR (vcount));END;/exit;

以普通客商登入

3、输入日志样品

本文由澳门威尼斯人平台发布于服务器&运维,转载请注明出处:Oracle中查看表空间使用率的SQL脚本分享

关键词: