Renetta.nl

In een Oracle database worden indexen opgeslagen een tablespace. Soms wil je deze fysiek verplaatsen naar een andere tablespace, bijvoorbeeld om te defragmenteren, te verplaatsen naar een ander filesystem of schijf of als je indexen de status “UNUSABLE” hebben (select owner, index_name, status from dba_indexes.

Gebruik hiervoor het volgende script (download hier: move_indexes.zip )

/* ----------------------------------------------------------
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
----------------------------------------------------------
Remarks:
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);

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

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

declare
—
— 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
)
is
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( idx.degree
, 0, ‘noparallel’
, 1, ‘noparallel’
, ‘parallel ‘||idx.degree
) 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
as
cursor c_usr (i_schema in varchar2)
is
select null
from dba_users usr
where usr.username = i_schema
;
begin
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
as
cursor c_tbs (i_tablespace_name in varchar2)
is
select null
from dba_tablespaces tbs
where tbs.tablespace_name = i_tablespace_name
;
begin
if p_tablespace_name is null
then
return true;
end if;
for r_tbs in c_tbs(p_tablespace_name) loop
return true;
end loop;
return false;
end tablespace_exists;

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

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

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

— check presence of tablespace
if not tablespace_exists(p_tablespace_name)
then
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!
begin
dbms_output.put_line(substr(vg_rebuildindex_stmt, 1,254) || ‘;’); — enable to debug
execute immediate (vg_rebuildindex_stmt);
exception
when others
then
dbms_output.put_line(‘rebuild of index failed:’);
dbms_output.put_line(‘in statement’);
dbms_output.put_line(substr(vg_rebuildindex_stmt, 1, 255));
dbms_output.put_line(sqlerrm);
end;

end loop;

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

exception
when eg_schema_not_found
then
dbms_output.put_line(cg_nl || ‘ERROR: Database Schema does not exist’);
when eg_tablespace_not_found
then
dbms_output.put_line(cg_nl || ‘ERROR: Tablespace does not exist!’);
when eg_no_schema_specified
then
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
then
dbms_output.put_line(‘error code: ‘||sqlcode|| cg_nl || sqlerrm);
end;
.

spool rebuild.log
/
spool off
set linesize 80

undefine p_schema
undefine p_tablespace
undefine 1
undefine 2