topic Re: Error in SAS in SAS Programming

Error in SAS

_Olga_ — Tue, 22 Mar 2016 11:46:17 GMT

Hello!

The code below causes ERROR: ORACLE execute error: ORA-12801: error signaled in parallel query server P029 ORA-01652: unable to extend temp segment by 4096 in tablespace SAS.

How to fix this error?

proc sql;

connect to oracle (PATH=mypath USER='sas' PASSWORD="mypass");

execute(create table table1 as select ...) by oracle;

execute(create table table2 as select ...) by oracle;

execute(create table table3 as select ...) by oracle;

execute(create table table4 as select ...) by oracle;

execute(create table table5 as select ...) by oracle;

execute(create table table6 as select ...) by oracle;

execute(create table table7 as select ...) by oracle;

quit;

Re: Error in SAS

RW9 — Tue, 22 Mar 2016 11:39:38 GMT

Hi,

Well first step is to break that SQL out into separate blocks and see which bit of code is causing the problem. I can't tell from that long list of code. Once you identify which bit of code is causing the problem, run it on Toad, or SQL developer - i.e. in the actual database iteself. It looks from that error message that the problem is on the database, not SAS, so would need to be fixed there. There are various things wrong with your code however - has it been tested at all? Just from a quick glance:

This code:

(

create table bmv_telebank_tmp as

select distinct

EID,

FIO_SEARCH,

CAMP,

RECALC_DATE,

DATE_UPTO,

O_CARD_SALARY,

LAST_ANKETA_REF,

LAST_ANKETA_BR,

OFFER_ID,

OFFER_STATUS_ID,

OFFER_STATUS_CD,

FD,

max(FD) over (partition

by FIO_SEARCH, OFFER_STATUS_CD) max_FD from bmv_tlb_tmp

/*where FD=MAX_FD*/

)

Is invalid. Missing bracket after (partition. No from clause, by in wrong place as there is no merge.

This code:

where

1 = 1 and

Is never a good idea, it will merge every record with every reocord, i.e. number of obs * number of obs.

Are you familiar with SQL? If not, then I would suggest extracting all data to SAS and then doing the rest of the processing there.

Re: Error in SAS

_Olga_ — Tue, 22 Mar 2016 11:54:35 GMT

I've edited the code.

Re: Error in SAS

RW9 — Tue, 22 Mar 2016 12:05:50 GMT

Yes, but that doesn't really help any. You need to test each SQL clause, in the database. One of those clauses is either incorrect code, or uses too much resource. I can't tell you which it is, you need to test it step by step in an Oracle app - Toad or SQL Developer. Once you find the bit of code which does it, fix it, or fix the database. It is not a SAS question. A search on Google responds:

http://www.dba-oracle.com/t_ora_12801_parallel_query.htm

https://community.oracle.com/thread/2402635?tstart=0

Re: Error in SAS

Ksharp — Wed, 23 Mar 2016 01:54:46 GMT

Don't use EXECUTE when you are using querying statement (SELECT). Use Pass through instead. proc sql; connect to oracle (PATH=mypath USER='sas' PASSWORD="mypass"); create table want as select * from connection to oracle (select * from ora.x); quit;

Re: Error in SAS

_Olga_ — Wed, 23 Mar 2016 11:41:25 GMT

Yes, it's not error in SAS, but in Oracle.
I tried to create table mytest in PL/SQL Developer, but source table bmv_tlb_tmp is big (it has 288'304'610 records).

I've tried to reset parameter sort_area_size to 1048576000 or to 204857600, but it cause error:
ORA-12801: error signaled in parallel query server P012
ORA-01652: unable to extend temp segment by 4096 in tablespace SAS

Code:
alter session set sort_area_size=204857600;
create table mytest as
select distinct
             EID,
             FIO_SEARCH,
             CAMP,
             RECALC_DATE,
             DATE_UPTO,
             O_CARD_SALARY,
             LAST_ANKETA_REF,
             LAST_ANKETA_BR,
             OFFER_ID,
             OFFER_STATUS_ID,
             OFFER_STATUS_CD,
             FD,
             max(FD) over (partition by FIO_SEARCH, OFFER_STATUS_CD) max_FD from bmv_tlb_tmp;

How to fix the error?

Re: Error in SAS

RW9 — Wed, 23 Mar 2016 11:53:50 GMT

Sorry, what is it your trying to do with the query? This:
max(FD) over (partition by FIO_SEARCH, OFFER_STATUS_CD) max_FD

Is your problem. You are not using any groupings, and it seems your trying to get the max of a subgrouping, which probably means the SQL processor is trying to create numerous copies of the data and merge them. Its hard to see what your trying to do. You may also want to check out SQL or Oracle specific forums, as partition by is not base SQL.

Re: Error in SAS

_Olga_ — Wed, 23 Mar 2016 11:55:00 GMT

I need to create table mytest not in SAS, but in Oracle (see my new comment above). It's gives the same error:

ORA-12801: error signaled in parallel query server P012
ORA-01652: unable to extend temp segment by 4096 in tablespace SAS

Reset of sort_area_size doesn't help.

Any suggessions?

Re: Error in SAS

Ksharp — Thu, 24 Mar 2016 00:47:16 GMT

If you want create table at Oracle side, then should use EXECUTE statement. proc sql; connect to oracle (PATH=mypath USER='sas' PASSWORD="mypass"); execute(create table table1 x varchar(20),........... ) by oracle;

Re: Error in SAS

LinusH — Thu, 24 Mar 2016 06:00:31 GMT

@_Olga_, I that should be clear now that you are facing an Oracle problem, not a SAS one. Seek help in Oracle forums, your Oracle DBAs instead.