Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE t (long_field VARCHAR2(1024));
INSERT INTO t ( long_field ) VALUES ( 'abcdef' );
1 rows affected

CREATE TABLE t2 AS
  SELECT substr(long_field, instr(long_field, 'c') + 1) short_field
  FROM   t;
1 rows affected

SELECT * FROM t2;


SHORT_FIELD


def


CREATE TABLE t3 AS
  SELECT CAST(
           substr(long_field, instr(long_field, 'c') + 1)
           AS VARCHAR2(10)
         ) short_field
  FROM   t;
1 rows affected

CREATE TABLE c (extra_long_field CLOB);
INSERT INTO c (extra_long_field)
VALUES (EMPTY_CLOB() || LPAD('A', 4000, 'A') || LPAD('A', 4000, 'A'));
1 rows affected

CREATE TABLE t4 AS
  SELECT substr(extra_long_field, 8000) AS max_field
  FROM   c;
1 rows affected

SELECT table_name,
       column_name,
       data_length
FROM   user_tab_cols;


TABLE_NAME
COLUMN_NAME
DATA_LENGTH


T4
MAX_FIELD
4000

C
EXTRA_LONG_FIELD
4000

T
LONG_FIELD
1024

T3
SHORT_FIELD
10

T2
SHORT_FIELD
4000

TABLE_NAME	COLUMN_NAME	DATA_LENGTH
T4	MAX_FIELD	4000
C	EXTRA_LONG_FIELD	4000
T	LONG_FIELD	1024
T3	SHORT_FIELD	10
T2	SHORT_FIELD	4000