Scripts for migrating Procedures and Functions?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Scripts for migrating Procedures and Functions?

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    15

    Question


    I have two oracle databases in two different box and I wanted to copy
    functions and procedures of one box to other box.

    Can anybody give me any idea on migrating those functions and procedures?

    Thanks,

    SK

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Could you give me the details on it like the purpose of the task ? to create copy of database on new box or for something else. who owns these procedures? application schema or one of your developers ?


  3. #3
    Join Date
    Jan 2001
    Posts
    15
    Those two are for development and testing box for the application and ultimately testing
    box will be turned to a production box in future. I have Stored procedure
    and function compiled in the development box and I wanted to transfer those
    to testing box and compiled there.

    Actually I don't want to do copy and paste form of action of Oracle Enterprize Manager, I will be doing
    a lot of changes in the development box's procedures and functions in future so I am looking for script or tool which will enable me to retrieve the all compiled procedures and functions into file and I can do recompile in other box.

    I am handling the both databases and have dba access but
    I don't have experiences on DBA. I have created a user for the application and I am using that
    user to access to the database and corresponding all objects.


    SK




  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    export/import the whole schema with rows=n
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Thats about it. Thats what(Jeff mentioned) I was having in mind, thought of confirming your situation before advising you.

    Need further steps. Let us know !

    [Edited by sreddy on 01-31-2001 at 01:23 PM]

  6. #6
    Join Date
    Dec 2000
    Posts
    46
    The view "ALL_Source" or "DBA_Source" has all the source code you need.
    You can extract all the code from there but you will have to include the
    "Create or Replace Package/Procedure/Functinon" to the extracted output
    for each object. Its easy to write this script. There is column in that table that
    tells you what type of object it is. You will have to go from there.

    The other alternative might be to to export/import the user without the
    data (I have not tried this but its worth a try, try on a test schema first).

    Good luck!

    Thanks

  7. #7
    Join Date
    Jan 2001
    Posts
    15
    Thanks all for your suggestions.
    I appreciated that one.

    I like the first suggestion of gram2000. I think I will take
    that path to copy all the stored procedures and
    function from the source code table. I was really looking for the source code
    storage table. Gram2000 has provided me that. Thanks again Gram2000.

    sreddy,
    Just for curiosity, I looked at the alternative way of migrating those
    by using importing and exporting utility. I could not get the exact
    command for using the export utility to export the particular schema.
    Suppose I have user 'bill' has its all oracle object on a billschema.
    Any suggestion on exporting that schema for any user
    or a perticular user of other database?


    SK






  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    There is a parameter OWNER in exp utility. If you mention rows=n, OWNER=xyz it will export all the objects of that particular user.

    Else you can take FULL=Y as system user you get full export of the database and you can control the whatever users objects you wanted while importing to test database.

    Use

    from user=xyz
    to user=xyz.

    Make sure that user exists on the TEST database, before importing. If not just create user and default user to the right tablespace.






  9. #9
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    to get done quickly, Simple procedure is If you specific schemas hold procedures and functions. Try importing those schemas to a your testing database(to play) if you have one. and drop rest of all the objects except preocudres and functions mentioning OBJECT_TYPE in WHERE CLAUSE of Dropping objects SQL statement. You will be left with only functions and procedures.

    Export and Import that user to whatever schema you want
    using

    fromuser=xyz
    touser=abc


  10. #10
    Join Date
    Sep 2000
    Posts
    155

    Use any of the third party tools (eg: SQL*Navigator, TOAD, etc.). This method is very easy and saves lot of time.

    Free downloads are available (www.questsoftware.com)

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