-
Error: ORA-1361: max # extents 1024 reached in table AA.BB
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
-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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
-
Originally Posted by BeefStu
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?
Originally Posted by BeefStu
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
Last edited by PAVB; 12-30-2010 at 09:52 AM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
Originally Posted by 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.
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
Last edited by PAVB; 12-30-2010 at 09:52 AM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|