1

I tried 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 for 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 tried 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?

[OWNER / OBJECT NAME / OBJECT TYPE]

INVALID OBJECTS

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, Chubs

Chubs
  • 11
  • 3

2 Answers2

1

If these are application objects then you should have the developers tell you explicitly which ones are safe to remove. They should be responsible for version control / configuration management of their stuff, not leaving it to you to guess.

pmdba
  • 6,457
  • 2
  • 6
  • 16
  • Thanks for the quick response. Yes they are application objects as PLAP is one of our apps. The developers told me its okay to remove, but I wasnt too sure what they know about dependencies. – Chubs Mar 10 '20 at 19:48
1

Your sample data and the way you worded your question are a little bit at odds.

The way your data is presented:

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

It looks like you are worried that the objects you intend to drop depend on other objects. This is fine and these do NOT represent a reason to worry about dropping these objects.

If the data were reversed, however... i.e., if there were rows in DBA_DEPENDENCIES where (REFERENED_OWNER, REFERENCED_NAME, REFERENCED_TYPE) matched an object that you want to drop, then it is a potential problem because it means something is depending on the object you want to drop. If you drop the object, that other object that depends on it will become invalid.

So,

  1. object to drop depends on other objects ==> fine
  2. other objects depend on object to drop ==> think about it
Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • Yes, I was worried that the objects(dependent/invalid objects) I intend to drop will have an effect on the referenced objects (turn them invalid). – Chubs Mar 10 '20 at 20:15
  • I was given the go ahead to remove them from the application owner but wasn't too sure the effect it would have on the referenced objects. But before that, I was a little confused reading up on referenced objects. Thanks for the clarification Matthew! So i should be able to delete these invalid schema objects with no worries. – Chubs Mar 10 '20 at 20:17