1：获得大小写敏感信息

SELECT SF_GET_CASE_SENSITIVE_FLAG()；

2：获取GUID

select GUID () ;

3：查询模式下的表名称和注释

SELECT
T.TABLE_NAME AS TABLE_NAME,
U.COMMENTS AS COMMENTS
FROM
DBA_TABLES T
JOIN USER_TAB_COMMENTS U
ON
T.TABLE_NAME =U.TABLE_NAME
WHERE
OWNER=‘DMHR’;

4：查询模式下的所有表字段和注释

select * from user_col_comments where owner = ‘DMHR’；

5：查询数据库实例下字段属性

select owner, table_name,column_name,data_type,data_length
from dba_tab_columns where owner = ‘DMHR’ and table_name=‘CITY’；

6:表增加注释

COMMENT ON TABLE “DMHR”.“CITY” IS ‘CITY’;

7：表字段增加注释

COMMENT ON COLUMN “DMHR”.“CITY”.CITY_ID IS ‘CITY_ID’;

8:数据库占用空间（单位：字节）

SELECT SUM(bytes) from dba_data_files;

9:表对象占用的空间大小（单位：kb）

SELECT TABLE_USED_SPACE(‘DMHR’, ‘CITY’);

10：查询数据文件和联机日志文件信息

SELECT B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES/1024/1024 SIZE_M
FROM DBA_DATA_FILES B
UNION ALL
SELECT ‘RLOG’, A.PATH, A.RLOG_SIZE/1024/1024
FROM V$RLOGFILE A;

11：查询表空间占用率

SELECT
B.FILE_NAME ,
B.TABLESPACE_NAME ,
B.BYTES /1024/1024 SIZE_M ,
(B.BYTES -(NVL(A.BYTES, 0)))/1024/1024 USED_M,
ROUND((B.BYTES-(NVL(A.BYTES, 0)))/(B.BYTES)*100, 2) USEDRATE
FROM
DBA_FREE_SPACE A,
DBA_DATA_FILES B
WHERE
A.FILE_ID(+) = B.FILE_ID
AND A.TABLESPACE_NAME(+) = B.TABLESPACE_NAME
ORDER BY
B.TABLESPACE_NAME;

12：计算字符出现的次数

SELECT regexp_count(CITY_ID,‘J’)+1 FROM “DMHR”.“CITY”;

13：删除字符串中不需要的字符

SELECT CITY_NAME, regexp_replace(CITY_NAME, ‘_’) FROM DMHR.CITY;

14：将字符与数字分离

SELECT CITY_NAME, REGEXP_REPLACE (CITY_NAME, ‘[0-9]’, ‘’) FROM DMHR.CITY;

15：查询只包含字母或数字的数据

SELECT CITY_NAME FROM DMHR.CITY WHERE REGEXP_LIKE (CITY_NAME, ‘^[1]+$’);

16：按字符串中的数字排序

SELECT
CITY_NAME,
TO_NUMBER (REGEXP_REPLACE (CITY_NAME, ‘[^0-9]’, ‘’))
FROM
DMHR.CITY
ORDER BY 2;

17：分割字符串

SELECT CITY_NAME, REGEXP_SUBSTR(CITY_NAME, ‘[^_]+’,1,1) FROM DMHR.CITY;

---

1. 0-9a-zA-Z ↩︎