Create Table from View in another schema in Oracle

I am trying to recreate a data structure similar to one in another database the only difference is that  I have a view in one database and I want to recreate that view as an actual table in another database.  Can someone provide some advice on how to write a script that would use the existing oracle metadata to generate the create table statement?


MedtronicVascSRAsked:
Who is Participating?
 
MedtronicVascSRConnect With a Mentor Author Commented:
jwittenm - thanks for the info - this is not quite what I'm trying to do though as I do not have the tables that the view is using in the target database, plus the table and column names in the target table are very cryptic.  So I want to use the view table/column names and create the table as that - with the proper datatypes for each column.

I was doing some more research and a colleague recommended - a Create table as select statement- this works well without having to go through the select from dba_views.

create table xxx_new
tablespace new_tablespace_name
storage (initial new_initial next new_next freelists new_freelist_number )
as
select * from xxx
0
 
jwittenmCommented:
use the dbms_metadata.get_ddl procedure.
0
 
jwittenmCommented:
sorry, that was too fast.  
set long 999999
select text from dba_views where view_name='<view_name>' will give you the text of the view.  Then just
'create table <schema>.<table_name> as <text from the above query>;
0
All Courses

From novice to tech pro — start learning today.