11g new feature - System Partitioning

Starting with 11G there is very useful feature - system partitioning.
If you want to control data partitioning to be done by application, not database itself - then you should use this feature.

SQL> create table myt1(x int, y int)
2  partition by system
3  (
4  partition p1,
5  partition p2,
6  partition p3
7  )
8  /

Table created.

You can check the type of partitioning by cheking in dba_part_tables:

SQL> select partitioning_type from dba_part_tables where table_name = 'MYT1';

PARTITION
---------
SYSTEM
Now try to insert a record:

SQL> insert into myt1 values(1, 2);
insert into myt1 values(1, 2)
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method

The partitions bounds are not known so the application must provide that information by using the partition-aware syntax while inserting data:

SQL> insert into myt1 partition(p1) values(1, 2);

1 row created.

SQL> insert into myt1 partition(p2) values(2, 3);

1 row created.

SQL> insert into myt1 partition(p2) values(1, 4);

1 row created.

SQL> select * from myt1;

X          Y
---------- ----------
1          2
2          3
1          4

SQL> select * from myt1 partition(p1);

X          Y
---------- ----------
1          2

SQL> select * from myt1 partition(p2);

X          Y
---------- ----------
2          3
1          4

While deleting, you have to provide the partition-aware syntax, if you want to delete that record with specific values (in our example it is x=1) in a specific partition. Otherwise, you do not need to specify partition-aware syntax:
SQL> delete from myt1 partition(p2) where x=1;

1 row deleted.

SQL> select * from myt1 partition(p1);

X          Y
---------- ----------
1          2

SQL> select * from myt1 partition(p2);

X          Y
---------- ----------
2          3

SQL> select * from myt1;

X          Y
---------- ----------
1          2
2          3

SQL> delete from myt1 where x=1;

1 row deleted.

SQL> select * from myt1;

X          Y
---------- ----------
2          3