How to find tablesize in Oracle

Sometime we need to determine how much size will be allocated in our system.
Oracle provide query how to find table size. Its so simple, just put this script into sqlplus :

SELECT  *
FROM     USER_SEGMENTS
WHERE  SEGMENT_NAME in ( ‘TABLE_NAME1’, ‘TABLE_NAME2′,…);

If You want to convert into KB, MG or GB You should convert the values in the BYTE coulumn into

Gigabytes   => ROUND((BYTE/1073741824*100)/100)||’ GB’
Megabytes => ROUND((BYTE/1048576*100)/100)||’ MB’
Kilobytes    => ROUND((BYTE/1024*100)/100)||’ KB’

One thought on “How to find tablesize in Oracle

  1. Thank you.
    For Oracle10g changed BYTE to BYTES.
    Also select based on size and then union the tree selects together so each table comes out in KB, MB or GB. So far no PB 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s