Amardeep Sidhu's Tech blog

Today I came across a requirement where users needed to ftp files time and again. So ftp’ing again and again is not a very good option. I wrote a small batch file for the same. Just sharing the same over here. I created a folder ftp in C drive and a file get_file.bat Contents of get_file.bat are: [sourcecode language=‘css’] set /p file_name=Enter the name of the file you want to ftp: echo oracle>c:\ftp\param.cfg echo oracle123»c:\ftp\param.cfg echo cd /home/oracle»c:\ftp\param.cfg echo lcd c:\ftp»c:\ftp\param.cfg bin get %file_name% ftp -s:param.cfg 127.0.0.1 ...

One may encounter this error while importing from a dmp file from older versions of Oracle. Genereally this error is caused by some statement like alter session set current_schema=scott; And the simple reason is that the user scott doesn’t exist. Yesterday I came across this error. And the reason was that user was not created. As in case of import we generally create tablespaces first (by creating the DDL using option show=Y) but creation of users is done by import itself. In older versions of Oracle, the temp tablespaces were no different from other tablespaces. But in newer versions temp tablespaces are different. So in dmp files from thoese older versions create user statements are written like create user t1 identified by t1 default tablespace temp temporary tablespace temp. This thing worked fine in older versions but in newer versions we cannot specify the TEMP tablespace as the default tablespace for a user. So the statement create user t2 identified by t2 default tablespace temp temporary tablespace temp throws ORA-12910: cannot specify temporary tablespace as default tablespace. In such cases the users (for which default statement is specified as TEMP) have to be created manually by specifying the appropriate tablespace as default tablespace and then the import should be run with ignore=Y. ...

Since I switched to this new job, my profile has changed. Here I work as a DBA. So my interaction with Oracle & anything related to Oracle has also increased. Started exploring Oracle related forums and websites specially OTN forums (http://forums.oracle.com) This is the only page thats almost always open on my Desktop in office & Laptop at home and I refresh more than my office Lotus Notes. Today I was reading APCs blog , that Jonathan Lewis has also started posting on OTN forums and being so busy man, from where he finds the time ? I too think the same. There are so many people having many years of experience in industry, answering the questions on OTN, uesnet groups and various other forums and everything is for free. They are not paid anything for the same thing. Its like taking time out of your time, understand somebody’s problem, create same scenario your PC, try out and then post the answer ! I am, sort of new to the forums and sometimes for whole of week, I am unable to post any answers, even knowing something about the issue someone has posted. Just the “time” thing. These days I am very close to OTN forums, visit for whole of the day and also post answers to the questions I know something about. There are many people who are regular visitors and are answering questions on the daily basis. Hats off ! to all these “big bosses” of the technology ! ...

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. ...

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 number SQL> var y number SQL> exec :z := power(2,102)*2e-31;PL/SQL procedure successfully completed.SQL> exec :y := 1e125;PL/SQL procedure successfully completed.SQL> set timi on SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loopPL/SQL procedure successfully completed.Elapsed: 00:00:00.10 SQL> set timi off SQL> print yY ---------- 9.988E-126SQL> exec :z := power(2,-104)*2e31;PL/SQL procedure successfully completed.SQL> exec :y := 1e125;PL/SQL procedure successfully completed.SQL> set timi on SQL> exec while (:y>1e-125) loop :y:=:y*:z; end loopPL/SQL procedure successfully completed.Elapsed: 00:00:00.04 SQL> set timi off SQL> print yY ---------- 9.988E-126SQL> Sidhu ...

A small tip, I read on OTN about spooling to a .xls (excel) file: It goes like this [sourcecode language=‘css’]set feed off markup html on spool onspool c:\salgrade.xls select * from salgrade; spool offset markup html off spool off[/sourcecode] And the xls it makes shows up like: Sidhu Comments Comment by hemant on 2007-06-26 16:44:00 +0530 hi i am working for a bank and we are using 10g. i am very raw at the oracle and have just started teaching myself through a book i have. we have many reports devised by our vendor still we need some that r not available. so we wanted the data to be exported in xl wherein i could manipulate to data to our need. abt this spooling thing. i have copied down ur script and want to test it… but how do i access the shell prompt(do not know unix either) thanks for the help extend it bit further for me please hemu ...

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 Comments Comment by Anu on 2011-06-01 12:16:50 +0530 Hi Amardeep, Am a newbie for Oracle and was searching for Howard Rogers articles. I found your post but It doesn’t have his articles anymore, When i click the link. It will be great if you can send me if the correct link or where can i find them or mail me if you have any. ...

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. ...

The very first question that may come to your mind is: what the heck this “OC” is ? Well if PC stands for Personal Computer then OC stands for Office Computer ;) The story behind all this is: One of my friend (He is working in a non-IT company) called me and asked how to burn a PC so that it no more works :) Why ? because the PC they are given is too slow & old and thats the only way they can get a new one. I thought for a while and 1 or 2 ideas came to my mind. But to dig into little more details I called my friend Vaibhav ( The guy I call to “discuss” and “know more about” all the techie things) He gave some really nice ideas. Combining all the thoughts, here is the summary of all the methods that you can use ;) ...

Well, a really old post on Google groups (uesnet:comp.databases.oracle.server). Someone posted a thread about Tom Kyte and then people responded with their thoughts (perfectly as expected). Read it here. Sidhu