How To Find Schema Details in Oracle

• Query to check all the schemas of database with their rows.

SQL> select owner, count(1), object_type from dba_objects group by owner, object_type having owner in
     (select username from dba_users where default_tablespace = 'USERS') and object_type = 'TABLE';

OWNER                         ,  COUNT(1),             OBJECT_TYPE
------------------------------,----------,--------------------------------------------
HMIS                              ,      1893,                          TABLE
SCOTT                           ,         4,                             TABLE
ARCHIVEDB                 ,         1 ,                              TABLE
SEC                                 ,        28,                           TABLE
CMIS                              ,       410,                           TABLE
FMIS                               ,       579,                           TABLE
IDMS7                            ,      1883,                          TABLE

7 rows selected.

• How to check number of rows for all the tables in particular Schema

SQL> select count(*) from dba_segments where owner='IDMS7';

  COUNT(*)
----------
      3845

• To check how many segments a schema have and there sizes.

SQL> select segment_name,bytes/1024/1024 from dba_segments where owner='IDMS7';

• How to check size of Particular Schema 

SQL> select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='IDMS7' group by owner;

OWNER           SIZE in GB
--------------- ----------------------
IDMS7            5.6675415


Happy Learning ....