SQL 小技巧
SQL 小技巧
不定时更新,欢迎交流指正
Oracle
1.浮点数转为字符
trim('.' from to_char(xxx,'fm999990.99'))
2.查询当前用户下各表大小
查看数据库表空间存储对象的大小
SELECT *
FROM (SELECT SEGMENT_NAME,
T.SEGMENT_TYPE,
round(SUM(BYTES) / 1024 / 1024/1024,2) SEGMENT_SIZE
FROM USER_SEGMENTS T
GROUP BY T.SEGMENT_NAME, T.SEGMENT_TYPE) T
ORDER BY SEGMENT_SIZE DESC;
实际表占用空间大小,包括表所属对象:INDEX、LOBINDEX、LOBSEGMENT
SELECT *
FROM (SELECT SEGMENT_NAME,
SEGMENT_TYPE,
ROUND(SUM(BYTES) / 1024 / 1024 / 1024, 2) SEGMENT_SIZE
FROM (SELECT T.TABLESPACE_NAME,
T.SEGMENT_NAME,
T.SEGMENT_TYPE,
T.BYTES
FROM USER_SEGMENTS T
WHERE T.SEGMENT_TYPE NOT IN
('INDEX', 'LOBINDEX', 'LOBSEGMENT')
UNION ALL
SELECT T.TABLESPACE_NAME,
UL.TABLE_NAME AS SEGMENT_NAME,
'TABLE' AS SEGMENT_TYPE,
T.BYTES
FROM USER_SEGMENTS T
INNER JOIN USER_LOBS UL
ON T.SEGMENT_NAME = UL.SEGMENT_NAME
WHERE T.SEGMENT_TYPE = 'LOBSEGMENT'
UNION ALL
SELECT T.TABLESPACE_NAME,
UI.TABLE_NAME AS SEGMENT_NAME,
'TABLE' AS SEGMENT_TYPE,
T.BYTES
FROM USER_SEGMENTS T
INNER JOIN USER_INDEXES UI
ON T.SEGMENT_NAME = UI.INDEX_NAME
WHERE T.SEGMENT_TYPE IN ('INDEX', 'LOBINDEX')) T
GROUP BY SEGMENT_NAME, SEGMENT_TYPE) T
ORDER BY SEGMENT_SIZE DESC;
3.PL/SQL 导入CSV文件前建议先查一下字符编码
select userenv(‘language’) from dual;
得到字符编码后再将csv文件做对应字符编码转换,避免不必要的时间浪费
本文来自博客园,作者:Splus,转载请注明原文链接:https://www.cnblogs.com/s-plus/p/15889932.html