How can I generate (or get) a ddl script on an existing table in oracle? I have to re-create them in Hive

sql oracle ddl database-metadata
VSJ picture VSJ · Oct 8, 2014 · Viewed 61.9k times · Source

How can I generate a DDL script on an existing table in oracle? I am working on a project where I have to re-create some tables that are present in Oracle table into Hive.

Answer

a_horse_with_no_name picture a_horse_with_no_name · Oct 8, 2014

If your SQL client doesn't support this, then you can use the dbms_metadata package to get the source for nearly everything in your database:

For a table use something like this:

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

You can also do this for all tables at once:

select dbms_metadata.get_ddl('TABLE', table_name)
from user_tables;

and spool the output into a SQL script.

More details are in the manual: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_metada.htm

Related questions

Get VIEW ddl using query

For database re-architecture I need to get DDL of each table and view in the database(Oracle). I don't want to go to property of each table/view and get SQL out of it in SQL Developer. I successfully got …

Read More
Grant privileges for an Oracle package?

I have a package A, which uses some variables and procedures in another package B in the same schema. Now I want to move package A to a new schema. What privileges should I grant to the new schema for …

Read More
Insufficient Privileges when creating tables in Oracle SQL Developer

I have granted the user in my connection to create tables, triggers, procedures, and sequence using sql+ (grant create table to <my_user>); however, that still does not allow me to create a table in that schema showing …

Read More