Determining the space used by particular tables

Paul, I'm reorganizing a database that has tables and indexes in the same tablespace. How do I determine the space used by the tables (not allocated) owned by a particular user with all the table names beginning with CT_? I want to know what size the file system needs to be where the tablespace is being created.

    Requires Free Membership to View

    Login

You can determine the number of blocks that currently contain data for a table by issuing the following query against the table: 
select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from ;
Multiply the result by your db block size to get the number of bytes required.

You can code a PL/SQL block to construct and execute dynamic SQL for each table of interest, summing the results as each table is queried.

This was first published in October 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top