How to set an Oracle DB 10g to start automatically

Login to where the Oracle DB is installed and vi as root

[bash]vi /etc/oratab[/bash]

Edit the file, find and set the lines as follows:

[text]orcl:/u01/app/oracle/product/10.2.0/db_1:N[/text]
vi cmd to type Insert key
[text]orcl:/u01/app/oracle/product/10.2.0/db_1:Y[/text]
vi cmd Esc to exit editing
vi cmd to write/save and exit :wq

Edit dbora, which is mainly to set the ORA home and owner and execute the listener to start or stop

[bash]vi /etc/init.d/dbora #file will be created if it does not exist[/bash]

Edit the file and enter the following as specific to your ORA environment vars:

[text]#!/bin/sh
# description: Oracle auto start-stop script.
# # Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
# # Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME PATH

ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ] then
echo “Oracle startup: cannot start”
exit fi

case “$1” in
‘start’)
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su $ORA_OWNER -c “$ORA_HOME/bin/dbstart $ORA_HOME” ;
;

‘stop’)
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su $ORA_OWNER -c “$ORA_HOME/bin/dbshut $ORA_HOME” ;
;
esac[/text]

Set the appropriate privileges to the file:
[bash]chmod 750 /etc/init.d/dbora[/bash]

Notes:
May need to set in /u01/app/oracle/product/10.2.0/db_1/bin/dbstart
[text]ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle/bin/tnslsnr
to:
ORACLE_HOME_LISTNER=$ORACLE_HOME[/text]

…in some cases where the listener won’t start.

How to find the ORACLE_HOME path for Oracle DB 10g

Login to the server which you have the DB installed on
[bash]sqlplus /nolog #login to SQL*Plus console[/bash]
From SQL*Plus console:
[sql]conn user/pass as sysdba
var ohp varchar2(100);  –set a variable ‘ohp’
exec dbms_system.get_env(‘ORACLE_HOME’, &#58ohp) ; –call the get_env system-procedure
print ohp; –prinout the result[/sql]

You should then see an output print of the ORACLE_HOME path for the DB installed:

[sql]/u01/app/oracle/product/10.2.0/db_1[/sql]

 

how to create a cursor to read through table rows

[sql]declare
  counter1 number := 0;
  counter2 number := 0;
  cursor cur_t is
    SELECT * from emp where e_name = 'Ptere'; --set cursor, a select statement
  rec_t emp%rowtype; --defines this is a row from the database

begin
  counter1 := 0;
  counter2 := 0; --some counters to log the records being changed
  open cur_t; --open cursor

  loop
    --begin loop
  
    fetch cur_t
      into rec_t; --fetches cursor row into the rec_t variable
  
    exit when cur_t%notfound;
    UPDATE emp SET e_name = 'Peter' where e_name = 'Ptere';
    counter1 := counter1 + 1; --increment updates
    insert into update_log
      (record_id, table_name, description) --useful for ref. purposes
    values
      (rec_t.emp_id, 'emp', 'Peter');
    counter2 := counter2 + 1; --increment inserts
    commit;
  end loop;
  --row_count := sql%rowcount; --sql counter for update statements
  dbms_output.put_line('EMP: ' || 'name changed, updated: ' || counter1);
  dbms_output.put_line('insert into UPDATE_LOG: ' || counter2);
  close cur_t; --close the cursor
end;
[/sql]

Note for DBMS output we need to set the command prompt:[sql]set serveroutput on size 1000000 [/sql]format wrapped

how to find specific text in oracle schema using a procedure and save to table

The following function will find any text matching the variable set within the procedure.
(the output will display on screen -dbms_output)

To execute: <SQL> [sql]execute update_found_records_proc;[/sql]

We could also add a parameter on calling the procedure as:
CREATE OR REPLACE PROCEDURE FIND_IN_RECORDS_PROC(val IN varchar2) IS…
note: ‘val’ is set once and used anywhere later in the code

Credit:: Rene Nyffenneger on Oracle

PL/SQL code: (more…)