Database Administration (MOSC)

MOSC Banner

Search and replace in view definitions?

edited Sep 18, 2018 5:03AM in Database Administration (MOSC) 1 commentAnswered

I am migrating a few schemas from one database to another using impdp with network_link parameter..  In the process, one of the schemas is being renamed, so for this the remap_schema parameter takes care of most every naming change.  My issue is that there are some views, and a couple of procedures, which have references to the schema name in them and the remap doesn't account for this.  As such, these objects are invalid in the new database and have to be updated with the new schema name before they will compile successfully.

My issue is that there are 100-150 of these affected objects, and I really don't want to spend the time it takes to manually edit each one -- especially during the relatively short maintenance window that the final migration will take place within.  So I am wondering if there is some way to programmatically "search & replace" this schema qualifier directly in the database (SQL script, PL/SQL block, mass DDL export/modify/import) for this known list of objects?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center