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

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

how do i find number of tables in the database?
0
gogoshar
Asked:
gogoshar
  • 6
  • 4
  • 2
  • +4
1 Solution
 
schwertnerCommented:
select count(*) from dba_tables;
0
 
sonicefuCommented:
select count(tname) from tab;
0
 
sonicefuCommented:
To find the number of tables in whole database


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

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sonicefuCommented:
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
 
chapmandewCommented:
for 2005:

select count(*) from sys.tables
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
gogosharAuthor Commented:
i am using:

select count(tname) from tab

what table is tab? does the count include any oracle system tables?
0
 
sonicefuCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
sonicefuCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
gogosharAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
sonicefuCommented:
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
 
hvalerCommented:

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
 
awking00Commented:
I don't think you need dba privileges, just "select any dictionary" privilege to access dba... views.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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