Is there any way to programmatically "move" an oracle table definition from one database to another?

Question

Suppose I have two Oracle databases. We'll call them database A and database B.

Now suppose I have a table in database A that's defined like this:

CREATE TABLE foo
(
    foo_id INT PRIMARY KEY,
    some_text VARCHAR2(10),
    other_table_id INT

    CONSTRAINT some_fk_constraint
        FOREIGN KEY (other_table_id)
        REFERENCES other_table(other_table_id)
)

...is there any easy way to create this same table in database B with the same primary key and same foreign key, but without any data? It would be ideal if there were a pure SQL method so I can do this in Python with cx_Oracle, but I can launch a shell script if need be.

Answer 1

DBMS_METADATA sounds like the package you're looking for

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

Then you could run it with EXECUTE IMMEDIATE

Answer 2

select dbms_metadata.get_ddl('TABLE','FOO','SCHEMA_NAME') from dual; 

This will return you the DDL necessary to create/recreate the table. One thing to note, this returns the tablespaces used in the source db (in your case A) so you'll need to make sure the they exist in db B and the user you have connected as has proper quotas for them.