I can’t Explain Why June 8, 2010
Posted by mwidlake in internals, performance.Tags: data dictionary, explain plan, SQL
trackback
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 
Excellent explanation and analysis of the problem.
One minor issue that I have experienced, at least with Datapump Import, is that if the temporary tablespace specified for the user does not exist, the user will not be created in the database. For example, the import log may show something like this:
Is it possible that the temporary tablespace existed at one point (when the user was created), but was later dropped (maybe it grew to be very large due to an out of control query)?
A small test:
In the second session:
So, the above works, now back in session 1:
In session 2:
Thanks for that Charles.
That’s a very nice demo that you can drop a temporary tablespace even though users reference it (and I remember being caught out by that a couple of years ago). However, I am not 100% sure that was the cause of the issues in the current situation. I am not sure as I was not involved with the actual creation of the database.
According to my source, the new database was created with an non-datapump full export/import with no data.Then there were various changes made to the structure to drop unwanted partitions and the like and then selected data exported/imported. My source is not aware of ever dropping the temporary tablespace, but then tablespaces were pre-created before the import as the underlying storage was different. We suspect, but cannot easily prove, that the users were imported without the relevant temporary tablespace being created and without errors (though maybe warnings that were ignored).
Thanks,
Martin
I think changing the default temporary tablespace may fix the issue as well, although it may not be the way you want to fix it.
select property_value from database_properties where property_name = ‘DEFAULT_TEMP_TABLESPACE’ (this was introduced in 9iR2>).
alter database default temporary tablespace
I think this should over-ride a dropped temporary tablespace for a user who still has it defined as their default.