Category Archives: Oracle Tips

DBA_MVIEW_COMMENTS view in 10g

Leave a reply

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.

BTW if you try to comment on the table (which is created with MVIEW) it won’t allow you to do so and instead raise ORA-12098: cannot comment on the materialized view.

So may be that little change needs to be done !

Import and default values for columns

Leave a reply

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.

Unxutils for Windows

Leave a reply
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.
Happy more’ing…less’ing…

ORA-03113 Refresh of a mview in Oracle 10g

Leave a reply

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.

Sidhu

Importing a full database…

3 Replies

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:

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

  2. Extract DDLs and create tablespaces: Now run the import with show=Y option and create a log of all DDL statements. The main things to be looked for in the log are DDLs to create tablespaces and DB links. You may need to change the create tablespace statements according to the version of the Oracle you are using. If you have the export taken in an older version, where dictionary tablespaces were being used, you will need to change the statements accordingly, to create locally managed tablespaces.
    (If you have the dmp file in compressed (.Z) format check here, to run the import directly from compressed file)

  3. Adjust the size of SYSTEM, TEMP, USERS and UNDO: As SYSTEM, TEMP, USERS and UNDO tablespaces will get created with the database itself, so you can alter the sizes as per the sizes in the old database.

  4. Edit tnsnames.ora and create dblinks: Now edit tnsnames.ora to include all the databases used in the db links and create db links using the statements from DDL log.

  5. Run the import: Finally, run the import with FULL=Y and IGNORE=Y options and after the import finishes, look for any errors in the log. At last, compile all the invalid objects in the database (Here is the link to a script to compile all the invalid objects). (If the import terminates with ORA-01435, then have a look at this post.)

To read about all the options with imp have a look at Original Import & Export Utilities chapter of Oracle Utilities guide.

Sidhu