• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 895
  • Last Modified:

No of records in Table in Oracle 10g

How I can know the no of records in Table in Oracle 10g by SQL Plus ?

500 points for correct answer
0
interbus
Asked:
interbus
  • 20
  • 13
  • 4
  • +2
1 Solution
 
k_murli_krishnaCommented:
First connect to database i.e. if it is SQL*PLus then give scott/tiger@service in top most field or each in different fields.
If it is just SQL Plus, then:
connect scott@service
Password: tiger
SQL> select count(1) from emp;
You can use count(*) as well but * counts based on whole record and 1 based on PK column & hence is faster. So, use COUNT(1) only.
You can say:
SQL> spool D:\emp_count.txt
SQL> select count(1) from emp;
SQL> spool off
The result of number of records in emp table is captured in the text file.

If you want, you can capture entire data in a text file like:
SQL> spool D:\emp_data.txt
SQL> select * from emp;
SQL> spool off
Open this file in a editor like textpad, editplus etc. and view line numbers to see records as well as their total count. You can also do the following to see how much time each query takes:
SQL> set timing on;



0
 
QlemoC++ DeveloperCommented:
The exact count is only available by using
  select count(*) from yourtable;

The approximative count (based on last statistic collection) can be retrieved by
  select table_name, num_rows from user_tables

0
 
k_murli_krishnaCommented:
If the table average row size is just fitting in a block, then the number of rows will be the number of blocks used for the table. One can also use inbuilt as well as third party tools like DBArtisan etc. to directly see the count of records against each and every table.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
QlemoC++ DeveloperCommented:
@k_murli_krishna:
Count(1) being faster is a myth. There were some issues using Ora 7 (or earlier, using a rule based optimizer), but nowadays the cost based query optimizer knows of the best way to retrieve the count!
0
 
interbusAuthor Commented:
There another question please

How I found the count for each table in the database in one command ?
That meaning I want to make automatic command to find all no of records for each table samiler as
SELECT table_name, column_name FROM all_tab_columns;

Any answer for this ?
0
 
QlemoC++ DeveloperCommented:
Exact count?
0
 
interbusAuthor Commented:
YES exact count
0
 
interbusAuthor Commented:
Exatly I want the output for this select Tablename with No of records for each table in database in one time
0
 
QlemoC++ DeveloperCommented:
No way. You will have to go thru all tables and count the records there, so this is nothing you will like to do!
0
 
k_murli_krishnaCommented:
You can generate a script using a SELECT statement like this after connecting to database with a user which is nothing but schema as well & this is the only one considered for USER_TABLES:
SQL> spool D:\scott_schema_tables_scs.txt
SELECT 'SELECT COUNT(1) FROM ' || table_name || ';' FROM USER_TABLES;
SQL> spool off
Now, execute the script while spooling result to another file:
SQL> spool D:\scott_schema_tables_scs_msg.txt
SQL> @D:\scott_schema_tables_scs.txt
SQL> spool off

This is a fantastic way of generating scripts from meta data of data dictionary i.e. it is a semi-automatic method. The direct way is to write a stored procedure of the following query:
Select '''||table_name||' : ''||count(*) from '||table_name||';',
    to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
from user_tables
order by table_name;

I have given you additionally SYSDATE and user. You can remove from SELECT list. You can use either COUNT(1) or COUNT(*) or COUNT(ROWID). num_rows suggested by Qlemo will be quite accurate provided the statistics are updated frequently enough and with 10g at least reorgranization is automatic.
If the row size of the table is just fitting in a block, then getting number of blocks for a table may also help. Hope you got what you wanted.

0
 
interbusAuthor Commented:
Hello k_murli_krishna

Please check your examples is give me errors, therefore checking with SQL Plus before you write your example here
0
 
sujith80Commented:
Can you explain what is the basis of this requirement?
Is this for some estimation purpose or something else?
For estimation purposes use NUM_ROWS from all_tables.
If you are looking for accurate count, you may use a code like this one. (copy and paste the whole thing in sqlplus and run).
set serveroutput on size 200000
declare
 l_count number;
begin
 for rec in (select table_name from user_tables ) loop
  execute immediate 'select count(*) from '||rec.table_name into l_count;
  dbms_output.put_line('Table : '||rec.table_name||' , Count : '||to_char(l_count));
 end loop;
end;
/

Open in new window

0
 
interbusAuthor Commented:
Hello sujith80

My purpose to create reports for all tables include count of records in each.

your example is working with SQL Plus ???
0
 
sujith80Commented:
have you tried the example?
0
 
interbusAuthor Commented:
Yes I tried the example and the output is
10
11
12
13
and continue

only numbers for example the first time display 10 after I press enter that I get 11 and so on
please check your code again
0
 
sujith80Commented:
interbus:

"COPY EVERYTHING POSTED IN THE LAST POST AND RUN IT IN SQLPLUS"

When I asked you to copy everything I posted to sqlplus and try, it means you have to copy and paste everything there.
You have missed out to copy the slash ( "/" ) in the last line, that is the reason why you are getting those numbers 10, 11, 12 etc. Its not from the code.

See the below how it runs. (I have limited for 10 tables here by using a rownum < 11, you dont need that part).


SQL> set serveroutput on size 200000
SQL> declare
  2   l_count number;
  3  begin
  4   for rec in (select table_name from user_tables where rownum < 11) loop
  5    execute immediate 'select count(*) from '||rec.table_name into l_count;
  6    dbms_output.put_line('Table : '||rec.table_name||' , Count : '||to_char(l_count));
  7   end loop;
  8  end;
  9  /
Table : MUR_TMS_TRADE_NEW , Count : 915789
Table : MUR_CURRENT , Count : 130827
Table : MUR_DIRECT , Count : 130827
Table : MUR_CONV_ASP , Count : 4726
Table : MUR_DIRECT_ASP , Count : 4726
Table : STATTBL , Count : 0
Table : TAB_PARAMETERS , Count : 5
Table : TAB_TRACE , Count : 0
Table : ORCL_TRACE_PARAMS , Count : 4
Table : ORCL_TRACE , Count : 175
 
PL/SQL procedure successfully completed.
 
SQL> 

Open in new window

0
 
sujith80Commented:
To run a PL/SQL block from sqlplus, you need to enter a slash in the last line and then press "enter".

Otherwise sqlplus doesnt run the block it will keep on giving you line numbers.
0
 
interbusAuthor Commented:
If I want to display owner name (Schema name) with table name what I can do ?
0
 
sujith80Commented:
The script below will give you most of what you are looking for.

Note:
You may change all_tables to dba_tables to cover the entire database. But that will require the script to be run as a privileged account like SYSTEM or SYS
set serveroutput on size 200000
declare
 l_count number;
begin
 for rec in (select owner, table_name 
             from all_tables 
	     where owner not like '%SYS%'
	     and owner not in ('OUTLN', 'TOAD')
	     and rownum < 11
	     ) loop
  execute immediate 'select count(*) from '||rec.owner||'.'||rec.table_name into l_count;
  dbms_output.put_line('Owner : '||rec.owner||' - Table : '||rec.table_name||' , Count : '||to_char(l_count));
 end loop;
end;
/

Open in new window

0
 
interbusAuthor Commented:
There was a final question

How I print the output on file not with SQL Plus ?
0
 
interbusAuthor Commented:
Hello sujith80

I still have small question please help me and i give you 500 points

500 points it's for you
0
 
sujith80Commented:
set serveroutput on size 200000
spool c:\myoutput.txt
declare
 l_count number;
begin
 for rec in (select owner, table_name
             from all_tables
           where owner not like '%SYS%'
           and owner not in ('OUTLN', 'TOAD')
           and rownum < 11
           ) loop
  execute immediate 'select count(*) from '||rec.owner||'.'||rec.table_name into l_count;
  dbms_output.put_line('Owner : '||rec.owner||' - Table : '||rec.table_name||' , Count : '||to_char(l_count));
 end loop;
end;
/
spool off;
0
 
interbusAuthor Commented:
I'm sorry I have another question
When I see the result I don't found the view names

How I can also display tables with views or views it self

Thank you sujith80

You actual a wizard
0
 
gvsbnarayanaCommented:
from the code of sujith80, line 5
execute immediate 'select count(*) from '||rec.table_name into l_count;

change it to
execute immediate 'select count(*) ' || rec.table_name || ' from '||rec.table_name into l_count;
HTH
Regards,
Badri.
0
 
sujith80Commented:

set serveroutput on size 200000
spool c:\myoutput.txt
declare
 l_count number;
begin
 for rec in ( select owner, object_name, object_type
              from all_objects 
              where owner not like '%SYS%'
              and owner not in ('OUTLN', 'TOAD')
	      and object_type in ('TABLE', 'VIEW' )
              and rownum < 11
             ) loop
  execute immediate 'select count(*) from '||rec.owner||'.'||rec.object_name into l_count;
  dbms_output.put_line('Owner : '||rec.owner||' - '||rec.object_type||' : '||rec.object_name||' , Count : '||to_char(l_count));
 end loop;
end;
/
spool off;

Open in new window

0
 
sujith80Commented:
you may remove the line
>> and rownum < 11
0
 
interbusAuthor Commented:
Hello sujith80

There an error in lines 10

Line 10 the error is "OBJECT_TYPE": invalid identifier
0
 
sujith80Commented:
there is no error

by now you SHOULD be able to fix these minor things
SQL> declare
  2   l_count number;
  3  begin
  4   for rec in ( select owner, object_name, object_type
  5                from all_objects 
  6                where owner not like '%SYS%'
  7                and owner not in ('OUTLN', 'TOAD')
  8         and object_type in ('TABLE', 'VIEW' )
  9                and rownum < 11
 10               ) loop
 11    execute immediate 'select count(*) from '||rec.owner||'.'||rec.object_name into l_count;
 12    dbms_output.put_line('Owner : '||rec.owner||' - '||rec.object_type||' : '||rec.object_name||' , Count : '||to_char(l_count));
 13   end loop;
 14  end;
 15  /
Owner : XDB - TABLE : XDB$SIMPLE_TYPE , Count : 37
Owner : XDB - TABLE : XDB$COMPLEX_TYPE , Count : 86
Owner : XDB - TABLE : XDB$ALL_MODEL , Count : 0
Owner : XDB - TABLE : XDB$CHOICE_MODEL , Count : 12
Owner : XDB - TABLE : XDB$SEQUENCE_MODEL , Count : 82
Owner : XDB - TABLE : XDB$ELEMENT , Count : 394
Owner : XDB - TABLE : XDB$ATTRIBUTE , Count : 169
Owner : XDB - TABLE : XDB$ANYATTR , Count : 0
Owner : XDB - TABLE : XDB$ANY , Count : 7
Owner : XDB - TABLE : XDB$GROUP_DEF , Count : 0
 
PL/SQL procedure successfully completed.
 
SQL> 

Open in new window

0
 
interbusAuthor Commented:
Hello sujith80

When I remove rownum < 11

I get the list of tables but I get error in the last as

ORA-00933: SQL command not properly ended
ORA-06512: at line 12

If I want to add this statement
order by owner, object_name in the previous code will be working
0
 
interbusAuthor Commented:
This is code I used

set serveroutput on size 200000
--spool f:\Workspace\result.txt
declare
      l_count number;
begin
      for rec in ( select owner, object_name, object_type
      from all_objects
                   where owner not like '%SYS%'
                   and owner not in ('OUTLN', 'TOAD')
                   and object_type in ('TABLE', 'VIEW' )
--                   and rownum < 11
--      order by owner, object_name
                  ) loop
        execute immediate 'select count(*) from '||rec.owner||'.'||rec.object_name into l_count;
       dbms_output.put_line('Owner : '||rec.owner||' - '||rec.object_type||' : '||rec.object_name||' , Count : '||to_char(l_count));
       end loop;
  end;
   /
spool off
0
 
interbusAuthor Commented:

set serveroutput on size 200000
--spool f:\Workspace\result.txt
declare
	l_count number;
begin
	for rec in ( select owner, object_name, object_type
	from all_objects 
                   where owner not like '%SYS%'
                   and owner not in ('OUTLN', 'TOAD')
                   and object_type in ('TABLE', 'VIEW' )
--                   and rownum < 11
--	order by owner, object_name
                  ) loop
        execute immediate 'select count(*) from '||rec.owner||'.'||rec.object_name into l_count;
       dbms_output.put_line('Owner : '||rec.owner||' - '||rec.object_type||' : '||rec.object_name||' , Count : '||to_char(l_count));
       end loop;
  end;
   /
spool off

Open in new window

0
 
sujith80Commented:
You have objects in Oracle Recycle bin, that is the reason. Use this.
(this is the last attempt)
set serveroutput on size 200000
spool f:\Workspace\result.txt
 
declare
      l_count number;
begin
      for rec in ( select owner, object_name, object_type
      from all_objects 
                   where owner not like '%SYS%'
                   and owner not in ('OUTLN', 'TOAD')
                   and object_type in ('TABLE', 'VIEW' )
		   and object_name not like 'BIN$%'
--                   and rownum < 11
--      order by owner, object_name
                  ) loop
        execute immediate 'select count(*) from '||rec.owner||'.'||rec.object_name into l_count;
       dbms_output.put_line('Owner : '||rec.owner||' - '||rec.object_type||' : '||rec.object_name||' , Count : '||to_char(l_count));
       end loop;
end;
/
spool off

Open in new window

0
 
sujith80Commented:
Do you have any updates on this?
It is not fair to abandon a question after so many followups.
0
 
interbusAuthor Commented:
Ok your code is working and display to me a list of tables and views

but I get error I think this is case to stop display results as follow: -
declare
*
íæÌÏ ÎØà ÚäÏ ÓØÑ 1:    <Available error in line 1>
ORA-00942: table or view does not exist
ORA-06512: at line 11
0
 
interbusAuthor Commented:
This is the last code I use it
set serveroutput on size 200000
spool f:\Workspace\result.txt
 
declare
      l_count number;
begin
      for rec in ( select owner, object_name, object_type
      from all_objects
                   where owner not like '%SYS%'
                   and owner not in ('OUTLN', 'TOAD')
                   and object_type in ('TABLE', 'VIEW' )
                   and object_name not like 'BIN$%'
                  ) loop
        execute immediate 'select count(*) from '||rec.owner||'.'||rec.object_name into l_count;
       dbms_output.put_line('Owner : '||rec.owner||' - '||rec.object_type||' : '||rec.object_name||' , Count : '||to_char(l_count));
       end loop;
end;
/
spool off

Open in new window

0
 
interbusAuthor Commented:
What I need exatly there no error come to me with result.

Test the code with huge number of tables & views
0
 
interbusAuthor Commented:
Still your code not display all tables and views because happend error and stop the result
0
 
sujith80Commented:
>> Test the code with huge number of tables & views

Experts are working people too. Time doesnt permit to do that.

>> Still your code not display all tables and views because happend error and stop the result

You have to understand that the problem IS NOT with the code. The problem lies in your environment and database. These kind of issues can be de-bugged only if I have access to the db and I am sure it is a permission issue and can be resolved in a minute or two JUST by a careful look at the tables and permissions.

YOu may try the below.
set serveroutput on size 200000
spool f:\Workspace\result.txt
 
declare
      l_count number;
begin
      for rec in ( select owner, object_name, object_type
      from all_objects
                   where owner not like '%SYS%'
                   and owner not in ('OUTLN', 'TOAD')
                   and object_type in ('TABLE', 'VIEW' )
                   and object_name not like 'BIN$%'
                  ) loop
        begin
        execute immediate 'select count(*) from '||rec.owner||'.'||rec.object_name into l_count;
        dbms_output.put_line('Owner : '||rec.owner||' - '||rec.object_type||' : '||rec.object_name||' , Count : '||to_char(l_count));
	exception
	 when others then
	  null;
	end;
       end loop;
end;
/
spool off

Open in new window

0
 
interbusAuthor Commented:
Your code working fine but I still have a question

How I can order the result ?
0
 
sujith80Commented:
Add an ORDER BY
declare
      l_count number;
begin
      for rec in ( select owner, object_name, object_type
      from all_objects
                   where owner not like '%SYS%'
                   and owner not in ('OUTLN', 'TOAD')
                   and object_type in ('TABLE', 'VIEW' )
                   and object_name not like 'BIN$%'
                   order by owner, object_name
                  ) loop
        begin
        execute immediate 'select count(*) from '||rec.owner||'.'||rec.object_name into l_count;
        dbms_output.put_line('Owner : '||rec.owner||' - '||rec.object_type||' : '||rec.object_name||' , Count : '||to_char(l_count));
	exception
	 when others then
	  null;
	end;
       end loop;
end;
/

Open in new window

0
 
interbusAuthor Commented:
Thank you sujith80 I should give you 10000 points after so many replys

but the maximum I give you 500
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 20
  • 13
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now