Move indexes

In an Oracle database indexes are stored in tablespace. Sometimes you want to physically move them to another tablespace, i.e. to defragment, move to another filesystem or spindle or if indexes have status “UNUSABLE” (select owner, index_name, status from dba_indexes.

To resolve this, you can use the following script (download here: )

/* ----------------------------------------------------------
OS : any (pure PL/SQL)
DBMS : ORACLE Versions 8i and up
Created by : Rene Zijlstra
Created on : 12/5/2002
Usage : Rebuild all indexes for a schema
Version : 1.6
Parameters :
&1 = schema owner
&2 = tablespace to store index (optional)
Examples :
@@rebuild_all_indexes rene idx
Increasing sort_area_size speeds up process considerably
Rev User revision Date comment
--- ------------- ------------- -------------------
1.0 Rene Zijlstra 05 Dec 2002 creation
1.1 Rene Zijlstra 06 Dec 2002 added schema
1.2 Rene Zijlstra 10 Dec 2002 added tablespace
1.3 Rene Zijlstra 13 Dec 2002 repaired next_extent
1.4 Rene Zijlstra 02 Feb 2003 repaired tablespace
1.5 Rene Zijlstra 04 Feb 2003 Version independend
1.6 Rene Zijlstra 07 Feb 2003 added additional values
-- Almost as good as Dutch apple-pie --

— added to tune this script. Enable if necessary
–alter session set sort_area_size = 41943040;

variable p_schema varchar2(16);
variable p_tablespace varchar2(16);

:p_schema := ‘SCHEMA_NAME’; — Owner of indexes
:p_tablespace := ‘INDEX_TS’; — Tablespace_name where the indexes need to be stored

clear screen
set serveroutput on size 1000000;
set verify off
set linesize 250


— constants

cg_nl constant varchar2(2) := chr(13)||chr(10); — new line

— parameters

p_schema varchar2(30) := :p_schema;
p_tablespace_name varchar2(30) := :p_tablespace;

— global variables

vg_rebuildindex_stmt varchar2(250);
vg_coalesce_stmt varchar2(250);

— exceptions

eg_no_schema_specified exception;
eg_schema_not_found exception;
eg_tablespace_not_found exception;

— retrieve information to build script
cursor c_idx ( i_schema in varchar2
, i_tablespace in varchar2
select idx.owner
, idx.index_name
, nvl( i_tablespace, idx.tablespace_name) tablespace_name
, decode( idx.initial_extent
, to_number(null), ”
, ‘initial ‘ || to_char(idx.initial_extent)
) initial_extent
, decode( idx.next_extent
, to_number(null), ”
, ‘next ‘ || to_char(idx.next_extent)
) next_extent
, decode( idx.pct_increase
, to_number(null), ”
— Disabled, because we do not want this anymore!!!
— , ‘pctincrease ‘ || to_char(idx.pct_increase)
, ‘pctincrease 0’
) pct_increase
, decode( idx.logging
, ‘yes’, ‘logging’
) logging
, decode(
, 0, ‘noparallel’
, 1, ‘noparallel’
, ‘parallel ‘||
) parallel_mode
from dba_indexes idx
where idx.owner = i_schema
and idx.index_type in (‘NORMAL’, ‘FUNCTION-BASED NORMAL’)
and idx.tablespace_name != i_tablespace

— schema_exists

— verify presence of schema

function schema_exists (p_schema_name in varchar2) return boolean
cursor c_usr (i_schema in varchar2)
select null
from dba_users usr
where usr.username = i_schema
for r_usr in c_usr(p_schema) loop
return true;
end loop;
return false;
end schema_exists;

— tablespace_exists

— verify presence of tablespace if tablespace_name is specified.

function tablespace_exists (p_tablespace_name in varchar2) return boolean
cursor c_tbs (i_tablespace_name in varchar2)
select null
from dba_tablespaces tbs
where tbs.tablespace_name = i_tablespace_name
if p_tablespace_name is null
return true;
end if;
for r_tbs in c_tbs(p_tablespace_name) loop
return true;
end loop;
return false;
end tablespace_exists;

— Show session information
dbms_application_info.set_module( ‘rebuild_all_indexes.sql’,’Rebuilding indexes’ );

— check if schema is specified
if p_schema is null
raise eg_no_schema_specified;
end if;

— check presence of schema
if not schema_exists(p_schema)
raise eg_schema_not_found;
end if;

— check presence of tablespace
if not tablespace_exists(p_tablespace_name)
raise eg_tablespace_not_found;
end if;

— loop through the retrieved indexes and create the statements
for r_idx in c_idx(p_schema, p_tablespace_name) loop

— Show session information
dbms_application_info.set_action( r_idx.index_name );

— build the script to rebuild the index
vg_rebuildindex_stmt := ‘alter index “‘||p_schema||'”.”‘||r_idx.index_name||'” rebuild online ‘ || cg_nl
|| nvl(r_idx.logging, ‘nologging’) || cg_nl
|| r_idx.parallel_mode || cg_nl
|| ‘tablespace “‘||r_idx.tablespace_name||'”‘ || cg_nl

— rebuild the index. Do it now!
dbms_output.put_line(substr(vg_rebuildindex_stmt, 1,254) || ‘;’); — enable to debug
execute immediate (vg_rebuildindex_stmt);
when others
dbms_output.put_line(‘rebuild of index failed:’);
dbms_output.put_line(‘in statement’);
dbms_output.put_line(substr(vg_rebuildindex_stmt, 1, 255));

end loop;

— Reset session information
dbms_application_info.set_module( null, null );

when eg_schema_not_found
dbms_output.put_line(cg_nl || ‘ERROR: Database Schema does not exist’);
when eg_tablespace_not_found
dbms_output.put_line(cg_nl || ‘ERROR: Tablespace does not exist!’);
when eg_no_schema_specified
dbms_output.put_line(cg_nl || ‘ERROR: No schema specified.’);
dbms_output.put_line(‘SYNTAX: @@rebuild_all_indexes
dbms_output.put_line(‘or @@rebuild_all_indexes “”‘);

when others
dbms_output.put_line(‘error code: ‘||sqlcode|| cg_nl || sqlerrm);

spool rebuild.log
spool off
set linesize 80

undefine p_schema
undefine p_tablespace
undefine 1
undefine 2

Database search

Sometimes you need to search an Oracle database for a numeric or an alphanumeric value. I made a Pl / SQL script for Oracle to do that.

You can use this script to search one database schema at the time. The script understands difference between numeric and alphanumeric values, so if a VARCHAR2 column contains a numeric value, you don’t have any luck. This of course will never happen in a well thought out data model 😉
You can download the script as a zip file: Database search script

-- ----------------------------------------------------------
-- OS : any
-- RDBMS : Oracle 9i+
-- Created by : Rene Zijlstra
-- Created on : 2004/11/29
-- Usage : Searh a string in all tables
-- Version : 1.1
-- File : search.sql
-- ----------------------------------------------------------
-- Parameters :
-- none, the input paramaters are prompted for
-- ----------------------------------------------------------
-- Remarks:
-- You will be prompted for database schema and string to search
-- Added dbms_application_info, so progress can be viewed in v$session
-- ----------------------------------------------------------
-- Rev User revision date comment
-- --- ------------- ------------- -------------------
-- 0.1 Rene Zijlstra 2004/11/09 creation
-- 1.0 Rene Zijlstra 2006/01/04 Modified and tuned (totally rewritten)
-- 1.1 Rene Zijlstra 2008/04/06 Removed views from cursor + added dbms_application_info
-- 1.2 Rene Zijlstra 2009/12/02 Added support for lowercase tables an columns (Why do people want that!?)
-- ----------------------------------------------------------
-- Almost as good as Dutch apple-pie --

-- Set the following to speed up a bit
alter session set sort_area_size=20971520;

set serveroutput on size 100000;
set verify off;

accept i_schema prompt "Enter database schema to search in: "
accept i_string prompt "Enter Text string to search for: "

-- Multiple rows fetch
-- Get all tables with CHAR-like columns

vg_stime pls_integer default dbms_utility.get_time;

-- Check if column is numeric
function fg_is_number (i_string varchar2) return boolean
vl_dummy pls_integer;
vl_dummy := to_number(i_string);
return true;
when others then
return false;
end fg_is_number;

procedure pg_search_table( i_schema in varchar2
, i_table in varchar2
, i_column in varchar2
, i_string in varchar2
type cursor_type is ref cursor;
rl_cnt cursor_type;
vl_cnt pls_integer;
open rl_cnt for 'select count(*) from "'||i_schema||'"."'||i_table||'" where lower("'||i_column||'") like :b3'
using i_string;
fetch rl_cnt into vl_cnt;
when (vl_cnt > 0) then dbms_output.put_line (i_schema||'.'||i_table||'.'||i_column|| ': ' || vl_cnt);
else null;
end case;
close rl_cnt;
end pg_search_table;

procedure pg_search_schema( i_schema in varchar2, i_string in varchar2)

vl_dtp varchar2(16) := 'VARCHAR2_TABLE';

cursor cl_tab( i_schema in varchar2, i_data_type in varchar2)
select tcs.table_name
, tcs.column_name
from dba_tab_columns tcs
where data_type like i_data_type
and data_type != vl_dtp
and owner = i_schema
and not exists ( select null
from dba_views vws
where vws.view_name = tcs.table_name
and vws.owner = tcs.owner
order by table_name
, column_name

type tl_tabnm is table of all_tab_columns.table_name%type;
type tl_colnm is table of all_tab_columns.column_name%type;
vl_tables tl_tabnm;
vl_columns tl_colnm;
vl_data_type varchar2(20);


-- If the search-string is a numeric value search in NUMBER and VARCHAR2 columns
-- Else search in all CHAR-like columns
if fg_is_number(i_string)
vl_data_type := 'NUMBER';
dbms_output.put_line('searching for number '||i_string||' in database schema '||i_schema||chr(10));
vl_data_type := '%CHAR%';
dbms_output.put_line('searching for string "'||i_string||'" in schema '||i_schema||chr(10));
end if;

open cl_tab(i_schema, vl_data_type);
fetch cl_tab
bulk collect into vl_tables, vl_columns;
for rl_tab in 1..cl_tab%rowcount loop
dbms_application_info.set_action( 'searching ' || vl_tables(rl_tab) || '.' || vl_columns(rl_tab) );
pg_search_table(i_schema, vl_tables(rl_tab), vl_columns(rl_tab), i_string );
end loop;
close cl_tab;
end pg_search_schema;


-- Tell to uncle v$session what you are doing
dbms_application_info.set_module( 'search.sql','searching for "&&i_string"' );
-- dbms_lock.sleep(5);
pg_search_schema(upper('&&i_schema'), lower('&&i_string'));

-- Reset session information
dbms_application_info.set_module( null, null );

dbms_output.put_line('Search time: '||round((dbms_utility.get_time-vg_stime)/100, 2) ||' Seconds...' );

undefine i_schema;
undefine i_string;

Theme: Blogging Themes