Re: ORA-04045: catalog.sql and catproc.sql damaged my db.

"fee pack" <defection_at_163.net> wrote in message news:4e732814.0208221911.279160a3_at_posting.google.com...

> do u mean i can recover my db by following these steps?
> step
> 1.ALTER SYSTEM SET _SYSTEM_TRIG_ENABLED='FALSE'
> 2.run catalog.sql, catproc.sql
> 3.ALTER SYSTEM SET _SYSTEM_TRIG_ENABLED='true'
>
> by the way, i am using 9i, do i need catalog.sql and catproc.sql?

You can recover your db by following these steps, but you only need to do this *once*. You don't need to run catalog or catproc normally, this is only done when database is created or updated with a patchset. And you indeed need both scripts - they create all standard database objects needed for it to operate. You just don't need to run them now and then. So, correct sequence for recovering your db from the state it is in now is this:

• to prevent unprivileged users from logging in or doing things
• while you do maintenance; quiesce may take some time to
• execute as it will wait until all unprivileged sessions finish their
• active transactions/fetches and go to inactive state. alter system enable restricted session; alter system quiesce restricted;
• disable system triggers alter system set _system_trig_enabled='false'; @?/rdbms/admin/catalog @?/rdbms/admin/catproc
• this script is only needed if you need Java VM @?/javavm/install/initjvm
• re-enable system triggers alter system set _system_trig_enabled='true';
• restore normal mode of operation alter system unquiesce; alter system disable restricted session;

As a matter of fact, since you seem to be not very proficient with Oracle maintenance, I would recommend using GUIs provided with Oracle for maintenance and DBA tasks, like creating new tablespaces. Otherwise you face risk of damaging your database. The script above should restore system catalog to stable state and you SHOULD NOT touch it afterwards unless you are installing a patchset and strictly following patchset installation instructions.

> any idea for my script?
> does the following sequence make sense:
> 1.run catalog.sql
> 2.create tablespace
> 3.run catproc.sql

No, it doesn't. Simple operation of tablespace creation has nothing to do with recreating system catalog. Remove steps 1 and 3 and you'll be allright here :) Though I still recommend you to use GUI for creating tablespace - it provides you with all options you can set for operation in a user-friendly manner, so you don't have to familiarize yourself with CREATE TABLESPACE command and all of its options.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.