Re: ADRCI
Date: Sat, 16 Jan 2016 19:27:31 -0500
Message-ID: <569ADFF3.7060809_at_gmail.com>
Hmmmm, according to what you have shown me, V$DIAG_ALERT_EXT produces more information than X$DBGALERT. Also, I am inclined to use V$ tables whenever available. X$ tables are usually not documented, which makes them subject to change without notice.
Regards
On 01/15/2016 05:23 PM, Sayan Malakshinov wrote:
> v$diag_alert_ext calls x$diag_ALERT_EXT, that's different function and
> it's heavier than X$DBGALERTEXT.
>
> SQL> set timing on
> SQL> select count(*) from X$DBGALERTEXT;
>
> COUNT(*)
> ----------
> 90615
>
> Elapsed: 00:00:02.40
> SQL> select count(*) from v$diag_alert_ext;
>
> COUNT(*)
> ----------
> 6238249
>
> Elapsed: 00:03:26.04
>
> Btw, x$diag_ALERT_EXT has fixed index(although it's not very useful),
> but X$DBGALERTEXT hasn't.
>
> SQL> _at_fixed_indexes.sql x$diag_ALERT_EXT
>
> TABLE_NAME INDEX_NUMBER COLUMN_POSITION COLUMN_NAME
> ------------------------------ ------------ ---------------
> ------------------------------
> X$DIAG_ALERT_EXT 3 0 ADR_PATH_IDX
>
>
> On Sat, Jan 16, 2016 at 1:06 AM, Mladen Gogala
> <gogala.mladen_at_gmail.com <mailto:gogala.mladen_at_gmail.com>> wrote:
>
> Hi Nassyam,
> Why do you use X$ table and not v$diag_alert_ext? The latter is
> documented and accessible from any schema with enough privilege,
> not just SYS. V$DIAG_ALERT_EXT is just another name for
> x$dbgalertext, those two "tables" are essentially equivalent.
> Regards
>
>
> On 01/15/2016 12:16 PM, Nassyam Basha wrote:
>> I use SQL Query for same information to avoid using various
>> utilities. Here is the query below.
>> Again if you are looking only from ADRCI then like above Rajest
>> mentioned.
>>
>> col ORIGINATING_TIMESTAMP for a40
>> col MESSAGE_TEXT for a80
>> set linesize 500
>> SELECT
>> originating_timestamp,
>> message_text
>> FROM
>> sys.x$dbgalertext
>> WHERE
>> message_text LIKE '%&Text%';
>>
>> Thank You.
>>
>> On Fri, Jan 15, 2016 at 10:32 PM, Rajesh Aialavajjala
>> <r.aialavajjala_at_gmail.com <mailto:r.aialavajjala_at_gmail.com>> wrote:
>>
>> Woody,
>>
>> Don Seiler (a frequent contributor to this list) had this
>> example in
>> http://www.nocoug.org/download/2011-08/Seiler_Pythian_Learning_to_Love_the_ADR.pdf
>>
>>
>> "show alert -p "message_text like '%ORA%' and
>> originating_timestamp >= systimestamp-30" -term" indicating
>> you can use "AND" in adrci...
>>
>> Thanks,
>>
>> --Rajesh
>>
>> On Fri, Jan 15, 2016 at 11:59 AM, Seth Miller
>> <sethmiller.sm_at_gmail.com <mailto:sethmiller.sm_at_gmail.com>> wrote:
>>
>> The doc is terrible in this case. I had to figure this
>> one out myself.
>>
>> show alert -p "MESSAGE_TEXT LIKE 'ORA-%' and
>> ORIGINATING_TIMESTAMP > systimestamp - 1"
>>
>> Seth Miller
>>
>> On Fri, Jan 15, 2016 at 10:28 AM, Woody McKay
>> <woody.mckay_at_gmail.com <mailto:woody.mckay_at_gmail.com>> wrote:
>>
>> Hi, (12.1.0.2 in win64)
>>
>> Does anyone have an example of using adrci to view
>> ORA- errors after a specific date/time?
>>
>> I have a good command for seeing all ORA- errors in
>> the alert log, but would like to check it after a
>> specific data and time. The docs don't show if AND or
>> OR can be used for two predicates
>>
>> adrci> show alert -p "MESSAGE_TEXT LIKE 'ORA-%'"
>>
>> Was wondering if somehow I can add -p
>> originating_timestamp or something like that.
>>
>> My overall goal is to monitor the DB alert log for
>> ORA- errors and send a notification on new errors. I
>> started a perl, but got excited when I saw ADRCI.
>> OEM cost more than leadership will spend.
>>
>> Thoughts?
>>
>> --
>> Sincerely,
>>
>> Woody
>>
>>
>>
>>
>>
>>
>> --
>> Nassyam Basha.
>> *Oracle Database Consultant*| *Pythian * <http://www.pythian.com/>
>> ACED Profile
>> <https://apex.oracle.com/pls/apex/f?p=19297:4:::NO:4:P4_ID:13140>
>> Oracle 11g Certified Master
>> <http://education.oracle.com/education/otn/NassyamBasha.htm>
>> Co-Author: Oracle Data Guard 11gR2
>> <http://www.amazon.in/Oracle-Guard-11gR2-Administration-Beginners/dp/1849687900>
>> Co-founder of Oraworld-team <http://www.oraworld-team.com>
>>
>> Facebook <https://www.facebook.com/nassyambasha> Twitter
>> <https://twitter.com/oracle_ckpt> LinkedIn
>> <https://in.linkedin.com/in/nassyambasha> Google +
>> <https://plus.google.com/+NassyamBasha> CKPT Blog
>> <http://www.oracle-ckpt.com/>
>>
>>
>
>
> --
> Mladen Gogala
> Oracle DBA
> http://mgogala.freehostia.com
>
>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 17 2016 - 01:27:31 CET