DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Import view ?

  1. #1
    Join Date
    Jun 2001
    Posts
    103
    Hi All.
    Can I import only one view from a export file ?
    If yes, How ?
    Best Regards.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Import the underlying tables, the view will come along.




  3. #3
    Join Date
    Jun 2001
    Posts
    103
    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.


  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    :-))

    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.

    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.



  5. #5
    Join Date
    Oct 2000
    Posts
    467
    Work around would be to take the views from dba_views and spool it into a txt file and execute it .
    Vinit

  6. #6
    Join Date
    Nov 2001
    Location
    Chennai
    Posts
    22
    How about altering the definition of the view and revalidating it?




  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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;



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width