-
space occupied by a schema
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
PNRDBA
-
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
I remember when this place was cool.
-
You might try
select sum(bytes) from dba_extents
where owner='unused_schema_name'.
That will give all the space allocated to the schema.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
-
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
PNRDBA
-
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:
Code:
select owner, sum(bytes) from dba_SEGMENTS
group by owner
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thank you all gurus in solving this problem, i got it....
Thanks again
PNRDBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|