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" fromMultiply 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.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments