CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    30

    Oracle 10g Object Dependencies

    I have been charged with the task of refreshing database. I want to pull text for objects from the database, but haveing trouble building a script that will do just that.

    The db i am refreshing is the development instance and will basically copy the production environment and reapply all the script that were applied to development to bring it back to where it is currently with fresh data.

    My first try at this was to use the user_source view, and order by last_ddl_time. That way I would have the order in which the scripts were applied to the database. However, this didn't work because of the fact that last ddl_time includes grants and revokes.

    My second stab at it since i cannot get time of the last time each object was created or replaced was to build this list through user_dependencies. That way, i wouldnt need the time i would just go by the dependency to get the correct order in which to reapply the objects in the db.

    Please let me know of any suggestions and also, if the oracle dependency view is what i need. I am worried that the dependency list may have dependencies of their own and the would just go on forever. I guess i would need to write a recursive kind of a function. - or somthing.

    Anyway, thanks in advance. any suggestion is greatly appreciated.

  2. #2
    Join Date
    Jun 2006
    Posts
    437

    Re: Oracle 10g Object Dependencies

    Hi all.

    If I've understood your problem, you're trying to refresh the development environment with the db objects that are into production environment, but the development environment is changed as regards the production environment, so you've to apply some scripts to the development environment in order to get two identical environments.
    This is a hard task.
    May be a simplier solution is to do an export from the production environment, then an import into development envirronment.

  3. #3
    Join Date
    Mar 2007
    Posts
    30

    Re: Oracle 10g Object Dependencies

    Well, this is what i am planning on doing at this point. Im not sure if this will work, but i hope at least some of the gurus out there will weigh in on whether or not i am on the right track.

    I am refreshing dev from prod to get clean, meaningful data. So, to state the obvious, there are more objects that have been developed and applied to the dev database compared to production. So, after copying the database from prod environment to dev i must apply all packages, procs, triggers, synonyms ..... and whatever else to that fresh copy of prod which will become the new dev database (hope i am explaining this well enough for others to follow).

    So, in light of that, i need to account for structual changes before i apply the objects to this new dev db. I plan to use a scritp generated by oracle developer tool to build the sync script to sync these two instances up. The script includes all alters and whatever else.... never used it befor, so not sure how good of an idea this is.

    After i get the structure, i will apply the scripts that were in dev previously. I am getting these scripts from the dbms_metadata.get_ddl function supplied with 10g. I am going to have a pl/sql program pull the text and store it on my unix box for each object that exits in dev but not prod.

    The final hurdel i have to over come is the order in which to apply the scripts. I was thinking for this i would use the get dependent ddl fucntion supplied with the same metadata package supplied by 10g. However, this just gives errors unlike the get_dll function and is pretty much usless as far as i can tell. So, i have resorted to using the user_dependencies table supplied by oracle.

    Here, i pass in the object name to a recursive procedure and it returns all of the objects and their dependencies for all levels in the heirarchy. So, if one object refrences another, and that object another, it could go on for ever, this procedure builds a hierarchal list. With this list, i then build a list of the order that the objects must be reapplied in.

    So, with all that in mind, this should work from what i can tell...... please let me know if i have over looked somthing. I guess, i am wondering if this user_dependency table is giving me the strait dope on the db objects i want to restore.

    Anyway, i have run this proc and verified results of the dependencies and it matches with what the user_dependencies table gives me as dependencies. Please let me know if this is the way to do this. I am a newbie to oracle db and not sure that just becasue this seems like it will work means that it will.

    Thanks in advance for all comments!!

  4. #4
    Join Date
    Mar 2002
    Location
    St. Petersburg, Florida, USA
    Posts
    12,125

    Re: Oracle 10g Object Dependencies

    Why create a custom solution when there are tools available that do this in a VERY reliable fashion. IO use Red-Gate's DBCompare and DBDataCompare.
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009,2010
    In theory, there is no difference between theory and practice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  5. #5
    Join Date
    Mar 2007
    Posts
    30

    Re: Oracle 10g Object Dependencies

    Well, the main issue here is money. I realize it is a 14 day free trial, however i didn't want to use it, then have to rely on it at some point later. Just thought this would be a strait forward way to get the order in which to reapply the text already in the db. Anyway, will what i want to do work?

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured