Re: no free space in tablespace

From: thiagomz <thiagomz_at_gmail.com>
Date: Wed, 16 Jul 2008 09:43:38 -0300
Message-ID: <487ded99$0$25950$6e1ede2f@read.cnntp.org>

kawu escreveu:
> Hi
>
> Today I have problem. In tablespace I don't have free space. I add
> datafile to that tablespce. Should I do or check something else to be
> sure that everything is ok?
>
> Thanks in advance,
> kawu.

Kawu,

Use that SQL:

COL KTABLESPACE   FOR A20      HEADING 'Tablespace'
COL KTBS_SIZE     FOR 999,990  HEADING 'Tamanho|atual'       JUSTIFY RIGHT
COL KTBS_EM_USO   FOR 999,990  HEADING 'Em uso'              JUSTIFY RIGHT
COL KTBS_MAXSIZE  FOR 999,990  HEADING 'Tamanho|maximo'      JUSTIFY RIGHT
COL KFREE_SPACE   FOR 999,990  HEADING 'Espaco|livre atual'  JUSTIFY RIGHT
COL KSPACE        FOR 999,990  HEADING 'Espaco|livre total'  JUSTIFY RIGHT
COL KPERC         FOR 990      HEADING '%|Ocupacao'          JUSTIFY RIGHT

break on report

compute sum label Total: of ktbs_size    on report
compute sum              of ktbs_em_uso  on report
compute sum              of ktbs_maxsize on report
compute sum              of kfree_space  on report
compute sum              of kspace       on report

select t.tablespace_name ktablespace,
        substr(t.contents, 1, 1) tipo,
        trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024) ktbs_em_uso,
        trunc(d.tbs_size/1024/1024) ktbs_size,
        trunc(d.tbs_maxsize/1024/1024) ktbs_maxsize,
        trunc(nvl(s.free_space, 0)/1024/1024) kfree_space,
        trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 
0))/1024/1024) kspace,
        decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 

0))*100/d.tbs_maxsize)) kperc
from

   ( select SUM(bytes) tbs_size,

            SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,

            tablespace_name tablespace
     from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, 
tablespace_name
            from dba_data_files
            union all
            select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, 
tablespace_name
            from dba_temp_files
          )
     group by tablespace_name

   ) d,
   ( select SUM(bytes) free_space,

            tablespace_name tablespace
     from dba_free_space
     group by tablespace_name

   ) s,
   dba_tablespaces t
where t.tablespace_name = d.tablespace(+) and

       t.tablespace_name = s.tablespace(+) order by 8;

Regards,

thiagomz Received on Wed Jul 16 2008 - 07:43:38 CDT