Discussion in 'SQL PL/SQL' started by khotsuhas, May 19, 2010.
i want to refresh oracle schema using PL/SQL stored procedure.
Please help me out.
What do you mean by "refreshing Oracle Schema" ?
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.
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"
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.
You will need to look for Flashback databse technique. Please consult with your DBA.
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.