Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    23

    Question Unanswered: How many times can I run catalog.sql

    Hi

    I have 9.2.0.5 Oracle version, in Red Hat operating system.
    Due to a problems with my database (with drop user and create user), I have seen in any forums that the solution is to run catalog.sql and catproc.sql. But my doubt is if anything would be wrong in my database if I execute again these procedures.

    thank you

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "catalog.sql" creates data dictionary views; I guess there would be no harm if you run it more than once (as it consists mostly of the "CREATE OR REPLACE VIEW ..." statements). However, as I'm not a DBA, perhaps you should wait a little bit longer to hear another opinion.

  3. #3
    Join Date
    Sep 2004
    Posts
    60

    Lightbulb

    I believe you don't need to rerun these scripts (due to problems with drop user and create user). It won't affect dictionry views.

  4. #4
    Join Date
    May 2006
    Posts
    132
    What type of errors are you getting?

    I would first check in DBA_OBJECTS to see if there are any INVALID objects. If you want to rerun catalog & catproc, then I would start the database up in restricted mode and rerun the scripts. No guarantee however that this will solve your problem.

  5. #5
    Join Date
    Sep 2003
    Posts
    23
    hi

    I checked the status objects in DBA_OBJECTS, and I have many objects (more or less 50) with status INVALID from schema SYS. for example:

    TYPE SYS AGGRCENTROID 1 11 PLS-00201: identifier 'MDSYS.SDOAGGR' must be declared

    What do you think about this, is this a problem in my database?
    I don´t know what is my problem, because I don`t have experienced in database administracion.

    Please can I help me

    thank for advanced

  6. #6
    Join Date
    May 2006
    Posts
    132
    You can try to run utlrp.sql script to recompile all invalid PL/SQL packages and then recheck the INVALID objects. If you still have that many INVALID objects after running the script, then startup in restricted mode and try the catalog.sql & catproc.sql script.

    BTW...have you patched your database recently? If so, did you run catpatch.sql?

  7. #7
    Join Date
    Sep 2003
    Posts
    23
    Hi

    Yes, I think is a good idea run script utlrp.sql, I have read any post in Metalink about this. The problem is with objects property schema MDSYS but I don,t have MDSYS schema, we don,t have install Oracle SPATIAL .
    Only question, is necessary startup database in restricted mode to run utlrp.sql?.
    The other side, the last patch installed in my database is July 2005

    thank for advanced

  8. #8
    Join Date
    May 2006
    Posts
    132
    You don't need to be in restricted mode to run utlrp.sql.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •