DATA Step, Macro, Functions and more

Error in SAS

Reply
Occasional Contributor
Posts: 5

Error in SAS

[ Edited ]

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;

Super User
Super User
Posts: 7,401

Re: Error in SAS

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.

Occasional Contributor
Posts: 5

Re: Error in SAS

I've edited the code.
Super User
Super User
Posts: 7,401

Re: Error in SAS

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

 

Occasional Contributor
Posts: 5

Re: Error in SAS

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?

Super User
Super User
Posts: 7,401

Re: Error in SAS

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.

Super User
Posts: 9,681

Re: Error in SAS

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;
Occasional Contributor
Posts: 5

Re: Error in SAS

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?

Super User
Posts: 9,681

Re: Error in SAS

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;
Super User
Posts: 5,257

Re: Error in SAS

@_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.
Data never sleeps
Ask a Question
Discussion stats
  • 9 replies
  • 550 views
  • 0 likes
  • 4 in conversation