贴篇metalink的文章
How To Free Temporary Segment in Temporary Tablespace Dynamically
Purpose
-------
The purpose of this article is to advise on how to free the sort segment in a
temporary tablespace of type TEMPORARY.
Scope & Application
-------------------
DBAs
How to free a sort segment
--------------------------
You want to liberate the sort segment created in its current temporary
tablespace of type TEMPORARY.The storage clause associated with this segment
is not correct and you must change it.But, as explained in [NOTE:102339.1],
the sort segment is only freed by smon on startup of the instance.
Sometimes it will be interesting to change the storage clause of the sort
segment without shutting down the database.Particularly when these storage
clauses are too small, it will be interesting to increase it, to avoid
splitting of the temporary segment on too many extents.
Too many extents on a temporary segment may lead to spinning of smon on the
next startup of the database.Please note that this extent maintenance will
take place immediately after you have freed them.
Answer
------
If you consider a sort segment which is already created on the current
temporary TEMP segment.
SQL> select tablespace_name, CURRENT_USERS, TOTAL_BLOCKS, USED_BLOCKS, FREE_BLOCKS
2from v$sort_segment;
TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------- ------------- ------------ ----------- -----------
TEMP0 120 0 120
You can see that this segment is split on 120 extents.If you consider now the
storage clauses of the tablespace TEMP, you can change the storage parameters
to get a better allocation of sort segments using the ALTER TABLESPACE command.
SQL> select * from dba_tablespaces
2where tablespace_name = 'TEMP';
TABLESPACE_NAMEINITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTSLOGGING EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- ----------
ALLOCATIO PLU
--------- ---
TEMP 65536 65536 1
0 65536 ONLINE TEMPORARY LOGGING DICTIONARY
USER NO
These new storage clauses will be inherited only when recreating the sort
segment.This can be done with the following sql command:
SQL> alter tablespace TEMP
2default storage (pctincrease 0);
Please note, changing the default storage can only affect new objects, because of
which we need to create a new segment (to make the change effective). The old
segment (and its space) is released when it is no longer needed and since it
is cleaned up, the free space will be coalesced too.
This is the expected behaviour. If this is not desirable, then altering the tablespace
during normal operations should be avoided. Depending on the size of the original
segment, you could experience ORA-1562 errors.
If we query now the v$sort_segment:
SQL> select tablespace_name, current_users, total_blocks, used_blocks, free_blocks
2from v$sort_segment;
no rows selected
In dba_segments, the same result is presented:
SQL> select owner, segment_name, segment_type from dba_segments
2where tablespace_name = 'TEMP';
no rows selected
You can now affect new default storage clause associated with TEMP. On the
first next sort, the temporary sort segment will be recreated with the new
storage clauses.
如果觉得《oracle清理temp表空间 请问在oracle中temp表空间如何才能释放。》对你有帮助，请点赞、收藏，并留下你的观点哦！
