::
==========================================================
::
TwoPanelTabIndSizeNumRows.bat
:: Compare Sizes and Numrows data
for tables and indexes side-by-side.
::
::
==========================================================
::
Jerome Christopher [Jerome_christopher@yahoo.com
:: http://sqlhtm.blogspot.com ::
http://www.sqlhtm.com/sqlhtm ::
==========================================================
del
c:\11.out c:\12.out
(
echo spool c:\1.out
echo set pages 1000
lines 200 echo off feedback off
echo prompt
^<html^>^<head^>
echo set heading off feedback off
echo select
^'^<^'^|^|^'style
type^=^'^|^|^'^'^'^'^|^|^'text^'^|^|^'^/^'^|^|^'css^'^|^|^'^'^'^'^|^|^'^>^'^|^|
chr^(10^)^|^|
echo ^'body ^{font^:6pt
Arial^,Helvetica^,sans^-serif^; color^:black^;
background^:^}^'^|^|chr^(10^)^|^|
echo
^'p ^{font^:bold 6pt
Arial^,Helvetica^,sans^-serif^; color^:black^;
background^:^}^'^|^|chr^(10^)^|^|
echo ^'table ^{font^:6pt
Arial^,Helvetica^,sans^-serif^; color^:Black^;
background^:^#f7f7e7^;^}^'^|^|chr^(10^)^|^|
echo
^'tr ^{font^:6pt Arial^,Helvetica^,sans^-serif^;
color^:Black^; background^:^#f7f7e7^;^}^'^|^|chr^(10^)^|^|
echo
^'td ^{font^:bold bold 7pt
Arial^,Helvetica^,sans^-serif^; color^:black^;
background^:aqua^;^}^'^|^|chr^(10^)^|^|
echo
^'th ^{font^:bold 6pt
Arial^,Helvetica^,sans^-serif^; color^:white^; background^:olive^;
^}^'^|^|chr^(10^)^|^|
echo ^'a
^{font^:6pt Arial^,Helvetica^,sans^-serif^; color^:^#663300^;
background^:^#ffffff^; vertical^-align^:top^;^}^'^|^|chr^(10^)^|^|
echo ^'^<^'^|^|^'^/^'^|^|^'style^>^' from dual^;
echo
prompt ^<^/head^>^<body^>
echo select ^'Two Panel Compare Tool - Tables and
Indexes - By Size And Number of Rows^' from dual;
echo prompt
^<h1^>Top20 Tables By Size And NumRows^<^/h1^>
echo
select name,to_char^(sysdate,^'dd-mon-yy hh:mi:ss pm Dy^'^) Today
from v$database;
echo set heading on feedback on
echo set
markup HTML ON table ^"^" entmap off
echo prompt
^<table^>^<tr^>^<td^>
echo SELECT rownum No,
c.^* FROM ^(
echo SELECT a.owner, a.segment_type,
a.segment_name, b.last_analyzed last_an,
echo
sum^(a.bytes^)^/1024^/1024 mb, b.num_rows num_rows
echo FROM
dba_segments a, dba_tables b
echo WHERE a.owner NOT LIKE
^'SYS%%^' AND a.segment_type=^'TABLE^'
echo AND a.owner=b.owner
AND a.segment_name=b.table_name
echo GROUP BY a.owner,
a.segment_type, a.segment_name, b.last_analyzed, b.num_rows
echo
ORDER BY mb DESC
echo ^) c
echo WHERE rownum^<21;
echo set heading off
echo set feedback off
echo spool off
rem echo prompt
=====================================================
rem echo
prompt
^'^<^/pre^>^<^/td^>^<^/tr^>^<^/table^>^' ^;
echo prompt
^<^/td^>^<^/tr^>^<^/table^>^<^/body^>^<^/html^>
echo exit
)> c:\1.bat
(
echo spool c:\1.out
echo set pages 1000
lines 200 echo off feedback off
echo prompt
^<html^>^<head^>
echo set heading off feedback off
echo select
^'^<^'^|^|^'style
type^=^'^|^|^'^'^'^'^|^|^'text^'^|^|^'^/^'^|^|^'css^'^|^|^'^'^'^'^|^|^'^>^'^|^|
chr^(10^)^|^|
echo ^'body ^{font^:6pt
Arial^,Helvetica^,sans^-serif^; color^:black^;
background^:^}^'^|^|chr^(10^)^|^|
echo
^'p ^{font^:bold 6pt
Arial^,Helvetica^,sans^-serif^; color^:black^;
background^:^}^'^|^|chr^(10^)^|^|
echo ^'table ^{font^:6pt
Arial^,Helvetica^,sans^-serif^; color^:Black^;
background^:^#f7f7e7^;^}^'^|^|chr^(10^)^|^|
echo
^'tr ^{font^:6pt Arial^,Helvetica^,sans^-serif^;
color^:Black^; background^:^#f7f7e7^;^}^'^|^|chr^(10^)^|^|
echo
^'td ^{font^:bold bold 7pt
Arial^,Helvetica^,sans^-serif^; color^:black^;
background^:aqua^;^}^'^|^|chr^(10^)^|^|
echo
^'th ^{font^:bold 6pt
Arial^,Helvetica^,sans^-serif^; color^:white^; background^:olive^;
^}^'^|^|chr^(10^)^|^|
echo ^'a
^{font^:6pt Arial^,Helvetica^,sans^-serif^; color^:^#663300^;
background^:^#ffffff^; vertical^-align^:top^;^}^'^|^|chr^(10^)^|^|
echo ^'^<^'^|^|^'^/^'^|^|^'style^>^' from dual^;
prompt ^<^/head^>^<body^>
echo select ^'Two Panel Compare Tool - Top20
Tables and Indexes - By Size And Number of Rows^' from dual;
echo prompt ^<h1^>Top20 Indexes By Size And
NumRows^<^/h1^>
echo select
name,to_char^(sysdate,^'dd-mon-yy hh:mi:ss pm Dy^'^) Today from
v$database;
echo set heading on feedback on
echo set markup
HTML ON table ^"^" entmap off
echo prompt
^<table^>^<tr^>^<td^>
echo SELECT rownum No,
c.^* FROM ^(
echo SELECT a.owner, a.segment_type,
a.segment_name, b.last_analyzed last_an,
echo
sum^(a.bytes^)^/1024^/1024 mb, b.num_rows num_rows
echo FROM
dba_segments a, dba_indexes b
echo WHERE a.owner NOT LIKE
^'SYS%%^' AND a.segment_type=^'INDEX^'
echo AND a.owner=b.owner
AND a.segment_name=b.index_name
echo GROUP BY a.owner,
a.segment_type, a.segment_name, b.last_analyzed, b.num_rows
echo
ORDER BY mb DESC
echo ^) c
echo WHERE rownum^<21;
echo set heading off
echo set feedback off
echo spool off
rem echo prompt
=====================================================
rem echo
prompt ^'^<^/td^>^<^/tr^>^<^/table^>^' ^;
echo
prompt
^<^/td^>^<^/tr^>^<^/table^>^<^/body^>^<^/html^>
echo exit
)> c:\2.bat
(
echo ^<html^>
echo
^<head^>
echo ^<title^>
echo
TwoPanelTabIndSizeNumRows
echo
^<^/title^>
echo ^<^/head^>
echo ^<FRAMESET COLS^=^"600^,*^" border^=1^>
echo ^<FRAME NAME^=^"top^"
SRC^=^"c^:^\11.htm^" marginwidth^=^"10^" scrolling^=^"Auto^"^>
echo ^<FRAME NAME^=^"bottom^"
SRC^=^"c^:^\12.htm^" marginwidth^=^"10^" scrolling^=^"Auto^"^>
echo ^<^/FRAMESET^>
echo ^<^/html^>
)>c:\tptisnr.hta
c:\tptisnr.hta
exit
echo prompt Two Panel Compare Tool - Tables
and Indexes Last Analyzed.
echo prompt For
tiny dbs, start and go for coffee.
echo
prompt For medium dbs, start and go for lunch.
echo prompt For VLDBS, start and go take a
vacation :^)
