首页分享 SQL 小技巧

SQL 小技巧

来源：花匠小妙招时间：2024-11-06 15:39

SQL 小技巧

不定时更新，欢迎交流指正

Oracle

1.浮点数转为字符

trim('.' from to_char(xxx,'fm999990.99')) 12.查询当前用户下各表大小

查看数据库表空间存储对象的大小

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

实际表占用空间大小，包括表所属对象：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;

12345678910111213141516171819202122232425262728293031

参考地址

3.PL/SQL 导入CSV文件前建议先查一下字符编码

select userenv(‘language’) from dual; 1

得到字符编码后再将csv文件做对应字符编码转换，避免不必要的时间浪费