RE: monitoring multiple databases using PL/SQL -- follow-up question
Point 3 sounds correct..
Make sure that you also *monitor the monitor*!! Even if you just set up AT
job or something that runs from a DBA desktop every 10-20 minutes, connects
to the instance and "select 'OK' from dual;" to test the connection.. If it
fails, flag an error through something like "net send" or email..
HTH
Mark
===
Mark Leith | T: +44 (0)1905 330 281
Sales & Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: [EMAIL PROTECTED]
===
http://www.cool-tools.co.uk
Maximising throughput & performance
-Original Message-
[EMAIL PROTECTED]
Sent: 24 April 2002 23:04
To: Multiple recipients of list ORACLE-L
question
Thanks all who replied to my E-mail.
Right now I am still leaning toward having a centralized monitoring script
running in a admin database because -
1. Ease of administration
2. We don't need anything fancy, don't need application level monitorin, so
the requirements for different databases are similar.
3. If we use PL/SQL jobs and monitoring tables then all of our scripts can
be backed up as part of the database backup. So if anything happens to the
monitoring server/db all we have to do is recovering the database. We can
even restore the monitoring database to another server if we need to.
I am not certain if point 3 is correct so any thoughts on this?
Dennis Meng
Database Administrator
Focal Communications Corp.
Stephane
Faroult To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Subject: Re: monitoring
multiple databases using PL/SQL
Sent by:
root@fatcity.
com
04/23/02
08:03 PM
Please
respond to
ORACLE-L
Ypu may find the following paper of interest:
http://www.oriole.com/papers/monitor.html
A few years old, but sits somewhere in the middle as Chris suggests. And
you can use all of his scripts :-).
"Grabowy, Chris" wrote:
>
> Dennis,
>
> True, but you should also consider...
>
> "In a decentralized configuration, the monitoring software or scripts
reside
> with the database. This complicates maintenance, but allows for higher
> monitoring availability."
>
> I believe that your better off somewhere in the middle.
>
> You can host a set of PL/SQL procs in a "monitoring" schema on each
database
> server to check the basics of the database, and report back to you. In
this
> configuration, you can enhance and add features to the development
version,
> and make sure it works, before updating all the databases. Du'oh!!
> Configuration Management!!!
>
> And then you can host specific "are you up/bogus login" scripts on a
central
> host.
>
> Generally, your Oracle database stays up...since it's not SQL
> Server...du'oh!!! The majority of the database problems are in the
> database, ie. maxextents, no space, performance, blah, blah...there is a
> list in my paper.
>
> Don't forget to collect all this monitoring data in some table(s), as it
> will become useful.
>
> And be sure to CC your boss on all the benefits your monitoring services
> provide you. Hopefully, some funding will eventually be thrown your way
to
> get a decent monitoring product.
>
> Also, recently I saw a demo of a monitoring product called AutoDBA. It
is
> very slick, almost like having a junior DBA doing all the boring,
> monitoring, dirty work. Except it doesn't get your coffee for you...or
wash
> your car...or...
>
> HTH.
>
> Chris Grabowy
>
> DISCLAIMER: I am not in any way associated with the makers of AutoDBA.
My
> paper also includes a list of other monitoring products.
>
> -Original Message-
> Sent: Tuesday, April 23, 2002 1:53 PM
> To: Multiple recipients of list ORACLE-L
>
> >From 'Oracle Database Monitoring for the Beginner' (pdf) - Chris Grabowy
>
> "In a centralized configuration, the monitoring software scripts reside
on
> one server. This obviously makes maintenance easier, but if the hosting
> server fails then there is no (more) monitoring of the databases"
>
> regards
> Madhu
>
> >From: [EMAIL PROTECTED]
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >Subject: monitoring multiple databases using PL/SQL
> >Date: Tue, 23 Apr 2002 08:00:33 -0800
> >
> >Greetings -
> >I am planning to centralize our Oracle monitoring process by using one
> >PL
Re: monitoring multiple databases using PL/SQL -- follow-up question
Thanks all who replied to my E-mail. Right now I am still leaning toward having a centralized monitoring script running in a admin database because - 1. Ease of administration 2. We don't need anything fancy, don't need application level monitorin, so the requirements for different databases are similar. 3. If we use PL/SQL jobs and monitoring tables then all of our scripts can be backed up as part of the database backup. So if anything happens to the monitoring server/db all we have to do is recovering the database. We can even restore the monitoring database to another server if we need to. I am not certain if point 3 is correct so any thoughts on this? Dennis Meng Database Administrator Focal Communications Corp. Stephane Faroult To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: monitoring multiple databases using PL/SQL Sent by: root@fatcity. com 04/23/02 08:03 PM Please respond to ORACLE-L Ypu may find the following paper of interest: http://www.oriole.com/papers/monitor.html A few years old, but sits somewhere in the middle as Chris suggests. And you can use all of his scripts :-). "Grabowy, Chris" wrote: > > Dennis, > > True, but you should also consider... > > "In a decentralized configuration, the monitoring software or scripts reside > with the database. This complicates maintenance, but allows for higher > monitoring availability." > > I believe that your better off somewhere in the middle. > > You can host a set of PL/SQL procs in a "monitoring" schema on each database > server to check the basics of the database, and report back to you. In this > configuration, you can enhance and add features to the development version, > and make sure it works, before updating all the databases. Du'oh!! > Configuration Management!!! > > And then you can host specific "are you up/bogus login" scripts on a central > host. > > Generally, your Oracle database stays up...since it's not SQL > Server...du'oh!!! The majority of the database problems are in the > database, ie. maxextents, no space, performance, blah, blah...there is a > list in my paper. > > Don't forget to collect all this monitoring data in some table(s), as it > will become useful. > > And be sure to CC your boss on all the benefits your monitoring services > provide you. Hopefully, some funding will eventually be thrown your way to > get a decent monitoring product. > > Also, recently I saw a demo of a monitoring product called AutoDBA. It is > very slick, almost like having a junior DBA doing all the boring, > monitoring, dirty work. Except it doesn't get your coffee for you...or wash > your car...or... > > HTH. > > Chris Grabowy > > DISCLAIMER: I am not in any way associated with the makers of AutoDBA. My > paper also includes a list of other monitoring products. > > -Original Message- > Sent: Tuesday, April 23, 2002 1:53 PM > To: Multiple recipients of list ORACLE-L > > >From 'Oracle Database Monitoring for the Beginner' (pdf) - Chris Grabowy > > "In a centralized configuration, the monitoring software scripts reside on > one server. This obviously makes mainte
Re: monitoring multiple databases using PL/SQL
Ypu may find the following paper of interest: http://www.oriole.com/papers/monitor.html A few years old, but sits somewhere in the middle as Chris suggests. And you can use all of his scripts :-). "Grabowy, Chris" wrote: > > Dennis, > > True, but you should also consider... > > "In a decentralized configuration, the monitoring software or scripts reside > with the database. This complicates maintenance, but allows for higher > monitoring availability." > > I believe that your better off somewhere in the middle. > > You can host a set of PL/SQL procs in a "monitoring" schema on each database > server to check the basics of the database, and report back to you. In this > configuration, you can enhance and add features to the development version, > and make sure it works, before updating all the databases. Du'oh!! > Configuration Management!!! > > And then you can host specific "are you up/bogus login" scripts on a central > host. > > Generally, your Oracle database stays up...since it's not SQL > Server...du'oh!!! The majority of the database problems are in the > database, ie. maxextents, no space, performance, blah, blah...there is a > list in my paper. > > Don't forget to collect all this monitoring data in some table(s), as it > will become useful. > > And be sure to CC your boss on all the benefits your monitoring services > provide you. Hopefully, some funding will eventually be thrown your way to > get a decent monitoring product. > > Also, recently I saw a demo of a monitoring product called AutoDBA. It is > very slick, almost like having a junior DBA doing all the boring, > monitoring, dirty work. Except it doesn't get your coffee for you...or wash > your car...or... > > HTH. > > Chris Grabowy > > DISCLAIMER: I am not in any way associated with the makers of AutoDBA. My > paper also includes a list of other monitoring products. > > -Original Message- > Sent: Tuesday, April 23, 2002 1:53 PM > To: Multiple recipients of list ORACLE-L > > >From 'Oracle Database Monitoring for the Beginner' (pdf) - Chris Grabowy > > "In a centralized configuration, the monitoring software scripts reside on > one server. This obviously makes maintenance easier, but if the hosting > server fails then there is no (more) monitoring of the databases" > > regards > Madhu > > >From: [EMAIL PROTECTED] > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: monitoring multiple databases using PL/SQL > >Date: Tue, 23 Apr 2002 08:00:33 -0800 > > > >Greetings - > >I am planning to centralize our Oracle monitoring process by using one > >PL/SQL procedure to query database extents, invalid objects, alert logs etc > >through database links. I wonder if anybody has done it before and if there > >is any cons with it. The pros would be ease of administration, ease of > >standardization etc. > > > >TIA > > > >Dennis > > -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: monitoring multiple databases using PL/SQL
Dennis, True, but you should also consider... "In a decentralized configuration, the monitoring software or scripts reside with the database. This complicates maintenance, but allows for higher monitoring availability." I believe that your better off somewhere in the middle. You can host a set of PL/SQL procs in a "monitoring" schema on each database server to check the basics of the database, and report back to you. In this configuration, you can enhance and add features to the development version, and make sure it works, before updating all the databases. Du'oh!! Configuration Management!!! And then you can host specific "are you up/bogus login" scripts on a central host. Generally, your Oracle database stays up...since it's not SQL Server...du'oh!!! The majority of the database problems are in the database, ie. maxextents, no space, performance, blah, blah...there is a list in my paper. Don't forget to collect all this monitoring data in some table(s), as it will become useful. And be sure to CC your boss on all the benefits your monitoring services provide you. Hopefully, some funding will eventually be thrown your way to get a decent monitoring product. Also, recently I saw a demo of a monitoring product called AutoDBA. It is very slick, almost like having a junior DBA doing all the boring, monitoring, dirty work. Except it doesn't get your coffee for you...or wash your car...or... HTH. Chris Grabowy DISCLAIMER: I am not in any way associated with the makers of AutoDBA. My paper also includes a list of other monitoring products. -Original Message- Sent: Tuesday, April 23, 2002 1:53 PM To: Multiple recipients of list ORACLE-L >From 'Oracle Database Monitoring for the Beginner' (pdf) - Chris Grabowy "In a centralized configuration, the monitoring software scripts reside on one server. This obviously makes maintenance easier, but if the hosting server fails then there is no (more) monitoring of the databases" regards Madhu >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: monitoring multiple databases using PL/SQL >Date: Tue, 23 Apr 2002 08:00:33 -0800 > >Greetings - >I am planning to centralize our Oracle monitoring process by using one >PL/SQL procedure to query database extents, invalid objects, alert logs etc >through database links. I wonder if anybody has done it before and if there >is any cons with it. The pros would be ease of administration, ease of >standardization etc. > >TIA > >Dennis > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: monitoring multiple databases using PL/SQL
Having done db monitoring via links, cron jobs, dbms_jobs, and a few others I finally did mine via C/Pro*C running on an NT workstation. Acts like a client, gets at everything I want (with the help of some extproc) and has been extremely reliable, robust and dependable. Not to mention a real pain in the ___ in the middle of the night, but then it was programmed to be insistent. Dick Goulet Reply Separator Author: "Madhusudhanan Sampath" <[EMAIL PROTECTED]> Date: 4/23/2002 9:53 AM >From 'Oracle Database Monitoring for the Beginner' (pdf) - Chris Grabowy "In a centralized configuration, the monitoring software scripts reside on one server. This obviously makes maintenance easier, but if the hosting server fails then there is no (more) monitoring of the databases" regards Madhu >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: monitoring multiple databases using PL/SQL >Date: Tue, 23 Apr 2002 08:00:33 -0800 > >Greetings - >I am planning to centralize our Oracle monitoring process by using one >PL/SQL procedure to query database extents, invalid objects, alert logs etc >through database links. I wonder if anybody has done it before and if there >is any cons with it. The pros would be ease of administration, ease of >standardization etc. > >TIA > >Dennis > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: monitoring multiple databases using PL/SQL
Well, that's why you should monitor your monitoring server. -Original Message- Sent: Tuesday, April 23, 2002 1:53 PM To: Multiple recipients of list ORACLE-L >From 'Oracle Database Monitoring for the Beginner' (pdf) - Chris Grabowy "In a centralized configuration, the monitoring software scripts reside on one server. This obviously makes maintenance easier, but if the hosting server fails then there is no (more) monitoring of the databases" regards Madhu >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: monitoring multiple databases using PL/SQL >Date: Tue, 23 Apr 2002 08:00:33 -0800 > >Greetings - >I am planning to centralize our Oracle monitoring process by using one >PL/SQL procedure to query database extents, invalid objects, alert logs etc >through database links. I wonder if anybody has done it before and if there >is any cons with it. The pros would be ease of administration, ease of >standardization etc. > >TIA > >Dennis > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: monitoring multiple databases using PL/SQL
>From 'Oracle Database Monitoring for the Beginner' (pdf) - Chris Grabowy "In a centralized configuration, the monitoring software scripts reside on one server. This obviously makes maintenance easier, but if the hosting server fails then there is no (more) monitoring of the databases" regards Madhu >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: monitoring multiple databases using PL/SQL >Date: Tue, 23 Apr 2002 08:00:33 -0800 > >Greetings - >I am planning to centralize our Oracle monitoring process by using one >PL/SQL procedure to query database extents, invalid objects, alert logs etc >through database links. I wonder if anybody has done it before and if there >is any cons with it. The pros would be ease of administration, ease of >standardization etc. > >TIA > >Dennis > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: monitoring multiple databases using PL/SQL
Finally something that I can contribute to ...
Dennis,
I put together this script to be run every two hours through 'cron' (we
don't trust dbms_job).
--- start script ---
set serveroutput on
set trimspool on
set feedback off
SET lin 200
set pagesize 100
spool ncs_health.log
PROMPT
set hea off
select 'Starting: ' || to_char(sysdate,'MM-DD- HH:MI:SS AM') from dual
/
prompt
prompt Server Information
set hea off
SELECT 'DATABASE: ' || instance_name||'@'||host_name || ' running ' ||
version || ' since ' ||
TO_CHAR(startup_time,'MM-DD- HH:MI:SS AM') "Startup Time"
FROM sys.V_$INSTANCE;
prompt
set hea on
prompt Buffer Busy Waits
SELECT 'Block Class' "Class Type",
w.class block_class,
w.COUNT total_waits,
w.TIME time_waited
FROM
sys.V_$WAITSTAT w
WHERE
w.COUNT > 0
UNION
SELECT 'Tablespace' "Class Type",
d.tablespace_name,
SUM(x.COUNT) total_waits,
SUM(x.TIME) time_waited
FROM
sys.X_$KCBFWAIT x,
sys.DBA_DATA_FILES d
WHERE
x.inst_id = USERENV('Instance') AND
x.COUNT > 0 AND
d.file_id = x.indx + 1
GROUP BY
d.tablespace_name
UNION
SELECT 'Buffer Pool' "Class Type",
p.bp_name BUFFER_POOL,
SUM(s.bbwait) total_waits,
0
FROM
sys.X_$KCBWDS s,
sys.X_$KCBWBPD p
WHERE
s.inst_id = USERENV('Instance') AND
p.inst_id = USERENV('Instance') AND
s.set_id >= p.bp_lo_sid AND
s.set_id <= p.bp_hi_sid AND
p.bp_size != 0
GROUP BY
p.bp_name
HAVING
SUM(s.bbwait) > 0
ORDER BY 1, 4 DESC
/
prompt
prompt Shared Pool LRU Stats
SELECT
kghlurcr "RECURRENT_CHUNKS",
kghlutrn "TRANSIENT_CHUNKS",
kghlufsh "FLUSHED_CHUNKS",
kghluops "PINS AND_RELEASES",
kghlunfu "ORA-4031_ERRORS",
kghlunfs "LAST_ERROR_SIZE"
FROM
sys.X_$KGHLU
WHERE
inst_id = USERENV('Instance')
/
prompt
prompt Basic Library Cache Stats
select
namespace,
gets locks,
gets - gethits loads,
pins,
reloads,
invalidations
from
sys.v_$librarycache
where
gets > 0
order by
2 desc
/
prompt
prompt Cursor Version Counts
select
substr(to_char(min(v)) ||
decode( max(v) - min(v), 0, null, ' to ' || to_char(max(v))),1,40)
version_count,
count(*) cursors
from
( select count(*) v from sys.x_$kglcursor
where inst_id = userenv('Instance') and
kglhdadr != kglhdpar
group by kglhdpar, kglnahsh)
group by
trunc(round(log(2, v), 37))
/
prompt
prompt
prompt Currently Executing Packages
--column type format a9
--column owner format a25
--column name format a30
--column sid format
--column serial format 99
SELECT
substr(DECODE(o.kglobtyp,
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13,
'CLASS'),1,15) "TYPE",
substr(o.kglnaown,1,30) "OWNER",
substr(o.kglnaobj,1,30) "NAME",
s.indx "SID",
s.ksuseser "SERIAL"
FROM
sys.X_$KGLOB o,
sys.X_$KGLPN p,
sys.X_$KSUSE s
WHERE
o.inst_id = USERENV('Instance') AND
p.inst_id = USERENV('Instance') AND
s.inst_id = USERENV('Instance') AND
o.kglhdpmd = 2 AND
o.kglobtyp IN (7, 8, 9, 12, 13) AND
p.kglpnhdl = o.kglhdadr AND
s.addr = p.kglpnses
ORDER BY 1, 2, 3
/
prompt
prompt List of Objects That Will Fail To Extend
SELECT /*+ RULE ORDERED */
substr(a.tablespace_name,1,30) "Tablespace"
,substr(a.owner,1,30) "Object Owner"
,substr(a.segment_name,1,30)"Object Name"
,a.extents "# Of Extents"
,ROUND(next_extent/1024) "Next Req(KB)"
,ROUND(b.free / 1024)"Max Avail(KB)"
FROM DBA_SEGMENTS a,
(SELECT df.tablespace_name, MAX(fs.bytes) free
FROM DBA_DATA_FILES df,
DBA_FREE_SPACE fs
WHERE df.file_id = fs.file_id
AND df.tablespace_name NOT IN
(SELECT ts.tablespace_name
FROM DBA_TABLESPACES ts
WHERE EXISTS
(SELECT 1
FROM DBA_DATA_FILES df2
WHERE df2.tablespace_name = ts.tablespace_name
AND df2.autoextensible = 'YES'))
GROUP BY df.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
and a.tablespace_name <> 'TEMP_SEGS'
AND a.next_extent > b.free
ORDER BY 1,2,3
/
prompt
prompt List of INVALID Objects
SELECT substr(OWNER,1,30) "Owner"
,substr(OBJECT_NAME,1,30) "Object Name"
,substr(OBJECT_TYPE,1,30) "Object Type"
FROM sys.DBA_OBJECTS
WHERE status = 'INVALID'
ORDER BY 1,2,3
/
prompt
prompt List of DISABLED TRIGGERS
SELECT /*+ RULE ORDERED */
owner
, NVL(table_name, '') table_name
, trigger_name
FROM DBA_TRIGGERS
WHERE status = 'DISABLED'
ORDER BY 1,2,3
/
prompt
prompt List of DISABLED Constraints
SELECT substr(owner,1, 30) "Owner"
,substr(table_name,1, 30) "Table Name"
,substr(constraint_name,1, 30) "Constraint Name"
,substr(DECODE(CONSTRAINT_TYPE,
'C', '(CHECK CONSTRAINT)',
monitoring multiple databases using PL/SQL
Greetings - I am planning to centralize our Oracle monitoring process by using one PL/SQL procedure to query database extents, invalid objects, alert logs etc through database links. I wonder if anybody has done it before and if there is any cons with it. The pros would be ease of administration, ease of standardization etc. TIA Dennis -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
