Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76

    Unanswered: Tablespace fragmentations problem

    Someone deleted a couple of tables in the the USERS tablespace at our organization, and I rebuild all the indexes so that they were all in USERS_IDX. Now, using OEM if I go to see the space used by the tablespace, I see 6G allocated, -5.7G used, making -943.5% used.

    Obviously this isn't correct values, and I'm guessing the problem is because of fragmentations.

    How do I get Oracle to obtain the tablespace usage correctly?
    System: Oracle 10g on Fedora Core 1

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    DBA_EXTENTS & DBA_FREE_SPACE should give you the info you want.

    If you want to resolve the fragmentation, try exporting the objects in the tabespace (compress=y will automatically defrag the objects), recreate the tablespace & then import. Or, if you've only a few tables in the tablespace you could do an "Alter table...move" but specifying the same tablespace name (sounds weird, but it works (from 8i onwards, I believe)).
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    tablespace fragmentation? what's that?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    If you are using DMT, you can also try submitting a 'alter tablespace tablespace_name coalesce'. But in any case, if this doesn't happens much, I would do what cis_groupie is suggesting.

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Exactly - fragmentation is an issue with old-style dictionary-managed tablespaces. Locally-managed tablespaces were a new feature in 8i and in effect solve that problem, as well as being more efficient all round. Can you tell which you are using for USERS and USERS_IDX?

  6. #6
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76
    Quote Originally Posted by WilliamR
    Exactly - fragmentation is an issue with old-style dictionary-managed tablespaces. Locally-managed tablespaces were a new feature in 8i and in effect solve that problem, as well as being more efficient all round. Can you tell which you are using for USERS and USERS_IDX?
    They are both locally-managed tablespaces.
    System: Oracle 10g on Fedora Core 1

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by unicef2k
    They are both locally-managed tablespaces.
    then you don't have fragmentation at the tablespace level, only the table level.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76
    Quote Originally Posted by cis_groupie
    DBA_EXTENTS & DBA_FREE_SPACE should give you the info you want.

    If you want to resolve the fragmentation, try exporting the objects in the tabespace (compress=y will automatically defrag the objects), recreate the tablespace & then import. Or, if you've only a few tables in the tablespace you could do an "Alter table...move" but specifying the same tablespace name (sounds weird, but it works (from 8i onwards, I believe)).
    I used DBA_EXTENTS & DBA_FREE_SPACE, and got values that I didn't want. I ran the tablespace usage query found here: http://vsbabu.org/oracle/sect03.html and still got:
    Code:
    TABLESPACE_NAME | BYTES_USED | BYTES_FREE | LARGEST | PERCENT_USED
    ...
    USERS | 6362234880 | 6.6391E+10 | 160890880 | -943.51
    I guess I'll have to export, then import as you suggested after hours.
    Last edited by unicef2k; 12-13-04 at 15:29.
    System: Oracle 10g on Fedora Core 1

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    do you get anything different by running this script by Tom?
    PHP Code:
    column  dummy noprint
    column  pct_used format 999.9       heading 
    "%|Used"
    column  name     format a16          heading "Tablespace Name"
    column  Kbytes   format 999,999,999 heading "KBytes"
    column  used     format 999,999,999 heading "Used"
    column  free     format 999,999,999 heading "Free"
    column  largest  format 999,999,999 heading "Largest"
    break   on report
    compute sum of kbytes on report
    compute sum of free on report
    compute sum of used on report

    select nvl
    (b.tablespace_name,
           
    nvl(a.tablespace_name'UNKOWN')) name,
           
    kbytes_alloc kbytes,
           
    kbytes_alloc-nvl(kbytes_free,0used,
           
    nvl(kbytes_free,0free,
           ((
    kbytes_alloc-nvl(kbytes_free,0)) / kbytes_alloc)*100 pct_used,
           
    nvl(largest,0largest
    from 
    select sum(bytes)/1024 Kbytes_free,
                  
    max(bytes)/1024 largest,
                  
    tablespace_name
           from  sys
    .dba_free_space
           group by tablespace_name 
    a,
         ( 
    select sum(bytes)/1024 Kbytes_alloc,
                  
    tablespace_name
           from sys
    .dba_data_files
           group by tablespace_name 
    )b
    where a
    .tablespace_name (+) = b.tablespace_name
    order by 1

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76
    I get the same results with the last query.
    System: Oracle 10g on Fedora Core 1

Posting Permissions

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