Parallel_Server_Issue

Hi expert,

i'm facing below issue while executing my query since object is exists there.

DECLARE
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P078
ORA-08103: object no longer exists
ORA-06512: at "SUPERUSER.F_IDRP_WRITE_TO_FILE", line 33
ORA-06512: at line 1
ORA-06512: at line 25

Please suggest for optimal way to avoid this kind of error.

# Please let me know if required any additional info .

thanks in advance
MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Query please?
0
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginnerAuthor Commented:
HI Pawan,
I'm tried to Exporting data from  "IDRP Eligibility data" from  table "STSC.UDT_SKU" to a flat file .
--------------------------------------------------------------------------------------------------------------------------------------------
Please find the below query FYI !!
-------------------------------------------------------------------------------------------------
DECLARE
    v_cnt_ntt   superuser.t_idrp_num_tbl;
    v_totcnt    PLS_INTEGER;
        v_f_name    VARCHAR2(100);  -- IPS-2010

BEGIN

     SELECT NAME||'_IDRP_ITEM_ELIGIBILITY_EXTRACT' INTO v_f_name FROM v$database;               -- IPS-2010

   
        SELECT *
       BULK COLLECT INTO v_cnt_ntt
       FROM TABLE(
                  superuser.f_idrp_write_to_file
                     (
                      p_rc         => CURSOR(
                                             SELECT /*+ PARALLEL(16) */ ITEM_ELIGIBILITY_EXTRACT FROM SUPERUSER.V_ITEM_ELIGIBILITY_EXTRACT
                                             ),
                      p_filename   =>  v_f_name,
                      p_limit      => 10000,
                      p_filedir    => 'SHARE_DIR'
                      )
                   );

  -- Print counts of extracted rows
   SELECT sum(column_value)
    INTO v_totcnt
    FROM TABLE(v_cnt_ntt);

  superuser.PG_IDRP_GLOBAL.p_log_msg('Total Number of exported rows: ' || v_totcnt);

END;
/
0
Mark GeerlingsDatabase AdministratorCommented:
Are you sure that the "bulk collect" and the "/*+ PARALLEL(16) */" hint both add value for a process that simply extracts data from an Oracle table to an ASCII file?

If you try this process without those two features, what happens?  Is it noticeably slower that way, or not?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginnerAuthor Commented:
Thanks Mark,

As per architect team setup i have to use both "bulk collect" and the "/*+ PARALLEL(16) */ . Is there any other way where i can do workaround
is order to fix this issue please let me know .

thanks again mark Your suggestion is really appreciate .
0
Mark GeerlingsDatabase AdministratorCommented:
If your architect team knows how to use Oracle better than I do, then you don't need my assistance.  I wanted to know if you had actually tested a query for this without both the "bulk collect" and "parallel" options.  

Yes, I agree that those options can help some Oracle processes perform faster in some systems.  I'm not convinced that they help this process run any faster though.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
The error you originally posted tells you where the error is from:
ORA-06512: at "SUPERUSER.F_IDRP_WRITE_TO_FILE", line 33

To help diagnose what might be causing it, we would need to see that code.

I also agree with what Mark sort of hinted at.  If all you want to do is get data from an Oracle table into a file, there are MUCH easier ways to go about it that what you are doing.  I also have no idea why you need the PL/SQL table for any of it.

Depending on how superuser.t_idrp_num_tbl is declared, you also might not need the SELECT to get the count.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.