Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

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

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL SQL Developer 7 52
Oracle DBLINKS From 11g to 8i 3 47
Sybase and replication server 13 37
Database Design Dilemma 6 58
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

790 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