Tag businessintelligence

Table Size with Query on Oracle

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

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' );