Import view ?

Hi All.
Can I import only one view from a export file ?
If yes, How ?
Best Regards.

Import the underlying tables, the view will come along.

Hi julian.
number of underlying tables of this view is 20 , and structure of this tables changed from export date, thus I can't import underlying tables .
I think oracle must add parameters like (VIEWS=...,PROCEDURES=...,FUNCTIONS=....,PACKAGE=....) or (OBJECTS=....) to IMP/EXP utility.
I do this with (IMP ROWS=N CONSTRAINTS=N .....SHOW=Y) and then extract view source from log file.
Is there better way?

Can I do this with searching view name in .DMP file and then extract view source from .DMP file?
My .DMP file size is 1.2 GB and my editors(NotePad or Wordpad in windows) can't edit this file.

Sorry for my english!!!
Best regards.

Quote:

---

Originally posted by mahoori
Hi julian.
number of underlying tables of this view is 20 , and structure of this tables changed from export date, thus I can't import underlying tables .
I think oracle must add parameters like (VIEWS=...,PROCEDURES=...,FUNCTIONS=....,PACKAGE=....) or (OBJECTS=....) to IMP/EXP utility.

---

:-))

Quote:

---

I do this with (IMP ROWS=N CONSTRAINTS=N .....SHOW=Y) and then extract view source from log file.
Is there better way?

---

SHOW=Y displays the contents of the export file but does not cause IMPORT to import anything.

Quote:

---

Can I do this with searching view name in .DMP file and then extract view source from .DMP file?
My .DMP file size is 1.2 GB and my editors(NotePad or Wordpad in windows) can't edit this file.

---

You should not edit .DMP files with notepad!
I usually "import" my view onto a flat file with SQL Navigator. It is the easiest way in my opinion.

Work around would be to take the views from dba_views and spool it into a txt file and execute it .

How about altering the definition of the view and revalidating it?

One of varians for recreating views:
------------------------------------------
-- prepared statments
------------------------------------------
create or replace
function list_tab_cols( p_owner varchar2, p_table varchar2) return varchar2
is
str varchar2(1000);
sep varchar2(3);
begin
str := '';
sep := ' ';
for rc in (select COLUMN_NAME
from DBA_TAB_COLUMNS
where owner = p_owner
and
table_name = p_table
order by COLUMN_ID)
loop
str := str || sep || rc.COLUMN_NAME ;
sep := ','|| chr(10) || ' ';
end loop;
return str;
exception
when others then return null;
end;
/
show errors;


create or replace view v_list_tab_cols
(
owner ,
table_name ,
col_list ,
obj_type
)
as select
owner ,
table_name,
list_tab_cols(owner, table_name),
'TABLE'
from dba_tables
union
select
owner ,
view_name,
list_tab_cols(owner, view_name),
'VIEW'
from dba_views
;


------------------------------------------
-- example of select
------------------------------------------
set long 20000
set linesize 120
set pagesize 0

select 'create or replace view '||chr(10)||
c.owner||'.'||c.view_name||chr(10)||
'('||chr(10)||
l.col_list||chr(10)||
')'||chr(10)||
'as ',
c.text
from dba_views c,
v_list_tab_cols l
where c.owner = 'SYSTEM'
and
c.owner = l.owner
and
c.view_name = l.table_name;