Shulou(Shulou.com)05/31 Report--

This article introduces the implementation of truncate trigger ORA-02266 solution process is how, the content is very detailed, interested friends can refer to, hope to be helpful to you.

The developer puts forward a requirement to empty the data in the test database, which involves the relationship between the master and child tables, as shown below

The most intuitive approach is the truncate table. First truncate the child tables, but when executing the truncate main table, an error is prompted, ORA-02266: unique/primary keys in table referenced by enabled foreign keys.

There is no data in the child table at this time, why not delete the data of the main table?

To simulate the process, we first create a test table, the main table aqum1, and the child table broom1

SQL > create table axi1 (id number)

Table created.

SQL > create table bread1 (id number, id_a_1 number)

Table created.

SQL > alter table axi1 add constraint pk_a_1 primary key (id)

Table altered.

SQL > alter table bread1 add constraint fk_b_a foreign key (id_a_1) references astat1 (id)

Table altered.

At this point, both the truncate child table and the main table will be successful.

SQL > truncate table bread01

Table truncated.

SQL > truncate table aq01

Table truncated.

However, when the master child table has data, the truncate child table, and then do the operation of the truncate master table, it will prompt the error of ORA-02266

SQL > insert into axi1 values (1)

1 row created.

SQL > insert into bread1 values (1BI 1)

1 row created.

SQL > commit

Commit complete.

SQL > truncate table bread1

Table truncated.

SQL > truncate table axi1

Truncate table a_1

*

ERROR at line 1:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

The error meaning of ORA-02262 is, "unique / foreign key references enabled for primary keys in the table"

02262, 00000, "ORA-%s occurs while type-checking column default value expression"

/ / * Cause: New column datatype causes type-checking error for existing column

/ / default value expression.

/ / * Action: Remove the default value expression or don't alter the column

/ / datatype.

But there is no data in the child table, how can the main table still prompt this error? The reason for this is related to the truncate operation, because truncate is DDL, but the DDL statement does not check for constraints, in other words, he doesn't know if any data in the child table depends on him, so he simply refuses to do so.

But there is a doubt, when the master child table has no data, the truncate master table can be successful, only when the master child table has data, the truncate master table will prompt. I looked at the statistics, there is no quantity record in the table, how does Oracle know that the current table has data and forbids truncate?

I guess, maybe it's the influence of the delay?

1. Look at the data in the following table. The 10046 generated by truncate is executed. In the main table of truncate table axiom 1, the parameter of a binding variable is Blocl. It is inferred that we know that there is a foreign key reference in aqum1, and then an error is reported, err=2266.

...

LOCK TABLE "Atoll 1" IN EXCLUSIVE MODE NOWAIT

...

Truncate table a_1

...

Bind#1

Oacdty=01 mxl=32 (03) mxlc=00 mal=00 scl=00 pre=00

Oacflg=10 fl2=0001 frm=01 csi=873 siz=0 off=24

Kxsbbbfp=7f2df926afc8 bln=32 avl=03 flg=01

Value= "Barrier 1"

...

ERROR # 139835430202688:err=2266 tim=1562853681567125

...

two。 Seeing that there is no data in the following table, execute the 10046 generated by truncate, and find that he will retrieve the deferred_stg$ view. Truncate depends on the aw_trunc_proc stored procedure.

...

LOCK TABLE "Atoll 1" IN EXCLUSIVE MODE NOWAIT

...

Select pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg, maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg, maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg, cmpflag_stg, cmplvl_stg from deferred_stg$ where obj# =: 1

...

Truncate table a_1

...

BEGIN

Aw_trunc_proc (ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner)

END

...

3. Turn off the latency feature at the session level

Gment_creation=false

Session altered.

There is no data in the table, the 10046 generated by executing truncate, compared with the above two, the operation is the easiest, LOCK table, execute truncate, no other operations

...

LOCK TABLE "Atoll 1" IN EXCLUSIVE MODE NOWAIT

...

Truncate table a_1

...

From the point of view of the phenomenon, it is not the characteristics of the delay section, which leads to the difference between the two, which needs to be advised by the boss.

There are several solutions to ORA-02266 errors

Scenario 1: disable constraints-truncate- enable constraints

You can refer to MOS's article "OERR: ORA-2266" unique/primary keys in table referenced by enabled foreign keys "Reference Note (Doc ID 19499.1)".

1. Find out the constraints of the main table

SELECT constraint_name

FROM user_constraints

WHERE table_name =''

AND constraint_type ='P'

SELECT *

FROM user_constraints

WHERE constraint_type ='R'

AND r_constraint_name =''

Or execute any of the following SQL to get the foreign key reference related to the primary key

Select a.constraint_type,a.table_name,a.status, b.table_name,b.column_name,b.constraint_name from user_constraints a

Inner join user_cons_columns b on a.constraint_name = b.constraint_name

Where a.ringing intact constraint 'primary key constraint name'

Select c.TABLE_NAME

From all_constraints p, all_constraints c

Where p.table_name = 'primary key constraint name'

And p.OWNER = SYS_CONTEXT ('USERENV',' CURRENT_SCHEMA')

And c.OWNER=SYS_CONTEXT ('USERENV',' CURRENT_SCHEMA')

And c.constraint_type ='R'

And p.CONSTRAINT_NAME = c.R_CONSTRAINT_NAME

two。 Delete constraint

SQL > alter table axi1 disable primary key cascade

Table altered.

3. Execute truncate

4. Enable constraint

Just note that when enable resumes the operation of the primary key, it does not automatically enable the foreign key. It requires manual enable foreign key.

SQL > alter table tbl_a enable primary key

Table altered.

SQL > alter table tbl_b enable constraint fk_b_a

Table altered.

Scenario 2:delete deletion

Using delete,DML operation, you can delete the main table normally, but it is not suitable for scenarios with a large amount of data.

On the implementation of truncate trigger ORA-02266 solution process is how to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.