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

Soms moet je in een Oracle database een numerieke of alfanumerieke waarde zoeken. Ik heb een PL/SQL script gebouwd om dat in een Oracle database te doen.

Je kunt met dit script één database-schema per keer doorzoeken. Het script houdt rekening met het verschil tussen numerieke en alfanumerieke waarden, dus als een varchar2 kolom een numerieke waarde bevat, heb je pech. Dit zal natuurlijk nooit gebeuren bij een goed doordacht datamodel 😉 .

Je kunt het script downloaden als zip bestand: Database search script

Dit is het 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
declare

vg_stime pls_integer default dbms_utility.get_time;

— Check if column is numeric
function fg_is_number (i_string varchar2) return boolean
is
vl_dummy pls_integer;
begin
vl_dummy := to_number(i_string);
return true;
exception
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
)
as
type cursor_type is ref cursor;
rl_cnt cursor_type;
vl_cnt pls_integer;
begin
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;
case
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)
is

vl_dtp varchar2(16) := ‘VARCHAR2_TABLE’;

cursor cl_tab( i_schema in varchar2, i_data_type in varchar2)
is
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);

begin

— 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)
then
vl_data_type := ‘NUMBER’;
dbms_output.put_line(‘searching for number ‘||i_string||’ in database schema ‘||i_schema||chr(10));
else
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;

begin

— 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…’ );
end;
/




undefine i_schema;
undefine i_string;