1. Oracle Tablespace Compression for OLTP is enabled
ALTER TABLESPACE GIS_BDATA DEFAULT COMPRESS FOR OLTP;
2. Pro load featureclass into the geodatabase
3. Oracle SQL Developer check table is compressed
--check if table is compressed select table_name,tablespace_name,compression from user_tables order by table_name;
4. Pro add Global IDs and Enable Editor Tracking for the Featureclass
5. If try to enable Branch Version (aka. Register as Version) the Featureclass then it is expected to fail because the table is compressed
Pro Error Message " Table is not suitable candidate for versioning [Table GIS.FIREZONES is compressed, and so cannot be registered as branch versioned : ORA-39726: unsupported add/drop column operation on compressed tables]"
6. the workaround is to use SQL to decompress the table
alter table gis.FIREZONES move nocompress parallel 8;
7. when the table is decompressed it causes invalid indexes, the reason is because the indexes reference the table that was compressed, thus the indexes need to be rebuild to reference the table that is now decompressed
SELECT * FROM USER_INDEXES d WHERE d.status NOT IN ('VALID','N/A');
8. rebuild the invalid indexes
--rebuild invalid indexes select 'alter index '||ui.table_owner||'.'||ui.index_name||' rebuild online parallel 8;' from user_indexes ui WHERE ui.status NOT IN ('VALID','N/A') AND ui.index_name NOT LIKE '%_IX1' order by ui.table_owner,ui.table_name,ui.index_name; --spatial indexes '%_IX1' cannot be rebuild online select 'alter index '||ui.table_owner||'.'||ui.index_name||' rebuild parallel 8;' from user_indexes ui WHERE ui.status NOT IN ('VALID','N/A') AND ui.index_name LIKE '%_IX1' order by ui.table_owner,ui.table_name,ui.index_name;
alter index GIS.R6517_SDE_ROWID_UK rebuild online parallel 8; alter index GIS.UUID_6517 rebuild online parallel 8; alter index GIS.A865_IX1 rebuild parallel 8;
9. check again for invalid indexes using SQL ( see step 7)
10. Pro enable Branch Version (aka Register as Versioned) for the featureclass works fine this time because table is decompressed
11. compress table again
alter table gis.FIREZONES move compress parallel 8;
12. when table is compressed it causes invalid indexes ( see step 7)
13. rebuild the invalid indexes ( see step 7 )
alter index GIS.GDB_CT1_6517 rebuild online parallel 8; alter index GIS.GDB_CT2_6517 rebuild online parallel 8; alter index GIS.GDB_CT3_6517 rebuild online parallel 8; alter index GIS.R6517_SDE_ROWID_UK rebuild online parallel 8; alter index GIS.UUID_6517 rebuild online parallel 8; alter index GIS.A865_IX1 rebuild parallel 8;
14. check again for invalid indexes using SQL ( see step 7 )
Case A: do not decompress table prior to disable branch version (aka unregister as versioned)
15. Pro disable Branch Version (aka Unregister as Versioned) works fine.
remember the table was compressed before you disabled Branch Version
16. follow previous steps to decompress and rebuild indexes again
17. Pro try to enable Branch Version (aka Register as Versioned) fails with message
Pro Error Message "Unique indexes cannot be specified for multiversion tables [Unique index GDB_CT3_6517 found while enabling multibranch for GIS.FIREZONES]"
the featureclass got to a state that cannot re-enable Branch Version.
if delete the unique indexes in the featureclass then get other ORA-xxxx errors, it never fixes the problem manually.
Note: !!! this problem was fixed in Pro 3.0 !!! If you have Pro 2.9 then use the workaround below !!!
Case B: decompress table prior to disable branch version (aka unregister as versioned)
18. Pro delete the featureclass and repeat the steps [1-13] above again to load the featureclass, disable compression, enable Branch Version and re-enable compression
19. Before disable Branch Version (aka Unregister as Versioned) first decompress the table, follow steps [6,7,8,9]
20. Pro disable Branch Version (aka Unregister as Versioned) works fine
remember the table was decompressed before you disabled Branch Version
21. Re-compress the table, rebuild invalid indexes, check if table is compressed, check invalid indexes
22. decompress the table, rebuild invalid indexes, check if table is decompressed, check invalid indexes
23. Pro enable Branch Version (aka Register as Versioned) works fine!
24. Re-compress the table, rebuild invalid indexes, check if table is compressed, check invalid indexes