Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, jithesh.

Asked: October 14, 2020 - 8:36 pm UTC

Last updated: October 21, 2020 - 1:29 am UTC

Version: 19c

Viewed 1000+ times

You Asked

I do have a question about Oracle Indatabase archiving in Oracle 19c,
given below table has a XMLtype column
alter table HTS_SUMMARY_DATA_CELL row archival
Error report -
SQL Error: ORA-38353: This table type is not supported by the ROW ARCHIVAL clause.

This error comes.

The same Indatabase archiving in Oracle 12c,12.1.0.2

alter table HTS_SUMMARY_DATA_CELL row archival

The table gets archived.

What is the differnce there in 12C and 19C versions,



and Connor said...

I suspect this might be a bug, because if I reverse the order it works...

SQL> create table t ( x int, y xmltype );

Table created.

SQL> alter table t row archival;
alter table t row archival
*
ERROR at line 1:
ORA-38353: This table type is not supported by the ROW ARCHIVAL clause.

SQL> drop table t purge;

Table dropped.

SQL> create table t ( x int) row archival;

Table created.

SQL> alter table t add y xmltype;

Table altered.


The challenge now is find out whether we actually support it....I'll ask around internally and try find out

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jithesh, October 16, 2020 - 8:33 am UTC

Is this one is a bug of Oracle19c version.
Please have an answer
Connor McDonald
October 19, 2020 - 2:31 am UTC

I spoke to the compression PM.

Using Xmltype with row archival should NOT be permitted.

InDB Archiving for XMLType columns

Ali Rodriguez, October 20, 2020 - 2:21 pm UTC

Hi,

If this is a limitation why it was never mentioned in the documentation?

Is any chance to fix it in the future?
Connor McDonald
October 21, 2020 - 1:29 am UTC

That is no different to my example (ie, adding the row archival afterwards) and according the compression PM, it is *this* that is a bug, ie, it should not be allowed.

I'd suggest raising an enhancement request here

https://community.oracle.com/tech/welcome/categories/15571

InDB Archiving for XMLType columns allow in previous DB 12.1 release

Ali Rodriguez, October 20, 2020 - 2:46 pm UTC

Hi,
I want to show you that the XMLtype columns are working in previous versions 12.1.0.2 when we archive the tabke, so that seems to me like a bug in 19c release.

Test Case:

alter session set current_schema=en1210;

Select version from v$instance;
VERSION
12.1.0.2.0

create table en1210.ALI_XMLTYPE ( x int, y xmltype );

Table created.

alter table en1210.ALI_XMLTYPE row archival;

Table EN1210.ALI_XMLTYPE altered.


select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from dba_tab_cols where table_name like '%ALI_XMLTYPE%';

TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
ALI_XMLTYPE X NO YES
ALI_XMLTYPE Y NO YES
ALI_XMLTYPE SYS_NC00003$ YES NO
ALI_XMLTYPE SYS_NC00004$ YES NO
ALI_XMLTYPE ORA_ARCHIVE_STATE YES NO



alter table en1210.ALI_XMLTYPE no row archival
Table EN1210.ALI_XMLTYPE altered.

drop table en1210.ALI_XMLTYPE purge;

Table dropped.


What do you think about it?

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database