Tip

Who is low on space?

You've received a warning that a table in your database cannot add another extent and that you need to add space or modify the object's parameters. But what table? What tablespace? Does that tablespace autoextend? How big is it, anyway?

This script summarizes the tablespaces in a database instance, highlights the one(s) that cannot throw another extent, and details all datafiles in the database. Finally, it creates a script that you can use in an emergency to adjust the next extent parameters of all of the objects that are in danger.

The reports in this script are wider than 80 columns. The script works at 8i and above; it won't work on 7.x without modification.

Here is the sample output:

SQL> @space_critical 

Generating Tablespace Summary Listing... 

                           Max Next_Extent        Max Chunk CHECK  Total Space Free Space 
Tablespace                         (BYTES)          (BYTES) THIS          (MB)       (MB)  % Used 
------------------------- ---------------- ---------------- ------ ----------- ---------- ------- 
CAT_DATA                         5,242,880       18,489,344              300.0       20.7   93.11 
DRSYS                               90,112        2,539,520                5.0        2.4   51.56 
INDX                            52,428,800       48,340,992 <=====       250.0       46.2   81.51 
NONCAT_DATA                         65,536        1,310,720               51.8        1.3   97.59 
RBS                              1,048,576        1,507,328               52.1       30.9   40.72 
SYSTEM                             761,856       15,867,904              181.1       15.8   91.29 
TOOLS                            8,388,608       28,385,280               75.0       27.3   63.67 

Generating Datafile Details Listing... 

                                                                           Size     Increment Max Size 
TS Name        File Name                                                   (MB) AUT      (MB)     (MB) 
-------------- ----------------------------------------------------- ---------- --- --------- -------- 
CAT_DATA       /ofa/u02/oradata/ECCP/CAT_DATA_ECCP.dbf                    300.0 YES        50    1,024 
DRSYS          /ofa/u02/oradata/ECCP/Ctx1_ECCP.dbf                          5.0 YES        10      500 
INDX           /ofa/u02/oradata/ECCP/INDX_ECCP.dbf                        250.0 YES        50    1,024 
NONCAT_DATA    /ofa/u02/oradata/ECCP/NonCat1_ECCP.dbf                      51.8 YES        25      500 
RBS            /ofa/u02/oradata/ECCP/Rbs1_ECCP.dbf                         52.1 YES        20      500 
SYSTEM         /ofa/u02/oradata/ECCP/Sys1_ECCP.dbf                        181.1 YES        25      250 
TEMP           /ofa/u02/oradata/ECCP/Tmp1_ECCP.dbf                         30.0 YES        25      500 
TOOLS          /ofa/u02/oradata/ECCP/Tools01_ECCP.dbf                      75.0 YES        25      250 

Generating Troubled Objects Listing... 

                                                           Segment                 Next  Pct      Largest 
OWNER        TS Name        Segment Name                   Type             Extent (MB) Incr Segment (MB) 
------------ -------------- ------------------------------ --------------- ------------ ---- ------------ 
EMERGE35     INDX           DBA_LOG_CREATE_DATE_IDX        INDEX                   50.0    0         46.1 


creating /tmp/update_storage.sql 

SQL>

    Requires Free Membership to View

    Login 

 
SQL> !cat /tmp/update_storage.sql 
prompt 
prompt Updating objects to clear space problems 
prompt 

set echo on feedback on termout on pause off heading on pagesize 24 

alter INDEX EMERGE35.DBA_LOG_CREATE_DATE_IDX storage ( next 40M pctincrease 0); 

SQL>

Here is the code:

set echo off feedback off heading on 
set linesize 132 feed 200 pagesize 75 trimspool on pause off tab off 

column tsname          format a25             heading 'Tablespace' 
column max_next_extent format 999,999,999,999 heading "Max Next_Extent|(BYTES)" 
column max_chunk       format 999,999,999,999 heading "Max Chunk|(BYTES)" 
column chk_me          format a6              heading "CHECK|THIS" 
column tsmb            format 999,999.9       heading "Total Space|(MB)" 
column fsmb            format 999,999.9       heading "Free Space|(MB)" 
column pctused         format 999.99          heading "% Used" 

prompt 
prompt 
prompt 
prompt Generating Tablespace Summary Listing... 
select tsname, 
       max_ne max_next_extent, 
       max_chunk max_chunk, 
       chk_me, 
       tsbytes/1048576 tsmb, 
       fsbytes/1048576 fsmb, 
       ( ( (tsbytes - fsbytes) / tsbytes) * 100) pctused 
from   (select   df1.tablespace_name tsname, 
                 sum(df1.bytes) tsbytes 
        from     sys.dba_data_files df1 
        group by df1.tablespace_name), 
       (select   fs1.tablespace_name fsname, 
                 sum(fs1.bytes) fsbytes 
        from     sys.dba_free_space fs1 
        group by fs1.tablespace_name), 
       (select   a.tablespace_name cname, 
                 max(a.next_extent) max_ne, 
                 max(b.bytes) max_chunk, 
                 decode(sign((max(b.bytes) - max(a.next_extent))),-1,'<=====', NULL) CHK_ME 
        from     sys.dba_segments   a, 
                 sys.dba_free_space b 
        where    a.tablespace_name = b.tablespace_name 
        group by a.tablespace_name) 
where  tsname = fsname 
and    tsname = cname 
order by tsname 
/ 

column file_name       format a53       wrap heading "File Name" 
column tablespace_name format a14            heading "TS Name" 
column MB              format 999,999.9      heading "Size|(MB)" 
column increment_by    format 999,999        heading "Increment|(BLOCKS)" 
column incr_mb         format 999,999        heading "Increment|(MB)" 
column MAXMB           format 999,999        heading "Max Size|(MB)" 

prompt Generating Datafile Details Listing... 
select df.tablespace_name, df.file_name, df.bytes/1048576 MB , df.autoextensible, 
       decode(df.autoextensible, 'YES', ((df.increment_by*to_number(vp.value))/1048576), 
                                 NULL) incr_mb, 
       decode(df.autoextensible, 'YES', df.maxbytes/1048576, 
                                 NULL) MAXMB 
from   sys.dba_data_files df, 
       v$parameter        vp 
where  vp.name = 'db_block_size' 
order by df.tablespace_name, df.file_id 
/ 



col segment_name format a30           heading "Segment Name" 
col maxseg       format 9,999,999.9   heading "Largest|Segment (MB)" 
col segment_type format a15           heading "Segment|Type" 
col pct_increase format 999           heading "Pct|Incr" 
col next_ext     format 9,999,999.9   heading "Next|Extent (MB)" 
col owner        format a12 

prompt Generating Troubled Objects Listing... 
select   a.owner, a.tablespace_name, 
         a.segment_name, a.segment_type, 
         a.next_extent/1048576 next_ext, a.pct_increase, 
         max(b.bytes)/1048576 MAXSEG 
from     sys.dba_segments   a, 
         sys.dba_free_space b, 
         (select max(fs.bytes) maxbytes, 
                 fs.tablespace_name tsname 
          from   sys.dba_free_space fs 
          group by fs.tablespace_name) 
where    a.tablespace_name = b.tablespace_name 
and      a.tablespace_name = tsname 
and      a.next_extent > maxbytes 
group by a.owner, a.tablespace_name, a.segment_name, 
         a.segment_type, a.next_extent, a.pct_increase 
order by a.next_extent desc, owner, segment_name 
/ 

set linesize 130 trimspool on tab off echo off feedback off 
set heading off pagesize 0 
col sql_text format a100 

prompt 
prompt creating /tmp/update_storage.sql 
prompt 

set termout off 
spool /tmp/update_storage.sql 
prompt prompt 
prompt prompt Updating objects to clear space problems 
prompt prompt 
prompt 
prompt set echo on feedback on termout on pause off heading on pagesize 24 
prompt 

select   'alter '||a.segment_type||' '||a.owner||'.'||a.segment_name|| 
         ' storage ( next '|| 
           decode(trunc(((max(b.bytes)/1048576)-5),-1), 
                  0,1, 
                  trunc(((max(b.bytes)/1048576)-5),-1)) 
           ||'M pctincrease 0);'||chr(10) 
from     sys.dba_segments   a, 
         sys.dba_free_space b, 
         (select max(fs.bytes) maxbytes, 
                 fs.tablespace_name tsname 
          from   sys.dba_free_space fs 
          group by fs.tablespace_name) 
where    a.tablespace_name = b.tablespace_name 
and      a.tablespace_name = tsname 
and      a.segment_type    <> 'ROLLBACK' 
and      a.next_extent     > maxbytes 
group by a.segment_type, a.owner, a.segment_name, a.next_extent 
order by a.next_extent desc 
/ 
prompt 
prompt 
spool off 

set termout on heading on pagesize 24 linesize 100 
set feedback on 
prompt

For More Information

  • What do you think about this tip? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in October 2001

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top