[Dica] SQL com gráfico de percentual

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Até onde vai a criatividade humana...

Esse SQL mostra uma barra ao lado representando o percentual:

Selecionar tudo

set linesize 132
set numformat 99990d0
column "Tablespace" format a16
column "Used %" format a46
ttitle 'TABLESPACES USE REPORT' skip 2

select ts "Tablespace", tot "Total Mb", free "Free Mb",
lpad('I', used*40, 'I')||' '||to_char(used*100, 'fm990d0')||'%' "Used %"
from
(
select total.tablespace_name ts, total.mb tot, free.mb free, (total.mb - free.mb)/total.mb used
from
(
select tablespace_name, round(sum(bytes/1048576),1) mb
from dba_data_files
group by tablespace_name
) total,
(
select tablespace_name, round(sum(bytes/1048576),1) mb
from dba_free_space
group by tablespace_name
) free
where total.tablespace_name = free.tablespace_name
)
order by 1
/
Exemplo:

Selecionar tudo

SQL> set linesize 132
SQL> set numformat 99990d0
SQL> column "Tablespace" format a16
SQL> column "Used %" format a46
SQL> ttitle 'TABLESPACES USE REPORT' skip 2
SQL> select ts "Tablespace", tot "Total Mb", free "Free Mb",
  2  lpad('I', used*40, 'I')||' '||to_char(used*100, 'fm990d0')||'%' "Used %"
  3  from
  4  (
  5  select total.tablespace_name ts, total.mb tot, free.mb free, (total.mb - free.mb)/total.mb used
  6  from
  7  (
  8  select tablespace_name, round(sum(bytes/1048576),1) mb
  9  from dba_data_files
 10  group by tablespace_name
 11  ) total,
 12  (
 13  select tablespace_name, round(sum(bytes/1048576),1) mb
 14  from dba_free_space
 15  group by tablespace_name
 16  ) free
 17  where total.tablespace_name = free.tablespace_name
 18  )
 19  order by 1
 20  /

TABLESPACES USE REPORT

Tablespace       Total Mb  Free Mb Used %
---------------- -------- -------- ----------------------------------------------
DRSYS                20.0     10.3 IIIIIIIIIIIIIIIIIII 48.5%
EXAMPLE             145.6      0.4 IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 99.7%
INDX                 25.0     24.9  0.4%
ODM                  20.0     10.8 IIIIIIIIIIIIIIIIII 46.0%
SAS_TABLES        36996.0      3.9 IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 100.0%
SYSTEM              380.0      7.7 IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 98.0%
TOOLS                71.9      1.1 IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 98.5%
UNDOTBS1            620.0    608.7  1.8%
USERS                25.0     24.9  0.4%
XDB                  46.9      2.7 IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 94.2%

10 rows selected.

SQL> 
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 7 visitantes