space used by the database

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?

Check dba_free_space

Quote:

---

Originally posted by theworm
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?

---

The problem is that you have not read the Oracle documentation, and you don't understand how Oracle works.

This is very basic stuff. Go and read the Concepts Guide before your customer figures out that you are not very good at managing oracle databases.

Hey Slim. Thanks for the kind words. Colorado Springs is not that far from my work, what don't we meet in person so I can thank you personally

Quote:

---

Originally posted by theworm
Hey Slim. Thanks for the kind words. Colorado Springs is not that far from my work, what don't we meet in person so I can thank you personally

---

WTF is this?

Quote:

---

Originally posted by slimdave
WTF is this?

---

Sounds like he and a couple of big "friends" are going to duff you up.

Quote:

---

Originally posted by DaPi
Sounds like he and a couple of big "friends" are going to duff you up.

---

Fisticuffs, eh? Meet you behind the bike sheds after school, Wormy!

No friends needed

Quote:

---

Originally posted by theworm
No friends needed

---

Heh heh heh. A true professional.

If my general presumption is correct, factoring in what I perceive the ages to be, I think each rotating, buying the other a round at the local pub would be in order.
This could become quite time consuming and fruitless.

You folks keep me in stitches at time, that and I do get good information here.

Rick

Quote:

---

Originally posted by Alchemy
... factoring in what I perceive the ages to be ...

---

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 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?

---

OK, here's the story.

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

Quote:

---

Originally posted by Alchemy
I couldn't get all the way through Ohio without getting lost.

---

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 marist89
Nobody can go through Ohio without getting lost. The people that live there just stopped trying to find their way back.

---

Four years ... four years of my life gone in that place ... * sob *

Quote:

---

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!

:)

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

Quote:

---

Four years ... four years of my life gone in that place ... * sob *

---

Dave - where in Ohio were you?

Quote:

---

Originally posted by jodie
Dave - where in Ohio were you?

---

Dayton

* shudder *

Quote:

---

Dayton

* shudder *

---

Ahh.. that explains it. Dayton is definitely not indicative of the rest of Ohio. Quite nice in NE Ohio - if you don't mind a little Lake Effect snow! Columbus and Cincinnati are nice too!

Quote:

---

Originally posted by jodie
Ahh.. that explains it. Dayton is definitely not indicative of the rest of Ohio. Quite nice in NE Ohio - if you don't mind a little Lake Effect snow! Columbus and Cincinnati are nice too!

---

I think you're in a State of Denial ;)

Quote:

---

I think you're in a State of Denial

---

Probably!!! :D
Not all of us can be fortunate enough to live in sunny Co Springs!

Quote:

---

Originally posted by jodie
Probably!!! :D
Not all of us can be fortunate enough to live in sunny Co Springs!

---

I also work from home, from where i have a view of the front range of the Rockies, including Pike's Peak.

Nah nah!

I've never been been west of Belpre, I think it is in lower Warren County, just west of Parkersburg. If I'm not to awful mistaken I think one could see Pikes Peak from eastern Ohio.

Rick

Quote:

---

Originally posted by slimdave
I think you're in a State of Denial ;)

---

That's going to be the 51st state, isn't it?

Quote:

---

Originally posted by slimdave
I also work from home, from where i have a view of the front range of the Rockies, including Pike's Peak.

Nah nah!

---

Okay, I'll admit, I'm jealous. My twin sis lives out there, could see Pike's Peak from her old appt. It's just beautiful there. But cost-of-living is out of this world.

Umm.... anyone else think this thread has gone out in left field and probably should be moved??

Re isn't that going to be the 51st State?

Well, for years DaPi it had been bandied about that Puerto Rico would be the 51st, but I suspect that Denial will make it first.

i think its better if the post be moved to obfuction unlimited

Quote:

---

Originally posted by jodie
Umm.... anyone else think this thread has gone out in left field and probably should be moved??

---

Oh definately so. Then I can tell everyone about my trip to Breckenridge -- 2 hours in the car, and lots of lovely snow, lovely mountains etc..

better be moved to obf. unl.

I had a fine view of the sun rising behind the Mont Blanc as I drove in this morning. Unfortunately the view from my office window is dominated by Mike Wong and McDonald - a lot to answer for.

P.S. what's wrong with "left field"?