deep copy of complex database tables

hartwig has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: deep copy of complex database tables by Thilosophy (Curate) on Jun 21, 2005 at 11:04 UTC
You may want to look at Oracle-supplied utitilies, in particular `exp`, which dumps consistent snapshots of parts (or the whole) of the database. You can then later import these with the `imp` utility. If you are only concerned with a few tables, you can just do something like the following (which would also work across database links if you set those up): `create table testschema.blah as select * from realschema.blah;` [download] What do you mean by "complex" tables that need a "deep copy"?	[reply] [d/l] [select]
Re: deep copy of complex database tables by merlyn (Sage) on Jun 21, 2005 at 10:37 UTC
I'm not an Oracle guy, but in PostgreSQL, I can say "CREATE DATABASE foo_test TEMPLATE foo;", and Pg creates a snapshot into my test database. For this to work, nobody can be attached to the source database. -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply.	[reply]
Re: deep copy of complex database tables by ghenry (Vicar) on Jun 21, 2005 at 11:05 UTC
Update: Thilosophy beat me to it ;-) Why reinvent the wheel? You should use the oracle utilities for this, which you can script via perl. You need to use Oracle's exp utility. If you have admin rights on the DB, then you can export objects from one schema and import into another. `$ORACLE_HOME/bin/exp help=yes for export syntax` [download] `$ORACLE_HOME/bin/imp help=yes for import syntax` [download] I hope I am not barking up the wrong tree. Walking the road to enlightenment... I found a penguin and a camel on the way..... Fancy a yourname@perl.me.uk? Just ask!!!	[reply] [d/l] [select]
Re: deep copy of complex database tables by DrHyde (Prior) on Jun 22, 2005 at 07:57 UTC
If you do fancy reinventing the wheel, look at the following tables in the data dictionary: user_tables user_tab_columns user_constraints and no doubt a few others to figure out what tables you need to copy, then `CREATE TABLE foo ... AS SELECT ...`	[reply] [d/l]
Re^2: deep copy of complex database tables by hartwig (Sexton) on Jun 23, 2005 at 13:20 UTC
Before writing nifty comments about reinventing- try to read the question carefully :~\| - Assume you have a graph-structure which is circular and fairly easy to construct with the given two tables - it is non trivial to extract this structure from the graph. As I mentioned - what I like to have is a deep copy (including all 'sub-table-entries') - any ideas?	[reply]