giovedì 19 febbraio 2015

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.

Nessun commento:

Posta un commento