RBDMS ORACLE - Compare and Generate the Object Metadata(Structure) Difference in Oracle 11gR2:

In oracle esistono alcuni packages per effettuare analisi sulle strutture dati e per estrarre le differenze tra queste.
I packges di cui parlo sono DBMS_METADATA e DBMS_METADATA_DIFF.
Di seguito una descrizione delle funzioni e di cosa possano ricevere in input.

DESC DBMS_METADATA_DIFF
PROCEDURE                    Nome argomento Tipo           IN/OUT Predefinito
---------------------------- -------------- -------------- ------ -----------
...
COMPARE_ALTER (FUNCTION)     <return value> CLOB           OUT               
COMPARE_ALTER                OBJECT_TYPE    VARCHAR2       IN                
COMPARE_ALTER                NAME1          VARCHAR2       IN                
COMPARE_ALTER                NAME2          VARCHAR2       IN                
COMPARE_ALTER                SCHEMA1        VARCHAR2       IN     DEFAULT    
COMPARE_ALTER                SCHEMA2        VARCHAR2       IN     DEFAULT    
COMPARE_ALTER                NETWORK_LINK1  VARCHAR2       IN     DEFAULT    
COMPARE_ALTER                NETWORK_LINK2  VARCHAR2       IN     DEFAULT    
COMPARE_ALTER_XML (FUNCTION) <return value> CLOB           OUT               
...
COMPARE_SXML (FUNCTION)      <return value> CLOB           OUT               
...
FETCH_CLOB (1) (FUNCTION)    <return value> CLOB           OUT               
...
OPENC (FUNCTION)             <return value> NUMBER         OUT                
OPENC                        OBJECT_TYPE    VARCHAR2       IN                

DESC SYS.DBMS_METADATA
PROCEDURE                              Nome argomento     Tipo                        IN/OUT Predefinito
-------------------------------------- ------------------ --------------------------- ------ -----------
ADD_TRANSFORM (FUNCTION)               <return value>     NUMBER                      OUT               
CHECK_MATCH_TEMPLATE (FUNCTION)        <return value>     NUMBER                      OUT               
CHECK_MATCH_TEMPLATE_LOB (FUNCTION)    <return value>     NUMBER                      OUT               
CHECK_MATCH_TEMPLATE_PAR (FUNCTION)    <return value>     NUMBER                      OUT               
...
GET_CHECK_CONSTRAINT_NAME (FUNCTION)   <return value>     VARCHAR2                    OUT               
...
GET_DDL (FUNCTION)                     <return value>     CLOB                        OUT               
GET_DDL                                OBJECT_TYPE        VARCHAR2                    IN                
GET_DDL                                NAME               VARCHAR2                    IN                
GET_DDL                                SCHEMA             VARCHAR2                    IN     DEFAULT    
GET_DDL                                VERSION            VARCHAR2                    IN     DEFAULT    
GET_DDL                                MODEL              VARCHAR2                    IN     DEFAULT    
GET_DDL                                TRANSFORM          VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_DDL (FUNCTION)           <return value>     CLOB                        OUT               
GET_DEPENDENT_DDL                      OBJECT_TYPE        VARCHAR2                    IN                
GET_DEPENDENT_DDL                      BASE_OBJECT_NAME   VARCHAR2                    IN                
GET_DEPENDENT_DDL                      BASE_OBJECT_SCHEMA VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_DDL                      VERSION            VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_DDL                      MODEL              VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_DDL                      TRANSFORM          VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_DDL                      OBJECT_COUNT       NUMBER                      IN     DEFAULT    
GET_DEPENDENT_SXML (FUNCTION)          <return value>     CLOB                        OUT               
GET_DEPENDENT_SXML                     OBJECT_TYPE        VARCHAR2                    IN                
GET_DEPENDENT_SXML                     BASE_OBJECT_NAME   VARCHAR2                    IN                
GET_DEPENDENT_SXML                     BASE_OBJECT_SCHEMA VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_SXML                     VERSION            VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_SXML                     MODEL              VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_SXML                     TRANSFORM          VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_SXML                     OBJECT_COUNT       NUMBER                      IN     DEFAULT    
GET_DEPENDENT_XML (FUNCTION)           <return value>     CLOB                        OUT               
GET_DEPENDENT_XML                      OBJECT_TYPE        VARCHAR2                    IN                
GET_DEPENDENT_XML                      BASE_OBJECT_NAME   VARCHAR2                    IN                
GET_DEPENDENT_XML                      BASE_OBJECT_SCHEMA VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_XML                      VERSION            VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_XML                      MODEL              VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_XML                      TRANSFORM          VARCHAR2                    IN     DEFAULT    
GET_DEPENDENT_XML                      OBJECT_COUNT       NUMBER                      IN     DEFAULT    
GET_DOMIDX_METADATA (FUNCTION)         <return value>     TABLE                       OUT                 
GET_EDITION (FUNCTION)                 <return value>     VARCHAR2                    OUT               
GET_EDITION_ID (FUNCTION)              <return value>     NUMBER                      OUT               
GET_FK_CONSTRAINT_NAME (FUNCTION)      <return value>     VARCHAR2                    OUT               
GET_FK_CONSTRAINT_NAME                 HANDLE             NUMBER                      IN                
GET_GRANTED_DDL (FUNCTION)             <return value>     CLOB                        OUT               
GET_GRANTED_DDL                        OBJECT_TYPE        VARCHAR2                    IN                
GET_GRANTED_DDL                        GRANTEE            VARCHAR2                    IN     DEFAULT    
GET_GRANTED_DDL                        VERSION            VARCHAR2                    IN     DEFAULT    
GET_GRANTED_DDL                        MODEL              VARCHAR2                    IN     DEFAULT    
GET_GRANTED_DDL                        TRANSFORM          VARCHAR2                    IN     DEFAULT    
GET_GRANTED_DDL                        OBJECT_COUNT       NUMBER                      IN     DEFAULT    
GET_GRANTED_XML (FUNCTION)             <return value>     CLOB                        OUT               
... 
GET_HASHCODE (FUNCTION)                <return value>     RAW                         OUT               
...            
SET_REMAP_PARAM                        TRANSFORM_HANDLE   NUMBER                      IN                
SET_REMAP_PARAM                        NAME               VARCHAR2                    IN                
SET_REMAP_PARAM                        OLD_VALUE          VARCHAR2                    IN                
SET_REMAP_PARAM                        NEW_VALUE          VARCHAR2                    IN                
SET_REMAP_PARAM                        OBJECT_TYPE        VARCHAR2                    IN     DEFAULT    
... 

Queste funzioni oltre a restituire le DDL o le differenze tra oggetti, permettono anche di rimappare e rinominare tablespaces, nomi di oggetti ecc.

Di seguito alcuni esempi base di come si utilizzano.
Differenza tra due tabelle appartenenti allo stesso schema:

SELECT DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','T_ANSTA_SX_W','T_ANSTA_SX_V','WRES049','WRES049') FROM DUAL;

  ALTER TABLE "WRES049"."T_ANSTA_SX" DROP ("SYS_STU#QKM96$R5TPG8FS$3BTGQW_");
  ALTER TABLE "WRES049"."T_ANSTA_SX" MODIFY DEFAULT ATTRIBUTES  TABLESPACE "T4967_LIVE_32K_DAT";
  ALTER TABLE "WRES049"."T_ANSTA_SX" MOVE PARTITION "P_MAX" TABLESPACE "T4967_LIVE_32K_DAT";
  ALTER TABLE "WRES049"."T_ANSTA_SX" RENAME TO "T_ANSTA_SX_V";

Estrazione della ddl di una tabella ed di un indice:

-- Tabella
SELECT SYS.DBMS_METADATA.GET_DDL  ('TABLE','T_ANSTA_SX_W','WRES049') FROM DUAL;


  CREATE TABLE "WRES049"."T_ANSTA_SX_W"
   (    "COLONNA1" NUMBER(11,0) NOT NULL ENABLE,
    ...
       ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "T4967_LIVE_32K_DAT" 

-- Indice
 SELECT SYS.DBMS_METADATA.GET_DDL  ('INDEX','IX_T_ANSTA_SX_W_1','WRES049') FROM DUAL;

  CREATE UNIQUE INDEX "WRES049"."IX_T_ANSTA_SX_W_1" ON "WRES049"."T_ANSTA_SX_W" ("ELEMENTVALUE", "STARTDATE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "T4967_LIVE_32K_IDX"
 -- Grants

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'WRES049') FROM DUAL;

  GRANT ADMINISTER SQL TUNING SET TO "WRES049"
  GRANT EXECUTE ANY PROGRAM TO "WRES049"
  GRANT CREATE ANY JOB TO "WRES049"
  GRANT CREATE JOB TO "WRES049"
  GRANT ADVISOR TO "WRES049"
 ...
  GRANT UNLIMITED TABLESPACE TO "WRES049"
  GRANT CREATE SESSION TO "WRES049"
  GRANT ALTER SYSTEM TO "WRES049"

 Di seguito i possibili oggetti type SQL che si possono comparare col metadata_diff sono :

• CLUSTER, CONTEXT, DB_LINK, FGA_POLICY, INDEX, MATERIALIZED_VIEW, MATERIALIZED_VIEW_LOG, QUEUE, QUEUE_TABLE, RLS_CONTEXT, RLS_GROUP, RLS_POLICY, ROLE, SEQUENCE, SYNONYM, TABLE, TABLESPACE, TRIGGER, TYPE, TYPE_SPEC, TYPE_BODY, USER, and VIEW

 Per quanto riguarda invece il package METADATA i possibili type sono i seguenti dove la colonna Attributes indica rispettivamente:

• S represents a schema object,  
• N represents a named object, 
• D represents a dependent object,  
• G represents a granted object,  
• H represents a heterogeneous object.:

 DBMS_METADATA: Object Types

Type Name	Meaning	Attributes	Notes
AQ_QUEUE	queues	SND	Dependent on table
AQ_QUEUE_TABLE	additional metadata for queue tables	ND	Dependent on table
AQ_TRANSFORM	transforms	SN	None
ASSOCIATION	associate statistics	D	None
AUDIT	audits of SQL statements	DG	Modeled as dependent, granted object. The base object name is the statement audit option name (for example, ALTER SYSTEM). There is no base object schema. The grantee is the user or proxy whose statements are audited.
AUDIT_OBJ	audits of schema objects	D	None
CLUSTER	clusters	SN	None
COMMENT	comments	D	None
CONSTRAINT	constraints	SND	Does not include: primary key constraint for IOT column NOT NULL constraints certain REF SCOPE and WITH ROWID constraints for tables with REF columns
CONTEXT	application contexts	N	None
DATABASE_EXPORT	all metadata objects in a database	H	Corresponds to a full database export
DB_LINK	database links	SN	Modeled as schema objects because they have owners. For public links, the owner is PUBLIC. For private links, the creator is the owner.
DEFAULT_ROLE	default roles	G	Granted to a user by ALTER USER
DIMENSION	dimensions	SN	None
DIRECTORY	directories	N	None
FGA_POLICY	fine-grained audit policies	D	Not modeled as named object because policy names are not unique.
FUNCTION	stored functions	SN	None
INDEX_STATISTICS	precomputed statistics on indexes	D	The base object is the index's table.
INDEX	indexes	SND	None
INDEXTYPE	indextypes	SN	None
JAVA_SOURCE	Java sources	SN	None
JOB	jobs	S	None
LIBRARY	external procedure libraries	SN	None
MATERIALIZED_VIEW	materialized views	SN	None
MATERIALIZED_VIEW_LOG	materialized view logs	D	None
OBJECT_GRANT	object grants	DG	None
OPERATOR	operators	SN	None
OUTLINE	stored outlines	N	This type is being deprecated.
PACKAGE	stored packages	SN	By default, both package specification and package body are retrieved. See "SET_FILTER Procedure".
PACKAGE_SPEC	package specifications	SN	None
PACKAGE_BODY	package bodies	SN	None
PROCEDURE	stored procedures	SN	None
PROFILE	profiles	N	None
PROXY	proxy authentications	G	Granted to a user by ALTER USER
REF_CONSTRAINT	referential constraint	SND	None
REFRESH_GROUP	refresh groups	SN	None
RESOURCE_COST	resource cost info		None
RLS_CONTEXT	driving contexts for enforcement of fine-grained access-control policies	D	Corresponds to the DBMS_RLS.ADD_POLICY_CONTENT procedure
RLS_GROUP	fine-grained access-control policy groups	D	Corresponds to the DBMS_RLS.CREATE_GROUP procedure
RLS_POLICY	fine-grained access-control policies	D	Corresponds to DBMS_RLS.ADD_GROUPED_POLICY. Not modeled as named objects because policy names are not unique.
RMGR_CONSUMER_GROUP	resource consumer groups	SN	Data Pump does not use these object types. Instead, it exports resource manager objects as procedural objects.
RMGR_INTITIAL_CONSUMER_GROUP	assign initial consumer groups to users	G	None
RMGR_PLAN	resource plans	SN	None
RMGR_PLAN_DIRECTIVE	resource plan directives	D	Dependent on resource plan
ROLE	roles	N	None
ROLE_GRANT	role grants	G	None
ROLLBACK_SEGMENT	rollback segments	N	None
SCHEMA_EXPORT	all metadata objects in a schema	H	Corresponds to user-mode export.
SEQUENCE	sequences	SN	None
SYNONYM	synonyms	See notes	Private synonyms are schema objects. Public synonyms are not, but for the purposes of this API, their schema name is PUBLIC. The name of a synonym is considered to be the synonym itself. For example, in CREATE PUBLIC SYNONYM FOO FOR BAR, the resultant object is considered to have name FOO and schema PUBLIC.
SYSTEM_GRANT	system privilege grants	G	None
TABLE	tables	SN	None
TABLE_DATA	metadata describing row data for a table, nested table, or partition	SND	For partitions, the object name is the partition name. For nested tables, the object name is the storage table name. The base object is the top-level table to which the table data belongs. For nested tables and partitioning, this is the top-level table (not the parent table or partition). For nonpartitioned tables and non-nested tables this is the table itself.
TABLE_EXPORT	metadata for a table and its associated objects	H	Corresponds to table-mode export
TABLE_STATISTICS	precomputed statistics on tables	D	None
TABLESPACE	tablespaces	N	None
TABLESPACE_QUOTA	tablespace quotas	G	Granted with ALTER USER
TRANSPORTABLE_EXPORT	metadata for objects in a transportable tablespace set	H	Corresponds to transportable tablespace export
TRIGGER	triggers	SND	None
TRUSTED_DB_LINK	trusted links	N	None
TYPE	user-defined types	SN	By default, both type and type body are retrieved. See "SET_FILTER Procedure".
TYPE_SPEC	type specifications	SN	None
TYPE_BODY	type bodies	SN	None
USER	users	N	None
VIEW	views	SN	None
XMLSCHEMA	XML schema	SN	The object's name is its URL (which may be longer than 30 characters). Its schema is the user who registered it.

Nel prossimo articolo vedremo delle semplici funzioni di esempio su come estrarre le ddl e manipolarle.