Insert/delete Partition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Insert/delete Partition

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218

    Question Insert/delete Partition

    Hi,
    I am searching in google for a pl/sql script that will purge old partitions and insert a new partition based on the date. (ie. keep 24 months, each month insert a new partition and delete the old partition(24),

    I have not found anything in google and thought maybe someone might have a similar script that I can use as a base.

    Your help is greatly appreciated.

    Thanks,

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Assuming from your posting you are resorting to range-partitioning...

    You do not insert a new partition, you usually split the max-value partition.
    You do not delete a partition, you either truncate it or drop it.

    Ora10g might help you creating new partitions as needed but, don't be lazy, you can write the script. Just google "alter table drop partition" and "alter table split partition"
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    Pascal,

    Is it realy so hard to read the Oracle documentation and to write 2 lor 3 lines of code ???

    I gave you the links of all the documents you would need to do it:
    http://www.dbasupport.com/forums/showthread.php?t=55723

  4. #4
    Join Date
    Feb 2007
    Location
    UNDER MOON
    Posts
    44
    this proc drop part based on value.........u can modify with ur req...i dont know wather its helpfull for u or not

    -----CREATE A PARTITION TABLE.

    SQL> ED
    Wrote file afiedt.buf

    1 CREATE TABLE p_history (
    2 prof_history_id NUMBER(10),
    3 person_id NUMBER(10) NOT NULL,
    4 organization_id NUMBER(10) NOT NULL,
    5 record_date DATE NOT NULL,
    6 ph_comments VARCHAR2(2000))
    7 PARTITION BY RANGE (record_date) (
    8 PARTITION YEAR1
    9 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
    10 PARTITION YEAR2
    11 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
    12 PARTITION YEAR3
    13 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY')),
    14 PARTITION YEAR4
    15* VALUES LESS THAN (MAXVALUE))
    16 /

    Table created.


    ----SEE THE PARTITION CREATED.

    SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME
    2 FROM DBA_TAB_PARTITIONS
    3 WHERE TABLE_NAME='P_HISTORY';

    TABLE_OWNER TABLE_NAME PARTITION_NAME
    ------------------------------ ------------------------------ ------------------------------
    SCOTT P_HISTORY YEAR1
    SCOTT P_HISTORY YEAR2
    SCOTT P_HISTORY YEAR3
    SCOTT P_HISTORY YEAR4


    ----CREATE A PROCEDURE FOR DROP PARTITION.

    SQL> ED
    Wrote file afiedt.buf

    1 CREATE OR REPLACE PROCEDURE DROP_PART(P_OWNER VARCHAR2, P_TABLE VARCHAR2, P_PARTITION VARCHAR2)
    2 IS
    3 V_STAT VARCHAR2(300);
    4 BEGIN
    5 V_STAT := 'ALTER TABLE '||P_OWNER||'.'||P_TABLE||' DROP PARTITION '||P_PARTITION||' UPDATE GLOBAL INDEXES';
    6 EXECUTE IMMEDIATE V_STAT;
    7 DBMS_OUTPUT.PUT_LINE('Owner:'||P_OWNER||' Table:'||P_TABLE||' Partition:'||P_PARTITION||' has droped.');
    8 EXCEPTION
    9 WHEN NO_DATA_FOUND THEN
    10 DBMS_OUTPUT.PUT_LINE('Partition is not found.');
    11 WHEN OTHERS THEN
    12 DBMS_OUTPUT.PUT_LINE(SQLERRM);
    13* END;

    Procedure created.

    ----RUN THE PROCEDURE.

    SQL> SET SERVEROUTPUT ON
    SQL> EXEC DROP_PART('SCOTT', 'P_HISTORY', 'YEAR2');
    Owner:SCOTT Table:P_HISTORY Partition:YEAR2 has droped.

    PL/SQL procedure successfully completed.


    ---SEE THE RESULT.

    SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME
    2 FROM DBA_TAB_PARTITIONS
    3 WHERE TABLE_NAME='P_HISTORY';

    TABLE_OWNER TABLE_NAME PARTITION_NAME
    ------------------------------ ------------------------------ ------------------------------
    SCOTT P_HISTORY YEAR1
    SCOTT P_HISTORY YEAR3
    SCOTT P_HISTORY YEAR4


    ---PARTITION (YEAR 2) IS DROPED.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by liakat
    this proc drop part based on value
    No it doesn't. It drops partition based on partition_name.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218
    Hi.
    "Is it realy so hard to read the Oracle documentation and to write 2 lor 3 lines of code ???" Mike9, I had no problem creating the partitioned table, that was not the issue for me. It was writing the pl/sql to loop and remove the oldest partition.

    Liakat. Thanks for the terrific example. I will use it as my baseline. Much appreciated.

    Cheers.

  7. #7
    Join Date
    Feb 2007
    Location
    UNDER MOON
    Posts
    44
    Quote Originally Posted by PAVB
    No it doesn't. It drops partition based on partition_name.

    SORRY, YES It drops partition based on partition_name.
    I thing i need more carefull.

    thanks prov

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width