Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2001
    Posts
    80

    Unanswered: tablespace usage

    If I create the tablespace with local extent managment (i.e. create tablespace . . . . extent management local . . ), can I get the accurate free space information by query dba_free_space? if not, how can I get these information?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    yes, you should be able to do so.
    here is a wonderful query from Tom Kyte I use frequently:
    PHP Code:
    set pages 30
    set lines 150
    column  dummy noprint
    column  pct_used format 999.9       heading 
    "%Used"
    column  name     format a18         heading "Tablespace Name"
    column  Kbytes   format 999,999,999,999 heading "KBytes"
    column  used     format 999,999,999,999 heading "Used"
    column  free     format 999,999,999,999 heading "Free"
    column  largest  format 999,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
    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 ...

Posting Permissions

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