Determining the space used by particular tables

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.

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.

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...

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

as each table is queried.

This was last published in October 2005

Sybase vs. Oracle: Users speak out

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

Meet all of our Oracle Database / Applications experts

View all Oracle Database / Applications questions and answers

SearchDataManagement

Related Resources

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.