Stored Procedure: Add Partition

Partitioning ?

In DataWarehouses and OLTP systems we use partitions for keeping data separately. In this way when we run a query database engine doesn’t need to scan all the table , it just accesses the partition that needs.

Oracle defines its partitioning like this ;

Partitioning is powerful functionality that allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides a comprehensive range of partitioning schemes to address every business requirement. Moreover, since it is entirely transparent in SQL statements, partitioning can be used with any application, from packaged OLTP applications to data warehouses.

Sometimes we need create new partitions for loading data to a table. When we do this we use this stored procedure ;

CREATE PROCEDURE sp_add_partition 
(p_table_owner varchar2,p_table_name varchar2,p_start_date number, p_end_date number,p_partition_name VARCHAR2 default NULL) 
  IS
  
    v_high_value number;
    v_YEAR_MONTH varchar2(6);
    v_table_name varchar(100);
	--
    CURSOR c1
    IS   
	select 
      DISTINCT  to_char(DAY_ID,'YYYYMM')          YEAR_MONTH ,
      to_number(to_char(DAY_ID,'YYYYMM') ||'99')  high_value
  from    DIM_CALENDAR_DATES  
		where to_number ( to_char(DAY_ID ,'YYYYMMDD' ))    between p_start_date and p_end_date
		order by 1;
  BEGIN
  --
    OPEN c1;
      LOOP
        FETCH c1 INTO v_YEAR_MONTH , v_high_value;
        EXIT WHEN c1%NOTFOUND;
          --
          execute immediate ' select max(Table_Name) from dba_tables 
                              where owner = UPPER('''||p_table_owner||''') 
                              and table_name = UPPER('''||p_table_name||''')' into v_table_name;
          BEGIN
            execute immediate 
                'ALTER TABLE '||p_table_owner||'.'||v_table_name||'
                ADD PARTITION '||nvl(p_partition_name,v_table_name)||'_'||v_YEAR_MONTH||' 
                VALUES LESS THAN ('||v_high_value||')
                STORAGE    (
                INITIAL          64K
                NEXT             50M
                MAXSIZE          UNLIMITED
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
               )
                NOLOGGING';
            --
          
          END;
      END LOOP;
    --
    CLOSE c1;
  --
  END  ;
  
  

For get more information about partitioning you can check links below.

https://www.oracle.com/database/technologies/partitioning.html

https://www.oracle.com/technetwork/database/options/partitioning/partitioning-wp-12c-1896137.pdf

PL-SQL Stored Procedure : DROP_IF_EXISTS

Hi ,

While developing ETL processes with PL-SQL we use “drop-create” for loading data to temp tables. Before creating temp table, we need to drop if it is exists in the database. If you try directly drop the table, and the table did not exists yet , then you will get “ORA-00942table or view does not exist” error.

Here is a PL-SQL procedure to drop it so you can create it without any error;

CREATE OR REPLACE PROCEDURE TECHBROS.DROP_IF_EXISTS (p_table_name IN VARCHAR2) IS
v_control NUMBER(1);
BEGIN
SELECT COUNT(1) 
  INTO v_control 
  FROM user_tables a
  WHERE UPPER(a.table_name) = UPPER(p_table_name); 
IF v_control >= 1 THEN 
  EXECUTE IMMEDIATE('DROP TABLE '||p_table_name); 
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END drop_if_exists; 

For run the procedure;

call TECHBROS.DROP_IF_EXISTS('TEMP_TABLE_TO_DELETE');

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