BookmarkSubscribeRSS Feed
_Olga_
Calcite | Level 5

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;

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

_Olga_
Calcite | Level 5
I've edited the code.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

_Olga_
Calcite | Level 5

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User
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;
_Olga_
Calcite | Level 5

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?

Ksharp
Super User
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;
LinusH
Tourmaline | Level 20
@_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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3605 views
  • 0 likes
  • 4 in conversation