Trying to shutdown a database which takes about 10 mins sometimes to shutdown. According to my research I found out that smon creates temporary segments which it tries to colensce (release) and that takes time. I believe Oracle was saying that by increasing the sort area size this might help. My question is how do i know on which tablespace has these temporary segments been created?
What about increasing hash area size?
Do you know if there were active sessions when you initiated shutdown?
Hope you are aware of the fact that if you use "shutdown" oracle waits for
all active session to disconnect (but would prevent new connections) and
if you use "shutdown immediate" and If you did have any active session,
oracle still has to disconnect all those sessions and their active transaction.
jlakhani, SORT_AREA_SIZE is an init.ora parameter that only affect under what conditions a user will sort to disk, rather than in memory. setting this will have very little or no effect on your slow shutdown.
first i would check to what tablespace users are using for temp segments:
SQL> SELECT DISTINCT TEMPORARY_TABLESPACE FROM DBA_USERS;
From this query you should not see more than a few thousand extents. This is the coalescing that must go on before shutdown. I have 409 extents of 5M in size. my shutdowns are pretty quick, so this is OK. furthermore, you should only get one line from the query as written, since you want your extents to be the same size.
You should make sure you are using temporary tablespaces for temp segs.
1 select tablespace_name from dba_tablespaces
2* where CONTENTS ='TEMPORARY'
If you are using 8i, you might want to consider temporary tablespaces (listed in DBA_TEMP_FILES, rather than DBA_DATA_FILES). however, this is just a topic for consideration and the above info should give you some ways to check out if temp segs are causing your slow shutdowns.