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.sqlYour 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.