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 used as follow 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 of adding new tablespace 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.

SYNTAX : 
ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD PARTITION < PARTITION_NAME> VALUES LESS THAN < HIGH_VALUE> TABLESPACE <TABLESPACE_NAME > < UPDATE GLOBAL INDEXES(optional)>;

Example: Range partition added as follows:
--With update global indexes
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" UPDATE GLOBAL INDEXES;
--Without update global indexes
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";

-- In oracle 12c, we can add multiple partition in one command:
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",
PARTITION "SALES200402" VALUES LESS THAN (TO_DATE(' 2004-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SALES200402",
UPDATE GLOBAL INDEXES;

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

SYNTAX : 
ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> DROP PARTITION < PARTITION_NAME> <UPDATE GLOBAL INDEXES(optional)>;

Example:
ALTER TABLE sales DROP PARTITION SALES200401;
ALTER TABLE sales DROP PARTITION SALES200401 UPDATE GLOBAL INDEXES;

--In oracle 12c, we can drop multiple partitions in one command
ALTER TABLE SCOTT.SALES DROP PARTITIONS SALES200401, SALES200402 UPDATE GLOBAL INDEXES;