i have an unusal question i think, but i am curios about your meaning about this.
I am often in the situation to get a production database from an old db-server to a new one. In some cases i am also instructed to lift up the Oracle Version. Naturally i use export/import to get all the data of our application into the new database.
Sometimes the databases are quite big (about 20 to 200 Gig), so the Import and some other stuff lasts long. The consequence is, of course, to do it at weekends or after buisness hours.
So far so good. There is no problem so far, but i was thinking about another solution for this task.(maybe i am just lazy? ;-)
Here is my idea.
Write a package to get the application data via a database link into the new database. The easiest way is "create table ... as select * from ...@(protected)"
This is very fast, as my first tests have shown.
After that, i get all Indexes via some dynamic SQL, the same with all sorts of constraints. At last i get all defaults for the table columns also via dynamic SQL
The advantage for me is, i can start one procedure, enjoy my weekend and just have to look for problems after everything is done. It seems to be safe for the old production database, so, in worst case, we just have to cancel the installation of the new db-server, but the production database is still available.
What did i miss? Where are the pitfalls?
I started to write the package. It is working inside a test - scenario with a small database.
Some things are still missing. Views are not implemented now, also Triggers are not ready yet. Packages of the application are no problem, because they are part of the installation of the application so i can install it after the process is done. if they are compiling without errors, it is also a good sign for the whole "migration" of the data because of the dependencies. Sequences are also not totally ready now.
