Home » SQL & PL/SQL » SQL & PL/SQL » Getting all the Records from the Database
Getting all the Records from the Database [message #241345] Tue, 29 May 2007 05:51 Go to next message
vinoth_mohan
Messages: 3
Registered: May 2007
Junior Member
Q. How will get total number of records in database in single select statement?

Can any one help me...

How to get it
Re: Getting all the Records from the Database [message #241357 is a reply to message #241345] Tue, 29 May 2007 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fetch all of them, this is the only way.

Regards
Michel
Re: Getting all the Records from the Database [message #241359 is a reply to message #241345] Tue, 29 May 2007 06:16 Go to previous messageGo to next message
kdipankar
Messages: 9
Registered: December 2006
Junior Member
Hi,

Can you please elaborate.

regards,
Dipankar.
Re: Getting all the Records from the Database [message #241361 is a reply to message #241345] Tue, 29 May 2007 06:21 Go to previous messageGo to next message
vinoth_mohan
Messages: 3
Registered: May 2007
Junior Member
Thats with a single select stmt i have to get all the records from the database


Re: Getting all the Records from the Database [message #241364 is a reply to message #241359] Tue, 29 May 2007 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To know how many rows returns a select you have to first retrieve all the rows.

Regards
Michel

Re: Getting all the Records from the Database [message #241371 is a reply to message #241364] Tue, 29 May 2007 06:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Before spending a good part of your day on this:
WHY?? What on earth is the use of knowing that number?
Re: Getting all the Records from the Database [message #241665 is a reply to message #241345] Wed, 30 May 2007 06:19 Go to previous messageGo to next message
punithavel
Messages: 21
Registered: May 2007
Location: Chennai
Junior Member

Me::: Laughing Laughing Laughing Laughing Laughing

Select * from *

you: Embarassed Embarassed Embarassed Embarassed Embarassed Embarassed
Re: Getting all the Records from the Database [message #241679 is a reply to message #241665] Wed, 30 May 2007 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Me: Question Question Question

Regards
Michel
Re: Getting all the Records from the Database [message #241701 is a reply to message #241679] Wed, 30 May 2007 08:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If you just want a number, you could analyze the schema and query user_tables Wink.

MHE
Re: Getting all the Records from the Database [message #244079 is a reply to message #241345] Mon, 11 June 2007 09:53 Go to previous messageGo to next message
pjansse
Messages: 1
Registered: June 2007
Junior Member
run these statements:

drop table temp_rec_count;

create table temp_rec_count(table_name varchar2(100), nr_records number);

set pagesize 0
set linesize 999
set heading off

spool tab_stmnt.sql

select 'insert into temp_rec_count select '''||table_name||''' , count(*) from '||table_name||';' from all_tables
/

spool off

@@tab_stmnt

commit;

select SUM(nr_records) from temp_rec_count
/

You may be able to do something similar in a single statement using a pl/sql table or whatever method you prefer, but I chucked it up into pieces and inserted results into a table to make it easier to understand.
*edit: spelling, not Tori either thankfully.

[Updated on: Mon, 11 June 2007 09:56]

Report message to a moderator

Re: Getting all the Records from the Database [message #244113 is a reply to message #244079] Mon, 11 June 2007 11:08 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
may be like this

SQL> select 'select '''||table_name||''', COUNT(*) FROM '||owner||'.'||table_name||' ;' from dba_tables
where owner in ('ABC','ORACLE','XYZ')
icon6.gif  Re: Getting all the Records from the Database [message #250886 is a reply to message #241665] Wed, 11 July 2007 13:55 Go to previous messageGo to next message
punithavel
Messages: 21
Registered: May 2007
Location: Chennai
Junior Member

punithavel wrote on Wed, 30 May 2007 16:49
Me::: Laughing Laughing Laughing Laughing Laughing

Select * from *

you: Embarassed Embarassed Embarassed Embarassed Embarassed Embarassed



Sorry for replied in such way...

But NOW I come with good resolution for this query

Write an Anonymous BLOCK

begin
DBMS_STATS.gather_schema_stats('SCHEMA'); -- Here write your schema name (JUST LIKE SCOTT, PUNITH(ITS ME))
end;

/

then try this query it will work

select sum (num_rows)from all_tables where owner='SCHEMA'; --again schema name

Ha ha ha... ITS SIMPLE//// Pls Reply Laughing


Laughing Laughing Laughing

[Updated on: Wed, 11 July 2007 13:58]

Report message to a moderator

Re: Getting all the Records from the Database [message #250899 is a reply to message #241701] Wed, 11 July 2007 14:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Maaher wrote on Wed, 30 May 2007 15:00
If you just want a number, you could analyze the schema and query user_tables Wink.

MHE

punithavel wrote on Wed, 11 July 2007 20:55
begin
DBMS_STATS.gather_schema_stats('SCHEMA'); -- Here write your schema name (JUST LIKE SCOTT, PUNITH(ITS ME))
end;
/
then try this query it will work

select sum (num_rows)from all_tables where owner='SCHEMA'; --again schema name

You spent only 10 weeks implementing Maaher's suggestion.
Good work Smile
Re: Getting all the Records from the Database [message #250912 is a reply to message #241345] Wed, 11 July 2007 15:30 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
vinoth_mohan wrote on Tue, 29 May 2007 06:51
Q. How will get total number of records in database in single select statement?



I haven't heard of a more ridiculous request than this one, but heck, here you go (as a DBA user):
select count(*) from user1.table1
UNION
select count(*) from user1.table2
UNION
...
select count(*) from user2,table1
UNION
...
select count(*) from user{n}.table{n}
/


Now all you need to do it type out all 80,000 or so tables in your database. I am sure this total number of records will be very useful. ./fa/917/0/
Re: Getting all the Records from the Database [message #250913 is a reply to message #241371] Wed, 11 July 2007 15:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Tue, 29 May 2007 13:47
Before spending a good part of your day on this:
WHY?? What on earth is the use of knowing that number?

That's another way of putting it, Joy!!

Razz
Re: Getting all the Records from the Database [message #250964 is a reply to message #250899] Thu, 12 July 2007 01:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
flyboy wrote on Wed, 11 July 2007 21:24
You spent only 10 weeks implementing Maaher's suggestion.
Good work Smile
Story of my life...Very Happy

MHE
Re: Getting all the Records from the Database [message #250970 is a reply to message #241345] Thu, 12 July 2007 01:49 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
vinoth_mohan wrote on Tue, 29 May 2007 05:51
Q. How will get total number of records in database in single select statement?

Can any one help me...

How to get it


Don't remember where I saw it, so unfortunately cannot provide a link, but you can do this using XML:

SQL> select sum(extractvalue(dbms_xmlgen.getXMLtype('select count(*) cnt from ' ||
  2                                                 t.TABLE_NAME),
  3                          '/ROWSET/ROW/CNT')) total_rows
  4    from user_tables t
  5  /

TOTAL_ROWS
----------
    834097

SQL> 
SQL> select table_name,
  2         extractvalue(dbms_xmlgen.getXMLtype('select count(*) cnt from ' ||
  3                                             t.TABLE_NAME),
  4                      '/ROWSET/ROW/CNT') rows_in_a_table
  5    from user_tables t
  6    where rownum<10
  7    and instr(table_name,'$')=0
  8  /

TABLE_NAME                     ROWS_IN_A_TABLE
------------------------------ --------------------------------------------------------------------------------
ACCT_HIST                      20
AUDITLOG_TST                   500000
BALANCE                        13
BASE                           5
CASH_FLOW                      8
COMPAR                         1
DAYS_OF_THE_WEEK               7
DEPOSITS                       10
DOLLAR_CONV_TBL                2

9 rows selected

SQL> 

Re: Getting all the Records from the Database [message #250999 is a reply to message #250970] Thu, 12 July 2007 02:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I've seen this solution somewhere on the OTN forums I think. What's the performance of such a query?

MHE
Re: Getting all the Records from the Database [message #251003 is a reply to message #250999] Thu, 12 July 2007 03:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
nice query, but who cares about the performance? If you have time to get such useless information from your database, who cares how long it takes! Wink
Re: Getting all the Records from the Database [message #251030 is a reply to message #251003] Thu, 12 July 2007 04:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Frank wrote on Thu, 12 July 2007 10:05
nice query, but who cares about the performance? If you have time to get such useless information from your database, who cares how long it takes! Wink

http://www.orafaq.com/forum/fa/451/0/ We have a winner! My guess was that Michel would reply first. Razz

You are absolutely right, Frank.

MHE
Re: Getting all the Records from the Database [message #251033 is a reply to message #251030] Thu, 12 July 2007 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have tried to be less harsh than usually. ./fa/940/0/

Regards
Michel

[Updated on: Thu, 12 July 2007 12:42]

Report message to a moderator

Re: Getting all the Records from the Database [message #251195 is a reply to message #250970] Thu, 12 July 2007 12:09 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
Volder wrote on Thu, 12 July 2007 01:49

Don't remember where I saw it, so unfortunately cannot provide a link, but you can do this using XML:



I've found where I read this example.
It was an example from Laurent Schneider.
Re: Getting all the Records from the Database [message #251270 is a reply to message #251195] Thu, 12 July 2007 15:12 Go to previous messageGo to next message
laurentschneider
Messages: 6
Registered: March 2007
Location: Switzerland
Junior Member
a NON-XML alternative could be

create or replace function count_rows(table_name varchar2) return number is
  c number;
begin
  execute immediate 'select count(*) from '||table_name into c;
  return c;
end;
/

select table_name, count_rows(table_name) from user_tables;


probably faster than xml, and also easier to understand.

My xml solution had a huge success, probably because it does not require to create any database object Cool

Re: Getting all the Records from the Database [message #251274 is a reply to message #251270] Thu, 12 July 2007 15:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As everybody post his solution, I realize I didn't post mine (that I already posted in forum 10 years ago):
Set heading   off
Set feedback  off
Set pagesize  0
Set termout   off
Set trimout   on
Set trimspool on
Set recsep    off
Set linesize  100
Column d noprint new_value date_
Column u noprint new_value user_
Spool tmp.lst
Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';',
to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
from user_tables
where tablespace_name is not null or partitioned='YES'
order by table_name
/
Spool off
Set termout on
Spool count_&user_._&date_
@tmp.lst
Spool off

You have the result in a spool file named from the user name and the current date.

Regards
Michel
Re: Getting all the Records from the Database [message #251281 is a reply to message #251274] Thu, 12 July 2007 15:33 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Excellent, excellent, excellent everyone. These are great solutions indeed, but you may have to hit your hand against your head when you realize that the OP asked for a single statement.
Voldermort's solution taken from Laurent's page satisfies the question. I'll tuck that away into my cool new things to do. Thanks Laurent.
Re: Getting all the Records from the Database [message #251324 is a reply to message #251281] Thu, 12 July 2007 23:25 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
Quote:
title=joy_division wrote on Thu, 12 July 2007 ...



Here's a link with one more execute immediate. Cool


BTW joy_division, what you mean by adding "mort" to my nick, huh?
Re: Getting all the Records from the Database [message #251398 is a reply to message #251324] Fri, 13 July 2007 08:03 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Oh, hahaha, I am sorry. I guess I had Harry Potter on my mind. It's just when I was typing "Volder," my fingers kept going and added the "mort." It's kind of like typing the word "ratio." I can never do that. For some reason, I always add an "n" to that word.
No offense intended.
Previous Topic: Inserting / Reading More than 32kb XML File from OS
Next Topic: Procedure Code [Ref Cursor] Help NOT Urgent
Goto Forum:
  


Current Time: Thu Dec 08 20:05:33 CST 2016

Total time taken to generate the page: 0.11485 seconds