Q
Problem solve Get help with specific problems with your technologies, process and projects.

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 as each table is queried.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

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

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close