Error: ORA-1361: max # extents 1024 reached in table AA.BB

[jitender singh]

Hi friends,
Please anyone help me in solving the problem.
i checked in the alert logs in Oracle Database and noticed an Error.
The error is :
ORA-1361: max # extents 1024 reached in table AA.BB
please suggest how to solve the problem and Also tell me the reason why this error is coming? what will be the effect of this error on the database if i will not rectify this error in the database?

I am new to the Oracle. So, please suggest me with deatils.

thanks
regards

Jitender Singh

[vnktummala]

This error is coming because the table reached max extents. Increase the max extents to unlimited or any higher number which is higher than the current value using the below command.

ALTER TABLE AA.BB STORAGE(MAXEXTENTS UNLIMITED);

Thanks,

[BeefStu]

If you dont want to have unlimited extents I see two more options available
to you:

1) You can export the data, drop the table and re-create with larger initial and next storage parameters, this will lower the number of extents.


2) You can try de-allocating the unsed space beyond the High Water Mark (alter table table_name deallocate unused keep 0, as shown on the next demo:


SQL> edit
Wrote file afiedt.buf

1 create table emp1
2 storage( initial 8m next 8m )
3* as select * from employees
SQL> /

Table created.

SQL> analyze table emp1 compute statistics;

Table analyzed.


SQL>
1 select blocks, segment_name from user_segments
2* where segment_name ='EMP1'

BLOCKS SEGMENT_NAME
---------- ----------------
1024 EMP1


SQL> alter table emp1 deallocate unused keep 0;

Table altered.

SQL> select blocks, segment_name from user_segments
2 where segment_name ='EMP1';

BLOCKS SEGMENT_NAME
---------- ----------------
8 EMP1

SQL> select table_name, initial_extent from user_tables;

TABLE_NAME INITIAL_EXTENT
------------------------------ --------------
EMP1 65536
EMPLOYEES 65536

[PAVB]

1) You can export the data, drop the table and re-create with larger initial and next storage parameters, this will lower the number of extents.

Oh yes... this is provided you are manually handling extent sizes and your boss is concerned about running out of extents, huh?

2) You can try de-allocating the unsed space beyond the High Water Mark

If you already got ORA-1631 it means there is nothing beyond HWM

[onedbguru]

It will be nice when everyone starts using ASM and ASSM. Then these silly questions will finally just go away. Use Locally Managed Tablespaces, ASM and ASSM with storage devices (preferably a SAN implementation) commensurate with the application and you will get a lot more sleep.

[PAVB]

It will be nice when everyone starts using ASM and ASSM. Then these silly questions will finally just go away. Use Locally Managed Tablespaces, ASM and ASSM with storage devices (preferably a SAN implementation) commensurate with the application and you will get a lot more sleep.

Well... in that line of thinking do you think the very same day databases gain conciousness all questions will finally go away? Don't think so, some people will still ask questions like "what it means don't touch anything? what's anything?"

I have a list of things I never underestimate, in between them: human lazyness

[onedbguru]

It was once said that the mother of invention was necessity. I would say that the mother of invention is actually laziness - or is it that innate desire in all of us to sleep at night.

When you deal with VLDB's (hundreds of TB) you will want to use all of the tools you can to keep the thing running. The side-effect is that you get to sleep at night...

Can you detect a common theme here

And if Larry has his way, the db becoming self-aware may not be that far off lol