Oracle Scratchpad

February 11, 2016

Quiz Night

Filed under: 12c,Infrastructure,LOBs,Oracle,Statistics — Jonathan Lewis @ 3:28 pm GMT Feb 11,2016

I was setting up a few tests on a copy of recently when I made a mistake creating the table – I forgot to put in a couple of CAST() calls in the select list, so I just patched things up with a couple of “modify column” commands. Since I was planning to smash the table in all sorts of ways and it had taken me several minutes to create the data set (10 million rows) I decided to create a clean copy of the data so that I could just drop the original table and copy back the clean version – and after I’d done this I noticed something a little odd.

Here’s the code (cut down to just 10,000 rows), with a little output:

rem     Script:         12c_vc32767_b.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2016

create table t1 (
        id      not null,
        rownum                                          id,
                add_months(sysdate, -120) +
                        (rownum)* 3652 / 1e7
        )                                               date_open,
                        add_months(sysdate, -120) +
                                (rownum) * 3652 / 1e7,
                        12 * trunc(dbms_random.value(1,6))
        )                                               date_closed,
        dbms_random.string('U',1)                       deal_type,
        dbms_random.string('U',4)                       client_ref,
        lpad(rownum,10)                                 vc_small,
        rpad('x',100,'x')                               vc_padding
connect by
        level <= 10000    -- > comment to avoid WordPress format issue

alter table t1 modify deal_type varchar2(1);
alter table t1 modify client_ref varchar2(4);

create table t2 nologging as select * from t1;

                method_opt=>'for all columns size 1'
                method_opt=>'for all columns size 1'

select table_name, num_rows, blocks, avg_row_len from user_tables;

-------------------- ---------- ---------- -----------
T1                        10000        304         139
T2                        10000        218         139

There’s no hidden code – this is the code I ran, and the tables both went into the same tablespace- yet one table has used about 30% more blocks than the other on a simple “create as select”, even though the row lengths and row counts are the same!

When I first did this test it just so happened that the only place in the database I had to make the t2 copy was in a tablespace using freelist management, and I’d created the original table in a tablespace using ASSM, so I wasted a little time examining the ASSM space management (bitmap) blocks before I realised what had happened.

Suggestions about what has happened, and how to confirm your hypothesis, invited in the comments.

Update (following morning)

The explanation is in the comments: I had set max_string_size = extended.  My calls to dbms_random.string() had returned character values and the return type defaults to the maximum character length, which meant they were declared as varchar2(32767) and those are implemented as (unhackable) CLOBs in

When I modified the table to change the varchar2(32767) to shorter lengths Oracle let me get away with it because none of the actual stored values at that time was longer than my target declaration – but it didn’t physically change the stored column values to “short” varchar2(), it left them as CLOBs, and a “short” CLOB includes a LOB Locator which starts out at about 30 bytes.

When I created t2 from t1 Oracle first copied the (short) column definitions, and then copied the data, reading and converting the CLOBs to normal varchar2() storage, and that explains why t2 needed fewer blocks.  The avg_row_len for the two tables matched because the code to gather stats simply applies the sys_op_opnsize() function to the varchar2() defined in t1, and doesn’t give you any indication about the LOB locator.

(If you use the deprecated analyze command to gather stats on the two tables you’ll find that the avg_row_len of table t1 then allows for the LOB locator size.)


I should have used:  cast(dbms_random.string(‘U’,4) as varchar2(4)) and the column definition would have been a proper varchar2(4) from the start.  (As noted in a comment from Ivica Arsov below, substr(…, 1, 4) would also have worked.  Of course I should have used cast() for the numerics as well so that I could declare them as things like number(8,0) rather than the slightly dangerous “number”.

Note:  I’ve put “workaround” in quotes because it’s not really a workaround to a problem – it’s just an example of doing it right.


Here’s a symbolic dump of a single character (‘T’) being stored as a varchar2(32767):

  Length:        84(31)
  Version:        1
  Byte Length:    1
  Flags[ 0x01 0x0c 0x00 0x80 ]:
    Type: BLOB
    Storage: SecureFile
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: ReadWrite
  SecureFile Header:
    Length:   11
    Old Flag: 0x48 [ DataInRow SecureFile ]
    Flag 0:   0x90 [ INODE Valid ]
      Lengths Array: INODE:5
        00 00 01 01 54

Note the 84(31) at line 3: this is a reminder of how big a LOB locator could get for an “out of row” LOB, compared to the current size of the LOB locator. Rows that hold “out of row” LOBs can actually be much longer than avg_row_len tells you.


Another side effect of enabling extended varchar2() types is the surprise you get when you use error logging in SQL (i.e. syntax like: “insert into t1 select … log errors”). When you create the “clone” table for the log it represents every column in the original table as a varchar2(32767) e.g.:

desc target
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ID                                                                                NUMBER
 N1                                                                                NUMBER
 N2                                                                                NUMBER
 SMALL_VC                                                                          VARCHAR2(10)
 PADDING                                                                           VARCHAR2(100)


desc err$_TARGET
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ORA_ERR_NUMBER$                                                                   NUMBER
 ORA_ERR_MESG$                                                                     VARCHAR2(2000)
 ORA_ERR_ROWID$                                                                    ROWID
 ORA_ERR_OPTYP$                                                                    VARCHAR2(2)
 ORA_ERR_TAG$                                                                      VARCHAR2(2000)
 ID                                                                                VARCHAR2(32767)
 N1                                                                                VARCHAR2(32767)
 N2                                                                                VARCHAR2(32767)
 SMALL_VC                                                                          VARCHAR2(32767)
 PADDING                                                                           VARCHAR2(32767)

You probably hope that you’re not going to see many rows inserted into the error log table, so the extra space taken up in the table by error rows probably won’t be a problem – but the fact that you get two segments (the LOB and the LOBINDEX) created for every column in the original table might be a nuisance – even if they are completely empty.


  1. You gave a hint but revoked it :-)

    Comment by Valentin Nikotin — February 11, 2016 @ 4:33 pm GMT Feb 11,2016 | Reply

    • Valentin,

      That’s really going to confuse people – but yes, you’re right, you must have seen the article within a couple of minutes of me posting it. I didn’t notice I’d left in a spool command until too late.

      Thanks for not giving the game away.

      Comment by Jonathan Lewis — February 11, 2016 @ 4:36 pm GMT Feb 11,2016 | Reply

  2. Jonathan,
    some ideas without a particular order:

    1. Compression
    Objects getting larger after a change of information always reminds me of compression. But since the datatype change is a metadata only operation I don’t think that the usual inflate after updates on compressed rows has its part in this effect. I also did a quick check (but in – the only system I could reach) and could not reproduce the issue (after setting the tablespace’s default to compress).

    2. Different Space Management for direct load operations
    Maybe the two operations used different strategies for the direct path loads – as explained by Niegel Bayliss in To check this theory I would take a look at the sessions statsistics – since this is a good idea most of the time… Of course the theory has a big problem: why should the system choose different strategies though the session parameters did not change.

    Maybe I would start with a check of the distribution of data in the blocks using ora_rowscn (are there always fewer rows per block for t1 or are there some almost empty blocks)

    To summarize: I don’t have a clue.



    Comment by Martin Preiss — February 11, 2016 @ 5:19 pm GMT Feb 11,2016 | Reply

    • Martin,

      You won’t see the problem in 11g, it’s got to be 12c.

      The space management is a reasonable thought – except they’re both “create as select nologging” to the same tablespace, so I would have expected them to use the same method (though, I guess it’s possible that the optimizer might have had a choice depending on execution plan for the select).

      Comment by Jonathan Lewis — February 11, 2016 @ 6:27 pm GMT Feb 11,2016 | Reply

  3. My first idea was that the two tables could be using different NLS_LENGTH_SEMANTICS (char/byte). However I don’t really see how that would happen. At least as long as everything is executed in the same session.

    For further analysis i would start looking at user_tab_columns.

    select *
    from user_tab_columns
    where table_name in (‘T1′,’T2’)
    order by column_name;

    Especially things like avg_col_length, density, character_set_name, char_used, etc.

    Comment by svenweller — February 11, 2016 @ 6:14 pm GMT Feb 11,2016 | Reply

  4. another idea: maybe parallel dml and different parallel degrees in both CTAS operations? If the first execution used a much higher parallel degree for the CREATE TABLE operation I would expect to see additional blocks added to the structure: using your example (now in with an additional parallel hint in the second CTAS I get 200 blocks for the first table and 207 blocks for the second table.

    And maybe the use of auto DOP could result in different parallelization for different executions – so this could explain why there was no need for a change of parameters.

    On the other hand I am still not able to reproduce your numbers and the parallelism explanation could also occur on 11.2 – and I still guess that the ALTER TABLE commands are not a red herring; so I don’t assume to be on the right track…

    Comment by Martin Preiss — February 11, 2016 @ 7:14 pm GMT Feb 11,2016 | Reply

    • Martin,

      I don’t think parallelism will have any effect – I cut and pasted the code above after issuing:
      alter session force parallel ddl;
      alter session force parallel query;

      I got the same result – the connect by select can’t run parallel, and the “create” dispatched 4 parallel slaves, but then used only one of them (perhaps because the result set was so small – with a much larger set I think I would have seen a serial to parallel distribution then parallel load).

      Comment by Jonathan Lewis — February 11, 2016 @ 7:27 pm GMT Feb 11,2016 | Reply

  5. Only a guess: database can have 32K VARCHAR2 and running “alter table t1 modify … varchar2(…)” makes Oracle a little bit clever when allocating space in the second CTAS

    Comment by Pierre Forstmann — February 11, 2016 @ 7:27 pm GMT Feb 11,2016 | Reply

    • Pierre,

      Good guess – you’ve identified the source of the anomaly: now we need someone to fill in the detail.

      Comment by Jonathan Lewis — February 11, 2016 @ 8:02 pm GMT Feb 11,2016 | Reply

      • the documentation tells us that extended data types are stored in LOBs: – “Extended data type columns are stored out-of-line, leveraging Oracle’s LOB technology. The LOB storage is always aligned with the table. In tablespaces managed with Automatic Segment Space Management (ASSM), extended data type columns are stored as SecureFiles LOBs. Otherwise, they are stored as BasicFiles LOBs. The use of LOBs as a storage mechanism is internal only. Therefore, you cannot manipulate these LOBs using the DBMS_LOB package.” And I could imagine that thes LOBs use significantly more storage in t1 than the two small varchar2 columns in t2.

        Comment by Martin Preiss — February 11, 2016 @ 8:24 pm GMT Feb 11,2016 | Reply

  6. In a database with AL32UTF8 charactset set code I have;

    TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
    ------------------------------ ---------- ---------- -----------
    T1                                  10000        300         139
    T2                                  10000        212         139


    >select segment_name, bytes, blocks from user_segments order by 1;
    SEGMENT_NAME                        BYTES     BLOCKS
    ------------------------------ ---------- ----------
    SYS_IL0000091983C00004$$            65536          8
    SYS_IL0000091983C00005$$            65536          8
    SYS_LOB0000091983C00004$$          131072         16
    SYS_LOB0000091983C00005$$          131072         16
    T1                                3145728        384
    T2                                2097152        256

    The difference is in the table blocks not in the LOB segment

    Comment by Pierre Forstmann — February 11, 2016 @ 8:49 pm GMT Feb 11,2016 | Reply

  7. This only happens if you are using function (built-in or user defined) to generated the columns data.

    With literal values or sub query values same number of blocks are allocated.

    Comment by Ivica Arsov — February 11, 2016 @ 9:01 pm GMT Feb 11,2016 | Reply

    • Here is a possible workaround/solution:

      substr(dbms_random.string('U',1),1,1) deal_type,
      substr(dbms_random.string('U',4),1,4) client_ref,

      Maybe, using substr will give more information about the length of the data produced (not completely sure) ?


      ----------   ----------   ----------   -----------
      T1                10000          212           139
      T2                10000          212           139

      Comment by Ivica Arsov — February 11, 2016 @ 11:35 pm GMT Feb 11,2016 | Reply

  8. If i dump with alter system dump datafile … block … one table segment block I get:

    - for T1
    col  0: [ 3]  c2 02 4d
    col  1: [ 7]  78 6a 02 0b 01 01 01
    col  2: [ 7]  78 6b 02 0b 01 01 01
    col  3: [31]
     00 54 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 23 de 6d 00 0b 48 90 00
     05 00 00 01 01 55
    - for T2:
    col  0: [ 3]  c2 03 34
    col  1: [ 7]  78 6a 02 0b 01 01 01
    col  2: [ 7]  78 6c 02 0b 01 01 01
    col  3: [ 1]  5a

    I think that this means that col 3 [ which represents DEAL_TYPE VARCHAR2(1) ] for both tables) is using 31 bytes for T1 and only 1 byte for T2.

    Comment by Pierre Forstmann — February 11, 2016 @ 9:09 pm GMT Feb 11,2016 | Reply

    • Pierre,

      That’s correct – and what you see is the LOB locator with it’s first node – which includes the data because the data is less than 3960 bytes.

      Comment by Jonathan Lewis — February 12, 2016 @ 8:20 am GMT Feb 12,2016 | Reply

  9. […] be a clone of the t1 table with changes to column types (which might be interesting if you’ve enabled 32K columns in 12c) and a few extra columns. There are some features of Oracle that aren’t supported by the […]

    Pingback by Never … | Oracle Scratchpad — August 24, 2018 @ 10:12 am BST Aug 24,2018 | Reply

  10. […] for copying a large volume of data from one table to another, but originally in an addendum about a little surprise you may get when you use extended strings (max_string_size = […]

    Pingback by Error Logging | Oracle Scratchpad — August 24, 2018 @ 11:20 am BST Aug 24,2018 | Reply

  11. […] effect you might overlook when creating a table as select with derived […]

    Pingback by 32K Columns | Oracle Scratchpad — October 30, 2019 @ 3:54 pm GMT Oct 30,2019 | Reply

  12. […] Quiz Night 30 (June 2016): What’s the clue that this plan came from 12c? […]

    Pingback by Execution Plans Catalogue | Oracle Scratchpad — August 20, 2022 @ 2:57 pm BST Aug 20,2022 | Reply

  13. […] Quiz Night 30 (June 2016): What’s the clue that this plan came from 12c? […]

    Pingback by Quiz Catalogue | Oracle Scratchpad — August 20, 2022 @ 3:13 pm BST Aug 20,2022 | Reply

  14. […] Quiz Night 29 (Feb 2016): a side-effect of setting max_string_size to extended […]

    Pingback by LOB Catalogue | Oracle Scratchpad — August 20, 2022 @ 4:31 pm BST Aug 20,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: