How we can easily backup metadata in an Oracle database
Certifications:
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 begin dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true); dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true); end; /
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.
