select count(*) from TABELAS_DO_USUARIO

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 11 Apr 2006 3:33 pm

Good afternoon, everyone This is my first post here: I have no experience with sql, but I need a lot of one that returns a count (*) from the tables of the current user, more or less like this: 01 02 TABLE TABLE = 1789 = 03 = TABLE TABLE 5890 3010 04 = 2410 ...

If you have something ready to contribute this tell me and sorry for the unnecessary post.

Thank you for your attention
MarcioRM
Location: Curitiba / PR

Poston Tue, 11 Apr 2006 4:49 pm

Well, there's nothing AUTOMATIC that does this ...

The more automatic you have is you consult the USER_TABLES. In this dictionary, table has a field called NUM_ROWS, which has the number of rows in the table.

See:
Code: Select all
SQL> select table_name, num_rows, last_analyzed
  2  from user_tables
  3  order by num_rows desc nulls last
  4  /

TABLE_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
TB_EMBARQUE_DEMONSTRATIVO        79894226 26-MAR-06
TB_EMBARQUE                      78932637 26-MAR-06
TB_LOG_RECEPCAO_AVERBCOL         29269790 26-MAR-06
TB_LOG_EMBARQUE                  18651547 26-MAR-06
Note that the largest table here has 79 million lines.
It is clear that such information is false, because this field NUM_ROWS corresponds to the number of rows that had the day was done the last ANALYZE table. In other words, if you does not collect statistics on your seat periodically, maybe wrong information in this field.

Let's prove it: note that made the count now and my table has 81 million rows! (against 79 mi collected on 26 March)
Code: Select all
SQL> select count(*) from TB_EMBARQUE_DEMONSTRATIVO;

  COUNT(*)
----------
  81159845

SQL>
another way to do this, you create a script that GENERATES the sql ´ s with COUNT. Like this:
Code: Select all
SQL> select 'SELECT COUNT(*) FROM '||table_name||';'
  2  from user_tables
  3  order by num_rows desc nulls last
  4  /

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
SELECT COUNT(*) FROM TB_EMBARQUE_DEMONSTRATIVO;
SELECT COUNT(*) FROM TB_EMBARQUE;
SELECT COUNT(*) FROM TB_LOG_RECEPCAO_AVERBCOL;
SELECT COUNT(*) FROM TB_LOG_EMBARQUE;
SELECT COUNT(*) FROM TT_LOG_EXCLUSAO;
SELECT COUNT(*) FROM TT_RECEPCAO_AVERBCOL;
SELECT COUNT(*) FROM TT_RECEPCAO_AVERBCOL_OK;
SELECT COUNT(*) FROM TT_CONCENTRACAO_RISCO;
SELECT COUNT(*) FROM TB_RATEIO_MOVIMENTO;
SELECT COUNT(*) FROM TB_COMISSAO;
SELECT COUNT(*) FROM TB_AUTO_COBERTURA;
SELECT COUNT(*) FROM TB_PARCELA_LOG;
SELECT COUNT(*) FROM TB_RATEIO_MOVIMENTO_CWI;
SELECT COUNT(*) FROM TB_MOVIMENTO_FINANCEIRO;
SELECT COUNT(*) FROM TB_LOG_LIQ_MOVIMENTO;
SELECT COUNT(*) FROM TB_ARQUIVO_EMBARQUE;
SELECT COUNT(*) FROM TB_MOVIMENTO_FINANCEIRO_CWI;
SELECT COUNT(*) FROM TB_RATEIO_GAP;
SELECT COUNT(*) FROM TB_ERRO_AVERBNET;
SELECT COUNT(*) FROM TB_PERC_COMISSAO_LOG;
SELECT COUNT(*) FROM TB_PARCELA_RECIBO;
SELECT COUNT(*) FROM TB_PARCELA_NOTA;
SELECT COUNT(*) FROM TB_RATEIO_MOVIMENTO_FABIO;
SELECT COUNT(*) FROM TB_MOVIMENTO_FINANCEIRO_FABIO;
SELECT COUNT(*) FROM TB_ITEM_PROPOSTA_CONTRATO;
SELECT COUNT(*) FROM TB_REGRA_PROCESSO_SEGURADO;
SELECT COUNT(*) FROM TB_PARCELA;
SELECT COUNT(*) FROM TB_GAP;
SELECT COUNT(*) FROM TB_LOG_LIQ_MOVIMENTO_FABIO;
Hence, simply run the script,:-):-
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Wed, 12 Apr 2006 11:22 am

Many thanks dr_gori one more thing ...:D

How could I do to display of which table is each \"count\".
I generated the script by setting the arquivo.txt spool for the two cases, but the file with the totals have been generated without the table name, like this: COUNT (*)----------87855 count(*)----------6 would be better like this: SELECT count(*) FROM table)!
COUNT (*)----------87855 SELECT count(*) FROM table)!
COUNT (*)----------6 How could I do that?

Thank you for your attention
MarcioRM
Location: Curitiba / PR

Poston Wed, 12 Apr 2006 11:33 am

That this stewardship forum, é?

Code: Select all
select 'SELECT '''||table_name||': '' TABELA, COUNT(*) FROM '||table_name||';'
from user_tables
order by num_rows desc nulls last


:-o
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Fri, 14 Apr 2006 3:08 pm

Thank you very much dr_gori.

I hope in a few days to be collaborating with the forum rather than just take advantage of perks him:D

Thanks
MarcioRM
Location: Curitiba / PR


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 2 guests