Using a Sequence across a database link

[commatom]

OK folks got another one for ya.

I have been tuning an insert statement over a database link. The statement had a trigger that assigned a sequence to one of the columns and it took 40 minutes to run and then 9 minutes when I dropped the trigger.

Now I need to add the sequence to my insert statement. When I create the sequence from where I am running the statement I get the following error.

ERROR at line 1:
ORA-02069: global_names parameter must be set to TRUE for this operation

I looked up the error and it tells me to set the global_names to TRUE so I do that and I get this next error.

 15:44:27 SQL>  exec DW.load_fact_prepaid_sales_proc
BEGIN DW.load_fact_prepaid_sales_proc; END;

*
ERROR at line 1:
ORA-02085: database link IDWDW connects to IDWDEV
ORA-06512: at "DW.LOAD_FACT_PREPAID_SALES_PROC", line 22
ORA-06512: at line 1

OK so maybe the sequence needs to be on the instance where I am pushing the data. I re-create the sequence on the other instance and when I run the insert I get this error.

ORA-01031: insufficient privileges

I have granted all on the sequence to the id that is used for the database link. Anyone know what I am missing?

 

[dbtoo2001]

Does the userid in the dblink have the authority to insert/update on the distributed table?

It might help to post the dblink, tnsnames entry, and the sql that does the distributed query.

 

[commatom]

Yeah, Im not sure what the DBA did but he got it working.

Sorry I cant post the resolution.