ORA01653 1

[new2ora]

Hi,

I had posted a question before and I got a quick and accurate response. Thanks guys/gals.

When the users save the screen in the front-end application, an error 1653 appears Unable to extend table ts.table by 669 in Tablespace ts. Using Enterprise manager(storage manager) I checked the datafiles and the datafile 1 of 4 is the only one with autoextend enabled. The other 3 don't have that. Even datafile1 is at 590M and the max allowed is 614.

Question1: Can I enable auto-extend without fear of losing anything.
Question2: Can I change the maximum extent?
Question3: When is the ts extended?
Question4: Is the actual file size increased on the disk?

Thanks for any help.

 

[carp]

1. Yes. The only thing that you will lose is whatever free space you have on your drive that gets allocated to the tablespace.
2. I think you can change the default maxextents for the tablespace. I don't think you can change the maxextents for a table after it's created (you can't on 9.2.0.4.0, which is the only database I have to test this on).
3. When a new extent is required and there is insufficient contiguous free space to create it.
4. Yes.

Elbert, CO
1043 MST.

 

[SantaMufasa]

New2Ora,

Just to add a bit to Carp's always excellent responses:

Item 1) In case you need the syntax to turn AUTOEXTEND ON, it is:
ALTER DATABASE DATAFILE '<filename>'
AUTOEXTEND ON NEXT <increment size> MAXSIZE <maxsize>;

Item 2) The answer here is &quot;It depends&quot;. The reason why Carp could not change maxextents on his Oracle 9i database is because Oracle 9i defaults to &quot;EXTENT MANAGEMENT LOCAL&quot; (available in 8i, but not the default). If you are using Local Extent Management (LEM) for a tablespace, then extent management is not under your control and you can not modify MAXEXTENTS either for a new table, and existing table, or as a default for the tablespace. If you are not using LEM, then, yes, you are in control of MAXEXTENTS for all: new tables, existing tables, and default for the tablespace.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:21 (09Feb04) UTC (aka &quot;GMT&quot; and &quot;Zulu&quot, 11:21 (09Feb04) Mountain Time)

 

[carp]

Dave -
Thanks for filling in my gaps! I THOUGHT we used to be able to change maxextents, but was some surprised when it blew up on me! I figured it was yet another symptom of senility creeping in on me!

A star is in your future!

Elbert, CO
1221 MST

 

[new2ora]

I thought that the tables would always autoextend till tablespace max reached. So now you are telling me that I can extend individual tables as well??
1)How do I find out the size/extent of a TABLE(ts.table)?
2)How do I extend it? or extending the size for the table space should take care of it?
3)Can I find out which table is on which datafile?

Thanks. You guys are awesome!

 

[SantaMufasa]

New2Ora,

Q. &quot;So now you are telling me that I can extend individual tables as well??&quot;
A. Tables will &quot;autoextend&quot; until:
1) It reaches its own MAXEXTENTS value (for non-LEM tablespaces) or
2) There is no &quot;free extent&quot; for the tablespace large enough to accommodate a table's request to extend to its NEXT EXTENT size.
3) An autoextending tablespace either reaches its MAXSIZE or the file system providing storage spaces reaches its capacity.
4) The schema that &quot;owns&quot; the table does not exceed its QUOTA on the tablespace. (A DBA can increase a user's QUOTA on a tablespace all the way to UNLIMITED.)

Q. &quot;How do I find out the size/extent of a TABLE(ts.table)?&quot;
A. select segment_name a, segment_type b, bytes c
from user_segments
order by bytes

You can also look at individual extent sizes for a table (or any other type of segment) by querying USER_EXTENTS.

Q. &quot;How do I extend it (a table)? or extending the size for the table space should take care of it?&quot;
A. Provided you do not abrogate one of the above limitations on size.

Q. &quot;Can I find out which table is on which datafile?&quot;
A. Yes, if you &quot;desc dba_extents&quot;, you will see &quot;FILE_ID&quot; which you can join to dba_data_files.FILE_ID to see the actual data-file name holding the extent.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:07 (09Feb04) UTC (aka &quot;GMT&quot; and &quot;Zulu&quot, 13:07 (09Feb04) Mountain Time)

 

[new2ora]

I am going to enable auto-extend on the datafile2,3and4 early tomorrow morning, after a succesful backup tonite. I'll also increase the Max extent on the datafile 1. Lets hope it takes care of the error.

I did this; (if you &quot;desc dba_extents&quot;, you will see &quot;FILE_ID&quot; which you can join to dba_data_files.FILE_ID to see the actual data-file name holding the extent.) and the table lists all 3 of the 4 datafiles?? Does that mean its distributed?

I'll update you folks. Thanks.

 

[SantaMufasa]

New2Ora,

If you have multiple files providing support to a single tablespace, then it is very reasonable to have extents for a single tables distributed over the multiple datafiles.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:46 (10Feb04) UTC (aka &quot;GMT&quot; and &quot;Zulu&quot, 17:46 (09Feb04) Mountain Time)

 

[new2ora]

Ok. Everything worked out well. The error is now gone. I learned a lot of things in the process.

I have another question which I will post in a separate thread.

Thank you guys.