Andrew Fraser DBA

Fix for ORA-00997: illegal use of LONG datatype for CTAS dba_triggers dba_tab_cols

CTAS doesn’t work for columns of LONG datatype – fails with error ORA-00997: illegal use of LONG datatype.

That’s irritating if you want to make a copy of dictionary views like dba_triggers, dba_tab_cols, etc – and a copy like that can be very useful for checking everything is the same as you expect after some outage or build work.

Fortunately the COPY command does work ok for LONGs – that command is available in both SQL*Plus and Oracle Sql Developer from at least version 17.2.

The COPY command is often used for copying between two different different databases, but it works just as well inside a single database.

Below is the script I used:

COPY FROM user/password@server/database REPLACE arc_triggers USING SELECT * FROM dba_triggers WHERE table_owner = 'MYSCHEMA' ;
COPY FROM myUser/myPassword@dbServer/database REPLACE arc_indexes USING SELECT * FROM dba_indexes WHERE table_owner = 'MYSCHEMA' ;
COPY FROM user/password@server/database REPLACE arc_tables USING SELECT * FROM dba_tables WHERE owner = 'MYSCHEMA' ;
COPY FROM user/password@server/database REPLACE arc_tab_privs USING SELECT * FROM dba_tab_privs WHERE owner = 'MYSCHEMA' ;
COPY FROM user/password@server/database REPLACE arc_constraints USING SELECT * FROM dba_constraints WHERE owner = 'MYSCHEMA' OR r_owner = 'MYSCHEMA' ;
COPY FROM user/password@server/database REPLACE arc_objects USING SELECT * FROM dba_objects WHERE owner = 'MYSCHEMA' ;
COPY FROM user/password@server/database REPLACE arc_ind_columns USING SELECT * FROM dba_ind_columns WHERE table_owner = 'MYSCHEMA' ;
COPY FROM user/password@server/database REPLACE arc_cons_columns USING SELECT * FROM dba_cons_columns WHERE owner = 'MYSCHEMA' ;
COPY FROM user/password@server/database REPLACE arc_log_groups USING SELECT * FROM dba_log_groups WHERE owner = 'MYSCHEMA' ;
COPY FROM user/password@server/database REPLACE arc_tab_cols USING SELECT owner , table_name , column_name , virtual_column , hidden_column , data_default , data_type , data_length , data_precision , data_scale , nullable FROM dba_tab_cols WHERE owner = 'MYSCHEMA' ;

Some of those views can be copied ok with a standard CREATE TABLE AS SELECT, others can’t, for simplicity I went with the same method for them all.

There are other solutions to this using pl/sql or to_lob functions (e.g. Adrian Billington, Carlos Sierra): COPY command is maybe the simplest and quickest to put together if in a hurry before an outage.

The reason for specifying column names for dba_tab_cols in the above script is to workaround limitation in COPY command where it does not handle RAW datatypes – get error ORA-12899: value too large for column “SYSTEM”.”ARC_TAB_COLS”.”LOW_VALUE” (actual: 7, maximum: 1) otherwise. Only the low_value and high_value columns are affected, which weren’t important for my purposes.

Ideally Oracle will change its dictionary tables from LONGs to CLOBs someday, they go on enough about how their customers’ developers should stop using LONGs after all. MySQL has an analogous issue where its dictionary is stored in old MyISAM instead of newer innoDB.