Jump to content

18c user defined sharding

Recommended Posts

18c user manged sharding with range partition, need help in following issues
1 catalog db,3shards , each shard has 5 tablespace and different date data.
1-- how can i split my maxvalue partition into new partition, when i try recursive sql errors show
2-- can't load bulk load, as we are used to in simple tables , array dml not suported on sharded table
3-- how can i offload data from sharded table , alter table exchange partition

please guide me what i do now, thanks

Share this post

Link to post
Share on other sites

Hi, and welcome to the forum!

I have not used sharding, but let’s see what I can find.

First, be careful on how you spell sharding:


Anyway, as I understand user defined sharding, it is just a way to map tablespace stocks multiple servers.



1 - Can I see your split command please?  This doc may be helpful:


You may want to check MOSC on the recursive SQL erred, it

It may be a bug:



2 - bulk load 

Are you using oracleloader?

I think that you can still run parallel SQL*Loader for bulk loads:


Would this work for you?


3 - offload data from sharded partition

By offloading, you don’t mean Exadata cell offloading, right?

And this is not using data pump expdp, right?

Can you please explain how you define offloading?

Please advise. , . 




Share this post

Link to post
Share on other sites

Hi Sir,  thanks for your response,

  1--   split partition command that i am using , target tablespace is on same shard,  

ALTER TABLE location SPLIT PARTITION location_NODE4_P24    INTO (PARTITION location_NODE4_P24  VALUES LESS THAN (TIMESTAMP' 2018-05-01 00:00:00')   tablespace Node4_TS_8 , PARTITION location_NODE2_P25  TABLESPACE Node4_TS_9 ) update indexes
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02680: unsupported construct referred in the query

2-- sqlloder giving me following ora error

ORA-02681: array DML not supported on a sharded table   

3-- offloading mean simply  alter table exchange partition. 




Share this post

Link to post
Share on other sites

Hi Kahloon,

As I noted, I have not used sharding, so I cannot help much!

I would post this question in the Oracle Support forum on MOSC.  They have experts on call to assist you:


Please post the solution when you find it.

Good Luck!

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now