Oracle re-indexing Shell script, SQL script sharing, oracleshell

Oracle re-indexing Shell script, SQL script sharing, oracleshell

Indexing is a powerful weapon to improve database query performance. Without an index, it is like a library without a book tag. It is more difficult to find a book you want than to climb to the sky. However, in the process of using indexes, especially in the case of batch DML, the corresponding fragments will be generated, and the B-tree height will change accordingly, therefore, you can reconstruct indexes with large changes to improve performance. N a long time ago, Oracle recommended that we regularly recreate indexes on tables with a height of 4 and deleted index entries accounting for at least 20% of the total number of existing index entries. However, Oracle strongly recommends that you do not rebuild indexes on a regular basis. For details, refer to the article: the necessity of re-indexing Oracle. However, it is necessary to re-build the index, but it is not recommended to regularly. This article provides a script for re-indexing for your reference.

1. re-create the index shell script

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

2. re-create the SQL script called by the index

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. Input log sample

Current date and time is : Sun Apr 20 02:00:02 HKT 2014 Current DB is SYBO2 ===============================================Rebuilding index GX_ADMIN.SYN_OUT_DATA_TBL_PK...Rebuilding index GX_ADMIN.IDX_TDBK_SPLNK_PARENT_REF...Rebuilding index GX_ADMIN.IDX_TDBK_SPLNK_CHILD_REF...Rebuilding index GX_ADMIN.PK_TRADE_BROKER_TBL...Rebuilding index GX_ADMIN.IDX_TDBK_INPUT_DATE...  ................

4. Postscript

A. If there are multiple instances on the same server and each instance has the same schema, this script will patrol all instances and rebuild Based on the analyze results.
A. You should make adjustments as needed, such as the script path information.
B. Modify the schema name.
D. Adjust the degree of parallelism according to the system environment.

Write a shell script in linux to extract data with specified conditions from the oracle database and store the data in a text file.

Empno = 100 does not exist. It is changed to some data.
#! /Bin/bash
Result = $ (sqlplus-s 'Scott/tiger @ dbname' <EOF
Spool test.txt
Set pages 0
Set feed off
Set heading off;
Set feedback off;
Set verify off;
Set linesize 1000;
SELECT * FROM scott. emp where empno = 7369;
Spool off
EOF
)
Echo $ result
~
~
~
~
~
~
~
~
~
"Test. sh" 14L, 256C written
Oracle @ ****: ~> ./Test. sh
7369 smith clerk 7902 17-DEC-80 2240.06 20
Oracle @ ****: ~> More test.txt
7369 smith clerk 7902 17-DEC-80 2240.06
20

How can shell scripting be used with oracle?

Empno = 100 does not exist. It is changed to some data.
#! /Bin/bash
Result = $ (sqlplus-s 'Scott/tiger @ dbname' <EOF
Spool test.txt
Set pages 0
Set feed off
Set heading off;
Set feedback off;
Set verify off;
Set linesize 1000;
SELECT * FROM scott. emp where empno = 7369;
Spool off
EOF
)
Echo $ result
~
~
~
~
~
~
~
~
~
"Test. sh" 14L, 256C written
Oracle @ ****: ~> ./Test. sh
7369 smith clerk 7902 17-DEC-80 2240.06 20
Oracle @ ****: ~> More test.txt
7369 smith clerk 7902 17-DEC-80 2240.06
20