Solved

oracle:  how do i find number of tables in the database

Posted on 2008-06-12
16
4,876 Views
Last Modified: 2013-11-11
how do i find number of tables in the database?
0
Comment
Question by:gogoshar
  • 6
  • 4
  • 2
  • +4
16 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 21769625
select count(*) from dba_tables;
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 21769641
select count(tname) from tab;
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 21769668
To find the number of tables in whole database


select count(*) from dba_objects
where object_type='TABLE';

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 13

Expert Comment

by:sonicefu
ID: 21769964
As your question is in the SQL Server 2005 zone as well so you can use the following quries
select count(*) from sysobjects where xtype='U'
 
--or
 
select count(*) from INFORMATION_SCHEMA.TABLES 

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21770322
for 2005:

select count(*) from sys.tables
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 21771644
In Oracle you can only use the "dba..." objects if you are logged in as an Oracle user with DBA (superuser) privilege.  Other Oracle users can use the "all..." objects instead, but they won't show you all objects, just all of those you have permission to see.
0
 

Author Comment

by:gogoshar
ID: 21771958
i am using:

select count(tname) from tab

what table is tab? does the count include any oracle system tables?
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 21772051
no, it'll show you only the tables of current user (to which you are connected)

use the following statement in oracle to find the total number of tables in your database

select count(*) from dba_objects
where object_type='TABLE';

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 21772132
The Oracle object named "tab" is not a table.  It is a view that includes: tables, views, clusters and synonyms, but it returns a different number of objects depening on who you are logged in to Oracle as.

Yes, this will work in Oracle (if you have permission to use the "dba..." objects):
select count(*) from dba_objects
where object_type='TABLE';

But why?  This is simpler, and will give the same answer:
select count(*) from dba_tables;
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 21772293
Results from the following queries are different, second query skips some of the tables

--1)
select count(*) from dba_objects
where object_type='TABLE';

--2)

select count(*) from dba_tables;
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 21772404
Ok, I tried those two queries in an Oracle10.1 database and there are a few objects that show up in the query from dba_objects that don't show up in dba_tables.

But, maybe we ought to find out what the real business question or motive is here.  Does it really matter (or add value to know) how many "tables" are in the database?  This is different with each version of Oracle.

It seems like a more useful question might be: how many user-created (that is: non-default) tables are in the database?
0
 

Author Comment

by:gogoshar
ID: 21772472
when i try to run:

select count(*) from dba_objects
where object_type='TABLE'

i get an error message:

ORA-00942: table or view does not exist


0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 21772529
That means (as I indicated earlier) that you are not logged in to Oracle as a user with DBA (superuser) privileges.  You can use "all_objects" instead, but this will only show you the objects you are allowed to see, which is usually (far) less than all objects in the database, if you are not logged in with DBA privilege.  The DBA privilege in Oracle is like the "root" user in UNIX/Linux, or a "Domain Administrator" in a Windows-based network.  (I don't know the SQL Server equivalent.)
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 21774745
connect as a DBA user or ask your DBA to grant you the privileges.

If you know the password of system or sys user then connect to the database using any one of them and then execute above mentioned statement.

I think you installed oracle yourself then you must remember that what password you set at the beginning of oracle installation.
0
 
LVL 1

Accepted Solution

by:
hvaler earned 500 total points
ID: 21784268

SELECT COUNT(OBJECT_NAME) from user_objects where object_type = 'TABLE' -- Tables of current user
SELECT COUNT(TABLE_NAME) FROM USER_ALL_TABLES  -- Tables of current user
SELECT COUNT(TABLE_NAME) FROM ALL_ALL_TABLES;--All object and relational tables accessible to the user, With User : System All Tables DB
SELECT count(TABLE_NAME) from Dictionary; --????
 
select count(OBJECT_NAME) from dba_objects where object_type='TABLE' --All database Tables . Login: System

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 21919781
I don't think you need dba privileges, just "select any dictionary" privilege to access dba... views.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can Unique column have more than one Null? 8 53
Wrong number of values in the INTO list of a FETCH statement 16 58
Query to return total 6 17
pl/sql - query very slow 26 57
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now