I can’t Explain Why June 8, 2010
Posted by mwidlake in internals, performance.Tags: data dictionary, explain plan, SQL
3 comments
Have you ever tried to use Explain Plan and it gives you an error like the below (this is on 10.2)?
DWDBT1> set autotrace on
1 select count(*)
2 from person pers
3 ,person_name pena
4 where pena.pers_id=pers.pers_id
5* and pena.surname='SMITH'
DWDBT1> /
COUNT(*)
----------
23586
1 row selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00959: tablespace 'USER_TEMP' does not exist
SP2-0612: Error generating AUTOTRACE EXPLAIN report
I seem to run into this once a year or so and, as it has happened three times now, this time around my brain had at long last hung onto the details and I remembered the cause. As a clue, I run into this issue when I am using a new development database that was derived from a live one…
As you can see from the above error message, tablespace USER_TEMP does not exist. How can you be referencing a tablespace that does not exist? First thing, does the tablespace really not exist and thus the error is real?
DWDBT1> @ts_lst
Enter value for ts_name: u
old 12: where tablespace_name like upper('&ts_name'||'%')
new 12: where tablespace_name like upper('u'||'%')
Any Key>;
TS_NAME INI_EXT_K NEXT_EXT_K MIN_EX
--------------------------------------------------- ---------- ---------- ------
MAX_EX PCT MIN_EXTLN ST
---------- --------- --------- ---------
UNDOTBS 64 1
2147483645 64 ON
USERS 40 40 1
2147483645 0 40 ON
2 rows selected.
As you can see, there is no tablespace USER_TEMP. So it must be something to do with PLAN_TABLE, the table that underlies EXPLAIN PLAN. So let’s check out that the table exists.
DWDBT1> desc plan_table Name Null? Type ----------------------------------------------------------- -------- --------------------- STATEMENT_ID VARCHAR2(30) PLAN_ID NUMBER TIMESTAMP DATE REMARKS VARCHAR2(4000) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(255) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_ALIAS VARCHAR2(65) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ...
Yes, the table exists. Or a view or something that looks like a table anyway. Let’s check further.
DWDBT1> @obj_lst
Enter value for obj_name: plan_table
old 8: where object_name like upper(nvl('&obj_name','WHOOPS')||'%')
new 8: where object_name like upper(nvl('plan_table','WHOOPS')||'%')
Enter value for sub_name:
old 9: and nvl(subobject_name,'WHOOPS') like upper(nvl('&sub_name','WHOOPS')||'%')
new 9: and nvl(subobject_name,'WHOOPS') like upper(nvl('','WHOOPS')||'%')
Enter value for obj_type: %
old 10: and object_type like upper(nvl('&obj_type','TABLE')||'%')
new 10: and object_type like upper(nvl('%','TABLE')||'%')
Any Key>
OWNER OBJECT_NAME SUBOBJECT_NA OBJ_TYPE CRE_DATE LAST_DDL
---------- -------------------- ------------ ------------ --------------- ------------------------
PUBLIC PLAN_TABLE SYNONYM 10-JUL-07 10-JUL-07 12:19:14
SYS PLAN_TABLE$ TABLE 10-JUL-07 10-JUL-07 12:19:14
If you are not aware, in V10 PLAN_TABLE was replaced with a global temporary table PLAN_TABLE$ and a public synonym of PLAN_TABLE referencing it, which is what you see above. If I quickly pull out a few details of the table, you can see that it is temporary and that is has no tablespace allocated to the table.
1 select table_name,status,temporary TEMP,tablespace_name 2 from dba_tables 3* where owner=user and table_name ='PLAN_TABLE$' DWDBT1> / TABLE_NAME STATUS TEMP TABLESPACE_NAME ------------------------------ -------- ---- ------------------------------ PLAN_TABLE$ VALID Y
The temporary table segment goes into the user’s temporary tablespace (and we are getting close to the cause of the error now, honest). Here is a subset of user details:
USERNAME USER_ID DFLT_TABSPACE ------------ ---------- --------------------------------------------- TEMP_TABSPACE CREATED --------------------------------------------- ----------------- XXADMIN 413 WORKING_128K USER_TEMP 07-DEC-2009 17:34 XXRED 134 DW_COMMON_MG BATCH_TEMP 07-DEC-2009 17:29 DWABCD 414 USERS USER_TEMP 07-DEC-2009 17:3
DWABCD is the user I log in as and it has the temp tablespace set to USER_TEMP, from the original error message. But if I now check for what temporary files are on the system then I see the below:
DWDBT1> select * from dba_temp_files 2 / Any Key...> FILE_NAME ---------------------------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT ---------- ------------------------------ ---------- ---------- --------- ------------ --- MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ---------- ---------- ------------ ---------- ----------- /u01/oradata/dwDBT1/temp01.tdbf 1 TEMP 5368709120 655360 AVAILABLE 1 YES 3.4360E+10 4194302 1280 5367660544 655232 /u01/oradata/dwDBT1/batch_temp01.tdbf 2 BATCH_TEMP 104857600 12800 AVAILABLE 1 YES 3.4360E+10 4194302 1280 103809024 12672 2 rows selected.
Only two and neither are called USER_TEMP.
So, the error is occurring when the call to Explain Plan is trying to generate a temporary segment in the non-existent temp tablespace. The fix is to simply set the TEMPORARY tablespace for the user to one that exists {or I guess you could create a new temporary tablespace of the correct name}:
DWDBT1> alter user dwabcd temporary tablespace temp; User altered. DWDBT1> select count(*) from person; Any Key...> COUNT(*) ---------- 322798 1 row selected. `Elapsed: 00:00:03.96 Execution Plan ---------------------------------------------------------- Plan hash value: 1154882994 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2417 (1)| 00:01:06 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| PERSON | 328K| 2417 (1)| 00:01:06 | ---------------------------------------------------------------------
What is the cause of the problem? Well, Oracle will not let you set the temporary tablespace of a user to a non-existent temporary tablespace, as you can see by my attempt to set my user back to the invalid value:
DWDBT1> alter user dwabcd temporary tablespace USER_TEMP 2 / alter user dwabcd temporary tablespace USER_TEMP * ERROR at line 1: ORA-00959: tablespace 'USER_TEMP' does not exist
But it will import users from another system where the temporary tablespace exists and user have it set as their default temporary tablespace. This is why I run into the problem every year or so. I find myself helping out with the creation of development databases from a live database and this mismatch in temporary tablespaces seems to regularly occur.
Martin Widlake's Yet Another Oracle Blog 