Q
Problem solve Get help with specific problems with your technologies, process and projects.

Copying views from one database to another

How do I copy all views from one database to another?

The best way to move just views from one database to another is to reverse engineer the CREATE VIEW commands for each database. If you are using Oracle 9i or 10g, then you can use the DBMS_METADATA.GET_DDL procedure to obtain the DDL command to recreate a view. To reverse engineer all of your schema's views, the GET_DDL procedure can be used as follows:

SELECT DBMS_METADATA.GET_DDL('VIEW',view_name) FROM user_views;

If you are using Oracle 8i or earlier, then you have more work to do. The following command will perform the same functionality for just views:

SELECT 'CREATE VIEW '||view_name||' AS '||text||';' FROM user_views;

Now that you know how to reverse engineer the DDL for a view, you will want to spool the output to a script. This can be done as follows:

SQL> spool create_my_views.sql
SQL> SELECT DBMS_METADATA.GET_DDL('VIEW',view_name) FROM user_views; 
SQL> spool off

This will create a file called "create_my_views.sql" with all of your CREATE VIEW DDL commands in it. Sign on to your new database with SQL*Plus and run the script as follows:

SQL> @create_my_views.sql
Your views will be created in your schema in this new database.

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close