Vot uzh ne dumal, chto opredelit pustyje TS v bolshoj base budet tak slozhno.

elementarnyj Statement:

select tablespace_name from dba_tablespaces
where tablespace_name not in
(select distinct tablespace_name
from dba_segments);

imejet Plan na 1000 Strok i dlitsya na BD s 17000 Segmentov bolshe 20 Min,
prichinyaja 16000000 consistent gets.

Peredelal na PL/SQL:

declare
v_tsname varchar2(30);
v_tssize integer;
v_tsfree integer;
v_tsfpct integer;
v_tsstot integer := 0;
v_tsftot integer := 0;
v_string varchar2(79);
v_empty integer;
cursor c1 is
select tablespace_name,round(sum(bytes)/1024/1024,2) tssize
from dba_data_files
group by tablespace_name;
cursor c2 is
select distinct name
from ts$ t,seg$ s
where t.ts#=s.ts#;
cursor c3 is
select round(sum(bytes)/1024/1024,2) tsfree
from dba_free_space
where tablespace_name = v_tsname;
begin
dbms_output.enable(50000);
v_string := rpad('Tablespace',30)||lpad('Size (Mb)',12)||lpad('Free (Mb)',12)||lpad('Pct free',10);
dbms_output.put_line(v_string);
for i1 in c1 loop
v_tsname:=i1.tablespace_name;
v_tssize := i1.tssize;
v_empty:=1;
for i2 in c2 loop
if i2.name=v_tsname then
v_empty:=0;
end if;
end loop;
if v_empty=1 then
v_tsstot := v_tsstot + v_tssize;
for i3 in c3 loop
v_tsfree := i3.tsfree;
v_tsftot := v_tsftot + v_tsfree;
v_tsfpct := round(v_tsfree * 100 / v_tssize,0);
v_string := rpad(v_tsname,30)||lpad(v_tssize,12)||lpad(v_tsfree,12)||lpad(v_tsfpct,10);
dbms_output.put_line(v_string);
end loop;
end if;
end loop;
v_string := rpad('-',30,'-')||' '||lpad('-',11,'-')||' '||lpad('-',11,'-')||' '||lpad('-',9,'-');
dbms_output.put_line(v_string);
v_tsfpct := round(v_tsftot * 100 / v_tsstot,0);
v_string := rpad('Total',30)||lpad(v_tsstot,12)||lpad(v_tsftot,12)||lpad(v_tsfpct,10);
dbms_output.put_line(v_string);
v_string := chr(10);
dbms_output.put_line(v_string);
end;
effekt totzhe.

Hotel chtoby bylo krasivo, a poluchilos kak vsegda!:((

Mozh pomozhet kto, lyudi dobryje!!!

k sozhaleniju i

select tablespace_name from dba_tablespaces t
2 where not exists (
3 select 1 from dba_segments s
4* where s.tablespace_name = t.tablespace_name )

dlitsya bolshe 15 minut.

Dolzhen byt gde-to drugoj vychod.

Variant konechmo luchshij, no resultaty zaprosov ne sovpadajut.

obschem rodil krokodila :-).
Kto zhelajet polzujtes. Spasibo za pomosch.

set pause off
set echo off
set termout on
prompt
prompt sel_ts_empty.sql
prompt
prompt Script lists the empty tablespaces.
prompt
define spoolfile = sel_ts_empty.lst
set pages 50000
set linesize 79
set heading on
set verify off
set feedback off
set serveroutput on
spool &&spoolfile
declare
v_tsname varchar2(30);
v_tssize integer;
v_tsfree integer;
v_tsfpct integer;
v_tsstot integer := 0;
v_tsftot integer := 0;
v_string varchar2(79);
cursor c1 is
select tablespace_name,round(sum(bytes)/1024/1024,2) tssize
from dba_data_files
where tablespace_name in (
select tablespace_name from dba_tablespaces
minus
select tablespace_name from dba_tablespaces t
where exists (
select 1 from dba_segments s
where s.tablespace_name = t.tablespace_name )
)
group by tablespace_name;
cursor c2 is
select round(sum(bytes)/1024/1024,2) tsfree
from dba_free_space
where tablespace_name = v_tsname;
begin
dbms_output.enable(50000);
v_string := rpad('Tablespace',30)||lpad('Size (Mb)',12)||lpad('Free (Mb)',12)||lpad('Pct

free',10);
dbms_output.put_line(v_string);
v_string := rpad('-',30,'-')||' '||lpad('-',11,'-')||' '||lpad('-',11,'-')||' '||lpad('-',9,'-');
dbms_output.put_line(v_string);
for i1 in c1 loop
v_tsname := i1.tablespace_name;
v_tssize := i1.tssize;
v_tsstot := v_tsstot + v_tssize;
for i2 in c2 loop
v_tsfree := i2.tsfree;
v_tsftot := v_tsftot + v_tsfree;
v_tsfpct := round(v_tsfree * 100 / v_tssize,0);
v_string := rpad(v_tsname,30)||lpad(v_tssize,12)||lpad(v_tsfree,12)||lpad(v_tsfpct,10);
dbms_output.put_line(v_string);
end loop;
end loop;
v_string := rpad('-',30,'-')||' '||lpad('-',11,'-')||' '||lpad('-',11,'-')||' '||lpad('-',9,'-');
dbms_output.put_line(v_string);
v_tsfpct := round(v_tsftot * 100 / v_tsstot,0);
v_string := rpad('Total',30)||lpad(v_tsstot,12)||lpad(v_tsftot,12)||lpad(v_tsfpct,10);
dbms_output.put_line(v_string);
v_string := chr(10);
dbms_output.put_line(v_string);
end;
/
spool off
clear breaks
clear computes
set pages 60
set verify on
set feedback on
prompt
prompt Output is spooled to &&spoolfile
prompt
undef spoolfile

ne sovpadajet kollichestbo empty tablespaces. Pochozhe chto seg$ mozhet soderzhat segmenty allokirovanyje v uzhe ne suschstvujuschich tablespasah.