tablespace growing constantly

[ksr]

Hello

I am faced with a wierd problem. The tablespace of my database is growing contantly and is nearing the limit. What needs to be done? How can I reduce it? Can it lead to Oracle crash. Any suggestions. I am new to databases.
Thanks
ksr

 

[SantaMufasa]

KSR,

There are a variety of causes and remedies to your tablespace growth:

Causes:
* INSERTion of new rows

* UPDATE-ing rows to values larger than their previous values. (Additionally, if there is not large enough PCTFREE of empty space in a block to accommodate an update, then Oracle relocates the entire row to another block with enough space. Oracle leaves in the old location a "forwarding address" of the new location of the row so that indexes do not need to be re-written. This is called "chained rows".)

* "Swiss Cheese" -- that is, holes (unused space/intra-block fragmentation) that Oracle leaves in a block due to a) deleted rows, b) chained rows or c) updates that result in less space consumption.

Remedies:
* "Swiss Cheese" removal -- you can get rid of intra-block fragmentation by either exporting, dropping, then importing tables; or the much faster and more efficient MOVE-ing of tables.

alter table <owner>.<table_name> move parallel nologging;

You cannot, however, move a table with a LONG column. For that, you must still export/drop/import.

* Add more file space. You can add one or more files to a tablespace whenever you please without bouncing the database:

ALTER TABLESPACE <ts_name> ADD DATAFILE '<fully qualified path and file name>' SIZE <n M>;

To avoid early "filling up" of files as you are experiencing, I add new files with the AUTOEXTEND feature:

ALTER TABLESPACE <ts_name> ADD DATAFILE '<fully qualified path and file name>' SIZE <n M>
AUTOEXTEND ON NEXT <n M> MAXSIZE <n M>;

In each of the formats, above the <n M> means "some number of Megabytes".

* Cause existing files to AUTOEXTEND. If your existing files are nearly full, you can give them extra life by putting them into AUTOEXTEND mode. I place a limit of 2 gigabytes on files since some operating systems still have problems with files > 2GB. Plus, backing up files is easier when they do not exceed 2GB. So, the syntax to cause an existing file to start AUTOEXTEND-ing is:

ALTER DATABASE DATAFILE '<fully qualified file name>'
AUTOEXTEND ON NEXT <n M> MAXSIZE <n M>;

So, in the above syntax example, I would code the "MAXSIZE 2000M", so that the file would not exceed 2 gigabytes.

If your existing files have grown to 2GB already, then I would simply add more datafiles (perhaps 10MB initial size) that autoextend to 2GB.

If you have additional questions about the causes and remedies of your file growth, please reply post.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:34 (18Jul04) UTC (aka "GMT" and "Zulu"), 10:34 (18Jul04) Mountain Time)

 

[sem]

Isn't that temporary tablespace?

Regards, Dima

http://seminihin.narod.ru