Forum Stats

  • 3,837,803 Users
  • 2,262,300 Discussions
  • 7,900,397 Comments

Discussions

Categories

Is it OK to drop Invalid Objects that have a Referenced_Owner/Referenced_Name? (dba_dependencies)

4089142
4089142 Member Posts: 3
edited Mar 11, 2020 8:45AM in SQL & PL/SQL

I attempting to clean out my Oracle 18c databases of Invalid Objects. I was given the go ahead from the developers to remove a few invalid objects but after googling, I noticed that I would have to watch out of other dependent objects.

If I drop these invalid objects would it have any effect on the referenced objects. Would it turn those invalid as well, creating more invalid objects? I already attempted to recompile and got compilation errors. I sent them to the application owners (Developers) and they said I can remove the objects. Below I provided the Invalid objects and the referenced objects to them. How should I clean out the invalid objects?

INVALID OBJECTS  **[OWNER / OBJECT NAME / OBJECT TYPE]**

PLAP/TEMP_DIS_CONSTR/PROCEDURE

PLAP/TEMP_DRP_CONSTR_PK/PROCEDURE

PLAP/TEMP_UPDATE_MILESTONE_TABLE/PROCEDURE

INVALID OBJECTS -----> REFERENCED OBJECTS

PLAP/TEMP_DIS_CONSTR/PROCEDURE -------> SYS/ALL_CONS_COLUMNS/VIEW

PLAP/TEMP_DIS_CONSTR/PROCEDURE -------> PUBLIC/DBMS_OUTPUT/SYNONYM

PLAP/TEMP_DIS_CONSTR/PROCEDURE -------> SYS/STANDARD/PACKAGE

PLAP/TEMP_DRP_CONSTR_PK/PROCEDURE ------> SYS/STANDARD/PACKAGE

PLAP/TEMP_DRP_CONSTR_PK/PROCEDURE ------> PUBLIC/DBMS_OUTPUT/SYNONYM

PLAP/TEMP_DRP_CONSTR_PK/PROCEDURE ------> PLAP/AOP_CAPABILITY_MILESTONES/TABLE

PLAP/TEMP_UPDATE_MILESTONE_TABLE/PROCEDURE ------> SYS/STANDARD/PACKAGE

PLAP/TEMP_UPDATE_MILESTONE_TABLE/PROCEDURE ------> PLAP/AOP_MILESTONES_SEQ/SEQUENCE

Thanks

Tagged:
4089142
· Share on TwitterShare on Facebook

Answers

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Mar 10, 2020 4:58PM

    You only have to worry about objects that reference the invalid objects (directly or through other dependencies), not objects that the invalid objects reference (caveated that they are not referenced by another invalid object).

    E.g. Dropping PLAP/TEMP_DIS_CONSTR/PROCEDURE, won't affect SYS/ALL_CONS_COLUMNS/VIEW

    4089142
    · Share on TwitterShare on Facebook
  • BEDE
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy
    edited Mar 11, 2020 2:43AM

    That depends...

    Some of those invalid packages/procedures/functions/views may need to have their code changed so that they may be used later on. So, I don't think it is the case to rush into such spring cleaning. First use dbms_utility.compile_schema(x_schema,false) to see what can recompile, then send all the developers you know to be working on that database a list of the invalid objects, eventually including also the errors, with the warning that, if in within two weeks they won't fix them, then they will be dropped.

    Yet, if your concern is space, tables are those that take most of the space.

    4089142
    · Share on TwitterShare on Facebook
  • 4089142
    4089142 Member Posts: 3
    edited Mar 11, 2020 8:42AM

    Thanks so much! That's exactly what I did. First I tried to see what invalid objects I could recompile (except I used @utlrp.sql). Then, I sent all the developers a list of the invalid objects with each error included so that they can look through and fix the code if need be.

    · Share on TwitterShare on Facebook
  • 4089142
    4089142 Member Posts: 3
    edited Mar 11, 2020 8:45AM

    Thanks for the clear and concise answer Paulzip! This is exactly what I needed to know!

    · Share on TwitterShare on Facebook