Oracle partition table

We have an oracle table which used to use store proc to populate from other oracle table. Now since our upstream data source migrated to Hive, we need to do it thru jdbc and persist with JPA. And we hit a problem with: ‘inserted partition key does not map to any partition’.

Turns out that in the store proc, before we insert the data, the store proc would create a new partition(month+year) for the table. Googled a lot, it seems there is no good way of handling it in the java world, i guess we have to go to the native sql way to tackle this.

Some notes on what learnt during the research.

Good oracle doc about table partitioning.

command for checking partition info


--view all partitioned tables
SELECT DISTINCT TABLE_NAME FROM ALL_TAB_PARTITIONS;
--user partitioned tables
SELECT DISTINCT TABLE_NAME FROM user_tab_partitions;
--look at all partitions inside a specific table
select * from ALL_TAB_PARTITIONS where table_name = 'YOUR_PAR_TABLE _NAME';
--check partition key
select * from ALL_PART_KEY_COLUMNS;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s