How we can easily backup metadata in an Oracle database

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

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.

Sources:

Oracle Database PL/SQL packages Reference