Category Archives: Oracle

Oracle: find dependent objects

Posted on by
1

This query helps to find the objects, that depend from the specified one

select CONSTRAINT_NAME, OWNER,TABLE_NAME from DBA_CONSTRAINTS
where R_CONSTRAINT_NAME in
( select CONSTRAINT_NAME
from dba_constraints
where
OWNER=upper('&user_dependent_from') and
TABLE_NAME=upper('&object_dependent_from') )
and STATUS='ENABLED';

Example:

create table city ( city_id int ,
name char(10) ,
primary key ( city_id ) ) ;

create table country (
cou_id int,
name char(19),
city_id int
constraint ZZZ references city ( city_id ) on delete cascade,
primary key(cou_id) ) ;

insert into CITY values ( 1, 'Paris' ) ;
insert into CITY values ( 2, 'Berlin') ;

insert into COUNTRY values ( 101, 'France', 1 ) ;
insert into COUNTRY values ( 102, 'Germany', 2 );

Output:

Enter value for user_dependent_from: FRODO
Enter value for object_dependent_from: CITY

CONSTRAINT_NAME OWNER TABLE_NAME
ZZZ FRODO COUNTRY

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

There is also standard script from Oracle, which displays the dependencies: ${ORACLE_HOME}/rdbms/admin/utldtree.sql


execute deptree_fill('table', 'scott', 'emp');
select * from deptree order by seq#;

Write to the alert log from PL/SQL code

Posted on by
Reply

The following undocumented function could be used to write to the alert log:


execute sys.dbms_system.ksdwrt(code, message);

Parameters:

1 – Write to trace file.
2 – Write to alertlog.
3 – Write to both.

Example:

execute sys.dbms_system.ksdwrt(2,to_char(sysdate, 'Dy Mon DD HH24:MI:SS YYYY')||'Hello!' );


create or replace trigger trg_delme
BEFORE UPDATE on frodo.delme
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
sys.dbms_system.ksdwrt(2,to_char(sysdate, 'Dy Mon DD HH24:MI:SS YYYY') );
end;

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;