SELECT * FROM (

  SELECT D.TABLESPACE_NAME,

          SPACE || 'M' "SUM_SPACE(M)",

          BLOCKS "SUM_BLOCKS",

          SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",

          ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'

             "USED_RATE(%)",

          FREE_SPACE || 'M' "FREE_SPACE(M)"

     FROM (  SELECT TABLESPACE_NAME,

                    ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,

                    SUM (BLOCKS) BLOCKS

               FROM DBA_DATA_FILES

           GROUP BY TABLESPACE_NAME) D,

          (  SELECT TABLESPACE_NAME,

                    ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE

               FROM DBA_FREE_SPACE

           GROUP BY TABLESPACE_NAME) F

    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

   UNION ALL                                                           

   SELECT D.TABLESPACE_NAME,

          SPACE || 'M' "SUM_SPACE(M)",

          BLOCKS SUM_BLOCKS,

          USED_SPACE || 'M' "USED_SPACE(M)",

          ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",

          NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"

     FROM (  SELECT TABLESPACE_NAME,

                    ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,

                    SUM (BLOCKS) BLOCKS

               FROM DBA_TEMP_FILES

           GROUP BY TABLESPACE_NAME) D,

          (  SELECT TABLESPACE_NAME,

                    ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,

                    ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE

               FROM V$TEMP_SPACE_HEADER

           GROUP BY TABLESPACE_NAME) F

    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

   ORDER BY 1)  

   WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM');

SELECT *

  FROM (SELECT SEGMENT_NAME,

               SUM(BYTES) / 1024 / 1024 TOTAL_MB,

               TABLESPACE_NAME

          FROM DBA_SEGMENTS

         WHERE TABLESPACE_NAME IN  

               ('SYSTEM', 'SYSAUX')

         GROUP BY SEGMENT_NAME, TABLESPACE_NAME

         ORDER BY 2 DESC)

 WHERE ROWNUM <= 20;

SELECT OCCUPANT_NAME "Item",

        SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",

        SCHEMA_NAME "Schema",

        MOVE_PROCEDURE "Move Procedure"

   FROM V$SYSAUX_OCCUPANTS

  ORDER BY 1;

select count(*) from  WRH$_ACTIVE_SESSION_HISTORY;

select min(snap_id),max(snap_id) from  wrh$_active_session_history;

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>1,high_snap_id => 1600);

SELECT SEGMENT_NAME, PARTITION_NAME, BYTES / 1024 / 1024 / 1024 GB

  FROM DBA_SEGMENTS

 WHERE SEGMENT_NAME = 'WRH$_ACTIVE_SESSION_HISTORY';

alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_SES_MXDB_MXSN;

alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_2831053008_0;

select index_name from dba_indexes where table_name='WRH$_ACTIVE_SESSION_HISTORY';

alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild;

select partition_name from dba_ind_partitions where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK';

alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_2831053008_0;

alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_SES_MXDB_MXSN;

select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';

select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY_PK';