There is different type of Table partition
- Range Partitioning
- Hash Partitioning
- List Partitioning
- Composite Range-Hash Partitioning
- Composite Range-List Partitioning
Create Partitioned Tables Of Different Partition Types
1.Range Partitioning
The following statement creates a table sales_range that is range partitioned on the sales_date field:
CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')));
2.Hash Partitioning
The following statement creates a table sales_hash, which is hash partitioned on the salesman_id field:
CREATE TABLE sales_hash
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
week_no NUMBER(2))
PARTITION BY HASH(salesman_id)
PARTITIONS 4;
3.List Partitioning
The following example creates a list partitioned table grouping states according to their sales regions:
CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT));
4)Composite Range-Hash Partitioning
The following example partitions the table sales_range_hash by range on the column s_saledate to create four partitions that order data by time. Then, within each range partition, the data is further subdivided into 16 subpartitions by hash on the column s_productid:
CREATE TABLE sales_range_hash(
s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
PARTITION BY RANGE (s_saledate)
SUBPARTITION BY HASH (s_productid) SUBPARTITIONS 8
(PARTITION sal99q1 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY')),
PARTITION sal99q2 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),
PARTITION sal99q3 VALUES LESS THAN (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),
PARTITION sal99q4 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));
5.Composite Range-List Partitioning
This statement creates a table halfyeraly_regional_sales that is range partitioned on the txn_date field and list subpartitioned on state.
CREATE TABLE quarterly_regional_sales
(deptno NUMBER, item_no VARCHAR2(20),
txn_date DATE, txn_amount NUMBER, state VARCHAR2(2))
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(
PARTITION q1_1999 VALUES LESS THAN(TO_DATE('1-APR-1999','DD-MON-YYYY'))
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_1999_southcentral VALUES ('NM', 'TX')),
PARTITION q2_1999 VALUES LESS THAN(TO_DATE('1-JUL-1999','DD-MON-YYYY'))
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('NM', 'TX')),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT'),
SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q3_1999_southcentral VALUES ('NM', 'TX')),
PARTITION q4_1999 VALUES LESS THAN (TO_DATE('1-JAN-2000','DD-MON-YYYY'))
(SUBPARTITION q4_1999_northwest VALUES('OR', 'WA'),
SUBPARTITION q4_1999_southwest VALUES('AZ', 'UT'),
SUBPARTITION q4_1999_northeast VALUES('NY', 'VM', 'NJ'),
SUBPARTITION q4_1999_southeast VALUES('FL', 'GA'),
SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q4_1999_southcentral VALUES ('NM', 'TX')));
Table Maintenance
ADDING PARTITION
ALTER TABLE ... ADD PARTITION statement is used to 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, SPLIT PARTITION clause can be used.
Prerequisites:
The table must be in your own schema, or you must have ALTER object privilege on the table, or you must have ALTER ANY TABLE system privilege.
You must also have space quota in the tablespace in which space is to be acquired in order to use the add_table_partition, modify_table_partition, move_table_partition, and split_table_partition clauses.
1)Adding a Partition to a Range-Partitioned Table:
For example consider table PRICE_VALUE.It containing data for 2007 and 2008.
SQL> CREATE TABLE <table name>(
current_dat date,
cost number(23))
partition by range(current_dat)
(PARTITION PRICES_2007
VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')),
PARTITION PRICES_2008
VALUES LESS THAN (TO_DATE('01-JAN-2009', 'DD-MON-YYYY')));
SQL> select table_name,partition_name from user_tab_partitions where table_name='<table name>';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PRICE_VALUE PRICES_2007
PRICE_VALUE PRICES_2008
A new partition for year 2009 can be added as:
SQL> Alter table <table name> add partition PRICES_2009 values less than(TO_DATE('01-JAN-2010', 'DD-MON-YYYY'));
SQL> select table_name,partition_name from user_tab_partitions where table_name='PRICE_VALUE';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PRICE_VALUE PRICES_2007
PRICE_VALUE PRICES_2008
PRICE_VALUE PRICES_2009
NOTE: Local and global indexes associated with the range-partitioned table remain usable.
2)Adding a Partition to a Hash-Partitioned Table:
Consider the following table:
SQL>CREATE TABLE PRODUCT
(product_id NUMBER,
name VARCHAR2 (60))
PARTITION BY HASH (product_id)
PARTITIONS 4;
SQL>select table_name,partition_name from user_tab_partitions where table_name='PRODUCT';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PRODUCT SYS_P23
PRODUCT SYS_P24
PRODUCT SYS_P25
PRODUCT SYS_P26
A new partition can be added as:
SQL>alter table product add partition;
SQL>select table_name,partition_name from user_tab_partitions where table_name='PRODUCT';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PRODUCT SYS_P23
PRODUCT SYS_P24
PRODUCT SYS_P25
PRODUCT SYS_P26
PRODUCT SYS_P27
Choosing the above method adds a new hash partition whose partition name is system generated, and which is placed in the table default tablespace.
To adds a new hash partition, explicitly named p_named and tablespace gear5,use the following method:
SQL>alter table product ADD PARTITION p_named TABLESPACE gear5;
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='PRODUCT';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PRODUCT P_NAMED GEAR5
PRODUCT SYS_P21 SYSTEM
PRODUCT SYS_P22 SYSTEM
PRODUCT SYS_P23 SYSTEM
PRODUCT SYS_P24 SYSTEM
PRODUCT SYS_P25 SYSTEM
NOTE:
For Regular(Heap) tables----->
Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:
* The local indexes for the new partition, and for the existing partition from which rows were redistributed, are marked UNUSABLE and must be rebuilt.
* All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.
For Index-organized tables-------->
* For local indexes, the behavior is the same as for heap tables.
* All global indexes remain usable.
3)Adding a Partition to a List-Partitioned Table:
Consider the following table:
SQL>CREATE TABLE <table name>
(deptno number,
deptname varchar2(20),
quarterly_sales number(10, 2),
state varchar2(2))
PARTITION BY LIST (state)
(PARTITION northwest VALUES ('OR', 'WA'),
PARTITION southwest VALUES ('AZ', 'UT', 'NM'),
PARTITION northeast VALUES ('NY', 'VM', 'NJ'),
PARTITION southeast VALUES ('FL', 'GA'),
PARTITION northcentral VALUES ('SD', 'WI'));
SQL> select table_name,partition_name from user_tab_partitions where table_name='<table name>';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SALES_BY_REGION NORTHCENTRAL
SALES_BY_REGION NORTHEAST
SALES_BY_REGION NORTHWEST
SALES_BY_REGION SOUTHEAST
SALES_BY_REGION SOUTHWEST
To add a new partition:
SQL> alter table <table name>add PARTITION southcentral VALUES ('OK', 'TX');
SQL> select table_name,partition_name from user_tab_partitions where table_name='<table name>';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SALES_BY_REGION NORTHCENTRAL
SALES_BY_REGION NORTHEAST
SALES_BY_REGION NORTHWEST
SALES_BY_REGION SOUTHCENTRAL
SALES_BY_REGION SOUTHEAST
SALES_BY_REGION SOUTHWEST
NOTE:
Any value in the set of literal values that describe the partition being added must not exist in any of the other partitions of the table.
You cannot add a partition to a list-partitioned table that has a default partition, but you can split the default partition. By doing so, you effectively create a new partition defined by the values that you specify, and a second partition that remains the default partition.
Local and global indexes associated with the list-partitioned table remain usable.
4)Adding Partitions to a Range-Hash Composite-Partitioned Table:
Partitions can be added at both the range partition level and the hash subpartition level.
SQL> CREATE TABLE sales_product
(prod_id number,
prod_name varchar2(20),
price number)
PARTITION BY RANGE(prod_id) SUBPARTITION BY HASH(prod_name)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1)
(PARTITION sale_pa1 VALUES LESS THAN (1000),
PARTITION sale_pa2 VALUES LESS THAN (2000));
SQL> select table_name,partition_name,subpartition_name from user_tab_subpartitions where table_name='SALES_PRODUCT';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME
------------------------------
SALES_PRODUCT SALE_PA1
SALE_PA1_SP1
SALES_PRODUCT SALE_PA2
SALE_PA2_SP1
SQL> select table_name,partition_name,subpartition_name from user_tab_subpartitions where table_name='SALES_PRODUCT';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME
------------------------------
SALES_PRODUCT SALE_PA1
SALE_PA1_SP1
SALES_PRODUCT SALE_PA2
SALE_PA2_SP1
SALES_PRODUCT SAL_PA3
SAL_PA3_SP1
---- Adding a Subpartition to a Range-Hash Partitioned Table:
SQL>ALTER TABLE SALES_PRODUCT MODIFY PARTITION SALE_PA1
ADD SUBPARTITION SAL_PA4;
SQL> select table_name,partition_name,subpartition_name from user_tab_subpartitions where table_name='SALES_PRODUCT';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME
------------------------------
SALES_PRODUCT SALE_PA1
SALE_PA1_SP1
SALES_PRODUCT SALE_PA1
SAL_PA4
SALES_PRODUCT SALE_PA2
SALE_PA2_SP1
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME
------------------------------
SALES_PRODUCT SAL_PA3
SAL_PA3_SP1
NOTE:Index subpartitions corresponding to the added and rehashed subpartitions must be rebuilt unless you specify UPDATE INDEXES
5)Adding a Partition to a Range-List Partitioned Table:
Partitions can be added at both the range partition level and the list subpartition level.
SQL> CREATE TABLE sales_product
(prod_id number,
prod_name varchar2(20),
price number)
PARTITION BY RANGE(prod_id) SUBPARTITION BY LIST (prod_name)
SUBPARTITION TEMPLATE(
SUBPARTITION Prod1 VALUES('SK', 'LM', 'TG'),
SUBPARTITION prod2 VALUES('CF', 'AR', 'HI'))
(PARTITION sale_pa1 VALUES LESS THAN (1000));
SQL> select table_name,partition_name,subpartition_name from user_tab_subpartitions where table_name='SALES_PRODUCT';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME
------------------------------
SALES_PRODUCT SALE_PA1
SALE_PA1_PROD1
SALES_PRODUCT SALE_PA1
SALE_PA1_PROD2
Following statement statements adds a new partition to the SALES_PRODUCT table:
SQL> ALTER TABLE sales_product
ADD PARTITION sal_pa2 VALUES LESS THAN (2000)
(SUBPARTITION prod3 VALUES('IL', 'OK', 'FG'));
Some new physical attributes can be specified for this new partition while table-level defaults are inherited for those that are not specified as:
SQL> ALTER TABLE sales_product
ADD PARTITION sal_pa2 VALUES LESS THAN (2000)
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING
(SUBPARTITION prod3 VALUES('IL', 'OK', 'FG'));
SQL> select table_name,partition_name,subpartition_name from user_tab_subpartitions where table_name='SALES_PRODUCT';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME
------------------------------
SALES_PRODUCT SALE_PA1
SALE_PA1_PROD1
SALES_PRODUCT SALE_PA1
SALE_PA1_PROD2
SALES_PRODUCT SAL_PA2
PROD3
----Adding a Subpartition to a Range-List Partitioned Table:
MODIFY PARTITION ... ADD SUBPARTITION clause of the ALTER TABLE statement can be used to add a list subpartition.
SQL>ALTER TABLE sales_product
MODIFY PARTITION sale_pa1
ADD SUBPARTITION prod_south
VALUES ('MS','AL');
The new subpartition can be created in tablespace ts2 as well
SQL>ALTER TABLE sales_product
MODIFY PARTITION sale_pa1
ADD SUBPARTITION prod_south
VALUES ('MS','AL') tablespace ts2;
SQL> select table_name,partition_name,subpartition_name from user_tab_subpartitions where table_name='SALES_PRODUCT';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME
------------------------------
SALES_PRODUCT SALE_PA1
PROD_SOUTH
SALES_PRODUCT SALE_PA1
SALE_PA1_PROD1
SALES_PRODUCT SALE_PA1
SALE_PA1_PROD2
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME
------------------------------
SALES_PRODUCT SAL_PA2
PROD3
Real-time Example
To Add New Partition
select PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_partitions where TABLE_OWNER='EASYOWNER' and TABLE_NAME='EASYINVOICE' order by PARTITION_POSITION ;
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ --------------------------------------------------------------------------------
EASYINVOICE_PARTITION1 1 TO_DATE(' 2015-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
EASYINVOICE_PARTITION2 2 TO_DATE(' 2016-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
EASYINVOICE_PARTITION3 3 TO_DATE(' 2016-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
EASYINVOICE_PARTITION4 4 TO_DATE(' 2017-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
EASYINVOICE_PARTITION5 5 TO_DATE(' 2017-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> alter table EASYOWNER.EASYINVOICE add partition EASYINVOICE_PARTITION6 values less than (to_date('30062018','ddmmyyyy'));
set long 5000
select dbms_metadata.get_ddl('TABLE','EASYINVOICE','EASYOWNER') from dual;
SQL> select INDEX_NAME,PARTITIONED from dba_indexes where TABLE_NAME='EASYINVOICE' and TABLE_OWNER='EASYOWNER';
INDEX_NAME PAR
------------------------------ ---
EASYINVOICE_PART_INDEX1 NO
SQL> select PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_partitions where TABLE_OWNER='EASYOWNER' and TABLE_NAME='EASYINVOICE' order by PARTITION_POSITION ;
SQL> ALTER TABLE EASYOWNER.EASYINVOICE ADD PARTITION P4 VALUES LESS THAN (20181231) ;
Table altered.
SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where TABLE_NAME='EASYINVOICE' and TABLE_OWNER='EASYOWNER';
INDEX_NAME STATUS PAR
------------------------------ -------- ---
EASYINVOICE_PART_INDEX1 VALID NO
Solution:
This error is because, the value which we are trying to insert is not satisfying the partition key range criteria.
Lets check the partition details.
SQL> select partition_name,high_value from dba_tab_partitions where table_name='EASYINVOICE';
PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P3 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
Partitioning Indexes
There are two basic types of partitioned index.
Local - All index entries in a single partition will correspond to a single table partition (equipartitioned). They are created with the LOCAL keyword and support partition independance. Equipartioning allows oracle to be more efficient whilst devising query plans.
Global - Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and do not support partition independance. Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equipartitioned, but Oracle will not take advantage of this structure.
Both types of indexes can be subdivided further.
Prefixed - The partition key is the leftmost column(s) of the index. Probing this type of index is less costly. If a query specifies the partition key in the where clause partition pruning is possible, that is, not all partitions will be searched.
Non-Prefixed - Does not support partition pruning, but is effective in accessing data that spans multiple partitions. Often used for indexing a column that is not the tables partition key, when you would like the index to be partitioned on the same key as the underlying table.
Local Prefixed Indexes
Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning are examples of local prefixed indexes.
CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL;
CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);
Oracle will generate the partition names and build the partitions in the default tablespace using the default size unless told otherwise.
Local Non-Prefixed Indexes
Assuming the INVOICES table is range partitioned on INVOICE_DATE, the following example is of a local non-prefixed index. The indexed column does not match the partition key.
CREATE INDEX invoices_idx ON invoices (invoice_no) LOCAL
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);
Global Prefixed Indexes
Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning examples is of a global prefixed index.
CREATE INDEX invoices_idx ON invoices (invoice_date)
GLOBAL PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE users);
Note that the partition range values must be specified. The GLOBAL keyword means that Oracle can not assume the partition key is the same as the underlying table.
Global Non-Prefixed Indexes
Oracle does not support Global Non Prefixed indexes.
Partitioning Existing Tables
The ALTER TABLE ... EXCHANGE PARTITION ... syntax can be used to partition an existing table, as shown by the following example. First we must create a non-partitioned table to act as our starting point.
CREATE TABLE my_table (
id NUMBER,
description VARCHAR2(50)
);
INSERT INTO my_table (id, description) VALUES (1, 'One');
INSERT INTO my_table (id, description) VALUES (2, 'Two');
INSERT INTO my_table (id, description) VALUES (3, 'Three');
INSERT INTO my_table (id, description) VALUES (4, 'Four');
COMMIT;
Next we create a new partitioned table with a single partition to act as our destination table.
CREATE TABLE my_table_2 (
id NUMBER,
description VARCHAR2(50)
)
PARTITION BY RANGE (id)
(PARTITION my_table_part VALUES LESS THAN (MAXVALUE));
Next we switch the original table segment with the partition segment.
ALTER TABLE my_table_2
EXCHANGE PARTITION my_table_part
WITH TABLE my_table
WITHOUT VALIDATION;
We can now drop the original table and rename the partitioned table.
DROP TABLE my_table;
RENAME my_table_2 TO my_table;
Finally we can split the partitioned table into multiple partitions as required and gather new statistics.
ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)
INTO (PARTITION my_table_part_1,
PARTITION my_table_part_2);
EXEC DBMS_STATS.gather_table_stats(USER, 'MY_TABLE', cascade => TRUE);
The following query shows that the partitioning process is complete.
COLUMN high_value FORMAT A20
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
------------------------------ ------------------------------ -------------------- ----------
MY_TABLE MY_TABLE_PART_1 3 2
MY_TABLE MY_TABLE_PART_2 MAXVALUE 2
2 rows selected.
To Drop partition from Table, please follow below steps
1. Take the export backup of EASY table with permanent retention into tape.
2. Take the size of the table
SQL> select segment_name,sum(bytes)/1024/1024/1024 from dba_segments where segment_name='EASYINVOICE' group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024/1024
--------------- -------------------------
EASYINVOICE 73.3447266
SQL>
3.Take out the partittion details using below query
select PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_partitions where TABLE_NAME='EASYINVOICE'
Current Production partition details:--
SQL> col PARTITION_NAME for a30
SQL> select PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_partitions where TABLE_NAME='EASYINVOICE';
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ ----------------------------------------------------------------
P1 1 20200501
SYS_P6446 2 20210701
SYS_P7066 3 20210801
SYS_P7686 4 20210901
SYS_P8326 5 20211001
SYS_P8926 6 20211101
SYS_P9546 7 20211201
SYS_P10146 8 20211301
SYS_P10786 9 20220201
SYS_P11406 10 20220301
SYS_P11966 11 20220401
SYS_P12586 12 20220501
SYS_P13186 13 20220601
SYS_P13806 14 20220701
SYS_P14391 15 20220801
15 rows selected.
4.Take the index details and its status using below query.
select index_name,status,degree from dba_indexes where table_name='EASYINVOICE';
SQL> select index_name,status,degree from dba_indexes where table_name='EASYINVOICE';
INDEX_NAME STATUS DEGREE
------------------------------ -------- ----------------------------------------
EASY_NDX211 VALID 1
INDX_EASY411 VALID 1
IND_TRAN_UDATE_TIME11 VALID 1
IK_EASY_ORGDEV11 VALID 1
EASY_NDX11 VALID 1
NDX_EASY311 VALID 1
6 rows selected.
5.Drop the partition which is older than 6 month using below command.
SQL> alter table EASYOWNER.EASYINVOICE drop partition SYS_P6446;
Table altered.
SQL> alter table EASYOWNER.EASYINVOICE drop partition SYS_P7066;
Table altered.
SQL> alter table EASYOWNER.EASYINVOICE drop partition SYS_P7686;
Table altered.
SQL> alter table EASYOWNER.EASYINVOICE drop partition SYS_P8326;
Table altered.
SQL> alter table EASYOWNER.EASYINVOICE drop partition SYS_P8926;
Table altered.
SQL> alter table EASYOWNER.EASYINVOICE drop partition SYS_P9546;
Table altered.
SQL>
SQL> col PARTITION_NAME for a30
select PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_partitions where TABLE_NAME='EASYINVOICE';
SQL>
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ --------------------------------------------------------------------------------
P1 1 20200501
SYS_P10146 2 20211301
SYS_P10786 3 20220201
SYS_P11406 4 20220301
SYS_P11966 5 20220401
SYS_P12586 6 20220501
SYS_P13186 7 20220601
SYS_P13806 8 20220701
SYS_P14391 9 20220801
9 rows selected.
SQL> SQL>
6.Rebuild the indexes using below command(User parallel based on the server utilization)
SQL> select index_name,status,degree from dba_indexes where table_name='EASYINVOICE';
INDEX_NAME STATUS DEGREE
------------------------------ -------- ----------------------------------------
EASY_NDX211 UNUSABLE 1
INDX_EASY411 UNUSABLE 1
IND_TRAN_UDATE_TIME11 UNUSABLE 1
IK_EASY_ORGDEV11 UNUSABLE 1
EASY_NDX11 UNUSABLE 1
NDX_EASY311 UNUSABLE 1
6 rows selected.
SQL> alter index EASYOWNER.EASY_NDX211 rebuild parallel 6;
Index altered.
SQL> alter index EASYOWNER.INDX_EASY411 rebuild parallel 6;
Index altered.
SQL> alter index EASYOWNER.IND_TRAN_UDATE_TIME11 rebuild parallel 6;
Index altered.
SQL> alter index EASYOWNER.IK_EASY_ORGDEV11 rebuild parallel 6;
Index altered.
SQL> alter index EASYOWNER.EASY_NDX11 rebuild parallel 6;
Index altered.
SQL> alter index EASYOWNER.NDX_EASY311 rebuild parallel 6;
Index altered.
SQL>
7.After rebuild change the parallel to noparallel.
SQL> alter index EASYOWNER.EASY_NDX211 noparallel ;
alter index EASYOWNER.INDX_EASY411 noparallel ;
alter index EASYOWNER.IND_TRAN_UDATE_TIME11 noparallel ;
alter index EASYOWNER.IK_EASY_ORGDEV11 noparallel ;
alter index EASYOWNER.EASY_NDX11 noparallel ;
alter index EASYOWNER.NDX_EASY311 noparallel ;
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
8.Take out the size of the tables and status of indexes.
select index_name,status,degree from dba_indexes where table_name='EASYINVOICE';
SQL> select index_name,status,degree from dba_indexes where table_name='EASYINVOICE';
INDEX_NAME STATUS DEGREE
------------------------------ -------- ----------------------------------------
EASY_NDX211 VALID 1
INDX_EASY411 VALID 1
IND_TRAN_UDATE_TIME11 VALID 1
IK_EASY_ORGDEV11 VALID 1
EASY_NDX11 VALID 1
NDX_EASY311 VALID 1
6 rows selected.
SQL>
Procedure for EASYTABLE Partitioning Realtime Example
Database Name : EASYDB
Server
Name
: EASYSERVER
Summary: EASYDB partitioning is a monthly activity to be performed by the DBA
team every month. The 6 months old
partitions are to be dropped and new
partitions for the month ahead have to be created. The partitions should be
created before the end of last
week of every month.
Mandatory Steps:
Ø
On
EASYSERVER, the scripts required for creating the partitions are located in
/opt/app/oracle/scripts/partitions
1. Login as Oracle user and cd to /opt/app/oracle/scripts/partitions
2. Before starting, the oldest month’s partition has to be dropped before proceeding. Amend the drop_partitions.sql script to drop the 6 month old partition.
drop_partitions.sql
alter table EASYDB.INVOICE drop partition APR10 update global indexes;
alter table EASYDB.INVOICEITEM drop partition
APR10 update global indexes;
alter table EASYDB.STATEMENT drop partition APR10 update global indexes;
alter table EASYDB.EASYTABLESPACE drop partition APR10
update global indexes;
3.
Check
if the relevant tablespaces (EASYTABLESPACE_month_DAT and EASYTABLESPACE_month_IND)
are empty by querying dba_segments.
SQL> Select count(*) from dba_segments
where tablespace_name=&tbs;
Select count(*) from dba_segments where tablespace_name='EASYTABLESPACE_APR_DAT';
Select count(*) from dba_segments where
tablespace_name='EASYTABLESPACE_APR_IND';
4.
Make
a note of which filesystems on which we have deleted the oldest month
tablespace.
select
file_name,AUTOEXTENSIBLE,bytes/1024/1024 from dba_data_files where
tablespace_name='EASYTABLESPACE_APR_DAT’;
select
file_name,AUTOEXTENSIBLE,bytes/1024/1024 from dba_data_files where tablespace_name='EASYTABLESPACE_APR_IND';
5.
After
confirming that the tablespace doesn’t contain any data. the 6 month oldest
tablespaces and datafiles should be dropped. Amend the drop_tablespaces.sql
scripts to drop the oldest tablespaces and execute it.
drop tablespace EASYTABLESPACE_APR_DAT including contents and datafiles;
drop tablespace EASYTABLESPACE_APR_IND including contents and datafiles;
6. Wait a few minutes as there is sometimes a delay before the filesystems show the newly available space.
CREATE TABLESPACE EASYTABLESPACE_NOV_DAT
DATAFILE '/data/EASYDB/p05/EASYTABLESPACE_NOV_dat_01.dbf' SIZE 256m AUTOEXTEND ON NEXT 128M
MAXSIZE 32000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072K
LOGGING
ONLINE
SEGMENT SPACE MANAGEMENT AUTO
/
ALTER TABLESPACE EASYTABLESPACE_NOV_DAT
ADD DATAFILE '/data/EASYDB/p05/EASYTABLESPACE_NOV_dat_02.dbf' SIZE 256m AUTOEXTEND ON NEXT 128M
MAXSIZE 32000M
/
ALTER TABLESPACE EASYTABLESPACE_NOV_DAT
ADD DATAFILE '/data/EASYDB/p05/EASYTABLESPACE_NOV_dat_03.dbf' SIZE 256m AUTOEXTEND ON NEXT 128M
MAXSIZE 32000M
/
ALTER TABLESPACE EASYTABLESPACE_NOV_DAT
ADD DATAFILE '/data/EASYDB/p05/EASYTABLESPACE_NOV_dat_04.dbf' SIZE 256m AUTOEXTEND ON NEXT 128M
MAXSIZE 32000M
/
ALTER TABLESPACE EASYTABLESPACE_NOV_DAT
ADD DATAFILE '/data/EASYDB/p05/EASYTABLESPACE_NOV_dat_05.dbf' SIZE 256m AUTOEXTEND ON NEXT 128M
MAXSIZE 32000M
CREATE
TABLESPACE EASYTABLESPACE_NOV_IND
DATAFILE
'/data/EASYDB/p05/EASYTABLESPACE_NOV_ind_01.dbf' SIZE 256m AUTOEXTEND ON NEXT
128M MAXSIZE 32000M
EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 131072K LOGGING ONLINE
SEGMENT
SPACE MANAGEMENT AUTO
/
ALTER
TABLESPACE EASYTABLESPACE_NOV_IND
ADD
DATAFILE '/data/EASYDB/p05/EASYTABLESPACE_NOV_ind_02.dbf' SIZE 256m AUTOEXTEND
ON NEXT 128M MAXSIZE 32000M
/
ALTER
TABLESPACE EASYTABLESPACE_NOV_IND
ADD
DATAFILE '/data/EASYDB/p05/EASYTABLESPACE_NOV_ind_03.dbf' SIZE 256m AUTOEXTEND
ON NEXT 128M MAXSIZE 32000M
/
ALTER
TABLESPACE EASYTABLESPACE_NOV_IND
ADD
DATAFILE '/data/EASYDB/p05/EASYTABLESPACE_NOV_ind_04.dbf' SIZE 256m AUTOEXTEND
ON NEXT 128M MAXSIZE 32000M
/
ALTER
TABLESPACE EASYTABLESPACE_NOV_IND
ADD
DATAFILE '/data/EASYDB/p05/EASYTABLESPACE_NOV_ind_05.dbf' SIZE 256m AUTOEXTEND
ON NEXT 128M MAXSIZE 32000M
/
alter
table EASYDB.invoice add
PARTITION NOV10
VALUES LESS THAN (TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
PCTUSED 0
TABLESPACE EASYTABLESPACE_NOV_DAT
(SUBPARTITION NOV10_BTC
VALUES('BTC') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_FIX
VALUES('FIX') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_VFM
VALUES('VFM') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_VTM
VALUES('VTM') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_TMB
VALUES('TMB') TABLESPACE EASYTABLESPACE_NOV_DAT)
/
alter
table EASYDB.invoiceitem add
PARTITION NOV10
VALUES LESS THAN (TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
PCTUSED 0
TABLESPACE EASYTABLESPACE_NOV_DAT
(SUBPARTITION NOV10_BTC
VALUES('BTC') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_FIX
VALUES('FIX') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_VFM
VALUES('VFM') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_VTM
VALUES('VTM') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_TMB
VALUES('TMB') TABLESPACE EASYTABLESPACE_NOV_DAT)
/
alter
table EASYDB.statement add
PARTITION NOV10
VALUES LESS THAN (TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
PCTUSED 0
TABLESPACE EASYTABLESPACE_NOV_DAT
(SUBPARTITION NOV10_BTC
VALUES('BTC') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_FIX
VALUES('FIX') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_VFM
VALUES('VFM') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_VTM
VALUES('VTM') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_TMB VALUES('TMB')
TABLESPACE EASYTABLESPACE_NOV_DAT)
/
alter
table EASYDB.EASYTABLESPACE add
PARTITION NOV10
VALUES LESS THAN (TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
PCTUSED 0
TABLESPACE EASYTABLESPACE_NOV_DAT
(SUBPARTITION NOV10_BTC
VALUES('BTC') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_FIX
VALUES('FIX') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_VFM
VALUES('VFM') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_VTM
VALUES('VTM') TABLESPACE EASYTABLESPACE_NOV_DAT,
SUBPARTITION NOV10_TMB
VALUES('TMB') TABLESPACE EASYTABLESPACE_NOV_DAT)
/
10)
Finally amend the alter_index.sql for the next month and run it.
alter
index EASYDB.invoiceitemI2 rebuild subpartition NOV10_BTC
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemI2 rebuild subpartition NOV10_FIX
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemI2 rebuild subpartition NOV10_VFM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemI2 rebuild subpartition NOV10_VTM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemI2 rebuild subpartition NOV10_TMB
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemI3 rebuild subpartition NOV10_BTC
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemI3 rebuild subpartition NOV10_FIX
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemI3 rebuild subpartition NOV10_VFM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemI3 rebuild subpartition NOV10_VTM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemI3 rebuild subpartition NOV10_TMB
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemp1 rebuild subpartition NOV10_BTC
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemp1 rebuild subpartition NOV10_FIX
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemp1 rebuild subpartition NOV10_VFM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemp1 rebuild subpartition NOV10_VTM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.invoiceitemp1 rebuild subpartition NOV10_TMB
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementI2 rebuild subpartition NOV10_BTC
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementI2 rebuild subpartition NOV10_FIX
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementI2 rebuild subpartition NOV10_VFM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementI2 rebuild subpartition NOV10_VTM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementI2 rebuild subpartition NOV10_TMB
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementI3 rebuild subpartition NOV10_BTC
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementI3 rebuild subpartition NOV10_FIX
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementI3 rebuild subpartition NOV10_VFM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementI3 rebuild subpartition NOV10_VTM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementI3 rebuild subpartition NOV10_TMB
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementp1 rebuild subpartition NOV10_BTC
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementp1 rebuild subpartition NOV10_FIX
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementp1 rebuild subpartition NOV10_VFM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementp1 rebuild subpartition NOV10_VTM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.statementp1 rebuild subpartition NOV10_TMB
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.EASYTABLESPACEI2 rebuild subpartition NOV10_BTC
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.EASYTABLESPACEI2 rebuild subpartition NOV10_FIX
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.EASYTABLESPACEI2 rebuild subpartition NOV10_VFM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.EASYTABLESPACEI2 rebuild subpartition NOV10_VTM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.EASYTABLESPACEI2 rebuild subpartition NOV10_TMB
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.EASYTABLESPACEp1 rebuild subpartition NOV10_BTC
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.EASYTABLESPACEp1 rebuild subpartition NOV10_FIX
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.EASYTABLESPACEp1 rebuild subpartition NOV10_VFM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.EASYTABLESPACEp1 rebuild subpartition NOV10_VTM
tablespace EASYTABLESPACE_NOV_IND;
alter
index EASYDB.EASYTABLESPACEp1 rebuild subpartition NOV10_TMB
tablespace EASYTABLESPACE_NOV_IND;
10.
Send
and email to the team to let them know the work is done.
Note1: All the text in blue color
will be changed month after month during partition activity.
Note 2: Though there is not enough space to
grow all datafiles upto 32 Gigs, we have made them autoextensible upto 32 Gigs
to accomodate the maximum space required by both DATA and INDEX tablespace, also
we have added new metrics in OEM to monitor the file system space usage (P01 to
P07) 80% warning and 90% critical to avoid the space crunch in advance
No comments:
Post a Comment