Good afternoon to all
This is my first post here:
I do not have experience with SQL, but I need a lot of one that returns me a count (* ) of current user tables, more or less like this:
Table 01 = 1789
Table 02 = 3010
Table 03 = 5890
Table 04 = 2410
...
If you have something ready to tell me this tell me and sorry for the unnecessary post.
Thanks for the attention
select count (*) from TABELAS_DO_USUARIO
- dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Well, there is nothing automatic that does this ...
The most automatic you have is for you to consult the User_Tables. In this dictionary table, it has a field called num_rows, which has the amount of rows of the table.
See:
Note that the largest table here has 79 million lines!
Of course this information is false, because this Num_rows field corresponds to the number of lines that had the last analyze the table. That is, if you do not collect statistics on your bank periodically, you may have information very wrong in this field.
Let's prove this:
Note that I did the count now and my table has 81 million lines! (against 79 mi collected on March 26)
Another way to do this, is you create A script that generates SQL's with count. Thus:
Hence, just run the script,
: -O
The most automatic you have is for you to consult the User_Tables. In this dictionary table, it has a field called num_rows, which has the amount of rows of the table.
See:
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
Of course this information is false, because this Num_rows field corresponds to the number of lines that had the last analyze the table. That is, if you do not collect statistics on your bank periodically, you may have information very wrong in this field.
Let's prove this:
Note that I did the count now and my table has 81 million lines! (against 79 mi collected on March 26)
SQL> select count(*) from TB_EMBARQUE_DEMONSTRATIVO;
COUNT(*)
----------
81159845
SQL>
Another way to do this, is you create A script that generates SQL's with count. Thus:
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, just run the script,
: -O
Thank you so much Dr_Gori
One more thing ...: D
How I could do to display which table is every "count".
I generated the script by setting the spool file.txt for the two cases,
but the file with the totals was generated without the table name, thus:
[0]]
would be better:
How could I do this?
Thanks for the attention
One more thing ...: D
How I could do to display which table is every "count".
I generated the script by setting the spool file.txt for the two cases,
but the file with the totals was generated without the table name, thus:
[0]]
COUNT(*)
----------
6
SELECT COUNT(*) FROM TABELA)!
COUNT(*)
----------
87855
SELECT COUNT(*) FROM TABELA)!
COUNT(*)
----------
6
Thanks for the attention
- dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
What stewardship this forum, huh?
: -O
select 'SELECT '''||table_name||': '' TABELA, COUNT(*) FROM '||table_name||';'
from user_tables
order by num_rows desc nulls last
-
- Information
-
Who is online
Users browsing this forum: No registered users and 12 guests