space occupied by a schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: space occupied by a schema

Hybrid View

  1. #1
    Join Date
    Apr 2002
    Posts
    291

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    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.

  3. #3
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    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'

  4. #4
    Join Date
    Apr 2002
    Posts
    291
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Apr 2002
    Posts
    291
    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
  •  



Click Here to Expand Forum to Full Width