Setting range of changeLog generation (or diff)

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+

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

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?

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

Nathan

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

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