Re: When to expect the next extent of table?

Hi

This may not be exactly what you are looking for, but it will give you some ideas and you may be able to modify the script to get what you want.

Another source for useful scripts is www.oramag.com. Check their archives which goes back 2 years.

Good luck !!!  

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

set dof off pause off

/************************************************************************


*                                                                       *

*    AUTHOR      : Oracleguru                                       *

*    FILE NAME   : analyze_tablespace_usage.sql                         *

*    LANGUAGE    : SQL/SQL*Plus/PL-SQL                                  *

*    INPUT PARAM : This script expects 1 parameter: Tablespace_name     *

*    OUTPUT PARAM: None.                                                *

*    INPUT FILES : None.                                                *

*    OUTPUT FILES: $HOME/rep/analyze_tablespace_names_TNAME.lst         *

*                     where TNAME = Tablespace_name entered on          *

*                                   command line to run this script     *

*                                                                       *

*    DESCRIPTION : This script generates tablespace usage for a         *

*                  TABLESPACE name entered on the command line.         *

*                                                                       *

************************************************************************/
 

set pause off termout off verify off wrap on serveroutput on size 1000000 set newpage 0 pagesize 58 linesize 80  

/*

   Generate tablespace analysis report only on Wednesdays.    Uncomment the following statements if this is what you want to do.  

whenever sqlerror exit  

select 1/0
  from dual
 where to_char(sysdate, 'day') not in ('wednesday'); */  

clear breaks  

column today        new_value today   noprint
column time         new_value time    noprint
 

/*

   Remove report file if exists, otherwise in case of problem, you may    receive a print out of an old file.
*/  

host rm -f $HOME/rep/analyze_tablespace_usage_&1..lst  

spool $HOME/rep/analyze_tablespace_usage_&1..lst  

set feedback off  

ttitle today center 'TABLE AND INDEX SPACE USAGE IN TABLESPACE &1' -

   right 'Page ' format 990 sql.pno skip 1 -    time -
   skip 1 -
   analyze_tablespace_usage.sql  

select to_char(sysdate, 'DD-MON-YYYY') today,

         to_char(sysdate, 'HH:MI:SS AM') time   from dual;  

set feedback on  

/*

   Compute total and unused disk space in tablespace. */  

DECLARE

   total_blocks                 number;
   total_bytes                  number;
   unused_blocks                number;
   unused_bytes                 number;/* Above High water mark */
   last_used_extent_file_id     number;
   last_used_extent_block_id    number;
   last_used_block              number;
   free_space                   number;/* Below High water mark */
   user_id                      all_tables.owner%type;
   object_name                  all_tables.table_name%type;
   object_type                  varchar2 ( 5);

--
   cursor   c1
       is
   select   owner, table_name, 'TABLE'
     from   all_tables
    where   tablespace_name =  upper('&1')
   union
   select   owner, index_name, 'INDEX'
     from   all_indexes
    where   tablespace_name =  upper('&1');
BEGIN
   open c1;
   LOOP
      fetch   c1
       into   user_id, object_name, object_type;
      exit when c1%notfound;
   dbms_space.unused_space
      ( user_id,
        object_name,
        object_type,
        total_blocks,
        total_bytes,
        unused_blocks,
        unused_bytes,
        last_used_extent_file_id,
        last_used_extent_block_id,
        last_used_block
      );
   dbms_space.free_blocks
      ( user_id,
        object_name,
        object_type,
        0,
        free_space);
--
   dbms_output.put_line
      ( object_type||' Name     = '||user_id||'.'||object_name );
   dbms_output.put_line
      ( '**********'                                             );
   dbms_output.put_line
      ( 'Total Bytes    = '||to_char(total_bytes, '999,999,990')||
        '     Free Bytes above Water Mark    = '||
        to_char(unused_bytes,'999,999,990') );
   dbms_output.put_line
      ( 'Bytes to W.Mark= '||to_char(total_bytes -
unused_bytes,'999,999,990')||
        '     Free Bytes below Water Mark    = '||
        to_char(free_space*8192, '999,999,990') );
   dbms_output.put_line
      (
'-------------------------------------------------------------------------
-------');
   END LOOP;
END;
..
/
 
column object           format a26              heading 'OBJECT'
column file_id          format 99990            heading 'FILE|ID '
column block_id         format 999990           heading 'BLOCK|ID '
column blocks           format 99990            heading 'BLOCKS'
column bytes            format 9,999,999,999    heading 'BYTES'
 
ttitle today center 'POSSIBLE FRAGMENTATION IN TABLESPACE &1' -
   right 'Page ' format 990 sql.pno skip 1 -
   time -
   skip 2 -
   analyze_tablespace_usage.sql skip 2
select   'freespace' owner, '       ' object,
         file_id, block_id, blocks, bytes         bytes
  from   sys.dba_free_space
 where   tablespace_name = upper('&1')
union
select   substr(owner, 1, 15),
         substr(segment_name, 1, 31),
         file_id, block_id, blocks, bytes         bytes
  from   sys.dba_extents
 where   tablespace_name = upper('&1')
order by 3, 4
/
 
break       on bytes on report
compute sum of bytes on report
 
column segment_name    format a26
column tablespace_name format a15
 
ttitle today center 'SPACE USAGE BY OBJECTS IN TABLESPACE &1' -
   right 'Page ' format 990 sql.pno skip 1 -
   time -
   skip 2 -
   analyze_tablespace_usage.sql skip 2
select   segment_name, tablespace_name, sum(bytes) bytes
  from   sys.dba_extents
 where   tablespace_name = '&1'
 group   by  segment_name, tablespace_name
/
 
set feedback off
 
column space                            heading '   SPACE'
column name                             heading '         INDEX NAME'
column btree_space  format 9,999,999,999        heading 'TOTAL SPACE'
column used_space   format 9,999,999,999        heading 'USED SPACE'
 
ttitle today center 'USED AND FREE SPACE USAGE FOR TABLESPACE &1' -
   right 'Page ' format 990 sql.pno skip 1 -
   time -
   skip 2 -
   analyze_tablespace_usage.sql skip 2
select   'Free Space ' space, sum(bytes )          bytes
  from   sys.dba_free_space
 where   tablespace_name = upper('&1')
union
select   'Used Space ' space, sum(bytes )          bytes
  from   sys.dba_extents
 where   tablespace_name = upper('&1')
/
 
prompt
prompt
prompt.               ********** END OF REPORT *********
 
spool off
 
exit



Ng TC <tcng_at_yas.com.sg> wrote in article
<6uevf0$t7a$1_at_newton2.pacific.net.sg>...
> 
> Hi,
> 
> How do I check if a table (or other objects) will be creating the next
> extent (which dynamice views, dba_ views??)?
> 
> How do I check how much of the current extent is being used?
> 
> Thanks in advance.
> 
> Regards,
> Tse Chong
> 
> (Please reply trhough email:  tcng_at_yas.com.sg )
> 
> 
> 
>