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;
