Re: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor

I first built the workaround with no_merge, so I can do at least a regression test.
The regression took about an hour. (should take about 25 minutes .In regression, we have only a few data. But at least no crashes or any Ora-00600 errors.).
Looks like the upgrading will make us headache.

If the regression is over, I'll start a performance test see which SQL become slower.

Your suggestion sounds interesting concerning the performance.

Thanks

Am Di., 29. Jan. 2019 um 14:04 Uhr schrieb kathy duret < katpopins21_at_yahoo.com>:

> Didn't read the thread and someone may have suggested this already but we
> have turned off the adaptive tuning feature in 12.1 .
>
> You might try turning it off in a lower environment and see how things
> perform afterwards.
>
> I think Oracle is "retooling" this feature in the future. We are skipping
> 12.2 and going to 12.3 due to some bugs.
>
> Good Luck
>
>
> Kathy Duret
>
>
>
> On Tuesday, January 29, 2019, 4:29:04 AM CST, Ahmed <gherrami_at_gmail.com>
> wrote:
>
>
> A colleague came up with the idea to use a no_merge hint. Then everything
> works again. Very strange.
>
> CURSOR get_fks(par_target_table user_tables.table_name%TYPE)
> IS
> WITH
> user_constr AS
> (
> SELECT /*+ no_merge */ *
> FROM all_constraints
> WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
> )
> SELECT r.constraint_name,
> r.table_name
> FROM user_constr r,
> user_constr t
> WHERE t.table_name = par_target_table
> AND t.constraint_type = 'P'
> AND t.constraint_name = r.r_constraint_name
> ;
>
> Regards
> Ahmed Fikri
>
> Am Di., 29. Jan. 2019 um 10:33 Uhr schrieb Ahmed <gherrami_at_gmail.com>:
>
> Yes the schema running the code is the same as the schema owning the tables
>
> That's what I did:
>
> CREATE USER tester IDENTIFIED BY tester QUOTA 10M ON USERS;
> GRANT CREATE SESSION TO tester;
> GRANT CREATE TABLE TO tester;
> -- after loggon as tester
> CREATE TABLE PARENT(ID NUMBER PRIMARY KEY);
> CREATE TABLE PARENT(ID NUMBER PRIMARY KEY);
> CREATE TABLE CHILD(ID NUMBER, parent_id NUMBER ,CONSTRAINT FOREIGN KEY
> REFERENCES PARENT(ID));
>
> DECLARE
> -- table that has Foreignkey pointing on it
> v_tbl_name VARCHAR2(30) := 'PARENT';
> CURSOR get_massdata_tableinfo
> IS
> SELECT v_tbl_name table_name FROM dual
>
> ;
> CURSOR get_fks(par_target_table user_tables.table_name%TYPE)
> IS
> WITH
> user_constr AS
> (
> SELECT *
> FROM all_constraints
> WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
> )
> SELECT r.constraint_name,
> r.table_name
> FROM user_constr r,
> user_constr t
> WHERE t.table_name = par_target_table
> AND t.constraint_type = 'P'
> AND t.constraint_name = r.r_constraint_name
> ;
> BEGIN
> FOR crec IN get_massdata_tableinfo
> LOOP
> --
> dbms_output.put_line('Table Name ' || crec.table_name);
> -- disable FK´s pointing to table
> FOR rec IN get_fks(crec.table_name) --no rows in 12.2.0.1 (but it
> works in 11.2.0.4 and 12.1.0.2)
> LOOP
> dbms_output.put_line('ALTER TABLE ' || rec.table_name ||
> ' DISABLE CONSTRAINT ' || rec.constraint_name);
>
> END LOOP;
> END LOOP;
>
> END;
>
> I get AS Output:
> Table Name PARENT
>
> But on 11.2.0.4 or 12.1.0.2-- the same steps I get as Output:
> Table Name PARENT
> ALTER TABLE CHILD DISABLE CONSTRAINT SYS_C00444321
>
> This confuses me
>
> The context of these error is an attempt to upgrade our software from
> 11.2.0.4 to 12.2.0.1.
> Everything works fine on 11.2.0.4. In the example block above I try to
> summarize only the error here in this list.
>
> we have patched the following patch.
>
> ARU platform id: 226
>
> ARU platform description:: Linux x86-64
>
>
>
> Installed Top-level Products (1):
>
>
>
> Oracle Database 12c
> 12.2.0.1.0
>
> There are 1 products installed in this Oracle Home.
>
>
>
>
>
> Interim patches (1) :
>
>
>
> Patch 28662603 : applied on Sat Nov 24 14:07:07 CET 2018
>
> Unique Patch ID: 22485591
>
> Patch description: "Database Oct 2018 Release Update : 12.2.0.1.181016
> (28662603)"
> Created on 5 Oct 2018, 03:26:32 hrs PST8PDT
>
> Regards
> Ahmed Fikri
>
> Am Di., 29. Jan. 2019 um 10:05 Uhr schrieb Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk>:
>
>
> It works for me on an unpatched 12.2.0.1
>
> In my case I log in, create a parent and child table, then run your code.
> Is the schema running your code the same as the schema owning the tables ?
> Could there be a difference in privileges rather than a bug.
>
> Regards
> Jonathan Lewis
>
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Ahmed <gherrami_at_gmail.com>
> Sent: 28 January 2019 14:57
> To: ORACLE-L
> Subject: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor
>
> Hi,
>
> The following block should provide the commands to disable the foreign key
> pointing on a table. It works wonderfully in 11.2.0.4 and 12.1.0.2 but not
> on 12.2.0.1. Does not trigger an error but also no results. You can test
> it, just replace 'MYTABLE' with a table that has foreign key pointing on it.
>
> DECLARE
> -- table that has Foreignkey pointing on it
> v_tbl_name VARCHAR2(30) := 'MYTABLE';
> CURSOR get_massdata_tableinfo
> IS
> SELECT v_tbl_name table_name FROM dual
>
> ;
> CURSOR get_fks(par_target_table user_tables.table_name%TYPE)
> IS
> WITH
> user_constr AS
> (
> SELECT *
> FROM all_constraints
> WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
> )
> SELECT r.constraint_name,
> r.table_name
> FROM user_constr r,
> user_constr t
> WHERE t.table_name = par_target_table
> AND t.constraint_type = 'P'
> AND t.constraint_name = r.r_constraint_name
> ;
> BEGIN
> FOR crec IN get_massdata_tableinfo
> LOOP
> --
> dbms_output.put_line('Table Name ' || crec.table_name);
> -- disable FK´s pointing to table
> FOR rec IN get_fks(crec.table_name) --no rows in 12.2.0.1 (but it
> works in 11.2.0.4 and 12.1.0.2)
> LOOP
> dbms_output.put_line('ALTER TABLE ' || rec.table_name ||
> ' DISABLE CONSTRAINT ' || rec.constraint_name);
>
> END LOOP;
> END LOOP;
>
> END;
>
> If I call the SQL directly, then I get records:
>
> WITH
> user_constr AS
> (
> SELECT *
> FROM all_constraints
> WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
> )
> SELECT r.constraint_name,
> r.table_name
> FROM user_constr r,
> user_constr t
> WHERE t.table_name = 'MY_TABLE'
> AND t.constraint_type = 'P'
> AND t.constraint_name = r.r_constraint_name
>
> Is this a bug or have I just missed something?
>
> Regards
> Ahmed Fikri
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l