oracle 时间转换条件查询
SELECT * FROM table WHERE createdate >= to_date('2018/10/8','yyyy/mm/dd')
And t.requestdate <= to_date('2018-12-27 00:35:00','yyyy-mm-dd hh24:mi:ss')
两个Date类型字段:START_DATE,END_DATE,
计算这两个日期的时间差(分别以天,小时,分钟,秒,毫秒):
天:ROUND(TO_NUMBER(END_DATE - START_DATE))
小时: ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
分钟: ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
秒: ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
毫秒: ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)
表空间查询:
SELECT Upper(F.TABLESPACE_NAME) "表空间名",D.TOT_GROOTTE_MB "表空间大小(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比", F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME, Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1
查询表空间下的表:
select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='ULTIMUSDATA';