RE: script

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01C35132.977F4B64

Content-Type: text/plain;

        charset="iso-8859-1"

For something a little different, play with the attached free.sql script.

-----Original Message-----

where can i find a script to find segments which can't extend due to low of space in tablespace .

-ak

------_=_NextPart_000_01C35132.977F4B64

Content-Type: application/octet-stream;

        name="free.sql"
Content-Disposition: attachment;

        filename="free.sql"

• Wrote by Stephen Lee set serveroutput on prompt Wait .... declare free_bytes number := 0.0; tspace_size number := 0.0; total_size number := 0.0; total_free number := 0.0; max_free number := 0.0; max_tab_next number := 0.0; max_ind_next number := 0.0; max_next number := 0.0; thingy varchar2(10); cursor c1 is select tablespace_name from dba_tablespaces; begin dbms_output.enable(1000000); dbms_output.put_line(rpad('TABLE SPACE',18,' ')||rpad('SIZE',16,' ')||rpad('TOTAL FREE',16,' ')||rpad('MAX FREE',16)||'MAX NEXT'); dbms_output.put_line(rpad('-',17,'-')||' '||rpad('-',15,'-')||' '||rpad('-',15,'-')||' '||rpad('-',15,'-')||' '||rpad('-',13,'-')); for tsname in c1 loop select sum(nvl(bytes,0)) into tspace_size from dba_data_files where tablespace_name = tsname.tablespace_name; select max(nvl(a.bytes,0)) into max_free from dba_free_space a, dba_tablespaces b where a.tablespace_name(+) = b.tablespace_name and b.tablespace_name = tsname.tablespace_name; select sum(nvl(a.bytes,0)) into free_bytes from dba_free_space a, dba_tablespaces b where a.tablespace_name(+) = b.tablespace_name and b.tablespace_name = tsname.tablespace_name; select max(nvl(next_extent,0)) into max_tab_next from dba_tables where tablespace_name = tsname.tablespace_name; select max(nvl(next_extent,0)) into max_ind_next from dba_indexes where tablespace_name = tsname.tablespace_name; if max_tab_next > max_ind_next then max_next := max_tab_next; else max_next := max_ind_next; end if; if max_tab_next is null and max_ind_next is not null then max_next := max_ind_next; end if; if max_ind_next is null and max_tab_next is not null then max_next := max_tab_next; end if; if max_next > max_free then thingy := '<'; else thingy := ''; end if; dbms_output.put_line(rpad(tsname.tablespace_name,17,' ')||rpad(to_char(tspace_size,'999,999,999,999'),16,' ')|| rpad(to_char(free_bytes,'999,999,999,999'),16,' ')||rpad(to_char(max_free,'999,999,999,999'),16,' ')|| rpad(to_char(max_next,'9,999,999,999'),14,' ')||thingy); total_size := total_size + tspace_size; total_free := total_free + free_bytes; end loop; dbms_output.put_line(rpad('-',17,'-')||' '||rpad('-',15,'-')||' '||rpad('-',15,'-')||' '||rpad('-',15,'-')||' '||rpad('-',13,'-')); dbms_output.put_line(rpad('TOTALS',17,' ')||rpad(to_char(total_size,'999,999,999,999'),16,' ')|| rpad(to_char(total_free,'999,999,999,999'),16,' '));