[oracle] Count number of tables in Oracle

Like MySQL has "SHOW TABLES" , how do you count tables in Oracle DB. Little bit of research gave me this query:

select owner, count(*) from dba_tables

So like MySQL has a standard command, does Oracle have one?

Use this query which will give you the actual no of counts in respect to the table owners

SELECT COUNT(*),tablespace_name  FROM USER_TABLES group by tablespace_name;

If you'd like a list of owners, and the count of the number of tables per owner, try:

    SELECT distinct owner, count(table_name) FROM dba_tables GROUP BY owner;

Select count(*) FROM all_tables where owner='schema_name'

THIS QUERY WILL FIND ALL THE OBJECTS COUNTS IN A SPECIFIC SCHEMA

select owner, object_type, count(*) from dba_objects where owner='owner_name' group by owner, object_type order by 3 desc;

REM setting current_schema is required as the 2nd query depends on the current user referred in the session

ALTER SESSION SET CURRENT_SCHEMA=TABLE_OWNER;

SELECT table_name,
         TO_NUMBER (
            EXTRACTVALUE (
               xmltype (
                  DBMS_XMLGEN.getxml ('select count(*) c from ' || table_name)),
               '/ROWSET/ROW/C'))
            COUNT
    FROM dba_tables
   WHERE owner = 'TABLE_OWNER'
ORDER BY COUNT DESC;

These documents describe data dictionary views:

all_tables: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4473.htm#REFRN26286

user_tables: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091

dba_tables: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4155.htm#i1627762

You can run queries on these views to count what you need.

To add something more to @Anurag Thakre's answer:

Use this query which will give you the actual no of counts respect to the owners

SELECT COUNT(*),tablespace_name  FROM USER_TABLES group by tablespace_name;

Or by table owners:

SELECT COUNT(*), owner  FROM ALL_TABLES group by owner;

Tablespace itself does not identify an unique object owner. Multiple users can create objects in the same tablespace and a single user can create objects in various tablespaces. It is a common practice to separate tables and indexes into different tablespaces.

Please find below - its the simplest one I use :

select owner, count(*) from dba_tables group by owner;

If you want to know the number of tables that belong to a certain schema/user, you can also use SQL similar to this one:

SELECT Count(*) FROM DBA_TABLES where OWNER like 'PART_OF_NAME%';

select COUNT(*) from ALL_ALL_TABLES where OWNER='<Database-name>';

.....

try:

SELECT COUNT(*) FROM USER_TABLES;

Well i dont have oracle on my machine, i run mysql (OP comment)

at the time of writing, this site was great for testing on a variety of database types.