Raj

I think @Gandolf989 might be onto something. In your above output you never show the connection command, so how do we/you know for sure if you connected to FooA or FooB? We will take your word for it, but I'd rather see proof :)

what is the value of parameter GLOBAL_NAME also what is the output of select sysdate, instance_name from v$instance@FooB; ?

Unfortunately you still have not shown the actual sqlplus command you ran for 'drop package' and the actual output. the output you have shown seems hand assembled. Is that very hard to run the command in sqlplus and copy that line along with few mote from stack of errors?

show us the output of select object_name, object_type from user_objects where object_name like '%name%'; where name is the name for that object

@CharlesBurns, if you must look for and support PK, then your software could perform some checks on the data dictionary and see if existing constraints and indexes truly specify a PK? if there is a mismatch (as you have shown) perhaps your software can raise an error and let user fix the table before proceeding? I think that may be doable and will make your application more robust in terms of such dependencies.

In your example, you create a constraint disabled and then enable novalidate and you want a PK? You really have to make up your mind. Furthermore the index you created needs to be unique. Basically this feels like is a (Rube Goldberg style) carefully set-up test to win a bet. Could you re-run the test by just adding the PK constraint after inserts (do not disable, and do not create index). but I like the setup, nice.

use external tables as, 1. You need an external procedure job with suitable privileges so it can execute commands on the server 2. Output of these commands should get written to a directory and expose this directory to oracle as 'oracle directory'. 3. Call your procedure, this should call external job that would execute command and generate CSV in the (known) directory 4.1 expose each line as a single column for an ext table or 4.2.1 - same as 4.1 4.2.2 - Use your column list and create or modify a view on that external table and use that in your processing.

Yes, so you want automation but don't like Oracle imposed limit? I wouldn't hold my breath for a quick workaround. If I were you, I'd just convert to range, how hard it is to schedule a job using dbms_job, dbms_scheduler or plain old cron to pre-create monthly partitions? heck you can do 6 months at a time. If you must use interval partition (which technically is NOT a requirement), I can't convince you.

Use range partition instead of interval. You can create partitions manually or automatically by writing few lines of code. read this and [this too] (docs.oracle.com/database/121/REFRN/…)

Perhaps I wasn't clear, if you read the first link I provided, you will realize that Hard limit of 1m partitions is applied differently for interval and range partitions. My suggestion is NOT to use interval partitioning and use purely RANGE. Now do you think you have problems creating RANGE partitions for months?