Solved

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

Posted on 2008-06-12
16
4,872 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
Comment Utility
select count(*) from dba_tables;
0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility
select count(tname) from tab;
0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility
To find the number of tables in whole database


select count(*) from dba_objects

where object_type='TABLE';

Open in new window

0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility
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
Comment Utility
for 2005:

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

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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
Comment Utility

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 31

Expert Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

763 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

8 Experts available now in Live!

Get 1:1 Help Now