Tag Archives: Oracle

Quick calculation of the row count

Posted on by
Reply

Warning! It depends on the statistics and could be not accurate!

Sybase

select user_name( obj.uid), obj.name , rowcnt(doampg)
from sysindexes idx, sysobjects obj
where idx.indid < 2 and idx.id = obj.id and obj.type = 'U'
order by 1,2
go

select object_name( idx.id ), rowcnt(doampg)
from sysindexes idx
where idx.indid < 2
and object_name( idx.id ) in ('object1','object2')
go

Oracle

select OWNER, TABLE_NAME, NUM_ROWS
from dba_tables
where TABLE_NAME = upper( '&what_tbl.' );

select OWNER, TABLE_NAME, NUM_ROWS
from dba_tables
order by 1,2;

Usage of the temp tablespace in Oracle

Posted on by
Reply

First find the block size:


select value "BLOCK_SIZE"
from v$parameter
where name='db_block_size' ;

Now check the usage of the tablespace:

select
s.username "USER",s.sid,s.osuser,
u.tablespace "TS" ,
sum(u.blocks) * &BLOCK_SIZE./1024/1024 MB,
x.sql_text
from v$session s,v$sort_usage u,v$sqltext x
where s.saddr=u.session_addr
and s.sql_address=x.address
group by
s.sid, s.username, osuser,
tablespace, sql_text, address, piece
order by sid, piece asc;

Invalid objects in Oracle DB (health check)

Posted on by
Reply

Some standard queries to check the state of the Oracle DB

select 'alter '
|| decode(lower(object_type), 'package body','package',lower(object_type))
|| ' ' || owner || '.'
|| object_name
|| decode(object_type,'PACKAGE BODY', ' compile body;',' compile;') INVALID_OBJECTS
from DBA_OBJECTS
where STATUS = 'INVALID';

select 'ALTER INDEX '|| owner || '.' || index_name || ' rebuild online ;'
from dba_indexes where status = 'UNUSABLE'
union
select 'ALTER INDEX ' || index_owner || '.' || index_name
|| ' rebuild partition ' || partition_name || ' online ;'
from dba_ind_partitions
where status = 'UNUSABLE'
union
select 'ALTER INDEX ' || index_owner || '.' || index_name
|| ' rebuild subpartition ' || subpartition_name || ' online ;'
from dba_ind_subpartitions
where status = 'UNUSABLE';

SELECT name, unrecoverable_change# , unrecoverable_time
FROM v$datafile
order by 2;

Search for Oracle errors in the alert log

Posted on by
Reply

Search for Oracle errors in the last $NUM lines.


NUM=1000
SIGNAL_LIST='^(...-|Error|Starting.*instance)|terminating instance'

tail -$NUM alert_${ORACLE_SID}.log | awk '
BEGIN {prev="" ; ret=1 }
/'"$SIGNAL_LIST"'/ { if ( prev !~ /'"$SIGNAL_LIST"'/ ) { print "" ; print prev;} print $0;ret=0}
{prev=$0}
END { exit ret } '

On Unix Oracle alert log is normally located in bdump/alert_{ORACLE_SID}.log

Manupulating Oracle LDAP entries

Posted on by
Reply

Examples for changing the Oracle LDAP entries.
Warning!
Put the space after every ldif command!
Check the version of ldapmodify (it should be Oracle binary, not the OS standard one)

#### modify.sh ####

ldapmodify -D 'cn=orcladmin' -w $LDAP_PASS -h $LDAP_HOST -p $LDAP_PORT -v -f modify.ldif

#### modify.ldif ####
dn: cn=SOME_ORACLE_SID,cn=OracleContext,dc=ny,dc=company,dc=com
changetype: modify
replace:orclnetdescstring
orclnetdescstring: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=456.456.456.456)
(PORT=1521))(CONNECT_DATA=(SID=SOME_ORACLE_SID) (GLOBAL_DBNAME=SOME_ORACLE_SID.ny.company.com)))

#### add.sh ####

ldapmodify -D 'cn=orcladmin' -w $LDAP_PASS -h $LDAP_HOST -p $LDAP_PORT -v -f add.ldif

### add.ldif ###
dn: cn=SOME_ORACLE_SID,cn=OracleContext,dc=ny,dc=company,dc=com
orclnetdescstring: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=456.456.456.456)
(PORT=1521))(CONNECT_DATA=(SID=SOME_ORACLE_SID) (GLOBAL_DBNAME=SOME_ORACLE_SID.ny.company.com)))
objectclass: top
objectclass: orclNetService
cn: SOME_ORACLE_SID
orclnetdescname: 000:cn=DESCRIPTION_0

### unload.sh ###

#!/bin/ksh
if [ -z "$1" ] ; then
if [ -z "$CONTEXT" ] ; then
print "Examples of context: ny.company.com or dc=ny,dc=company,dc=com" >&2
printf "Enter context (Enter for default): " >&2
read CONTEXT
fi
CONTEXT=${CONTEXT:-"dc=world"}
else
CONTEXT=${1:-"dc=world"}
fi

case "$CONTEXT" in
"dc="* ) ;;
* ) CONTEXT=$( echo "${CONTEXT}" | sed 's#\([^.]*\)$#dc=\1#;s#\([^.]*\)\.#dc=\1,#g') ;;
esac

echo "# CONTEXT: $CONTEXT" >&2

ldapsearch -h $LDAP_HOST -p $LDAP_PORT -D "cn=orcladmin" -w $LDAP_PASS -L -s sub -b "cn=OracleContext,${CONTEXT}" "(objectclass=orclnetService)" "*"

Print current Oracle character set

Posted on by
Reply

Short PL/SQL block to print the characters in range [033..256]

set serveroutput on

declare
i number;
j number;
k number;
begin
for i in 2..15 loop
for j in 1..16 loop
k:=i*16+j;
dbms_output.put((to_char(k,'000')) || ':' || chr(k) || ' ' );
if k mod 8 = 0 then
dbms_output.put_line('');
end if;
end loop;
end loop;
end;

Start sqlplus in Windows .BAT script

Posted on by
Reply

Here is the example of emulating “HERE-documents” in Windows shell.


@ECHO OFF

FIND "/*%none% some_label" <%0 |sqlplus USER/PASS@SERVER GOTO end : ----------- embedded SQL------------------------- /* some_label */ select count(*) from dba_free_space; /* some_label */ select tablespace_name from dba_tablespaces; : ----------- end of embedded SQL------------------------- :end

It's also possible to use procedures in .bat files.
%none% is replaced by empty string.
Spaces are mandadory in "/* some_label */" string.

Another possibility:
[cc lang="dos"]
@(
echo select global_name from global_name ;
) | sqlplus USER/PASS@SERVER
[/cc]

[amazon-product]0735622620[/amazon-product]

Show hidden parameters in Oracle

Posted on by
Reply

Check the hidden underscore parameters…


col "Parameter" for a50
col "Session Value" for a15
col "Instance Value" for a15

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and lower(a.ksppinm) like lower('%&param%')
order by a.ksppinm;

Setting the parameter:

alter system set "&param." = value scope = &scope. ;

scope is one of the following: “both”, “memory”, “spfile”

To remove completely the hidden parameter from the spfile:

alter system reset "&param." scope=spfile SID='*';