Home » RDBMS Server » Server Utilities » Running CATALOG.SQL and Catexp.sql (Oracle 9i)
Running CATALOG.SQL and Catexp.sql [message #346278] Mon, 08 September 2008 01:10 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am new to export and import. I was facing some problem with export option. I looked into google and found that I should run the file catexp.sql and catalog.sql.
I ran the sql catexp.sql but after I executed the file, I realized that the username with which I had logged in didn't had DBA rights. So I saw many messages which alerted that DBA permission is required. And many objects got created and many not due to permission problem. I didn't knew what to do and I closed the window of Sql*Plus.

The problem with export was due to something else and I was able to export. But later in the evening I found that when I reconnected the database I started getting errors like:

ORA-00980 synonym translation is no longer valid
ORA-04063: view "WYETH.V_$OPTION" has errors


There are other databases too on the same server and they all are getting this error.
I don't know what happenend and how to correct this.

Further I looked at the link:
http://www.orafaq.com/wiki/Export
and found that :
Quote:
To use export utility CATEXP.SQL or CATALOG.SQL (which in turn call CATEXP.SQL) must be run after the database has been created. CATEXP.SQL has to be run only once when the database is created. In case CATEXP.SQL was not called by CATALOG.SQL (due to CATALOG.SQL not finding the file CATEXP.SQL where it needs), you can run CATEXP.SQL as user SYS. Be careful not to run CATALOG.SQL once again as it might destroy your data dictionaries.


I am afraid if I had done a blunder mistake.

Please help me in correcting the mistake I have done.

Regards,
Mahi

[Updated on: Mon, 08 September 2008 01:15] by Moderator

Report message to a moderator

Re: Running CATALOG.SQL and Catexp.sql [message #346281 is a reply to message #346278] Mon, 08 September 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You ran the script with user WYETH.
Drop the user, connect as SYS and rerun the scripts.

Regards
Michel
Re: Running CATALOG.SQL and Catexp.sql [message #346285 is a reply to message #346281] Mon, 08 September 2008 01:26 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I thought of restarting the server and rerun the script catalog.sql, catproc.sql and catexp.sql as sysdba.

But do I need to drop the user Wyeth. Is it needed or it can work without dropping the user wyeth because it's a schema which is very important.
Re: Running CATALOG.SQL and Catexp.sql [message #346291 is a reply to message #346285] Mon, 08 September 2008 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your database is a mess.
If you are able to determine what is correct, what is wrong, what are the consequences and how to fix then do it.
If not, recreate the database.

Regards
Michel
Re: Running CATALOG.SQL and Catexp.sql [message #346332 is a reply to message #346291] Mon, 08 September 2008 04:29 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Michel,
As I told you in my first post that I am new to this and need your help on this. I can't recreate the database. There is little scope of making changes to it.
Thats why I asked you if dopping of the Wyeth user is essential. Also please tell me as why this user needs to be dropped. And if I have to drop it anyway then what should I do after these steps to get wyeth user back :
Drop the user, connect as SYS and rerun the scripts.


I am sorry for asking such questions but please bear with me as I do not want to make any further mistakes and the wyeth user too is needed back in case I drop it.

Regards,
Mahi
Re: Running CATALOG.SQL and Catexp.sql [message #346358 is a reply to message #346332] Mon, 08 September 2008 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mixed SYS objects in user schema.
This is very bad.
Depending on what you badly installed, that is the level of mess of your database, the action is different.
As you are newbie, the only safe action is to recreate the database.
The second level in safety is to drop the user and reexecute the misinstalled scripts.
The third level is to try to find all SYS objects that are in your user schema, try to drop them and reexecute the scripts.

Regards
Michel
Re: Running CATALOG.SQL and Catexp.sql [message #349630 is a reply to message #346358] Mon, 22 September 2008 07:38 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Michel,

Thanks for your advice.

As you are newbie, the only safe action is to recreate the database.


The database is old having lots of data. I have exported it and can create this user again by dropping it.
But if I import the exported data for tables, views , procedures etc. then the possibility is that the same thing might happen again.

Kindly put some light on it as I do not want to do any mistake with this database now.

The second level in safety is to drop the user and reexecute the misinstalled scripts.


I can drop the user with cascade.
DROP USER USERNAME CASCADE
But do I need to execute the script catalog.sql again? I am able to export the databse now however. Also how will I get the data on recreating the new user.

The third level is to try to find all SYS objects that are in your user schema, try to drop them and reexecute the scripts.


This seems to be the easiest and I can save my data too. How to find all SYS objects without dropping any useful SYS object.

Sorry for asking so many questions. Please bear with me as I do not want to make any further mistakes.

Thanks for your help,

Soni
Re: Running CATALOG.SQL and Catexp.sql [message #349637 is a reply to message #349630] Mon, 22 September 2008 08:02 Go to previous message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to process by subtraction: assume that all objects that in this schema are not yours shoud be SYS ones in wrong place.
If you don't know what are your objects, you are out of luck.

Regards
Michel
Previous Topic: Ctl file doubts
Next Topic: Importing a full export - creating tablespaces first?
Goto Forum:
  


Current Time: Fri Dec 09 11:24:09 CST 2016

Total time taken to generate the page: 0.05719 seconds