Re: Tablespace is running out of space (Oracle8i Enterprise Edition Release 8.1.7.0.0)

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Tue, 20 May 2003 09:49:05 +0200
Message-ID: <bacmol$rf1ec$1@ID-152732.news.dfncis.de>

Hi Henning,

Hi.

Check out the following data dictionary views:

dba_segments / user_segments

 OWNER                                    VARCHAR2(30)   --not in
user_segments
 SEGMENT_NAME                             VARCHAR2(81)
 PARTITION_NAME                           VARCHAR2(30)
 SEGMENT_TYPE                             VARCHAR2(18)
 TABLESPACE_NAME                          VARCHAR2(30)   --define your
critical ts name
 HEADER_FILE                              NUMBER
 HEADER_BLOCK                             NUMBER
 BYTES                                    NUMBER
 BLOCKS                                   NUMBER
 EXTENTS                                  NUMBER         --examine for
extraordinary growth
 INITIAL_EXTENT                           NUMBER
 NEXT_EXTENT                              NUMBER
 MIN_EXTENTS                              NUMBER
 MAX_EXTENTS                              NUMBER
 PCT_INCREASE                             NUMBER
 FREELISTS                                NUMBER
 FREELIST_GROUPS                          NUMBER
 RELATIVE_FNO                             NUMBER
 BUFFER_POOL                              VARCHAR2(7)

Maybe you could make a spool file or an auditing table to fill everyday, and examine the number of extents
of the critically growing schema objects, over a period of time. If you have many added extents
every day, then you have found the object for further investigations:

>>>

dba_tables, dba_indexes, and so on.

And you may go further into dba_dependencies, which stored procedure is accessing the table, for instance:

SELECT
   name,
   type,
   referenced_name,
   referenced_type,
FROM dba_dependencies

WHERE OWNER = 'IchBins'
AND type IN ('FUNCTION','PROCEDURE','PACKAGE BODY','TRIGGER')
AND referenced_type = 'TABLE'

ORDER BY type, name;

HTH, Jan

"kielhd" <kielhd_at_freenet.de> schrieb im Newsbeitrag news:10a4173d.0305182348.241523a9_at_posting.google.com...
> Hi,
> I am looking into the following problem and hope to get some help
> here.
>
> One of the tablespaces I am using was nearly out of space. I enlarged
> it to keep the database running.
>
> Of course, there is a lot of traffic on the database and lots of
> sentences are inserted/updated every day into lots of tables. I know
> that the tablespace has to run out of space because of this someday! I
> just like to know the main reason for this.
>
> Is Oracle collecting data that can tell me which tables are increasing
> in size fast?
> Are there tools from Oracle available to monitor this?
>
> Thanks in advance
> Henning
Received on Tue May 20 2003 - 02:49:05 CDT