-- #################################################################
--
-- Filename:    list_tabspace_73.sql
--
-- Version:     Certified for Oracle73
--
-- Purpose:     Show number of rows which can be inserted
--
-- Environment: This Script must be executed by the SYS owner.
--
-- #################################################################

ACCEPT schema_owner CHAR PROMPT 'Schema-Owner or Wildcard <%> : ' DEFAULT '%'
ACCEPT schema_table CHAR PROMPT 'Schema-Table or Wildcard <%> : ' DEFAULT '%'

set feed on
set verify off
set pagesize 10000
set linesize 200
set wrap off
set scan on
set termout on

break on owner skip 2
column datum      new_value   datum    noprint
column extents      format      999        heading EXT
column used_blocks   format      9999999  heading USED_BL
column allo_blocks   format      9999999  heading ALLO_BL
column next_extent   format       99999999 heading NEXT_EXT
column space_for   format      9999999    heading SPACE_FOR
 
select to_char(sysdate,'dd.mm.yyyy') datum,
      rpad(t.owner,10,'.') owner,
      rpad(t.table_name,24,'.') table_name,
      extents,
      t.blocks used_blocks,
      s.blocks allo_blocks,
         ((( value-57-4-(ini_trans*23))*empty_blocks)*
         (1-pct_free/100))/avg_row_len space_for,
      s.next_extent
 from  sys.dba_tables t, sys.dba_segments s, v$parameter
where  t.owner = s.owner
  and  t.table_name = s.segment_name
  and  t.owner like upper('&schema_owner')
  and  t.table_name like upper('&schema_table')
  and  name = 'db_block_size'
  and  avg_row_len > 0
  and  num_rows > 0
  and  t.cluster_name is null
  and  upper(t.owner) not in ('SYS')
order by  t.owner, t.table_name
/
exit;