DBMS_METADATA generating incorrect syntax for the CREATE TABLE statements with ROW ARCHIVAL enabled (12.1.0.2)

While experiencing with the In-Database Archiving feature in 12.1, I stumble upon this unpublished bug with DBMS_METADATA.GET_DDL.

	SQL> select patch_id, patch_uid, version, status, description
	2 from dba_registry_sqlpatch
	3 where bundle_series = 'PSU';
	DESCRIPTION
	--------------------------------------------------------------------------------
	Database Patch Set Update : 12.1.0.2.4 (20831110)
	SQL> create table t (x int, y varchar2(30)) row archival;
	Table created.
	SQL> col column_name for a20
	SQL> col data_type for a20
	SQL> col nullable for a20
	SQL> select column_id, column_name, data_type, data_length, nullable, hidden_column
	2 from user_tab_cols
	3 where table_name = 'T'
	4 order by column_id;
	COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE HID
	---------- -------------------- -------------------- ----------- -------------------- ---
	1 X NUMBER 22 Y NO
	2 Y VARCHAR2 30 Y NO
	ORA_ARCHIVE_STATE VARCHAR2 4000 Y YES
	SQL> set long 2000
	SQL> select dbms_metadata.get_ddl('TABLE', table_name, owner) as tab_ddl
	2 from all_tables where owner = user
	3 and table_name = 'T';
	TAB_DDL
	--------------------------------------------------------------------------------
	CREATE TABLE "C##ZEUS"."T"
	( "X" NUMBER(*,0),
	"Y" VARCHAR2(30)
	) SEGMENT CREATION DEFERRED
	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
	NOCOMPRESS LOGGING
	TABLESPACE "USERS"
	ILM ENABLE LIFECYCLE MANAGEMENT
	SQL> CREATE TABLE "C##ZEUS"."T"
	2 ( "X" NUMBER(*,0),
	3 "Y" VARCHAR2(30)
	4 ) SEGMENT CREATION DEFERRED
	5 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
	6 NOCOMPRESS LOGGING
	7 TABLESPACE "USERS"
	8 ILM ENABLE LIFECYCLE MANAGEMENT
	ILM ENABLE LIFECYCLE MANAGEMENT
	*
	ERROR at line 8:
	ORA-00933: SQL command not properly ended

view raw gistfile1.txt hosted with ❤ by GitHub

The clause "ILM ENABLE LIFECYCLE MANAGEMENT" was returned instead of "ROW ARCHIVAL" which causing an ORA-00933 at creation time. And here is what I found on My Oracle Support about this issue who is also impacting datapump imports in 12c:

CAUSE
The cause of this problem has been identified in:
unpublished Bug:17653443 - DBMS_METADATA.GET_DDL GENERATES WRONG DDL FOR TABLE WITH ROW ARCHIVAL
closed as duplicate of:
unpublished Bug:17654567 - DATA FROM ROW ARCHIVAL COLUMN ORA_ARCHIVE_STATE NOT TRANSFERRED WITH DATA PUMP
It is caused by DBMS_METADATA generating incorrect syntax for the CREATE TABLE statement in the dump file.
Bug:17654567 is fixed in RDBMS 12.2.

ORA-39083 and ORA-0093 When Using IMPDP to Import a Table With Row Archival Enabled (Doc ID 1999047.1)