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

# Determining table size

I'd like to know the size occupied by each table and its details, such as the size of the index for each table, the size allocated, and the free space for each table of my database in Oracle.

The "size" of a table can mean many things. I'll cover the many ways that people use to determine the size of an object.

To see how much space is allocated for an object, issue the following query:

```SELECT block,bytes FROM dba_segments
WHERE owner='SCOTT' and segment_name='EMP';
```

In all of these examples, I'll use the SCOTT.EMP table, but you can easily rewrite these examples for your specific needs.

If you calculate statistics on the table, then you can use the average row length and the total number of rows as an estimate of the total space used.

```ANALYZE TABLE scott.emp COMPUTE STATISTICS;
SELECT avg_row_len,num_rows,avg_row_len*num_rows
AS used_space FROM all_tables WHERE owner='SCOTT' and table_name='EMP';
```

You can use similar queries for indexes.

The DBMS_SPACE package can compute how much space is being used, particularly the SPACE_USAGE procedure of this package. For instance, to see the space used by one of your tables, you might used code like the following in SQL*Plus:

```variable unf number;   --unformatted blocks
variable unfb number;  --unformatted bytes
variable fs1 number;   --number of blocks with 0-25% free space
variable fs1b number;  --number of bytes with 0-25% free space
variable fs2 number;   --number of blocks with 25-50% free space
variable fs2b number;  --number of bytes with 25-50% free space
variable fs3 number;   --number of blocks with 50-75% free space
variable fs3b number;  --number of bytes with 25-50% free space
variable fs4 number;   --number of blocks with 75-100% free space
variable fs4b number;  --number of bytes with 25-50% free space variable full number;
--number of full blocks variable fullb number;
--number of full bytes

begin
dbms_space.space_usage('SCOTT','EMP','TABLE',
:unf, :unfb, :fs1, :fs1b, :fs2, :fs2b,
:fs3, :fs3b, :fs4, :fs4b,
:full, :fullb); end; / print unf ; print unfb ; print fs4 ; print fs4b; print fs3 ; print fs3b; print fs2 ; print fs2b; print fs1 ; print fs1b; print full; print fullb;
```

I've put comment besides the variable names to show their meaning.

It is important to note that the SPACE_USAGE procedure details the space used below the table's High Water Mark. Blocks above the HWM are not analyzed in this process.

The DBMS_SPACE.UNUSED_SPACE can be used to determine how space is not being used in the segment. An example of this procedure can be seen below:

```variable totalblk number;
--number of total blocks variable totalbyt number;
--number of total bytes variable unusedblk number;
--number of unused blocks variable unusedbyt number;
--number of unused bytes variable lastextentfile number;
--last used extent file id variable lastextentblk number;
--last used exent block number variable lastusedblk number;
--last used block

begin
dbms_space.unused_space('SCOTT','EMP','TABLE',
:totalblk, :totalbyt,
:unusedblk, :unusedbyt,
:lastextentfile, :lastextentblk,
:lastusedblk);
end;
/

print totalblk;
print totalbyt;
print unusedblk;
print unusedbyt;
print lastextentfile;
print lastextentblk;
print lastusedblk;
```

By taking the total number of blocks and subtracting the unused blocks, you can get a good idea of how many blocks are being used. If you want to know the usage of those blocks, you'll have the use the SPACE_USAGE procedure discussed above.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Oracle Data Science efforts advance with new services

Oracle adds news new services to its cloud infrastructure platform in a bid to provide data scientists, as well as data analysts,...

• ### Data governance metrics: Data quality, data literacy and more

Statistics on data quality are the most common metrics used to track data governance programs, but other types of data-related ...

• ### Snowflake raises \$479 million to boost cloud data warehouse

Snowflake is looking to grow its data warehouse platform with new funding and a partnership with Salesforce that could ...

• ### Yellowbrick Data, MicroStrategy partnership aims to enhance analytics

The new partnership will enable flexible scalability, support for mixed workloads and multi-cloud support. The goal is to provide...

• ### Ethical data collection in the spotlight over new regulations

New regulations have put data privacy top of mind for many consumers. Here's a look at how businesses can incorporate ethical ...

• ### Regulatory approval allows Google acquisition of Looker to close

Google's purchase of analytics vendor Looker was approved by a United Kingdom regulatory agency, more than eight months after the...

## SearchSAP

Business Suite 7 will receive mainstream maintenance until 2027, a decision industry observers say reflects the difficulty of an ...

• ### SAP cloud revenue up, S/4HANA adoption still slow

Although SAP reported impressive revenue and cloud growth for 2019, it remains challenged to get the majority of its installed ...

• ### SAP ECC to S/4HANA migration: 4 essential issues

The ERP landscape is transforming and SAP customers in particular are dealing with decision overload related to ECC to S/4HANA ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Why the 8 Java primitive data types are not objects

What's the difference between Java primitive types and objects? To start, don't classify primitive types as objects. Let's ...

• ### A brief history of Java: How it forever changed programming

The Java programming language is an important tool in an application developer's quiver. But Java has also undergone many changes...

• ### How to properly perform Java String comparisons

Don't let incorrect Java String object comparisons bog down your code. Follow this tutorial to understand the differences between...

## SearchDataCenter

• ### Initiative aims to improve data center incident reporting

The Data Center Incident Reporting Network hopes to pull back the smoke screen on software and hardware issues to improve ...

• ### A guide to GPU implementation and activation

Data center GPU usage goes beyond hardware. Admins must work with vendors and developers to have the right software architecture ...

• ### Key components for negotiating an HPC colocation contract

When it comes to HPC colocation, there are specialized areas to cover in a service-level agreement. Review provider protocols for...

## SearchContentManagement

• ### Akeneo rolls out newest version of PIM software

Akeneo PIM version 4.0 features digital asset manager, API-integrated connection modules and AI attribute mapping to better ...

• ### Get to know 4 workflow automation tools

Workflow automation can benefit businesses by making manual processes digital, giving employees more time to work on other tasks....

Organizations that sell content might benefit from a paywall. Here are some questions to ask when deciding if a paywall strategy ...

## SearchHRSoftware

• ### 8 HR technology trends CHROs should investigate in 2020

Many emerging HR technologies are solidly in the realm of 'must-haves,' while others remain hype. Here's some advice to help you ...

• ### How to attract top talent in a competitive hiring market

Finding the best candidates can seem impossible in today's competitive hiring landscape. Use these methods to help you attract ...

• ### Federal workforce too reliant on degrees, says White House

In its budget proposal, the Trump administration complains that hiring rules hurt its ability to hire those just out of college ...

Close