Check if a table has reached it maximum size?

[thewing]

Hi,
I'm a junior dba and was wondering how do you check if a table has reached it's maximum size??We have a database on an NT server and the following error is coming up on the system event log - "The server could not expand a table because the table reached the maximum size" withe an SQL error type 2009.Is there a view or a query that I can use that will tell me what tables are reaching their maxextents level??

Thanks
TheWing

[sreddy]

DBA_SEGMENTS

[stecal]

SQL error type? You mean ORA error type? 2009? That is related to a file size specification error.

ORA-02009 the size specified for a file must not be zero

Cause: A value of zero was specified in a SIZE or RESIZE clause of a file specification.

Action: Use correct syntax, or, if allowed, omit the SIZE or RESIZE clause


What is maximum size for a table? Do you mean you're out of space, max number of extents has been reached, or what? Tables don't reach a maximum size - they grow in accordance with the storage parameters you set.

[thewing]

yeah, how do i check if maxextents has been reached?

[stecal]

Try dba_tables

[thewing]

And what columns in dba_tables will tell besides maxextents?Which column tells me the number of extents used??

[stecal]

Do you know how to use the describe command? Do you have any DBA books?

[thewing]

I know that much, so which of these when compared with each other will let me see which tables are reaching their maxextents level??

SQL> desc dba_tables;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)

SQL>

[aarroyob]

I think it's what you're looking for

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

PROMPT
PROMPT Tables nearing MAX_EXTENTS
PROMPT **************************
SELECT t.table_name,
e.extents,
t.max_extents,
Trunc(t.initial_extent/1024) "INITIAL K",
Trunc(t.next_extent/1024) "NEXT K"
FROM all_tables t,
(SELECT segment_name,
owner,
count(*) extents
FROM dba_extents
WHERE segment_type = 'TABLE'
GROUP BY segment_name, owner) e
WHERE e.segment_name = t.table_name
AND e.owner = t.owner
AND e.extents > (t.max_extents - 5)
ORDER BY e.extents DESC
/

PROMPT Indexes nearing MAX_EXTENTS
PROMPT ***************************
SELECT i.index_name,
e.extents,
i.max_extents,
Trunc(i.initial_extent/1024) "INITIAL K",
Trunc(i.next_extent/1024) "NEXT K"
FROM all_indexes i,
(SELECT segment_name,
owner,
count(*) extents
FROM dba_extents
WHERE segment_type = 'INDEX'
GROUP BY segment_name, owner) e
WHERE e.segment_name = i.index_name
AND e.owner = i.owner
AND e.extents > (i.max_extents - 5)
ORDER BY e.extents DESC
/

[thewing]

aarroyob - my hero!!!Way too hungover hence using this board for answer.

Cheers
TheWing