Oracle users' table and index on tablespace

set pagesize 60
set echo off
spool users_obj_tab.txt
column tablespace_name format a20
column owner format a20
column objects format a20
break on owner on tablespace_name
select substr(owner,1,20) owner,substr(tablespace_name,1,32) tablespace_name,count(*)||decode(count(*),1,' table',' tables') objects
from sys.dba_tables
group by substr(owner,1,20),substr(tablespace_name,1,32)
union
select substr(owner,1,20) owner,substr(tablespace_name,1,32) tablespace_name, count(*)||decode(count(*),1,' index',' indexes') objects
from sys.dba_indexes
group by substr(owner,1,20),substr(tablespace_name,1,32)
/
spool off




OWNER TABLESPACE_NAME OBJECTS
-------------------- -------------------- --------------------
ABC USERS 1 table
ANUJ USERS 2 indexes
4 tables
ANUJREP USERS 1 index
1 table
ANUJTEST ANUJTEST 1 table
APEX_030200 SYSAUX 1101 indexes
356 tables
4 tables
APEX_040000 TSAPEXU 1362 indexes
422 tables
4 tables
APPQOSSYS SYSAUX 2 tables
CTXSYS SYSAUX 34 tables
58 indexes
13 tables
DBSNMP SYSAUX 11 indexes
21 tables
2 indexes
4 tables
EXFSYS SYSAUX 20 tables
40 indexes
1 index
27 tables