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?

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.

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}:

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:

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.

Like this:

Related

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:

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).