Oracle 10g for solaris 10
Oracle 10g for Solaris 10 I got error message “Checking operating system version: must be 5.8 or 5.9. Actual 5.10 Failed
Category: SQL scripts
Error Handling in Proc
There is a standard way of trapping any SQL or database errors in Pro*C.This is done using EXEC SQL WHENEVER SQLERROR DO ; just ensure that the above statement goes before any sql statements whenever an error is encountered , the control goes to the function function_name in the body of the function_name , just…
Proc code
1. EXEC SQL COMMIT WORK RELEASE; (When you want to commit transactions and disconnect;similar to EXIT in SQL*PLUS) 2. EXEC SQL ROLLBACK WORK RELEASE; (When you want to rollback transactions and disconnect;similar to QUIT in SQL*PLUS)
To see how much time or progress of long transaction
select * from v$session_longops where sid=100;
cp_filesystem.sql
exec dbms_output.enable(10000); set lines 130 pages 1000 set serveroutput on size 1000000 declare cursor c1 is select substr(name,1,61) complete_filename, bytes/(1024) KB, substr(name, instr(name, ‘/’, -1)+1) only_filename from v$datafile order by file#; threshold_kb number(10) := 94000000 ; size_counter number := 0; mountpoint_counter number := 1; mountpoint_prefix varchar2(10) := ‘/800t’; common_mountpoint_prefix varchar2(15) := ‘/oradata/800T/’; target_filename varchar2(61); begin…
Caching sequence in Memory
ALTER SEQUENCE CS.SESSION_ACTIVE_SEQ_ID CACHE 2000; exec sys.DBMS_SHARED_POOL.KEEP(‘CS.SESSION_ACTIVE_SEQ_ID ‘, ‘Q’) =>> To pin the sequence using the DMBS_SHARED_POOL.KEEP procedure (IF NEEDED) Potential drawbacks Setting a cache size larger then 0 can result in the loss of sequence values if the system is shutdown abruptly. When the system fails, the values that were preserved in memory are…
Oracle 11g Environment Setup
11g Environment Setting: ======================= —————————————————————– ==>cat .profile . /oracle/app/oracle/set_env_var_dba PS1=” `hostname`*${ORACLE_SID}-${PWD} ==>” EDITOR=vi; export EDITOR set -o vi stty erase PATH=$PATH:. dba() { cd /oracle/app/oracle/scripts/MAINT/DBA/bin } ———————————————————————————— ==>cat /oracle/app/oracle/set_env_var_dba # This File Sets all the Oracle Environment Variables and is called from # Each Users’s . profile file. # # Please Don’t change these settings…
New OFA for 11g
export INSTANCE_NAME=`echo $ORACLE_SID | tr ‘[:upper:]’ ‘[:lower:]’` export ORA_BDUMP=$ORACLE_HOME/admin/”$ORACLE_SID”_`hostname`/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID/trace
Alter procedure auditing
A common misconception is that the ‘audit any procedure’ is a privileges auditing option associated with the privilege ‘alter any procedure’ and thus does NOT cover the alteration of a procedure that is owned by the user altering it. Note that AUDIT PROCEDURE covers: CREATE FUNCTION CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE…
EXTPROC
EXTPROC Scope and Application A list of the essential components for executing an external procedure is included below, along with a brief description of the role of each. Database: Responsible for storing any information required to locate and execute an external procedure (e.g. the location of shared library, number and types of arguments, etc.) Initiates…