rem rem Information about tablespace - Extents, Size, Freespace, Datafiles rem Version 8i rem Biju Thomas - Jun 2001 rem rem Provide the tablespace name along with the scriptname rem set serveroutput on feedback off verify off pages 0 spool /tmp/tsinfo8i.lst declare wtablespace varchar2 (30) := '&1'; /* Tablespaces */ cursor cts is select tablespace_name, contents, logging, extent_management, allocation_type, decode(plugged_in,'YES','PLUGGED','NATIVE') plugged_in, (ROUND(INITIAL_EXTENT/1024) ||' /' || ROUND(NEXT_EXTENT/1024) || ' /' || ROUND(MIN_EXTLEN/1024)) EXT_SIZE, (MIN_EXTENTS || ' /' || DECODE(MAX_EXTENTS,2147483645,'UNL',MAX_EXTENTS) || ' /' || PCT_INCREASE) EXT_LIMITS, STATUS from dba_tablespaces where tablespace_name like upper(wtablespace); /* Data files */ cursor cdf (t in varchar2) is select file_name, round(bytes/1048576,1) filesize, autoextensible, round(increment_by/1024,1) inc, round(maxbytes/1048576,1) max from dba_data_files where tablespace_name = upper(t) union all select file_name, round(bytes/1048576,1) filesize, autoextensible, round(increment_by/1024,1) inc, round(maxbytes/1048576,1) max from dba_temp_files where tablespace_name = upper(t); wcount number := 0; wdate varchar2 (25) := to_char(sysdate,'Mon DD, YYYY HH:MI AM'); w5space char(5) := '. '; wdum1 varchar2 (255); wdum2 varchar2 (255); wdum3 varchar2 (255); wdum4 varchar2 (255); wdum5 varchar2 (255); wdum6 varchar2 (255); wdum7 varchar2 (255); begin dbms_output.enable(100000); for rts in cts loop dbms_output.put_line('********** TABLESPACE INFORMATION ********** ' || wdate); dbms_output.put_line('*--------------------------------------------------------------------------*'); dbms_output.put_line('Tablespace Name : ' || rpad(rts.tablespace_name,26) || lpad(rts.status || ' - ' || rts.logging || ' - ' || rts.plugged_in, 30)); dbms_output.put_line('Management : ' || rts.extent_management || ' - ' || rts.contents || ' - '|| rts.allocation_type ||' EXTENT SIZES' ); dbms_output.put_line('Init/Next/Min(K): ' || rpad(rts.ext_size,20) || ' Min/Max/PctInc : ' || rts.ext_limits); wcount := wcount + 1; if rts.contents = 'TEMPORARY' then select round(sum(bytes)/1048576,1) into wdum1 from dba_temp_files where tablespace_name = rts.tablespace_name; else select round(sum(bytes)/1048576,1) into wdum1 from dba_data_files where tablespace_name = rts.tablespace_name; select round(sum(bytes)/1048576,1), count(tablespace_name), round(max(bytes)/1024,2) into wdum2, wdum3, wdum4 from dba_free_space where tablespace_name = rts.tablespace_name; end if; dbms_output.put_line('Total(M)/Free(M): ' || rpad(wdum1 || '/' || wdum2, 20) || ' FreeExts/MaxSize(K) : ' || wdum3 ||'/'||wdum4); dbms_output.put_line('*--------------------------------------------------------------------------*'); dbms_output.put_line('File Name Size(M) Ext Next(M) Max(M)'); dbms_output.put_line('*--------------------------------------------------------------------------*'); for rdf in cdf (rts.tablespace_name) loop if length(rdf.file_name) < 45 then dbms_output.put_line(rpad(rdf.file_name,47) || rpad(rdf.filesize,9) || rdf.autoextensible || lpad(rdf.inc,10) || lpad(rdf.max,8)); else dbms_output.put_line(rdf.file_name); dbms_output.put_line(rpad('. ',47) || rpad(rdf.filesize,9) || rdf.autoextensible || lpad(rdf.inc,10) || lpad(rdf.max,8)); end if; end loop; dbms_output.put_line(w5space); end loop; dbms_output.put_line('*--------------------------------------------------------------------------*'); if wcount =0 then dbms_output.put_line('******************************************************'); dbms_output.put_line('* *'); dbms_output.put_line('* Plese Verify Input Parameters... No Matches Found! *'); dbms_output.put_line('* *'); dbms_output.put_line('******************************************************'); end if; end; / set serveroutput off feedback on verify on pages 999 spool off prompt prompt Output saved at /tmp/tsinfo.lst TTITLE OFF