TB Blog

Table Size with Query on Oracle

By inOracle DB Tag , , , , , , , , , , , ,

Sometimes you have lots of data but not enough space at your storage. And you need to check your tables sizes and tables spaces etc. I think it is easier use queries than use clients utilities.

We can use system tables for control size, parallelism degree , compression level and more. I use this two scripts for check things up.

First one the size ; 

select 
 us.segment_name,
 us.TABLESPACE_NAME, 
 sum(us.bytes)/1024/1024/1024 GB ,
 sum(us.bytes)/1024/1024  MB 
from user_segments us
where  us.segment_type='TABLE'  
 and us.segment_name LIKE '%YOUR_TABLE_NAME%' 
group by 
 us.segment_name , 
 us.TABLESPACE_NAME;

The second one compress ,parallelism degrees and etc. ; 

SELECT 
 ut.table_name ,
 ut.tablespace_name , 
 ut.logging, 
 ut.num_rows,
 ut.degree , 
 ut.instances , 
 ut.last_analyzed ,
 ut.partitioned    
FROM user_tables ut

But generally I use them both as below ; 

SELECT 
 us.table_name ,
 us.TABLESPACE_NAME,
 us.gb_SIZE,
 us.mb_SIZE,
 ut.logging, 
 ut.num_rows,
 ut.degree , 
 ut.instances , 
 ut.last_analyzed ,
 ut.partitioned   ,
 ut.compression 
FROM  
( select 
 us.segment_name table_name ,
 us.TABLESPACE_NAME, 
 sum(us.bytes)/1024/1024/1024 GB_SIZE ,
 sum(us.bytes)/1024/1024  MB_SIZE 
from user_segments us
where  us.segment_type='TABLE'  
 and us.segment_name LIKE '%Z$%' 
 AND TABLESPACE_NAME ='SATA'
group by 
 us.segment_name , 
 us.TABLESPACE_NAME ) us  
 INNER JOIN 
 (SELECT 
 ut.table_name ,
 ut.tablespace_name , 
 ut.logging, 
 ut.num_rows,
 ut.degree , 
 ut.instances , 
 ut.last_analyzed ,
 ut.partitioned   ,
 ut.compression 
FROM user_tables ut   ) ut  
ON us.table_name = ut.table_name 
AND us.TABLESPACE_NAME = ut.TABLESPACE_NAME 
ORDER BY MB_SIZE DESC

I will be glad if it helps ,

Have a nice day

Stored Procedure : Truncate Partitions by HIGH_VALUE

By inOracle DB Tag , , , , ,

Hi !

In this case I need to truncate a partition but I don’t know it’s name. This table has a range partition for every month , so I know HIGH_VALUE of particular partition.

We can find partition_name in ALL_TAB_PARTITIONS table but HIGH_VALUE column has CLOB data type. We need a script like below for to change it’s type to char.

CREATE OR REPLACE PROCEDURE   GUNES.TRUNCATE_PARTITION  
( p_table_owner IN varchar2 ,
  p_table_name IN VARCHAR2  ,
  p_high_value IN varchar2     ) IS


v_control varchar2 (200); 


BEGIN

SELECT  'ALTER TABLE '|| p_table_name || ' TRUNCATE PARTITION ' || (  

WITH DATA AS (

select table_name,

       partition_name,

          trim ( 

                     extractvalue (

                       dbms_xmlgen.getxmltype (

                       'select high_value from all_tab_partitions 
                                  where table_name='''

                                || table_name

                                || ''' and table_owner = '''

                                || table_owner

                                || ''' and partition_name = '''

                                || partition_name

                                || ''''),

                             '//text()') )

          high_value_in_date_format

  FROM all_tab_partitions

WHERE table_name = p_table_name AND table_owner = p_table_owner
)

SELECT partition_name FROM DATA  

WHERE  high_value_in_date_format = p_high_value )  AS DYN_TASK 

INTO v_control  

FROM dual ;


EXECUTE IMMEDIATE( v_control  ); 


EXCEPTION WHEN OTHERS THEN NULL;


END TRUNCATE_PARTITION;

To run this procedure ;

call  TRUNCATE_PARTITION('GUNES' ,'GUNES_PARTITION'  ,'20190399' );