How we can easily backup metadata in an Oracle database

How we can easily backup metadata in an Oracle database
How we can easily backup metadata in an Oracle database

Sometimes we need to backup a procedure, a package, a function, etc. (the so-called metadata) so that we can see its code or keep it somewhere so that we can restore it if necessary.

In this article let's see an easy and fast way using a terminal window (sqlplus from command prompt / terminal window / Putty / PLSQL Developer command window).

The method we will use is the systematic package DBMS_METADATA. But before we call the package we have to connect via sqlplus ή command window to run the following parameters so that they appear to us in a correct form without losing a piece of the code.

The parameters

set long 1000000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
set echo off 
set termout off 
set head off 
set feed off 
set timing off
set echo off

   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);

Once we have executed the above, we can now run the package dbms_metadata so we can get the backup.

As a first step, we spool the file with the name we want to save in the folder we are in.

Then the package takes 3 parameters:

  • What type is it ('PROCEDURE','PACKAGE_BODY','PACKAGE','FUNCTION' etc.).
  • The name he has.
  • And which user / scheme does it belong to.

The code

spool proc_custom_BACKUP.txt
select dbms_metadata.get_ddl('PROCEDURE','PROC_CUSTOM','XRISTIS') from dual;
spool off

We can repeat the process as many times as we want by changing these 3 parameters and of course the name of the file in which we spool.

From there, programs like PLSQL Developer and Oracle SQL Developer offer us the possibility to do the dbms_metadata process also using graphics, by right-clicking on each object.


Oracle Database PL/SQL packages Reference

Share it

Leave a reply