Loading “Views” from One Database to Another

Loading “views” from one database to another seems to be a simple request, right?  Let’s go to the regular cases/simple answers:

• Use DBMS_METDATA.GET_DDL to get the view code: DBMS_METADATA.get_ddl (‘VIEW’, <VIEW_NAME>, <OWNER>)
• Use datapump with include=VIEW, or even specifying the list INCLUDE=VIEW:\”IN (\’VW_EXAMPLE1\’, \’VW_EXAMPLE2\’)\,” or part of the name: INCLUDE=VIEW:”LIKE ‘VW_%EXEMPLE%'”

However, when supporting a client with this need there may be some caveats. The options above load the view purely. They don’t load the “content” of the view from an application perspective.

Let’s be clear; a view doesn’t store any data. Instead, it consists of a stored query to retrieve the data from actual tables. We could have underlying “physical” tables with the content of the views in case of materialized views, but in essence, even those are transitory tables built from the actual database tables. As being, views do not store any physical data – they are just a SQL stored that is used every time we query the view. Materialized views, on the other hand, store the data (results from that SQL) for a period of time.

In this scenario, how can we load views to other databases without the source tables including the underlying data?

This question has two answers:

Exporting the views as tables

We could do this manually. We could create tables from views then export them. However, to do this I’d need to have space for creating those tables, which could be considerable.

There is also the VIEWS_AS_TABLES clause in Data Pump which specifies that one or more views are to be exported as tables. As a reference: https://docs.oracle.com/database/121/SUTIL/GUID-E4E45E81-5391-43BE-B27D-B763EF79A885.htm#SUTIL3904.

However, in this case, the import will bring the views as tables, not as views. Which will theoretically resolve the issue from a data perspective, but may not be what’s required. What if I need the views as views on the destination database?

In this case, it’s necessary to load all the underlying tables. How to extract them? The hard way is opening all the view codes and listing them. The smart way is the second option.

Listing the tables referenced by views from dba_dependencies and exporting them

This would usually trigger a follow-up question; how to list the tables? If views can be complex queries involving several tables, and if I have around 300 views, this could mean a week of work, right?

Not necessarily. We can use dba_dependencies for that:

select owner as schema_name,
name as view_name,
referenced_owner as referenced_schema_name,
referenced_name,
referenced_type
from dba_dependencies
where type = 'VIEW'
-- excluding some Oracle maintained schemas
and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS',
'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
order by owner, name, referenced_name, referenced_owner, referenced_type;

And, that’s what I actually needed. With the list in place, it’s a matter of exporting with Data Pump, including the tables on the list and the views (as mentioned above).

I hope this helps!

Please feel free to leave any thoughts or questions in the comments.