Create or Drop of Partition in Oracle

Creation of Partition

For adding more partition in existing partition table. Need to follow following steps. It help to add next year partition
Example is for monthly wise.

1. Check the table space and file_name already present for partition.

column tablespace_name format a25
column file_name format a45
column maxbytesgb format 99999
select tablespace_name,file_name,maxbytes/1024/1024/1024 as maxbytesGB from dba_data_files order by 1;


2. Check partition table present in database rather than SYS,SYSTEM Schema

select * from dba_part_tables where owner not in ('SYS','SYSTEM');


3. Check the detail of partition by specify table name and partition name like tablespace_name,high_value

column b.tablespace_name format a25
column table_name format a20
column partition_name format a30
select Table_name,Partition_name,High_Value,tablespace_name from DBA_TAB_PARTITIONS
where table_owner = 'SCOTT' and table_name ='SALES' ;

Note: Check each partition that having new tablespace or common tablespace.
If having new tablespace then we need to add tablespace and datafiles.

4. In case adding new table space or data file , verify the db_files parameter

–check the value of db_files parameter

column name format a40
column value format a40
select name,value from v$parameter where lower(name) like 'db_file%';

–Exact value of datafiles present in the database

column datafilespresent format 99999
select count(*) as datafilespresent from v$datafile;

5. If you have sufficient number of db_files value to add datafile, move forward add new tablespace

Example:
CREATE TABLESPACE "SALES200401" LOGGING DATAFILE
'E:\ORACLE\ORADATA\ORCL\PARTITIONS\SALES20040101.DBF' SIZE 25M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 16000M
EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE "SALES200402" LOGGING DATAFILE
'E:\ORACLE\ORADATA\ORCL\PARTITIONS\SALES20040201.DBF' SIZE 25M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 16000M
EXTENT MANAGEMENT LOCAL;


6. Assign quota to new tablespace to specific user

example:
ALTER USER "SCOTT" QUOTA UNLIMITED ON SALES200401;
ALTER USER "APP1" QUOTA UNLIMITED ON SALES200401;


7. Add new partition in the table

Example: Range partition added as follows:

ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200401" VALUES LESS THAN (TO_DATE(' 2004-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200401";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200402" VALUES LESS THAN (TO_DATE(' 2004-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200402";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200403" VALUES LESS THAN (TO_DATE(' 2004-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200403";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200404" VALUES LESS THAN (TO_DATE(' 2004-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200404";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200405" VALUES LESS THAN (TO_DATE(' 2004-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200405";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200406" VALUES LESS THAN (TO_DATE(' 2004-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200406";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200407" VALUES LESS THAN (TO_DATE(' 2004-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200407";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200408" VALUES LESS THAN (TO_DATE(' 2004-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200408";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200409" VALUES LESS THAN (TO_DATE(' 2004-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200409";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200410" VALUES LESS THAN (TO_DATE(' 2004-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200410";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200411" VALUES LESS THAN (TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200411";
ALTER TABLE SCOTT.SALES ADD PARTITION "SALES200412" VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200412";


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

ALTER TABLE sales DROP PARTITION SALES200401;

ALTER TABLE sales DROP PARTITION SALES200401 UPDATE GLOBAL INDEXES;