This is an attempt to write a short article about the LONG data type history in Oracle Database.
In Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions, Tom Kyte writes page 513 that "the LONG types date back to version 6 of Oracle, when they were limited to 64 KB of data.".
In Oracle 7 (see Oracle 7 Server Reference page 71 - chapter 2 page 23) the LONG data type is able to store up to 2GB of data.
In Oracle 8.0.4, Oracle Corp. implemented 3 new data types to store large objects (LOB):
The Oracle 8.0 SQL Reference lists the LONG data types limitations (quite long actually) but does not explicitely recommend to use LOBs instead of LONG: only in the Oracle 8.0 Concepts Guide it is written that "in new applications, you should use CLOB and NCLOB datatypes for large amounts of character data.".
The Oracle 8.1.5 SQL Reference "strongly recommends that you convert LONG columns to LOB columns.".
The Oracle 9.2 SQL Reference
states "Oracle Corporation strongly recommends that you convert LONG columns to LOB columns as soon as possible. Creation of new LONG columns is scheduled for desupport.".
The Oracle 10.1 SQL Reference instructs "Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead." and "Oracle also recommends that you convert existing LONG columns to LOB columns.". The Oracle 10.1 Application Developer's Guide - Large Objects answers the question "Why Not Use LONGs ?" with "The database supports LONG as well as LOB datatypes. When possible, change your existing applications to use LOBs instead of LONGs because of the added benefits that LOBs provide.".
Oracle Corp. did not change the 10.2, 11.1 and 11.2 documentation about LONG data type recommended usage and possible desupport.
The same wording is still used in the Oracle 12.1 SQL Reference and in the Oracle 12.1 Database SecureFiles and Large Objects Developer's Guide.
Note that it is only starting in Oracle 12.1 released in 2013 that VARCHAR2 data type can store more than 4000 characters.
In a 12.1 database the database dictionary is still using a lot of LONG columns likely for "backward compatibility":
SYS@CDB12C>select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0 SYS@CDB12C>select owner, count(*) 2 from dba_tab_columns 3 where data_type='LONG' 4 group by owner; OWNER COUNT(*) -------------------- ---------- APEX_040200 1 OUTLN 1 SYS 242 SYSTEM 11 WMSYS 2
Here are the tables with LONG colums differences between 12.1 and 11.2:
C##ADMIN@cdbrac1>select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0 C##ADMIN@cdbrac1>-- C##ADMIN@cdbrac1>create database link ldb11 connect to admin identified by admin using 'db11'; Database link created. C##ADMIN@cdbrac1>select * from v$version@ldb11; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production C##ADMIN@cdbrac1>-- C##ADMIN@cdbrac1>select tc.owner, tc.table_name, tc.column_name 2 from dba_tab_cols tc, 3 dba_tables t 4 where data_type = 'LONG' 5 and (t.owner = tc.owner and t.table_name = tc.table_name) 6 and (tc.owner, tc.table_name, tc.column_name) not in 7 (select owner, table_name, column_name from dba_tab_cols@ldb11); OWNER TABLE_NAME COLUMN_NAME -------------------- ------------------------------ ------------------------------ SYS SQLOBJ$PLAN OTHER C##ADMIN@cdbrac1>-- C##ADMIN@cdbrac1>select tc.owner, tc.table_name, tc.column_name 2 from dba_tab_cols@ldb11 tc, 3 dba_tables@ldb11 t 4 where data_type = 'LONG' 5 and (t.owner = tc.owner and t.table_name = tc.table_name) 6 and (tc.owner, tc.table_name, tc.column_name) not in 7 (select owner, table_name, column_name from dba_tab_cols); OWNER TABLE_NAME COLUMN_NAME -------------------- ------------------------------ ------------------------------ EXFSYS EXF$PLAN_TABLE OTHER SYSTEM MVIEW$_ADV_PARTITION QUERY_TEXT
At the same time some tables with LONG columns (EXFSYS.EXP$PLAN_TABLE.OTHER and SYSTEM.MVIEWS$_ADV_PARTITION.QUERY_TEXT) have been removed but a new table with LONG column (SYS.SQLOBJ$PLAN.OTHER) has been added.
One can really wonder if some future major Oracle version will migrate database dictionary LONG columns to LOB columns and desupport LONG data types.
Here are some interesting links about LONG data type usage especially if you have to work with database dictionary tables still using this data type:
I hope that this article is not too long.