Searching my blog recently for some details about a temporary space problem I came across a draft I’d written a few years ago about LOB columns in global temporary tables. It was a summary of an exchange from OTN that did a good job of capturing my general air of scepticism when reviewing database results. The basic problem came from a user who had discovered that when they included a LOB column in a global temporary table the LOBINDEX segment “was created in the SYSTEM tablespace”.
This note is probably not going to be of much practical benefit to many people – because no-one should be using an version old enough to display the anomaly – but it does demonstrate a principle and a pattern of thinking, so here goes – 5 years late.
Like the “Earn $50M by helping me steal $100M” email the claim from the OP seemed a little suspect. My basic approach to Oracle is: “if it looks unreasonable it probably isnt’ true so test it”, so I did. Here’s the first bit of SQL (which I ran on 12.1.0.2 on an empty schema):
rem rem Script: gtt_lobs.sql rem Author: Jonathan Lewis rem Dated: May 2016 rem Purpose: rem rem Last tested rem 12.1.0.2 rem 11.2.0.4 create global temporary table gtt1( id number not null, v1 varchar2(10) not null, c1 clob, constraint gtt1_pk primary key(id) ) -- tablespace gtt_temp ; select table_name, index_name, tablespace_name from user_indexes ; TABLE_NAME INDEX_NAME TABLESPACE_NAME -------------------- -------------------------------- ------------------------------ GTT1 GTT1_PK GTT1 SYS_IL0000168694C00003$$ SYSTEM
Sure enough, the query against user_indexes says the LOBINDEX for the LOB in the global temporary table will be created in the SYSTEM tablespace! This is clearly ridiculous – so I’m not going to believe it until I’ve actually confirmed it. I tend to trust the data dictionary rather more than I trust the manuals and MOS – but the data dictionary is just a bunch of tables and a bit of SQL that’s been written by someone else. so even the data dictionary can be wrong. It’s easy enough to test:
insert into gtt1 values(1,'a',rpad('x',4000,'x')); select username, tablespace, segtype, segfile#, segblk# from v$tempseg_usage ; USERNAME TABLESPACE SEGTYPE SEGFILE# SEGBLK# ------------------------------ ------------------------------- --------- ---------- ---------- TEST_USER TEMP DATA 201 261632 TEST_USER TEMP INDEX 201 261760 TEST_USER TEMP LOB_DATA 201 262016 TEST_USER TEMP INDEX 201 261888
Whatever the data dictionary says, the actual segment (at file 201, block 261888 – it would be nice if the type were LOB_INDEX rather than just INDEX, but that still hasn’t been fixed, even in 21.3.0.0) has been created in the temporary tablespace. Checking the definiton of the dba_indexes view, I came to the conclusion that the property column of the sys.ind$ table hadn’t had the bit set to show that it’s an index associated with a temporary table; as a result the SQL in the view definition reports tablespace 0 (SYSTEM) rather than decoding the zero to a null.
You’ll note that there’s a commented reference to “tablespace gtt_temp” in my original table creation statement. It’s not terribly well known but in recent versions of Oracle you can associate a global temporary table with a specific temporary tablespace – this gives you some scope for easily monitoring the impact of particular global temporary tables on the total I/O resource usage. After re-running the test but specifying this tablespace as the location for the GTT I got the following results:
TABLE_NAME INDEX_NAME TABLESPACE_NAME -------------------- -------------------------------- ------------------------------ GTT1 GTT1_PK GTT_TEMP GTT1 SYS_IL0000262251C00003$$ GTT_TEMP USERNAME TABLESPACE SEGTYPE SEGFILE# SEGBLK# ------------------------------ ------------------------------- --------- ---------- ---------- TEST_USER GTT_TEMP DATA 202 512 TEST_USER GTT_TEMP INDEX 202 384 TEST_USER GTT_TEMP LOB_DATA 202 128 TEST_USER GTT_TEMP INDEX 202 256
The anomaly disappears – everything is reported as being linked to the gtt_temp tablespace (and that includes the table itself, as reported in view user_tables, though I haven’t included that query in the test or results).
Footnote:
A few months after the first draft of this note I happened to rediscover it (but still failed to publish it) and after a quick search on MOS found the following bug, reported as fixed in 12.2, with a backport currently available to 11.2.0.3 (the document has since been hidden, and the only remaining evidence is a patch for AIX):
Bug 18897516 : GLOBAL TEMPORARY TABLE WITH LOBCOLUMNS CREATE LOB INDEX IN SYSTEM SCHEMA
Interestingly the description says: “lob index created in system tablespace” rather than “lob index incorrectly reported as being in system tablespace”. You do have to be very careful with how you describe things if you don’t want to cause confusion – this “problem” isn’t a space management threat, it’s just an irritating reporting error.
[…] GTT LOBs (Aug 2021) – a delayed report of an error in the 12.1.0.2 data dictionary about lob_index placement, and a reminder that you can associate global temporary tables with a specific temporary tablespace. […]
Pingback by LOB Catalogue | Oracle Scratchpad — January 29, 2022 @ 6:24 pm GMT Jan 29,2022 |
[…] GTT LOBs (Aug 2021) – a delayed report of an error in the 12.1.0.2 data dictionary about lob_index placement, but including a reminder that you can associate global temporary tables with a specific temporary tablespace. […]
Pingback by Design catalogue | Oracle Scratchpad — January 29, 2022 @ 6:27 pm GMT Jan 29,2022 |