Home » RDBMS Server » Server Administration » Reuse of Space in 9.2.0.1.0
Reuse of Space in 9.2.0.1.0 [message #281224] Fri, 16 November 2007 03:20 Go to next message
DaHuhn
Messages: 8
Registered: November 2007
Junior Member
Hello All,

short question:
I am running an Oracle 9i Enterprise 9.2.0.1.0 since 3 Years now.
There are some Tables with more than a billion entries. Actually we are running out of disk space (so far auto-extend worked for us)

From the "big" tables we deleted a lot of content.Row by Row.
Now we are wondering, if the space created by the delete will be useg again by the database automatically?
I mean, the data files will stay at the same size as before. But will Oracle use the deleted rows/space again now?

Kind regards
Tobias
Re: Reuse of Space in 9.2.0.1.0 [message #281234 is a reply to message #281224] Fri, 16 November 2007 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Now we are wondering, if the space created by the delete will be useg again by the database automatically?

Yes, for new rows inside the same table.
Not for other tables.

Regards
Michel
Re: Reuse of Space in 9.2.0.1.0 [message #281242 is a reply to message #281234] Fri, 16 November 2007 03:49 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Michel,

Please clear my doubt regarding this... If even after deleting some rows from the table, the free space in each datablock is (suppose) not below PCTUSED parameter, then new insertions into the table will not use this space. right ?

Nand

[Updated on: Fri, 16 November 2007 03:51]

Report message to a moderator

Re: Reuse of Space in 9.2.0.1.0 [message #281243 is a reply to message #281224] Fri, 16 November 2007 03:54 Go to previous messageGo to next message
DaHuhn
Messages: 8
Registered: November 2007
Junior Member
First of all,
Thanks for your really quick reply!

Give me the change to understand you completey:

For example we got a table which gathers data (xml) minute by minute (every minute a new row) for the last 3 years by an import procedure.
Now we deleted row by row the data "older" than 01.01.2006.
The import procedure is still the same, also the incoming data is going into the same table. --> it will use free rows in the table?
Is there a way in oracle 9.2.0.1.0 Enterprise to "check" this precisely?

At the moment we thought our last chance would be upgrading to 10gR2 and use the "Live Reorg"...

Many Regards so far and thank you again.
d.h.


Re: Reuse of Space in 9.2.0.1.0 [message #281244 is a reply to message #281242] Fri, 16 November 2007 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it was a short answer.
I should say:
- it can be used for updates of current rows
- it can be used for new rows if percentage of used space is less than pctused or if you use ASSM

But as OP said "we deleted a lot of content" I assumed blocks are often quite empty.

Regards
Michel
Re: Reuse of Space in 9.2.0.1.0 [message #281254 is a reply to message #281244] Fri, 16 November 2007 04:20 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Another doubt Michel,

One datablock may have data from multiple tables...So, other tables which have data in these datablocks should also be able to use this space for insertions.


Nand
Re: Reuse of Space in 9.2.0.1.0 [message #281256 is a reply to message #281224] Fri, 16 November 2007 04:23 Go to previous messageGo to next message
DaHuhn
Messages: 8
Registered: November 2007
Junior Member
Hmmm, is there a chance for me to find out in Enterprise Manager how it deals with free space?
Re: Reuse of Space in 9.2.0.1.0 [message #281268 is a reply to message #281244] Fri, 16 November 2007 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:

The import procedure is still the same, also the incoming data is going into the same table. --> it will use free rows in the table?

It depends, see previous answer:
Quote:

it can be used for new rows if percentage of used space is less than pctused or if you use ASSM


Quote:

One datablock may have data from multiple tables..

No. Blocks are private to one segment.

Regards
Michel
Re: Reuse of Space in 9.2.0.1.0 [message #281271 is a reply to message #281268] Fri, 16 November 2007 05:03 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Thank you Michel.

Nand



[Updated on: Fri, 16 November 2007 05:12]

Report message to a moderator

Re: Reuse of Space in 9.2.0.1.0 [message #281276 is a reply to message #281268] Fri, 16 November 2007 05:35 Go to previous messageGo to next message
DaHuhn
Messages: 8
Registered: November 2007
Junior Member
I see,

Quote:

it can be used for new rows if percentage of used space is less than pctused or if you use ASSM


Yes, i use SEGMENT SPACE MANAGEMENT = AUTO.

But how can i be sure that the free space is really used again?
Sad
Re: Reuse of Space in 9.2.0.1.0 [message #281300 is a reply to message #281276] Fri, 16 November 2007 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Trust Oracle or make tests and dump the blocks.

Regards
Michel
Re: Reuse of Space in 9.2.0.1.0 [message #281306 is a reply to message #281224] Fri, 16 November 2007 06:40 Go to previous messageGo to next message
DaHuhn
Messages: 8
Registered: November 2007
Junior Member
It`s a pity.
I thought there is some kind of overview (e.g. Tablespace Map) which tells me something like "Block/Segment available for use again" or something like that...
Re: Reuse of Space in 9.2.0.1.0 [message #281310 is a reply to message #281306] Fri, 16 November 2007 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is, this is the allocation bitmap but it is not exposed.
You can try to play with dbms_space package.

Regards
Michel
Re: Reuse of Space in 9.2.0.1.0 [message #281317 is a reply to message #281310] Fri, 16 November 2007 07:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yuo can use DBMS_ROWID to get the block number that a row is stored in.
You could use that to check that more recent rows can be found in older blocks.
Re: Reuse of Space in 9.2.0.1.0 [message #281331 is a reply to message #281224] Fri, 16 November 2007 07:34 Go to previous messageGo to next message
DaHuhn
Messages: 8
Registered: November 2007
Junior Member
Maybe an easier way (for me Confused )

As the data to insert into the table is imported every minute...
usually every minute a new row should be added into the table?
(As far as i understand our import procedure)

--> At the moment, i should have "free" rows so that the inserts should not ADD new rows (until the free rows are used up again)?

I would do a "select count (*) from my_table;"
--> Result e.g. 1 000 000 items.
--> If i do it again in 12 hours and the row count did not raise (still 1 000 000) --> inserts have successfully been put in the free rows.
Re: Reuse of Space in 9.2.0.1.0 [message #281335 is a reply to message #281331] Fri, 16 November 2007 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

At the moment, i should have "free" rows so that the inserts should not ADD new rows (until the free rows are used up again)?

There is no such thing that "free row", there is free space in block that can be filled by rows.

Regards
Michel
Re: Reuse of Space in 9.2.0.1.0 [message #281338 is a reply to message #281335] Fri, 16 November 2007 07:51 Go to previous messageGo to next message
DaHuhn
Messages: 8
Registered: November 2007
Junior Member
I am Sorry,
Michel Cadot wrote on Fri, 16 November 2007 07:43


There is no such thing that "free row", there is free space in block that can be filled by rows.

Regards
Michel


I mean: As long as the "count (*) from my_table" does not "raise" --> The inserts (rows) will use free space in the block.

[Updated on: Fri, 16 November 2007 07:57] by Moderator

Report message to a moderator

Re: Reuse of Space in 9.2.0.1.0 [message #281340 is a reply to message #281338] Fri, 16 November 2007 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
count(*) counts the actual row inside the table, so if you insert new rows count(*) increases whether you use free space in blocks or not.

Regards
Michel
Re: Reuse of Space in 9.2.0.1.0 [message #281711 is a reply to message #281340] Mon, 19 November 2007 01:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
However, fllowing that approach, if you make sure the tables are analysed before and after the operation, you could check the BLOCKS and NUM_ROWS columns in ALL_TABLES
If you're reusing empty space, then BLOCKS should stay the same, while NUM_ROWS should rise.
Re: Reuse of Space in 9.2.0.1.0 [message #282599 is a reply to message #281340] Thu, 22 November 2007 08:21 Go to previous message
DaHuhn
Messages: 8
Registered: November 2007
Junior Member
Dear all,

thanks for your hints & ideas.
I finally got the dbms show_space procedure working (asktom...) which gives me the following result:

Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 5
FS2 Blocks (25-50) ..................... 13
FS3 Blocks (50-75) ..................... 18
FS4 Blocks (75-100)..................... 2,772,513
Full Blocks ..................... 7,360,401
Total Blocks............................ 10,147,456
Total Bytes............................. 83,127,959,552
Total MBytes............................ 79,277
Unused Blocks........................... 4,096
Unused Bytes............................ 33,554,432
Last Used Ext FileId.................... 20
Last Used Ext BlockId................... 622,728
Last Used Block......................... 4,096


Daily Monitoring through the last days of the output shows me that every 24h about 10k of the FS4 Blocks are being used (by the import procedure) again.
This makes me believe that the free Blocks we created by DELETE are being re-used now... until the FS4 Blocks are used up again. (Will take some days, hopefully)

Kind regards and thank you again for the help.
Previous Topic: oracle 8i: full sql text in v$sqlarea
Next Topic: Books for oracle
Goto Forum:
  


Current Time: Thu Dec 08 19:52:29 CST 2016

Total time taken to generate the page: 0.16156 seconds