Posts Tagged ‘external table’
OERR for Windows
Yes, Oracle does not ship oerr with Windows version. But some smart guys struggled to solve the problem. Here you can download the windows oerr implemented with Perl.
But, I’ve lived a long life without this tool. I just built up my own oerr written in 100% SQL.
One very simple but not useful way is to use following code.
dbms_output.put_line(sqlerrm(-1555)) ;
But this generate only the simple message, without cause and action information.
ORA-01555: snapshot too old: rollback segment number with name "" too small
Following is how I built up my own oerr which is capable of showing the entire message.
1. Create directory where oraus.msg file lives(In this demo, I’m in Windows oracle database)
create or replace directory dir_oraus as 'C:\Documents and Settings\exem' ;
2. Create external table to which oraus.msg file is loaded.
create table ext_oraus( line varchar2(4000) ) organization external ( default directory dir_oraus access parameters ( records delimited by newline fields terminated by '~' ) location ('oraus.msg') ) reject limit unlimited ;
3. Create permanent table to store the contents of oraus.msg file and load the contents into that table.
create table t_oraus (
err_code varchar2(5),
message varchar2(1000),
texts clob
);
declare
v_err_code varchar2(4000);
v_message varchar2(4000);
v_texts clob;
begin
for r in (select line from ext_oraus) loop
if substr(r.line, 6, 1) = ',' then
-- record new data
if v_err_code is not null then
insert into t_oraus(err_code, message, texts)
values(v_err_code, v_message, v_texts);
end if;
v_err_code := substr(r.line, 1, 5);
v_message := substr(r.line, 14);
v_texts := '';
elsif substr(r.line, 1, 2) = '//' then
v_texts := v_texts || r.line || chr(13)|| chr(10);
end if;
end loop;
-- last record
insert into t_oraus(err_code, message, texts)
values(v_err_code, v_message, v_texts);
commit;
end;
/
create index t_oraus_n1 on t_oraus(err_code);
4. Now we can do oerr just by selecting corresponding data from the table t_oraus
select err_code, texts
from t_oraus
where err_code = '01555'
; ERR_C TEXTS ----- ------------------------------------------------------------------------- 01555 // *Cause: rollback records needed by a reader for consistent read are // overwritten by other writers // *Action: If in Automatic Undo Management mode, increase undo_retention // setting. Otherwise, use larger rollback segments
5. The fact that the whole data is stored in RDBMS gives me the full flexibility, like
ed oerr.sql
select err_code, texts from t_oraus where err_code = lpad(to_char(&1), 5, '0') ;
@oerr 1555
ERR_C TEXTS ----- -------------------------------------------------------------------------- 01555 // *Cause: rollback records needed by a reader for consistent read are // overwritten by other writers // *Action: If in Automatic Undo Management mode, increase undo_retention // setting. Otherwise, use larger rollback segments
Or
select err_code, texts from t_oraus where err_code like '0403%' ;
ERR_C TEXTS ----- ---------------------------------------------------------------------------- 04030 // *Cause: Operating system process private memory has been exhausted // *Action: 04031 // *Cause: More shared memory is needed than was allocated in the shared // pool. // *Action: If the shared pool is out of memory, either use the // dbms_shared_pool package to pin large packages, // reduce your use of shared memory, or increase the amount of // available shared memory by increasing the value of the // INIT.ORA parameters "shared_pool_reserved_size" and // "shared_pool_size". // If the large pool is out of memory, increase the INIT.ORA // parameter "large_pool_size". 04032 // *Cause: attempt to set workarea_size_policy to AUTO while // pga_aggregate_target is not specified // *Action: before setting workarea_size_policy, set pga_aggregate_target // to a value representing the total private memory available to // the instance. This total is generally the total physical memory // available in the system minus what is needed for the SGA minus // what is needed for the operating system (e.g. 500MB) 04033 // *Cause: The system had insufficient memory to grow the pool to the // specified size. // *Action: Specify a smaller value to grow the pool. 04034 // *Cause: The pool could not shrink to the specified size as it could not // free memory. // *Action: Specify a larger value to which to shrink the pool to.
Very flexible, isn’t it?
I’m just still curios why Oracle doesn’t ship oerr for Windows. :)