pvmehta.com

cold backup scripts to copy locally

28-Oct-2009 Admin No Comments

==>cat SOX_BACKUP.ksh #!/usr/bin/ksh # +———————————————————————— # | Usage : ./SOX_BACKUP.ksh # | Description : Cold Backup of Database. Automatically starts/stop the DB. # +———————————————————————— . /export/home/oracle/FLRSTB.env sqlplus -s /nolog /mnt/ORACLEBACKUP/files.log connect / as sysdba set term off set feedback off; set head off; set pagesize 150 select name||’ /mnt/ORACLEBACKUP/FLRPRD’ from v$datafile; exit; EOF cat /mnt/ORACLEBACKUP/files.log|grep…

Linux/Unix, shell

Oracle 10g for solaris 10

28-Oct-2009 Admin No Comments

Oracle 10g for Solaris 10 I got error message “Checking operating system version: must be 5.8 or 5.9. Actual 5.10 Failed

Oracle, SQL scripts

Error Handling in Proc

21-Oct-2009 Admin No Comments

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…

Read More “Error Handling in Proc” »

Oracle, SQL scripts

Proc code

21-Oct-2009 Admin No Comments

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)

Oracle, SQL scripts

Removing Ctrl-M from end of line using vi

11-Oct-2009 Admin No Comments

ou can remove the ^M by doing the following: Bring the datafile into the Unix editor vi. In command mode you would type in: :1,$s/^V^M// Make sure you are entering ‘control V’ and ‘control M’. The ‘control V’ says to accept the next character exactly as I type it. Adding the two slashes at the…

Linux/Unix, shell

replacing ^M character when passing files from Windows to Unix

30-Sep-2009 Admin No Comments

To replace the ^M ‘s on the screen in vi type :1, $ s/r/

Linux/Unix, shell

To see how much time or progress of long transaction

30-Sep-2009 Admin No Comments

select * from v$session_longops where sid=100;

Oracle, SQL scripts

cp_filesystem.sql

15-Sep-2009 Admin No Comments

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

14-Sep-2009 Admin No Comments

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, SQL scripts

Remove DOS CR/LFs (^M)

11-Sep-2009 Admin No Comments

Remove DOS style CR/LF characters (^M) from UNIX files using: sed -e ‘s/^M$//’ filename > tempfile The newly created tempfile should have the ^M character removed

Linux/Unix, shell