Oracle Database Discussions

Hi all,

I have a reasonably large schema and I need to rename each and every table to comply with the new naming conventions. The issue, as you'd have guessed is with finding the best possible way to update the dependencies so that all the references to the old table name are replaced by the new one.

I have implemented a Store Procedure which takes the old and the new table names as input parameters and alters the table accordingly. After calling this Stored Procedure on all the tables, I execute UTL_RECOMP.RECOMP_SERIAL('user'); to recompile the invalidated objects. This step however does not fix my issue where I do not want to see the old name any more in any of the dependent objects (notably Views, Synonyms, Procedures, Functions, Packages and Triggers) and the objects remain in the INVALID state.

Is it normal that UTL_RECOMP does not take the new table name into account? Do we need to explicitly modify each and every dependent object?

I am just thinking about extending my stored procedure to deal with the dependencies. However, I need some advice from anyone who has already been through this situation. I am able to manually find all the dependencies by querying tables like user_sources/user_dependencies/user_objects etc, but, given the number of tables I have, it'll be more practical if I can extend my stored procedure.

Any suggestions?