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文件做对应字符编码转换，避免不必要的时间浪费