Oracle Tips

Our application (3 tier, Front end Forms10g and back end 10gR2) provides user with a front end to refresh the mviews. That form has 2 columns showing mview name and the comment against it. Recently i saw that while opening this front end ORA-01403 NO DATA FOUND was being raised. I opened the fmb and found that it was populating comments from DBA_TAB_COMMENTS. In 10g the comments against mviews are stored in DBA_MVIEW_COMMENTS unlike till 9i where it was in ALL_TAB_COMMENTS. So there was a little modification required. ...

I had got an export of a database and had to import it to a new database. The only difference was that in new database few of the large tables were partitioned. So instead of partitioning it after the import, i thought about pre-creating the tables (with partitioning) and then run import with ignore=Y . Everything went fine. But later on the front end application gave some error and we came to know that default values for columns in some tables were not set. I did some googling and didn’t find much. Then i posted the same to OTN forums and came to know that if the table pre-exists, import doesn’t take care of default values of columns. Metalink note 224727.1 discusses this. So if you are pre-creating the tables and there are some default values for any columns, set it manually, don’t rely on import for this. Same is true for impdp as well. ...

If you are used to work in Unix enviroment and then sometime, in between have to sit on Windows and tail -f alert_DB.log, its a real pain. There is a small bundle of utilities, called Unxutils which can make you feel at home in Windows too. These are the exe’s of all major commands in Unix like more, less, ls, grep etc… To use it just download the zip file from the link above, extract it to some folder and add the path of exe’s to your Windows PATH. Restart your machine and you are done. ...

At my workplace we were facing a problem with refresh of a mview. Say it was created in schema of user1 but when I tried to refresh it from user2 it would give ORA-03113: end-of-file on communication channel. Then we raised a SR and have been following up with Oracle support for long but it was not getting anywhere. Yesterday that guy seemed to have reached some point. The mviews that we have created and are having problem with refresh are created on top of both local & remote objects and he said that up to 11gr2 there is no possibility of creating mviews on both local and remote objects. I did validate this thing. All the mviews failing to refresh are created on top of both local & remote objects. But again from the owner the refresh is fine but from another user it gives problem. By the way that guy hinted at bug 4084125 and also suggested a work around. I haven’t tried that yet. Will try and update about the results. ...

Many times, we are required to restore a database from an export dmp file. Its a simple task but sometimes there are some issues left like invalid objects or some objects missing, in the newly created database. Following steps, followed in order can help in creating an error free database: Create a blank database:The very first step is to create a blank database which is to be used as the target database. That can be done using Database Configuration Assistant. (In last step of the DBCA, change redo log file sizes to 500 MB each (or some appropriate values depdening upon the size of the databaes), as during import, lot of redo will be generated, so large redo size helps in that scenario) ...

Many times you need to move datafiles from one location to another. The simplest approach for this is to take the tablespace offline, copy the datafiles to new location, rename the files with alter database rename file (Except that you dont have to move the SYSTEM and UNDO tablespace, as you can’t take SYSTEM tablespace offline) [sourcecode language=‘css’] SYS@orcl AS SYSDBA> alter tablespace system offline; alter tablespace system offline * ERROR at line 1: ORA-01541: system tablespace cannot be brought offline; shut down if necessary ...

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

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