AskTom

Hi,

this is not my script but I found it very useful so I put it into my knowledge base.

rem *****************************************************************
clear screen
prompt ***************************************
prompt *                                     *
prompt *  This reveals the user tables with  *
prompt *  a column of a particular value.    *
prompt *                                     *
prompt *  Enter a column name:               *
prompt *                                     *
prompt ***************************************
accept in_column char
prompt ***************************************
prompt *                                     *
prompt *  Enter a value:                     *
prompt *                                     *
prompt ***************************************
accept in_value char
set echo     off
set recsep   wrapped
set termout  off
set feedback off
set heading  off
set linesize 80
set pagesize 0
set verify   off
spool fval.sql
select ’select distinct ‘||””||table_name||””||’
from ‘||table_name||’
where  upper (&in_column) = upper (’||”’&in_value”’||’);’
from  user_tab_columns
where column_name = upper (’&in_column’);
spool off
spool fval.txt
start fval.sql
spool off
set termout  on
set feedback 6
set heading  on
set pagesize 24
set verify   on
pause Done…  Press ENTER to view results
edit fval.txt

or solution from Tom Kyte.

Regards,

Tom

Hi,

my OMS suddenly stopped working. I wasn’t able to upload data from any monitored servers.
Agents told me that everuthink is ok but number of pendind messages were quite high.

oracle@db01:~> /opt/app/oracle/product/10.2/agent10g/bin/emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
—————————————————————
Agent Version     : 10.2.0.4.0
OMS Version       : 10.2.0.5.0
Protocol Version  : 10.2.0.4.0
…
Started at        : 2009-10-25 02:14:15
Started by user   : oracle
Last Reload       : 2009-10-26 13:21:34
Last successful upload                       : 2009-12-07 11:34:01
Total Megabytes of XML files uploaded so far :  1343.77
Number of XML files pending upload           :     1916
Size of XML files pending upload(MB)         :    46.33
Available disk space on upload filesystem    :    39.89%
Collection Status                            : Disabled by Upload Manager
Last successful heartbeat to OMS             : 2009-12-07 11:33:13
—————————————————————
Agent is Running and Ready
oracle@db01:~> /opt/app/oracle/product/10.2/agent10g/bin/emctl upload agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
—————————————————————
EMD upload error: Upload was successful but collections currently disabled - disk full

Other corresponding messages:
EMD upload error: Upload timed out before completion.
EMD upload error: Failed to upload file D0096435.xml: HTTP error.
Response received: ERROR-100|No space left on device

I found a lot of core files in OMS_HOME:
-rw——-   1 oracle oinstall 16265216 2009-12-07 14:22 core.24493
-rw——-   1 oracle oinstall 16265216 2009-12-07 14:22 core.24533
-rw——-   1 oracle oinstall 16265216 2009-12-07 14:22 core.24582
-rw——-   1 oracle oinstall 16265216 2009-12-07 14:22 core.24588
…

Solution:
I fount the problem. My access_log in Apache had more than 2GB size.
oracle@beta:/opt/app/oracle/product/10/oms10g/Apache/Apache/logs> du -hs .
2.3G    .
oracle@beta:/opt/app/oracle/product/10/oms10g/Apache/Apache/logs> ls -ltr access_log
-rw-r—–  1 oracle oinstall 2147483647 2009-12-04 21:23 access_log

Kindly delete them and upload data from agents. That’s all.

Metalink:
Problem: Core Dumps: Grid Control OMS generates huge amount of core.xxx files causing the system to halt [ID 419999.1]
How We Corrected a Disabled Agent Due to Disk Full [ID 754458.1]
Agent Upload To Oms Fails With Collections Disabled Due To Disk Full [ID 317242.1]

Regards,

Tom

Hi,
this is part from Julian Dyke’s blog. I put this selects there because are very usefull.

Listing All Events

Most events are numbered in the range 10000 to 10999. To dump all event messages in this range use

SET SERVEROUTPUT ON

DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE ‘%Message ‘||err_num||’ not found%’ THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/

On Unix systems event messages are in the formatted text file

$ORACLE_HOME/rdbms/mesg/oraus.msg

To print detailed event messages (Unix only) use the following script

event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done

Listing Enabled Events

To check which events are enabled in the current session

SET SERVEROUTPUT ON
DECLARE
l_level NUMBER;
BEGIN
FOR l_event IN 10000..10999
LOOP
dbms_system.read_ev (l_event,l_level);
IF l_level > 0 THEN
dbms_output.put_line (’Event ‘||TO_CHAR (l_event)||
‘ is set at level ‘||TO_CHAR (l_level));
END IF;
END LOOP;
END;
/

Regards,

Tom