Basic SQLs – EBS DB Custom Object Backup

 

Business Need:

Backup DB object source codes (Tables, Functions, Packages, Triggers, etc.) from a development instance to local machine. Can be used before cloning to avoid loss of development work.

Note: This will back up only database objects. Any Unix (or other OS) files are not backed up by this, including:

• Ø Java Packages, sources
• Ø Shell Scripts
• Ø JSP Files, other middle tier files

They have to be backed up manually.

 

Tables:

All custom tables

 

SQLs:

Get Cloned date of current development instance:

 

-- get cloned date
SELECT resetlogs_time FROM v$database;
 
06/26/2016 9:44:39 PM

 

Get a list of all custom objects, created or changed after cloning date (substitute date from above SQL). Replace custom schema names:

-- all recent custom objects - change date
 
select *
from dba_objects
where LAST_DDL_TIME > TO_DATE('06/26/2016 9:44:39 PM', 'MM/DD/YYYY HH:MI:SS AM')
-- created > TO_DATE('06/26/2016 9:44:39 PM', 'MM/DD/YYYY HH:MI:SS AM')
and ((object_name like 'O%') OR (object_name like 'XXAAA%') OR (object_name like 'XXBBB%') OR (object_name like 'XXCCC%'))
and owner in ('APPS', 'XXAAA', 'XXBBB', 'XXCCC')
order by owner, object_type, object_name;
 
 
-- get source codes of all APPS-Owned objects
-- to remove Line Feed, replace \r\n"\r\n with "\r\n
select src.owner, src.name, src.type, src.line, src.text
from dba_source src
where owner in ('APPS', 'XXAAA', 'XXBBB', 'XXCCC')
and ((src.name like 'OXX%') OR (src.name like 'UHXX%') OR (src.name like 'KESX%') OR (src.name like 'SSXX%'))
and exists (select *
    from dba_objects obj
    where LAST_DDL_TIME > TO_DATE('7/29/2020 5:54:47 PM', 'MM/DD/YYYY HH:MI:SS AM')
    and ((object_name like 'O%') OR (object_name like 'XXAAA%') OR (object_name like 'XXCCC%') OR (object_name like 'XXBBB%'))
    and owner in ('APPS', 'XXAAA', 'XXBBB', 'XXCCC')
    and src.name = obj.object_name
    and src.owner = obj.owner)
order by src.owner, src.type, src.name, src.line asc;
 
 

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications, Query, SQL, SQLPLUS