ORA-02069 and database link, synonym, sequence

Database Link is used to insert data into tables in a remote database. If the sequence in the local database is used

Will encounterORA-02069: The global_names parameter for this operation must be set to true.

Solution: Use the sequence in the remote database.

 

1. The Scott User grants the report user the select, insert, and update permissions on the dept table.

SQL> conn Scott/Tiger
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as Scott
SQL> grant select, insert, update on dept to report;
Grant succeeded

 

2. The report user establishes the Scott user's database link (the database link is only used to simulate the problem)

SQL> conn report/Report
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as report

SQL> CREATE DATABASE LINK Scott
2 connect to Scott identified by Tiger
3 using 'liangwei ';
Database link created

 

3. Create based on database linkDept @ ScottSynonym of a table

SQL> Create or replace synonym dept for dept @ Scott;
Synonym created

 

4. Create a sequence deptid under the report user

SQL> Create sequence deptid
2 minvalue 10
3 maxvalue 1000
4 start with 30
5 increment by 1
6 cache 20;

Sequence created

 

5. Insert data into the new synonym dept with the sequence deptid, And the ORA-02069 appears

SQL> insert into dept values (deptid. nextval, 'code', 'bj ');

Insert into dept values (deptid. nextval, 'code', 'bj ')

ORA-02069: The global_names parameter for this operation must be set to true

 

SQL> show parameter global_names

Name type value
-----------------------------
Global_names Boolean false

 

If global_names is not set to true, can this problem be solved.

 

 

6. log on to Scott to create a sequence deptid.
SQL> conn Scott/Tiger
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as Scott

 

SQL> Create sequence deptid
2 minvalue 10
3 maxvalue 1000
4 start with 30
5 increment by 1
6 cache 20;

Sequence created

 

7. Delete the deptid sequence from the report user.

SQL> conn report/Report
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as report

SQL> drop sequence deptid;
Sequence dropped

 

8. Use the sequence to insert data under the repot user as the synonym Dept

SQL> insert into dept values (deptid. nextval, 'code', 'bj ');
Insert into dept values (deptid. nextval, 'code', 'bj ')
ORA-02289: sequence does not exist

Because the sequence deptid under the report user can be deleted, so the error ORA-02289: sequence does not exist

 

 

9. Use the sequence of scoot users through database link to insert data to the synonym dept in the report user. OK

 

SQL> insert into dept values (deptid. nextval @ Scott, 'code', 'bj ');
1 row inserted

SQL> commit;
Commit complete

 

SQL> select * from Dept;
Deptno dname Loc
---------------------------------
31 Code BJ
10 Accounting New York
20 research Dallas
30 sales chicago1
40 operations Boston
11 code Beijing
6 rows selected

 

 

Conclusion: The sequence in the remote database should be used when data is inserted into the remote database through database link.