vendredi 29 janvier 2010

Linux Commands - A practical reference

This is a linux command line reference for common operations.

Examples marked with • are valid/safe to paste without modification into a terminal, so

you may want to keep a terminal window open while reading this so you can cut & paste.

All these commands have been tested both on Fedora and Ubuntu.



































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































CommandDescription
apropos whatisShow commands pertinent to string. See also threadsafe
man -t man | ps2pdf - > man.pdfmake a pdf of a manual page
which commandShow full path name of command
time commandSee how long a command takes
time catStart stopwatch. Ctrl-d to stop. See also sw
nice infoRun a low priority command (The "info" reader in this case)
renice 19 -p $$Make shell (script) low priority. Use for non interactive tasks
dir navigation
cd -Go to previous directory
cdGo to $HOME directory
(cd dir && command)Go to dir, execute command and return to current dir
pushd .Put current dir on stack so you can popd back to it
file searching
alias l='ls -l --color=auto'quick dir listing
ls -lrtList files by date. See also newest and find_mm_yyyy
ls /usr/bin | pr -T9 -W$COLUMNSPrint in 9 columns to width of terminal
find -name '*.[ch]' | xargs grep -E 'expr'Search 'expr' in this dir and below. See also findrepo
find -type f -print0 | xargs -r0 grep -F 'example'Search all regular files for 'example' in this dir and below
find -maxdepth 1 -type f | xargs grep -F 'example'Search all regular files for 'example' in this dir
find -maxdepth 1 -type d | while read dir; do echo $dir; echo cmd2; doneProcess each item with multiple commands (in while loop)
find -type f ! -perm -444Find files not readable by all (useful for web site)
find -type d ! -perm -111Find dirs not accessible by all (useful for web site)
locate -r 'file[^/]*\.txt'Search cached index for names. This re is like glob *file*.txt
look referenceQuickly search (sorted) dictionary for prefix
grep --color reference /usr/share/dict/wordsHighlight occurances of regular expression in dictionary
archives and compression
gpg -c fileEncrypt file
gpg file.gpgDecrypt file
tar -c dir/ | bzip2 > dir.tar.bz2Make compressed archive of dir/
bzip2 -dc dir.tar.bz2 | tar -xExtract archive (use gzip instead of bzip2 for tar.gz files)
tar -c dir/ | gzip | gpg -c | ssh user@remote 'dd of=dir.tar.gz.gpg'Make encrypted archive of dir/ on remote machine
find dir/ -name '*.txt' | tar -c --files-from=- | bzip2 > dir_txt.tar.bz2Make archive of subset of dir/ and below
find dir/ -name '*.txt' | xargs cp -a --target-directory=dir_txt/ --parentsMake copy of subset of dir/ and below
( tar -c /dir/to/copy ) | ( cd /where/to/ && tar -x -p )Copy (with permissions) copy/ dir to /where/to/ dir
( cd /dir/to/copy && tar -c . ) | ( cd /where/to/ && tar -x -p )Copy (with permissions) contents of copy/ dir to /where/to/
( tar -c /dir/to/copy ) | ssh -C user@remote 'cd /where/to/ && tar -x -p'Copy (with permissions) copy/ dir to remote:/where/to/ dir
dd bs=1M if=/dev/sda | gzip | ssh user@remote 'dd of=sda.gz'Backup harddisk to remote machine
rsync (Network efficient file copier: Use the --dry-run option for testing)
rsync -P rsync://rsync.server.com/path/to/file fileOnly get diffs. Do multiple times for troublesome downloads
rsync --bwlimit=1000 fromfile tofileLocally copy with rate limit. It's like nice for I/O
rsync -az -e ssh --delete ~/public_html/ remote.com:'~/public_html'Mirror web site (using compression and encryption)
rsync -auz -e ssh remote:/dir/ . && rsync -auz -e ssh . remote:/dir/Synchronize current directory with remote one
ssh (Secure SHell)
ssh $USER@$HOST commandRun command on $HOST as $USER (default command=shell)
ssh -f -Y $USER@$HOSTNAME xeyesRun GUI command on $HOSTNAME as $USER
scp -p -r $USER@$HOST: file dir/Copy with permissions to $USER's home directory on $HOST
ssh -g -L 8080:localhost:80 root@$HOSTForward connections to $HOSTNAME:8080 out to $HOST:80
ssh -R 1434:imap:143 root@$HOSTForward connections from $HOST:1434 in to imap:143
wget (multi purpose download tool)
(cd dir/ && wget -nd -pHEKk http://www.pixelbeat.org/cmdline.html)Store local browsable version of a page to the current dir
wget -c http://www.example.com/large.fileContinue downloading a partially downloaded file
wget -r -nd -np -l1 -A '*.jpg' http://www.example.com/dir/Download a set of files to the current directory
wget ftp://remote/file[1-9].iso/FTP supports globbing directly
wget -q -O- http://www.pixelbeat.org/timeline.html | grep 'a href' | headProcess output directly
echo 'wget url' | at 01:00Download url at 1AM to current dir
wget --limit-rate=20k urlDo a low priority download (limit to 20KB/s in this case)
wget -nv --spider --force-html -i bookmarks.htmlCheck links in a file
wget --mirror http://www.example.com/Efficiently update a local copy of a site (handy from cron)
networking (Note ifconfig, route, mii-tool, nslookup commands are obsolete)
ethtool eth0Show status of ethernet interface eth0
ethtool --change eth0 autoneg off speed 100 duplex fullManually set ethernet interface speed
iwconfig eth1Show status of wireless interface eth1
iwconfig eth1 rate 1Mb/s fixedManually set wireless interface speed
iwlist scanList wireless networks in range
ip link showList network interfaces
ip link set dev eth0 name wanRename interface eth0 to wan
ip link set dev eth0 upBring interface eth0 up (or down)
ip addr showList addresses for interfaces
ip addr add 1.2.3.4/24 brd + dev eth0Add (or del) ip and mask (255.255.255.0)
ip route showList routing table
ip route add default via 1.2.3.254Set default gateway to 1.2.3.254
tc qdisc add dev lo root handle 1:0 netem delay 20msecAdd 20ms latency to loopback device (for testing)
tc qdisc del dev lo rootRemove latency added above
host pixelbeat.orgLookup DNS ip address for name or vice versa
hostname -iLookup local ip address (equivalent to host `hostname`)
whois pixelbeat.orgLookup whois info for hostname or ip address
netstat -tuplList internet services on a system
netstat -tupList active connections to/from system
windows networking (Note samba is the package that provides all this windows specific networking support)
smbtreeFind windows machines. See also findsmb
nmblookup -A 1.2.3.4Find the windows (netbios) name associated with ip address
smbclient -L windows_boxList shares on windows machine or samba server
mount -t smbfs -o fmask=666,guest //windows_box/share /mnt/shareMount a windows share
echo 'message' | smbclient -M windows_boxSend popup to windows machine (off by default in XP sp2)
text manipulation (Note sed uses stdin and stdout. Newer versions support inplace editing with the -i option)
sed 's/string1/string2/g'Replace string1 with string2
sed 's/\(.*\)1/\12/g'Modify anystring1 to anystring2
sed '/ *#/d; /^ *$/d'Remove comments and blank lines
sed ':a; /\\$/N; s/\\\n//; ta'Concatenate lines with trailing \
sed 's/[ \t]*$//'Remove trailing spaces from lines
sed 's/\([`"$\]\)/\\\1/g'Escape shell metacharacters active within double quotes
seq 10 | sed "s/^/      /; s/ *\(.\{7,\}\)/\1/"Right align numbers
sed -n '1000{p;q}'Print 1000th line
sed -n '10,20p;20q'Print lines 10 to 20
sed -n 's/.*<title>\(.*\)<\/title>.*/\1/ip;T;q'Extract title from HTML web page
sed -i 42d ~/.ssh/known_hostsDelete a particular line
sort -t. -k1,1n -k2,2n -k3,3n -k4,4nSort IPV4 ip addresses
echo 'Test' | tr '[:lower:]' '[:upper:]'Case conversion
tr -dc '[:print:]' < /dev/urandomFilter non printable characters
history | wc -lCount lines
set operations (Note you can export LANG=C for speed. Also these assume no duplicate lines within a file)
sort file1 file2 | uniqUnion of unsorted files
sort file1 file2 | uniq -dIntersection of unsorted files
sort file1 file1 file2 | uniq -uDifference of unsorted files
sort file1 file2 | uniq -uSymmetric Difference of unsorted files
join -t'\0' -a1 -a2 file1 file2Union of sorted files
join -t'\0' file1 file2Intersection of sorted files
join -t'\0' -v2 file1 file2Difference of sorted files
join -t'\0' -v1 -v2 file1 file2Symmetric Difference of sorted files
math
echo '(1 + sqrt(5))/2' | bc -lQuick math (Calculate φ). See also bc
echo 'pad=20; min=64; (100*10^6)/((pad+min)*8)' | bcMore complex (int) e.g. This shows max FastE packet rate
echo 'pad=20; min=64; print (100E6)/((pad+min)*8)' | pythonPython handles scientific notation
echo 'pad=20; plot [64:1518] (100*10**6)/((pad+x)*8)' | gnuplot -persistPlot FastE packet rate vs packet size
echo 'obase=16; ibase=10; 64206' | bcBase conversion (decimal to hexadecimal)
echo $((0x2dec))Base conversion (hex to dec) ((shell arithmetic expansion))
units -t '100m/9.58s' 'miles/hour'Unit conversion (metric to imperial)
units -t '500GB' 'GiB'Unit conversion (SI to IEC prefixes)
units -t '1 googol'Definition lookup
seq 100 | (tr '\n' +; echo 0) | bcAdd a column of numbers. See also add and funcpy
calendar
cal -3Display a calendar
cal 9 1752Display a calendar for a particular month year
date -d friWhat date is it this friday. See also day
[ $(date -d "tomorrow" +%d) = "01" ] || exitexit a script unless it's the last day of the month
date --date='25 Dec' +%AWhat day does xmas fall on, this year
date --date='@2147483647'Convert seconds since the epoch (1970-01-01 UTC) to date
TZ='America/Los_Angeles' dateWhat time is it on west coast of US (use tzselect to find TZ)
date --date='TZ="America/Los_Angeles" 09:00 next Fri'What's the local time for 9AM next Friday on west coast US
echo "mail -s 'get the train' P@draigBrady.com < /dev/null" | at 17:45Email reminder
echo "DISPLAY=$DISPLAY xmessage cooker" | at "NOW + 30 minutes"Popup reminder
locales
printf "%'d\n" 1234Print number with thousands grouping appropriate to locale
BLOCK_SIZE=\'1 ls -lget ls to do thousands grouping appropriate to locale
echo "I live in `locale territory`"Extract info from locale database
LANG=en_IE.utf8 locale int_prefixLookup locale info for specific country. See also ccodes
locale | cut -d= -f1 | xargs locale -kc | lessList fields available in locale database
recode (Obsoletes iconv, dos2unix, unix2dos)
recode -l | lessShow available conversions (aliases on each line)
recode windows-1252.. file_to_change.txtWindows "ansi" to local charset (auto does CRLF conversion)
recode utf-8/CRLF.. file_to_change.txtWindows utf8 to local charset
recode iso-8859-15..utf8 file_to_change.txtLatin9 (western europe) to utf8
recode ../b64 < file.txt > file.b64Base64 encode
recode /qp.. < file.qp > file.txtQuoted printable decode
recode ..HTML < file.txt > file.htmlText to HTML
recode -lf windows-1252 | grep euroLookup table of characters
echo -n 0x80 | recode latin-9/x1..dumpShow what a code represents in latin-9 charmap
echo -n 0x20AC | recode ucs-2/x2..latin-9/xShow latin-9 encoding
echo -n 0x20AC | recode ucs-2/x2..utf-8/xShow utf-8 encoding
CDs
gzip < /dev/cdrom > cdrom.iso.gzSave copy of data cdrom
mkisofs -V LABEL -r dir | gzip > cdrom.iso.gzCreate cdrom image from contents of dir
mount -o loop cdrom.iso /mnt/dirMount the cdrom image at /mnt/dir (read only)
cdrecord -v dev=/dev/cdrom blank=fastClear a CDRW
gzip -dc cdrom.iso.gz | cdrecord -v dev=/dev/cdrom -Burn cdrom image (use dev=ATAPI -scanbus to confirm dev)
cdparanoia -BRip audio tracks from CD to wav files in current dir
cdrecord -v dev=/dev/cdrom -audio -pad *.wavMake audio CD from all wavs in current dir (see also cdrdao)
oggenc --tracknum='track' track.cdda.wav -o 'track.ogg'Make ogg file from wav file
disk space (See also FSlint)
ls -lSrShow files by size, biggest last
du -s * | sort -k1,1rn | headShow top disk users in current dir. See also dutop
df -hShow free space on mounted filesystems
df -iShow free inodes on mounted filesystems
fdisk -lShow disks partitions sizes and types (run as root)
rpm -q -a --qf '%10{SIZE}\t%{NAME}\n' | sort -k1,1nList all packages by installed size (Bytes) on rpm distros
dpkg-query -W -f='${Installed-Size;10}\t${Package}\n' | sort -k1,1nList all packages by installed size (KBytes) on deb distros
dd bs=1 seek=2TB if=/dev/null of=ext3.testCreate a large test file (taking no space). See also truncate
> filetruncate data of file or create an empty file
monitoring/debugging
tail -f /var/log/messagesMonitor messages in a log file
strace -c ls >/dev/nullSummarise/profile system calls made by command
strace -f -e open ls >/dev/nullList system calls made by command
ltrace -f -e getenv ls >/dev/nullList library calls made by command
lsof -p $$List paths that process id has open
lsof ~List processes that have specified path open
tcpdump not port 22Show network traffic except ssh. See also tcpdump_not_me
ps -e -o pid,args --forestList processes in a hierarchy
ps -e -o pcpu,cpu,nice,state,cputime,args --sort pcpu | sed '/^ 0.0 /d'List processes by % cpu usage
ps -e -orss=,args= | sort -b -k1,1n | pr -TW$COLUMNSList processes by mem (KB) usage. See also ps_mem.py
ps -C firefox-bin -L -o pid,tid,pcpu,stateList all threads for a particular process
ps -p 1,2List info for particular process IDs
last rebootShow system reboot history
free -mShow amount of (remaining) RAM (-m displays in MB)
watch -n.1 'cat /proc/interrupts'Watch changeable data continuously
system information (see also sysinfo) ('#' means root access is required)
uname -aShow kernel version and system architecture
head -n1 /etc/issueShow name and version of distribution
cat /proc/partitionsShow all partitions registered on the system
grep MemTotal /proc/meminfoShow RAM total seen by the system
grep "model name" /proc/cpuinfoShow CPU(s) info
lspci -tvShow PCI info
lsusb -tvShow USB info
mount | column -tList mounted filesystems on the system (and align output)
grep -F capacity: /proc/acpi/battery/BAT0/infoShow state of cells in laptop battery
#dmidecode -q | lessDisplay SMBIOS/DMI information
#smartctl -A /dev/sda | grep Power_On_HoursHow long has this disk (system) been powered on in total
#hdparm -i /dev/sdaShow info about disk sda
#hdparm -tT /dev/sdaDo a read speed test on disk sda
#badblocks -s /dev/sdaTest for unreadable blocks on disk sda
interactive (see also linux keyboard shortcuts)
readlineLine editor used by bash, python, bc, gnuplot, ...
screenVirtual terminals with detach capability, ...
mcPowerful file manager that can browse rpm, tar, ftp, ssh, ...
gnuplotInteractive/scriptable graphing
linksWeb browser
xdg-open .open a file or url with the registered desktop application
miscellaneous
alias hd='od -Ax -tx1z -v'Handy hexdump. (usage e.g.: • hd /proc/self/cmdline | less)
alias realpath='readlink -f'Canonicalize path. (usage e.g.: • realpath ~/../$USER)
set | grep $USERSearch current environment
touch -c -t 0304050607 fileSet file timestamp (YYMMDDhhmm)
python -m SimpleHTTPServerServe current directory tree at http://$HOSTNAME:8000/

lundi 18 janvier 2010

Export TABLE Records into Flat File with INSERTs

The GENERATE_STMT procedure of this UTILITY package needs four parameters i.e. TABLE NAME, WHERE CLAUSE, PATH WHERE Developer WANT FILE TO BE GENERATED and FILE NAME. WHERE CLAUSE value must be passed in the form of quotes. For e.g. 'WHERE ename = ' || '''' || 'SCOTT' || ''''

This procedure writes an ASCII file to specified folder. UTL_FILE_DIR parameter must be set in the init.ora file prior to installing and running the procedure.

This package looks for TABLE COLUMNS in USER_TAB_COLUMNS data dictionary. So user will be able to fetch the data only from the table(s) which resides in the CURRENT SCHEMA.

First create the UTILITY package.

/***************************************************************************
CREATED BY: Nikhil Dave nikhil@mailindia.stgil.com

COMPANY: Keysone Solutions Pvt. Ltd.
DATE: 20.02.2003
OBJECTIVE: Export data into Flat file in the form of INSERT statment(s).

VERSION TESTING: This script was tested on Oracle 8.1.6 AND
8.1.7. It can be used on SQL*Plus as well
as from any PL/SQL Object.


***************************************************************************/

CREATE OR REPLACE PACKAGE utility
IS
FUNCTION change_datatype (
prm_value IN VARCHAR2,
prm_data_type IN VARCHAR2)
RETURN VARCHAR2;


PROCEDURE generate_stmt (
prm_table_name IN VARCHAR2,
prm_where_clause IN VARCHAR2,
prm_output_folder IN VARCHAR2,
prm_output_file IN VARCHAR2);
END utility;
/

CREATE OR REPLACE PACKAGE BODY utility
IS

-- VARIABLES USED by PROCEDURE generate_stmt
-- File Related PACKAGE Variable
cmn_file_handle UTL_FILE.file_type;
--
--

PROCEDURE close_file
IS
BEGIN
UTL_FILE.FCLOSE (cmn_file_handle);
EXCEPTION

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20003, 'File handle was invalid');
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20004, 'Invalid path for file');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20005, 'CLOSE_FILE Error in creating file. Message: ' || SQLERRM);
END close_file;

PROCEDURE open_file (
prm_output_folder IN VARCHAR2,
prm_output_file IN VARCHAR2)
IS

BEGIN
cmn_file_handle := UTL_FILE.FOPEN (prm_output_folder, prm_output_file, 'a', 32767);
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
close_file;
RAISE_APPLICATION_ERROR(-20000, 'File handle was invalid');
WHEN UTL_FILE.INVALID_PATH THEN
close_file;
RAISE_APPLICATION_ERROR(-20001, 'Invalid path for file');
WHEN OTHERS THEN
close_file;
RAISE_APPLICATION_ERROR(-20002, 'OPEN_FILE Error in creating file. Message: ' || SQLERRM);
END open_file;


FUNCTION change_datatype (
prm_value IN VARCHAR2,
prm_data_type IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF prm_value IS NULL THEN
RETURN ('NULL');
END IF;

IF prm_data_type = 'C' THEN
IF INSTR(prm_value, CHR(10)) > 0 THEN
RETURN ('REPLACE(' || '''' || REPLACE (prm_value, CHR(10), CHR(977)) || '''' || ', CHR(977), CHR(10))');

END IF;
ELSIF prm_data_type = 'D' THEN
RETURN ('TO_DATE(' || '''' || prm_value || '''' || ', ' || '''' || 'DD-MON-YYYY HH24:MI:SS' || '''' || ')');
ELSIF prm_data_type = 'N' THEN
RETURN (prm_value);
END IF;
RETURN ('''' || prm_value || '''');
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002, 'CHANGE_DATATYPE Error in Converting DataType. Message: ' || SQLERRM);
END change_datatype;

PROCEDURE generate_stmt (
prm_table_name IN VARCHAR2,
prm_where_clause IN VARCHAR2,

prm_output_folder IN VARCHAR2,
prm_output_file IN VARCHAR2)
IS
TYPE ref_cols IS REF CURSOR;
mmy_ref_cols ref_cols;

mmy_column_name VARCHAR2(100);
mmy_column_data_type VARCHAR2(1);
mmy_col_string VARCHAR2(32767);
mmy_query_col_string VARCHAR2(32767);
BEGIN
IF prm_table_name IS NULL OR
prm_output_folder IS NULL OR
prm_output_file IS NULL THEN
RAISE_APPLICATION_ERROR(-20012, 'Invalid Argument Passed');
END IF;


OPEN mmy_ref_cols
FOR SELECT LOWER(column_name) column_name,
DECODE (data_type, 'VARCHAR2', 'C', 'CHAR', 'C', 'LONG', 'C', 'NUMBER', 'N', 'DATE', 'D')
data_type
FROM user_tab_columns
WHERE table_name = UPPER(prm_table_name)
ORDER BY column_id;
LOOP
FETCH mmy_ref_cols INTO mmy_column_name, mmy_column_data_type;
EXIT WHEN mmy_ref_cols%NOTFOUND;
mmy_col_string := mmy_col_string || mmy_column_name || ', ';
IF mmy_column_data_type = 'D' THEN
mmy_query_col_string := mmy_query_col_string || 'change_datatype(' || 'TO_CHAR(' || mmy_column_name || ', ' || '''' || 'DD-MON-YYYY HH24:MI:SS' || '''' || ')' || ', ' || '''' || mmy_column_data_type || '''' || ') || ' || '''' || ', ' || '''' || ' || ';
ELSIF mmy_column_data_type IN ('N', 'C') THEN
mmy_query_col_string := mmy_query_col_string || 'change_datatype(' || mmy_column_name || ', ' || '''' || mmy_column_data_type || '''' || ') || ' || '''' || ', ' || '''' || ' || ';
END IF;

END LOOP;
CLOSE mmy_ref_cols;

IF mmy_col_string IS NOT NULL AND
mmy_query_col_string IS NOT NULL THEN

IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
open_file(prm_output_folder, prm_output_file);
END IF;

mmy_col_string := 'INSERT INTO ' || LOWER(prm_table_name) || ' (' || CHR(10) || CHR(9) || CHR(9) || mmy_col_string;
mmy_col_string := RTRIM (mmy_col_string, ', ');
mmy_col_string := mmy_col_string || ')' || CHR(10) || 'VALUES ( ' || CHR(9);
mmy_query_col_string := RTRIM (mmy_query_col_string, ' || ' ||'''' || ',' || '''' || ' || ') || ' one_pare';

OPEN mmy_ref_cols
FOR ' SELECT ' || mmy_query_col_string ||
' FROM ' || prm_table_name ||

' ' || prm_where_clause;
LOOP
FETCH mmy_ref_cols INTO mmy_query_col_string;
EXIT WHEN mmy_ref_cols%NOTFOUND;
mmy_query_col_string := mmy_query_col_string || ');';
UTL_FILE.put (cmn_file_handle, mmy_col_string);
UTL_FILE.put_line (cmn_file_handle, mmy_query_col_string);
END LOOP;
CLOSE mmy_ref_cols;

If UTL_FILE.IS_OPEN(cmn_file_handle) THEN
close_file;
END IF;
END IF;
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
IF mmy_ref_cols%ISOPEN THEN
CLOSE mmy_ref_cols;
END IF;

close_file;
RAISE_APPLICATION_ERROR(-20009, 'File handle was invalid');
WHEN UTL_FILE.INVALID_PATH THEN
IF mmy_ref_cols%ISOPEN THEN
CLOSE mmy_ref_cols;
END IF;
close_file;
RAISE_APPLICATION_ERROR(-20010, 'Invalid path for file');
WHEN OTHERS THEN
IF mmy_ref_cols%ISOPEN THEN
CLOSE mmy_ref_cols;
END IF;
close_file;
RAISE_APPLICATION_ERROR(-20011, 'GENERATE_STMT Error in populating file. Message: ' || SQLERRM);
END generate_stmt;
END utility;
/

Trigger to Prevent Dropping Objects

This trigger secures objects, preventing a user from dropping them by mistake. To begin, create a table for the example below called secured_objects with a column objectname, with datatype varchar2(20). Insert a record into this table with a value, the name of the object, which should not get dropped. Then, create the following trigger:

create or replace trigger check_beforedrop
before drop on database
declare
oname char(20);
begin
select objectname into oname from secured_objects

where upper(objectname)=ora_dict_obj_name;
if sql%found then
RAISE_APPLICATION_ERROR(-20001,'You did not want this object to be dropped');
end if;
exception
when no_data_found
then dbms_output.put_line('This object will be dropped');
end;

After this trigger is created, you cannot drop an object whose name is entered in the table. In the case where you need to drop an object, remove the entry from the secured_objects table.

Find Invalid Synonyms

"ORA-00980: synonym translation is no longer valid" is a common error encountered in a development environment. This can happen for many reasons. Some of them are
1. You created a synonym on non-existing object by mistake. For example, you created a synonym on SCOTT.DEPT where either the SCOTT schema in not present or the DEPT table is missing.
2. You dropped an object but you did not drop the synonyms referencing the object.
3. You dropped a user, but you did not drop synonyms referencing the objects owned by that user. When an object is dropped, synonyms referring to the object are not dropped. This script generates DDL to drop synonyms whose translation is no longer valid.
USE WITH CAUTION.

rem
rem Exludes SYS and SYSTEM users
rem
select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',
'SYNONYM'||s.owner||'.')||s.synonym_name||';'
from dba_synonyms s
where table_owner not in('SYSTEM','SYS')
and db_link is null
and not exists
(select 1
from dba_objects o
where s.table_owner=o.owner
and s.table_name=o.object_name)
/

Find Full Table Scans

This query provides a list of queries (1,000 characters only) involved in full table scans. The total number of rows and blocks for each table is also displayed so that you can determine whether the full scan is degrading performance.
select sp.object_owner,sp.object_name,
(select sql_text from v$sqlarea sa
where sa.address = sp.address
and sa.hash_value =sp.hash_value) sqltext,
(select executions from v$sqlarea sa
where sa.address = sp.address
and sa.hash_value =sp.hash_value) no_of_full_scans,
(select lpad(nvl(trim(to_char(num_rows)),' '),15,' ')||' | '||lpad(nvl(trim(to_char(blocks)),' '),15,' ')||' | '||buffer_pool
from dba_tables where table_name = sp.object_name
and owner = sp.object_owner) "rows|blocks|pool"
from v$sql_plan sp
where operation='TABLE ACCESS'
and options = 'FULL'
and object_owner IN ()
order by 1,2;

Instance Background Processes Information

This script displays instance background process information. The script works when the database is MOUNTed or OPENed.

-- Oracle 8I
select
A.SID,
A.SERIAL#,
A.PROGRAM,
P.PID,
P.SPID,
A.OSUSER, /* Who Started INSTANCE */
A.TERMINAL,
A.MACHINE,
A.LOGON_TIME,
B.NAME,
B.Description
from
v$session A,
v$process P,
v$bgprocess B
where
A.PADDR=B.PADDR
AND A.PADDR=P.ADDR
and A.type='BACKGROUND'
;


--Output-- ( UNIX )
----------

SID SERIAL# PROGRAM OSUSER PID SPID TERMINAL MACHINE
LOGON_TIME NAME DESCRIPTION
1 1 oracle@sunO817 (PMON) oracle 2 24782 UNKNOWN sunO817
15-Nov-2003 PMON process cleanup
2 1 oracle@sunO817 (DBW0) oracle 3 27864 UNKNOWN sunO817
15-Nov-2003 DBW0 db writer process 0
3 1 oracle@sunO817 (LGWR) oracle 4 28380 UNKNOWN sunO817
15-Nov-2003 LGWR Redo etc.
4 1 oracle@sunO817 (CKPT) oracle 5 28638 UNKNOWN sunO817
15-Nov-2003 CKPT checkpoint
5 1 oracle@sunO817 (SMON) oracle 6 28896 UNKNOWN sunO817
15-Nov-2003 SMON System Monitor Process
6 1 oracle@sunO817 (RECO) oracle 7 29154 UNKNOWN sunO817
15-Nov-2003 RECO distributed recovery
9 1 oracle@sunO817 (ARC0) oracle 10 29928 UNKNOWN sunO817
15-Nov-2003 ARC0 Archival Process 0
22 585 oracle@sunO817 (ARC1) oracle 16 53922 UNKNOWN sunO817
16-Nov-2003 ARC1 Archival Process 1




-- Oracle9I+
select
A.SID,
A.SERIAL#,
A.PROGRAM,
P.PID,
P.SPID,
A.OSUSER, /* Who Started INSTANCE */
A.TERMINAL,
A.MACHINE,
A.LOGON_TIME,
B.NAME,
B.Description
,P.PGA_USED_MEM
,P.PGA_FREEABLE_MEM
,P.PGA_MAX_MEM
from
v$session A,
v$process P,
v$bgprocess B
where
A.PADDR=B.PADDR
AND A.PADDR=P.ADDR
--and A.type='BACKGROUND'
--Alternative (you can use BACKGROUND column from v$process )
--------------
AND P.BACKGROUND=1
;


--Output-- ( Win2k )
----------

SID SERIAL# PROGRAM OSUSER PID SPID TERMINAL MACHINE LOGON_TIME NAME
DESCRIPTION
1 1 ORACLE.EXE SYSTEM 2 1312 ADVTEST0 ADVTEST0 20-Nov-03 PMON
process cleanup
2 1 ORACLE.EXE SYSTEM 3 972 ADVTEST0 ADVTEST0 20-Nov-03 DBW0
db writer process 0
3 1 ORACLE.EXE SYSTEM 4 1276 ADVTEST0 ADVTEST0 20-Nov-03 LGWR
Redo etc.
4 1 ORACLE.EXE SYSTEM 5 1228 ADVTEST0 ADVTEST0 20-Nov-03 CKPT
checkpoint
5 1 ORACLE.EXE SYSTEM 6 792 ADVTEST0 ADVTEST0 20-Nov-03 SMON
System Monitor Process
6 1 ORACLE.EXE SYSTEM 7 1448 ADVTEST0 ADVTEST0 20-Nov-03 RECO
distributed recovery

Comparing Contents of Two Tables with Identical Structure

This script generates a spool file that can be run from a SQL*Plus session to list the differences between 2 tables with identical structure. This can be useful if 2 similar tables need to be compared across different schemas (or different databases, in which case a local view will need to be created using database link), or to compare a table with itself at different points in time, for example, before and after an update through Applications to track how a table is affected. The script indicates whether a record is present in one table but not in the other, or if the same key is present in both tables but non-key columns have different values. Script :

undefine TABLE1
undefine TABLE2
define g_table1 = '&&TABLE1'
define g_table2 = '&&TABLE2'
set verify off
set feedback off
set serveroutput on size 1000000
spo temp_file.sql
declare
v_owntab1 varchar2(255) := '&&g_table1';
v_owntab2 varchar2(255) := '&&g_table2';
v_own1 varchar2(255);
v_own2 varchar2(255);
v_tab1 varchar2(255);
v_tab2 varchar2(255);
v_dot1 number := 0;
v_dot2 number := 0;
type t_cols is table of varchar2(255) index by binary_integer; v_cols1
t_cols; v_cols2 t_cols; v_out1 varchar2(255); v_out2 varchar2(255); kq
CONSTANT varchar2(1) := ''''; v_ind number := 0; v_str
varchar2(2000):=null; v_ind_found boolean := FALSE; v_ind_colno number
:= 0;
procedure print_cols (p_cols in t_cols) is
begin
for i in 1..p_cols.count
loop
dbms_output.put_line(','||p_cols(i));
end loop;
end print_cols;

begin
v_dot1 := instr(v_owntab1, '.');
v_dot2 := instr(v_owntab2, '.');

if v_dot1 > 0 then
v_own1 := upper(substr(v_owntab1, 1, v_dot1-1));
v_tab1 := upper(substr(v_owntab1, v_dot1+1));
else
v_own1 := null;
v_tab1 := upper(v_owntab1);
end if;

if v_dot2 > 0 then
v_own2 := upper(substr(v_owntab2, 1, v_dot2-1));
v_tab2 := upper(substr(v_owntab2, v_dot2+1));
else
v_own2 := null;
v_tab2 := upper(v_owntab2);
end if;
select column_name
bulk collect into v_cols1
from all_tab_columns
where table_name = v_tab1
and owner = nvl(v_own1, user)
order by column_id;

select column_name
bulk collect into v_cols2
from all_tab_columns
where table_name = v_tab2
and owner = nvl(v_own2, user)
order by column_id;

if v_cols1.count = 0 or v_cols2.count = 0 then
dbms_output.put_line('Either or Both the tables are invalid');
return;
end if;

dbms_output.put_line('(');
dbms_output.put_line('select '||kq||'TAB1'||kq);
print_cols(v_cols1);
dbms_output.put_line(' from '||nvl(v_own1, user)||'.'||v_tab1);
dbms_output.put_line('MINUS');
dbms_output.put_line('select '||kq||'TAB1'||kq);
print_cols(v_cols1);
dbms_output.put_line(' from '||nvl(v_own2, user)||'.'||v_tab2);
dbms_output.put_line(')');

dbms_output.put_line('UNION');

dbms_output.put_line('(');
dbms_output.put_line('select '||kq||'TAB2'||kq);
print_cols(v_cols1);
dbms_output.put_line(' from '||nvl(v_own2, user)||'.'||v_tab2);
dbms_output.put_line('MINUS');
dbms_output.put_line('select '||kq||'TAB2'||kq);
print_cols(v_cols1);
dbms_output.put_line(' from '||nvl(v_own1, user)||'.'||v_tab1);
dbms_output.put_line(')');

dbms_output.put_line('order by ');
for c1 in (
select b.column_name
from all_indexes a, all_ind_columns b
where a.owner=b.index_owner
and a.index_name=b.index_name
and a.uniqueness = 'UNIQUE'
and a.table_owner = nvl(v_own1, user)
and a.table_name = v_tab1
order by b.index_name, b.column_position
)
loop
v_ind_found := TRUE;
v_ind_colno := v_ind_colno + 1;
if v_ind_colno = 1 then
dbms_output.put_line(c1.column_name);
else
dbms_output.put_line(','||c1.column_name);
end if;
end loop;
if not v_ind_found then
dbms_output.put_line('2 ');
end if;
dbms_output.put_line(';');


end;
/
spo off
set feedback on

Testing : --Setup of Test Data

drop table test1;
create table test1 (f1 number, f2 number, f3 varchar2(10)); insert into
test1 values (1, 10, 'X1'); insert into test1 values (2, 20, 'X2');
insert into test1 values (3, 30, 'X3'); insert into test1 values (4, 40,
'X4');

drop table test2;
create table test2 (f1 number, f2 number, f3 varchar2(10)); insert into
test2 values (1, 100, 'X1'); insert into test2 values (2, 20, 'X2');
insert into test2 values (3, 30, 'X3'); insert into test2 values (40,
40, 'X4');

Run the script and pass the table names TEST1 and TEST2 when prompted. Run the spool file temp_file.sql from SQL*Plus. Output:

SQL> @temp_file

'TAB F1 F2 F3
---- ---------- ---------- ----------
TAB1 1 10 X1
TAB2 1 100 X1
TAB1 4 40 X4
TAB2 40 40 X4

4 rows selected.

Note: 1. Parameters can be either in schema.table_name format or only table_name format (in which case the current schema is assumed). 2. By default, the script compares all columns. If you don't need all the columns, edit the spool file to comment out the columns that are not required. If any key column is being removed, remove the column from the "order by" list as well. 3. The script sorts the records based on the unique keys so that related records can be viewed together. It determines the key list by querying the data dictionary ALL_INDEX and ALL_IND_COLUMNS views for the FIRST table passed as input. If no indexes are found, then ordering is based always on the first column. (In the test case column F1 is chosen because no unique key has been defined.) So if one of the tables is located in a remote database, make it the second table when generating the spool file.

samedi 16 janvier 2010

Compare table structure in oracle

I found an interesting script that I used sometime back to compare the structure of 2 tables. Thought I should preserve and blog it for the benefit of all.

--- SCRIPT START ---

Rem script name - compare_tables.sql
Rem Table structure comparison script
Rem Can compare tables across schema's provided the user has privilege
Rem Provide table name in the format SCHEMA_NAME.TABLE_NAME

set verify off
set heading off
set feedback off
set line 100

column owner format a20
column column_name format a20
column data_type format a20

accept table1 prompt 'Table 1: '
accept table2 prompt 'Table 2: '

prompt
prompt columns having same name but difference in datatype or length:
prompt -------------------------------------------------------------------------

column data_precision fold_after

select
a.column_name, a.data_type, a.data_length, a.data_scale,a.data_precision,
b.column_name, b.data_type, b.data_length, b.data_scale,b.data_precision
from
all_tab_columns a, all_tab_columns b
where
a.table_name = upper(substr('&table1',instr('&table1','.')+1)) and
a.owner = upper(substr('&table1',1,instr('&table1','.')-1)) and
b.table_name = upper(substr('&table2',instr('&table2','.')+1)) and
b.owner = upper(substr('&table2',1,instr('&table2','.')-1)) and
a.column_name = b.column_name and
(
a.data_type <> b.data_type or
a.data_length <> b.data_length or
a.data_scale <> b.data_scale or
a.data_precision <> b.data_precision
);

prompt columns present in &table1 but not in &table2
prompt ----------------------------------------------

select
column_name
from
all_tab_columns
where
table_name = upper(substr('&table1',instr('&table1','.')+1))
and owner = upper(substr('&table1',1,instr('&table1','.')-1))
minus
select
column_name --, data_type, data_length, data_scale, data_precision
from
all_tab_columns
where
table_name = upper(substr('&table2',instr('&table2','.')+1))
and owner = upper(substr('&table2',1,instr('&table2','.')-1));

prompt columns present in &table2 but not in &table1
prompt ----------------------------------------------

select
column_name --, data_type, data_length, data_scale, data_precision
from
all_tab_columns
where
table_name = upper(substr('&table2',instr('&table2','.')+1))
and owner = upper(substr('&table2',1,instr('&table2','.')-1))
minus
select
column_name
from
all_tab_columns
where
table_name = upper(substr('&table1',instr('&table1','.')+1))
and owner = upper(substr('&table1',1,instr('&table1','.')-1));

--- SCRIPT END --

Example using the script

CREATE TABLE DEPT_1
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(20),
LOC VARCHAR2(20)
);

create table dept_2 as select deptno,dname from dept;

alter table dept_2 modify dname VARCHAR2(40);

@compare_tables.sql

Table 1: SIPRAS.DEPT_1
Table 2: SIPRAS.DEPT_2

columns having same name but difference in datatype or length:
------------------------------------------------------------------------

SIPRAS DNAME VARCHAR2 20
SIPRAS DNAME VARCHAR2 40

columns present in SIPRAS.DEPT_1 but not in SIPRAS.DEPT_2
-------------------------------------------------------------------------

LOC
columns present in SIPRAS.DEPT_2 but not in SIPRAS.DEPT_1
--------------------------------------------------------------------------

mercredi 13 janvier 2010

ORA-01940: Cannot drop a user that is currently connected

ORA-01940: Cannot drop a user that is currently connected


Problem Description:

SQL> drop user strmadmin cascade;
drop user strmadmin cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

Solution of The Problem:

Find out the connected user sid and serial# by,

SQL> select sid, serial# from v$session where username = 'STRMADMIN';

SID SERIAL#
---------- ----------
268 1268
315 1223

Before killing session you may wish to lock the account for further connection attempts. This is extremely necessary to drop a user who automatically establish session like to drop an application user or to drop a user who perform batch jobs.

SQL> Alter user strmadmin account lock;

Now kill the connected session.
SQL> alter system kill session '268,1268';
System altered.

SQL> alter system kill session '315,1223';
System altered.

And then drop the user.
SQL> drop user strmadmin cascade;
User dropped.

mardi 12 janvier 2010

Create Read only user for a Schema

One thing you need to remember before read this post is there is no easy or shortcut way to make a read only user of another schema. Like grant select on username to another_username- there is no such single command like this. However you may have several alternatives to make read only user for a schema.

I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema.
Let's start by creating PROD user.
SQL> CREATE USER PROD IDENTIFIED BY P;
User created.

SQL> GRANT DBA TO PROD;
Grant succeeded.

SQL> CONN PROD/P;
Connected.

SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);
Table created.

SQL> INSERT INTO PROD_TAB1 VALUES(1,2);
1 row created.

SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE);
Table created.

SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1
BEGIN
INSERT INTO PROD_TAB2 VALUES(SYSDATE);
END;
/
Trigger created.

SQL>CREATE VIEW A AS SELECT * FROM PROD_TAB2;
View created.


Method 1: Granting Privilege Manually

Step 1: Create devels user
SQL> CREATE USER DEVELS IDENTIFIED BY D;
User created.

Step 2: Grant only select session and create synonym privilege to devels user.
SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO DEVELS;
Grant succeeded.

Step 3:Make script to grant select privilege.
$vi /oradata2/script.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/select_only_to_prod.sql
@@/oradata2/select_only_script.sql
SPOOL OFF

This script will run the /oradata2/select_only_script.sql and generate a output script /oradata2/select_only_to_prod.sql which need to be run in fact.

Step 4:
Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.

$vi /oradata2/select_only_script.sql
SELECT 'GRANT SELECT ON PROD.' ||TABLE_NAME || ' TO DEVELS;' FROM DBA_TABLES WHERE OWNER='PROD';
SELECT 'GRANT SELECT ON PROD.' ||VIEW_NAME || ' TO DEVELS;' FROM DBA_VIEWS WHERE OWNER='PROD';

Step 5:
Now execute the /oradata2/script.sql which will in fact generate scipt /oradata2/select_only_to_prod.sql.
SQL> @/oradata2/script.sql
GRANT SELECT ON PROD.PROD_TAB1 TO DEVELS;
GRANT SELECT ON PROD.PROD_TAB2 TO DEVELS;

Step 6:
Execute the output script select_only_to_prod.sql which will be used to grant read only permission of devels user to prod schema.
SQL> @/oradata2/select_only_to_prod.sql

Step 7:
Log on devels user and create synonym so that the devels user can access prod's table without any dot(.). Like to access prod_tab2 of prod schema he need to write prod.prod_tab2. But after creating synonym he simply can use prod_tab2 to access devels table and views.
To create synonym do the following,

SQL>CONN DEVELS/D;

SQL>host vi /oradata2/script_synonym.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/synonym_to_prod.sql
@@/oradata2/synonym_script.sql
SPOOL OFF

SQL>host vi /oradata2/synonym_script.sql
SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR PROD.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='PROD';
SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR PROD.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='PROD';
SQL>@/oradata2/script_synonym.sql
SQL>@/oradata2/synonym_to_prod.sql

Step 8: At this stage you have completed your job. Log on as devels schema and see,
SQL> select * from prod_tab1;
1 2

SQL> show user
USER is "DEVELS"

Only select privilege is there. So DML will throw error. Like,

SQL> insert into prod_tab1 values(4,3);
insert into prod_tab1 values(4,3)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Method 2: Writing PL/SQL Code
This is script for table :
set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name FROM dba_tables where owner='PROD';
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.table_name||' TO devels';
execute immediate sql_txt;
END LOOP;
END;
/

This is the script for grant select permission for views.
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT view_name FROM dba_views where owner='PROD';
BEGIN dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.view_name||' TO devels';
--dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

To create synonym on prod schema,
Log on as devels and execute the following procedure.
SQL>CONN DEVELS/D
SQL>
DECLARE
sql_txt VARCHAR2(300);
CURSOR syn_cur IS
SELECT table_name name FROM all_tables where owner='PROD'
UNION SELECT VIEW_NAME name from all_views where owner='PROD' ;
BEGIN dbms_output.enable(10000000);
FOR syn IN syn_cur LOOP
sql_txt:='CREATE SYNONYM '||syn.name|| ' FOR PROD.'||syn.name ;
dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

Method 3: Writing a Trigger


After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.

Create a log on trigger that eventually set current_schema to prod just after log in DEVELS user.

create or replace trigger log_on_after_devels
after logon ON DEVELS.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = prod';
END;
/

dimanche 10 janvier 2010

Extracting Table Data Using SELECT Statement into an XML File

create or replace procedure
getXML(newContext_qry varchar2,
rowSettag varchar2,
rowTag varchar2,filename varchar2)
 is
-- Input query string

-- Input rowsetTag , the root tag
-- Input row level tag
-- Input file name
-- Note make sure that before running this procedure,
-- it is required that UTL_FILE_DIR must
-- be set in init.ora file In this code
-- it has been set to d:\oracle9i.

begin
declare
qryCtx dbms_xmlgen.ctxHandle ;
result clob;
lob_length integer;
read_amount integer;
read_offset integer;

buffer varchar2(100);
loc varchar2(100) := 'usr_dir';
f_hand utl_file.file_type;

Begin
-- Setting up offset and no. of chars to be read in
-- in one go from clob datatype.
read_offset := 1;

read_amount := 75;

dbms_output.put_line('opening');
--Opening file
f_hand := Utl_File.Fopen(location =>'d:\oracle9i',
filename =>filename,
open_mode =>'w',
max_linesize => 32767);
dbms_output.put_line('file open');

-- Creating new context
qryCtx := dbms_xmlgen.newContext(newContext_qry);

-- Defining Rowsettag
DBMS_XMLGEN.setRowSetTag(qryCtx,rowSettag);

-- Defining Rowtag
DBMS_XMLGEN.setRowTag(qryCtx,rowTag);

-- Generating XML and storing in an clob datatype
result :=DBMS_XMLGEN.getXML(qryCtx);

dbms_output.put_line('xml generated');

-- Getting the length of the data stored in Clob
lob_length := dbms_lob.getlength(result);

-- Reading data from clob variable and writng into file.
while (lob_length > 0) loop
dbms_lob.read(result,read_amount,read_offset,buffer);

dbms_output.put_line('writing in file');
utl_file.put(f_hand,buffer);
dbms_output.put_line('written');
read_offset := read_offset+read_amount;
lob_length := lob_length-read_amount;
if lob_length < read_amount then
read_amount := lob_length;
end if;
end loop;
utl_file.fclose(f_hand);

EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');

WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');

WHEN UTL_FILE.INVALID_OPERATION then
RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');

WHEN UTL_FILE.INVALID_FILEHANDLE then
RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');

WHEN UTL_FILE.WRITE_ERROR then
RAISE_APPLICATION_ERROR(-20104,'Write Error');

WHEN UTL_FILE.READ_ERROR then
RAISE_APPLICATION_ERROR(-20105,'Read Error');

WHEN UTL_FILE.INTERNAL_ERROR then
RAISE_APPLICATION_ERROR(-20106,'Internal Error');

WHEN OTHERS THEN
UTL_FILE.FCLOSE(f_hand);

end;

end;
/