| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Monitoring script
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_000_01C0389D.C204F6A0
Content-Type: text/plain
Hi David
Here are couple examples
<<db_monit.sql>> <<dbreport.sh>> <<sendmail.sh>>
With regards
Alex Afanassiev
Oracle DBA, TOC OPS/Internet.Operations
Tel: (03) 8 661 20 61 Fax: (03) 9 650 36 74>
> -----Original Message-----
> From: David Turner [SMTP:turner_at_tellme.com]
> Sent: Wednesday, October 18, 2000 10:36 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Monitoring script
------_=_NextPart_000_01C0389D.C204F6A0
Content-Type: application/octet-stream;
name="db_monit.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="db_monit.sql"
reM * db_monit.sql package=20 REM * REM *
************************************************************************=
**
REM * =
*
REM * MAINTENANCE LOG =
*
REM * --------------- =
*
REM * DATE WHO DESCRIPTION =
*
------------------------------------------- *
REM * 12OCT98 Afanassiev Created =
*
************************************************************************=**
CREATE OR REPLACE PACKAGE db_monit =20
AS
PROCEDURE ts_space ( gp_ts_name VARCHAR2, gp_next_extent NUMBER, gp_measure VARCHAR2);=20 PROCEDURE ts_space ( gp_ts_name IN VARCHAR2, gp_next_extent NUMBER); PROCEDURE ts_space ( gp_ts_name IN VARCHAR2 ); PROCEDURE ts_space; PROCEDURE free_space ( gp_ts_name IN VARCHAR2, gp_next_extent NUMBER, gp_measure VARCHAR2); PROCEDURE free_space ( gp_ts_name IN VARCHAR2, gp_next_extent NUMBER); PROCEDURE free_space ( gp_ts_name IN VARCHAR2 ); PROCEDURE free_space; PROCEDURE get_session; PROCEDURE get_session ( gp_user_name IN VARCHAR2 ); PROCEDURE get_lock; PROCEDURE get_lock ( gp_obj_name IN VARCHAR2 ); PROCEDURE get_rollback; PROCEDURE db_growth; PROCEDURE tab_space ( gp_owner IN VARCHAR2 ); PROCEDURE tab_space ( gp_owner IN VARCHAR2, gp_name IN VARCHAR2 ); PROCEDURE tab_space ( gp_owner IN VARCHAR2, gp_name IN VARCHAR2 , = gp_ts_name IN VARCHAR2 ); PROCEDURE tabspace ( gp_owner IN VARCHAR2 ); PROCEDURE tabspace ( gp_owner IN VARCHAR2, gp_name IN VARCHAR2 ); PROCEDURE tabspace ( gp_owner IN VARCHAR2, gp_name IN VARCHAR2 , = gp_ts_name IN VARCHAR2 ); PROCEDURE print_head;=20 PROCEDURE print_tail;
END db_monit;
/
CREATE OR REPLACE PACKAGE BODY db_monit =20
AS=20
GBT NUMBER DEFAULT 1073741824;=20
MBT NUMBER DEFAULT 1048576;=20
KBT NUMBER DEFAULT 1024;=20
PROCEDURE ts_space=20
IS
BEGIN
ts_space(NULL, NULL, NULL);
END; -- end of ts_space
PROCEDURE ts_space (gp_ts_name VARCHAR2)=20
IS
BEGIN
ts_space(gp_ts_name, NULL, NULL);
END; -- end of ts_space
PROCEDURE ts_space ( gp_ts_name VARCHAR2,
gp_next_extent NUMBER )
IS
BEGIN
ts_space(gp_ts_name, gp_next_extent, NULL);
END; -- end of ts_space
PROCEDURE ts_space ( gp_ts_name VARCHAR2,
gp_next_extent NUMBER, gp_measure VARCHAR2)=20
CURSOR cur_used (p_ts_name VARCHAR2) IS
select t.tablespace_name tsname,
t.next_extent next_ext,
NVL(SUM(sg.bytes),0) used_bytes,
NVL(SUM(sg.extents),0) used_extents
from sys.dba_segments sg,
sys.dba_tablespaces t
where t.tablespace_name =3D sg.tablespace_name (+)
and t.tablespace_name LIKE DECODE(p_ts_name,
NULL, t.tablespace_name ,UPPER(p_ts_name)||'%')
group by t.tablespace_name, t.next_extent;
CURSOR cur_total (p_ts_name VARCHAR2) IS
select SUM(f.bytes) tot_bytes
from sys.dba_data_files f
, sys.dba_tablespaces t
where t.tablespace_name =3D f.tablespace_name
and t.tablespace_name =3D p_ts_name
group by f.tablespace_name;
CURSOR cur_free (p_ts_name VARCHAR2, p_next_extent NUMBER) IS
select NVL(SUM(f.bytes),0) tot_bytes,
--SUM(FLOOR(NVL(SUM(f.bytes),0)/p_next_extent)) tot_ext
-- the above is valid only when the neigbor. extents are =
coalesed
SUM(FLOOR(NVL(f.bytes,0)/p_next_extent)) tot_ext
from sys.dba_free_space f,
sys.dba_tablespaces t
p_next_extent NUMBER(12);
p_ts_next_extent NUMBER(12);
n_tot_ext NUMBER(6); n_tot_ext_t NUMBER(6); n_tot_gbt NUMBER(7,3); n_tot_gbt_t NUMBER(7,3); n_usd_ext NUMBER(6); n_usd_ext_t NUMBER(6); n_usd_bytes NUMBER(15); n_total_bytes NUMBER(15); n_usd_gbt NUMBER(7,3); n_usd_gbt_t NUMBER(7,3); n_fre_ext NUMBER(6); n_fre_ext_t NUMBER(6); n_fre_bytes NUMBER(15); n_aval_gbt NUMBER(7,3); n_aval_gbt_t NUMBER(7,3); n_lost_gbt NUMBER(7,3); n_lost_gbt_t NUMBER(7,3); p_col1 NUMBER; p_col2 NUMBER; p_col3 NUMBER; p_col4 NUMBER; p_col_del VARCHAR2(2); v_line VARCHAR2(255);
BEGIN
v_line :=3D '-';
p_col_del :=3D ' ';
n_tot_ext_t :=3D 0;
n_tot_gbt_t :=3D 0;=20
n_usd_ext_t :=3D 0;
n_usd_gbt_t :=3D 0;=20
n_fre_ext_t :=3D 0;
n_aval_gbt_t :=3D 0;=20
n_lost_gbt_t :=3D 0;=20
p_col1 :=3D 12;
p_col2 :=3D 6;
p_col3 :=3D 7;
p_col4 :=3D 6;
dbms_output.enable (10000000);
/* print heading */
print_head;
dbms_output.put_line(RPAD(v_line, p_col1 + 3 * p_col2=20
+ 4 * p_col3 + 3 * p_col4 + 3 * LENGTH(p_col_del),'-'));
dbms_output.put_line(RPAD('Tablespace', p_col1,' ')||p_col_del||
RPAD(' Extents', 3 * p_col2 )||p_col_del||=20
RPAD(' Gbt', 4 * p_col3 )||p_col_del||
RPAD(' Percent', 3 * p_col4));
dbms_output.put_line(RPAD('.', p_col1,' ')||p_col_del||
LPAD('Total', p_col2)|| LPAD('Used', p_col2)||=20
LPAD('Free', p_col2)|| p_col_del||
LPAD('Total', p_col3)|| LPAD('Used', p_col3)||=20
LPAD('Free', p_col3)||=20
LPAD('Lost', p_col3)||p_col_del||
LPAD('Used', p_col4)|| LPAD('Free', p_col4)|| =09
LPAD('Lost', p_col4));
dbms_output.put_line(RPAD(v_line, p_col1 + 3 * p_col2 +
4 * p_col3 + 3 * p_col4 + 3 * LENGTH(p_col_del),'-'));
FOR cur_used_rec IN cur_used (gp_ts_name) LOOP =20
n_usd_gbt :=3D cur_used_rec.used_bytes / GBT ;
n_usd_ext :=3D cur_used_rec.used_extents ;
OPEN cur_total(cur_used_rec.tsname);
FETCH cur_total INTO n_total_bytes;
CLOSE cur_total;
-- set to Gbtytes
n_tot_gbt :=3D n_total_bytes / GBT;
=20
IF gp_next_extent IS NULL THEN
p_next_extent :=3D cur_used_rec.next_ext;=20
ELSE
-- already set as a parameter
NULL;
END IF;
OPEN cur_free(cur_used_rec.tsname, p_next_extent);
FETCH cur_free INTO n_fre_bytes, n_fre_ext;
CLOSE cur_free;
n_aval_gbt :=3D (n_fre_ext * p_next_extent) / GBT;
n_lost_gbt :=3D ( n_fre_bytes / GBT) - n_aval_gbt;
n_tot_ext :=3D n_usd_ext + n_fre_ext;=20
dbms_output.put_line(RPAD(cur_used_rec.tsname, p_col1,'.')||
p_col_del||=20
LPAD(TO_CHAR(n_tot_ext, '99999'), p_col2)||
LPAD(TO_CHAR(n_usd_ext, '99999'), p_col2)||
LPAD(TO_CHAR(n_fre_ext, '99999'), p_col2)||p_col_del||
LPAD(TO_CHAR(n_tot_gbt, '999.99'),p_col3)||
LPAD(TO_CHAR(n_usd_gbt, '999.99'),p_col3)||
LPAD(TO_CHAR(n_aval_gbt, '999.99'),p_col3)||
LPAD(TO_CHAR(n_lost_gbt, '999.99'),p_col3)||p_col_del||
LPAD(TO_CHAR( (n_usd_gbt/n_tot_gbt) * 100,'999'),=20
p_col4)|| =20
LPAD(TO_CHAR( (n_aval_gbt/n_tot_gbt) * 100,'999'),=20
p_col4)||=20
LPAD(TO_CHAR( (n_lost_gbt/n_tot_gbt) * 100,'999'),=20
p_col4)); =20
n_tot_ext_t :=3D n_tot_ext_t + n_tot_ext;
n_tot_gbt_t :=3D n_tot_gbt_t + n_tot_gbt;
n_usd_ext_t :=3D n_usd_ext_t + n_usd_ext;
n_usd_gbt_t :=3D n_usd_gbt_t + n_usd_gbt;
n_fre_ext_t :=3D n_fre_ext_t + n_fre_ext;
n_aval_gbt_t :=3D n_aval_gbt_t + n_aval_gbt;
n_lost_gbt_t :=3D n_lost_gbt_t + n_lost_gbt;
END LOOP;
dbms_output.put_line(RPAD(v_line, p_col1 + 3 * p_col2 + 4 * p_col3 + =
3 * p_col4 + 3 * LENGTH(p_col_del)
,'-'));
dbms_output.put_line(RPAD('Total', p_col1,'.')||p_col_del||=20
LPAD(TO_CHAR(n_tot_ext_t, '99999'), p_col2)||
LPAD(TO_CHAR(n_usd_ext_t, '99999'), p_col2)||
LPAD(TO_CHAR(n_fre_ext_t, '99999'), =
p_col2)||p_col_del||
LPAD(TO_CHAR(n_tot_gbt_t, '999.99'),p_col3)||
LPAD(TO_CHAR(n_usd_gbt_t, '999.99'),p_col3)||
LPAD(TO_CHAR(n_aval_gbt_t, '999.99'),p_col3)||
LPAD(TO_CHAR(n_lost_gbt_t, =
'999.99'),p_col3)||p_col_del||
LPAD(TO_CHAR( (n_usd_gbt_t/n_tot_gbt_t) * =
100,'999'), p_col4)|| =20
LPAD(TO_CHAR( (n_aval_gbt_t/n_tot_gbt_t) * =
100,'999'), p_col4)||=20
LPAD(TO_CHAR( (n_lost_gbt_t/n_tot_gbt_t) * =
100,'999'), p_col4)); =20
dbms_output.put_line(RPAD(v_line, p_col1 + 3 * p_col2 + 4 * p_col3 + = 3 * p_col4 + 3 * LENGTH(p_col_del)
,'-'));
dbms_output.put_line('The calculations of free extents, available and =
lost space are based');
dbms_output.put_line('on the assumption that all existing in the =
database objects as well as');
dbms_output.put_line('the objects created in the future have the same =
INITIAL and NEXT EXTENT');
dbms_output.put_line('storage parameters and PCT INCREASE set to 0.');
-- print_tail;
END; -- end ts_space procedure
PROCEDURE tab_space ( gp_owner IN VARCHAR2 )
IS
BEGIN
tab_space(gp_owner, NULL, NULL);
END; -- end of tab_space
PROCEDURE tab_space ( gp_owner IN VARCHAR2, gp_name IN VARCHAR2 )
IS
BEGIN
tab_space(gp_owner, gp_name, NULL);
END; -- end of tab_space
PROCEDURE tab_space ( gp_owner IN VARCHAR2, gp_name IN VARCHAR2 , =
gp_ts_name IN VARCHAR2 )
IS
CURSOR cur_tab_space (p_owner VARCHAR2,=20
p_name VARCHAR2,=09
p_ts_name VARCHAR2) IS
SELECT tb.tablespace_name tsname
, tb.owner towner
, tb.table_name tname
, tb.num_rows row_cnt
, sg.extents ext
, ((sg.blocks * blk.sz)/MBT) Mgb
, ((tb.blocks * blk.sz)/MBT) b_mgb
, ((tb.empty_blocks * blk.sz)/MBT) a_mgb
, (tb.blocks/sg.blocks) * 100 used_perc
, (tb.empty_blocks/sg.blocks) * 100 free_perc
, (((sg.blocks - tb.empty_blocks - 1) * blk.sz)/MBT) hwm_mgb
, ((sg.blocks - tb.empty_blocks - 1) / sg.blocks) * 100 hwm_perc
-- , ((tb.avg_space * blk.sz)/MBT ) avg_mgb =20
, tb.chain_cnt chain
FROM sys.dba_tables tb
, sys.dba_segments sg,
(select value sz from v$parameter where name =3D 'db_block_size') =
blk
WHERE tb.table_name =3D sg.segment_name
AND tb.owner =3D sg.owner
AND tb.tablespace_name=20
LIKE NVL(UPPER(p_ts_name), tb.tablespace_name)||'%'
AND tb.table_name LIKE NVL(UPPER(p_name), tb.table_name)||'%'
AND tb.owner LIKE NVL(UPPER(p_owner), tb.owner)||'%'
ORDER BY b_mgb DESC , tsname, tname;
p_col1 NUMBER;
p_col11 NUMBER;
p_col12 NUMBER;
p_col13 NUMBER;
p_col14 NUMBER;
p_col15 NUMBER;
p_col2 NUMBER;
p_col21 NUMBER;
p_col22 NUMBER;
p_col23 NUMBER;
p_col3 NUMBER;
p_col31 NUMBER;
p_col32 NUMBER;
p_col4 NUMBER;
p_col41 NUMBER;
p_col42 NUMBER;
p_col_del VARCHAR2(2);
v_line VARCHAR2(512);
BEGIN
v_line :=3D '-';
p_col_del :=3D ' ';
p_col1 :=3D 9;
p_col11 :=3D 9;
p_col12 :=3D 27;
p_col13 :=3D 9;
p_col14 :=3D 3;
p_col15 :=3D 6;
p_col2 :=3D 24;
p_col21 :=3D 8;
p_col22 :=3D 8;
p_col23 :=3D 8;
p_col3 :=3D 16;
p_col31 :=3D 8;
p_col32 :=3D 8;
p_col4 :=3D 14;
p_col41 :=3D 6;
p_col42 :=3D 8;
dbms_output.enable (10000000);
/* print heading */
print_head;
dbms_output.put_line(RPAD(v_line, p_col1 + p_col11 + p_col12 + p_col13 =
+=20
p_col14 + p_col15 + 3 * p_col2 +=20
3 * p_col3 + 3 * LENGTH(p_col_del) ,'-'));
dbms_output.put_line(RPAD('TS name', p_col1,' ')||p_col_del||
RPAD('Owner', p_col11,' ')||p_col_del||
RPAD('Table name', p_col12,' ')||p_col_del||
RPAD('Row count', p_col13,' ')||p_col_del||
RPAD('Ext', p_col14,' ')||p_col_del||
RPAD('Chain', p_col15,' ')||p_col_del||
LPAD('Mbt', p_col2,' ')||p_col_del||
LPAD('Perc', p_col3,' ')||p_col_del||
LPAD('HWM', p_col4,' '));
dbms_output.put_line(RPAD('.', p_col1,' ')||p_col_del||
RPAD('.', p_col11,' ')||p_col_del||
RPAD('.', p_col12,' ')||p_col_del||
RPAD('.', p_col13,' ')||p_col_del||
RPAD('.', p_col14,' ')||p_col_del||
RPAD('.', p_col15,' ')||p_col_del||
LPAD('Tot', p_col21,' ')||p_col_del|| =20
LPAD('Used', p_col22,' ')||p_col_del|| =20
LPAD('Free', p_col23,' ')||p_col_del|| =20
LPAD('Used', p_col31,' ')||p_col_del|| =20
LPAD('Free', p_col32,' ')||p_col_del|| =20
LPAD('Mbt', p_col41,' ')||p_col_del|| =20
LPAD('Perc', p_col42,' ')); =20
dbms_output.put_line(RPAD(v_line, p_col1 + p_col11 + p_col12 + p_col13 =
+ p_col14 + p_col15 + 3 * p_col2 +=20
p_col4 + p_col3 + 3 * LENGTH(p_col_del) ,'-'));
FOR cur_tab_space_rec IN cur_tab_space (gp_owner, gp_name, = gp_ts_name) LOOP =20
dbms_output.put_line(=20
RPAD(cur_tab_space_rec.tsname, p_col1,'.')||p_col_del||
RPAD(cur_tab_space_rec.towner, p_col11,' ')||p_col_del||
RPAD(cur_tab_space_rec.tname, p_col12,' ')||p_col_del||
LPAD(cur_tab_space_rec.row_cnt, p_col13,' ')||p_col_del||
LPAD(cur_tab_space_rec.ext, p_col14,' ')||p_col_del||
LPAD(cur_tab_space_rec.chain, p_col15,' ')||p_col_del||
LPAD(TO_CHAR(cur_tab_space_rec.Mgb, '9999.999'), p_col21)||
LPAD(TO_CHAR(cur_tab_space_rec.b_mgb, '9999.999'), p_col22)||
LPAD(TO_CHAR(cur_tab_space_rec.a_mgb, '9999.999'), p_col23)||
LPAD(TO_CHAR(cur_tab_space_rec.used_perc, '999.99'), p_col31)||
LPAD(TO_CHAR(cur_tab_space_rec.free_perc, '999.99'), p_col32)||
LPAD(TO_CHAR(cur_tab_space_rec.hwm_mgb, '999.9999'),p_col41)||
LPAD(TO_CHAR(cur_tab_space_rec.hwm_perc, '999.99'),p_col42)
-- p_col_del
-- -- || LPAD(TO_CHAR(cur_tab_space_rec.avg_mgb, '999.99'),p_col4)
);
END LOOP;
dbms_output.put_line(RPAD(v_line, p_col1 + p_col11 + p_col12 + p_col13 = + p_col14 + p_col15 + 3 * p_col2 +=20 p_col4 + p_col3 + 3 * LENGTH(p_col_del) ,'-'));
END; -- end of tab_space
PROCEDURE tabspace ( gp_owner IN VARCHAR2, gp_name IN VARCHAR2 )
IS
BEGIN
tabspace(gp_owner, gp_name, NULL);
END; -- end of tab_space
PROCEDURE tabspace ( gp_owner IN VARCHAR2, gp_name IN VARCHAR2 , =
gp_ts_name IN VARCHAR2 )
IS
CURSOR cur_tab_space (p_owner VARCHAR2,=20
p_name VARCHAR2,=09
p_ts_name VARCHAR2) IS
SELECT sg.tablespace_name tsname
, sg.owner towner
, sg.segment_name tname
, sg.segment_type ttype
, sg.extents ext
, sg.blocks blk
, DECODE(sg.extents, 1, sg.initial_extent, sg.next_extent) ext_size
FROM sys.dba_segments sg
WHERE sg.tablespace_name=20
LIKE NVL(UPPER(p_ts_name), sg.tablespace_name)||'%'
AND sg.segment_name LIKE NVL(UPPER(p_name), sg.segment_name)||'%'
AND sg.owner LIKE NVL(UPPER(p_owner), sg.owner)||'%'
ORDER BY tsname, tname;
p_col1 NUMBER:=3D20;
p_col11 NUMBER:=3D27;
p_col12 NUMBER:=3D27;
p_col13 NUMBER:=3D55;
p_col_del VARCHAR2(2):=3D' ';
v_line VARCHAR2(512):=3D'-';
v_block_size NUMBER:=3D0;
v_segment_type VARCHAR2(30):=3D'TABLE';
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_unused_blocks NUMBER;
v_unused_bytes NUMBER;
v_last_used_extent_file_id NUMBER;
v_last_used_extent_block_id NUMBER;
BEGIN dbms_output.enable (10000000);
/* print heading */
print_head;
dbms_output.put_line(RPAD(v_line, p_col1 + p_col11 + p_col12 + + 3 * LENGTH(p_col_del) ,'-'));
FOR cur_tab_space_rec IN cur_tab_space (gp_owner, gp_name, = gp_ts_name) LOOP =20
dbms_space.unused_space (
cur_tab_space_rec.towner,
cur_tab_space_rec.tname,=20
v_segment_type,
v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes,
v_last_used_extent_file_id, v_last_used_extent_block_id,
v_last_used_block);
-- block size
IF v_block_size =3D 0 THEN=20
v_block_size :=3D v_total_bytes / v_total_blocks;=20
END IF;
dbms_space.free_blocks(
cur_tab_space_rec.towner,
cur_tab_space_rec.tname,=20
v_segment_type,
0,
v_free_blks,
null
);
=20
dbms_output.put_line(=20
RPAD('Tablespace', p_col13,' ')||' =3D '||cur_tab_space_rec.tsname);
dbms_output.put_line(=20
RPAD('Owner', p_col13,' ')||' =3D '|| =
cur_tab_space_rec.towner);
dbms_output.put_line(=20
RPAD('Segment name', p_col13,' ')||' =3D =
'||cur_tab_space_rec.tname);
dbms_output.put_line(
RPAD('Block size' , p_col13)||' =3D '||
to_char ( v_block_size ));
dbms_output.put_line(
RPAD('Extents' , p_col13)||' =3D '||
to_char ( cur_tab_space_rec.ext ));
dbms_output.put_line(
RPAD('Total number of blocks (bytes) in the extent', p_col13)
||' =3D '||(cur_tab_space_rec.ext_size/v_block_size =
)
||'('||cur_tab_space_rec.ext_size||')');
dbms_output.put_line(
RPAD('Total number of blocks (bytes) in the segment', p_col13)
||' =3D =
'||v_total_blocks||'('||v_total_bytes||')');
dbms_output.put_line(
RPAD('Number of blocks (bytes) above high water mark', p_col13)
||' =3D =
'||v_unused_blocks||'('||v_unused_bytes||')');
dbms_output.put_line(
RPAD('The file ID of the last extent which contains data', p_col13)
||' =3D '||v_last_used_extent_file_id);
dbms_output.put_line(
RPAD('The block ID of the last extent which contains data', p_col13) =
=20
||' =3D '||v_last_used_extent_block_id);
dbms_output.put_line(
RPAD('The last block within this extent which contains data', =
p_col13)=20
||' =3D '||v_last_used_block);
dbms_output.put_line(
RPAD('Number of free blocks under high water mark', p_col13)
||' =3D '||v_free_blks);
dbms_output.put_line(
RPAD('Number of used blocks under high water mark', p_col13)=20
||' =3D =
'||to_char(v_total_blocks-v_unused_blocks-v_free_blks));
dbms_output.put_line(
RPAD('Total used space (%)' , p_col13)||' =3D '||
to_char ((v_free_blks/v_total_blocks) * 100 ));
dbms_output.put_line(RPAD(v_line, p_col1 + p_col11 + p_col12 +
+ 3 * LENGTH(p_col_del) ,'-'));
END LOOP; EXCEPTION
WHEN NO_DATA_FOUND then
dbms_output.put_line('No data found for specified =
criteria');
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM||' found');
END; -- end of tab_space
PROCEDURE free_space=20
IS
BEGIN
free_space(NULL, NULL, NULL);
END; =20
PROCEDURE free_space (gp_ts_name VARCHAR2)=20
IS
BEGIN
free_space(gp_ts_name, NULL, NULL);
END; =20
PROCEDURE free_space (gp_ts_name VARCHAR2,
gp_next_extent NUMBER)=20
IS
BEGIN
free_space(gp_ts_name, gp_next_extent, NULL);
END; =20
PROCEDURE free_space ( gp_ts_name VARCHAR2,
gp_next_extent NUMBER, gp_measure VARCHAR2 )=20
CURSOR cur_free (p_ts_name VARCHAR2, p_next_extent NUMBER) IS select=20 f.tablespace_name tsname , (NVL(p_next_extent, t.next_extent)/MBT) next_ext , f.file_id fl_id , f.block_id blk_id =20 , ( f.block_id + NVL(f.blocks,0) ) next_blk_id , (NVL(SUM(f.bytes),0))/MBT free =20 , ( FLOOR(NVL(SUM(f.bytes),0)/NVL(p_next_extent, t.next_extent))=20 * NVL(p_next_extent, t.next_extent) / MBT ) aval =20 , FLOOR(NVL(SUM(f.bytes),0)/NVL(p_next_extent, t.next_extent)) = aval_ext from sys.dba_free_space f, sys.dba_tablespaces t where t.tablespace_name =3D f.tablespace_name (+) and t.tablespace_name =3D NVL(p_ts_name, t.tablespace_name) group by f.tablespace_name, f.file_id, f.block_id, f.blocks, =t.next_extent;=20
p_next_extent NUMBER(12);
n_free_t NUMBER(9,3);
n_aval_t NUMBER(9,3);
n_aval_ext_t NUMBER(7);
n_lost NUMBER(9,3);
n_lost_t NUMBER(9,3);
p_col1 NUMBER;
p_col2 NUMBER;
p_col3 NUMBER;
p_col4 NUMBER;
p_col5 NUMBER;
p_col6 NUMBER;
p_col7 NUMBER;
p_col8 NUMBER;
p_col9 NUMBER;
p_col_cnt NUMBER;
p_col_del VARCHAR2(2);
v_line VARCHAR2(255);
BEGIN
dbms_output.enable (10000000);
-- print parameters
dbms_output.put_line('Tablespace is '||NVL(gp_ts_name,'ALL')); =
=20
dbms_output.put_line(NVL(TO_CHAR(gp_next_extent), 'tablespace = ')||gp_measure||
' NEXT EXTENT size will be used to estimate available'|| ' and lost space ');
v_line :=3D '-';
p_col_del :=3D ' ';
n_free_t :=3D 0;
n_lost_t :=3D 0;
n_aval_t :=3D 0;
n_aval_ext_t :=3D 0;=09
p_col1 :=3D 12;
p_col2 :=3D 6;
p_col3 :=3D 7;
p_col4 :=3D 7;
p_col5 :=3D 7;
p_col6 :=3D 8;
p_col7 :=3D 8;
p_col8 :=3D 6;
p_col9 :=3D 8;
p_col_cnt :=3D 9;
/* print heading */
print_head;
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 + =
p_col5 + p_col6=20
+ p_col7 + p_col8 + p_col9 + p_col_cnt * LENGTH(p_col_del) - =
1,'-'));
dbms_output.put_line(RPAD('Tablespace', p_col1,' ')||p_col_del||
RPAD('Next Ext', p_col2 )||p_col_del|| RPAD('File Id', p_col3 =
)||p_col_del||
RPAD('Blk Id', p_col4 )||p_col_del|| RPAD('Nxt Blk Id', p_col5 =
)||p_col_del||
RPAD('Free Mbt', p_col6 )||p_col_del|| RPAD('Avl Mbt', p_col7 =
)||p_col_del||
RPAD('Avl Ext', p_col8 )|| p_col_del|| RPAD('Lost Mbt', p_col9));
FOR cur_free_rec IN cur_free (gp_ts_name, p_next_extent ) LOOP =20
n_free_t :=3D n_free_t + cur_free_rec.free; n_aval_t :=3D n_aval_t + cur_free_rec.aval; n_lost_t :=3D n_lost_t + n_lost; n_aval_ext_t :=3D n_aval_ext_t + cur_free_rec.aval_ext;
END LOOP; dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 + = p_col5 + p_col6=20
+ p_col7 + p_col8 + p_col9 + p_col_cnt * LENGTH(p_col_del) - = 1,'-'));
dbms_output.put_line( RPAD('Total',p_col1,'.')||p_col_del||=20
LPAD(TO_CHAR(0, '99999'), p_col2)||p_col_del||=20
LPAD(TO_CHAR(0,'99999'), p_col3)||p_col_del||=20
LPAD(TO_CHAR(0,'99999'), p_col4)||p_col_del||=20
LPAD(TO_CHAR(0,'99999'), p_col5)||p_col_del||=20
LPAD(TO_CHAR(n_free_t,'99999.99'), =
p_col6)||p_col_del||=20
LPAD(TO_CHAR(n_aval_t,'99999.99'), =
p_col7)||p_col_del||=20
LPAD(TO_CHAR(n_aval_ext_t,'99999'), =
p_col8)||p_col_del|| =20
LPAD(TO_CHAR(n_lost_t,'99999.99'), p_col9)); =20
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 + =
p_col5 + p_col6=20
+ p_col7 + p_col8 + p_col9 + p_col_cnt * LENGTH(p_col_del) - =
1,'-'));
dbms_output.put_line('The calculations of free extents, available and = lost space are based');
dbms_output.put_line('on the assumption that all existing in the = database objects as ');
dbms_output.put_line('well as the objects created in the future have = the same INITIAL and ');
dbms_output.put_line('NEXT EXTENT storage parameters and PCT INCREASE =
set to 0.');
-- print_tail;
EXCEPTION
WHEN NO_DATA_FOUND then
dbms_output.put_line('No data found for specified criteria');
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM||' found');
END;=20
PROCEDURE db_growth=20
IS
CURSOR cur_growth IS SELECT 'date_recorded' recorded,=20 12 tot FROM dual; --FROM segments_hist --GROUP BY date_recorded --UNION --SELECT SYSDATE recorded,=20 -- SUM(bytes) tot --FROM sys.dba_segments; n_bytes NUMBER(15); n_max NUMBER(15); n_ratio NUMBER(16,3); p_col1 NUMBER; p_col2 NUMBER; p_col3 NUMBER; p_col4 NUMBER; p_col_cnt NUMBER; p_col_del VARCHAR2(2); v_line VARCHAR2(255); v_scale VARCHAR2(255);
SCALE_SIZE NUMBER DEFAULT 100;=20
FILLER CHAR(1) DEFAULT '.';=20
BEGIN
dbms_output.enable (10000000);
v_line :=3D '-';
v_scale :=3D FILLER;
p_col_del :=3D ' ';
n_bytes :=3D 0;
n_max :=3D 0;
n_ratio :=3D 0;
p_col1 :=3D 9;
p_col2 :=3D 8;
p_col3 :=3D 7;
p_col4 :=3D 8;
p_col_cnt :=3D 4;
/* print heading */
print_head;
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4=20
+ p_col_cnt * LENGTH(p_col_del) - 1,'-'));
dbms_output.put_line(RPAD('Recorded ', p_col1,' ')||p_col_del||
RPAD(' Tot Mbt', p_col2 )||p_col_del|| RPAD('Tot Gbt', p_col3 =
)||p_col_del||
RPAD('Incrs Gbt', p_col4 ));
FOR cur_growth_rec IN cur_growth LOOP =20
dbms_output.put_line( RPAD(cur_growth_rec.recorded, =
p_col1,'.')||p_col_del||=20
LPAD(TO_CHAR(cur_growth_rec.tot / MBT, =
'99999.99'), p_col2)||p_col_del||=20
LPAD(TO_CHAR(cur_growth_rec.tot / =
GBT,'9999.99'), p_col3)||p_col_del||=20
LPAD(TO_CHAR( (cur_growth_rec.tot - n_bytes ) / =
GBT ,'99999.99'), p_col4) );
IF cur_growth_rec.tot > n_max THEN=20
n_max :=3D cur_growth_rec.tot;
END IF;
n_bytes :=3D cur_growth_rec.tot;
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4=20
+ p_col_cnt * LENGTH(p_col_del) ,'-'));
--dbms_output.put_line ('n_max '||n_max);
--dbms_output.put_line ('SCALE_SIZE'||SCALE_SIZE);
n_ratio :=3D n_max / SCALE_SIZE;
--dbms_output.put_line (n_ratio);
dbms_output.put_line(RPAD('Recorded ', p_col1,' ')||p_col_del||
RPAD(' Tot Gbt', p_col2 ));
FOR cur_growth_rec IN cur_growth LOOP =20
dbms_output.put_line( RPAD(cur_growth_rec.recorded, =
p_col1,'.')||p_col_del||=20
RPAD(v_scale, (ROUND(cur_growth_rec.tot / n_ratio) - 1 =
), FILLER )||
TO_CHAR(cur_growth_rec.tot / GBT, '999.99'));
END LOOP;
dbms_output.put_line(RPAD(v_line, SCALE_SIZE + p_col1 + p_col3 ,'-'));
EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM||' found');
END;=20
PROCEDURE get_session=20
IS
BEGIN
get_session(NULL);
END; =20
PROCEDURE get_session ( gp_user_name IN VARCHAR2 )
IS
CURSOR cur_session (p_user_name VARCHAR2 ) IS
SELECT=20
s.sid session_id=20
, SUBSTR(s.username,1,12) user_name
, s.status
, SUBSTR(s.schemaname,1,10) schema_name
, s.osuser=20
, s.process os_pid =20
, SUBSTR(s.machine,1,6) machine
, s.terminal
, SUBSTR(LTRIM(s.program),1,28) image
--, TO_CHAR(SYSDATE - (t.hsecs - ss.value)/(24*3600*100),
-- 'DD-MON') connect_date
, TO_CHAR( s.logon_time,'DD-MON') connect_date
--, TO_CHAR(SYSDATE - (t.hsecs - ss.value)/(24*3600*100),
--'HH24:MI:SS') connect_time
, TO_CHAR(s.logon_time, 'HH24:MI:SS') connect_time
, s.lockwait=20
, s.serial#=20
FROM v$session s
-- , v$sesstat ss,
--v$sysstat st,
-- v$timer t =20
WHERE s.USERNAME like UPPER(NVL(p_user_name, s.USERNAME))||'%'
--AND st.statistic# =3D ss.statistic#
--AND ss.sid =3D s.sid
--AND st.name =3D 'session connect time'
ORDER BY user_name, connect_time;=20
p_col1 NUMBER;
p_col2 NUMBER;
p_col3 NUMBER;
p_col4 NUMBER;
p_col5 NUMBER;
p_col6 NUMBER;
p_col7 NUMBER;
p_col8 NUMBER;
p_col9 NUMBER;
p_col10 NUMBER;
p_col11 NUMBER;
p_col_cnt NUMBER;
v_line VARCHAR2(255) DEFAULT '-';
p_col_del VARCHAR2(2) DEFAULT ' ';
v_connect_date VARCHAR2(25);
BEGIN
dbms_output.enable (10000000);
p_col1 :=3D 12;
p_col2 :=3D 3;
p_col3 :=3D 17;
p_col4 :=3D 8;
p_col5 :=3D 10;
p_col6 :=3D 7;
p_col7 :=3D 9;
p_col8 :=3D 8;
p_col9 :=3D 11;
p_col10 :=3D 28;
p_col11 :=3D 8;
p_col_cnt :=3D 11;
/* print heading */
print_head;
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 + =
p_col5 +=20
p_col6 + p_col7 + p_col8 + p_col9 + p_col10 + p_col11=20
+ p_col_cnt * LENGTH(p_col_del) - 1,'-'));
dbms_output.put_line(LPAD('User Name ', p_col1,' ')||p_col_del||
LPAD('SID', p_col2 )||p_col_del||=20
RPAD('Connected time', p_col3 )||p_col_del||
RPAD('Status', p_col4 )||p_col_del||
RPAD('Shema Name', p_col5 )||p_col_del||=20
RPAD('OS user', p_col6 )||p_col_del||
RPAD('OSpid', p_col7 )||p_col_del|| RPAD('Machine', p_col8 =
)||p_col_del||
RPAD('Terminal', p_col9 )||p_col_del|| RPAD('Program', p_col10 =
)||p_col_del||
RPAD('LockWait', p_col11 ));
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 + =
p_col5 +=20
p_col6 + p_col7 + p_col8 + p_col9 + p_col10 + p_col11=20
+ p_col_cnt * LENGTH(p_col_del) - 1,'-'));
FOR cur_session_rec IN cur_session (gp_user_name) LOOP =20
IF cur_session_rec.connect_date =3D TO_CHAR(SYSDATE, 'DD-MON') THEN=20
v_connect_date :=3D NULL;
ELSE
v_connect_date :=3D ' '||cur_session_rec.connect_date;
END IF;
dbms_output.put_line( RPAD(cur_session_rec.user_name, =
p_col1)||p_col_del||=20
LPAD(TO_CHAR(cur_session_rec.session_id), =
p_col2)||p_col_del||=20
RPAD(cur_session_rec.connect_time || ' =
'||v_connect_date
, p_col3)||p_col_del||=20
RPAD(NVL(cur_session_rec.status, 'UNKNOWN'), =
p_col4)||p_col_del||=20
RPAD(NVL(cur_session_rec.schema_name, =
'UNKNOWN'), p_col5)||p_col_del||=20
RPAD(NVL(cur_session_rec.osuser, 'UNKNOWN'), =
p_col6)||p_col_del||=20
RPAD(NVL(cur_session_rec.os_pid , 'UNKNOWN'), =
p_col7)||p_col_del||=20
RPAD(NVL(cur_session_rec.machine, 'UNKNOWN'), =
p_col8)||p_col_del||=20
RPAD(NVL(cur_session_rec.terminal, 'UNKNOWN'), =
p_col9)||p_col_del||=20
RPAD(NVL(cur_session_rec.image , 'UNKNOWN'), =
p_col10)||p_col_del||=20
RPAD(NVL(cur_session_rec.lockwait, 'UNKNOWN'), =
p_col11));
END LOOP; dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 = + p_col5 +=20
p_col6 + p_col7 + p_col8 + p_col9 + p_col10 + p_col11=20
+ p_col_cnt * LENGTH(p_col_del) - 1,'-'));
FOR cur_session_rec IN cur_session (gp_user_name) LOOP =20
IF cur_session_rec.connect_date =3D TO_CHAR(SYSDATE, 'DD-MON') THEN=20
v_connect_date :=3D NULL;
ELSE
v_connect_date :=3D ' '||cur_session_rec.connect_date;
END IF;
dbms_output.put_line('Execute ---> ALTER SYSTEM KILL SESSION =
'''||cur_session_rec.session_id||','||
cur_session_rec.serial#||''';' ||
' - to kill '||cur_session_rec.user_name||' =
(session id: '||=20
TO_CHAR(cur_session_rec.session_id)||') connected =
at '||=20
cur_session_rec.connect_time ||v_connect_date); =
END LOOP;
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4=20 + p_col5 + p_col6 + p_col7 + p_col8=20 + p_col9 + p_col10 + p_col11=20 + p_col_cnt * LENGTH(p_col_del) - 1,'-')); -- print_tail; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM||' found');
END; =20
PROCEDURE get_lock =20
IS
BEGIN
get_lock(NULL);
END; =20
PROCEDURE get_lock ( gp_obj_name IN VARCHAR2 )
IS
CURSOR cur_lock (p_obj_name VARCHAR2 ) IS
SELECT DISTINCT=20
SUBSTR(s.username,1,12) user_name,=20
--lc.sid ses_id, =09
lc.session_id ses_id, =09
SUBSTR(NVL(obj.object_name, rn.name), 1,25) obj_name,
SUBSTR(LTRIM(s.program),1,30) image ,
--SUBSTR(lc.type,1,26) a_lock_type,
--SUBSTR(lc.lmode,1,13) a_mode_held,
--SUBSTR(lc.request,1,13) a_mode_requested
SUBSTR(lc.lock_type,1,26) a_lock_type,
SUBSTR(lc.mode_held,1,13) a_mode_held,
SUBSTR(lc.mode_requested,1,13) a_mode_requested
FROM sys.dba_objects obj,=20
dba_locks lc,
--v$lock lc,
v$rollname rn,
v$session s
--WHERE obj.object_id (+) =3D lc.id1=20
WHERE obj.object_id (+) =3D lc.lock_id1=20
--AND lc.sid =3D s.sid=20
AND lc.session_id =3D s.sid=20
--AND trunc(lc.id1 / 65536)=3D rn.usn
AND trunc(lc.lock_id1 / 65536)=3D rn.usn
AND ( ( obj.object_name LIKE UPPER(NVL(p_obj_name, =
obj.object_name))||'%'
AND obj.owner not in ('SYS','SYSTEM') )=20
OR obj.object_name is NULL )=20
AND s.schemaname !=3D 'SYS'
--ORDER BY lc.sid
ORDER BY lc.session_id;
CURSOR cur_waiters (p_obj_name VARCHAR2 ) IS
SELECT SUBSTR(s.username,1,12) user_name,=20
lc.session_id ses_id, =09
SUBSTR(NVL(obj.object_name, rn.usn), 1,25) obj_name,
SUBSTR(LTRIM(s.program),1,30) image ,
SUBSTR(lc.lock_type,1,26) a_lock_type,
SUBSTR(lc.mode_held,1,13) a_mode_held,
SUBSTR(lc.mode_requested,1,13) a_mode_requested
FROM sys.dba_objects obj,=20
dba_locks lc,
v$rollname rn,
v$session s
WHERE obj.object_id (+) =3D lc.lock_id1=20
AND lc.session_id =3D s.sid=20
AND trunc(obj.object_id / 65536)=3D rn.usn
AND ( ( obj.object_name LIKE UPPER(NVL(p_obj_name, =
obj.object_name))||'%'
AND obj.owner not in ('SYS','SYSTEM') )=20
OR obj.object_name is NULL )=20
AND s.schemaname !=3D 'SYS'
--AND lc.mode_requested !=3D NULL
AND lc.mode_requested !=3D 'None'=20
ORDER BY lc.session_id;
p_col1 NUMBER;
p_col2 NUMBER;
p_col3 NUMBER;
p_col4 NUMBER;
p_col5 NUMBER;
p_col6 NUMBER;
p_col7 NUMBER;
p_col8 NUMBER;
p_col9 NUMBER;
p_col10 NUMBER;
p_col11 NUMBER;
p_col_cnt NUMBER;
v_line VARCHAR2(255) DEFAULT '-';
p_col_del VARCHAR2(2) DEFAULT ' ';
v_connect_date VARCHAR2(25);
BEGIN
dbms_output.enable (10000000);
p_col1 :=3D 12;
p_col2 :=3D 3;
p_col3 :=3D 25;
p_col4 :=3D 30;
p_col5 :=3D 26;
p_col6 :=3D 13;
p_col7 :=3D 13;
p_col_cnt :=3D 8;
/* print heading */
print_head;
b_found :=3D FALSE;
FOR cur_lock_rec IN cur_lock (gp_obj_name) LOOP =20
IF b_found =3D FALSE THEN
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 =
+ p_col5 + p_col6 + p_col7=20
+ p_col_cnt * LENGTH(p_col_del) - 1,'-'));
dbms_output.put_line(LPAD('User Name ', p_col1,' ')||p_col_del||' =
'||
LPAD('SID', p_col2 )||p_col_del||=20
RPAD('Object', p_col3 )||p_col_del||
RPAD('Program', p_col4 )||p_col_del||
RPAD('Type', p_col5 )||p_col_del||=20
RPAD('Mode held', p_col6 )||p_col_del||
RPAD('Mode reqs.', p_col7 ));
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 =
+ p_col5 + p_col6 + p_col7=20
+ p_col_cnt * LENGTH(p_col_del) - 1,'-'));
b_found :=3D TRUE; =20
END IF;
dbms_output.put_line( RPAD(NVL(cur_lock_rec.user_name, 'UNKNOWN'), =
p_col1)||p_col_del||=20
LPAD(TO_CHAR(cur_lock_rec.ses_id), =
p_col2)||p_col_del||=20
RPAD(NVL(cur_lock_rec.obj_name,'UNKNOWN'), =
p_col3)||p_col_del||=20
RPAD(NVL(cur_lock_rec.image, 'UNKNOWN'), =
p_col4)||p_col_del||=20
RPAD(NVL(cur_lock_rec.a_lock_type, 'UNKNOWN'), =
p_col5)||p_col_del||=20
RPAD(NVL(cur_lock_rec.a_mode_held, 'UNKNOWN'), =
p_col6)||p_col_del||=20
RPAD(NVL(cur_lock_rec.a_mode_requested, =
'UNKNOWN'), p_col7));
END LOOP;
IF b_found =3D TRUE THEN
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 + =
p_col5 + p_col6 + p_col7=20
+ p_col_cnt * LENGTH(p_col_del) - 1,'-'));
ELSE=20
dbms_output.put_line('No locks found');=20
END IF;
b_found :=3D FALSE;
FOR cur_lock_rec IN cur_waiters (gp_obj_name) LOOP =20
IF b_found =3D FALSE THEN
dbms_output.put_line('Session(s) waiting for lock');=20
dbms_output.put_line(LPAD('User Name ', p_col1,' ')||p_col_del||' =
'||
LPAD('SID', p_col2 )||p_col_del||=20
RPAD('Object', p_col3 )||p_col_del||
RPAD('Program', p_col4 )||p_col_del||
RPAD('Type', p_col5 )||p_col_del||=20
RPAD('Mode held', p_col6 )||p_col_del||
RPAD('Mode reqs.', p_col7 ));
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 =
+ p_col5=20
+ p_col6 + p_col7 + p_col_cnt * LENGTH(p_col_del) - 1,'-'));
b_found :=3D TRUE;
END IF;
dbms_output.put_line( RPAD(NVL(cur_lock_rec.user_name, 'UNKNOWN'), =
p_col1)||p_col_del||=20
LPAD(TO_CHAR(cur_lock_rec.ses_id), =
p_col2)||p_col_del||=20
RPAD(NVL(cur_lock_rec.obj_name,'UNKNOWN'), =
p_col3)||p_col_del||=20
RPAD(NVL(cur_lock_rec.image, 'UNKNOWN'), =
p_col4)||p_col_del||=20
RPAD(NVL(cur_lock_rec.a_lock_type, 'UNKNOWN'), =
p_col5)||p_col_del||=20
RPAD(NVL(cur_lock_rec.a_mode_held, 'UNKNOWN'), =
p_col6)||p_col_del||=20
RPAD(NVL(cur_lock_rec.a_mode_requested, =
'UNKNOWN'), p_col7));
END LOOP;
IF b_found =3D FALSE THEN
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 + =
p_col5 + p_col6 + p_col7=20
+ p_col_cnt * LENGTH(p_col_del) - 1,'-'));
ELSE=20
dbms_output.put_line('No waiting for the lock tansatctios found'); =
END IF;
-- print_tail;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM||' found');
END; =20
PROCEDURE get_rollback
IS
CURSOR cur_rollback IS
SELECT SUBSTR(rs.tablespace_name,1,12) tspace,
SUBSTR(name,1,12) rb_name,
extents ext,
rssize/(1024*1024) sizeMbt,
optsize/(1024*1024) optMbt,
shrinks,
aveshrink/1024 avrKbt,
wraps,
extends
FROM V$ROLLSTAT vs,
V$ROLLNAME vr,
sys.dba_rollback_segs rs
WHERE vs.usn =3D vr.usn
AND name =3D rs.segment_name
AND rs.tablespace_name !=3D 'SYSTEM'
ORDER BY rs.tablespace_name, rb_name;
p_col1 NUMBER;
p_col2 NUMBER;
p_col3 NUMBER;
p_col4 NUMBER;
p_col5 NUMBER;
p_col6 NUMBER;
p_col7 NUMBER;
p_col8 NUMBER;
p_col9 NUMBER;
p_col10 NUMBER;
p_col11 NUMBER;
p_col_cnt NUMBER;
v_line VARCHAR2(255) DEFAULT '-';
p_col_del VARCHAR2(2) DEFAULT ' ';
v_connect_date VARCHAR2(25);
BEGIN
dbms_output.enable (10000000);
p_col1 :=3D 12;
p_col2 :=3D 12;
p_col3 :=3D 8;
p_col4 :=3D 8;
p_col5 :=3D 8;
p_col6 :=3D 8;
p_col7 :=3D 8;
p_col8 :=3D 8;
p_col_cnt :=3D 8;
/* print heading */
print_head;
b_found :=3D FALSE;
FOR cur_rollback_rec IN cur_rollback LOOP =20
IF b_found =3D FALSE THEN
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 =
+ p_col5 + p_col6 + p_col7=20
+ p_col8 + p_col_cnt * LENGTH(p_col_del) - 1,'-'));
dbms_output.put_line(LPAD('Tablespace ', p_col1,' ')||p_col_del||' =
'||
RPAD('Rollback', p_col2 , ' ')||p_col_del||=20
LPAD('Extents', p_col3 )||p_col_del||
LPAD('Size Mbt', p_col4 )||p_col_del||
LPAD('Opt Mbt', p_col5 )||p_col_del||
LPAD('Shrinks', p_col6 )||p_col_del||=20
LPAD('Wraps', p_col7 )||p_col_del||
LPAD('Extend', p_col8 ));
dbms_output.put_line(RPAD(v_line, p_col1 + p_col2 + p_col3 + p_col4 =
+ p_col5 + p_col6 + p_col7=20
+ p_col8 + p_col_cnt * LENGTH(p_col_del) - 1,'-'));
b_found :=3D TRUE; =20
END IF;
dbms_output.put_line( RPAD(cur_rollback_rec.tspace, =
p_col1)||p_col_del||=20
RPAD(cur_rollback_rec.rb_name, =
p_col2)||p_col_del||=20
LPAD(TO_CHAR(cur_rollback_rec.ext, '99999'), =
p_col3)||p_col_del||=20
LPAD(TO_CHAR(cur_rollback_rec.sizeMbt, =
'99999.99'), p_col4)||p_col_del||=20
LPAD(TO_CHAR(cur_rollback_rec.optMbt, =
'99999.99'), p_col5)||p_col_del||=20
LPAD(TO_CHAR(cur_rollback_rec.shrinks), =
p_col6)||p_col_del||=20
LPAD(TO_CHAR(cur_rollback_rec.wraps), =
p_col7)||p_col_del||=20
LPAD(TO_CHAR(cur_rollback_rec.extends), =
p_col8)); =20
END LOOP;
EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM||' found');
END; =20
PROCEDURE print_head=20
IS
v_database VARCHAR2(15);=20
BEGIN DBMS_OUTPUT.ENABLE (10000000); SELECT name INTO v_database FROM V$DATABASE ;
/* print heading */
dbms_output.put_line(v_database||' database monitoring report');
dbms_output.put_line('generated on '||
TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS')||
' by '||USER||' user');
END; -- end of get_head procedure
PROCEDURE print_tail
IS
BEGIN
DBMS_OUTPUT.ENABLE (10000000);
dbms_output.put_line('end');
END; -- end of get_tail procedure
END db_monit; -- end of db_monit package body=20
/
------_=_NextPart_000_01C0389D.C204F6A0
Content-Type: application/octet-stream;
name="dbreport.sh"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="dbreport.sh"
#! /bin/ksh
#
# name dbreport.sh
#
# purpose Run monitoring repoprts =20
# usage dbreport.sh <dbname>=20
#
# parameters $1=3Ddbname ( database name optional, if not passed=20
# all oratab databases are checked)
#***********************************************************************=
******
#* MAINTENANCE LOG =
*
#* --------------- =
*
#* DATE WHO DESCRIPTION =
*
#* ------- -------------- =
----------------------------------- *
#* 09/03/99 AAfanassiev Created =
*
#***********************************************************************=
OUT=3D${LOGS}/${JOB}.trc =20
echo "$JOBNAME_SHORT: sqlplus output is redirected to "
echo "$JOBNAME_SHORT: to $OUT"
DBNAME=3D$1
# validate DBNAME
if [ `echo $SIDLIST |grep -c $DBNAME` -eq 0 ]; then
echo "$JOBNAME_SHORT: Invalid <dbname> : $DBNAME does not exists"
echo "$JOBNAME_SHORT: Must be one of: `echo $SIDLIST`"
exit ${ERROR}
else
# reset list to DBNAME
SIDLIST=3D${DBNAME}
echo "$JOBNAME_SHORT: Failed to set oracle env vars. Skipping =
$DBNAME ..."
else
# check if the instance is running
if [ `ps -fu oracle |grep ora_ | \
grep ${ORACLE_SID} | wc -l` -ge $ORA_PROCESS_CNT ]; then
#SHUTDOWN_AFTER=3D${TRUE}=20
#echo "$JOBNAME_SHORT: Starting up ${ORACLE_SID} database..."
#$ORACLE_HOME/bin/svrmgrl >> $OUT << EOF
#connect internal
#startup open pfile=3D${PFILE}
#EOF
# make sure that the database is open
STATUS=3D`${MONITOR}/dbstatus.sh | grep -v "Available ORACLE" | =
grep $ORACLE_SID |grep -c OPEN`
if [ $STATUS -gt 0 ]; then
OPEN=3D${TRUE}
else=20
echo "$JOBNAME_SHORT: ${ORACLE_SID} database is up but not =
open. Skipping ..."
OPEN=3D${FALSE}
fi
else
OPEN=3D${FALSE}
echo "$JOBNAME_SHORT: ${ORACLE_SID} database is down. Skipping =
..."
#SHUTDOWN_AFTER=3D${FALSE}=20
$ORACLE_HOME/bin/svrmgrl >> $OUT << EOF
connect internal
REM SELECT on the views below is required
REM to access them from PL/SQL code (db_monit.sql)
REM disable out to exclude ORA- error when
REM trying to drop DBA_LOCKS view
REM as it does not exist and nevr created
set termout off
@$ORACLE_HOME/rdbms/admin/catblock.sql
set termout on =20
grant select on dba_data_files to OPS\$${USER};
grant select on dba_free_space to OPS\$${USER};
grant select on dba_segments to OPS\$${USER};
grant select on dba_tablespaces to OPS\$${USER};
grant select on dba_tables to OPS\$${USER};
grant select on dba_objects to OPS\$${USER};
grant select on dba_locks to OPS\$${USER};
grant select on dba_rollback_segs to OPS\$${USER};
grant select on v_\$session to OPS\$${USER};
grant select on v_\$sysstat to OPS\$${USER};
grant select on v_\$rollstat to OPS\$${USER};
grant select on v_\$sesstat to OPS\$${USER};
grant select on v_\$rollname to OPS\$${USER};
grant select on v_\$timer to OPS\$${USER};
grant select on v_\$database to OPS\$${USER};
connect / =20
@${MONITOR}/db_monit.sql
set echo off feedback off verify off
set serveroutput on lines 132 pages 999
spool ${LOGS}/${JOB}_${ORACLE_SID}.rpt
execute db_monit.ts_space;
PROMPT --------------------------------------------------------
spool off
# analyze output=20
DBTITLE=3D$TRUE=20
grep -i "\.\.\." ${LOGS}/${JOB}_${ORACLE_SID}.rpt | while read =
LINE
do
Tablespace=3D`echo $LINE | awk '{print $1}'`
FreeExtents=3D`echo $LINE | awk '{print $4}'`
UsedPerc=3D`echo $LINE | awk '{print $9}'`
if [ ${UsedPerc} -gt ${PERC_USED} -a "${Tablespace}" !=3D =
"Total......." ]; then
if [ ${TITLE} -eq ${TRUE} ]; then
echo "`hostname` server report">> $MESSAGE_FILE
echo >> $MESSAGE_FILE=20
TITLE=3D$FALSE
fi
if [ ${DBTITLE} -eq ${TRUE} ]; then
echo >> $MESSAGE_FILE=20
echo "ATTENTION: More than ${PERC_USED}% of the space is =
already used in the following $ORACLE_SID tablespaces" >> $MESSAGE_FILE
echo " (more details in =
${LOGS}/${JOB}_${ORACLE_SID}.rpt)" >> $MESSAGE_FILE=20
echo >> $MESSAGE_FILE=20
echo =
"-----------------------------------------------------------------------=
-">> $MESSAGE_FILE
echo 'Tablespace\tUsed Space %\tAvail. extents (estim.)' =
>> $MESSAGE_FILE
echo =
"-----------------------------------------------------------------------=
-">> $MESSAGE_FILE
DBTITLE=3D$FALSE
fi=20
echo $Tablespace"\t"$UsedPerc"\t\t\t\t"$FreeExtents >> =
$MESSAGE_FILE
fi
done
else
echo "$JOBNAME_SHORT: Failed to create =
${LOGS}/${JOB}_${ORACLE_SID}.rpt file..."
fi
#
# recompile and check invalid objects and triggers
for I in "1 2"; do=20
$ORACLE_HOME/bin/sqlplus -s / >> $OUT << EOF
set pause off feedback off echo off verify off
set head off pages 999 lines 255
-- Check triger status
SELECT owner||'.'||trigger_name|| =20
' trigger on '||trigger_type||' event '||
triggering_event||' into/from '||
table_owner||'.'||table_name||' table'||' is '||status=20
FROM sys.dba_triggers
WHERE status =3D 'DISABLED'=20
order by owner, trigger_name;=20
-- Recompile invalid objects
spool ${LOGS}/${JOB}_${ORACLE_SID}.gql
SELECT 'ALTER TRIGGER '|| owner||'.'||
trigger_name||' compile;'
FROM dba_triggers
WHERE status =3D 'INVALID' ;
SELECT 'ALTER '||object_type||' '||owner||'.'||
object_name|| ' COMPILE;'
FROM dba_objects=20
WHERE status =3D 'INVALID'=20
AND object_type IN ('PACKAGE', 'PROCEDURE',
'TRIGGER', 'VIEW');
spool off=20
@${LOGS}/${JOB}_${ORACLE_SID}.gql=20
EOF
if [ ${TITLE} -eq ${TRUE} ]; then
echo "`hostname` server report" >> $MESSAGE_FILE
echo >> $MESSAGE_FILE
TITLE=3D$FALSE
fi
echo >> $MESSAGE_FILE
echo "ATTENTION: Invalid objects are found in $ORACLE_SID =
database (sql saved in ${LOGS}/${JOB}_${ORACLE_SID}.gql file)" >> =
$MESSAGE_FILE
cat ${LOGS}/${JOB}_${ORACLE_SID}.gql >> ${MESSAGE_FILE}=20
echo >> $MESSAGE_FILE
## shutdown database if it was started by the script
##
#if [ ${SHUTDOWN_AFTER} -eq ${TRUE} ]; then
# if [ `ps -fu oracle |grep ora_ | \
#grep ${ORACLE_SID} | wc -l` -ge ${ORA_PROCESS_CNT} ]; then
# echo "$JOBNAME_SHORT: Shutting down ${ORACLE_SID} =
database..."
# $ORACLE_HOME/bin/svrmgrl >> $OUT << EOF
# connect internal
# shutdown immediate;=09
# Send warning email
${TOOLS}/sendmail.sh ${MESSAGE_FILE} "$JOBNAME_SHORT: Database(s) =
monitoring report!" ${CCFLAG}
fi
if [ -f ${MESSAGE_FILE} ]; then
rm ${MESSAGE_FILE}
else
echo "$JOBNAME_SHORT: No message file generated"
fi
echo "$JOBNAME_SHORT: Completed monitoring reports..."
exit=20
------_=_NextPart_000_01C0389D.C204F6A0
Content-Type: application/octet-stream;
name="sendmail.sh"
Content-Disposition: attachment;
filename="sendmail.sh"
#!/bin/ksh # # Program Name: @(#) sendmail.sh # # This script sends status via emailReceived on Tue Oct 17 2000 - 19:52:50 CDT
 |
 |