Setting range of changeLog generation (or diff)

General Discussion
1

I try to generate Change Log of my database Schema with this key:

    --diffTypes=data
But liquibase throws exception [ ORA ] It seems, liquibase try to select rows from oracle system table, that matches with pattern: SYS_IOT_OVER% (For example) (Am I captain obvious? Yes I am!  :D) Here is a log:
    LiquiBase Update Failed: Error executing SQL SELECT * FROM SYS_IOT_OVER_3649894.  For more information, use the --logLevel flag) INFO 18.02.10 10:58:liquibase: Error executing SQL SELECT * FROM SYS_IOT_OVER_3649894 java.lang.RuntimeException: liquibase.exception.DatabaseException: Error executing SQL SELECT * FROM SYS_IOT_OVER_3649894 at liquibase.diff.DiffResult.addInsertDataChanges(DiffResult.java:1119) at liquibase.diff.DiffResult.printChangeLog(DiffResult.java:513) at liquibase.diff.DiffResult.printChangeLog(DiffResult.java:419) at liquibase.integration.commandline.CommandLineUtils.doGenerateChangeLog(CommandLineUtils.java:141) at liquibase.integration.commandline.Main.doMigration(Main.java:605) at liquibase.integration.commandline.Main.main(Main.java:105) Caused by: liquibase.exception.DatabaseException: Error executing SQL SELECT * FROM SYS_IOT_OVER_3649894 at liquibase.executor.core.jdbc.JdbcExecutor.execute(JdbcExecutor.java:58) at liquibase.executor.core.jdbc.JdbcExecutor.query(JdbcExecutor.java:124) at liquibase.executor.core.jdbc.JdbcExecutor.query(JdbcExecutor.java:132) at liquibase.executor.core.jdbc.JdbcExecutor.queryForList(JdbcExecutor.java:184) at liquibase.executor.core.jdbc.JdbcExecutor.queryForList(JdbcExecutor.java:179) at liquibase.diff.DiffResult.addInsertDataChanges(DiffResult.java:991) ... 5 more Caused by: java.sql.SQLException: ORA-25191: cannot reference overflow table of an index-organized table 
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:790)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1031)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1264)
at liquibase.executor.core.jdbc.JdbcExecutor$1QueryStatementCallback.doInStatement(JdbcExecutor.java:110)
at liquibase.executor.core.jdbc.JdbcExecutor.execute(JdbcExecutor.java:51)
... 10 more</ol>

    I can’t drop this table  ;D I’ve no power and might for this deal, so i hope only to exclude system-like tables from changeLog generation. Is there some way to setting it up? [Setting range of changeLog generation]

    with best regards,
    Gadge+

2

There is the ability in our OracleDatabase class to exclude system tables from the diff.  Currently the code looks like this:

    public boolean isSystemTable(String catalogName, String schemaName, String tableName) {         if (super.isSystemTable(catalogName, schemaName, tableName)) {             return true;         } else if (tableName.startsWith("BIN$")) { //oracle deleted table             return true;         } else if (tableName.startsWith("AQ$")) { //oracle AQ tables             return true;         } else if (tableName.startsWith("DR$")) { //oracle index tables             return true;         }         return false;     }

I can add "else if (tableName.startsWith(“SYS_)) { return false }” 

My concern is that there is nothing special about the SYS_ prefix for a table that prevents people from using it for their own tables.  I think that should be rare enough that we should add the SYS_ prefix in for oracle. 

I’ll commit the change today or tomorrow for the upcoming 2.0.  Are you using the 2.0 RC’s and could try the newest build from http://liquibase.org/ci/latest ? Or are you using 1.9 still?

Nathan

3

Thnx Nathan!

Yes, I’m using the 2.0 RC (exactly trunk latest build). There is no problem for me to download new fixXxed version of build :slight_smile: So, I’ll be waiting for )

That was i found about SYS_IOT_OVER prefix:
http://11iappsdba.blogspot.com/2009/08/iot-tables-starting-with-sysiotover-if.html

So I think that it would be better to use full name of prefix - ‘SYS_IOT_OVER’, because of too simple and wide case of prefix SYS_.
I’m worry that SYS_ is frequently usable prefix of data-tables… (i.e. SYS_CONSTS, SYS_DOCS, etc…)
Am I right?

4

But would those other SYS_ data tables be considered system tables that shouldn’t be diffed either?

Nathan

5

In our database these tables (such as SYS_CONSTS, SYS_DOCS, etc) are non-system tables.
It was just called with prefix ‘SYS_’.
So it should be diffed

6

OK, thanks for letting me know.  I changed the code in trunk to just check for SYS_IOT_OVER to mark it as a system table

Nathan