Like permanent tables, temporary tables are defined in the data dictionary. Temporary segments are allocated when data is first inserted. Until data is loaded in a session the table appears empty. Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.
rajesh@11GR2> select (select decode(extent_management,'LOCAL','*',' ') ||
2 decode(segment_space_management,'AUTO','a ','m ')
3 from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
4 nvl(a.tablespace_name,'UNKOWN')) name,
5 kbytes_alloc kbytes,
6 kbytes_alloc-nvl(kbytes_free,0) used,
7 nvl(kbytes_free,0) free,
8 ((kbytes_alloc-nvl(kbytes_free,0))/
9 kbytes_alloc)*100 pct_used,
10 nvl(largest,0) largest,
11 nvl(kbytes_max,kbytes_alloc) Max_Size,
12 decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
13 from ( select sum(bytes)/1024 Kbytes_free,
14 max(bytes)/1024 largest,
15 tablespace_name
16 from sys.dba_free_space
17 group by tablespace_name ) a,
18 ( select sum(bytes)/1024 Kbytes_alloc,
19 sum(maxbytes)/1024 Kbytes_max,
20 tablespace_name
21 from sys.dba_data_files
22 where 1 = 0
23 group by tablespace_name
24 union all
25 select sum(bytes)/1024 Kbytes_alloc,
26 sum(maxbytes)/1024 Kbytes_max,
27 tablespace_name
28 from sys.dba_temp_files
29 group by tablespace_name )b
30 where a.tablespace_name (+) = b.tablespace_name;
NAME KBYTES USED FREE PCT_USED LARGEST MAX_SIZE PCT_MAX_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
*m TEMP 16376 16376 0 100 0 33554416 .048804306
Now, the space allocated in Temporary tablespace ( TEMP ) is 16376 KB before loading data's into Global Temporary tables.
rajesh@11GR2>
rajesh@11GR2> create global temporary table t(
2 x ,
3 y ,
4 z )
5 on commit preserve rows
6 as
7 select level,rpad('*',100,'*'),sysdate
8 from dual
9 connect by level <= 100000;
Table created.
Elapsed: 00:00:00.29
rajesh@11GR2>
rajesh@11GR2> select (select decode(extent_management,'LOCAL','*',' ') ||
2 decode(segment_space_management,'AUTO','a ','m ')
3 from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
4 nvl(a.tablespace_name,'UNKOWN')) name,
5 kbytes_alloc kbytes,
6 kbytes_alloc-nvl(kbytes_free,0) used,
7 nvl(kbytes_free,0) free,
8 ((kbytes_alloc-nvl(kbytes_free,0))/
9 kbytes_alloc)*100 pct_used,
10 nvl(largest,0) largest,
11 nvl(kbytes_max,kbytes_alloc) Max_Size,
12 decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
13 from ( select sum(bytes)/1024 Kbytes_free,
14 max(bytes)/1024 largest,
15 tablespace_name
16 from sys.dba_free_space
17 group by tablespace_name ) a,
18 ( select sum(bytes)/1024 Kbytes_alloc,
19 sum(maxbytes)/1024 Kbytes_max,
20 tablespace_name
21 from sys.dba_data_files
22 where 1 = 0
23 group by tablespace_name
24 union all
25 select sum(bytes)/1024 Kbytes_alloc,
26 sum(maxbytes)/1024 Kbytes_max,
27 tablespace_name
28 from sys.dba_temp_files
29 group by tablespace_name )b
30 where a.tablespace_name (+) = b.tablespace_name;
NAME KBYTES USED FREE PCT_USED LARGEST MAX_SIZE PCT_MAX_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
*m TEMP 16376 16376 0 100 0 33554416 .048804306
Now, the space allocated in Temporary tablespace ( TEMP ) is still 16376 KB, so where does the segments for Temp tablespace is getting allocated and how to find that?
These information will be available in V$TEMPSEG_USAGE.
rajesh@11GR2> exec print_table(' select * from v$tempseg_usage ');
USERNAME------------ RAJESH
USER---------------- RAJESH
SESSION_ADDR-------- 2F3580BC
SESSION_NUM--------- 6
SQLADDR------------- 2A0A883C
SQLHASH------------- 1029988163
SQL_ID-------------- 9babjv8yq8ru3
TABLESPACE---------- TEMP
CONTENTS------------ TEMPORARY
SEGTYPE------------- DATA
SEGFILE#------------ 201
SEGBLK#------------- 128
EXTENTS------------- 1
BLOCKS-------------- 128
SEGRFNO#------------ 1
USERNAME------------ RAJESH
USER---------------- RAJESH
SESSION_ADDR-------- 2F3580BC
SESSION_NUM--------- 6
SQLADDR------------- 2A0A883C
SQLHASH------------- 1029988163
SQL_ID-------------- 9babjv8yq8ru3
TABLESPACE---------- TEMP
CONTENTS------------ TEMPORARY
SEGTYPE------------- DATA
SEGFILE#------------ 201
SEGBLK#------------- 1920
EXTENTS------------- 14
BLOCKS-------------- 1792
SEGRFNO#------------ 1
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.54
rajesh@11GR2>
Temporary segments are allocated as data added into database and segments are deallocated at the end of the Transaction ( for Transaction specific temp tables ) or the end of session ( for session specific temp tables). But the Temporary data files added to that Temporary tablespace will never shrink by default untill you request.
rajesh@11GR2> insert /*+ append */ into t
2 select level,rpad('*',100,'*'),sysdate
3 from dual
4 connect by level <= 100000;
100000 rows created.
Elapsed: 00:00:02.49
rajesh@11GR2> commit;
Commit complete.
Elapsed: 00:00:00.00
rajesh@11GR2> select count(*) from t;
COUNT(*)
----------
200000
Elapsed: 00:00:01.15
rajesh@11GR2> select (select decode(extent_management,'LOCAL','*',' ') ||
2 decode(segment_space_management,'AUTO','a ','m ')
3 from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
4 nvl(a.tablespace_name,'UNKOWN')) name,
5 kbytes_alloc kbytes,
6 kbytes_alloc-nvl(kbytes_free,0) used,
7 nvl(kbytes_free,0) free,
8 ((kbytes_alloc-nvl(kbytes_free,0))/
9 kbytes_alloc)*100 pct_used,
10 nvl(largest,0) largest,
11 nvl(kbytes_max,kbytes_alloc) Max_Size,
12 decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
13 from ( select sum(bytes)/1024 Kbytes_free,
14 max(bytes)/1024 largest,
15 tablespace_name
16 from sys.dba_free_space
17 group by tablespace_name ) a,
18 ( select sum(bytes)/1024 Kbytes_alloc,
19 sum(maxbytes)/1024 Kbytes_max,
20 tablespace_name
21 from sys.dba_data_files
22 where 1 = 0
23 group by tablespace_name
24 union all
25 select sum(bytes)/1024 Kbytes_alloc,
26 sum(maxbytes)/1024 Kbytes_max,
27 tablespace_name
28 from sys.dba_temp_files
29 group by tablespace_name )b
30 where a.tablespace_name (+) = b.tablespace_name;
NAME KBYTES USED FREE PCT_USED LARGEST MAX_SIZE PCT_MAX_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
*m TEMP 29688 29688 0 100 0 33554416 .088477177
Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> discon
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
rajesh@10GR2> connect rajesh/*****
Connected.
rajesh@11GR2> select count(*) from t;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
rajesh@11GR2> select (select decode(extent_management,'LOCAL','*',' ') ||
2 decode(segment_space_management,'AUTO','a ','m ')
3 from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
4 nvl(a.tablespace_name,'UNKOWN')) name,
5 kbytes_alloc kbytes,
6 kbytes_alloc-nvl(kbytes_free,0) used,
7 nvl(kbytes_free,0) free,
8 ((kbytes_alloc-nvl(kbytes_free,0))/
9 kbytes_alloc)*100 pct_used,
10 nvl(largest,0) largest,
11 nvl(kbytes_max,kbytes_alloc) Max_Size,
12 decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
13 from ( select sum(bytes)/1024 Kbytes_free,
14 max(bytes)/1024 largest,
15 tablespace_name
16 from sys.dba_free_space
17 group by tablespace_name ) a,
18 ( select sum(bytes)/1024 Kbytes_alloc,
19 sum(maxbytes)/1024 Kbytes_max,
20 tablespace_name
21 from sys.dba_data_files
22 where 1 = 0
23 group by tablespace_name
24 union all
25 select sum(bytes)/1024 Kbytes_alloc,
26 sum(maxbytes)/1024 Kbytes_max,
27 tablespace_name
28 from sys.dba_temp_files
29 group by tablespace_name )b
30 where a.tablespace_name (+) = b.tablespace_name;
NAME KBYTES USED FREE PCT_USED LARGEST MAX_SIZE PCT_MAX_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
*m TEMP 29688 29688 0 100 0 33554416 .088477177
Elapsed: 00:00:00.01
rajesh@11GR2>
Now even after records got removed for Global temporary tables, Temporary data files doesn't shrink by default untill you Alter tablespace to Shrink.
rajesh@11GR2> alter tablespace TEMP shrink space;
Tablespace altered.
Elapsed: 00:00:00.28
rajesh@11GR2> select (select decode(extent_management,'LOCAL','*',' ') ||
2 decode(segment_space_management,'AUTO','a ','m ')
3 from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
4 nvl(a.tablespace_name,'UNKOWN')) name,
5 kbytes_alloc kbytes,
6 kbytes_alloc-nvl(kbytes_free,0) used,
7 nvl(kbytes_free,0) free,
8 ((kbytes_alloc-nvl(kbytes_free,0))/
9 kbytes_alloc)*100 pct_used,
10 nvl(largest,0) largest,
11 nvl(kbytes_max,kbytes_alloc) Max_Size,
12 decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
13 from ( select sum(bytes)/1024 Kbytes_free,
14 max(bytes)/1024 largest,
15 tablespace_name
16 from sys.dba_free_space
17 group by tablespace_name ) a,
18 ( select sum(bytes)/1024 Kbytes_alloc,
19 sum(maxbytes)/1024 Kbytes_max,
20 tablespace_name
21 from sys.dba_data_files
22 where 1 = 0
23 group by tablespace_name
24 union all
25 select sum(bytes)/1024 Kbytes_alloc,
26 sum(maxbytes)/1024 Kbytes_max,
27 tablespace_name
28 from sys.dba_temp_files
29 group by tablespace_name )b
30 where a.tablespace_name (+) = b.tablespace_name;
NAME KBYTES USED FREE PCT_USED LARGEST MAX_SIZE PCT_MAX_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
*m TEMP 2040 2040 0 100 0 33554416 .006079677
Elapsed: 00:00:00.04
rajesh@11GR2>
No comments:
Post a Comment