oracleの一時表領域temporary tablespace
6210 ワード
temporary tablespace , metalink , , 。 。
。
metalink:Temporary Segments Are Not Being De-Allocated After a Sort(Note:1039341.6)
sort
======
temporary tablespace , sort , : temporary extent temporary tablespace, ora-1652( temp ), , ,smon 。
Scope & Application
===================
TEMPORARY TABLESPACE TEMPORARY,TEMPORARY TABLESPACE , shutdown。
PERMANENT,SMON process 。
TEMPORARY , startup , TEMPORARY tablespace statement sort segment, segment , restart, V$SORT_SEGMENT sort segments 。
PERMANENT tablespace , smon statement temporary segments, 。
:
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE ,
TABLESPACE_NAME
from DBA_SEGMENTS
where SEGMENT_TYPE = 'TEMPORARY';
to give results similar to:
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
----- ------------ ------------ ---------------
SYS 4.2 TEMPORARY TEMP
Note: segment_name and tablepace_name are likely to be different.
tablespace :
select TABLESPACE_NAME,
CONTENTS
from DBA_TABLESPACES
where TABLESPACE_NAME in (<list of tablespaces from above> );
to give results similar to:
TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY
TEMP1 PERMANENT
=====
1)
TEMPORARY TEMPORARY TABLESPACE, (7.3 ), ora-1652 , :-)
oracle startup , oracle shutdown , extent, storage 。
, temporary extent ,tablespace , extent , , extent free, sort available , temporary extent 。
2)
PERMANENT tablespace temporary segment( , ), parameter file :
event="10061 trace name context forever, level 10"
event="10269 trace name context forever, level 10"
smon temporary segment clean up coalescing, oraus.msg :
10061, 00000, "disable SMON from cleaning temp segments
10269, 00000, "Don't do coalesces of free space in SMON"
// *Cause: setting this event prevents SMON from doing free space coalesces
( ,smon temporary segment clean up coalescing。 metalink )
V$SORT_SEGMENT temp segment free being used
For example:
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
>>> DURING the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
---------------- ------------ ----------- -----------
TEMP 590 590 0
>>> AFTER the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
--------------- ------------ ------------ -----------
TEMP 590 0 590
sort ,segment used,sort , free。 extents SEP(sort extent pool) 。(SEP SGA temp extent , metalink )
, tablespace temporary :
ALTER TABLESPACE <TABLESPACE_NAME> TEMPORARY;
temp temp 9i shutdown startup
temporary temporary tablespace, , temp extent, free, 。temp autoextend, .
, temporary tablespace PERMANENT ,smon 。 TEMPORARY,smon , locally managed ( ) 。
pctincrease 0。 1, :
alter tablespace temp
default storage(pctincrease 1);
smon 。 :
alter tablespace temp coalesce; ( : temp 。)
Oracle ?
: , , , , 。 , Oracle , GB, Oracle Session , , 5s, 500MB , 。 。
Oracle , , , ( ) ,Oracle , 1GB, 1GB, , , 。 。
1. , 。 , , , , 。
2. 。 , , , , 。
3. 。Oracle , , , , 。
, , , 。
:
1.SQL , 。
2. , , 。
3. 。
, 。
Oracle: ...
1、 :
、 、 , 。
, , ( ), 。
2、 :
sql , 。
( !)。Oracle , 4 , 3 ! 4 , ? :
view0( table0,table1,table2),view1( table0,table3,table4),view2( table0,table5,table6)
view0、view1、view2 view。
!
, , !
:
1. ( , )
2. ,( , , ), ,
3. , ,OLTP ,
,
: , sql , stactpack ,