Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Unanswered: Calculate table free/used space in 9i rel. 2

    We're using Oracle 9i Rel. 2. All of our tablespaces are using LMT and ASSM.
    From time to time, there will be row deletions. How do we then calculate the actual space being used by a table. I'm not talking about space that's allocated to the table, but actual space usage within the table.

    We tried using DBMS_SPACE.SPACE_USAGE, but I don't think it's what we're looking for or perhaps we're misinterpretting the output.

  2. #2
    Join Date
    Sep 2003
    Virginia, USA
    do a search on

  3. #3
    Join Date
    Apr 2002
    California, USA
    ORACLE tags each record with a unique rowid that contains the datafile,the block number, and the rownumber. To find out how much space a table actually uses, count the number of unique blocks in that table's rowid.

    Since a single table could span multiple database files and the same block number could show up in both files, count the number of distinct block/file combinations:

    select count(distinct(substr(rowid,1,8)||substr(rowid,15, 4)))
    from <table>;

    It will get you within 5% of reality, which is close enough for what you need.

    Hope that helps,

    clio_usa - 8/8i/9i OCP DBA

Posting Permissions

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