Home » SQL & PL/SQL » SQL & PL/SQL » Count of tables (Windows XP)
Count of tables [message #433144] Mon, 30 November 2009 06:12 Go to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Hi,

I have executed the below query to get the count of tables in a schema .

select * from dba_objects where object_type = 'TABLE' and owner ='ABC';

But I am getting the below result

Total Count =count( Tables )+count(Materialised Views)

Actually I want to exclude the count of MVs and need only the count of tables? Can you please help me to get the solution?
Re: Count of tables [message #433147 is a reply to message #433144] Mon, 30 November 2009 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Depends on the Oracle version you didn't post.

Regards
Michel
Re: Count of tables [message #433151 is a reply to message #433147] Mon, 30 November 2009 06:25 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Oracle Verion - 10g

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Re: Count of tables [message #433154 is a reply to message #433151] Mon, 30 November 2009 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming simple case:
select nbt-nbm 
from (select count(*) nbt from dba_tables where...)
     (select count(*) nbm from dba_mviews where... and master_link is null)
/

Regards
Michel

[Updated on: Mon, 30 November 2009 06:29]

Report message to a moderator

Re: Count of tables [message #433155 is a reply to message #433154] Mon, 30 November 2009 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
or
select count(*)
from (select table_name from dba_tables where ...
      minus
      select container_name from dba_mviews
      where ...
        and master_link is null
     )
/

Regards
Michel
Re: Count of tables [message #433156 is a reply to message #433154] Mon, 30 November 2009 06:38 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Thanks . This query is working fine.

Similarly ,
How can we take the list of tables in a schema by excluding the materialized view LOG? Please help me
Re: Count of tables [message #433162 is a reply to message #433156] Mon, 30 November 2009 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the same way exclude dba_mviews_log.log_table

Regards
Michel
Re: Count of tables [message #433239 is a reply to message #433162] Mon, 30 November 2009 23:19 Go to previous message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Thanks.
Previous Topic: pl/sql object and type collection
Next Topic: List of tables
Goto Forum:
  


Current Time: Tue Dec 06 12:29:43 CST 2016

Total time taken to generate the page: 0.08455 seconds