DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: import

  1. #1
    Join Date
    Jun 2003
    Posts
    108

    import

    Hi DBAs,

    I want to import a particular procedure from an export dump file.

    Is there any way to import the procedure,, bcoz my developer

    wrongly droped an important procedure.

    Ple give some input if anything possible.

    Thanx in advance.

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Code:
    import show=y
    You should be able to see the code of the procedure. Just copy the code and create the procedure.
    or If you have the procedure in any other database or schema just query the code from DBA_SOURCE and create the procedure.

    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Jun 2003
    Posts
    108
    Dear Sanjay,

    What do u mean? ,, please give some detail explination on this.

    import show=y

    what does mean.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

  5. #5
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511
    First there is no way to import just the procedure you want. If you do not know what show=y is, you can try this:

    1.create a user called dummy.
    2.import the dump file into user dummy using FROMUSER=EXPORTUSER, TOUSER=DUMMY params.
    3.Using TOAD, sql*navigator, OEM or ALL_SOURCE data dictionary view get the procedure code. Example:

    SQL> select text from all_source where name='PSTUB';

    procedure pstub(pname varchar2, uname varchar2,
    stubSpec in out varchar2, stubText in out varchar2,
    flags varchar2 := '6') is
    rc varchar2(40);
    ty varchar2(5);
    cursor tub (una varchar2, dbna varchar2, luna varchar2, luty varchar2) is
    select line from sys.pstubtbl
    where (una is null or username = una) and
    (dbna is null or dbname = dbna) and
    lun = luna and lutype = luty
    order by lineno;
    begin -- main
    sys.pstubt(pname, uname, '', flags, rc);

    if rc like '$$$%' then stubText := rc; return; end if;
    if not (rc = 'PKG' or rc = 'SUB')
    then stubText := '$$$ other'; return;
    end if;
    stubSpec := '';
    stubText := '';
    if rc = 'PKG' then
    for s in tub(uname, '', pname, 'PKS') loop
    stubSpec := stubSpec || s.line;
    end loop;
    end if;
    if rc = 'PKG' then ty := 'PKB'; else ty := 'SUB'; end if;
    for s in tub(uname, '', pname, ty) loop

    stubText := stubText || s.line;
    end loop;
    end;

    29 rows selected.
    4. Spool the code to a script and execute the script to create the procedure the original schema.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by kris109
    3.Using TOAD, sql*navigator, OEM or ALL_SOURCE data dictionary view get the procedure code. Example:

    SQL> select text from all_source where name='PSTUB';
    Er, I would at least add ORDER BY clause to the above query, ie
    Code:
    select text from all_source where name='PSTUB'
    order by line;
    Also, an owner specification should be added to the WHERE clause, ie
    Code:
    select text from all_source
    where name='PSTUB' and owner = 'DUMMY'
    order by line;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Er, I would at least add ORDER BY clause to the above query, ie
    Code:
    select text from all_source where name='PSTUB'
    order by line;
    Well, is that needed?

    PS text code when its coming in ordered way ( by line ), then why use?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by abhaysk
    Well, is that needed?

    PS text code when its coming in ordered way ( by line ), then why use?
    It is very much needed. Oracle doesn't gurantee that rows will be returend in sorted manner unless you specify 'order by'.

    Imagine you are querying a 5000 lines of code and the lines aren't sorted.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  9. #9
    Join Date
    Jun 2003
    Posts
    108
    Hi everybody,,,

    Thanx for ur reply..

    My problem is I don't have more space in my DB to import the entire dump file and the size of dump is 1.2 GB.

    Is there anyother way to do this.

    Thanx in advance.

  10. #10
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    import only the objects of the user who dropped that
    important procedure.

    -Raja

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