System partitioning provides the ability to create a single table that has many physical partitions. System partitions doesn't have partition keys like other partitioning schemes.

REATE TABLE system_partitioned_tab (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY SYSTEM
(
  PARTITION part_1,
  PARTITION part_2
);

The partition must be explicitly defined in all insert statements or an error is produced.

INSERT INTO system_partitioned_tab VALUES (1, 'ONE', 'One', SYSDATE);
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by
the System method

SQL>

The PARTITION clause is used to define which partition the row should be placed in.

INSERT INTO system_partitioned_tab PARTITION (part_1) VALUES (1, 'ONE', 'One', SYSDATE);
INSERT INTO system_partitioned_tab PARTITION (part_2) VALUES (2, 'TWO', 'Two', SYSDATE);
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTEM_PARTITIONED_TAB');

COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A10

SELECT table_name, partition_name, high_value, num_rows
FROM   user_tab_partitions
ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE   NUM_ROWS
------------------------- -------------------- ---------- ----------
SYSTEM_PARTITIONED_TAB    PART_1                                   1
SYSTEM_PARTITIONED_TAB    PART_2                                   1

2 rows selected.

SQL>

Notice that the HIGH_VALUE for the partitions is blank.

The PARTITION clause is optional for update and delete statements, but omitting this clause will force all partitions to be scanned, since there is no way perform automatic partition pruning when the database has no control over row placement. When the PARTITION clause is used, you must be sure to perform the operation against the correct partition.

SQL> DELETE FROM system_partitioned_tab PARTITION (part_2) WHERE id = 1;

0 rows deleted.

SQL> UPDATE system_partitioned_tab PARTITION (part_1) SET code = 'TWO' WHERE id = 2;

0 rows deleted.

SQL>

The PARTITION clause can also be used in queries to target specific partitions.

SELECT COUNT(*)
FROM   system_partitioned_tab PARTITION (part_1);

  COUNT(*)
----------
         1

1 row selected.

SQL>

System partitioning gives you many of the advantages of partitioning, but leaves the decision of how the data is partitioned to the application layer.

Conditions and restrictions on system partitioning include:

• If you specify the PARTITION BY SYSTEM clause, but don't define partitions, a single partition is created with the name in the format of "SYS_Pn".
• If you specify PARTITION BY SYSTEM PARTITIONS n clause, the database creates "n" partitions with the name in the format of "SYS_Pn". The range of allowable values for "n" is from 1 to 1024K-1.
• System partitioning is not available for index-organized tables or a table that is part of a cluster.
• System partitioning can play no part in composite partitioning.
• You cannot split a system partition.
• System partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.
• To insert data into a system-partitioned table using an INSERT INTO ... AS subquery statement, you must use partition-extended syntax to specify the partition into which the values returned by the subquery will be inserted.

SELECT * FROM dba_part_tables;
SELECT * FROM dba_part_indexes;

One can list the partitions of a table with:

SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name>';

One can show the partitions of an given index with:

SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name>';

You will need 500GB of free space to do the split of a 500GB partition.  Behind the scenes  we have to build two new segments for the two new partitions; that's the only way for us to physically separate the data belonging to the new target partitions.   If you don't have 500GB available,  here is an alternative you can do:

This process is going to take awhile to run, depending on how much data is involved.  We assume your table is called PRICE.   You should first test this on  a smaller table.

1. Do a Create Table As Select (CTAS) to preserve the smaller target partition data as separate table.

Create table PRICE_2 NOLOGGING PARALLEL(DEGREE 2) as select /*+ PARALLEL(A, 2) */ * from PRICE PARTITION (PRICE_MAXVALUE) where month_year between 200501 and 200512;

Check table PRICE_2 to ensure all the correct rows are there.

2. Then do a DELETE from the same data from the large original partition.

Delete from PRICE where month_year between 200501 and 200512;

3. Do a split of the original partition. This will be a fast split now since we figure out that there is no data for one of the new target partitions (we just deleted that data).

alter table PRICE split partition PRICE_MAXVALUE at (20090801) into (partition PRICE_2009_07, partition PRICE_MAXVALUE) parallel 2;

4. Do an exchange with the empty partitions and the standalone table created in step 1.

alter table PRICE exchange partition PRICE_2009_07 with table PRICE_2;

5.  Repeat steps 1-4 for each additional partition you want to create.

6. You can run this after each exchange. This 'shrink ' clause is valid only for segments in tablespaces with automatic segment management.

alter table PRICE modify partition PRICE_MAXVALUE shrink space;

(New Table Partitioning Methods Introduced in Oracle Database 11g)

ALTER TABLE table_name SET INTERVAL (interval value);

ALTER TABLE month_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));
ALTER TABLE day_part SET INTERVALnumtodsinterval(1,'day'));

To change an interval-partitioned table back to a range-partitioned table. Use SET INTERVAL () to disable interval partitioning. 

The database converts existing interval partitions to range partitions, using the higher boundaries of created interval partitions as upper boundaries for the range partitions to be created.

You can also use SET INTERVAL(<numtodsinterval expression>) to change the interval of an existing interval-partitioned table. The database converts existing interval partitions to range partitions, and 
then automatically creates partitions of the specified numeric range or datetime interval as needed for data beyond the highest value allowed for the last range partition.

Restrictions on  interval partitioning

There are a few restrictions on interval partitioning that must be taken into consideration before deciding if it is appropriate for the business requirement:

• Cannot be used for index organized tables

• Must use only one partitioning key column and it must be a DATE or NUMBER

• Cannot create domain indexes on interval partitioned tables

• Are not supported at the sub-partition level

This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable.  If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.

In Oracle/PLSQL, the numtodsinterval function converts a number to an INTERVAL DAY TO SECOND literal.

The syntax for the numtodsinterval function is:

numtodsinterval( number, expression )

number is the number to convert to an interval.

expression is the unit. It must be one of the following values: DAY, HOUR, MINUTE, or SECOND.

Applies To: Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:
numtodsinterval(150, 'DAY') would return '+000000150'
numtodsinterval(1500, 'HOUR') would return '+000000062'
numtodsinterval(15000, 'MINUTE') would return '+000000010'

In Oracle/PLSQL, the numtoyminterval function converts a number to an INTERVAL YEAR TO MONTH literal.

The syntax for the numtoyminterval function is:


numtoyminterval( number, expression )

number is the number to convert to an interval.

expression is the unit. It must be one of the following values: YEAR or MONTH.

Applies To:
Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:
numtoyminterval(100000000, 'MONTH') would return '+0083'
numtoyminterval(100000, 'YEAR') would return '+0001'

If you ever want to query a partitioned table, and want to reference a specific partition without using the ‘where’ clause (and do not know the partition name), you could use the following syntax:

Select * from dbceo.part_table partition for (to_date(’08-SEP-2010’,’DD-MON-YYYY’)..