-- #############################################################################################
--
-- %Purpose: Show 'Who owns what where' in the Database
--
-- #############################################################################################
--
-- Script to map tablespace names to database
-- owners, and database owners to tablespaces.
-- This will allow you to see who owns what where.
-- In the event of a tablespace loss, you would
-- then be able to quickly determine what users
-- and tablespaces will be affected. So you should
-- start this script from every export and save
-- the output files at the same place as the
-- export file.
--
-- #############################################################################################

set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set echo off;
set scan on;
set verify off;

ttitle  left    'Output generated by: ' sql.user -
skip -
skip -
left  'Who owns what where' -
skip -
left  'Oracle Version ' format a15 sql.release -
skip 2
set feed off
set pagesize 10000
break on owner on tablespace_name
column owner format A20 heading 'Owner'
column tablespace_name format A32 heading 'Tablespace'
column objects format A26 heading 'Objects'

spool who_owns_what_where.lst;

select  substr(owner,1,20) owner,
  substr(tablespace_name,1,32) tablespace_name,
  count(*) || ' 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(*) || ' indexes' objects
from sys.dba_indexes
group by
  substr(owner,1,20),
  substr(tablespace_name,1,32);
clear columns
clear breaks

column tablespace_name format A32 heading 'Tablespace'
column owner format A20 heading 'Owner'
column objects format A26 heading 'Objects'
break on tablespace_name on owner

select  substr(tablespace_name,1,32) tablespace_name,
  substr(owner,1,20) owner,
  count(*) || ' tables' objects
from sys.dba_tables
group by
  substr(tablespace_name,1,32),
  substr(owner,1,20)
union
select  substr(tablespace_name,1,32) tablespace_name,
  substr(owner,1,20) owner,
  count(*) || ' indexes' objects
  from sys.dba_indexes
group by
  substr(tablespace_name,1,32),
  substr(owner,1,20);
spool off;