SQL queries for database operations

How many times query executed in a  day.

select s.begin_interval_time, sql.sql_id as sql_id, sql.executions_delta as exe_delta, sql.EXECUTIONS_TOTAL

from dba_hist_sqlstat sql, dba_hist_snapshot s

where sql_id='f3dwn5j209c7y'

and s.snap_id = SQL.snap_id

and s.begin_interval_time> TO_date('16-MAY-2018 00:00', 'dd-mon-yyyy hh24:mi')

and s.begin_interval_time< TO_date('17-MAY-2018 00:00', 'dd-mon-yyyy hh24:mi') order by s.begin_interval_time;

How to find the size of table partitions and sub partitions.

Find the size of Table(DEPARTMENT) with partitions

SELECT * FROM (

  SELECT

    owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,

    tablespace_name, extents, initial_extent,

    ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg

  FROM (

    -- Tables

    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,

          segment_name AS table_name, bytes,

          tablespace_name, extents, initial_extent

    FROM   dba_segments

    WHERE 

--segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') and segment_name='TABLENAME'))

segment_type IN ('TABLE PARTITION') and segment_name='DEPARTMENT'))

Find the size of Table(DEPARTMENT) which is partitioned (has subpartitions)

select

    s.owner,

    s.segment_name,

    sp.partition_name,

    SUM(bytes/1024/1024/1024) "GB"

from dba_segments s

    join dba_tab_subpartitions sp ON sp.subpartition_name = s.partition_name

where  segment_name='DEPARTMENT'

group by s.owner,

    s.segment_name,

    sp.partition_name;