Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, jithesh.

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

Answered by: Connor McDonald - Last updated: October 21, 2020 - 1:29 am UTC

Category: Database Development - Version: 19c

Viewed 100+ 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 we 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

and you rated our response

  (3 ratings)

Reviews

October 16, 2020 - 8:33 am UTC

Reviewer: Jithesh

Is this one is a bug of Oracle19c version.
Please have an answer
Connor McDonald

Followup  

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

October 20, 2020 - 2:21 pm UTC

Reviewer: Ali Rodriguez from Costa Rica

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

Followup  

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

October 20, 2020 - 2:46 pm UTC

Reviewer: Ali Rodriguez from Costa Rica

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