oracle

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';

# oracle  

评论

友情链接:

陌上花开

神奇的海螺

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×