SAP tablespace monitoring

[Former Member]

Hi Gurus,

I'm using SAP ECC6, ORACLE 10.2.0 and HPUX B.11.23.

Is there a way to check and monitor the growth of SAP tablespace via OS level, perhaps via customized script and scheduled it in as a cronjob and email out its output information? Or is there other alternative ways?

Would appreciate for any of your help and advice.

Thank you in advance.

Cheers.

[former_member204746]

duplicated at

http://sap.ittoolbox.com/groups/technical-functional/sap-basis/sap-tablespace-monitoring-3810605

you were not happy with answer you got over there?

[sumi_susan2]

Hi,

This below script will get you the tablespace datafile, freespace ,availiable and autoextend or not.

set pagesize 100

column file_name format a32

column tablespace_name format a15

column status format a3 trunc

column t format 999,999.000 heading "Total MB"

column a format a4 heading "Aext"

column p format 990.00 heading "% Free"

SELECT df.file_name,

df.tablespace_name,

df. status,

(df.bytes/1024000) t,

(fs.s/df.bytes*100) p,

decode (ae.y,1,'YES','NO') a

FROM dba_data_files df,

(SELECT file_id,SUM(bytes) s

FROM dba_free_space

GROUP BY file_id) fs,

(SELECT file#, 1 y

FROM sys.filext$

GROUP BY file#) ae

WHERE df.file_id = fs.file_id

AND ae.file#(+) = df.file_id

ORDER BY df.tablespace_name, df.file_id;

column file_name clear

column tablespace_name clear

column status clear

column t clear

column a clear

column p clear

ttitle off

Regards,

Sam

[Former Member]

HI Cherry S,

Thanks for your response. Appreciate it.

The SQL query works great. Please see below.

Sample output:

/oracle/QAS/sapdata1/sr3700_1/sr PSAPSR3700 AVA 5,406.720 0.38 YES

3700.data1

1) Is there a way to display the complete path in the query?

For example:

/oracle/QAS/sapdata1/sr3_27/sr3.data27 PSAPSR3 AVA 10,240.000 42.01 YES

2) Would it be possible to execute this SQL using a shell script?

Appreciate for any of your help and advice.

Thanks.

Cheers.

Edited by: mart1n1 on Oct 15, 2010 8:33 AM

Edited by: mart1n1 on Oct 15, 2010 8:34 AM

[Former Member]

> 1) Is there a way to display the complete path in the query?

You should be able to set in shell script.

> 2) Would it be possible to execute this SQL using a shell script?

yes , it is possible to run as shell script. In AIX we have as below.

#!/usr/bin/ksh

. /oracle/<SID>/.profile

sqlplus "/ as sysdba" @/<path_of_your_sql_file> /

BTW the URL is working: http://help.sap.com/saphelp_nw70ehp1/helpdata/en/29/581630f72011d2952900a0c930df15/frameset.htm

[Former Member]

Hi Sunil Bujade,

Thanks for your response.

> 1) Is there a way to display the complete path in the query?

> You should be able to set in shell script.

Some clarifications, what I meant by the above statement is, when I executed the SQL query given by Cherry S, it does not display the complete path as the path line is brought over to the the next line, see below:

Output issue:

--> /oracle/QAS/sapdata1/sr3700_1/sr PSAPSR3700 AVA 5,406.720 0.38 YES

3700.data1

The complete path should be:

--> /oracle/QAS/sapdata1/sr3_27/sr3.data27

So, probably in SQL query, would need to adjust the output width / column to cater for this issue.

This is the part that I was asking earlier, any advice / help?

> 2) Would it be possible to execute this SQL using a shell script?

> yes , it is possible to run as shell script. In AIX we have as below.

It should the same method if I wanted to run it under HPUX B11.23 system, right?

Lastly, the URL is still does not display anything. I'm currenty using IE7 and have tried using Mozilla 3.6, but still the same result. Nothing being displayed. Not sure what happened.

Please kindly help and advice.

Thanks.

Cheers.

[Former Member]

change the sql query, find below syntax:

column file_name format a32

and replace with

column file_name format a45

Nothing harm if you try to create a script and execute as this is only a select statement.

finally, if you search for 'tablespace monitoring' in help.sap.com or http://help.sap.com/search/sap_trex.jsp you will get the results.

[Former Member]

Hi Sunil Bujade,

Thanks for your response.

I will check it out.

Cheers.

[Former Member]

You can write your own sql query to get the tablespace utilization.

But I suggest you configure CCMS and then from there you can send as an email to you follow:

http://help.sap.com/saphelp_nw70ehp1/helpdata/en/29/581630f72011d2952900a0c930df15/frameset.htm

[Former Member]

Hi,

In OS level you can not write any customized scripts to monitor the tablespaces. As the table spaces are the logiacal components and those are completely related to oracle and OS doesn't know about the oracle logical components.

So the answer is you can not monitor the tablespaces from the OS level .

You need to configure CCMS.

Also clarify me if i am wrong....

Thanks,

Chaitanya.

[Former Member]

Hi Sunil Bujade,

Thanks for your response.

However, I'm unable to see anything for your URL link. Just a blank screen.

-> http://help.sap.com/saphelp_nw70ehp1/helpdata/en/29/581630f72011d2952900a0c930df15/frameset.htm

Please advice.

Thanks.

Cheers.

Edited by: mart1n1 on Oct 15, 2010 7:38 AM