Today I came across with some interesting issue and want to share that with all of you…

In one of my databases, I have a permanent tablespace called CHG_DATA. In this tablespace I have identified some temporary segments residing.
The below is the query to find out the information about temporary segments in permanent tablespace

SQL > select tablespace_name, owner, sum(bytes/1024/1024) from dba_segments
where segment_type = ‘TEMPORARY’ group by tablespace_name, owner;
In my case it is showing that CHG_DATA tablespace is consumed with 520MB of temporary segments

TABLESPACE_NAME     OWNER          SUM(BYTES/1024/1024)
——————-                 ————       ——————–
CHG_DATA                          SYS                   520
Now, its quite a bit surprise…. we know that temporary segments will be created in temporary tablespace, but will temporary segments gets created in permanent tablespace?

answer is YES IT WILL. What are the situations then?

1. CREATE TABLE AS SELECT
2. ALTER TABLE MOVE
3. CREATE INDEX
4. ALTER INDEX REBUILD etc

For any of the above operations that we do, Oracle internally will create a temporary table/index which occupies disk space. Once the operation completes, then oracle will mark those table/index as permanent segment. So, this means once the work is done, all temporary segments will become permanent. SMON will take the responsibility in clearing these temporary segments.

But in my case, why it is still showing some temporary segments? It could be because of any of the following reasons

Possibility 1: May be any user is executing above commands right now. To find out that, you can make use of v$sql and v$session views.

(I have verified possibility 1 and there are no users at that time.)

or

Possibility 2: When user is executing any of above commands, it failed all of a sudden (due to some reason) and in that case SMON didn’t cleaned up those temporary segments.

(If clean up has not done because of possiblity 2 specified above, then restarting the instance will help you [because SMON will do cleanup while starting the instance]. So, I restarted the database, but still it is showing the same.)

or

Possibility 3: It is possible that SMON cleanup of temporary segments has been disabled by setting an EVENT in the instance.

Note: If event 10061 is set at level 10 in the database, it implies that cleanup of temp segments by SMON is disabled

(I even verified this using below commands and it was not disabled

SQL> show parameter events

SQL> alter system set events ‘10061 trace name context off’;)
Finally, I forced SMON to do cleanup using

SQL> ALTER TABLESPACE CHG_DATA coalesce;

But still no luck 😦
Then after going through MOS doc id 1271120.1, I came to know that the possible reason could be that tablespace is in READ ONLY mode (which I have not checked earlier)
Querying DBA_tablespaces shows that the tablespace is in read-only mode.

SQL> Select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
—————————— ———
CHG_DATA                       READ ONLY
This might be caused by having the CHG_DATA in read-only mode before the temporary segments were cleaned up. This causes SMON to not be able to clean them afterwards even when forcing it to using ‘alter tablespace … coalesce’.

So, Solution for this issue is to make that tablespace READ WRITE, do cleanup and again make it READ ONLY.

SQL> alter tablespace CHG_DATA read write;

SQL> alter tablespace CHG_DATA coalesce;

SQL> alter tablespace CHG_DATA read only;

Have a HAPPY LEARNING 🙂