1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

refresh oracle schema using stored proedures

Discussion in 'SQL PL/SQL' started by khotsuhas, May 19, 2010.

  1. khotsuhas

    khotsuhas Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    HI,
    i want to refresh oracle schema using PL/SQL stored procedure.

    Please help me out.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    What do you mean by "refreshing Oracle Schema" ?
     
  3. khotsuhas

    khotsuhas Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Suppose i have imported a dump in db and have made some modification to that imported schema( fired alter create, update, delete queries)

    Some time latter I need to override all these changes made.
    So I need to clear and reimport that dump again, which is time consuming

    So I was thinking, Is there a solution for this as

    1] Import this same dump in 2 different users in database

    2] Use one of the user for performing operations
    and whenever we want to override the changes made,

    Make use of store procedure or function which will refresh the changes made to original (as like as imported dump )by making use of other user schema.

    I want to achieve this. Are there any chances to get it implemented ??


    And this process should not consume more time. Otherwise no use in implementing this ;)

    Hope you understand my requirements.
     
  4. khotsuhas

    khotsuhas Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Command used to import dump is

    "impdp username/password@orcl directory=DATA_PUMP_DIR dumpfile=schema.dmp remap_schema=dump_schema:my_schema remap_tablespace=dump_tablespace:my_tablespace transform=oid:n EXCLUDE=STATISTICS"
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I cannot think of a reason such a plan would not work but you're doubling the import time by doing this twice every time you have a new dmp file to process; if this import is so time-consuming how can you justify the time for two imports at the beginning but cannot justify the time to clear the tables and re-import when it becomes necessary?

    The logic escapes me.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You will need to look for Flashback databse technique. Please consult with your DBA.

    Flashback database
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Flashback table (part of the flashback database procedures) relies upon sufficient undo informaton available in the database thus if the flashback period is, say, two weeks the undo retention would need to be set to 1209600, a setting that would require an extremely large UNDO tablespace, possibly larger than would be feasable to create. Flashback database can do good things within short periods of time (hours, possibly a day) but for longer periods it may not be the best solution for such a situation. This also depends upon how much activity is recorded in the database during the recovery period. Rajavu is correct in pointing you to your DBA to discuss this.