Skip navigation.

Need of Framework in PL/SQL Coding

Over a period of time, I have seen many database (backend) centric projects using huge amount of processing, I found most of them lacking Development Framework, To make it more clear I would say that Framework is not only limited to GUI like JAVA DOT.NET. It is certain sets of standards used before starting development projects, This presentation will support approach towards achieving this objective.

AttachmentSize
pl_sql_coding_style_guidelines.pdf90.77 KB

ORA-20100

Hi,

I am using PL/SQL to generate HTML Page and I got ORA-20100 error. Any advice?

Thank you in advance

This is an error generated

This is an error generated when a file at the OS level does not have permissions for the Oracle Applications menu compile process to write. Generally this can be caused by multiple oracle systems running on the same server and the utl_file_dir has the same /usr/tmp or /tmp or whatever your specific setting is. The files are generated by process ids out of the rdbms and these number are re-used in different instances of the application. The file permissions default to the owner of the rdbms process (typically oracle) but it sounds like you may have different oracle owners at the os.

Solution for this is to shutdown the applications on this server and then remove the tmp files in the appropriate directory. You should be able to first locate the specific file name in the error message. Use the environment variable $APPLPTMP to find the path where the file is located then use the ls -l to file the permissions with the file name.

Once either the permissions are opened up or the file removed you should be able to continue. However, now that you shutdown. We generally clear all *.tmp files when this happens.

Good luck

useful unix command for dbas

df -k . - free
du -sk * - usage in kb

find /home/e024996 -name "CCF*" -print

find /home/e024996 -name "CCF*" -print 2>&1

find /home/e024996 -name "CCF*" -print 2>&1 > /dev/null 2>&1

find . -name "*log" -print -exec rm {} \;

Displays the top 20 CPU users on the system
$ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -6r
$ps -e -o pmem -o pcpu -o pid -o user -o args | sort -k 1 | tail -6r
$ps -e -o pcpu -o pmem -o pid -o user -o args | sort -k 1 | tail -6r
$ps -e -o pmem -o pcpu -o pid -o user -o args | sort -k 1 | tail -6r

find the % of cpu usage for single processes
$ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r | grep 578390

find the sum of specified no of processes
$ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -30r | awk '{sum = sum + $1} END {print sum}'

above same can be get from ps -eo pcpu,pid,args|grep -v defunct | sort -n | tail
this given by rich calvin,uts

find the sum of specified no of processes - for the cpu usage usage for maximum limit
$ps -e -o pcpu -o pid -o user -o args | sort -k 1 | awk '{if ($1>0.0) sum = sum + $1} END {print sum}'

find the sum of all the cpu processes
$ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -100r | awk '{sum = sum + $1} END {print sum}'

find the sum of all the processes
$ps -e -o pmem -o pid -o user -o args | sort -k 1 | tail -100r | awk '{sum = sum + $1} END {print sum}'

count lines (Shows how many processes)
$ps -e -o pcpu -o pid -o user -o args | sort -k 1 | awk 'END{print NR}'

sum of the file size in folder

$ls -al | awk '{sum = sum + $5} END {print sum}'

list the file size

$ls -l | awk '{print $5}'

this command displays all the files in the current directory sorted by file size
$ls -al | sort -n -r +4

The command ls -al writes the file size in the fifth column, which is why we skip the first four columns using +4.
The options -n and -r request a numeric sort (which is different than the normal alphabetic sort) in reverse order

Change 'vBNS' to 'NETWORK' with
$sed -e 's/vBNS/NETWORK/g' < nlanr.txt

tr translates characters
$tr 'a' 'Z' < sdsc.txt

converts upper case to lower case
$tr 'A-Z' 'a-z' < file

ls -s | awk '{if ($1 > 50) print $1 " " $2 }' list all files over 50 blocks in size.

find . -exec grep "DISPLAY" {} \; -print | pg search all files for the text string "DISPLAY" - takes a while to run !

find /ora0/admin -name "*log" -print Simple use of find - to list all files whose name end in log under /ora0/admin and its subdirectories
find . -name "*log" -print -exec rm {} \; to delete files whose name end in log. If you do not use the "-print" flag, the file names will not be listed on the screen

find . -exec chown oracle {} \; -print Change ownership on every single file in current directory and lower directories to oracle (useful if someone has done an install erroneously as root.)

find . -name "*.trc" -ctime +3 -exec rm {} \; simply search for the files with extension "trc" and remove them if they are more than three days old. A simple command does the trick:

To forcibly remove them prior to the three-day limit, use the -f option.
find . -name "*.trc" -ctime +3 -exec rm -f {} \;

If you just want to list the files:
find . -name "*.trc" -ctime +3 -exec ls -l {} \;

df -k /ora0 Freespace available on /ora0 in Kilobytes.
df -k . If you're not sure of the mount point name, go to the directory where you want to see the freespace and
issue this command,where "." indicates current directory.

du du [-s] [directoryname] Disk used; gives operating system blocks used by each subdirectory. To convert to KB, for 512K OS blocks,
divide the number by 2.
du -s gives the summary, no listing for subdirectories

find Find files.
find /ora0/admin -name "*log" -print 2>/dev/nullSimple use of find - to list all files whose name end in log under /ora0/admin and its subdirectories
find . -name "*log" -print -exec rm {} \; to delete files whose name end in log. If you do not use the "-print" flag,
the file names will not be listed on the screen.

grep oracle /etc/passwd to display the lines containing "oracle" from /etc/passwd file.
grep -i -l EMP_TAB *.sql to display only the file names (-l option) which contains the string EMP_TAB,
ignore case for the string (-i option), in all files with sql extention.
grep -v '^#' /etc/oratab display only the lines in /etc/oratab where the lines do not (-v option; negation) start with # character
(^ is a special character indicating beginning of line, similarly $ is end of line).

rcp [-r] sourcehost:filename destihost:filename Remote copy. Copy files from one coputer to another. rcp host1:/ora0/file1.txt host2:/ora0/temp/file1.txt

find / -name "tnsnames.ora" -print 2>/dev/null

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

find / -name "rda" -print 2>/dev/null

6965911.994

Good post Kamal

Nice set of commands..But some of them wont work in HP-UX Itanium.