Oracle partitioning by time and auto partitioning

(1)

---Create a partitioned table by time

CREATE TABLE Temp_workdate (
Workdate Date
)
PARTITION by RANGE (workdate) (
Partition p_20171020 values less than (To_date (' 2017-10-20 23:59:59 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ‘))
);

--The insertion time is less than the partition time data will not be error

Insert INTO Temp_workdate Select To_date (to_date (' 2017-09-11 23:59:59 ', ' syyyy-mm-dd HH24:MI:SS ')] from dual;

--The insertion time is larger than the partition time error data

Insert INTO Temp_workdate Select To_date (to_date (' 2017-12-11 23:59:59 ', ' syyyy-mm-dd HH24:MI:SS ')] from dual;

1. Because I use stored procedures, you can control data-oriented and partition generation with code;

2. The table name itself controls the generation rules, and later gets simple (at least my side is)

(2)

--Automatically partitioned by the database

CREATE TABLE Temp_workdate (
Workdate Date
)
PARTITION by RANGE (workdate)
INTERVAL (Numtodsinterval (1, ' Day ')) (
Partition p_20171020 values less than (To_date (' 2017-10-20 23:59:59 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ‘))
);

--Insert two data

Insert INTO Temp_workdate Select To_date (to_date (' 2017-12-11 23:59:59 ', ' syyyy-mm-dd HH24:MI:SS ')] from dual;
Insert INTO Temp_workdate Select To_date (to_date (' 2017-09-11 23:59:59 ', ' syyyy-mm-dd HH24:MI:SS ')] from dual;

--Success

--automatic partitioning, but the partition name is not well controlled and is not defined by itself. Need to take a loop to get it later

Oracle partitioning by time and auto partitioning