process id based files and processes
Following command can be used to find files used by specific processid. $ pfiles process_id Following command can be used to find files used by specific processid. $ pmap process_id
cold backup scripts to copy locally
==>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…
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
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)
Removing Ctrl-M from end of line using vi
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…
replacing ^M character when passing files from Windows to Unix
To replace the ^M ‘s on the screen in vi type :1, $ s/r/
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…