Oracle Partioning information

  SELECT table_name, partitioning_type, ref_ptn_constraint_name

 FROM user_part_tables

 WHERE table_name IN ('PROJECT','PROJECT_CUSTOMER','PROJ_CUST_ADDRESS');

SELECT table_name, partition_name, high_value

 FROM user_tab_partitions

 WHERE table_name in ('PROJECT','PROJECT_CUSTOMER’)

 ORDER BY table_name, partition_position;

ALTER TABLE project_cust_address

DROP PARTITION pd;

CREATE TABLE EVENTS( sensor_type VARCHAR2(50),

channel VARCHAR2(50), …)

PARTITION BY LIST (sensor_type, channel)

( partition p1 values (‘GYRO’,’CH1’),

 partition p2 values (‘GYRO’,’CH2’),

 partition p3 values (‘CAMERA’,’CH4’),

…

 partition p44 values ((‘THERMO’,’CH8’),

 (‘THERMO’,’CH14’)),

 partition p45 values (DEFAULT)

); 

CREATE TABLE orders ( order_id number,

 order_date DATE, … )

ORGANIZATION EXTERNAL

( TYPE oracle_loader DEFAULT DIRECTORY data_dir

 ACCESS PARAMETERS (..)

) REJECT LIMIT unlimited

PARTITION BY RANGE(order_date)

( partition q1_2015 values less than (‘2014-10-01’)

 DEFAULT DIRECTORY old_data_dir LOCATION (‘q1_2015.csv’),

 partition q2_2015 values less than (‘2015-01-01’)

 LOCATION (‘q2_2015.csv’),

 partition q3_2015 values less than (‘2015-04-01’)

 LOCATION (‘q3_2015.csv’),

 partition q4_2015 values less than (‘2015-07-01’)

)

CREATE TABLE EVENTS ( sensor_grp VARCHAR2 (50),

 channel VARCHAR2 (50), … );

ALTER TABLE EVENTS MODIFY

PARTITION BY LIST ( sensor_grp )

 (partition p1 values (‘GYRO_GRP’),

 partition p2 values (‘CAMERA_GRP’),

 partition p3 values (‘THERMO_GRP’),

 partition p4 values (DEFAULT))

UPDATE INDEXES ONLINE;

ALTER TABLE orders MOVE PARTITION q3_2020

TABLESPACE archive

INCLUDING ROWS WHERE order_state = ‘open’;

ALTER TABLE orders MOVE PARTITION q3_2020

TABLESPACE archive online

INCLUDING ROWS WHERE order_state = ‘open’;

ALTER TABLE EVENTS MODIFY

PARTITION BY RANGE ( run_id )

SUBPARTITION BY LIST ( sensor_type )…

UPDATE INDEXES

 (i1_run_id GLOBAL,

 i2_sensor LOCAL,

 i3 GLOBAL PARTITION BY RANGE ( … )

 (PARTITION p0100 VALUES LESS THAN (100000),

 PARTITION p1500 VALUES LESS THAN (1500000),

 PARTITION pmax VALUES LESS THAN (MAXVALUE)))

ONLINE

SQL > alter table pt merge partitions part05, part15, part25

 into partition p30;

Table altered.

SQL > alter table pt merge partitions part10 to part30

 into partition part30;

Table altered.

SQL > alter table pt split partition p30 into

2 (partition p10 values less than (10),

3 partition p20 values less than (20),

4 partition p30);

Table altered

Table Partition Maintenance

Operations

ALTER TABLE ADD PARTITION(S)

ALTER TABLE DROP PARTITION(S)

ALTER TABLE EXCHANGE PARTITION

ALTER TABLE MODIFY PARTITION

[PARALLEL][ONLINE]

ALTER TABLE MOVE PARTITION [PARALLEL][ONLINE]

ALTER TABLE RENAME PARTITION

ALTER TABLE SPLIT PARTITION [PARALLEL][ONLINE]

ALTER TABLE MERGE PARTITION(S) [PARALLEL]

[ONLINE]

ALTER TABLE COALESCE PARTITION [PARALLEL]

ALTER TABLE ANALYZE PARTITION

ALTER TABLE TRUNCATE PARTITION(S)

Export/Import [by partition]

Transportable tablespace [by partition]

Index Maintenance

Operations

ALTER INDEX MODIFY PARTITION

ALTER INDEX DROP PARTITION(S)

ALTER INDEX REBUILD PARTITION

ALTER INDEX RENAME PARTITION

ALTER INDEX RENAME

ALTER INDEX SPLIT PARTITION

ALTER INDEX ANALYZE PARTITION

CREATE TABLE events ( event_id number,

 evnt_date DATE, … ) read only

PARTITION BY RANGE(event_date)

( partition q1_2020 values less than (‘2020-04-01’),

 partition q2_2020 values less than (‘2020-07-01’),

 partition q3_2020 values less than (‘2020-10-01’),

 partition q4_2020 values less than (‘2021-01-01’) read write

);

QL > CREATE TABLE tx_simple

 2 (

 3 TRANSACTION KEY NUMBER,

 4 INQUIRY_TIMESTAMP TIMESTAMP(6),

 5 RUN_DATE DATE

 6 )

 7 PARTITION BY RANGE (RUN_DATE)

 8 (

 9 PARTITION TRANSACTION_202105 VALUES LESS THAN (TO_DATE(‘20210601’, ‘yyyymmdd’)),

10 PARTITION TRANSACTION_202106 VALUES LESS THAN (TO_DATE(‘20210701’, ‘yyyymmdd’)),

11 PARTITION TRANSACTION_202107 VALUES LESS THAN (TO_DATE(‘20210801’, ‘yyyymmdd’)),

12 PARTITION TRANSACTION_202108 VALUES LESS THAN (TO_DATE(‘20210901’, ‘yyyymmdd’)),

13 PARTITION TRANSACTION_202109 VALUES LESS THAN (TO_DATE(‘20211001’, ‘yyyymmdd’)),

14 PARTITION TRANSACTION_202110 VALUES LESS THAN (TO_DATE(‘20211101’, ‘yyyymmdd’)),

15 PARTITION TRANSACTION_MAX VALUES LESS THAN (MAXVALUE)

16 )

17 /

Table created

DROP INDEX meas_campaign DEFERRED INVALIDATION;

 

SQL > alter table tx_simple

 2 exchange partition TRANSACTION_202107

 3 with table daily_ETL_table

 4 including indexes

 5 --excluding indexes

 6 WITHOUT VALIDATION

 7 UPDATE GLOBAL INDEXES

 8 /

Table altered