Q

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.

This was first published in October 2005

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close