Archive for June, 2007
Shell script to spool a no of tables into .xls files…
On OTN someone asked a question that how to spool data from a table into a xls file. Spooling a single table I discussed in one of the previous posts. We can use the same approach to spool data from more than 1 table also. Well here I will do it through a shell script and assume that you have a text file having list of tables to be spooled (Even if you don’t have one, it can be easily made by spooling the names of tables into a simple text file) Here is the shell script that you can use to spool data to various xls files, table wise.
cat list.txt | while read a do echo "spooling $a" sqlplus username/password@string <<EOF set feed off markup html on spool on spool /home/oracle/$a.xls select * from $a; spool off set markup html off spool off EOF done
I didn’t see any work around for Windoze as SQLPLUS << EOF thing doesn’t seem to work in Windows. Will try to find some alternative. If you come across something, do let me know.
Sidhu
Is multiplication faster than division ?
An interesing post by Laurent. Check out http://laurentschneider.com/wordpress/2007/06/to-divide-or-to-multiply.html
My findings on 10gR2 on Windoze XP
SQL> var z numberSQL> var y numberSQL> exec :z := power(2,102)*2e-31;
PL/SQL procedure successfully completed.
SQL> exec :y := 1e125;
PL/SQL procedure successfully completed.
SQL> set timi onSQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10SQL> set timi offSQL> print y
Y----------9.988E-126
SQL> exec :z := power(2,-104)*2e31;
PL/SQL procedure successfully completed.
SQL> exec :y := 1e125;
PL/SQL procedure successfully completed.
SQL> set timi onSQL> exec while (:y>1e-125) loop :y:=:y*:z; end loop
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04SQL> set timi offSQL> print y
Y----------9.988E-126
SQL>
Sidhu
Spool to a .xls (excel) file…
A small tip, I read on OTN about spooling to a .xls (excel) file:
It goes like this
set feed off markup html on spool onspool c:\salgrade.xls select * from salgrade; spool offset markup html off spool off
And the xls it makes shows up like:
Sidhu
Oracle concepts…
A very nice series of articles by Howard Rogers about Oracle Concepts. It includes all the basics like what a database, instance is ? Various types of files and all basic stuff. Read it here
Sidhu
IMPORT & EXPORT from/to compressed files directly…
Well, a simple method to import(export) directly to(from) compressed files using pipes. Its for Unix based systems only, as I am not aware of any pipe type functionality in Windows. The biggest advantage is that you can save lots of space as uncompressing a file makes it almost 5 times or more. (Suppose you are uncompressing a file of 20 GB, it will make 100 GB) As a newbie I faced this problem, so thought about writing a post.
Lets talk about export first. The method used is that create a pipe, write to a pipe(ie the file in exp command is the pipe we created), side by side read the contents of pipe, compress (in the background) and redirect to a file. Here is the script that achieves this:
export ORACLE_SID=MYDB rm -f ?/myexport.pipe mkfifo ?/myexport.pipe cat ?/myexport.pipe |compress > ?/myexport.dmp.Z &amp; sleep 5 exp file=?/myexport.pipe full=Y log=myexport.log
Same way for import, we create a pipe, zcat from the dmp.Z file, redirect it to the pipe and then read from pipe:
export ORACLE_SID=MYDB rm -f ?/myimport.pipe mkfifo ?/myimport.pipe zcat ?/myexport.dmp.Z > ?/myimport.pipe &amp; sleep 5 imp file=myimport.pipe full=Y show=Y log=?/myimport.log
In case there is any issue with the script, do let me know
Update: If you are on Wintel, you can directly use a compressed folder as an export target. No need to create a pipe as the file system will automatically do it for you. ( Thanks Noons for the tip)
If you are using gunzip:
For export:
export ORACLE_SID=MYDB rm -f exp.pipe mknod exp.pipe gzip < exp.pipe > T1.dmp.gz &amp; exp file=exp.pipe full=Y log=myexport.log
For import:
export ORACLE_SID=MYDB rm -f imp.pipe mknod imp.pipe gzip < T1.dmp.gz > imp.pipe &amp; imp file=imp.pipe full=Y log=myimport.log
Update: I came across an article that discusses few ways to achieve the same on Windows. Check it here.