Skip to Main Content
  • Questions
  • In a single row I want to display the table name and number of rows in it.

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: September 29, 2007 - 1:53 am UTC

Last updated: February 16, 2009 - 10:40 am UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

In a single row I want to display the table name and number of rows in it.

Eg:

I want output like....

TNAME NoOfRecords

EmployeeMaster 1000
DepartmentMaster 15
DesignationMaster 26
.
.
.
.
.

and Tom said...

If you gather statistics on a regular enough basis,

select table_name, num_rows from user_tables;


will get you a good "guesstimate". The other (slow, resource intensive, not at all suggested, really bad idea) approach would be:

scott%ORA10GR2> create or replace function count_rows( p_tname in varchar2 )
  2  return number
  3  as
  4          l_cnt number;
  5  begin
  6          for x in ( select * from user_tables where table_name=upper(p_tname) )
  7          loop
  8                  execute immediate 'select count(*) from "' || upper(p_tname) || '"' into l_cnt;
  9          end loop;
 10          return l_cnt;
 11  end;
 12  /

Function created.

scott%ORA10GR2>
scott%ORA10GR2> select table_name, count_rows( table_name ), num_rows from user_tables;

TABLE_NAME                     COUNT_ROWS(TABLE_NAME)   NUM_ROWS
------------------------------ ---------------------- ----------
EMP                                                15         15
DEPT                                                4          4
BONUS                                               0          0
SALGRADE                                            5          5
DUMMY                                               1          1
T1                                                  0          0
T                                               40868      40704
SYS_TEMP_FBT                                        0
RUN_STATS                                           0

9 rows selected.



Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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
Tom Kyte
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.
Tom Kyte
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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library