Home » SQL & PL/SQL » SQL & PL/SQL » Partition Creation based on other table values (Oracle 11G Unix)
Partition Creation based on other table values [message #577103] Tue, 12 February 2013 07:07 Go to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Hi

i am trying to create Procedure which will create the partitions based on the other table date values one per each day.

CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2)
    AS
    V_PART_NM  VARCHAR2(20);
    V_PART_CNT NUMBER;
    V_DATE DATE;
    V_SCHEMA_NAME VARCHAR(15);
    V_TABLE_NAME VARCHAR2(30);
    V_PRT_NM    VARCHAR2(30);
    V_PRT_PNT    VARCHAR2(10);
    V_SQL VARCHAR2(200);
    V_PART_DATE_TABLE VARCHAR2(30);
    CURSOR C_DATE
    IS
    SELECT TRUNC(PART_DATE) FROM ERS_SRC.RIO_PART_DATE;
BEGIN
     V_TABLE_NAME :=TABLE_NAME;
       V_SCHEMA_NAME := SCHEMA_NAME;
       V_PART_DATE_TABLE := PART_DATE_TABLE;

    --EXECUTE IMMEDIATE 'SELECT MAX(TRUNC(TRANSACTION_END_TIME))  FROM ' || V_SCHEMA_NAME ||'.' || V_TABLE_NAME INTO V_DATE;
    --EXECUTE IMMEDIATE 'SELECT MAX(TRUNC(DATE_LOADED))  FROM ' || V_SCHEMA_NAME ||'.' || V_TABLE_NAME INTO V_DATE;
 OPEN C_DATE;
 LOOP
  FETCH C_DATE INTO V_DATE;
     IF C_DATE%NOTFOUND THEN
          EXIT;
     END IF;
  
    IF V_TABLE_NAME LIKE '%RIO%'  THEN 
       IF V_SCHEMA_NAME = 'ERS_SRC' THEN
         V_PART_NM := 'RIO_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    V_PRT_NM := 'RIO_31123000';
    
    ELSIF V_SCHEMA_NAME = 'ERS_STAGE' THEN
      V_PART_NM := 'RIO_STG_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    V_PRT_NM := 'RIO_STG_31123000';
    ELSIF V_SCHEMA_NAME = 'ERS_TARGET' THEN
       /*IF V_TABLE_NAME = 'ERS_RIO_HR_AGG' THEN
      V_PART_NM := 'RIO_TGT_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    V_PRT_NM := 'RIO_TGT_31123000';
    ELSIF V_TABLE_NAME = 'ERS_RIO_DAILY_EXT' THEN
    V_PART_NM := 'RIO_TGT_EXT_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    V_PRT_NM := 'RIO_TGT_EXT_31123000';
    ELSIF V_TABLE_NAME = 'ERS_RIO_FACT' THEN
      V_PART_NM := 'RIO_TGT_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    V_PRT_NM := 'RIO_TGT_31123000';
    END IF;*/
    select SUBSTR(PARTITION_NAME,1,LENGTH(PARTITION_NAME) - 8)|| TO_CHAR(V_DATE ,'DDMMYYYY') into V_PART_NM   from all_tab_partitions
    where table_name = V_TABLE_NAME AND ROWNUM< 2;
    
    select SUBSTR(PARTITION_NAME,1,LENGTH(PARTITION_NAME) - 8)|| '31123000' into V_PRT_NM  from all_tab_partitions
    where table_name = V_TABLE_NAME AND ROWNUM< 2;
    
    END IF;
    ELSIF V_TABLE_NAME LIKE '%CER%' THEN
      V_PART_NM := 'CER_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    ELSIF V_TABLE_NAME LIKE '%PAC%' THEN
      V_PART_NM := 'CER_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    END IF;
    DBMS_OUTPUT.PUT_LINE(V_PART_NM);
    
    SELECT COUNT(*) INTO V_PART_CNT FROM ALL_TABLES A,ALL_TAB_PARTITIONS B where A.OWNER NOT IN ('SYS','SYSTEM') AND
    A.TABLE_NAME = B.TABLE_NAME
    AND A.PARTITIONED = 'YES'
    AND B.TABLE_NAME = V_TABLE_NAME 
    AND B.TABLE_OWNER = V_SCHEMA_NAME 
    AND B.PARTITION_NAME = V_PART_NM;
    
    DBMS_OUTPUT.PUT_LINE('.....SPLITIING PARTITION..........');
    IF V_PART_CNT = 0 THEN
       V_SQL := 'ALTER TABLE ' || SCHEMA_NAME ||'.' ||V_TABLE_NAME ||' SPLIT PARTITION ' || V_PRT_NM ||' AT (TO_DATE(' || '''' || TO_CHAR(V_DATE +1  ,'DD/MM/YYYY') ||''''   
     || ',''DD/MM/YYYY''))' || ' INTO ( PARTITION '|| V_PART_NM || ' ,PARTITION ' ||V_PRT_NM ||') PARALLEL 3' ;
 
      DBMS_OUTPUT.PUT_LINE(V_SQL);
      EXECUTE IMMEDIATE V_SQL;

     --EXECUTE IMMEDIATE 'ANALYZE TABLE ' || SCHEMA_NAME ||'.' ||V_TABLE_NAME ||' COMPUTE STATISTICS';
     --DBMS_STATS.GATHER_TABLE_STATS (SCHEMA_NAME,V_TABLE_NAME, DEGREE => 3);
   
     --DBMS_OUTPUT.PUT_LINE('ANALYZE TABLE ' || SCHEMA_NAME ||'.' ||V_TABLE_NAME ||' COMPUTE STATISTICS');
        -- DBMS_OUTPUT.PUT_LINE('.....ANALYZING TABLE..........');
    ELSE
    DBMS_OUTPUT.PUT_LINE('.....PARTITION ALREAY EXIST..........');
    --DBMS_STATS.GATHER_TABLE_STATS (SCHEMA_NAME,V_TABLE_NAME, DEGREE => 3);
    
    END IF;
END LOOP;   
   EXCEPTION
   WHEN OTHERS THEN
   NULL;

   END PARTITION_TEST;
/


It is not creating the partitions and even not giving any errors.
Re: Partition Creation based on other table values [message #577105 is a reply to message #577103] Tue, 12 February 2013 07:14 Go to previous messageGo to next message
flyboy
Messages: 1779
Registered: November 2006
Senior Member
Hi,
nagaraju.ch wrote on Tue, 12 February 2013 14:07
It is not creating the partitions and even not giving any errors.

Of course it is not throwing any error as you explicitly told to ignore all of them in WHEN OTHERS THEN NULL exception hider.
Remove it forever, then you will be able to find out what is wrong.
Do not forget to read e.g. this article: http://www.orafaq.com/wiki/WHEN_OTHERS
Re: Partition Creation based on other table values [message #577117 is a reply to message #577105] Tue, 12 February 2013 08:50 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Thanks flyboy,

I have one more doubt can we use dynamic sql in while fetching the rows?

as below code

    CURSOR C_DATE
    IS
    EXECUTE IMMEDIATE 'SELECT TRUNC(PART_DATE) FROM ' ||PARAMETER_SCHEMA||'.'||PARAMETER_TABLE||
Re: Partition Creation based on other table values [message #577119 is a reply to message #577117] Tue, 12 February 2013 09:00 Go to previous messageGo to next message
BlackSwan
Messages: 23060
Registered: January 2009
Senior Member
below is REALLY, REALLY, Really, really bad implementation!
END LOOP;   
   EXCEPTION
   WHEN OTHERS THEN
   NULL;

It should be 100% completely eliminated, deleted & removed and NEVER used again anywhere.
Re: Partition Creation based on other table values [message #577124 is a reply to message #577117] Tue, 12 February 2013 09:58 Go to previous message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have one more doubt can we use dynamic sql in while fetching the rows?


PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Previous Topic: to get common records
Next Topic: NVL2 problem when using forms
Goto Forum:
  


Current Time: Thu Nov 27 18:23:51 CST 2014

Total time taken to generate the page: 0.13300 seconds