how to dynamically query oracle from SAPGUI

[Former Member]

hi all,

there are so many facilities available in SAPGUI to query the database for checking it status etc etc. but these queries i guess are based on the last database check done using db13. if it is true can any body tell me how can i dynamically query the database as and when required. eg to check the status of the datafile etc.

if i check the "space statistics" in db02 is is based on the db13 database check. "refresh" is not feasible as it takes much longer time and puts load on produciton system.

regards

Aijaz

[Former Member]

Try the SQVI transaction. This opens up an interactive SQL Viewer which interactivelly lets you create queries and give the results in various desired output formats.

Hope this helps

Regards

Lokesh

[Former Member]

Hi,

you can execute RSORADJV this report in se38 and can run that query.

and SYS user is having very extensive authorizations.

i think this answers to you question.

[Former Member]

thanks Kasi,

it works for me. you are genius. By the way where i can find the documentation for understanding the st03/st04 parameters.

regards

Aijaz

[mike_howles4]

That's interesting that this worked, my understanding was that dba_data_files was not a public or sys schema owned table. That RSORADJV is pretty cool though, didn't know it was out there.

[Former Member]

Aijaz,

Careful with RSORADJV: in a relatively recent SP (see note 1523173) the program is disabled (filled with code simply displaying a message that the report is obsolete). This was done to "reduce maintenance efforts". Exceptionally it might be a good idea to create a customer (Z) copy of the program if you don't want to lose the functionality.

Regards,

Mark

[former_member204746]

you must schedule SAP_PERFORMANCE_COLLECTOR job on an hourly basis for this to get updated which run program RSCOLL00. then, only at 7am and 7pm, it also updates DB02 stats.

you can get near-realtime datafile info with RZ20

[Former Member]

hi Eric

Running this report will unnecessary put load on the server and sometimes my requirement is very simple like i need to run

SQL> select sum(bytes),file_id from dba_data_files group by file_id;

is there a way to get this information from sap.

regards

Aijaz

[former_member204746]

you can write your own ABAP program if you want. if you want a SAP standard answer, re-read my answer.

[mike_howles4]

You could do a cost analysis on a SQL Statement to get to a screen where you can put in a straight SQL Statement (I think ST04 can get you there somehow, I don't know, I've move from Basis to BI, so I can't check to confirm if that's the right transaction). But I don't know if it will allow you access to non-schema owned tables such as dba_data_files.

Your best bet would be to have your Oracle DBA or OS or Basis group write a simple SQL query that is triggered at the OS level via sqlplus and maybe tie it to a command you could run ad-hoc via immediate job execution.

[fidel_vales]

hi,

on the DBA cockpit there is an option called "SQL Editor" where you can issued limited queries ( only to tables/views owned by SYS )

Performance -> Additional Functions -> SQL Command Editor

On ST04n there is a similar option

Additional Functions -> Arbitrary Monitoring