| Reuse of Space in 9.2.0.1.0 [message #281224] |
Fri, 16 November 2007 03:20  |
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 #281242 is a reply to message #281234] |
Fri, 16 November 2007 03:49  |
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 |
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 #281254 is a reply to message #281244] |
Fri, 16 November 2007 04:20 |
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 #281306 is a reply to message #281224] |
Fri, 16 November 2007 06:40 |
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 #281317 is a reply to message #281310] |
Fri, 16 November 2007 07:02 |
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 #281338 is a reply to message #281335] |
Fri, 16 November 2007 07:51 |
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 #281711 is a reply to message #281340] |
Mon, 19 November 2007 01:28 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)