ORACLE - Partitions

Oracle Partitions Overview:

INTERVAL Partition:

CREATE TABLE Schema.TableName
(
  TRANSACTIONTIME       TIMESTAMP(6),
  Col2                  CHAR(1 CHAR),
  col3                  VARCHAR2(50)
)
PARTITION BY RANGE (TRANSACTIONTIME)
INTERVAL (NUMTODSINTERVAL(15,'DAY'))
(partition part_data_0 values less than (TO_DATE('01-JAN-2011','DD-MON-YYYY'))
) TABLESPACE DATA;

Note:
1.Only one partitioning key column, and it must be of NUMBER or DATE type.
2.The optional STORE IN clause lets you specify one or more table-spaces into which the database stores interval partition data using a round-robin algorithm

List Partition:

CREATE TABLE Hotel_Region
      (ID number,
       quarterly_sales number(10, 2),
       state varchar2(2))
PARTITION BY LIST (state)
      (PARTITION p1 VALUES ('CA', 'WA'),
       PARTITION p2 VALUES ('AZ', 'NJ', 'NY'),
       PARTITION p3 VALUES  ('FL', 'SD', 'WI'),
       PARTITION p4 VALUES ('TX', 'GA'));

Local Index:
CREATE UNIQUE INDEX INDEX_NAME ON TableName (Col_Name)
TABLESPACE TBS01 LOCAL PARALLEL NOLOGGING;

Global Index Non-Partion Index:
CREATE INDEX GINDX ON Hotel_Region (ID);

Global Index Partion Index:CREATE INDEX GINDX ON Hotel_Region (ID)
PARTITION BY LIST (ID)
(PARTITION p1 VALUES ('CA', 'WA'),
PARTITION p2 VALUES ('AZ', 'NJ', 'NY'));

View Partiton and Indexes;
Select index_name,partitioned, index_type from user_indexes where table_name=<table_name>;
Select PARTITION_NAME, num_rows from dba_tab_partitions where TABLE_OWNER = 'Schema' and TABLE_NAME IN  (  'Table_Name');