BookmarkSubscribeRSS Feed
Asbon
Obsidian | Level 7

Hi i'm using below query to gatter and update stats if stale_stats is "YES" then to "NO" status 

to gatter stats:-
proc sql;
create table stats as
select owner,
table_name,
partition_name,
subpartition_name,
stattype_locked,
stale_stats               /* YES or NO */
from pcds.dba_tab_statistics
where table_name = 'ADDR_TLNBR_MAINT_EVENT_C';
quit;
 
to update if stats is "YES" to  make it "NO":-

proc sql;
connect to oracle (user="&pcdsuser" orapw="&pcdspw" path="@pcds");
execute( BEGIN
DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME => %TSLIT ('FMT_TRAN_DBA'),
TABNAME => %TSLIT ('ADDR_TLNBR_MAINT_EVENT_C'),
PARTNAME => %TSLIT ('&_PARTITION_NAME.'),
GRANULARITY => %TSLIT ('PARTITION'),
CASCADE => TRUE,
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=> %TSLIT ('FOR ALL COLUMNS SIZE AUTO'),
FORCE=>TRUE);
END;
) by oracle;
disconnect from oracle;
quit;

 

I'm getting following error:

ERROR: ORACLE execute error: ORA-20001: &_OWNER. is an invalid identifierORA-06512: at "SYS.DBMS_STATS", line 24281ORA-06512: at
"SYS.DBMS_STATS", line 24332ORA-06512: at line 1.

 

can you please help in this.

 

Thanks.

5 REPLIES 5
SASKiwi
Opal | Level 21

I don't see any reference to &_OWNER in your posted code. It would help if you posted your complete SAS log including that error so we can see where it occurs.

Asbon
Obsidian | Level 7

below is complete log, only remove header part (from 1 to 86) to keep it short.
87 %macro ststs;
88
89 proc sql;
90 create table stats as
91 select owner,
2 The SAS System 23:44 Tuesday, August 1, 2023

92 table_name,
93 partition_name,
94 subpartition_name,
95 stattype_locked,
96 stale_stats /* YES or NO */
97 from pcds.dba_tab_statistics
98 where table_name = 'ADDR_TLNBR_MAINT_EVENT_C';
99 quit;
100
101 data stats_upd_ned;
102 set stats;
103 where upcase(trim(stale_stats))="YES";
104 run;
105
106 proc sql noprint;
107 select count(*) into :cnt from stats_upd_ned;
108 quit;
109
110 %if &cnt le 0 %then
111 %do;
112 %put "No RECORDS AVAILABLE";
113 %end;
114 %else
115 %do;
116
117 proc sql noprint;
118 select trim(OWNER), trim(TABLE_NAME),trim(PARTITION_NAME)
119 into
120 :_OWNER SEPARATED BY " ",
121 :_TABLE_NAME SEPARATED BY " ",
122 :_PARTITION_NAME SEPARATED BY " "
123 from stats_upd_ned
124 where trim(OWNER)='FMT_TRAN_DBA' and trim(TABLE_NAME) = 'ADDR_TLNBR_MAINT_EVENT_C';
125 quit;
126
127 %put &_OWNER.;
128 %put &_TABLE_NAME.;
129 %put &_PARTITION_NAME.;
130 /* options symbolgen;*/
131
132 %DO I=1 %TO &cnt;
133
134 proc sql;
135 connect to oracle (user="&pcdsuser" orapw="&pcdspw" path="@pcds");
136 execute( BEGIN
137 DBMS_STATS.GATHER_TABLE_STATS
138 (OWNNAME =>('FMT_TRAN_DBA'),
139 TABNAME =>('ADDR_TLNBR_MAINT_EVENT_C'),
140 PARTNAME =>%TSLIT(&_PARTITION_NAME.),
141 GRANULARITY =>('PARTITION'),
142 CASCADE => TRUE,
143 estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
144 method_opt=>('FOR ALL COLUMNS SIZE AUTO'),
145 FORCE=>TRUE);
146 END;
147 ) by oracle;
148 disconnect from oracle;
149 quit;
3 The SAS System 23:44 Tuesday, August 1, 2023

150
151 %end;
152 %end;
153 %mend ststs;
154
155 %ststs;


NOTE: Compressing data set WORK.STATS increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.STATS created, with 632 rows and 6 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.07 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 5299.87k
OS Memory 24228.00k
Timestamp 08/01/2023 11:51:40 PM
Step Count 16 Switch Count 3
Page Faults 0
Page Reclaims 503
Page Swaps 0
Voluntary Context Switches 50
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0


NOTE: There were 1 observations read from the data set WORK.STATS.
WHERE UPCASE(TRIM(stale_stats))='YES';
NOTE: The data set WORK.STATS_UPD_NED has 1 observations and 6 variables.
NOTE: Compressing data set WORK.STATS_UPD_NED increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 2567.87k
OS Memory 21928.00k
Timestamp 08/01/2023 11:51:40 PM
Step Count 17 Switch Count 2
Page Faults 0
Page Reclaims 512
Page Swaps 0
Voluntary Context Switches 24
Involuntary Context Switches 7
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 6202.56k
OS Memory 26024.00k
4 The SAS System 23:44 Tuesday, August 1, 2023

Timestamp 08/01/2023 11:51:40 PM
Step Count 18 Switch Count 0
Page Faults 0
Page Reclaims 248
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

SYMBOLGEN: Macro variable CNT resolves to 1
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 6211.96k
OS Memory 26024.00k
Timestamp 08/01/2023 11:51:40 PM
Step Count 19 Switch Count 0
Page Faults 0
Page Reclaims 252
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

SYMBOLGEN: Macro variable _OWNER resolves to FMT_TRAN_DBA
FMT_TRAN_DBA
SYMBOLGEN: Macro variable _TABLE_NAME resolves to ADDR_TLNBR_MAINT_EVENT_C
ADDR_TLNBR_MAINT_EVENT_C
SYMBOLGEN: Macro variable _PARTITION_NAME resolves to PTN_W20230730
PTN_W20230730
SYMBOLGEN: Macro variable CNT resolves to 1
SYMBOLGEN: Macro variable PCDSUSER resolves to aaaaaaaa
SYMBOLGEN: Macro variable PCDSPW resolves to xxxxxxxx
SYMBOLGEN: Macro variable _PARTITION_NAME resolves to PTN_W20230730
SYMBOLGEN: Macro variable VALUE resolves to PTN_W20230730
SYMBOLGEN: Macro variable VALUE resolves to PTN_W20230730
SYMBOLGEN: Macro variable S1 resolves to '"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable S2 resolves to "'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable V1 resolves to PTN_W20230730
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable V2 resolves to "PTN_W20230730"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable S2 resolves to "'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable S1 resolves to '"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable V3 resolves to 'PTN_W20230730'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
ERROR: ORACLE execute error: ORA-20000: Unable to analyze TABLE "FMT_TRAN_DBA"."ADDR_TLNBR_MAINT_EVENT_C" PTN_W20230730,
insufficient privileges or does not existORA-06512: at "SYS.DBMS_STATS", line 24270ORA-06512: at "SYS.DBMS_STATS", line
24332ORA-06512: at line 1.
5 The SAS System 23:44 Tuesday, August 1, 2023

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Statement not executed due to NOEXEC option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 3.92 seconds
user cpu time 0.01 seconds
system cpu time 0.09 seconds
memory 249.18k
OS Memory 19872.00k
Timestamp 08/01/2023 11:51:43 PM
Step Count 20 Switch Count 0
Page Faults 0
Page Reclaims 43
Page Swaps 0
Voluntary Context Switches 45
Involuntary Context Switches 60
Block Input Operations 0
Block Output Operations 0

156
157 %LET _CLIENTTASKLABEL=;
158 %LET _CLIENTPROCESSFLOWNAME=;
159 %LET _CLIENTPROJECTPATH=;
160 %LET _CLIENTPROJECTPATHHOST=;
161 %LET _CLIENTPROJECTNAME=;
162 %LET _SASPROGRAMFILE=;
163 %LET _SASPROGRAMFILEHOST=;
164
165 ;*';*";*/;quit;run;
166 ODS _ALL_ CLOSE;
167
168
169 QUIT; RUN;
170

SASKiwi
Opal | Level 21

Looks like you have a permissions problem reading certain Oracle system tables. You will have to talk to your Oracle DBA to fix that.

Quentin
PROC Star

I thought the purpose of %TSLIST is to add single quotes around a value?  I would expect to see something like:

 

OWNNAME => 'FMT_TRAN_DBA',
TABNAME => 'ADDR_TLNBR_MAINT_EVENT_C',
PARTNAME => %TSLIT(&_PARTITION_NAME),
GRANULARITY => 'PARTITION',

But I haven't used Oracle in years...

 

 

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
Patrick
Opal | Level 21

I believe you need to remove the single quotes as this is what %tslit() does for you.

Patrick_0-1690954292079.png

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 279 views
  • 2 likes
  • 4 in conversation