Home » RDBMS Server » Server Utilities » Is it possible to export a view
Is it possible to export a view [message #265446] Thu, 06 September 2007 06:01 Go to next message
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
Re: Is it possible to export a view [message #265450 is a reply to message #265446] Thu, 06 September 2007 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
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
Re: Is it possible to export a view [message #265457 is a reply to message #265450] Thu, 06 September 2007 06:29 Go to previous messageGo to next message
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"
Re: Is it possible to export a view [message #265465 is a reply to message #265446] Thu, 06 September 2007 06:53 Go to previous messageGo to next message
Littlefoot
Messages: 21823
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.
Re: Is it possible to export a view [message #265573 is a reply to message #265465] Thu, 06 September 2007 11:56 Go to previous messageGo to next message
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

Re: Is it possible to export a view [message #266989 is a reply to message #265573] Wed, 12 September 2007 03:37 Go to previous messageGo to next message
d.c.b.a
Messages: 44
Registered: March 2005
Location: China
Member

Hi:

Try to unload the rows as text file.


Good tools make work easy and improve life quality.
http://www.dbatools.net

Re: Is it possible to export a view [message #266996 is a reply to message #265446] Wed, 12 September 2007 03:50 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What are you advertising?
Re: Is it possible to export a view [message #267262 is a reply to message #266996] Wed, 12 September 2007 21:17 Go to previous message
d.c.b.a
Messages: 44
Registered: March 2005
Location: China
Member

Arju wrote on Wed, 12 September 2007 04:50
What are you advertising?


I have thought that he need to move the data.
Previous Topic: Data pump to transfer file from previous version of Oracle
Next Topic: Having Problems with SQL Loader
Goto Forum:
  


Current Time: Wed Dec 25 10:15:49 CST 2024