السلام عليكم ورجمة الله

i'm working with oracle apps 11i with db 8i

i have tables with rows inserted since 2003

i want to use a partitioning method to divide the tables

what are the statements i use to alter this tables in order to partition them ?


thanks

أخي الكريم

معظم ما يتعلق بالموضوع وبداخله كيفية عمل partitioning for existing table

موفق

Oracle partitions using DML:

The following DML statements contain an optional partition specification for non-remote partitioned tables:

INSERT
UPDATE
DELETE
LOCK TABLE
SELECT

For example:

SELECT * FROM schema.table PARTITION(part_name);

This syntax provides a simple way of viewing individual partitions as tables: A view can be created which selects from just one partition using the partition-extended table name, and this view can be used in lieu of a table.

With such views you can also build partition-level access control mechanisms by granting (revoking) privileges on these views to (from) other users or roles.

The use of partition-extended table names has the following restrictions:

* A partition-extended table name cannot refer to a remote schema object.
* The partition-extended table name syntax is not supported by PL/SQL.
* A partition extension must be specified with a base table. No synonyms, views, or any other schema objects are allowed.

In order to provide partition independence for DDL and utility operations, Oracle supports DML partition locks.

Partition independence allows you to perform DDL and utility operations on selected partitions without disturbing activities on other partitions.

The purpose of a partition lock is to protect the data in an individual partition while multiple users are accessing that partition or other partitions in the table concurrently.

Managing Oracle partitions:

Create a partitioned table:

Creating Oracle partitions is very similar to creating a table or index. You must use the CREATE TABLE statement with the PARTITION CLAUSE.

The first step to create a partitioned table would be to identify the column(s) to partition on and the range of values which go to each partition. Then you determine the tablespaces where each partition should go.

Here is a script to create a simple partitioned table:

CREATE TABLE AA_GENERAL_ATTENDANCE
(GL_MARKS_MONTH NUMBER (4),
GL_BATCH VARCHAR2(4),
GL_JIB VARCHAR2(1),
... ... ... ... GLR_OVER_UNDER_IND VARCHAR2(1))
PCTFREE 0 PCTUSED 40 INITRANS 1
STORAGE(INITIAL 250M NEXT 10M MINEXTENTS 1
MAXEXTENTS 1000 PCTINCREASE 0 )
PARTITION BY RANGE (GL_MARKS_MONTH)
(PARTITION SSTN7912 VALUES LESS THAN (8000)
TABLESPACE SSTN 7912
STORAGE (INITIAL 100M NEXT 10M PCTINCREASE 0)
, PARTITION SSTN 8012 VALUES LESS THAN (8100)
TABLESPACE SSTN 8012,
PARTITION SSTN 8112 VALUES LESS THAN (8200)
TABLESPACE SSTN 8112,
PARTITION SSTN 8212 VALUES LESS THAN (8300)
TABLESPACE SSTN 8212,
... ... ... ...
PARTITION SSTN 9712 VALUES LESS THAN (9800)
TABLESPACE SSTN 9712,
PARTITION SSTN 9801 VALUES LESS THAN (MAXVALUE)
TABLESPACE SSTN 9801
STORAGE (INITIAL 50M NEXT 5M PCTINCREASE 0)
);

Moving Oracle partitions:

You can use the MOVE PARTITION clause to move a partition. For example, a DBA wishes to move the most active partition to a tablespace that resides on its own disk (in order to balance I/O).

The DBA can issue the following statement:

ALTER TABLE aaa MOVE PARTITION bbb
TABLESPACE rrr NOLOGGING;

This statement always drops the partition's old segment and creates a new segment, even if you don't specify a new tablespace.

When the partition you are moving contains data, MOVE PARTITION marks the matching partition in each local index, and all global index partitions as unusable. You must rebuild these index partitions after issuing MOVE PARTITION.

Adding Oracle partitions:

You can use the ALTER TABLE ADD PARTITION statement to add a new partition to the "high" end.

If you wish to add a partition at the beginning or in the middle of a table, or if the partition bound on the highest partition is MAXVALUE, you should instead use the SPLIT PARTITION statement.

When the partition bound on the highest partition is anything other than MAXVALUE, you can add a partition using the ALTER TABLE ADD PARTITION statement.

ALTER TABLE edu
ADD PARTITION jan99 VALUES LESS THAN ( '990201' )
TABLESPACE tsjan99;

When there are local indexes defined on the table and you issue the ALTER TABLE ... ADD PARTITION statement, a matching partition is also added to each local index.

Since Oracle assigns names and default physical storage attributes to the new index partitions, you may wish to rename or alter them after the ADD operation is complete.

Dropping Oracle partitions:

You can use the ALTER TABLE DROP PARTITION statement to drop Oracle partitions.

If there are local indexes defined for the table, ALTER TABLE DROP PARTITION also drops the matching partition from each local index.

You cannot explicitly drop a partition for a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

If, however, the partition contains data and global indexes, and you leave the global indexes in place during the ALTER TABLE DROP PARTITION statement which marks all global index partitions unusable, you must rebuild them afterwards.

Truncating Partitioned Tables:

You can use the ALTER TABLE TRUNCATE PARTITION statement to remove all rows from a table partition with or without reclaiming space.

If there are local indexes defined for this table, ALTER TABLE TRUNCATE PARTITION also truncates the matching partition from each local index.

Splitting Oracle partitions:

You can split a table partition by issuing the ALTER TABLE SPLIT PARTITION statement.

If there are local indexes defined on the table, this statement also splits the matching partition in each local index.

Because Oracle assigns system-generated names and default storage attributes to the new index partitions, you may wish to rename or alter these index partitions after splitting them.

If the partition you are splitting contains data, the ALTER TABLE SPLIT PARTITION statement marks the matching partitions (there are two) in each local index, as well as all global index partitions, as unusable.

You must rebuild these index partitions after issuing the ALTER TABLE SPLIT PARTITION statement.

Exchanging Table Partitions:

You can convert a partition into a non-partitioned table, and a table into a partition of a partitioned table by exchanging their data and index segments.

Exchanging table partitions is most useful when you have an application using non-partitioned tables which you want to convert to partitions of a partitioned table.

Converting a Partition View into a Partitioned Table:

This part describes how to convert a partition view into a partitioned table. The partition view is defined as follows:

CREATE VIEW students
SELECT * FROM students_jan95
UNION ALL
SELECT * FROM students_feb95
UNION ALL
...
SELECT * FROM students_dec95;

Initially, only the two most recent partitions, students_NOV95 and students_DEC95, will be migrated from the view to the table by creating the partition table.

Each partition gets a temporary segment of 2 blocks (as a placeholder).

CREATE TABLE accounts_new (...)
TABLESPACE ts_temp STORAGE (INITIAL 2)
PARTITION BY RANGE (opening_date)
(PARTITION jan95 VALUES LESS THAN ('950201'),
...
PARTITION dec95 VALUES LESS THAN ('960101'));

Use the EXCHANGE command to migrate the tables to the corresponding partitions.

ALTER TABLE students_new
EXCHANGE PARTITION nov95
WITH TABLE students_95
WITH VALIDATION;

ALTER TABLE students_new
EXCHANGE PARTITION dec95 WITH TABLE students_dec95
WITH VALIDATION;

So now the placeholder data segments associated with the NOV95 and DEC95 partitions have been exchanged with the data segments associated with the students_NOV95 and students_DEC95 tables.

Redefine the students view:

CREATE OR REPLACE VIEW accounts
SELECT * FROM students_jan95
UNION ALL
SELECT * FROM students_feb_95
UNION ALL
...
UNION ALL
SELECT * FROM students_new PARTITION (nov95)
UNION ALL
SELECT * FROM students_new PARTITION (dec95);

Drop the students_NOV95 and students_DEC95 tables, which own the placeholder segments that were originally attached to the NOV95 and DEC95 partitions.

After all the tables in the UNIONALL view are converted into partitions, drop the view and rename the partitioned table as the view.

DROP VIEW students;
RENAME students_new TO accounts;br>

Rebuilding Index Partitions:

Some operations, such as ALTER TABLE DROP PARTITION, mark all Oracle partitions of a global index unusable. You can rebuild global index partitions in two ways:

1. Rebuild each partition by issuing the ALTER INDEX REBUILD PARTITION statement (you can run the rebuilds concurrently).
2. Drop the index and re-create it (probably the easiest method).

Merging Oracle partitions:

Partition-level Export and Import provide a way to merge Oracle partitions in the same table, even though SQL does not explicitly support merging partitions.

A DBA can use partition-level Import to merge a table partition into the next highest partition on the same table. To merge partitions, do an export of the partition you would like to merge, delete the partition and do an import

أشكرك جدا

جعله الله فى ميزان حسناتك

أخي عصام

كنت أود أن تستنتج الاجابة ولا أدري ان كنت عرفتها بعد الشرح المبسط

عموما

لا يمكننا اضافة partition ل non-partitioned table ولكن يمكننا اضافة new partition to a partitioned table

ولاضافة partition ل non-partitioned table يجب حذف ال non-partitioned table وعمله مجددا ك partitioned table

طبعا يمكنك اعادة تسمية القديم وعمل جدول جديد partitioned وتأخذ البيانات من القديم للجديد ثم حذف القديم تلاشيا لادخال البيانات من جديد

مثال
create table sales (year number(4),

product varchar2(10),

amt number(10,2))

partition by range (year)

partition p1 values less than (1992) tablespace u1,

partition p2 values less than (1993) tablespace u2,

partition p3 values less than (1994) tablespace u3,

partition p4 values less than (1995) tablespace u4,

partition p5 values less than (MAXVALUE) tablespace u5;

كما تري تم عمل الجدول ب 5 partitions

Partition p1 will contain rows of year 1991 and
it will be stored in tablespace u1. Partition p2 will contain rows of year 1992 and it will be stored in tablespace u2.
Similarly p3 and p4.



In the above example if you don’t specify the partition p4 with values less than MAVALUE, then you will not be
able to insert any row with year above 1994.



Although not required, you can place partitions in different tablespaces. If you place partitions in different tablespaces
then you can isolate problems due to failures as only a particular partition will not be available and rest of the
partitions will still be available.


في المثال تم عمل ال partition باستخدام range



الانوزاع هي



*

Range Partitioning
*

Hash Partitioning
*

List Partitioning
*

Composite Partitioning


Range Partitioning



This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed;
for example, value of year. Performance is best when the data evenly distributes across the range


Hash partitioning



Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for
performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a
specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key



The following example shows how to create a hash partition table.

The following example creates a hash-partitioned table. The partitioning column is partno, four partitions are created
and assigned system generated names, and they are placed in four named tablespaces (tab1,tab2, ...).



CREATE TABLE products

(partno NUMBER,

description VARCHAR2 (60))

PARTITION BY HASH (partno)

PARTITIONS 4

STORE IN (tab1, tab2, tab3, tab4);





List Partitioning



Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete
values for the partitioning column in the description for each partition. This is different from range partitioning, where a
range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to
partition mapping.



List partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally



The following example creates a table with list partitioning



Create table customers (custcode number(5),

Name varchar2(20),

Addr varchar2(10,2),

City varchar2(20),

Bal number(10,2))

Partition by list (city),

Partition north_India values (‘DELHI’,’CHANDIGARH’),

Partition east_India values (‘KOLKOTA’,’PATNA’),

Partition south_India values (‘HYDERABAD’,’BANGALORE’,

’CHENNAI’),

Partition west India values (‘BOMBAY’,’GOA’);



If a row is inserted in the above table then oracle maps the value of city column and whichever partition list matches the
city column the row is stored in that partition.





COMPOSITE PARTITONING

Composite partitioning partitions data using the range method, and within each partition, subpartitions it using
the hash method. Composite partitions are ideal for both historical data and striping, and provide improved
manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.

When creating composite partitions, you specify the following:

* Partitioning method: range
* Partitioning column(s)
* Partition descriptions identifying partition bounds
* Subpartitioning method: hash
* Subpartitioning column(s)
* Number of subpartitions for each partition or descriptions of subpartitions

The following statement creates a composite-partitioned table. In this example, three range partitions are created, each
containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the
STORE IN clause distributes them across the 4 specified tablespaces (tab1, ...,tab4).

CREATE TABLE PRODUCTS (partno NUMBER,

description VARCHAR(32),

costprice NUMBER)

PARTITION BY RANGE (partno)

SUBPARTITION BY HASH(description)

SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4)

(PARTITION p1 VALUES LESS THAN (100),

PARTITION p2 VALUES LESS THAN (200),

PARTITION p3 VALUES LESS THAN (MAXVALUE));





ALTERING PARTITION TABLES



To add a partition



You can add add a new partition to the "high" end (the point after the last existing partition). To add a partition
at the beginning or in the middle of a table, use the SPLIT PARTITION clause.



For example to add a partition to sales table give the following command.



alter table sales add partition p6 values less than (1996);



To add a partition to a Hash Partition table give the following command.



Alter table products add partition;



Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace.
To add a partition by user define name and in your specified tablespace give the following command.



Alter table products add partition p5 tablespace u5;



To add a partition to a List partition table give the following command.



alter table customers add partition central_India

values (‘BHOPAL’,’NAGPUR’);

Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions
of the table.


Coalescing Partitions

Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of subpartitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.

To coalesce a hash partition give the following statement.

Alter table products coalesce partition;

This reduces by one the number of partitions in the table products.
DROPPING PARTITIONS

To drop a partition from Range Partition table, List Partition or Composite Partition table give the following command.

Alter table sales drop partition p5;

Once you have drop the partition and if you have created a global index on the table. Then you have to rebuild the global index after dropping the partition by giving the following statement.

Alter index sales_ind rebuild;

To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then drop
the partition like this

Delete from sales where year=1994;

Alter table sales drop partition p4;

This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.

Another method of dropping partitions is give the following statement.

ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES;



This causes the global index to be updated at the time the partition is dropped.


Exchanging a Range, Hash, or List Partition

To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, table stocks can be range, hash, or list partitioned.

ALTER TABLE stocks

EXCHANGE PARTITION p3 WITH stock_table_3;



Merging Partitions

Use the ALTER TABLE ... MERGE PARTITIONS statement to merge the contents of two partitions into one partition. Te two original partitions are dropped, as are any corresponding local indexes.

You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.

You can only merged two adjacent partitions, you cannot merge non adjacent partitions.

For example the merge the partition p2 and p3 into one partition p23 give the following statement.

Alter table sales merge partition p2 and p3 into

partition p23;


Modifying Partitions: Adding Values

Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition's value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable.

The following statement adds a new set of cities ('KOCHI', 'MANGALORE') to an existing partition list.

ALTER TABLE customers

MODIFY PARTITION south_india

ADD VALUES ('KOCHI', 'MANGALORE');



Modifying Partitions: Dropping Values

Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then Oracle returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.

You cannot drop all literal values from the value list describing the partition. You must use the ALTER TABLE ... DROP PARTITION statement instead.

The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.

The statement below drops a set of cities (‘KOCHI' and 'MANGALORE') from an existing partition value list.

ALTER TABLE customers

MODIFY PARTITION south_india

DROP VALUES (‘KOCHI’,’MANGALORE’);





SPLITTING PARTITIONS



You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command.



Alter table sales split partition p5 into

(Partition p6 values less than (1996),

Partition p7 values less then (MAXVALUE));



TRUNCATING PARTITON



Truncating a partition will delete all rows from the partition.



To truncate a partition give the following statement



Alter table sales truncate partition p5;



LISTING INFORMATION ABOUT PARTITION TABLES



To see how many partitioned tables are there in your schema give the following statement



Select * from user_part_tables;



To see on partition level partitioning information



Select * from user_tab_partitions;

تحياتي
أحمد