Thanks for the question, Ramkumar.

Asked: June 07, 2006 - 12:44 am UTC

Last updated: August 15, 2023 - 2:09 am UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Dear Tom,

I am really happy that I got this opportunity to put the 2nd question to you.

We have a requirement to have a Global Temporary Table with a CLOB column. As per the Oracle Documentation, the CLOB segment will sit on the system tablespace and so it does. Even if I try to move it to another tablespace it does not allow that.

I want you suggestions as to do we leave it as it is in the system tablespace, or avoid using CLOB on temporary tables.

Thanks in advance

Ram

and Tom said...

no it doesn't, it is in your temporary tablespace.

The dictionary reports "system", but that is totally "bogus"

temporary tables allocate space from temp.

Until you actually use them - they consume NO STORAGE, not a single byte.

and in 10g, they will "blank this out" hopefully removing the confusion:

ops$tkyte@ORA10GR2> create global temporary table t ( x int, y clob );

Table created.

ops$tkyte@ORA10GR2> select segment_name, tablespace_name from user_segments;

no rows selected

ops$tkyte@ORA10GR2> select table_name, tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T

See - no segments, no tablespace association.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

very useful

Ramkumar, June 08, 2006 - 12:03 am UTC

Dear Tom,

Very crisp and clear answer, as usual from Tom.

Thanks a lot.

Regards

Ram

Global temporary table, clob, nologging, temp_undo_enabled

lh, March 09, 2021 - 10:51 am UTC

Hi

We have global temporary tables where there are columns of datatype CLOB.

Global temporary tables are defined as nologging, but the default setting for these CLOB columns is LOGGING.
Should these clob columns be defined for performance reasons as NOLOGGING ?
Has setting of temp_undo_enabled=true have any effects on this ?

lh

March 11, 2021 - 3:08 am UTC

We are pretty efficient in any event for clobs on GTTs, eg

SQL> create global temporary table gtt
  2  ( id int, c1 char(100), c2 clob ) ;

Table created.

SQL> @mystat
Enter value for statname: redo size

NAME                                                                                  VALUE
-------------------------------------------------------------------------------- ----------
redo size                                                                               676
redo size for lost write detection                                                        0
redo size for direct writes                                                               0

SQL>
SQL> insert into gtt
  2  select rownum, rownum, rpad('x',32000,'x')
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

SQL>
SQL> update gtt
  2  set c2 = rpad('y',32000,'y');

10000 rows updated.

SQL>
SQL> @mystat
Enter value for statname: redo size

NAME                                                                                  VALUE
-------------------------------------------------------------------------------- ----------
redo size                                                                           9365940
redo size for lost write detection                                                        0
redo size for direct writes                                                               0

That is 320MB of "change" to the data and we still only consumed 9meg of redo.

Having said that, you'll get *some* benefit from temp_undo, eg

SQL> alter system set temp_undo_enabled = true scope=both;

System altered.

SQL> create global temporary table gtt
  2  ( id int, c1 char(100), c2 clob ) ;

Table created.

SQL> @mystat
Enter value for statname: redo size

NAME                                                                                  VALUE
-------------------------------------------------------------------------------- ----------
redo size                                                                               676
redo size for lost write detection                                                        0
redo size for direct writes                                                               0

SQL>
SQL> insert into gtt
  2  select rownum, rownum, rpad('x',32000,'x')
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

SQL>
SQL> update gtt
  2  set c2 = rpad('y',32000,'y');

10000 rows updated.

SQL>
SQL> @mystat
Enter value for statname: redo size

NAME                                                                                  VALUE
-------------------------------------------------------------------------------- ----------
redo size                                                                             48936
redo size for lost write detection                                                        0
redo size for direct writes                                                               0

Ralf, June 18, 2021 - 8:12 am UTC

Hi Tom (meaning Chris/Connor ;-) ),
one extension to the original question: do you have a hint how to create a PTT with a blob column? The documentation does not say BLOBs are forbidden, but if I try it I get an error :-( ...

Error starting at line : 24 in command -

     CREATE PRIVATE TEMPORARY TABLE ORA$PTT_TESTRK
    (time_id      DATE,
     bdata  BLOB);

Error report -
ORA-14451: unsupported feature with temporary table
14451. 00000 - "unsupported feature with temporary table"
*Cause: An attempt was made to create an IOT, specify physical attributes,
specify partition or parallel clause.
*Action: do not do that.

June 22, 2021 - 1:36 am UTC

Well the

"ORA-14451: unsupported feature with temporary table"

seems reasonably self-explanatory?

But it is definitely either a software bug or a docs bug, because we say in the docs:

"You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause."

which suggests a LOB should be allowed.

I will ask around internally

PTT and BLOB/CLOB

A reader, August 14, 2023 - 1:02 pm UTC

Hi!
Any updates on private temporary tables and BLOB/CLOB ?

Hitting the same ORA-14451: unsupported feature with temporary table
in ADWH when trying to create PTT.

August 15, 2023 - 2:09 am UTC

Feedback from the internal devs is:

LOBs are (currently) not support in PTTs, and thus the docs will be updated accordingly.

Sorry.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

very useful

Global temporary table, clob, nologging, temp_undo_enabled

PTT and BLOB/CLOB

More to Explore

CLOB/BLOB/etc

CLOB in Temporary Table

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

very useful

Global temporary table, clob, nologging, temp_undo_enabled

PTT and BLOB/CLOB

More to Explore

CLOB/BLOB/etc