Oracle table size does not fix it constantly changes with DML operation. There is not only one query to check the table size in Oracle. Some factors change the actual size of a table. Below I added all the queries for table space with each factor.
Additionally, I’ll describe how to change the table size, move the table to a different tablespace, and reduce the table size. Here we use the “DBA_SEGMENTS” view to get the size of the target table.
* check the table size in Oracle:
1. Table Size using dba_segments
-- SELECT segment_name, segment_type, bytes/1024/1024 AS "Size(MB)" FROM dba_segments WHERE segment_name = 'your_table_name'; --
Replace ‘YOUR_TABLE_NAME’ with the name of the table you want to check.
2. Table Size according to the Row Data.
-- select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from user_tables where table_name = 'MYTAB'; --
3. Table size from user_tables
-- Select table_name, round((blocks*8)/1024,2)||'MB' "size" from USER_tables where table_name = 'Employee_tab';
4. Query to check the Table Size in Oracle with Partition for a table
--
select PARTITION_NAME,sum(bytes)/1024/1024/1024 GB
from dba_segments
where SEGMENT_NAME=upper('TABLE_NAME')
and PARTITION_NAME=upper('Partiton_name')
group by PARTITION_NAME;
--5. Table size of Temp Tables
Oracle creates some temporary tables for sorting and many other situations in those cases we can see temporary table space by running the below query
-- SELECT table_name, segment_type, bytes/1024/1024 AS "Size(MB)" FROM dba_segments WHERE segment_type = 'TEMPORARY'; --
Knowing the table size is important for Oracle’s performance. Because a lot of data in the table means performance can be dropped. In such a situation, we need to create an index on the table sometimes or a partition table to increase the table space allocations and sometimes remove the temp table space allocations.
* Check All Tables in a Schema:
The below query returns all the table sizes for Query to check the Table Size in Oracle
-- select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from user_tables ; -- fetch all tables SELECT table_name FROM all_tables; --
* Change Table Size:
a. Move to a Different Tablespace: We can move a table into different table spaces but you have to be more careful because this can be a problem if you make a mistake. Also, an index is unusable or can be invalidated.
--ALTER TABLE YOUR_TABLE_NAME MOVE TABLESPACE NEW_TABLESPACE_NAME; --
Replace ‘YOUR_TABLE_NAME’ with the name of the table and ‘NEW_TABLESPACE_NAME’ with the name of the new tablespace.
b. Shrink Table: You can shrink a table according to the size of your table
-- alter table mytable enable row movement; alter table mytable shrink space; Alter table mytable shrink space cascade; --
* Reduce Table Size:
a. Purge Data: you already know table size grows with table data. If you want to reduce the table data then you have to remove unnecessary data from a table. Then you have to execute below simple query below with the condition. Condition is important because otherwise, you will lose the required data as well.
-- DELETE FROM YOUR_TABLE_NAME WHERE CONDITION; --
Replace ‘CONDITION’ with the appropriate condition to identify the data you want to delete.
Special Notes:
- Please backup or use other safe methods to recover data if an error happens in the middle. Moving tables or deleting data can lost your data.
- Moving data or tables can be affected by current transactions. Therefore do that kind of operation in maintenance mode.
- Follow database best practices when doing critical Operations with a database.
