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.
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?
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