Re: Storage (tablespace) questions

"Mike DiChiappari" <mike_at_mysticsoft.com> wrote in message news:8r4t83$r3l_at_dispatch.concentric.net...
> I recently tried INSERTING some information to a database. I got the
> ORA-01653 error message saying that my table couldn't be extended. I
 tried
> several things, none of which worked (but I thought they should have
> worked). I'd appreciate any comments.
>

Diagnosis: your TABLESPACE has run out of room. Required cure: make more room in the tablespace.

> First, I tried increasing the extents for the table so it could grow
 passed
> it max amount. That failed to improve the situation.
>

Suggested cure: fiddle around with the TABLE settings. I suspect you mean you changed MAXEXTENTS for the table?

First off, since the problem is that the *tablespace* has no room, nothing you do to the table is going to have any effect. Second off, MAXEXTENTS sets what the table *could* grow to, if only it had room to do so. Your problem is the lack of room to do so, not an artificially low MAXEXTENTS. So this isn't going to work.

> Second, I tried increasing the extents allocated for the tablespace. That
> failed to improve the situation.
>

Suggested cure (I think): you've changed MAXEXTENTS as specified in the default storage clause at tablespace level.

That clause (hence the name) is only the default that *would* apply to a table if the table was initially created without an operative storage clause of its own. So changing it doesn't do a thing to the tablespace itself, it just changes what future tables might pick up as their storage clause. What's more, when you create a table, and it uses the tablespace default storage clause, that clause becomes the table's OWN storage clause. So even if you then change it at the tablespace level, the table has already picked up what the settings used to be -and hence, they are not affected at all by what you change for the tablespace.

In any case, since the diagnosis is that you've run out of room in the tablespace, fiddling around with maximum permissible numbers of extents at either table or tablespace level isn't going to help a bit. You need to add more room to the tablespace.

> Third, I tried increase the size of the datafile (only one, so far). That
> faile to improve the situation.
>

Good suggested cure... probably lousy execution. Difficult to know without seeing what and how you tried to do this. The command should have been 'alter database datafile X resize 1000M' (or some appropriate size). And it should have worked, and it would have fixed up your problem.

> Finally, I added a second data file to the tablespace and that worked.
>

Good cure. I prefer this to number 3, because I think it better to have a number of smaller files inside a tablespace than a single, stonkingly large file, even though the useable space would actually be much the same whichever method you used.

I suppose I should mention the other known way of resizing datafiles: autoextend. Try 'alter database datafile X autoextend on next Xm maxsize 1000m'. It means Oracle will dynamically resize the datafile in steps of 10M, until it reaches 1Gb in size. Convenient... at the cost of performance, of course. But it may be suitable for you.

Regards
HJR
> Why didn't any of the first three work?
>
> Thanks,
> Mike
>
>
Received on Sat Sep 30 2000 - 17:27:42 CDT