Used Space
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Used Space

  1. #1
    Join Date
    Nov 2000
    Posts
    178

    Question

    Hello,

    I queried the Oracle 8i (8.1.6) database and found out that we've used 90% of the space available. The CEO believes that the space was claimed because of the allocated space during creation but the database does not contain enough data to fill up this space.

    Can anyone out there throw more light on this in case there is a way to distinguish the weed from the corn.

    Please help!!!

    Ac

  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    You can make an estimate of how many blocks are used for data in a table.

    select count(distinct(dbms_rowid.rowid_block_number(row_id))) from MyTable;
    gives you the number of blocks in MyTable that are used for storing data.

    Hope this helps
    Gert

  3. #3
    Join Date
    Nov 2000
    Posts
    178
    Gert,

    Thanks for your reply.
    I ran the query on a table "CUSTOMER" and got the following error.

    SELECT count(distinct(dbms_rowid.rowid_block_number(customer_id))) FROM customer;

    ORA-06553: PLS-306: Wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER'.

    What I'm I doing wrong?


  4. #4
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    Sorry, my mistake

    I meant rowid and not row_id

    so the statement must be

    SELECT count(distinct(dbms_rowid.rowid_block_number(ROWID))) FROM customer;

    Hope this helps
    Gert


  5. #5
    Join Date
    Nov 2000
    Posts
    178

    Smile

    Gert,

    Thanks! It works. I queried one of the tables and got 4 blocks. The block size is 8192.
    Does this mean that there are 4 x 8192 bytes and if so what's taking up the rest of the space and how do Isafely reclaim the space.
    At the moment the above-mentioned table is taking up 440,401,920 bytes. What's hapenning.

    Ac

  6. #6
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    Yes that's what it means.

    You also have to take in account that migrated/chained rows can occupy some extract blocks.

    Also if you have LONG/LOB/BLOB objects in your table, those can take a lot of extra blocks you don't see using this query.

    But if no chained/migrated rows, or (B)LOB's, it's just a fact that the table was created with an initial extent that was too big.

    You can do a "move table" ( since you in 8.1.6 ) and specify new storage parameters.

    Hope this helps
    Gert


  7. #7
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    Smile

    Hi ac,

    Can you tell me the result of these queries?

    select tablespace_name, sum(bytes) free_bytes
    from dba_free_space
    group by tablespace_name
    /

    select d.tablespace_name, sum(d.bytes) total_size
    from dba_data_files d
    group by d.tablespace_name
    /

    When you create a datafile on disk the space for the datafile is allocated at once on discWhen new no objects are placed in this datafile so in a way it is empty.

    Hope this helps.

    tycho

  8. #8
    Join Date
    Nov 2000
    Posts
    178
    Hello Tycho,

    Thanks 4 the suggestion.

    I ran the queries and got the following results:

    Total Bytes: 2097152000
    Free Bytes: 203022336

    What's your opinion and how do I get the realistically used space.

    Ac

Posting Permissions

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



Click Here to Expand Forum to Full Width