set echo off
set termout on

--
--  File     : tabinfo8.sql
--  Created  : Hans Wesnitzer / MuniQSoft GmbH
--
--  Purpose  : Display some table information including indexes and 
--             constraints. Mainly used for tuning purposes.
--
--  Requires : Oracle RDBMS 8 
--
--  Usage    : @tabinfo <owner> <table_name>
--             Must be called as user SYSTEM from sqlplus.
--  
--
--  Changes  :  Version  Who   Date        Note
--              --------------------------------------------------------------
--              01.00    wes   31.10.2000  initial creation
--

set feedback off
set pagesize 500
set verify off
clear breaks
set serveroutput on size 20000

declare

    owner   varchar2(40);
    tablename  varchar2(40);
    str        varchar2(255);
    
    cursor c1 ( p_owner varchar2, p_tablename varchar2 ) 
    is 
     select t.*, 
            s.bytes as s_bytes, 
            s.blocks as s_blocks, 
            s.extents as s_extents
     from dba_tables t, dba_segments s
     where t.owner like p_owner 
       and t.table_name like p_tablename
       and s.segment_name = t.table_name
       and s.owner = t.owner;
       
    cursor c2 ( p_owner varchar2, p_table varchar2 ) 
    is 
      select *
      from dba_tab_columns
      where owner = p_owner
        and table_name = p_table
      order by column_name;
      
    cursor c3 ( p_owner varchar2, p_table varchar2 ) 
    is 
      select constraint_name, 
             decode( constraint_type, 
                     'P', 'Primary key',
                     'R', 'Foreign key',
                     'U', 'Unique' ) cons_type,
             r_owner, 
             r_constraint_name, 
             delete_rule, 
             status,
             deferrable, 
             deferred, 
             validated
      from dba_constraints 
      where owner = p_owner
        and table_name = p_table
        and constraint_type in ( 'P', 'R', 'P' ) 
        and status = 'ENABLED';

    cursor c4 ( p_owner varchar2, p_table varchar2, p_constraint varchar2 ) 
    is 
      select column_name
      from dba_cons_columns
      where owner = p_owner
        and table_name = p_table
        and constraint_name = p_constraint;

    cursor c5 ( p_owner varchar2, p_table varchar2 ) 
    is
     select i.*, 
            s.bytes as s_bytes, 
            s.blocks as s_blocks, 
            s.extents as s_extents
     from dba_indexes i, dba_segments s
     where i.table_owner = p_owner
       and i.table_name = p_table
       and s.segment_name = i.table_name
       and s.owner = i.table_owner;

    cursor c6 ( p_owner varchar2, p_table varchar2, p_index varchar2 ) 
    is
      select column_name
      from dba_ind_columns
      where index_owner = p_owner
        and table_name = p_table
        and index_name = p_index;

    cursor c_tab_part ( p_tab_owner varchar2, p_table varchar2 ) 
    is 
      select *
      from dba_tab_partitions
      where table_owner = p_tab_owner
        and table_name = p_table
      order by partition_position;

--
-- Lokale Prozedur OUTPUT_TABLE_PART gibt die Tabellen-Partitionen aus
--

    procedure output_table_part ( p_owner varchar2, p_table varchar2 ) is
      str varchar2(160);
      val varchar2(80);
      cursor cur0 is
        select column_name 
        from dba_part_key_columns 
        where owner = p_owner 
          and name = p_table
          and object_type like 'TABLE%'
        order by column_position;      
    begin

      str := 'Partition key columns: ';
      for rec in cur0 loop
        str := str || rec.column_name || ' ';
      end loop;
      dbms_output.put_line(str);

      for rec in c_tab_part(p_owner, p_table) loop
        dbms_output.put_line('
                         ');

        val := rec.high_value;
        str := '>Partition:' || lpad(rec.partition_name, 15) ||
               '  High value: ' || val;
        dbms_output.put_line(str);
        str := '>>Logging:' || lpad(rec.logging, 16) ||
               '  Buffer pool:' || lpad(rec.buffer_pool,14) ||
               '  TS:' || rec.tablespace_name;
        dbms_output.put_line(str);

        str := '>>Pct. Free: ' || lpad(to_char(rec.pct_free),12) || '%' || 
               '  Pct. Used:' || lpad(rec.pct_used,16);
	dbms_output.put_line(str);

	str := '>>Initial:  ' || 
               lpad(to_char(round(rec.initial_extent/1024,1)),8)	|| 
               ' KByte  Next:   ' || 
               lpad(to_char(round(rec.next_extent/1024,1)),12) || ' KByte' ||
               '  Pct. Increase: ' || to_char(rec.pct_increase) || '%';
	dbms_output.put_line(str);
         
        str := '>>Min. Extents: ' || lpad(to_char(rec.min_extent), 10) 
               || '  Max. Extents: ' || lpad(to_char(rec.max_extent), 12);
        dbms_output.put_line(str);

        str := '>>Freelists: ' || lpad(to_char(rec.freelists),13) ||
	       '  Freelist Grp.: ' || lpad(to_char(rec.freelist_groups),11);
        dbms_output.put_line(str);

	if ( rec.last_analyzed is null ) then
            str := '>>Last Analyzed: ' || lpad('n/a', 11);
	else
            str := '>>Last Analyzed: ' || 
		   to_char(rec.last_analyzed, 'dd-mon-yy hh24:mi:ss');
	end if;
	dbms_output.put_line(str);

	if ( rec.num_rows is not null ) then
            str := '>>Number of rows:'  ||  lpad(to_char(rec.num_rows), 9)
                    || '  Blocks: '  ||  lpad(to_char(rec.blocks), 18)  ||
	            '  Empty Blocks: '  ||  to_char(rec.empty_blocks);
            dbms_output.put_line(str);
        end if;

      end loop;
	
    end  output_table_part;
	
--
-- Lokale Prozedur OUTPUT_TABLE gibt die Tabelleneinstellungen aus
--
    procedure output_table( p_rec_table c1%rowtype ) is
	organization  varchar2(20);
    	str	      varchar2(160);
    begin
	str := 'Table:  ' || lpad(p_rec_table.table_name, 18) || '  Owner: ' ||
		lpad(p_rec_table.owner,19) || '  TS: ' ||  p_rec_table.tablespace_name;
	dbms_output.put_line( str );

	organization := nvl(p_rec_table.iot_type, 'HEAP');

	str := 'Organization: ' || lpad(organization, 12) || 
			'  Partitioned: ' || lpad(p_rec_table.partitioned, 13)  ||
		'  Logging: ' || p_rec_table.logging;
       	dbms_output.put_line(str);

	str := 'Cache: ' || lpad(p_rec_table.cache, 19) || '  Buffer Pool:' ||
			lpad(nvl(p_rec_table.buffer_pool,'Default'), 14) || 
				'  Table Lock: ' || p_rec_table.table_lock;
	dbms_output.put_line(str);

	if ( organization = 'HEAP'   and  p_rec_table.partitioned = 'NO' ) then
		str := 'Initial:  ' || 
			lpad(to_char(round(p_rec_table.initial_extent/1024,1)),10)
			|| ' KByte  Next:   ' || 
			lpad(to_char(round(p_rec_table.next_extent/1024,1)),12) || 
			' KByte' || '  Pct. Increase: ' || 
			to_char(p_rec_table.pct_increase) || '%';
			dbms_output.put_line(str);
	end if;

        if ( p_rec_table.partitioned = 'NO' ) then
	    str := 'Pct. Free: ' || lpad(to_char(p_rec_table.pct_free),14) || '%' ||
		'  Pct. Used: ' || lpad(to_char(p_rec_table.pct_used),14) 
		|| '%';

	    dbms_output.put_line(str);
        end if;

	if ( organization = 'HEAP'  and  p_rec_table.partitioned = 'NO' ) then
            str := 'Min. Extents: ' || lpad(to_char(p_rec_table.min_extents), 12) 
                      || '  Max. Extents: ' || 
			lpad(to_char(p_rec_table.max_extents), 12);
            dbms_output.put_line(str);
	end if;

	if ( organization = 'HEAP'  and  p_rec_table.partitioned = 'NO' ) then
            str := 'Freelists: '       || lpad(to_char(p_rec_table.freelists),15) ||
                   '  Freelist Grp.: ' || lpad(to_char(p_rec_table.freelist_groups),11);
            dbms_output.put_line(str);
            if ( p_rec_table.degree <> '' ) then
	        str := 'Parallel Degree: ' || lpad(ltrim(p_rec_table.degree),7) ||
			'   Parallel Instances : ' || 
					lpad(ltrim(p_rec_table.instances),3);
	    		dbms_output.put_line(str);
	    end if;
	end if;

	if ( organization = 'HEAP'  and  p_rec_table.partitioned = 'NO' ) then
            str := 'Extents:   '       || lpad(to_char(p_rec_table.s_extents),15) ||
                   '  Bytes:         ' || lpad(to_char(p_rec_table.s_bytes),11)   ||
                   '  Blocks: ' || to_char(p_rec_table.s_blocks);
            dbms_output.put_line(str);
	end if;

        if ( p_rec_table.partitioned = 'YES' ) then
            output_table_part(p_rec_table.owner, p_rec_table.table_name);
	    dbms_output.put_line('
                         ');
        end if;

	if ( p_rec_table.last_analyzed is null ) then
            str := 'Last Analyzed: ' || lpad('n/a', 11);
	else
            str := 'Last Analyzed: ' || 
		   to_char(p_rec_table.last_analyzed, 'dd-mon-yy hh24:mi:ss');
	end if;
	dbms_output.put_line(str);

	if ( p_rec_table.cluster_name is not null ) then
 	    dbms_output.put_line( 'Cluster: ' ||  p_rec_table.cluster_name );
	end if;

	if ( p_rec_table.num_rows is not null ) then
            str := 'Number of rows: '  ||  lpad(to_char(p_rec_table.num_rows), 10)
                    || '  Blocks: '  ||  lpad(to_char(p_rec_table.blocks), 18)  ||
	                  '  Empty Blocks: '  ||  
                                   lpad(to_char(p_rec_table.empty_blocks), 8);
            dbms_output.put_line(str);
            str := 'Chained Rows: ' || lpad(to_char(p_rec_table.chain_cnt), 12)  ||
	           '  Avg. Row Length: '  ||  
                   lpad(to_char(p_rec_table.avg_row_len), 9)  ||  '  Avg. Space: ' 
                          ||  lpad(to_char(p_rec_table.avg_space), 10);
            dbms_output.put_line(str);
	end if;

    end output_table;

--
-- Lokale Prozedur OUTPUT_INDEX gibt die Index-Einstellungen aus
--
    procedure output_index( p_rec_index c5%rowtype ) is
    	str	      varchar2(160);
    begin
        str := 'Type: '  ||  lpad(p_rec_index.index_type, 20)  ||
               '  Tablespace: ' || lpad(p_rec_index.tablespace_name, 14);
        dbms_output.put_line(str);

        str := 'Partitioned:' || lpad(p_rec_index.partitioned, 14)  ||
               '  Buffer pool:' || lpad(p_rec_index.buffer_pool, 14);
        dbms_output.put_line(str);

	str := 'Status: '  ||  lpad(p_rec_index.status, 18);
        if ( p_rec_index.partitioned = 'NO' ) then
            str := str || '  Pct. Free: ' || lpad(to_char(p_rec_index.pct_free),14) 
                   || '%'  || '  Logging: ' || lpad(p_rec_index.logging, 9);
        end if;
	dbms_output.put_line(str);

        if ( p_rec_index.partitioned = 'NO' ) then
	    str := 'Initial:  ' || 
               lpad(to_char(round(p_rec_index.initial_extent/1024,1)),10)	|| 
               ' KByte  Next:   ' || 
               lpad(to_char(round(p_rec_index.next_extent/1024,1)),12) || ' KByte' ||
               '  Pct. Increase: ' || to_char(p_rec_index.pct_increase) || '%';
	    dbms_output.put_line(str);
         
            str := 'Min. Extents: ' || lpad(to_char(p_rec_index.min_extents), 12) 
               || '  Max. Extents: ' || lpad(to_char(p_rec_index.max_extents), 12);
            dbms_output.put_line(str);

            str := 'Freelists: ' || lpad(to_char(p_rec_index.freelists),15) ||
	       '  Freelist Grp.: ' || lpad(to_char(p_rec_index.freelist_groups),11);
            dbms_output.put_line(str);

            if ( p_rec_index.degree <> '' ) then
	        str := 'Parallel Degree: ' || lpad(ltrim(p_rec_index.degree),7) ||
			'   Parallel Instances : ' || 
					lpad(ltrim(p_rec_index.instances),3);
	        dbms_output.put_line(str);
	    end if;
        end if;        

        if ( p_rec_index.partitioned = 'NO' ) then
            str := 'Extents:   '       || lpad(to_char(p_rec_index.s_extents),15) ||
                   '  Bytes:         ' || lpad(to_char(p_rec_index.s_bytes),11)   ||
                   '  Blocks: ' || to_char(p_rec_index.s_blocks);
            dbms_output.put_line(str);
        end if;

	if ( p_rec_index.last_analyzed is null ) then
            str := 'Last Analyzed: ' || lpad('n/a', 11);
	else
            str := 'Last Analyzed: ' || 
		   to_char(p_rec_index.last_analyzed, 'dd-mon-yy hh24:mi:ss');
	end if;
	dbms_output.put_line(str);

	if ( p_rec_index.num_rows is not null ) then
            str := 'Number of rows: '  ||  lpad(to_char(p_rec_index.num_rows), 11)
                    || '  Distinct keys: ' || 
                    lpad(to_char(p_rec_index.distinct_keys), 10) || '  B-Level: ' || 
                    lpad(to_char(p_rec_index.blevel), 9);
            dbms_output.put_line(str);

            str := 'Leaf blocks: ' || lpad(to_char(p_rec_index.leaf_blocks), 14) ||
                   '  Clustering factor: ' || 
                   lpad(to_char(p_rec_index.clustering_factor),6);
            dbms_output.put_line(str);

            str := 'Avg. leaf blocks/key:' ||
                   lpad(to_char(p_rec_index.avg_leaf_blocks_per_key), 6) ||
                   '  Avg. data blocks/key:' ||
                   lpad(to_char(p_rec_index.avg_data_blocks_per_key), 4);
            dbms_output.put_line(str);

	end if;

	dbms_output.put_line('
                         ');
    end output_index;

--
-- Lokale Prozedur OUTPUT_CONSTRAINT gibt die Constraint-Einstellungen aus
--
    procedure output_constraint( p_rec_constraint c3%rowtype ) is
    	str  varchar2(160);
        con_type varchar2(30);
        table_name varchar2(30);

    begin
        if ( p_rec_constraint.cons_type = 'Foreign key' ) then
            str := 'Ref. owner: ' || lpad(p_rec_constraint.r_owner, 14) ||
                   '  Ref. constraint:' || lpad(p_rec_constraint.r_constraint_name, 10) ||
                   ' --->>>';
            dbms_output.put_line(str);

            select  decode(
                      constraint_type, 'P', 'Primary key', 'U', 'Unique'),
                    table_name
            into  con_type, table_name
            from dba_constraints 
            where owner = p_rec_constraint.r_owner and constraint_name = p_rec_constraint.r_constraint_name;

            str := '--->>> Table: ' || table_name || '  Constrain type: ' ||
                   con_type;
            dbms_output.put_line(str);

            str := '--->>> Columns: ';
	    for rec_c4 in c4 ( p_rec_constraint.r_owner, table_name, p_rec_constraint.r_constraint_name ) loop	  
		str := str || rec_c4.column_name || ' ';
	    end loop;
	    dbms_output.put_line(str);
        end if;

	dbms_output.put_line('
                         ');
    end output_constraint;

begin

    owner := upper('&1'); 
    tablename := upper('&2');
    
    for rec_c1 in c1(owner, tablename) loop
    	dbms_output.put_line(' 
                               ');
	dbms_output.put_line('--------------------------------------------------------------------------------' );
    	dbms_output.new_line;

	output_table( rec_c1 );

	dbms_output.put_line('
                         ');
        str := '+---------------------------+------------+---------+----+------------+---------+';
	dbms_output.put_line(str);
	str := '|       Column              |    Type    | Length  |Null|  Distinct  |Last An. |';
	dbms_output.put_line(str);
	str := '+---------------------------+------------+---------+----+------------+---------+';
	dbms_output.put_line(str);

	for rec_c2 in c2(rec_c1.owner, rec_c1.table_name) loop
	    str := '|' || rpad(rec_c2.column_name,27) || '|' || 
			rpad(rec_c2.data_type,12) || '|' || 
			rpad(to_char(rec_c2.data_length),9) || '| ' ||
			rec_c2.nullable || '  |' ||
			rpad(nvl(to_char(rec_c2.num_distinct), ' ****'),12) 
			|| '|' || nvl(to_char(rec_c2.last_analyzed, 
				'dd-mon-yy'), '  ****   ') || '|';
	    dbms_output.put_line( str );
	end loop;

	str := '+---------------------------+------------+---------+----+------------+---------+';
	dbms_output.put_line(str);
	dbms_output.put_line('
                         ');

	for rec_c3 in c3(rec_c1.owner, rec_c1.table_name) loop
	    str := 'Constraint ' || rec_c3.constraint_name || ' (' ||
                                    rec_c3.cons_type || '): ';
	    for rec_c4 in c4(rec_c1.owner, rec_c1.table_name, 
                             rec_c3.constraint_name) loop	  
		str := str || rec_c4.column_name || ' ';
	    end loop;
	    dbms_output.put_line( str );
            output_constraint( rec_c3 );            
	end loop;

	for rec_c5 in c5(rec_c1.owner, rec_c1.table_name) loop
	    str := 'Index ' || rec_c5.index_name || ' (' ||
			rec_c5.uniqueness || ') : ';
	    dbms_output.put_line(str);
	    str := '>     ';
	    for rec_c6 in c6(rec_c1.owner, rec_c1.table_name, 
					rec_c5.index_name) loop	  
		str := str || rec_c6.column_name || ' ';
		if ( length(str) > 55 ) then
			dbms_output.put_line(str);
			str := '>     ';
		end if;
	    end loop;
	    dbms_output.put_line( str );
            output_index( rec_c5 );
	end loop;

    	dbms_output.put_line(' ');
	dbms_output.put_line('--------------------------------------------------------------------------------' );
    	dbms_output.put_line(' 
                               ');

    end loop;
    
end;
.
/