Extents - Full or Max?

From: Joel Garry <joelga_at_rossinc.com>
Date: Mon, 9 Jan 1995 22:12:32 GMT
Message-ID: <1995Jan9.221232.3151_at_rossinc.com>

I have a situation where I am unable to add any more to a tablespace. ORA-01547: failed to allocate extent of size 125 in tablespace XXX (not it's real name).

OK, fine, either I'm actually out of space or I've run into the extent limit. This was a little used test database, until the test started to be add and delete a bunch of schemas/users. 100M datafile on oracle7 7.0.16.4 on hp-ux 9.0 created with

CREATE TABLESPACE XXX
DATAFILE '$ORACLE_BASE/data/xxx/xxx.dbf' SIZE 100M DEFAULT STORAGE ( INITIAL 250K NEXT 250K PCTINCREASE 0 ) ONLINE; So first I looked at

>SELECT tablespace_name, file_id,

COUNT(*) "PIECES",

MAX(blocks) "MAXIMUM",
MIN(blocks) "MINIMUM",
AVG(blocks) "AVERAGE",
SUM(blocks) "TOTAL"

FROM sys.dba_free_space
GROUP BY tablespace_name, file_id
TABLESPACE_NAME FILE_ID PIECES MAXIMUM MINIMUM AVERAGE TOTAL

------------------ ------- ---------- ---------- ---------- ---------- ---------
XXX                      3          7       9875         74 3135.57143     21949
RBS                      2          1       1462       1462       1462      1462
SYSTEM                   1          1       9591       9591       9591      9591
TEMP                     6          1        274        274        274       274
TOOLS                    4          1       7434       7434       7434      7434
USERS                    5          1        511        511        511       511

(and one more I've deleted)

OK, a little fragged, but there is still contiguous room. Next, I tried the program out of the Adminstrators Guide, that is supposed to tell me which reason an extent can't be allocated. It has now been running 8 hours with no results. How long should this take? I'm the only one using this computer. I'm running as SYS. I'm pretty sure I'm out of extents, but I need to be able to help others when they run into this circumstance determine the reason. I don't think any clusters are defined - would that confuse this sql?

REM From Oracle Server Administrators guide, p. 8-85 SELECT seg.owner, seg.segment_name,

	seg.segment_type, seg.tablespace_name,
	DECODE(seg.segment_type,
		'TABLE', t.next_extent,
		'CLUSTER', c.next_extent,
		'INDEX', i.next_extent,
		'ROLLBACK', r.next_extent)
FROM sys.dba_segments seg,
	sys.dba_tables t,
	sys.dba_clusters c,
	sys.dba_indexes i,
	sys.dba_rollback_segs r
WHERE ((seg.segment_type = 'TABLE'
	AND seg.segment_name = t.table_name
	AND seg.owner = t.owner
	AND NOT EXISTS (SELECT tablespace_name
			FROM dba_free_space free
			WHERE free.tablespace_name = t.tablespace_name
			AND free.bytes >= t.next_extent))
OR	(seg.segment_type = 'CLUSTER'
	AND seg.segment_name = c.cluster_name
	AND seg.owner = c.owner
	AND NOT EXISTS (SELECT tablespace_name
			FROM dba_free_space free
			WHERE free.tablespace_name = c.tablespace_name
			AND free.bytes >= c.next_extent))
OR 	(seg.segment_type = 'INDEX'
	AND seg.segment_name = i.index_name
	AND seg.owner = i.owner
	AND NOT EXISTS (SELECT tablespace_name
			FROM dba_free_space free
			WHERE free.tablespace_name = i.tablespace_name
			AND free.bytes >= i.next_extent))
OR 	(seg.segment_type = 'ROLLBACK'
	AND seg.segment_name = r.segment_name
	AND seg.owner = r.owner
	AND NOT EXISTS (SELECT tablespace_name
			FROM dba_free_space free
			WHERE free.tablespace_name = r.tablespace_name
			AND free.bytes >= r.next_extent)))

OR seg.extents = seg.max_extents
OR seg.extents = 2048;

Comments/suggestions appreciated.

-- 
Joel Garry           joelga_at_amber.rossinc.com            Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.  
panic: ifree: freeing free inodes...

Received on Mon Jan 09 1995 - 23:12:32 CET