how to get unique constraint information for oracle db?

Help
Matt
2013-10-31
2014-03-28
  • Matt
    Matt
    2013-10-31

    I am unable to extract any unique constraint information with schema crawler. Not sure if this is a database dependant issue or if it is in general possible with schema crawler.
    If I have e.g. a oracle table with the following ddl:

    CREATE TABLE
        TEST
        (
            A INTEGER NOT NULL,
            B INTEGER,
            C INTEGER,
            PRIMARY KEY (A),
            CONSTRAINT TEST_IX1 UNIQUE (C, B, A)
        );
    

    and try to extract the unique constraint with the following code fragment:

            BundledDriverOptions bdo = new BundledDriverOptions();
            SchemaCrawlerOptions options =bdo.getSchemaCrawlerOptions(InfoLevel.maximum);
            options.getSchemaInfoLevel().setRetrieveForeignKeys(true);
            options.getSchemaInfoLevel().setRetrieveCheckConstraintInformation(true);
            options.getSchemaInfoLevel().setRetrieveAdditionalColumnAttributes(true);
            options.getSchemaInfoLevel().setRetrieveIndices(true);
            options.getSchemaInfoLevel().setRetrieveRoutines(true);
            options.getSchemaInfoLevel().setRetrieveAdditionalTableAttributes(true);
            options.getSchemaInfoLevel().setRetrieveAdditionalDatabaseInfo(true);
            options.getSchemaInfoLevel().setRetrieveTablePrivileges(true);
            Database database = SchemaCrawlerUtility.getDatabase(con, options);
            Schema schema = database.getSchema("XYZ");
    
            Table table =  database.getTable(schema, "TEST");
            System.out.println(table.getCheckConstraints());
    

    Unfortunately the table object does not contain any information about the constraint. Is it possible to extract unique constraint information with schemaCrawler? I am already using the oracle specific bundled driver options package.

    Many thanks,
    Matthias

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2013-10-31

    Matthias,

    I will try to reproduce, and see if it is a database/ driver issue.

    Sualeh.

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2013-10-31

    It works just fine for me. Here is what I get:

    SCHEMACRAWLER.TEST
    ------------------------------------------------------------------------
                                                                     [table]
      A                                 NUMBER not null             
      B                                 NUMBER                      
      C                                 NUMBER
    
    SYS_C003762                                                [primary key]
      A                                 unknown
    
    TEST_IX1                                        [unique clustered index]
      C                                 unknown                     
      B                                 unknown                     
      A                                 unknown
    
      CREATE UNIQUE INDEX "SCHEMACRAWLER"."TEST_IX1" ON "SCHEMACRAWLER"."TEST" ("C", "B", "A") 
      PCTFREE 10 INITRANS 2 MAXTRANS 255 
      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 "USERS"
    
    SYS_C003761                                           [check constraint]
    "A" IS NOT NULL
    
     
  • Sualeh Fatehi
    Sualeh Fatehi
    2013-10-31

    Can you try:

    System.out.println(table.getIndices());
    
     
    Last edit: Sualeh Fatehi 2013-10-31
  • Matt
    Matt
    2013-11-04

    attached you find the output of table.getIndices(); so I get the index information for that constraint (also that it is a unique index), but nothing about the constraint itself

     
    Attachments
  • Matt
    Matt
    2013-11-04

    please note, that I am interested in "unique constraints", not in unique indices. And there is no direct relation (at least not in oracle). Oracle needs not to create a unique index for a unique constraint, if there is already another index which could be used by oracle to check the constraint (it even does not need to be a unique index!). Therefore it is not enough to simply inspect the indices of a table.
    E.g. If I have the following table with a simple (not unique) index over all three columns:

    CREATE TABLE
        TEST2
        (
            A INTEGER NOT NULL,
            B INTEGER,
            C INTEGER,
            PRIMARY KEY (A)
        );
    
    CREATE INDEX    testIndex
    ON
        TEST2
        (
            A,
            B,
            C
        );
    

    and if I then define a unique constraint for the columns A and B like:

    ALTER TABLE
        TEST2 ADD CONSTRAINT TEST3_ix2 UNIQUE (A, B)
    

    In this case oracle will not create another unique index containing A,B, but instead it will simply use the existing index for (A,B,C). And this index needs even not to be unique for this purpose.

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2013-11-04

    Matt,

    While what you are saying may be true, in your first example, Oracle actually created an index. SchemaCrawler can only present the information that the Oracle JDBC driver provides. In fact, the driver also provided the CREATE INDEX statement.

    I will see what the Oracle JDBC driver reports with your second example.

    Sualeh.

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2013-11-05

    Ok, that second example was good. I will have a fix for you shortly.

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2013-11-06

    Matthias,

    An update - I have converted CheckConstraint into a new class called TableConstraint. This will get you all of the primary key, foreign key, unique and check constraints available in the database. For Oracle, you will also get the DDL for these. I will be releasing SchemaCrawler 10.7 shortly, as soon as I have finished testing.

    Please note the change to the API:

    System.out.println(table.getTableConstraints());
    

    Sualeh.

     
    Last edit: Sualeh Fatehi 2013-11-06
  • Sualeh Fatehi
    Sualeh Fatehi
    2013-11-07

    Matthias,

    Please download and use SchemaCrawler 10.7 for Oracle.

    Sualeh.

     
  • Matt
    Matt
    2013-11-08

    Thanks Sualeh,

    I have retested my example with 10.7 for Oracle. And I get now results for the unique constraint. What I am still missing is where to get the information about the involved columns.

    So on the example above,

    CREATE TABLE
        TEST
        (
            A INTEGER NOT NULL,
            B INTEGER,
            C INTEGER,
            PRIMARY KEY (A),
            CONSTRAINT TEST_IX1 UNIQUE (C, B, A)
        );
    

    where do I find in the TableConstraint class that the columns C, B and A belong to this unique constraint?

    Matthias

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2013-11-08

    Matt,

    I will put support for that next. I will have to query ALL_CONS_COLUMNS, and make that available in the TableConstraint object.

    Sualeh.

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2013-11-11

    Matt,

    Try SchemaCrawler 10.8. I have added in details of columns constrained by table constraints.

    Sualeh.

     
  • Matt
    Matt
    2013-11-15

    Thanks Sualeh,

    it works now perfect! :)

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2013-11-15

    Great - glad to hear that!

     
  • Hi Sualeh,

    thanks for the great tool you provide, i want to use it to compare two schemas.

    I use schemacrawler-oracle-10.08.02:
    sc -database=db -user=user -password=pwd -schemas=user -infolevel=detailed -command=schema -noinfo -portablenames -outputfile=user_detailed_schema.txt

    The output includes:

    ALTER TABLE "user"."table" ADD CONSTRAINT "XXX_CON_PK" PRIMARY KEY ("ID")
    USING INDEX 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 "tablespace" ENABLE

    I would expect the suppression of the schema-name by using the option -portablenames.
    I want also to suppress the storage-/segments-attributes and tablespacenames ?
    Is this maybe possible by adjusting select.INFORMATION_SCHEMA.EXT_TABLE_CONSTRAINTS or by some new options in schemacrawler.config.properties.

    In ORACLE this could be achieved by:
    suppressing storage-information: DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE=>DBMS_METADATA.SESSION_TRANSFORM, name=>'STORAGE', value=>false);
    suppressing segment-atributes: DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE=>DBMS_METADATA.SESSION_TRANSFORM, name=>'SEGMENT_ATTRIBUTES', value=>false);
    suppressing tablespace: DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE=>DBMS_METADATA.SESSION_TRANSFORM, name=>'TABLESPACE', value=>false);

    Regards Günter

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2014-03-07

    Günter,

    That is interesting. I did not know this - I am no Oracle expert. I think it is a great idea to make these changes that you suggest. I will have to put in support to execute the metadata procedures that you mention. I will get right to working on this.

    Sualeh.

     
  • Hi Sualeh,

    would appreciate that feature to configure ORACLE remap-parameter over the schemacrawler.config.properties :)

    I found a way to suppress the schema-name in the generated ddl:
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'EMIT_SCHEMA', false);

    Have also a loook at ORACLE Documentation about DBMS_METADATA:
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm#BGBBIEGA

    Would be great if i could configure the ddl-generation for following transformations(i use some selfdefined queries to get additional needed data, but struggle with unwanted details, which prevent me from comparing two schemas)

    -- If TRUE, format the output with indentation and line feeds. Defaults to TRUE.
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', false);

    -- If TRUE, append a SQL terminator (; or /) to each DDL statement. Defaults to FALSE.
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);

    -- If TRUE, emit all non-referential table constraints. Defaults to TRUE.
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);

    -- If TRUE, emit table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements. If FALSE, specify table constraints as part of the CREATE TABLE statement. Defaults to FALSE. Requires that CONSTRAINTS be TRUE.
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true);

    -- If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false);

    -- If TRUE, emit the BYTE keyword as part of the size specification of CHAR and VARCHAR2 columns that use byte semantics. If FALSE, omit the keyword. Defaults to FALSE.
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD', true);

    -- If TRUE, emit partitioning clauses; if FALSE, suppress them. Defaults to TRUE.
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PARTITIONING', false);

    -- If TRUE, emit all referential constraints (foreign keys). Defaults to TRUE.
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', true);

    -- If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', true);

    -- If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', false);

    -- If TRUE, emit the type specification. Defaults to TRUE
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SPECIFICATION', false);

    -- If TRUE, emit the type body. Defaults to TRUE
    EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'BODY', true);

    Regards Günter

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2014-03-23

    Günter,

    Please download SchemaCrawler 10.08.03. It has significant improvements for Oracle. Let me know how it work for you. Thanks.

    Sualeh.

     
  • Hi Sualeh,

    thanks for your effort - the added schemacrawler-oracle.before.sql and schemacrawler-oracle.show_unqualified_names.sql do their job.

    But strangely i have now the problem, that following compare show for two equal schemas in one all source for the [foreign key constraint] and [non-unique clustered index] and in the other nothing.

    commands:
    sc -database=<db> -user=<user1> -password=<pwd1> -schemas=<user1> -infolevel=detailed -command=schema -noinfo -portablenames -outputfile=<user1>_detailed_schema.txt
    sc -database=<db> -user=<user2> -password=<pwd2> -schemas=<user2> -infolevel=detailed -command=schema -noinfo -portablenames -outputfile=<user2>_detailed_schema.txt

    If i execute select.INFORMATION_SCHEMA.TABLE_CONSTRAINTS out of schemacrawler-oracle.config.properties, i get the correct results in both schemas ???

    Additional i have following issue:
    We are using partitioning and compression both informations are not yet shown e.g. "COMPRESS FOR OLTP" and "PARTITION BY RANGE ("PARTITIONING_TIME")", also we need some additional information which we can get from following queries defined in schemacrawler.config.properties:

    query.user_source=SELECT line, RTRIM(text,CHR(10)) text FROM user_source WHERE type != 'TYPE' ORDER BY type, name, line
    query.user_sequences=SELECT sequence_name, min_value, max_value, increment_by, cycle_flag, order_flag, cache_size FROM user_sequences WHERE NOT REGEXP_LIKE(sequence_name, '^MDRS|^SYS_IOT') ORDER BY sequence_name
    query.user_tables=SELECT dbms_lob.substr(DBMS_METADATA.get_ddl (object_type=>'TABLE', name=>table_name),4000) ddl FROM user_tables WHERE NOT REGEXP_LIKE(table_name, '^MDRS|^SYS_IOT') ORDER BY table_name

    Sadly now the needed information about partitioning and advanced compression that should been shown by the query.user_tables is now suppressed by the schemacrawler-oracle.before.sql. Is there any possibility to configure the call of this script ?

    What do you think about a additional infolevel (-infolevel=native) providing the native database information according to the INFORMATION_SCHEMA-Selects ?

    Regards Günter

     
    Last edit: Günter Leimbeck 2014-03-27
  • Sualeh Fatehi
    Sualeh Fatehi
    2014-03-27

    Günter, please email me at sualeh@hotmail.com, so I can get clarifications on what you need. Thanks.

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2014-03-28

    Günter,

    You can add a query to your properties file like this:

    query.segment_attributes={call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', TRUE)}

    Call this query before you call your table query, with:
    -command=query.segment_attributes,query.user_tables

    That should solve your problem.

     
  • Hi Sualeh,

    thanks for the tip {call} within a query - to omit the -portablenames(trigger obviously the schemacrawler-oracle.before.sql and schemacrawler-oracle.show_unqualified_names.sql) and using this method solved the problem regarding my query.user_tables.

    Regarding my problem with the compare of the two schemas i tested also with previous versions(10.7 and 10.8.02) and the error occors also there.

    I changed schemacrawler-oracle.config.properties to use instead of the ALL_ views directly the USER_ views and now the output is correct for both schemas.

    Maybe the error has to do with the schema names in my example LMS_XX and LMS_ZZ ? The output for LMS_XX is complete - the output for LMS_ZZ is missing the [foreign key constraint] and [non-unique clustered index] entries.

    Regards Günter

     
    Last edit: Günter Leimbeck 2014-03-28
  • Sualeh Fatehi
    Sualeh Fatehi
    2014-03-28

    Günter,

    I am glad that both your problems got resolved. Thanks for using SchemaCrawler!

    I think that the difference between the ALL_ views and the USER_ views may be related to database user permissions. But, I am not an Oracle expert.

    Sualeh.

     
  • Hi Sualeh,

    after further investigation, i believe the reason for this behaviour is that both schemas use different tablespaces for data TS_DATA_XX/TS_DATA_ZZ for indexes TS_INDEX_XX/TS_INDEX_ZZ.

    Regards Günter