oracle查询SQL

oracle如何查询哪个表数据量大
存储空间可以用如下语句查：
select  *　from user_segments s  where s.BYTES  is not null  order by s.BYTES desc；
查记录条数可以用如下语句：
select   *  　from user_tables t where t.NUM_ROWS is not  null  order by t.NUM_ROWS  desc；

SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments WHERE segment_name='EP_SC106'      ---具体占用多大空间
select owner from dba_tables where table_name='CREDIT_PERSON';   ---查询表属于哪个用户

select owner,SEGMENT_TYPE,sum(BYTES)/1024/1024 as SIZE_M from dba_segments group by owner,SEGMENT_TYPE;       数据库用户，怎么看看各个用户下所有的表、索引等使用了多少数据库空间？

select owner,segment_name ,BYTES/1024/1024 as SIZE_M from dba_segments where segment_type = 'TABLE'
order by owner,segment_name;     怎么查看每个用户下各个表占用的数据空间大小

SELECT INSTANCE_NUMBER "inst",
       sample_id,
       TO_CHAR(SAMPLE_TIME, 'MMDD HH24:Mi') "time",
       EVENT,
       COUNT(*)
  FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE sample_time >= to_date(&betim_time,'YYYYMMDDHH24MI')
  AND sample_time <= to_date(&end_time,'YYYYMMDDHH24MI')
  AND event LIKE '%&event%'
  AND instance_number LIKE '%&inst_id%'
  GROUP BY INSTANCE_NUMBER,sample_id,TO_CHAR(SAMPLE_TIME, 'MMDD HH24:Mi'),event
  ORDER BY INSTANCE_NUMBER,sample_id,TO_CHAR(SAMPLE_TIME, 'MMDD HH24:Mi');


 DBA_HIST_ACTIVE_SESS_HISTORY  看一下这个视图能不能查出什么
用sql_id分组看一下这个时间段内的大致情况


SELECT TO_CHAR(SAMPLE_TIME, 'MMDD HH24:Mi:SS') "time",
       SESSION_ID,
       SQL_ID,
       SQL_OPNAME,
   count(*)
  FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE sample_time >= to_date('20180702093000','YYYYMMDDHH24MISS')
  AND sample_time <= to_date('20180702095000','YYYYMMDDHH24MISS')
  group by TO_CHAR(SAMPLE_TIME, 'MMDD HH24:Mi:SS') ,
       SESSION_ID,
       SQL_ID,
       SQL_OPNAME
   order by TO_CHAR(SAMPLE_TIME, 'MMDD HH24:Mi:SS');     修改时间段就行


SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 ,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
       COUNT(*) TOTAL
FROM v$log_history  a
   where first_time>=to_char(sysdate-11)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
  查询归档日志切换是否正常