Results 1 to 4 of 4

Thread: space problem

  1. #1
    Join Date
    Oct 2005
    Posts
    39

    Unanswered: space problem

    Hello All,

    I am facing some problem with my database which is 8.1.7.4. When users are deleting some data from the database, it is not getting reflected in free space.

    I tried doing "coalesce" but it's not doing any good.

    Kindly give some pointers to me.

    Thanks, Raj

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    How are you determining the lack of increase in disk space? If you are looking at the sizes of the files for tablespaces, you won't see the change. When you create a tablespace to store Oracle tables, the (disk) space is allocated to the files without regard to how much is actually taken by table data.
    JoeB
    save disk space, use smaller fonts

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Neither delete nor coalesce will reclaim space, you better look at RESIZE'ing your datafiles.

  4. #4
    Join Date
    Oct 2005
    Posts
    39
    Quote Originally Posted by joebednarz
    How are you determining the lack of increase in disk space? If you are looking at the sizes of the files for tablespaces, you won't see the change. When you create a tablespace to store Oracle tables, the (disk) space is allocated to the files without regard to how much is actually taken by table data.
    Hello,
    Thanks for your reply.
    I understand what u r saying ....I am using the below mentioned script to see the space :

    set linesize 120 pagesize 60 feedback off heading on
    ttitle skip center "Space usage report by Tablespace" skip 2
    col Tablespace format a15
    set numformat 999999.9
    clear breaks
    clear computes


    create or replace view sstemprpt_free as select
    tablespace_name,sum(bytes) free
    from sys.dba_free_space
    group by tablespace_name;

    create or replace view sstemprpt_bytes as
    select tablespace_name,sum(bytes) bytes
    from sys.dba_data_files
    group by tablespace_name;

    create or replace view sstemprpt_status as
    select a.tablespace_name,free,bytes
    from sstemprpt_bytes a,sstemprpt_free b
    where a.tablespace_name=b.tablespace_name(+);

    select tablespace_name "Tablesapce",
    round(bytes/(1024*1024),1) "Size_Mb",
    round(nvl(bytes-free,bytes)/(1024*1024),1) "Used_Mb",
    round(nvl(free,0)/(1024*1024),1) "Free_Mb",
    round(nvl(100*(bytes-free)/bytes,100),1) "Used_%"
    from sstemprpt_status
    order by 5 desc;

    select rpad('Total',30,'.') " ",
    round(sum(bytes)/(1024*1024),1) " ",
    round(sum(nvl(bytes-free,bytes))/(1024*1024),1) " ",
    round(sum(nvl(free,0))/(1024*1024),1) " ",
    round((100*(sum(bytes)-sum(free))/sum(bytes)),1) " "
    from sstemprpt_status;


    drop view sstemprpt_bytes;
    drop view sstemprpt_free;
    drop view sstemprpt_status;


    Please let me know if this is OK....and this will give me the correct
    free space or not...?? I understand this should give me.

    regards, Raj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •