Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    29

    Unanswered: how to check table space in oracle

    hi

    i am not much familiar about dba terms

    i have used below quries to know tablespaces but i didn't found Free space column

    select * from dba_tablespaces

    select * from user_tablespaces

    select * from dba_data_files

    select * from dba_free_space

    please advise me how to check free and occupied table space in oracle


    Thanks in Advance!!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Don't you feel the bytes?

    Code:
    SQL> desc dba_free_space
     Name              Null?    Type
     ----------------- -------- ------------
     TABLESPACE_NAME            VARCHAR2(30)
     FILE_ID                    NUMBER
     BLOCK_ID                   NUMBER
     BYTES                      NUMBER
     BLOCKS                     NUMBER
     RELATIVE_FNO               NUMBER
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    max shrink by TOM kyte ..

    first part (select ) tells u the story .. and second select let u fix it .



    ----------- maxshrink.sql ----------------------------------

    set verify off
    column file_name format a50 word_wrapped
    column smallest format 999,990 heading "Smallest|Size|Poss."
    column currsize format 999,990 heading "Current|Size"
    column savings format 999,990 heading "Poss.|Savings"
    break on report
    compute sum of savings on report

    column value new_val blksize
    select value from v$parameter where name = 'db_block_size'
    /

    select file_name,
    ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
    ceil( blocks*&&blksize/1024/1024) currsize,
    ceil( blocks*&&blksize/1024/1024) -
    ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
    from dba_data_files a,
    ( select file_id, max(block_id+blocks-1) hwm
    from dba_extents
    group by file_id ) b
    where a.file_id = b.file_id(+)
    /

    column cmd format a75 word_wrapped

    select 'alter database datafile '''||file_name||''' resize ' ||
    ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
    from dba_data_files a,
    ( select file_id, max(block_id+blocks-1) hwm
    from dba_extents
    group by file_id ) b
    where a.file_id = b.file_id(+)
    and ceil( blocks*&&blksize/1024/1024) -
    ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
    /

Posting Permissions

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