Re: Space really used on TBS

On Apr 28, 10:42 am, Mauro Pagano <mauro.pag...@xxxxxxxxx> wrote:
Hi,
executing the following query

SELECT b.tablespace_name, a.*,
       TRUNC (a.segment_size / tbs_size * 100, 2) segment_perc_used,
       b.tbs_freespace,
       TRUNC (b.tbs_freespace / c.tbs_size * 100, 2) freespace_perc,
       c.tbs_size
  FROM (SELECT SUM (BYTES) / 1024 / 1024 segment_size
          FROM dba_extents
         WHERE tablespace_name = 'USER_CDC_DATA') a,
       (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024
tbs_freespace
            FROM dba_free_space
           WHERE tablespace_name = 'USER_CDC_DATA'
        GROUP BY tablespace_name) b,
       (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024 tbs_size
            FROM dba_data_files
           WHERE tablespace_name = 'USER_CDC_DATA'
        GROUP BY tablespace_name) c

I get this result

USER_CDC_DATA,112,0.72,5076.8125,32.75,15500

where 112Mb are used (0.72% of tbs total available space)
5076Mb are free (32.75% of tbs total available space)
15500Mb is the tbs size

Why I have less then 1% used and only 32.75% free?
Where I'm wasting space?
Please note that shrink objects on tbs doesn't provide any benefit.

Regards
Mauro

Possibly one or more of your datafiles for that tablespace is offline;
try this modification to your query and see what is returned:

SELECT b.tablespace_name, a.*,
TRUNC (a.segment_size / tbs_size * 100, 2) segment_perc_used,
b.tbs_freespace,
TRUNC (b.tbs_freespace / c.tbs_size * 100, 2) freespace_perc,
c.tbs_size
FROM (SELECT SUM (BYTES) / 1024 / 1024 segment_size
FROM dba_extents
WHERE tablespace_name = 'USER_CDC_DATA') a,
(SELECT tablespace_name, SUM (BYTES) / 1024 / 1024
tbs_freespace
FROM dba_free_space
WHERE tablespace_name = 'USER_CDC_DATA'
GROUP BY tablespace_name) b,
(SELECT tablespace_name, SUM (BYTES) / 1024 / 1024 tbs_size
FROM dba_data_files
WHERE tablespace_name = 'USER_CDC_DATA'
AND status = 'AVAILABLE'
GROUP BY tablespace_name) c


David Fitzjarrell
.

Relevant Pages

  • Re: limiting table access and RWOP queries
    ... your code is not executing a stored query. ... Queries that are executed through code, ... > If I log in as the owner of the table, ...
    (microsoft.public.access.security)
  • RE: Output Query Problem
    ... What is the total record count of the resulting Query? ... having a problem with my VBA code executing when the DB is on the server it ... does not output my query to create an excel file on the server. ... I would get the msgbox "no Data, no report" (an ...
    (microsoft.public.access.modulesdaovba)
  • Re: long running select min(timestamp) query
    ... the query seems to be taking upwards of 10 minutes!!! ... Execution Plan ... You can see if the migrated rows are fixed by ... truncating the CHAINED_ROWS table and executing the same analyze table ...
    (comp.databases.oracle.server)
  • Re: Invalidate cache
    ... I run my queries on a machine that is doing nothing except executing my ... Before every query I execute ... SET TRACE POINT DM421 ... The host machine is only executing VMware. ...
    (comp.databases.ingres)
  • Re: Invalidate cache
    ... I run my queries on a machine that is doing nothing except executing my ... Before every query I execute ... The host machine is only executing VMware. ...
    (comp.databases.ingres)