Re: creating indexes script

From: Tapio Luukkanen <vtl_at_hemuli.tte.vtt.fi>
Date: 1997/05/22
Message-ID: <waraezonbc.fsf@morko.tte.vtt.fi>#1/1

I have used the following two scripts, define_indexes and define_index. Both assume they are called by some OPS-user with DBA privileges. The 'change_sid' stuff in the beginning is a convention of my scripts, where the first parameter to the script determines and possible changes the current ORACLE_SID and TWO_TASK depending on what machine I am on currently.

All SYS's indexes would be shown with 'define_indexes SYS'. SYS's index named i_user1 would be shown with 'define_index SYS i_user1'.

Hope someone finds these useful.

  Tapio L.

---

#!/bin/ksh
#
# $Id: define_index,v 1.3 1995/11/17 14:03:35 vtl Exp vtl $
#
# Produce a 'create index' statement for given index.
#
# Comments-to: tapio.luukkanen_at_vtt.fi

. /usr/local/kassu/runtime/bin/change_sid

print_tab_owner="lower(di.table_owner) || '.' || "

if test -z "$1"; then
cat <<EOF
usage: define_index [sid_selection] [-no_owner] OWNER INDEX EOF
exit 1
fi

case "$1" in -no_owner) print_tab_owner=""; shift ;; esac

OWNER=$1
INDEX=$2 echo "
/* create index $INDEX; */
"

sqlplus -s <<END_OF_SCRIPT
/
set heading off;
set pagesize 0;
set linesize 79;
set showmode off;
set feedback off;
set verify off;
SELECT
    'CREATE ' || decode(substr(di.uniqueness,1,1),'U','UNIQUE ','') ||     'INDEX ' || lower(di.index_name) || ' ON ' ||     $print_tab_owner lower (di.table_name) || ' ('  FROM
       sys.dba_indexes di
 WHERE di.owner = upper('$OWNER')
   AND di.index_name = upper('$INDEX');

SELECT
    ' ' || lower(ic.column_name) ||
    DECODE(ic.column_position,i2.column_position,'',',')   FROM sys.dba_ind_columns ic, sys.dba_indexes di, sys.dba_ind_columns i2

 WHERE ic.index_owner = di.owner
   AND ic.index_name = di.index_name
   AND di.owner = upper('$OWNER')
   AND di.index_name = upper('$INDEX')
   AND i2.index_owner = di.owner
   AND i2.index_name = di.index_name
   AND i2.column_position = 
      (SELECT max(column_position) FROM sys.dba_ind_columns i3
        WHERE i2.index_owner = i3.index_owner
          AND i2.index_name = i3.index_name)

 ORDER BY ic.column_position;

SELECT
 ') TABLESPACE '||
 lower(di.tablespace_name) || CHR(10) ||  ' STORAGE ('||CHR(10)||
        ' initial ' ||
TO_CHAR(DECODE(SIGN(1048576-initial_extent),1,initial_extent,next_extent)) ||

              ' next ' || TO_CHAR(next_extent) ||
        ' maxextents ' || TO_CHAR(max_extents) ||
       ' pctincrease ' || TO_CHAR(pct_increase) ||

 CHR(10)|| ');'

 FROM sys.dba_indexes di
 WHERE di.owner = upper('$OWNER')
   AND di.index_name = upper('$INDEX');

QUIT;
END_OF_SCRIPT

---

#!/bin/ksh
#
# $Id: define_indexes,v 1.3 1995/04/22 08:31:36 vtl Exp $
#
# Comments-to: tapio.luukkanen_at_vtt.fi

. /usr/local/kassu/runtime/bin/change_sid

myarguments="$@"

if test -z "$1" ; then
cat <<EOF
usage: define_indexes [sid_selection] [-no_owner] OWNER

       define_indexes [sid_selection] [-no_owner] OWNER.TABLE EOF
exit 1
fi

no_owner=""
case "$1" in -no_owner) no_owner="$1"; shift ;; esac

owner="$1"; shift

case "$owner" in *.*)
  condition="and table_name = upper('${owner##?*.}')"   owner=${owner%%.?*}
esac

case "$1" in AND*|and*) condition="$1"; shift ; esac

tmp=`mktemp -p tables_columns.`

trap "rm -f $tmp" 0 1 2 3 15

sqlplus -s <<FOO_BAR_BAZ | sed 's:OPS.:OPS\\$:g' > $tmp /
set heading off;
set pagesize 0;
set linesize 79;
set showmode off;
set feedback off;
set verify off;

SELECT DISTINCT 'define_index $no_owner $owner ' || index_name ||' $@'   FROM sys.dba_indexes
 WHERE owner = upper('$owner')

       $condition;
FOO_BAR_BAZ echo "

/*
 * index definitions for user $owner's indexes.
 * created at "`date`"
 * with 'define_indexes $myarguments'
 * for database ORACLE_SID = $ORACLE_SID 
 *              TWO_TASK = $TWO_TASK
 */

"

. $tmp

---

Received on Thu May 22 1997 - 00:00:00 CDT