Smart Forever, October 01, 2007 - 12:35 am UTC
Great and Thanks.
dosen't work
sagar desai, October 01, 2007 - 6:47 am UTC
when i am issuing such sql statement it return num_rows
column as null and not the number of rows from the user_tables table
October 03, 2007 - 2:45 pm UTC
I did start by saying....
If you gather statistics on a regular enough basis,
Gary Dean, October 01, 2007 - 7:26 am UTC
you need to gather statistics first. Like this.
13:18:19 dea@GLCHDBD1> create table stat_test (id number);
Table created.
Elapsed: 00:00:00.00
13:18:30 dea@GLCHDBD1> insert into stat_test values (1);
1 row created.
Elapsed: 00:00:00.00
13:18:37 dea@GLCHDBD1> insert into stat_test values (2);
1 row created.
Elapsed: 00:00:00.00
13:18:39 dea@GLCHDBD1> insert into stat_test values (3);
1 row created.
Elapsed: 00:00:00.00
13:18:41 dea@GLCHDBD1> commit;
Commit complete.
Elapsed: 00:00:00.00
13:18:48 dea@GLCHDBD1> select num_rows from user_tables where table_name='STAT_TEST';
NUM_ROWS
----------
<NULL>
1 row selected.
Elapsed: 00:00:00.00
13:18:53 dea@GLCHDBD1> analyze table stat_test compute statistics;
Table analyzed.
Elapsed: 00:00:00.00
13:18:56 dea@GLCHDBD1> select num_rows from user_tables where table_name='STAT_TEST';
NUM_ROWS
----------
3
1 row selected.
Elapsed: 00:00:00.00
13:18:57 dea@GLCHDBD1>
RE: In a single row I want to display the table name and number of rows in it
Frank Zhou, October 01, 2007 - 11:19 am UTC
If your database is Oracle 10G,
the following query can be used to retrieve the resultset.
----------------------
select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
,'/ROWSET/ROW/X')) count
from user_tables
----where rownum <5;
Thanks,
Frank
Better way to get table count
Russell Ramaswamy, October 05, 2007 - 2:25 am UTC
Here's a way to it without using a stored proc but in a simple query
But before in sql + do something like
SET LINESIZE 200
set feedback off
set heading off
set echo off
set verify off
I usually have a script called turnoff.sql which contains the above statements
spool c:\tblcount.sql
select 'select count(*), ' || '''' || table_name || '''' ||' from ' || table_name from dba_tables
where owner='value'
/
spool off
@tblcount
Last ddl
Sandro, October 05, 2007 - 11:10 am UTC
I think that would be able is interesting to know for any table:
table_name = table name
num_rows = number of rows estimated/computed
count_num_rows = "correct" numbers of rows
last_analyzed = date of last analyze
last_dml = last ddl operation like insert/update/delete
How I can know "last_dml" ?
RE: In a single row I want to display the table name and number of rows in it
Edwin O'Neill, February 13, 2009 - 5:30 am UTC
Thanks to Frank for the query to count rows using xml. Just a query to anyone as to whether when using this did any of the tables have a column of datatype clob. I have found that the query only works with tables not using clob columns. Perhaps there is a way of enhancing this query to enable a count of rows in a table with clob columns.
February 16, 2009 - 10:40 am UTC
it has no problems with a clob, it just does a count(*), it doesn't really 'touch' any of the columns:
ops$tkyte%ORA10GR2> create table t ( x int, y clob );
Table created.
ops$tkyte%ORA10GR2> insert into t select rownum, 'xxxx' from all_users;
40 rows created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> set echo on
ops$tkyte%ORA10GR2> @test
ops$tkyte%ORA10GR2> select table_name,
2 to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
3 ,'/ROWSET/ROW/X')) count
4 from user_tables
5 /
TABLE_NAME COUNT
------------------------------ ----------
C 49782
KEY_LIST 100
TTTT 0
T1 40
MLOG$_T1 0
T_MV 40
TABLE_NAME1 5
TKTSTATUS 6
T 40
O 0
T$_KEY_LIST 0
RUN_STATS 0
12 rows selected.