Solved

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

Posted on 2008-06-12
16
4,878 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 48

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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

679 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