REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_tabsp_dif.sql
REM
REM <SQLDIR_GRP>TABSP MOST</SQLDIR_GRP>
REM 
REM Author:
REM	Vitaliy Mogilevskiy
REM 	VMOGILEV
REM	vit100gain@earthlink.net
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	---------------------------------------------------------
REM	Compares tablespace differences between current database
REM	and any other database.  Reports segments with different
REM	number of extents from these tablespaces.
REM	---------------------------------------------------------
REM	(Creates DB LINK and temp table)
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tabsp_dif.sql
REM 
REM Example:
REM	s_tabsp_dif.sql
REM
REM
REM History:
REM	09-17-2001	VMOGILEV	Created
REM
REM

clear col
ttit off
btit off

@x_db_name.sql


prompt +-----------------------------------+
prompt 1 = Remote Database TNS
prompt 2 = Remote Database SYSTEM's password
prompt +-----------------------------------+

set pages 60
set lines 132
set feedback on

set verify off
col tablespace_name format a15 heading "TS Name"
col Kbytes_dif                 heading "R_Kb - L_Kb"
col Kbytes_l        format a35 heading "Local TS|(dbf) Kbytes"
col Kbytes_r        format a35 heading "Remote TS|(dbf) Kbytes"

drop database link tabsp_dif_&&1 ;

create database link tabsp_dif_&&1
connect to system identified by &&2
using '&&1';

drop table dbatoolz_tabsp_dif_&&1 ;

create table dbatoolz_tabsp_dif_&&1 as
select /*+ ORDERED */
       l_ts.tablespace_name
,      r_ts.Kbytes - l_ts.Kbytes                          Kbytes_dif
,      '('||l_ts.dbf_cnt||') '||l_ts.Kbytes               Kbytes_l
,      '('||r_ts.dbf_cnt||') '||r_ts.Kbytes               Kbytes_r
from   (select count(*) dbf_cnt
        ,      SUM(bytes)/1024 Kbytes
        ,      tablespace_name
        from   dba_data_Files@tabsp_dif_&&1
        group by
           tablespace_name)           r_ts
,      (select count(*) dbf_cnt
        ,      SUM(bytes)/1024 Kbytes
        ,      tablespace_name
        from   dba_data_Files
        group by
           tablespace_name)           l_ts
where  l_ts.tablespace_name = r_ts.tablespace_name
and    (l_ts.Kbytes - r_ts.Kbytes) <> 0;

spool s_tabsp_dif.&db_name..&&1..&tstamp..log


select * from dbatoolz_tabsp_dif_&&1 ;


col ts           format a15  heading "TS name"
col seg          format a35  heading "OWNER.SEGMENT_NAME"
col ext_dif   format 999999  heading "R EXT# - |L EXT#"
col Kbytes_l  format 9999999 heading "Local Kb"
col Kbytes_r  format 9999999 heading "Remote Kb"
col extents_l format 999999  heading "L EXT#"
col extents_r format 999999  heading "R EXT#"
col segment_type noprint new_value s_type

break on segment_type skip page
ttitle  -
         "Segment Type: " s_type " **************" -


select /*+ ORDERED */
       DISTINCT
       l_segs.tablespace_name ts
,      l_segs.owner||'.'||l_segs.segment_name seg
,      l_segs.segment_type
,      r_segs.extents - l_segs.extents ext_dif
,      l_segs.bytes/1024 Kbytes_l
,      r_segs.bytes/1024 Kbytes_r
,      l_segs.extents extents_l
,      r_segs.extents extents_r
from   dba_segments@tabsp_dif_&&1 r_segs
,      dba_segments               l_segs
where  l_segs.owner = r_segs.owner
and    l_segs.segment_type = r_segs.segment_type
and    l_segs.segment_name = r_segs.segment_name
and    (l_segs.extents < r_segs.extents OR
        l_segs.bytes < r_segs.bytes)
and    l_segs.tablespace_name IN (select tablespace_name
                                  from   dbatoolz_tabsp_dif_&&1 )
order by
   3
,  4
,  1
,  2;



spool off

set verify on
clear col
--undefine 1
--undefine 2