Oracle Tips and Tricks — David Fitzjarrell

January 22, 2014

Space Exploration

Filed under: General — dfitzjarrell @ 12:58

Managing free space in a tablespace seems to be an easy task, what with the views DBA_DATA_FILES, DBA_TEMP_FILES and DBA_FREE_SPACE avaliable. Those views can provide accurate information for datafiles not set to autoextend, however for autoextensible datafiles they can paint a bleaker picture because they’re based on the current size of the file, not the autoextend limit that file could reach. Let’s look at how those views, without taking autoextend into consideration, can report a ‘problem’ that doesn’t actually exist.

For datafiles not set to autoextend the following queries return basically the same information:


SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       4383047680   38797312        .89
USERS                           104857600  103809024         99
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
USERS                                 32,767.98                100.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
INDX                                  32,767.98                 50.00          0.00    0.00
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

It’s when autoextend is enabled that the first query can report a ‘problem’ that doesn’t really exist. Let’s create a table and populate it with data to see how the USERS tablespace behaves:


SQL>
SQL>
SQL> create table spacetst(
  2          spaceid number,
  3          spacenm varchar2(20),
  4          spacedt date);

Table created.

SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       3287285760   30408704        .93
USERS                           104857600   68157440         65
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                100.00         34.00    0.10
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

So far, so good as both queries indicate we have plenty of available space. Let’s add more data and see where that leads:


SQL>
SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       3287285760   13697024        .42
USERS                           104857600   28311552         27
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                100.00         72.00    0.22
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>
SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>

Again the two queries don’t show any issues with the USERS tablespace. Let’s keep adding rows to the table to attempt to fill up the USERS tablespace and see what happens:


SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       1095761920    1048576         .1
USERS                           116654080    6553600       5.62
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                111.25        104.00    0.32
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>
SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       1095761920    2097152        .19
USERS                           144179200     524288        .36
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                137.50        136.00    0.42
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>

Now we’re getting to the point where the first query shows a space problem, a space problem that will resolve itself when the datafile autoextends. This is misleading as there is plenty of space which can be made available in the USERS tablespace, we just have to wait for Oracle to extend the datafile. This is where the second query provides a much clearer picture of the total available and potential space. Another insert to cause Oracle to extend the datafile doesn’t affect the output of the second query but alters the output of the first:


SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       1.6436E+10   63963136        .39
USERS                           186122240    8912896       4.79
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                177.50        168.00    0.51
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>

And another insert creates the same ‘picture’ from the first query; the second query keeps up with the allocations and reports the available space more accurately:


SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
USERS                           221511680    2359296       1.07
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

5 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                211.25        208.00    0.63
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>

On it goes, insert after insert, with the first query reporting only free space available from the current allocation and ignoring the fact that the datafile has a number of extensions left to go:


SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       1095761920    8388608        .77
USERS                           256901120    4194304       1.63
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                245.00        240.00    0.73
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>
SQL>

When autoextend is in use it’s not the best idea to rely solely upon the sum of the BYTES column in the DBA_FREE_SPACE view as it’s defined without autoextend in mind. You can fix that by using the second, more robust, query to report space usage and allocations for tablespaces, and the query automatically takes into consideration the autoextend size limit. Using the second query may significantly reduce your stress levels when a file is nowhere near it’s autoextend size limit as you won’t be scrambling to add space only to find that Oracle will do that for you automatically.

And I’ll just bet you thought we were going to Mars.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 724 other followers

%d bloggers like this: