Shulou(Shulou.com)06/01 Report--

Overview

Range Range partition: data is divided into a fixed range as a partition based on the value of a field in the table.

Example:

Create a sales department sales data inventory table:

Create table sales_list

(order_id number (5)

Sales_name varchar2 (20)

Order_amount number (10)

Order_date date

)

Partition by range (order_date)

(partition sales_201401 values less than (to_date ('2014-02-01)

Partition sales_201402 values less than (to_date ('2014-03-01)

Partition sales_201403 values less than (to_date ('2014-04-01)

Partition sales_201404 values less than (to_date ('2014-05-01)

Partition sales_201405 values less than (to_date ('2014-06-01)

Partition sales_201406 values less than (to_date ('2014-07-01)

Partition sales_201407 values less than (to_date ('2014-08-01)

Partition sales_201408 values less than (to_date ('2014-09-01)

Partition sales_201409 values less than (to_date ('2014-10-01)

Partition sales_201410 values less than (to_date ('2014-11-01)

Partition sales_201411 values less than (to_date ('2014-12-01)

Partition sales_201412 values less than (to_date ('2015-01-01-01-01))

Partition sales_201501 values less than (to_date ('2015-02-01)

Partition sales_201502 values less than (to_date ('2015-03-01)

);

Created 14 partitions, 14 segments

SQL > col partition_name format A20

SQL > col segment_name format A20

SQL > select segment_name,partition_name from user_segments where segment_name='SALES_LIST'

SEGMENT_NAME PARTITION_NAME

--

SALES_LIST SALES_201401

SALES_LIST SALES_201402

SALES_LIST SALES_201403

SALES_LIST SALES_201404

SALES_LIST SALES_201405

SALES_LIST SALES_201406

SALES_LIST SALES_201407

SALES_LIST SALES_201408

SALES_LIST SALES_201409

SALES_LIST SALES_201410

SALES_LIST SALES_201411

SALES_LIST SALES_201412

SALES_LIST SALES_201501

SALES_LIST SALES_201502

14 rows selected.

The creation process is used to delete old partitions and add new ones:

CREATE OR REPLACE PROCEDURE drop_add_partition AS

V_part_name VARCHAR2 (100)

V_next_name VARCHAR2 (100)

V_over_time NUMBER

V_string VARCHAR2 (10)

V_date DATE

BEGIN

SELECT MIN (partition_name)

INTO v_part_name

FROM user_tab_partitions

WHERE table_name = 'SALES_LIST';-- find the current oldest partition

SELECT MAX (partition_name)

INTO v_next_name

FROM user_tab_partitions

WHERE table_name = 'SALES_LIST';-- find the latest partition currently

SELECT substr (to_char (add_months (to_date) (substr (v_next_name, 7,6))

'yyyy-mm')

1),

'yyyymmdd')

one,

6)

INTO v_next_name

FROM dual;-concatenate the name string of the next new partition

SELECT round (months_between (SYSDATE)

To_date (substr (v_part_name, 7,6), 'yyyy-mm')

INTO v_over_time

FROM dual

-- calculate the number of months between the current time and the oldest partition

V_string: = to_char (add_months (to_date (v_next_name, 'yyyy-mm'), 1)

'yyyy-mm')

V_date: = to_date (substr (v_string, 1,4) | |'-'| |

Substr (v_string, 6,2) | |'- 01'

'yyyy-mm-dd')

Stitching a new partition requires a specified time-to-point string at the time of creation

IF v_over_time > 12

THEN

EXECUTE IMMEDIATE 'alter table sales_list drop partition' | |

Delete the old partition

EXECUTE IMMEDIATE 'alter table sales_list add partition SALES_' | |

REPLACE (v_next_name,'-',') | |

'values less than (''| | v_date | |'')';-add a new partition

END IF

EXCEPTION

WHEN OTHERS THEN

-- exception handling

Dbms_output.put_line (to_char (SQLCODE))

Dbms_output.put_line (SQLERRM)

END

SQL > set serveroutput on

SQL > exec drop_add_partition

PL/SQL procedure successfully completed.

SQL > select segment_name,partition_name from user_segments where segment_name='SALES_LIST'

SEGMENT_NAME PARTITION_NAME

--

SALES_LIST SALES_201402

SALES_LIST SALES_201403

SALES_LIST SALES_201404

SALES_LIST SALES_201405

SALES_LIST SALES_201406

SALES_LIST SALES_201407

SALES_LIST SALES_201408

SALES_LIST SALES_201503

SALES_LIST SALES_201409

SALES_LIST SALES_201410

SALES_LIST SALES_201411

SALES_LIST SALES_201412

SALES_LIST SALES_201501

SALES_LIST SALES_201502

14 rows selected.

Create a background job:

Declare

Job number

Begin

Dbms_job.submit (job,'drop_add_partition;',sysdate,'sysdate+1');-- run once a day, and execute once when the job is created

Commit

End

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.