ORA-01631 max extents reached in table

ORA-01631 max extents reached in table

Error:
ORA-01631 max # extents reached in table

Cause:
Table reached the limit of MAX Extend defined for table.

Solution:
Check the Max Extents in the table defined

col table_name for a20
select table_name,(max_extents)/1024/1024/1024 "MAXEXTENT GB" from user_tables where table_name='TEST';

TABLE_NAME           MAXEXTENT GB
-------------------- ------------
TEST                            2

Increase the value of Max Extents

-- specifiy integer as bytes size
alter table TEST.TEST storage (maxextents 300);

-- For unlimited
alter table TEST storage (maxextents unlimited);

Note: You can also check MAX Extents from GET_DDL packaage

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('TABLE','TEST','TEST') from dual;

CREATE TABLE "TEST"."TEST"
( "ID" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 10 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.