OraFAQ Forum: Server Utilities » Is it possible to export a view

Home » RDBMS Server » Server Utilities » Is it possible to export a view

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is it possible to export a view [message #265446]

Thu, 06 September 2007 06:01

ShaShalini
Messages: 59
Registered: January 2007

Member

Hi

I have a view which selects records from many tables. I want to export the view and import it in another schema.

Is it possible to export Views ??? If yes , then can you tell me how . Because when I am doing export it just gives me error table or view does not exist.

My Command for export was

exp usr1/usr1 file=rocview.dmp tables=VIEW_T1 rows=y log=exproc.log

Thanks

Report message to a moderator

Re: Is it possible to export a view [message #265450 is a reply to message #265446]

Thu, 06 September 2007 06:06

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> create view v as select * from t;

View created.

SQL> select dbms_metadata.get_ddl('VIEW','V') from dual;
DBMS_METADATA.GET_DDL('VIEW','V')
-----------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "MICHEL"."V" ("C1", "C2", "C3") AS
  select "C1","C2","C3" from t


1 row selected.

Regards
Michel

Report message to a moderator

Re: Is it possible to export a view [message #265457 is a reply to message #265450]

Thu, 06 September 2007 06:29

ShaShalini
Messages: 59
Registered: January 2007

Member

Thanks for the reply ..

My view is not a simple select from table, its a view of objects.

the view is created like this

create or replace view view_t1 of OBJ_T1 with OBJECT IDENTIFIER (Ser_no) as
SELECT --- statement goes on ..

OBJ_T1 is created like this

CREATE or replace TYPE OBJ_T1 as OBJECT (
ser_no varchar2(10),
|
|
and it goes on ..

When I run the statement that you provided it just gives me a single line output

CREATE OR REPLACE FORCE VIEW "USR1"."VIEW_T1" OF "USR1"."OBJT_T1"

Report message to a moderator

Re: Is it possible to export a view [message #265465 is a reply to message #265446]

Thu, 06 September 2007 06:53

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

ShaShalini

exp usr1/usr1 file=rocview.dmp tables=VIEW_T1 rows=y log=exproc.log

Oracle "Concepts" book, Glossary

A view is a custom-tailored presentation of the data in one or more tables. A view can also be thought of as a "stored query." Views do not actually contain or store data; they derive their data from the tables on which they are based.

In other words, it is useless to try to export ROWS when a view contains no rows at all.

It seems that it is a CREATE VIEW script that you are looking for. Do you have any of the GUI tools to connect to your Oracle database? Oracle's free product, SQL Developer might help here. I hope you'd be able to view a script and execute it in your target database.

If data is what you are interested in (and you don't mind whether it is a table, view or materialized view), perhaps you could create a table using the CREATE TABLE AS SELECT FROM VIEW and export this table instead.

Report message to a moderator

Re: Is it possible to export a view [message #265573 is a reply to message #265465]

Thu, 06 September 2007 11:56

andrew again
Messages: 2577
Registered: March 2000

Senior Member

create or replace type C2_TTyp as table of varchar2(2);
/

create or replace view v_C2_TTyp as (
SELECT x.column_value, y.dummy FROM TABLE (C2_TTyp ('A', 'B', 'C', 'D', 'E'))x, dual y);

select NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE, REFERENCED_LINK_NAME 
from user_dependencies 
where name  = 'V_C2_TTYP'
--where ...
;

NAME      TYPE    REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_LINK_NAME
--------- ------- ---------------- --------------- --------------- --------------------
V_C2_TTYP VIEW    SYS              DUAL            TABLE     
V_C2_TTYP VIEW    PUBLIC           DUAL            SYNONYM     
V_C2_TTYP VIEW    SCOTT            DUAL            NON-EXISTENT     
V_C2_TTYP VIEW    SCOTT            C2_TTYP         TYPE

[Updated on: Wed, 12 September 2007 03:50] by Moderator

Report message to a moderator