Count number of tables in Oracle

Question

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

User · Answer

select COUNT    from ALL ALL TABLES where OWNER   lt Database-name gt

User · Answer

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

User · Answer

Yeah sure your query will work just modify it a little  Look here for refrence   http   download oracle com docs cd B19306 01 server 102 b14237 statviews 2105 htm i1592091  Run this   SELECT TABLE NAME FROM DBA TABLES    to get list of tables   and  Run this   SELECT Count    FROM DBA TABLES    to get the count of tables

User · Answer

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

User · Answer

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

User · Answer

Select count    FROM all tables where owner  schema name

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Please find below - its the simplest one I use    select owner  count    from dba tables group by owner

User · Answer

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

[oracle] Count number of tables in Oracle

Examples related to oracle

Examples related to select

Examples related to count