col partition_name for a20
col subpartition_name for a20
col high_value for a15

set pagesize 1000

select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions where table_name = 'SASH_ALL';


when just installed the partitions names aren't as obvious
then they become
	partition date
	subpartition dbid

SQL> @parts

TABLE_NAME                     PARTITION_NAME       SUBPARTITION_NAME
------------------------------ -------------------- --------------------
SASH_ALL                       P_1                  P_1_S_1702456376
SASH_ALL                       P_1                  SP_1744687586_2
SASH_ALL                       P_2007_10_27         SP_1702456376_3
SASH_ALL                       P_2007_10_27         SP_1744687586_4


column segment_name format a30
select segment_name,PARTITION_NAME,bytes from user_segments
/


SEGMENT_NAME                   PARTITION_NAME            BYTES
------------------------------ -------------------- ----------
SASH_SQLTXT_ALL                                        3145728
SASH_ALL                       P_1_S_1702456376         262144
SASH_ALL                       SP_1744687586_2         3145728
SASH_ALL                       SP_1702456376_3           65536
SASH_ALL                       SP_1744687586_4           65536


execute dbms_stats.GATHER_TABLE_STATS('ASH','V$ASH_ALL');

exec dbms_stats.gather_table_stats (OWNNAME=>'ASH',tabname=>'V$ASH_ALL',granularity=>'SUBPARTITION')
/

exec dbms_stats.gather_table_stats (OWNNAME=>'ASH',tabname=>'V$ASH_ALL', granularity=>'PARTITION')
/

column table_name format a15
column partition_name format a20
column subpartition_name format a20

select table_name,
       partition_name, 
       subpartition_name ,
       blocks,
       empty_blocks
from user_tab_subpartitions;


execute dbms_stats.GATHER_TABLE_STATS('ASH','V$ASH_SQLSTATS_ALL');
execute dbms_stats.GATHER_TABLE_STATS('ASH','V$ASH_SQLPLANS_ALL');
execute dbms_stats.GATHER_TABLE_STATS('ASH','V$ASH_SQLTXT_ALL');

execute show_space('V$ASH_SQLSTATS_ALL');
execute show_space('V$ASH_SQLPLANS_ALL');
execute show_space('V$ASH_SQLTXT_ALL');

SQL> select table_name, blocks from user_tables where table_name like '%SQL%';
TABLE_NAME          BLOCKS
--------------- ----------
V$ASH_SQLPLANS_ALL         28
V$ASH_SQLSTATS_ALL       4810
V$ASH_SQLTXT_ALL      29266

need to clean:

v$ash_sqlstats_all
v$ash_sqlplans_all
v$ash_sqltxt_all

create table v$ash_sqltxt_all
            ( dbid number,
              address raw(8),
              hash_value number,
              command_type number,
              piece number,
              sql_text varchar(64),
              last_found date,
              first_found date,
              found_count number );



TABLE_NAME
------------------------------
DBIDS             - rename? - change to v$ash_targets

LATCHNAME         - rename? - not created yet - v$ash_latchnames_all
ROWCACHE          - rename? - not created yet - v$ash_rowcache_all
SOURCE            - rename? - not created yet - v$ash_source

V$ASHLOG              - rename v$ash_log
V$ASH_ALL             - cleaned 
V$ASH_EVENT_NAMES_ALL - stable
V$ASH_HOST            - stable
V$ASH_HOSTVIEW        - stable
V$ASH_OBJS_ALL        
V$ASH_PARAMS_ALL      - stable ?
V$ASH_SQLPLANS_ALL    - 
V$ASH_SQLSTATS_ALL    -
V$ASH_SQLTXT_ALL      _
V$ASH_USERS_ALL             


 update  v$ash_sqltxt_all
                set
                   last_found = sysdate and
                   found_count = found_count + 1
                where hash_value in (
                        select sql_id from (
                          select count(*) cnt, sql_id
                          from ${REPO_SCHEMA}.v\$ash_all${DBL}
                          where l_dbid = dbid
                             and sql_id != 0
                             and sample_time > (sysdate - 1/24)
                          group by sql_id
                          order by cnt desc )
                        where rownum < 21);

set pagesize 100
col index_name for a20
col table_name for a25
col column_name for a20
col pos for 99

select index_name,table_name,column_name,column_position pos from
user_ind_columns order by index_name,column_position
/
INDEX_NAME           TABLE_NAME                COLUMN_NAME          POS
-------------------- ------------------------- -------------------- ---
DBIDS_I              DBIDS                     HOST                   1
DBIDS_I              DBIDS                     SID                    2
DBIDS_I              DBIDS                     HOME                   3

V$ASH_EVENT_NAMES_I  V$ASH_EVENT_NAMES_ALL     DBID                   1
V$ASH_EVENT_NAMES_I  V$ASH_EVENT_NAMES_ALL     EVENT#                 2

V$ASH_I              V$ASH_ALL                 SAMPLE_TIME            1

V$ASH_OBJS_I         V$ASH_OBJS_ALL            DBID                   1
V$ASH_OBJS_I         V$ASH_OBJS_ALL            OBJECT_ID              2

V$ASH_PARAMS_I       V$ASH_PARAMS_ALL          DBID                   1
V$ASH_PARAMS_I       V$ASH_PARAMS_ALL          NAME                   2

V$ASH_SQLPLANS_I     V$ASH_SQLPLANS_ALL        DBID                   1
V$ASH_SQLPLANS_I     V$ASH_SQLPLANS_ALL        STATEMENT_ID           2

V$ASH_SQLTXT_I       V$ASH_SQLTXT_ALL          DBID                   1
V$ASH_SQLTXT_I       V$ASH_SQLTXT_ALL          HASH_VALUE             2
V$ASH_SQLTXT_I       V$ASH_SQLTXT_ALL          PIECE                  3

V$ASH_USERS_I        V$ASH_USERS_ALL           DBID                   1