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?
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 ?
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.
export/import the whole schema with rows=n
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]
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).
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.
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?
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.
Make sure that user exists on the TEST database, before importing. If not just create user and default user to the right tablespace.
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
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)
Click Here to Expand Forum to Full Width