system表空间被什么使用

2^#

发表于 2012-4-17 21:27:13

action plan:

执行以下命令并上传 spool出来的 action.lst

spool action

select DEFAULT_TABLESPACE from dba_users where username=SYS_CONTEXT ('USERENV', 'SESSION_USER') ;

REM tablespace report

set linesize 200

select a.tablespace_name,
   round(a.bytes_alloc / 1024 / 1024) megs_alloc,
   round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
   round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
   round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
   100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
   round(maxbytes / 1048576) Max
  from (select f.tablespace_name,
            sum(f.bytes) bytes_alloc,
            sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
      from dba_data_files f
      group by tablespace_name) a,
   (select f.tablespace_name, sum(f.bytes) bytes_free
      from dba_free_space f
      group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
   round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
   round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
         1048576) megs_free,
   round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
   round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
         sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
   100 -
   round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
         sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
   round(sum(f.maxbytes) / 1048576) max
  from sys.v_$TEMP_SPACE_HEADER h,
   sys.v_$Temp_extent_pool  p,
   dba_temp_files          f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 1
/

create table testabcs as select * from dba_objects;

REM tablespace report

set linesize 200

select a.tablespace_name,
   round(a.bytes_alloc / 1024 / 1024) megs_alloc,
   round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
   round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
   round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
   100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
   round(maxbytes / 1048576) Max
  from (select f.tablespace_name,
            sum(f.bytes) bytes_alloc,
            sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
      from dba_data_files f
      group by tablespace_name) a,
   (select f.tablespace_name, sum(f.bytes) bytes_free
      from dba_free_space f
      group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
   round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
   round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
         1048576) megs_free,
   round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
   round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
         sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
   100 -
   round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
         sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
   round(sum(f.maxbytes) / 1048576) max
  from sys.v_$TEMP_SPACE_HEADER h,
   sys.v_$Temp_extent_pool  p,
   dba_temp_files          f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 1
/

select segment_name,blocks from dba_segments where tablespace_name='SYSTEM' order by 2 asc;

3^#

发表于 2012-4-18 23:55:46

见附件：附件内容，俺看不出来啥东西。3Q

action.txt

302.34 KB, 下载次数: 853

4^#

发表于 2012-4-19 14:17:06

今天在11g的库上建表，建了8张，system多了3M的空间，
表是分区表，字段个数200+。列上上加了comment.

为什么？？

5^#

发表于 2012-4-19 14:35:11

ODM DATA:

SQL> select DEFAULT_TABLESPACE from dba_users where username=SYS_CONTEXT ('USERENV', 'SESSION_USER') ;

DEFAULT_TABLESPACE
------------------------------
SONG

DEFAULT_TABLESPACE 是 SONG    而非 SYSTEM  ，用户创建的数据段默认存放在SONG表空间

TABLESPACE_NAME             MEGS_ALLOC  MEGS_FREE  MEGS_USED PCT_FREE PCT_USED       MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SONG                               500       325       175       65       35       500
SYSAUX                               270       12       258       5       95    32768
SYSTEM                               500       19       481       4       96    32778
TEMP                                  37       37       0       100       0    32768
TEST_TABLESPACE                      50       50       0       100       0       50
UNDOTBS1                            265       232       33       88       12    32768
USERS                                  5       3       2       56       44    32768

SYSTEM 表空间上存放了数据字典对象已分配 500MB 使用了 481 MB ，字典  IDL_UB1$ 占用了 167MB SOURCE$ 41MB ，说明数据库中可能有较多的PL/SQL 对象，  这个空间使用量属于正常水平。

不知道你的疑问在哪里？

6^#

发表于 2012-4-20 00:17:18

我的疑问是：我怎么可以看到system表空间的增长？？（不是往里放数据）

system表空间存放数据字典的信息，比如说我新建表的话，应该会在数据字典中加入一些信息，那么system表空间的使用应该会稍微多一点。。但我建了20张表， system表空间大小没有变化。。。

7^#

发表于 2012-4-20 10:05:30

SQL>  select count(*) from tab$;

  COUNT(*)
----------
   896

SQL> alter session set events '10046 trace name context forever,level 1';

Session altered.

SQL>  create table tv tablespace users  as select  * from dba_objects where rownum <2;

Table created.

SQL>  select count(*) from tab$;

  COUNT(*)
----------
   897

create table 引发的数据字典递归调用，包括对 obj$ 、tab$等基表插入数据

[oracle@vrh8 udump]$ grep -i "insert" testchar_ora_20353.trc
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr, spare1, scanhint) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16,DECODE(:17,0,NULL,:17),:18)
insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols,audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances,dataobj#,avgspc_flb,flbcnt,trigflag,spare1,spare6)values(:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,decode(:9,0,null,:9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,decode(:26,1,null,:26),decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)

但是插入几条或者几十条记录并不足以让 tab$、col$这些基表高水位上升或者分配更多的 tablespace 空间，所以你看不到 system表空间有变化很正常。

如果仅仅创建几个或十几个对象就让system 表空间发生剧烈变化，那么显然是不可接受的。

8^#

发表于 2012-4-20 10:10:46

TABLESPACE_NAME             MEGS_ALLOC  MEGS_FREE  MEGS_USED PCT_FREE PCT_USED       MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYSAUX                               120       68       52       57       43    32768
SYSTEM                               300       68       232       23       77    32768

利用匿名块在非system表空间上创建10000个表

SQL> begin
  2  for i in 1..10000 loop
  3  execute immediate 'create table tabn'||i||' tablespace users as select * from dba_objects where rownum=1';
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

TABLESPACE_NAME             MEGS_ALLOC  MEGS_FREE  MEGS_USED PCT_FREE PCT_USED       MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYSAUX                               120       68       52       57       43    32768
SYSTEM                               300       39       261       13       87    32768

这样可以看到 system表空间的 MEGS_FREE 空闲空间量从 68 下降到 39

yueyangflash

9^#

发表于 2012-4-20 10:47:39

太详细了！增加或删除几十行记录不会使表空间的大小产生多少变化！

10^#

发表于 2012-4-20 23:57:45

谢谢ML ，明白了。