Oracle: problem relinking binaries on Solaris

I’ve got the following message relinking the binaries on Solaris:

ld: fatal: dlopen() of support library (libmakestate.so.1) failed with error:

ld.so.1: /usr/ccs/bin/sparcv9/ld: fatal: /usr/lib/libmakestate.so.1: wrong ELF class: ELFCLASS32

The problem was caused by missing 64-bit library libmakestate.so.1 in /usr/lib.

The normal library should look like the following:

[#] file /usr/lib/sparcv9/libmakestate.so.1
libmakestate.so.1: ELF 64-bit MSB dynamic lib SPARCV9 Version 1, dynamically linked, not stripped


If the library does not exist or is incorrect, it should be reinstalled from the package SUNWsprox ( Sun WorkShop Bundled 64-bit make library)

This command checks, if the package is installed in the system:
[#]  pkginfo -i SUNWsprox

After installing the package relink works fine.

Oracle: eval function

The function takes some expression as the argument and executes it, returning output in the varchar string


create or replace function eval (expr varchar2) return varchar2
as
ret varchar2(4000);
begin
execute immediate 'begin :result := ' || expr || '; end;' using out ret;
return ret;
end;
/

The discussion and examples could be found there

[amazon-product]1590599683[/amazon-product]

Oracle: list of the system events

This piece of code show the list of events, set in the Oracle database


set serveroutput on
declare
event_level number;
begin
dbms_output.enable(20000) ;
for i in 10000..33999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||
to_char(event_level));
end if;
end loop;
end;

Oracle: wait statisics by block class

This query helps to get information about the wait statistics (in fact, this query is contained in GV_$WAITSTAT)


SELECT
inst_id,
DECODE (indx,
1,'data block',
2,'sort block',
3,'save undo block',
4,'segment header',
5,'save undo header',
6,'free list',
7,'extent map',
8,'1st level bmb',
9,'2nd level bmb',
10,'3rd level bmb',
11,'bitmap block',
12,'bitmap index block',
13,'file header block',
14,'unused',
15,'system undo header',
16,'system undo block',
17,'undo header',
18,'undo block'
), count, time
FROM x$kcbwait
WHERE indx != 0;

Oracle: check the existence of logon/logoff triggers


select
decode((count(trigger_name)),0,'LOGON trigger missing',
'Number of logon triggers: ' || count(trigger_name) ) "INFO"
from sys.dba_triggers
where
TRIGGERING_EVENT like 'LOGON%'
and status='ENABLED'
and owner='SYS'
union
select
decode((count(trigger_name)),0,'LOGOFF trigger missing','',
'Number of logoff triggers:' || count(trigger_name)) "INFO"
from sys.dba_triggers
where
TRIGGERING_EVENT like 'LOGOFF%'
and status='ENABLED'
and owner='SYS'

The field TRIGGERING_EVENT could have the spaces at the end! Very clever…

Oracle: usage of the tablespaces


set pagesize 10000

set COLSEP '|'
set VERIFY off
set serveroutput on size 1000000
BREAK ON report

column tablespace_name format a30 heading 'TABLESPACE'
column sizegb format 9999999999D9 heading 'SIZE-Gb'
column usedproc format 999D99 heading 'USED-%'
column status format a10 heading 'STATUS'
COMPUTE sum LABEL 'Total size:' of sizegb ON report

SELECT b.tablespace_name ,
b.bytes/1024/1024/1024 as sizegb ,
NVL(100-((a.bytes/b.bytes)*100), 100) usedproc,
replace(c.status,' ','_') status
FROM
( select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name
) a ,
( select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name
) b ,
dba_tablespaces c
WHERE b.tablespace_name = a.tablespace_name (+)
AND b.tablespace_name = c.tablespace_name
union
select f.TABLESPACE_NAME,
f.TOTAL_MB/1024 sizegb,
nvl( (u.USED_MB/f.TOTAL_MB)*100, 0 ) usedproc,
'TEMPORARY' status
from
(
select f1.TABLESPACE_NAME,sum( f1.BYTES/1024/1024 ) TOTAL_MB
from (
select TABLESPACE_NAME,BYTES
from dba_temp_files
union all
select TABLESPACE_NAME,BYTES
from dba_data_files
where TABLESPACE_NAME in (
select TABLESPACE_NAME
from dba_tablespaces
where CONTENTS='TEMPORARY'
)
) f1
group by f1.TABLESPACE_NAME
) f,
(
select u1.TABLESPACE,
sum(u1.blocks) * max((select value from v$parameter where name='db_block_size')/1024/1024) USED_MB
from v$sort_usage u1
group by u1.TABLESPACE
) u
where f.TABLESPACE_NAME = u.tablespace (+)
ORDER BY 1;