Pro Branch Version & Oracle Table Compression

911
0
10-13-2021 09:41 AM
Labels (3)
MarceloMarques
Esri Regular Contributor
0 0 911

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;

pic1.jpg

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]"

pic2.jpg

  • we cannot register the data as branch version if the table has Oracle Compression enabled
  • branch version needs to create an Oracle Identity Field, and a column with Oracle Identity Field Type cannot be added when the table has Oracle Compression enabled
    ORA-39726: unsupported add/drop column operation on compressed tables
  • if the table is decompressed then the Oracle Identity Field can be created and then the table can have Oracle Compression enabled again

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

pic3.jpg

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]"

pic4.jpg

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

 

About the Author
| Marcelo Marques | Principal Product Engineer | Esri | Cloud & Database Administrator | OCP - Oracle Certified Professional | 30 years experience | www.linkedin.com/in/mmarquesbr | I have worked with Esri Technology since 1992 and I have been working with Enterprise Geodatabases since 1997 when the Geodatabase was first released. | " a successful Enterprise ArcGIS application deployment starts with a strong physical database design and best database administration practices" | " I do not fear computers. I fear the lack of them." - Isaac Asimov |