<\/p>\n
When calculating the size of a table in an Oracle database, it is incorrect to just look at the data size. It is necessary to calculate the size of the index, lobsegment and lobindex objects of the table. Because when we move the table to another database, all objects belonging to the table will also be moved.<\/p>\n
The following query can be used to calculate the actual size of all tables in a schema. You must write the user\/schema name you want to calculate instead of the “YOUR_USER\/SCHEMA_NAME” section in the query.<\/p>\n
SELECT\r\nowner, table_name, TRUNC(sum(bytes)\/1024\/1024) MB\r\nFROM\r\n(SELECT segment_name table_name, owner, bytes\r\nFROM dba_segments\r\nWHERE segment_type in ('TABLE','TABLE SUBPARTITION','TABLE PARTITION')\r\nUNION ALL\r\nSELECT i.table_name, i.owner, s.bytes\r\nFROM dba_indexes i, dba_segments s\r\nWHERE s.segment_name = i.index_name\r\nAND s.owner = i.owner\r\nAND s.segment_type in ('INDEX','INDEX SUBPARTITION','INDEX PARTITION')\r\nUNION ALL\r\nSELECT l.table_name, l.owner, s.bytes\r\nFROM dba_lobs l, dba_segments s\r\nWHERE s.segment_name = l.segment_name\r\nAND s.owner = l.owner\r\nAND s.segment_type = 'LOBSEGMENT'\r\nUNION ALL\r\nSELECT l.table_name, l.owner, s.bytes\r\nFROM dba_lobs l, dba_segments s\r\nWHERE s.segment_name = l.index_name\r\nAND s.owner = l.owner\r\nAND s.segment_type = 'LOBINDEX')\r\nWHERE owner in UPPER('YOUR_USER\/SCHEMA_NAME')\r\nGROUP BY table_name, owner\r\nORDER BY SUM(bytes) desc;<\/pre>\n\n<\/div>\n