23 May, 2015

How to take the Backup of different types of Objects

col OWNER for a10
col OBJECT_NAME for a30
col OBJECT_TYPE for a20
set linesize 150
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS, LAST_DDL_TIME from dba_objects where OBJECT_NAME like '&objectname';

Package Backup

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool 'filename.pks'
select text from dba_source where name='objectname' and type='PACKAGE';
spool off
exit

Package Body Backup

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool filename.pkb
select text from dba_source where name='objectname' and type='PACKAGE BODY';
spool off
exit


Backup of Triggers

set pagesize 0
set trimspool on
set heading off
set feedback off
set long 4000
spool filename.sql
select TRIGGER_BODY from dba_triggers where TRIGGER_NAME='&a' and OWNER='&a';
spool off


Backup of Function 

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool filename.sql
select text from dba_source where name='objectname' and type='FUNCTION';
spool off
exit

View backup


Need to perform as sys user

set long 999999999
set pagesize 5000
spool filename.sql
select dbms_metadata.get_ddl('VIEW','objectname','ownerof the object') from dual;
spool off

Index backup


Need to perform as sys user

set long 999999999
set pagesize 5000
spool filename.sql
select dbms_metadata.get_ddl('INDEX','objectname','ownerof the object') from dual;
spool off

No comments:

Post a Comment

OEM Agent Installation

./agentDeploy.sh AGENT_BASE_DIR=/u001/oracle/product/agent13c OMS_HOST=oem-dev.xxx.com EM_UPLOAD_PORT=1159 AGENT_REGISTRATION_PASSWORD=*****...