Oracle temporary table on generateChangeLog is created as normal table

[VGoudkov]

Environment

Liquibase Version:
4.1.1 #10
Liquibase Integration & Version: <Pick one: CLI, maven, gradle, spring boot, servlet, etc.>
CLI
Liquibase Extension(s) & Version:

Database Vendor & Version:
Oracle 12.2
Operating System Type & Version:
Win10

Description

Sometimes Hibernate generates following table(s)
CREATE GLOBAL TEMPORARY TABLE HT_AAA (ID NUMBER(19) NOT NULL) ON COMMIT DELETE ROWS;

GenerateChangelog creates standard changeSet, which leads to create normal (not temporary) table:

Steps To Reproduce

1. Create a temporary table
2. Create a changelog
3. Apply it to other database
4. Compare DDL's manually or by liquibase's diff

[molivasdat]

@VGoudkov Thanks for taking the time to write up this issue. We will add it to our list of issues to process.

[tati-qalified]

Hello @VGoudkov, sorry for the late response, we're trying to catch up on older tickets in our backlog.

I was able to replicate this issue both with Liquibase version 4.1.1 and 4.23.0, and with a more recent version of Oracle.

I will be forwarding this to the development team so they can evaluate how to move forward.

Thank you,
Tatiana

[hel1e]

In addition Oracle Queue tables are generated as normal tables.

Oracle queue tables must be created using sys.dbms_aqadm.create_queue_table. It would be better to exclude them from generateChangelog rather than generating wrong ddl for them.

All queue tables are found in the oracle data dictionary views dba_queue_tables (all_queue_tables / user_queue_tables).

[filipelautert]

Since Liquibase v4.19.0 we have a new createTable attribute called tableType . To solve @VGoudkov I'll try to fix snapshot so it generates the table as
<createTable tableName="HT_AAA" tableType="GLOBAL TEMPORARY">

For @hel1e case, I think the best case would be to ignore those tables. What do you think?

[hel1e]

Yes, I think it is better if you ignore objects you don't handle instead of creating a changelog which will produce the wrong result. But if you could also just add a comment in the generated changelog, or a summary report telling which objects have been ignored, that would be quite helpful!

I have also discovered a few other Oracle related things that are not supported correctly by generate-changelog:
partitioned tables (and indexes) -> are created as non-partitioned
bitmap indexes -> are created as ordinary indexes
indexes on virtual columns -> the create index statement fails, I guess it tries to create a function-based index instead.

[hel1e]

@filipelautert I took a look at your code change, and added a comment to the change you made to liquibase-standard/src/main/java/liquibase/snapshot/JdbcDatabaseSnapshot.java.

You don't actually filter out the queue-tables here as far as I can see. You will need to also add a where clause:
where q.QUEUE_TABLE is null