Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Luis.

Asked: May 16, 2001 - 11:46 am UTC

Last updated: December 06, 2004 - 12:58 pm UTC

Version: oracle 8i

Viewed 1000+ times

You Asked

Hi, i need to know how to assign more space to a table that is full now. Im not really a DBA but the database is under my responsability now. The OS is Windows NT server 4.0 and the database is oracle 8i.
When i tried to make an insert i got the following message:

SQLException: ORA-01653: unable to extend table CLORAS.COMPRAS by 92169 in tablespace DATA

and Tom said...

You need to add space to a file in that tablespace.

Here is what you do. first query:

tkyte@DEV2.THINK.COM> select file_name, trunc(bytes/1024/1024)
2 from dba_data_files
3 where tablespace_name = 'DATA';

FILE_NAME TRUNC(BYTES/1024/1024)
------------------------------ ----------------------
/d02/oradata/DEV2/DEV2_misc01.dbf 68


there may be more then one file listed there, thats OK, just pick one and then:


tkyte@DEV2.THINK.COM> alter database
2 datafile '/d02/oradata/DEV2/DEV2_misc01.dbf'
3 resize 70m;

Database altered.


resize it bigger, now you should see:

tkyte@DEV2.THINK.COM> select file_name, trunc(bytes/1024/1024)
2 from dba_data_files
3 where tablespace_name = 'DATA';

FILE_NAME TRUNC(BYTES/1024/1024)
------------------------------ ----------------------
/d02/oradata/DEV2/DEV2_misc01.dbf 70



Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Resizing a Tablespace

Robert, May 16, 2001 - 3:32 pm UTC

Great!
I used to just add datafiles.

sorry

Luis Eduardo, May 16, 2001 - 3:41 pm UTC

I made a mistake when following your instructions,but i saw it, and after, voilà. Im not good with the english, your answer was really useful, thanks you very much

Helena Markova, May 17, 2001 - 5:19 am UTC


Resizing a Tablespace

MELODY, December 14, 2001 - 2:25 am UTC

THANK YOU FOR ANSWERING THIS QUESTION SO WELL.

list of precausions

A reader, December 06, 2004 - 11:09 am UTC

hi tom ,

we just got the ora-1653, and our dba added the datafiles and it worked.

but, DBAs did this after we encountered the problem in prod.
they have just added more space (a new datafile).

I want to know what are the other thing that I should make sure to check(redo logs ?, fragmentations ?, HWM ?) . cause I am sure if one can forget to check this basic things, they can forget another. I think they are overworked. basically I want to provide them with the list, that will they should check/monitor and for my understanding so that Everymorning I can see the alert in the email .

can you please help me.

Thanks in Advance,


Tom Kyte
December 06, 2004 - 12:39 pm UTC

tell them to turn on OEM or any other monitoring tool out there.

can you please consider answering the above qestion ?

A reader, December 06, 2004 - 12:25 pm UTC

can you please consider answering the above qestion ? and help me ceate list of thing that I should put in the databose monitoring check list ? regarding ora-1653

Tom Kyte
December 06, 2004 - 12:43 pm UTC

geez, give me like 5 minutes sometime.

I did -- use a tool. there are billions of them out there.

sorry tom

A reader, December 06, 2004 - 12:51 pm UTC

i saw some other question updated twise, so I thought, you skiped this ..

They have OEM, eventhough they forgot ? so please tell me what are things, you think, that I tell them to send me email about .

meanwhile I browse throught your site and found.

Monitor tablespace/datafile size.
Monitor data file freespace
Monitor rollback segment.
Monitor redo log files and its mirror.
Sessions using max cpu time
Sessions using max memory.
Do we have enough SYSTEM table space available ?
how many oracle instances are running on that box ?
Do we have enough space to accommodate the TX in the rollback segment ? how big is it ?
Can we see the copy of init.ora of the production ?
Full tablescans
Waits for query or pl/sql block
Locks per session
CPU usage on the database box.
What is our SGA size ?
What is PGA size ?
What is our datafile block size ?
Are we using a uniform extent size ? or auto allocation ? for space extension ?
What kind of tablespace Management do we have ? (LMT or DMT )

any thing else ? please guide ..

Thanks

Tom Kyte
December 06, 2004 - 12:53 pm UTC

this is book sized in its scope and far beyond the initial questions scope.


You could just hire someone with experience -- but the tool generates alerts telling them when limits are getting hit, they only need turn it on and look at it. the tool does all of these things.

Thanks,

A reader, December 06, 2004 - 12:58 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.