And just what do you mean by that? Boy, it's a good job you're in Virginia, or I'd be a' callin' on you. Smack-down time!Quote:
Originally posted by Alchemy
... factoring in what I perceive the ages to be ...
Printable View
And just what do you mean by that? Boy, it's a good job you're in Virginia, or I'd be a' callin' on you. Smack-down time!Quote:
Originally posted by Alchemy
... factoring in what I perceive the ages to be ...
OK, here's the story.Quote:
Originally posted by theworm
Hello,
We currently run a nightly report that gives us some information about our customers databases. Some of the info is the total amount of space being used by the database. For this number I query on the sys.dba_data_files. We also try to see what tables have a large number of rows. For this I query num_rows from dba_tables. We started to do a large number of deletes from our database. The problem is were not seeing the amount of space being used decreasing. Is there any thing that we need to do to reclaim that space?
When you delete rows from a table in Oracle, the space is not reclaimed. The block that the row was in gets some more free space in it, and if the amount of space used is at or below the PCTUSED setting for that table, then the block is a candidate for new rows to be inserted.
Even if you delete every row from the table, all you have is a lot of empty blocks, but space is still allocated.
To deallocate the space the best method is generally to run ...
"ALTER TABLE MY_TABLE MOVE"
... followed by a rebuild of all indexes on that table (the indexes are rendered invalid by the table move).
Alternatively if you want to find out how much empty space is in a table, you can analyze it and look at various columns in the user_tables or dba_tables view ...
AVG_ROW_LEN, NUM_FREELIST_BLOCKS, AVG_SPACE_FREELIST_BLOCKS, EMPTY_BLOCKS, AVG_SPACE.
These are documented in the Oracle Reference, which you can find (along with the Concepts Guide) at http://tahiti.oracle.com
Thank you, for the explanation. I’m very new to this and some times, I’m trying to prove my self quickly. I appreciate the help.
Always a pleasure -- but seriously, that Concepts Guide is a great way of tackling the learning curve.
See http://asktom.oracle.com also
I will start the nightly reading tonight
I'm glad I live no where near Colorado Springs, I couldn't get all the way through Ohio without getting lost.
I see that you took it as humor...I meant no disrespect to either of you. I too am learning Oracle, and I get a great deal of help reading this site. The article you provided for worm looks to have great information, I too will be reading it.
Thanks
Rick
hi slimdave,
your notes are better than oracle docs.
concise, precise and lucid.
i enjoy your posts.
but we should bear your criticism.
thank you slimdave!
-Raja
Nobody can go through Ohio without getting lost. The people that live there just stopped trying to find their way back.Quote:
Originally posted by Alchemy
I couldn't get all the way through Ohio without getting lost.
Four years ... four years of my life gone in that place ... * sob *Quote:
Originally posted by marist89
Nobody can go through Ohio without getting lost. The people that live there just stopped trying to find their way back.
Should I be offended???? I came back after four years in KS. Sure, easier to find your way around out there, but a hell of lot more to do in Ohio!Quote:
Nobody can go through Ohio without getting lost. The people that live there just stopped trying to find their way back.
:)
If anyone is ever in Ohio - I'd be happy to show you around! And I promise to only get lost once or twice!
Jodie