Category Archives: Oracle

Blog Shmog – Cut and Paste at your peril

The Internet is a great thing but when a top Oracle site posts a method for doing something that ain’t quite right you are going to be able to see a cut and paste job when you see it.  I was speaking with a friend last week who received some expensivly produced documents and with a simple google she found internet cut and paste.  Cut and paste is no substitute for actually knowing what you are doing.

Therefore – to go off at a slight angle and with my own cut and paste but with corrections…

If you want to autostart Oracle on AIX – easy.

1. Create the script /etc/rc/oracle and make it executable… chmod +x /etc/rc.oracle

su – oracle <<EOF
<$ORACLE_HOME>/bin/dbstart
EOF

2. Add the script to the inittab using the mkitab utility.
$ /usr/sbin/mkitab “rcoracle:2:wait:/etc/rc.oracle >/dev/console 2>&1”

All references to <$ORACLE_HOME> should be replaced with the actual Oracle Home directory. Now upon system startup, the dbstart utility is invoked at run level 2.

Check your /etc/oratab and they will have entries like:

In the file the entries will be like :

ORACLE_SID:ORACLE_HOME:AUTOSTART(Y or N)

Eg as below :
MYDBINSTANCE:/u001/app/oracle/10.2.0/db_1:Y

I hope this saves you a bit of time…

 

 

 

Oracle Triggers

I have been working on this – I hope someone can point me in the right direction.

 My application has multiple schema’s – that can be helped – all in the same instance.  Each schema will generate buckets of info.  I want to be able to create a single audit table – and capture some information from each of the schema’s into a separate table. But I cant do it – the only way IO can see to do it is by having a remote database.

For example:

if I have two schema’s s1 and s2 and in those schema’s I have a table called table_trail – same layout.  I put a trigger on each table which should update a single audit table in audit_table in schema s3 – say.

Ok, so I have a trigger for each table:

CREATE OR REPLACE TRIGGER atrigger  after insert on table_trail  REFERENCING OLD AS old NEW AS new  FOR EACH ROW begininsert into s3.audit_table values(new.col1, new.col2);

end;

basically doesn’t work on a compile because the trigger cannot see s3.audit_table. 

Why is this ?  I though if various rights were granted then it should be OK ? But I cant get it to work.

I have been able to get the remote database link working ok – so audit_table@remote compiles fine and that works.