We have SAS job which is connect to Oracle to load data using Libname. But this connection is creating unnecessary process in Oracle environment and that process is creating and executing the below query:
SELECT OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS OBJ
WHERE (OBJ.OWNER = 'SCHEMA')
AND (OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE = 'VIEW'
And this process is not completing even though actual SAS job is completed in SAS environment.
It is causing some performance issues in Oracle side.
Could some one help me, how we can restrict to not to create Oracle process which is creating above query.
Thanks in Advance!!
Durga.
I think you are missing a right-hand bracket at the end of your query.
Have you tried running this query in an Oracle query tool such as SQL Plus or Toad directly on Oracle and if so how long did it take?
Try using SQL Passthru in SAS and you should get the same performance as in SQL Plus or Toad:
Proc sql;
connect to oracle (user = ‘userid’ password = ‘password’
path = schema)
select * from connection to oracle (
SELECT OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS OBJ
WHERE (OBJ.OWNER = 'SCHEMA')
AND (OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE = 'VIEW')
);
Disconnect from oracle;
Quit;
If you want to track down the source of a sql submission to the database use the sastrace option. Try like this:
options sastrace=',,,d' sastraceloc=log;
Thank You So much for ur responce. It really helpful.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.