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

In een Oracle database kunnen objecten soms de status “INVALID” krijgen. Dit kan als oorzaak hebben dat (geneste) afhankelijkheden niet meer beschikbaar zijn.

Een manier om de invalid objects opnieuw te compileren, is om het volgende script te gebruiken. Downloaden kan ook: recompile.zip

Hier is de code:

-- ----------------------------------------------------------
--  OS         : any
--  RDBMS      : Oracle 9i, 10g, 11g
--  Created by : Rene Zijlstra
--  Created on : 14/03/2007
--  Usage      : @[path]recompile[.sql]
--  Version    : 0.3
--  File       : recompile.sql
-- ----------------------------------------------------------
--  Parameters :
--  none
-- ----------------------------------------------------------
--  Remarks:
--  Extremely difficult way to do the same as
--    EXEC DBMS_UTILITY.compile_schema(schema => '<schema>');
-- ----------------------------------------------------------
-- --  Almost as good as Dutch apple-pie                   --
--

cl scr
set feedback off
set serveroutput on
exec   dbms_output.put_line('Gathering info...');
declare
vg_p_cnt        binary_integer;  -- previous number of invalid objects
vg_n_cnt        binary_integer;  -- new number of invalid objects
vg_compile_stmt varchar2(256);   -- the generated compile statement
vg_schema_stmt  varchar2(64);    -- usef for set current schema

-- Just a rough counter
cursor cg_cnt
is
select count(1)
from   sys.dba_objects
where  status != 'VALID'
;

-- cursor with dependencies
cursor cg_obj
is
select object_type
,      owner
,      object_name
from  sys.dba_objects dob
, (select max(level)  dlevel
,       object_id
from public_dependency dep
start with object_id in ( select object_id
from dba_objects dob
where dob.status != 'VALID'
)
connect by object_id = prior referenced_object_id
group by object_id
) lev
where dob.object_id = lev.object_id (+)
and dob.status != 'VALID'
order by lev.dlevel desc
, dob.object_name asc
, case owner
when 'SYS'    then 1
when 'SYSTEM' then 2
else 3
end
, owner
;

procedure pg_report(i_timing in varchar2)
as
vl_ordcol sys.dba_objects.owner%type;
vl_owner  sys.dba_objects.owner%type;
vl_type   sys.dba_objects.object_type%type;
vl_cnt    binary_integer;

cursor cl_obj
is
select  case owner
when 'SYS'    then 1
when 'SYSTEM' then 2
else 3
end case
,  owner
,  object_type   obj
,  count(*)      count
from     sys.dba_objects
where    status = 'INVALID'
group by object_type
,        owner
order by
1
, 2
, case object_type
when 'TYPE'         then 10
when 'TYPE BODY'    then 11
when 'VIEW'         then 20
when 'PROCEDURE'    then 30
when 'FUNCTION'     then 40
when 'PACKAGE'      then 50
when 'PACKAGE BODY' then 51
when 'SNAPSHOT'     then 60
else  70
end
;

begin
case vg_n_cnt
when 0
then dbms_output.put_line (chr(10) || 'Invalid objects ' || i_timing || ' compiling:');
dbms_output.put_line (chr(10) ||'There are no errors' || chr(10) );
else
dbms_output.put_line (chr(10) || 'Invalid objects ' || i_timing || ' compiling:' || chr(10) || chr(10));
dbms_output.put_line ('  Object Owner Object Type    Invalid Objects');
dbms_output.put_line ('  ------------ -----------    ---------------');
end case;
open cl_obj;
loop
fetch cl_obj into vl_ordcol, vl_owner, vl_type, vl_cnt;
exit when cl_obj%notfound;
dbms_output.put_line (rpad(vl_owner,13) || rpad(vl_type,15) || rpad(vl_cnt,15));
end loop;
close cl_obj;
dbms_output.put_line(chr(10));
end pg_report;

procedure p_error( i_owner in  varchar2
, i_type  in  varchar2
, i_name  in  varchar2
)
as

vl_owner sys.dba_errors.owner%type;
vl_err   varchar2(255);   -- the error message of the invalid object

cursor cl_err( i_owner in varchar2
, i_type  in varchar2
, i_name  in varchar2
)
is
select decode(owner,'SYS'   ,'1'
,'SYSTEM','2'
,'3'
)        as ordcol
,      rpad(text,255) as text
from   dba_errors
where owner = i_owner
and   type  = i_type
and   name  = i_name
order by ordcol
;

begin

open  cl_err(i_owner, i_type, i_name);
fetch cl_err into vl_owner, vl_err;
close cl_err;

dbms_output.put_line(i_type || '  ' || i_owner || '.' || i_name);
dbms_output.put_line(vl_err);

end p_error;

begin
dbms_output.enable(1000000);
-- Show session information
dbms_application_info.set_module( 'recompile.sql','Gather info' );

pg_report('before');
loop
open  cg_cnt;
fetch cg_cnt into vg_n_cnt;
close cg_cnt;
exit when vg_p_cnt = vg_n_cnt;

vg_p_cnt := vg_n_cnt;

for rg_obj in cg_obj loop
-- Show session information
dbms_application_info.set_action( 'compile ' || rg_obj.object_type || ' ' || rg_obj.object_name );
-- Build the compile statement for the invalid object

vg_schema_stmt := 'alter session set current_schema='
||case rg_obj.owner
when 'PUBLIC' then 'SYSTEM'
else rg_obj.owner
end
;

vg_compile_stmt := 'alter '
|| case rg_obj.object_type
when 'PACKAGE BODY' then 'PACKAGE '
when 'TYPE BODY'    then 'TYPE '
when 'UNDEFINED'    then 'MATERIALIZED VIEW '
else rg_obj.object_type
end
|| ' "' || rg_obj.owner || '"."'
|| case rg_obj.object_type
when 'JAVA CLASS'  then ' DBMS_JAVA.LONGNAME"(' || rg_obj.object_name || ')" '
else rg_obj.object_name
end
|| case rg_obj.object_type
when 'JAVA CLASS' then ' resolve '
else '" compile '
end
|| case rg_obj.object_type
when 'PACKAGE BODY' then 'BODY '
when 'TYPE BODY'    then 'BODY '
else null
end
;

begin
-- set current schema (Some objects will only compile as owner...)
execute immediate (vg_schema_stmt);

-- Compile the invalid object
execute immediate (vg_compile_stmt);

exception
when others then null;
end;
end loop;
end loop;

-- Show session information
dbms_application_info.set_module( 'recompile.sql', 'Error report' );
-- Show report after compiling
pg_report('after');

-- Show errors on screen
case vg_n_cnt
when 0 then null;
else
dbms_application_info.set_action( 'Error information' );
dbms_output.put_line( 'Errors:' || chr(10) || '-------' );

for rg_obj in cg_obj loop
-- Lookup the error message and log them on screen
p_error(rg_obj.owner, rg_obj.object_type, rg_obj.object_name);
end loop;
end case;


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

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;