/*
 * Title  : dp_reco.sql
 * Author : EXADIME LLC
 * Created: 05-Jan-2021
 * Updated: 05-Jan-2021
 * Release: 1.0.0
 * Purpose: The following script shows the tablespace & user space utilization and their mapping to help during data pump restore.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 125
set pagesize 0
set pause off
set long 80

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

column dummy	 noprint
column sno	 format 9999		heading "#"
column name	 format a30		heading "Tablespace Name"
column total	 format 99,999,999,999	heading "Total"
column alloc	 format 99,999,999,999	heading "Allocated"
column used	 format 99,999,999,999	heading "Used"
column free	 format 99,999,999,999	heading "Free"
column pct_used	 format 999.99		heading "%|Used"
column largest   format 99,999,999,999	heading "KBytes|Largest"

compute sum of total on report
compute sum of alloc on report
compute sum of free  on report
compute sum of used  on report

break on report

prompt
prompt 1.Tablespace Report
prompt

select
	row_number() over(order by 7)			        sno,
	nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN'))	name,
	total_kbytes						total,
	alloc_kbytes						alloc,
	alloc_kbytes-nvl(free_kbytes,0)				used,
	nvl(free_kbytes,0)+(total_kbytes-alloc_kbytes)		free,
	round(((total_kbytes-(nvl(free_kbytes,0)+
	(total_kbytes-alloc_kbytes)))/total_kbytes)*100,2) 	pct_used,
	nvl(largest_kbytes,0) 					largest
from	
(
select 
	sum(bytes)/1024 free_kbytes,
	max(bytes)/1024 largest_kbytes,
	tablespace_name
from  
	sys.dba_free_space
	group by tablespace_name 
) a,
(
select 
	sum(decode (autoextensible,'YES',maxbytes/1024,bytes/1024)) total_kbytes,
	sum(bytes)/1024 					    alloc_kbytes,
	tablespace_name
from 
	sys.dba_data_files
	group by tablespace_name
) b,
(
select
	tablespace_name,
	contents
from
	sys.dba_tablespaces
	) c
where 
	a.tablespace_name (+) = b.tablespace_name
	and
	b.tablespace_name=c.tablespace_name
order by
	7
/

prompt
prompt 2.User Report
prompt

col sno	    format 9999		heading "#"
col used    format 99,999,999	heading "MB|Used"
col owner   format a30          heading "User Name"

compute sum of used on report
break on report

select
	row_number() over(order by 3) sno,
	owner,
	sum(bytes)/1024/1024 used
from (
	select 
		owner,
		bytes
	from 
		dba_extents
	where
		owner not in (
			'SYS',
			'SYSTEM',
			'AUDSYS',
			'GSMADMIN_INTERNAL'
			,'GSMCATUSER'
			,'GSMUSER'
			,'OJVMSYS'
			,'SYSBACKUP'
			,'SYSKM'
			,'XS$NULL'
			,'SYSDG',
			'DBSNMP',
			'OUTLN',
			'MDSYS',
			'ORDSYS',
			'ORDPLUGINS',
			'CTXSYS',
			'DSSYS',
			'WKPROXY',
			'WKSYS',
			'WMSYS',
			'XDB',
			'ANONYMOUS',
			'ODM',
			'ODM_MTR',
			'OLAPSYS',
			'TRACESVR',
			'PERFSTAT',
			'REPADMIN',
			'EXFSYS',
			'DMSYS',
			'SYSMAN',
			'DIP',
			'TSMSYS',
			'DVF',
			'DVSYS',
			'WKUSER',
			'WK_TEST',
			'LBACSYS',
			'SI_INFORMTN_SCHEMA',
			'APPQOSSYS',
			'ORACLE_OCM',
			'RMAN',
			'GGS',
			'ORACLE',
			'SYS$UMF',
			'GGSYS',
			'DBSFWUSER',
			'ORADBA',
			'REMOTE_SCHEDULER_AGENT',
			'SYSRAC',
			'ORDDATA',
			'MDDATA',
			'SPATIAL_CSW_ADMIN_USR'
		)
	) 
group by 
		owner
order by
	3
/

col sno		format 9999	heading "#"
col tsd		format a42	heading "Tablespace Name"
col usn		format a30	heading "User Name"

break on tsd

prompt
prompt 3.Tablespce and User Mapping
prompt

select
	row_number() over(order by 1,2) sno,
	tsd,
	usn
from (
	select
		distinct
		rpad(tsn,30)			
		|| ' [' 
		||
		(
			select
				contents
			from
				dba_tablespaces
			where
				tablespace_name=tsn
		)				
		|| ']' 					tsd,
		usn
	from 
		(
			select
				tablespace_name		tsn,
				owner			usn
			from
				dba_extents
			union
			select
				default_tablespace	tsn,
				username		usn
			from
				dba_users
			union
			select
				temporary_tablespace	tsn,
				username		usn
			from
				dba_users
		)
	where 
		usn not in (
			'SYS',
			'SYSTEM',
			'AUDSYS',
			'GSMADMIN_INTERNAL'
			,'GSMCATUSER'
			,'GSMUSER'
			,'OJVMSYS'
			,'SYSBACKUP'
			,'SYSKM'
			,'XS$NULL'
			,'SYSDG',
			'DBSNMP',
			'OUTLN',
			'MDSYS',
			'ORDSYS',
			'ORDPLUGINS',
			'CTXSYS',
			'DSSYS',
			'WKPROXY',
			'WKSYS',
			'WMSYS',
			'XDB',
			'ANONYMOUS',
			'ODM',
			'ODM_MTR',
			'OLAPSYS',
			'TRACESVR',
			'PERFSTAT',
			'REPADMIN',
			'EXFSYS',
			'DMSYS',
			'SYSMAN',
			'DIP',
			'TSMSYS',
			'DVF',
			'DVSYS',
			'WKUSER',
			'WK_TEST',
			'LBACSYS',
			'SI_INFORMTN_SCHEMA',
			'APPQOSSYS',
			'ORACLE_OCM',
			'RMAN',
			'GGS',
			'ORACLE',
			'SYS$UMF',
			'GGSYS',
			'DBSFWUSER',
			'ORADBA',
			'REMOTE_SCHEDULER_AGENT',
			'SYSRAC',
			'ORDDATA',
			'MDDATA',
			'SPATIAL_CSW_ADMIN_USR'
		)
) order by
	1,2
/

clear columns
clear computes

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80
set long 80

set termout on
set serverout off
set feedback on
set echo off
