Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 16 April 2013

If found Temporary Segment in Permanent Tablespace?

If there is a situation when you see the “temporary segment” in permanent tablespace hanging around and not getting cleaned up. These temporary segments take actual disk space when SMON fails to perform its assign job to clean up this temporary segment.
Overview: Generally temporary segment is created in temporary tablespace, but some of the circumstances when temporary segment gets created in permanent tablespace such as:
For any of the above operations when you perform, 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. Thus indicating the work is done all temporary segments will become permanent. SMON will take the responsibility in clearing these temporary segments. If there is situation when you see the “temporary segment” in permanent tablespace hanging around and SMON fails to perform its assign job to clean up this temporary segment.
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;
TABLESPACE_NAME     OWNER          SUM(BYTES/1024/1024)
------------------- ------------   --------------------
SDH_TIMES           SYS            34576
SDH_INDEX           SYS            4120
SDH_HRMS            SYS            44284.875
SDH_EDSS            SYS            14.69
SDH_SHTR            SYS            41452.39
SDH_FIN             SYS            208.425
Here we can see the tablespace SDH_SHTR,SDH_HRMS having large temporary segment. But in his case, why it is still showing some temporary segments? It could be because of any of the following reasons. Find the correct reason perform the necessary action.
Reason 1: Possibly any DDL is active which can create temporary segment. To find such DDL, you can make use of join query on v$sql and v$Session views:
SQL> select pid from v$process where username= ‘owner_name’;
SQL> alter session set tracefile_identifier='TEMPORARY_SEGMENTS';
Open the corresponding trace file and check the “current sql”
·        If it is DDL like create table as select, alter table move or index rebuild, then wait for the operation to complete. 
·        If there is no PID returned then these segments are “stray segments” and needs to clean manually.
You can force SMON to clean up the temporary segments with oradebug
Connect with SQL*Plus as sysdba
oradebug wakeup <smonprocessnb> 
Reason 2: Possibly user command is executing and suddenly it failed due to any reason in that case SMON didn’t clean up those temporary segments. Try to restart the instance will help you to clean up the resource.
Finally, He tried to force SMON to do cleanup using below command
Reason 3
Check possible that tablespace is in READ ONLY mode, which cause SMON to not be able to clean them even if forcing to coalesce. If this the reason then try to turn back the tablespace in read/write mode and then force to coalesce the tablespace.
SQL> Select tablespace_name, status from dba_tablespaces;
-------------------     ---------
SDH_SHTR                READ ONLY
SQL>alter tablespace SDH_SHTR read write;
SQL>alter tablespace SDH_SHTR coalesce;
SQL>alter tablespace SDH_SHTR read only;
Reason 4: Finally, It is also possible that SMON cleanup of temporary segments has been disabled by setting an EVENT in the instance. If an event 10061 is set at level 10 in the database, it implies that cleanup of temp segments by SMON is disabled
Try to verify it do the following necessary changing.
SQL> show parameter events
SQL> alter system set events ’10061 trace name context off’;


Post a Comment