how will you take only stored procedure backup in oracle 10g?

Question

how will you take only stored procedure backup in oracle 10g?

Answer 1

Tools like TOAD offer a feature to export the source code of stored procedures, functions, packages, triggers etc.

If you don't mind exporting table structure (without content) as well,

exp user/password file=emptybackup.dmp owner=myschema rows=n

should do the trick.

Answer 2

ammoQ's answer is correct.

To take it a bit further, if you want just the stored procs without the table structure, you will need to connect to the database and use SQL (i.e. with sqlplus or something). Then, using a list of the stored procs you are interested in, call the dbms_metadata function. You can use sqlplus to do something like this:

SELECT dbms_metadata.get_ddl('PROCEDURE','PROC1') FROM dual;

which will give you the source of procedure PROC1.

Also there is a view called USER_SOURCE, which you can use something like this:

select * from user_source where type in ('PROCEDURE', 'PACKAGE', 'PACKAGE_BODY', 'FUNCTION', 'TRIGGER');

which gives you the source for everything owned by the user you are logged in as.

Answer 3

If you're not taking care of configuration management, i.e. not keeping your stored procedures in a proper source control application (e.g. CVS, Subversion, MSS) you're simply not doing the right thing. Even a one-man team should use a version control system, for any non-trivial work.

Read up on Revision control