Dynamic temporary tablespace switching bug/feature.

Up to last day I was absolutely sure that in Oracle terms USER and SCHEMA actually synonyms. The schema does not have attributes and exists just like container for user objects. I was doing some R&D about allocating different TEMPORARY Tablespaces to different users in a system when discover that the same user use different tablespaces in different sessions, without any configuration changes.

I have two temporary tablespaces TEMP and TESTTEMP. Tablespace TEMP is default and associated to all users except TEST.

SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='TEST';

USERNAME	TEMPORARY_TABLESPACE
TEST	TESTTEMP

We have table TESTTABLE with 100000 rows. And I create spacial sql with massive sort operation.

SELECT * FROM TEST.TESTTABLE a FULL OUTER JOIN TEST.TESTTABLE b on 
a.COL1=b.COL2 FULL OUTER JOIN TEST.TESTTABLE c on b.COL1=c.COL1 ORDER BY 
1,2,3,4,5,6,7,8,9;

By default the query use expected TESTTEMP tablespaces

SELECT S.sid, S.username, SUM (T.blocks) * 8192 / 1024 / 1024 mb_used, T.tablespace
FROM gv$sort_usage T, gv$session S
WHERE T.session_addr = S.saddr AND t.inst_id=S.inst_id
GROUP BY S.sid, S.username, T.tablespace
ORDER BY sid;

SID	USERNAME	MB_USED	TABLESPACE
23	TEST	408	TESTTEMP

But when I change the CURRENT_SCHEMA in a session (ALTER SESSION SET CURRENT_SCHEMA=SYSTEM;) I got different results

SID	USERNAME	MB_USED	TABLESPACE
23	TEST	487	TEMP

The same query using the same objects from the same session without changing user configuration use different Temporary Tablespaces. I believe this feature can be very helpful for fine grand sort operation development.

Couple bonus remarks:
a) Calling SQL from PL/SQL objects created with definer rights works like switching to the object owner schema.
b) Using synonyms does not switch temporary tablespace to the tablespace of the referencing objects.

Simply Renaming A Schema

What started off as just moving a database from UAT to QA soon turned into something slightly more complex, with an interesting discovery for me.

For reasons that preceed my involvement in this project, not only did the database name indicate the region (UAT, QA, production), but the username for the application schema also indicated the region. This didn’t fit with the standard, but the work required to change this in all regions wasn’t considered viable.

Anyway, this left me in a position of having copied and renamed the UAT database to QA, but the application username/schema indicated UAT. This was clearly a recipe for later confusion… What I needed was to rename the schema, but unfortunately Oracle doesn’t provide this as one-liner.

This is a 9.2.0.8 database, so it was time for imp and exp… “Just a case of export user, create new appropriately named user, import from original user to new user, drop original user if all goes smoothly.” Well, it didn’t.

The export went fine, but when importing I hit ORA-02304 errors in more than one place. This was a new one on me. An example of the errors is shown below:

IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "xxxxxxx" TIMESTAMP 'yyyy-mm-dd:hh24:mi:ss' OID '###############"


Oracle was complaining about the fact that I was trying to create objects with same object identifier (OID) as objects that already exist in the database. I had decided that keeping the original schema in place until I’d the new user was successfully imported was a sensible thing to do just in case things didn’t go to plan, but as it turned out keeping the original user in the database whilst importing under the new user was the root of my problem.

If you found this page because, like me, you haven’t yet got to the point of checking MetaLink as the first port of call then check out MetaLink note 1066139.6 for more detail.