space occupied by a schema

[pnrdba]

Hi Gurus
We are running Oracle 9i on Linux 7.1 . We got 8 schemas in our database and our database size is growing in a massive way. We figured out a schema which is not in use now and we want to drop that after taking a backup. Before dropping that schema, i want to know how much space is occupied by that schema in our database. Can any one please tell me how to calculate the space occupied by a particular schema? All our tablespaces are shared across different users. So, i can't calculate based on the TS size.

Any help would be greatly appreciated .....

Thanks in advance

[Mr.Hanky]

Well, one way is to backup that schema, drop it, and see how much you gained. That is the easy way.

Another would be to calculate the size of each table and index owned by that schema, that could be more involved.

If it s not used why not just drop it?
Why do you need to get this info, it's not as if you will be loosing space?

MH

[jrpm]

You might try

select sum(bytes) from dba_extents
where owner='unused_schema_name'.

That will give all the space allocated to the schema.

[pnrdba]

First let me thank you Mr.Hanky for your immediate response.
I've to submit the free space which i'm going to get by dropping that unused schema , to my manager. He want to know the statistics of each schema. Though it is unsued, but we have all the data of the finished projects in that schema. So, i've to know the size and inform him by today evening.

Sorry to say this , but , The two methods which you told cannot be done here. B'coz its on our prod box. Is there any other way round?

Thanks again

[jmodic]

Originally posted by jrpm
select sum(bytes) from dba_extents
where owner='unused_schema_name'.

This one seems even more logical (and efficient), although the results of both should be the same:

select owner, sum(bytes) from dba_SEGMENTS
group by owner

[pnrdba]

Thank you all gurus in solving this problem, i got it....


Thanks again