drop table SSI_ETAT;

create table SSI_ETAT (
        table_owner                     varchar2(30),
        table_name                      varchar2(30),
        tablespace_name                 varchar2(30),
        segment_space_management        varchar2(6),
        partitioned                     varchar2(3),
        partition_name                  varchar2(30),
        subpartition_name               varchar2(30),
        pct_free                        number,
        avg_row_len                     number,
        last_analyzed                   date,
        chain_cnt                       number,
        blocks                          number,
        avg_space                       number,
        num_rows                        number,
        total_blocks                    number,
        unused_blocks                   number,
        unformatted_blocks              number,
        fs1_blocks                      number,
        fs2_blocks                      number,
        fs3_blocks                      number,
        fs4_blocks                      number,
        full_blocks                     number,
        free_blocks                     number
) tablespace TOOLS_001_PMO2;

insert into ssi_etat (table_owner, table_name, tablespace_name, segment_space_management, partitioned, pct_free, avg_row_len, last_analyzed, chain_cnt, blocks, avg_space, num_rows)
    select a.owner table_owner,
           a.table_name,
           a.tablespace_name,
           b.segment_space_management,
           a.partitioned,
           a.pct_free,
           a.avg_row_len,
           a.last_analyzed,
           a.chain_cnt,
           a.blocks,
           a.avg_space,
           a.num_rows
    from   dba_tables a,
           dba_tablespaces b
    where  a.owner = 'OPS$PSGADMIN'
    and    a.table_name in ('HISTORY')
    and    a.tablespace_name = b.tablespace_name
    and    a.partitioned = 'NO';

insert into ssi_etat (table_owner, table_name, tablespace_name, segment_space_management, partitioned, partition_name, pct_free, avg_row_len, last_analyzed, chain_cnt, blocks, avg_space, num_rows)
    select a.table_owner,
           a.table_name,
           a.tablespace_name,
           b.segment_space_management,
           'YES',
           a.partition_name,
           a.pct_free,
           a.avg_row_len,
           a.last_analyzed,
           a.chain_cnt,
           a.blocks,
           a.avg_space,
           a.num_rows
    from   dba_tab_partitions a,
           dba_tablespaces b
    where  a.table_owner = 'OPS$PSGADMIN'
    and    a.table_name in ('HISTORY')
    and    a.tablespace_name = b.tablespace_name
    and    a.subpartition_count = 0;

insert into ssi_etat (table_owner, table_name, tablespace_name, segment_space_management, partitioned, partition_name, subpartition_name, pct_free, avg_row_len, last_analyzed,
chain_cnt, blocks, avg_space, num_rows)
    select a.table_owner,
           a.table_name,
           a.tablespace_name,
           b.segment_space_management,
           'YES',
           a.partition_name,
           a.subpartition_name,
           a.pct_free,
           a.avg_row_len,
           a.last_analyzed,
           a.chain_cnt,
           a.blocks,
           a.avg_space,
           a.num_rows
    from   dba_tab_subpartitions a,
           dba_tablespaces b
    where  a.table_owner = 'OPS$PSGADMIN'
    and    a.table_name in ('HISTORY')
    and    a.tablespace_name = b.tablespace_name;

commit;