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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
MedtronicVascSRAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.