Databases Reference
In-Depth Information
Finalizing the Patch
The finalization of the patch is for the most part just the extraction of the current sources from the
database in order to include them in the patch script and make sure that you have all changes. You can
extract the sources manually using development environments like Quest Toad, Oracle SQL Developer,
or the PL/SQL Developer from Allround Automations. They enable you to spool all sources from a
certain database schema into the file system.
Another option is to extract the sources using custom scripts based on the Oracle standard package
DBMS METADATA
. This is the approach we have taken.
First, extract all DDL statements from all objects in the specified schema into the table
XLIB DDL STATEMENTS
. In Listing 9-11 you can see parts of the script; the full script can be downloaded at
http://www.opal-consulting.de/tools
.
Listing 9-11.
Script to Extract the DDL Statements for All Database Objects
TRUNCATE TABLE xlib ddl statements;
col CURR USER new value USR
select user CURR USER from dual;
SET timing off
SET LINESIZE 132 PAGESIZE 0 FEEDBACK off VERIFY off TRIMSPOOL on LONG 1000000
set sqlblanklines on
COLUMN ddl string FORMAT A100 WORD WRAP
COLUMN row order FORMAT 999 NOPRINT
EXECUTE DBMS METADATA.SET TRANSFORM PARAM(DBMS METADATA.SESSION TRANSFORM,'STORAGE',false);
EXECUTE DBMS METADATA.SET TRANSFORM PARAM(DBMS METADATA.SESSION TRANSFORM,'PRETTY',true);
EXECUTE DBMS METADATA.SET TRANSFORM PARAM(DBMS METADATA.SESSION TRANSFORM,'SQLTERMINATOR',
true);
EXECUTE DBMS METADATA.SET TRANSFORM PARAM(DBMS METADATA.SESSION TRANSFORM,'REF CONSTRAINTS',
false);
EXECUTE DBMS METADATA.SET TRANSFORM PARAM(DBMS METADATA.SESSION TRANSFORM,
'CONSTRAINTS AS ALTER', true);
alter session set NLS NUMERIC CHARACTERS='.,';
------------------------------------------------------------------------------
PROMPT -- PROFILES
------------------------------------------------------------------------------
INSERT INTO xlib ddl statements
(ddl object name, ddl object type, ddl base object, ddl text)
SELECT PROFILE, 'PROFILE', 'PROFILE',
DBMS METADATA.get ddl ('PROFILE', profile) ddl string
FROM (SELECT PROFILE
FROM dba users
WHERE username = '&&USR'
AND profile != 'DEFAULT') pr
/
