ORACLE 中非常有用的语句

Sun_Hongbin

已于 2024-04-12 13:18:40 修改

阅读量388

点赞数 1

文章标签： oracle 数据库

于 2019-11-28 10:49:50 首次发布

本文链接：https://blog.csdn.net/Sun_Hongbin/article/details/103289256

版权

工作后才开始接触数据库增删改查的多重复杂场景，学校自学的时候都是小打小闹。记录一下工作中常用的 Oracle 数据库的技巧。

去重：DISTINCT

SELECT DISTINCT TRIM(NET_BUS_NET) FROM ZWR_HST_NET_T

去空格：TRIM

SELECT DISTINCT TRIM(NET_BUS_NET) FROM ZWR_HST_NET_T

截取某一个字符前的值：SUBSTR INSTR

SELECT SUBSTR('AAA-BBB-CCC-DDD', 1, INSTR('AAA-BBB-CCC-DDD', '-', -1)-1) FROM dual

第一个1：从哪一位开始，如果改成3，结果为 A-BBB-CCC-D
INSTR(‘AAA-BBB-CCC-DDD’, ‘-’, -1)-1 : 最后不减去1，则结果为AAA-BBB-CCC- , 所以第一个1和最后的-1决定了结果长度
：括号里面的-1是从后往前数第几个符号，改成1的话结果为 AAA-

实战案例：资源中心函数，ZWRCMD_NetCheck CMDB查询结果网段检查 ——

SELECT DISTINCT SUBSTR(VWA_SRV_IPA, 1, INSTR(VWA_SRV_IPA, '.', -1)) 
FROM ZWR_CMD_VWA_T

AS后的字段取不到问题-视图问题

SELECT DISTINCT SUBSTR(VWA_SRV_IPA, 1, INSTR(VWA_SRV_IPA, '.', -1)) AS SEG , VWA_PRD_STS, VWA_RUN_STS, VWA_VWA_COD, VWA_VMA_NAM 
FROM ZWR_CMD_VWA_T 
WHERE SUBSTR(VWA_SRV_IPA, 1, INSTR(VWA_SRV_IPA, '.', -1)) != ' ' 
AND TRIM(SUBSTR(VWA_SRV_IPA, 1, INSTR(VWA_SRV_IPA, '.', -1))) NOT IN (SELECT DISTINCT TRIM(NET_BUS_NET) FROM ZWR_HST_NET_T)

前面可以用AS SEG，但是AND 后面接的不能写 SEG NOT IN …

查看ORACLE数据库版本

SELECT * FROM v$version

一张表增加数据时，该表的属性附加表也增加一条数据：MERGE INTO

MERGE INTO ZWR_DAP_ART_T A 
USING (SELECT APP_SVC_COD, APP_APP_NAM  FROM ZWR_DAP_APP_T) B
ON (A.ART_APP_NAM = B.APP_APP_NAM AND A.ART_SVC_COD = B.APP_SVC_COD)
WHEN NOT MATCHED THEN
INSERT (ART_SVC_COD, ART_APP_NAM, ART_DTB_NAM, ART_MOV_TAG, ART_GRY_TAG, ART_ERR_TAG, ART_MNT_DAT, ART_MNT_TIM, ART_RCD_VER, ART_RCD_STS)
VALUES (B.APP_SVC_COD, B.APP_APP_NAM, ' ', ' ', ' ', ' ', 
TO_NUMBER (TO_CHAR (SYSDATE, 'yyyyMMdd')),  TO_NUMBER (TO_CHAR (SYSDATE, 'hh24miss')), '1', 'A');`

用一张表数据去更新领一张表（多行批量更新不能用UPDATE否则会报错“单行子查询返回多个行”）：MERGE INTO

MERGE INTO ZWR_DWA_VWA_T A 
USING (SELECT APP_NAM,DIV_NAM ,DIV_RAC, RDD_NBR , DPL_SEQ ,VWA_IPA  FROM APP1.NEWTABLE) B 
ON (A.VWA_VWA_IPA = B.VWA_IPA) 
WHEN MATCHED THEN 
UPDATE SET A.VWA_APP_NAM = B.APP_NAM, A.VWA_DIV_NAM = B.DIV_NAM ,  
A.VWA_DIV_RAC =  B.DIV_RAC, A.VWA_RDD_NBR B.RDD_NBR , A.VWA_APP_SEQ =  B.DPL_SEQ

接上一条，批量更新只能是主动给值

UPDATE APP1.NEWTABLE SET MSG = 'N' WHERE VWA_IPA IN
(SELECT  VWA_IPA FROM ZWR_DWA_VWA_T INNER JOIN APP1.NEWTABLE 
ON VWA_VWA_IPA = VWA_IPA AND VWA_APP_NAM != APP_NAM)

Oracle查看数据库最大连接数与当前连接数

数据库允许的最大连接数

select value from v$parameter where name = 'processes'

–当前的连接数

select count(*) from v$process

数据库时间

TO_NUMBER(TO_CHAR(SYSDATE,'yyyymmdd')) #注意用yyyy，小写y表示当天所在的年份，大写YYYY代表week in which year
TO_NUMBER(TO_CHAR(SYSDATE,'hh24miss'))

分页查询

使用ROWNUM的方式进行分页查询，查询页起始记录和结束记录，使用两层嵌套的查询方式

SELECT *
FROM
(
      SELECT ROWNUM RN, T1.*
      FROM (
              SELECT * FROM TRANS_XDT_T
              WHERE TRS_SEQ <= '查询条件'
              ORDER BY TRS_SEQ ASC
      ) T1
      WHERE ROWNUM <= endIndex
)T2
WHERE RN >= beginIndex
ORDER BY T2.TRS_SEQ ASC

使用ROWNUM的方式进行分页查询，查询页起始记录和结束记录，使用一层筛选的查询方式

SELECT *
FROM
(
       SELECT ROWNUM RN, T1.*
       FROM (
               SELECT * FROM TRANS_XDT_T
               WHERE TRS_SEQ <= '查询条件'
               ORDER BY TRS_SEQ ASC
       ) T1
)T2
WHERE RN >= beginIndex AND RN <= endIndex
ORDER BY T2.TRS_SEQ ASC

⭐使用续传键值进行续传查询

SELECT T1.*
FROM (
        SELECT * FROM TRANS_XDT_T
        WHERE TRS_SEQ >= '流水号'
        ORDER BY TRS_SEQ ASC
) T1
WHERE ROWNUM <= N
ORDER BY T1.TRS_SEQ ASC

使用ROWNUM的方式进行分页查询，查询页起始记录和结束记录，使用两层嵌套的查询方式。并通过先读索引，再关联查询其他字段的方式进行优化。

SELECT T4.*
FROM
(
        SELECT T2.TRS_SEQ
        FROM
        (
                SELECT ROWNUM RN, T1.TRS_SEQ
                FROM (
                        SELECT TRS_SEQ
                        FROM TRANS_XDT_T
                        WHERE TRS_SEQ <= '查询条件'
                        ORDER BY TRS_SEQ ASC
                        ) T1
                WHERE ROWNUM <= endIndex
        )T2
        WHERE RN >= beginIndex
) T3
INNER JOIN TRANS_XDT_T T4
ON T3.TRS_SEQ = T4.TRS_SEQ
ORDER BY T4.TRS_SEQ ASC

取前10名，然后给这十条记录排序

SELECT RANK() OVER(ORDER BY OPR_CNT) AS RANK, T.* FROM TABLE ORDER BY T.SORT #这种方式会有并列第一名的情况
SELECT ROW_NUMBER() OVER(ORDER BY OPR_CNT DESC) AS RANK, T.* FROM TABLE ORDER BY T.SORT #这种方式不会有并列第一名的情况，DESC的用处是数量最多的给第一名

ORACLE使用场景甄别

当需要从A表中查出数据放到B表中，一次又要重复查多项时，也就是插入是批量插入时有两种方法，
一、addBatch()然后executeBatch();
二、

INSERT INTO TABLE_A VALUES(...)  
SLECET * FROM TABLE_B 
UNION ALL 
SELECT * FROM TABLE_B

实践证明，用executeBatch的效率比拼接SQL的效率更高，时间缩短了近一倍

ONLINE创建表空间

CREATE INDEX 索引名 ON 表名(字段1, 字段2, 字段3)
TABLESPACE 表空间名称 ONLINE;

HAVING COUNT(字段名)用法

HAVING子句用于在GROUP BY 子句后过滤分组结果。与COUNT函数结合使用，来返回满足特定条件的记录数。
可以用来检查是否有重复数据。

语法格式如下：

SELECT col1, COUNT(col2)
FROM tbl
GROUP BY clo1
HAVING COUNT(col2) > value
ORDER BY col1 DESC;

案例

SELECT BUSI_DATE,CUST_ID
FROM tbl
GROPY BY BUSI_DATE,CUST_ID
HAVING COUNT(1) > 1
ORDER BY BUSI_DATE DESC;

创建同义词

有时候SQL表名前不想加SCHEMA，就可以创建同义词。这样有个好处就是简便，但是不规范。对数据库执行SQL他能找到特定的表。但是对于多个SCHEMA下有同名表的就会有问题了，不一定就调用到哪个标了。
这里的场景是为了接入SQL治理平台，怕有些SQL表名前不一定添加了属主引用，为了保证只读用户能正常解析SQL执行计划，需要给各表都创建公共同义词。
先用数据库管理员用户查出哪些表没有公共同义词，并拼装创建公共同义词的SQL。

SELECT
	'CREATE PUBLIC SYNONYM '||T.TABLE_NAME||' FOR '||T.OWNER||'.'||T.TABLE_NAME||';'
FROM
	SYS.ALL_TABLES T 
WHERE
	T.OWNER IN (‘owner_name1’,’owner_name2’,’...’) -- 这里替换为要监控的owner列表
	AND T.TABLE_NAME NOT IN (
SELECT
	S.TABLE_NAME 
FROM
	SYS.ALL_SYNONYMS S 
WHERE
	S.OWNER = 'PUBLIC' 
	AND S.TABLE_OWNER = T.OWNER);

再用数据库管理员用户执行查询结果，创建公共同义词。创建后，再次执行上面的SQL检查，查询结果应该为空。
后面新增的表，发布到生产时，同样需按CREATE PUBLIC SYNONYM synonym_name FOR owner_name.table_name的格式，创建公共同义词。

我先创建了一波，结果查询发现有多个SCHEMA下同名的表，所以检查了下是不是这个问题：

SELECT * 
  FROM SYS.ALL_SYNONYMS S
 WHERE S.OWNER='PUBLIC' 
   AND S.TABLE_NAME = '这里输入执行有报错说名称已被占用的表名，需大写，如果有多个表，=的条件改成in的条件'

发现确实查出了两条记录，RDMODS和RDMEDW下有同名的，为了安全起见，这样的同义词要删掉，避免不知情的同学不写属主导致调错表：
删除同义词

SELECT 'DROP PUBLIC SYNONYM '||S.SYNONYM_NAME||';'
  FROM SYS.ALL_SYNONYMS S
 WHERE S.SYNONYM_NAME 
    IN (SELECT T.TABLE_NAME  FROM SYS.ALL_TABLES T GROUP BY TABLE_NAME HAVING COUNT(*)>=2)

NOT IN会把NULL值一起过滤

如果一个表中有A, B, C，NULL四种数据，那么NOT IN (‘A’, ‘B’) 只会把C查出，NULL那条数据不会被查出来

Oracle查询表空间使用情况

查询表空间使用情况可以通过以下代码实现：

SELECT segment_name "表名",
       segment_type "对象类型",
       sum(bytes) / 1024 / 1024 "占用空间MB"
  FROM dba_extents
 GROUP BY segment_name, segment_type

通过以上代码，可以得到每个表空间包含的数据文件所占用的文件名、字节数、块数和最大字节数。

回收水位线

DBA提供：
select 'alter table '||owner||'.'||table_name||' enable row movement;' from dba_tables WHERE table_name  in ('TIF_VOUCHINFO','TPORTHOLD','TPORTVALUE','TPORTCURRENTS','TIF_GZB');
 
select 'alter table '||owner||'.'||table_name||' move;' from dba_tables where table_name in ('TIF_VOUCHINFO','TPORTHOLD','TPORTVALUE','TPORTCURRENTS','TIF_GZB') and partitioned='NO';
 
select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_Name||' ;' from dba_tab_partitions
where table_name in (select table_name from dba_tables where table_name in ('TIF_VOUCHINFO','TPORTHOLD','TPORTVALUE','TPORTCURRENTS','TIF_GZB') and partitioned='YES');
 
select 'alter table '||owner||'.'||table_name||' disable row movement;' from dba_tables WHERE table_name  in ('TIF_VOUCHINFO','TPORTHOLD','TPORTVALUE','TPORTCURRENTS','TIF_GZB');
 
 
=====================================
 
-- 回收高水位线
SELECT owner
, TABLE_NAME 表名
, NUM_ROWS 表实际数据条数
, ROUND(AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9) 表需要存储空间MB
, ROUND(BLOCKS*8/1024) 表实际存储空间MB
, ROUND((BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9)) RECOVER_MB
FROM dba_tables
WHERE BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9 > 1
AND OWNER IN ('SCHEMA名')
order by RECOVER_MB desc;
 
-- 查询需要回收的高水位表
SELECT OWNER,
       TABLE_NAME,
       ROUND ( (BLOCKS * 8) / 1024, 2) "HIGH_WATER(MB)",
       ROUND ( (NUM_ROWS * AVG_ROW_LEN / 1024) / 1024, 2) "USED_SPACE(MB)",
       ROUND ( (BLOCKS * 10 / 100) * 8 / 1024, 2) "RESERVE_SPACE(MB)",
       ROUND ( (BLOCKS * 8 - (NUM_ROWS * AVG_ROW_LEN / 1024) - BLOCKS * 8 * 10 / 100) /1024 , 2) "RECOVERY_SPACE(MB)",
       DECODE (NVL((BLOCKS * 8),0), 0, 0, ROUND ( (1 - (NUM_ROWS * AVG_ROW_LEN / 1024)/(BLOCKS * 8)) * 100 ,0)) "PERCENT(%)" --一般超过20%就需要回收了
  FROM DBA_TABLES
 WHERE OWNER IN ('ETL', 'ICRCONFIG', 'ICRDATA', 'ICREDIT_CONFIG', 'ICREDIT_DATA', 'RDMDDW', 'RDMEDW', 'RDMETL', 'RDMODS', 'RISKCONFIG', 'RISKDATA', 'RPCONFIG', 'RPDATA')
 --AND TABLE_NAME = '表名'  
   AND ROUND ( (BLOCKS * 8) / 1024, 2) > 1024 -- 只查出超过1G数据量的
 ORDER BY "PERCENT(%)" DESC;
 
--a) 启动行迁移：
ALTER TABLE 表名 ENABLE ROW MOVEMENT;
--b）进行表的收缩：
ALTER TABLE 表名 MOVE;
--c）关闭行迁移
ALTER TABLE 表名 DISABLE ROW MOVEMENT;
 
--blocks字段和有数据的字段对比，用1减，求出没有数据的块的百分比（一般超过20%就需要回收了）
select round((1-a.used/b.num_total)*100,0)  percent from
(SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) Used FROM 表名) a,
(select blocks num_total from dba_tables where table_name='表名' and owner='SCHEMA名') b;
 
-- 重新分析（如果表数据大要谨慎执行，时间会很久）
ANALYZE TABLE 表名 COMPUTE STATISTICS;
 
-- 重建索引
alter INDEX 索引名 REBUILD;

权限

--赋予表权限
GRANT SELECT ON table_name FROM user;
--收回表访问权限
REVOKE SELECT ON table_name FROM user;
--收回视图访问权限
REVOKE SELECT ON view_name TO user;

权限

-- ==================== 创建一个只读用户给其他平台 ======================
-- 创建用户密码
CREATE USER dgp_reader IDENTIFIED BY 密码;

-- 赋予该用户连接到数据库的权限
GRANT CONNECT TO dgp_reader;

-- 将查询任何表的权限授予用户 'dgp_reader'
GRANT SELECT ANY TABLE TO dgp_reader;

-- 授予权限，包括对系统表的访问
GRANT SELECT ANY DICTIONARY TO dgp_reader;

-- ==================== 创建一个只支持表增删改查业务表、修改存储过程和函数的用户APP1 ======================
-- 创建用户密码
CREATE USER APP1 IDENTIFIED BY 密码;

-- 赋予该用户连接到数据库的权限
GRANT CONNECT TO APP1;

-- 将查询任何表的权限授予用户 'APP1'
GRANT SELECT ANY TABLE TO APP1;

-- 删除用户
--DROP USER APP1 CASCADE;


SELECT * FROM V$SESSION 
WHERE USERNAME IS NOT NULL 
AND USERNAME NOT IN ('ZABBIX') 
AND STATUS = 'ACTIVE' 
AND MODULE NOT IN ('dataflow.exe') ;

-- 表
BEGIN
    FOR t IN (SELECT OWNER, table_name FROM DBA_TABLES 
               WHERE OWNER  NOT IN ('SYS', 'SYSTEM', 'WMSYS', 'EXFSYS', 'APPQOSSYS','OUTLN', 'DBMGR', 'DBSNMP', 'XDB')
               ) LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || T.OWNER || '.' || t.table_name || ' TO APP1';
    END LOOP;
END;

-- 存储过程，这样循环有的编译报错的就过不去。我是直接全量给权限
GRANT CREATE ANY PROCEDURE,ALTER ANY PROCEDURE to APP1;
-- 下面这样循环会报错
BEGIN
    FOR t IN (SELECT OWNER,OBJECT_NAME FROM DBA_PROCEDURES
               WHERE OWNER  NOT IN ('SYS', 'SYSTEM', 'WMSYS', 'EXFSYS', 'APPQOSSYS','OUTLN', 'DBMGR', 'DBSNMP', 'XDB')
                 AND OBJECT_TYPE = 'PROCEDURE' 
               ) LOOP
        EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || T.OWNER || '.' || t.OBJECT_NAME || ' TO APP1';
    END LOOP;
END;

-- 执行函数的权限
BEGIN
    FOR t IN (
              SELECT DISTINCT OWNER, NAME
                FROM DBA_SOURCE
               WHERE OWNER  NOT IN ('SYS', 'SYSTEM', 'WMSYS', 'EXFSYS', 'APPQOSSYS','OUTLN', 'DBMGR', 'DBSNMP', 'XDB')
                 AND TYPE = 'FUNCTION') LOOP
        EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || T.OWNER || '.' || t.NAME || ' TO APP1';
    END LOOP;
END;

--赋予创建函数权限
GRANT CREATE FUNCTION TO APP1;

--赋予视图权限
GRANT SELECT ON RDMEDW.V_CRD_DAILY_REPORT TO APP1;
--如果报错RDMDDW.H22_CRD_GUARANTEE_RATIO 不是可授予的对象，就执行下列语句
GRANT SELECT ON RDMDDW.H22_CRD_GUARANTEE_RATIO TO RDMEDW WITH GRANT OPTION;

-- ============================== OKR统计用 ==============================

-- 信用
'ETL', 'ICRCONFIG', 'ICRDATA', 'ICREDIT_CONFIG', 'ICREDIT_DATA', 'RDMDDW', 'RDMEDW', 'RDMETL', 'RDMODS', 'RISKCONFIG', 'RISKDATA', 'RPCONFIG', 'RPDATA'

-- 集市
'ETL', 'PTCONFIG', 'PTDATA', 'RDMADI', 'RDMDDW', 'RDMDROP', 'RDMEDW' , 'RDMETL', 'RDMODS', 'RPCONFIG', 'RPDATA'

-- 流动性
'ETL', 'LMCONFIG', 'LMDATA', 'RDM', 'RDMDDW', 'RDMEDW', 'RDMODS' , 'RPCONFIG', 'RPDATA'

-- 有注释的字段数量
SELECT COUNT(*) FROM ( 
   SELECT a.OWNER , a.table_name, a.comments, b.column_name, a.comments 
     FROM dba_tab_comments a
LEFT JOIN dba_col_comments b 
       ON a.table_name = b.table_name
    WHERE a.table_type = 'TABLE' 
      AND a.comments IS NOT NULL
      AND a.OWNER IN ('ETL', 'ICRCONFIG', 'ICRDATA', 'ICREDIT_CONFIG', 'ICREDIT_DATA', 'RDMDDW', 'RDMEDW', 'RDMETL','RDMODS','RISKCONFIG', 'RISKDATA', 'RPCONFIG', 'RPDATA')
    );
    
-- 总的的字段数量
SELECT COUNT(*) FROM ( 
   SELECT a.OWNER , a.table_name, a.comments, b.column_name, a.comments 
     FROM dba_tab_comments a
LEFT JOIN dba_col_comments b 
       ON a.table_name = b.table_name
    WHERE a.table_type = 'TABLE' 
      AND a.OWNER IN ('ETL', 'ICRCONFIG', 'ICRDATA', 'ICREDIT_CONFIG', 'ICREDIT_DATA', 'RDMDDW', 'RDMEDW', 'RDMETL','RDMODS'
    ,'RISKCONFIG', 'RISKDATA', 'RPCONFIG', 'RPDATA')
    );

-- 有表注释的表数量
SELECT COUNT(*) FROM ( 
SELECT a.OWNER , a.table_name, a.comments
     FROM dba_tab_comments a
    WHERE a.table_type = 'TABLE' 
      AND a.comments IS NOT NULL 
      AND a.OWNER IN ('ETL', 'ICRCONFIG', 'ICRDATA', 'ICREDIT_CONFIG', 'ICREDIT_DATA', 'RDMDDW', 'RDMEDW', 'RDMETL','RDMODS'
    ,'RISKCONFIG', 'RISKDATA', 'RPCONFIG', 'RPDATA')
);

-- 总的表数量
SELECT COUNT(*) FROM ( 
SELECT a.OWNER , a.table_name, a.comments
     FROM dba_tab_comments a
    WHERE a.table_type = 'TABLE' 
      AND a.OWNER IN ('ETL', 'ICRCONFIG', 'ICRDATA', 'ICREDIT_CONFIG', 'ICREDIT_DATA', 'RDMDDW', 'RDMEDW', 'RDMETL','RDMODS'
    ,'RISKCONFIG', 'RISKDATA', 'RPCONFIG', 'RPDATA')
);

DB LINK

--查看DBLINK链接信息
SELECT * FROM DBA_DB_LINKS;

--创建语句
CREATE PUBLIC DATABASE LINK XYFX CONNECT TO WOLF  IDENTIFIED BY wolf USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.155.166 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ncrms)
    )
)';

-- 删除DBLINK
DROP PUBLIC DATABASE LINK LINK_NAME;

-- 查询语句
SELECT * FROM ICRCONFIG.T_INDUSTRY_INFO@XYFX

索引

-- 查询索引最后分析的时间
  SELECT *
    FROM DBA_INDEXES
   WHERE LAST_ANALYZED IS NOT NULL
ORDER BY LAST_ANALYZED DESC;

-- 查询索引创建的时间
   SELECT * 
    FROM DBA_OBJECTS 
   WHERE OBJECT_TYPE='INDEX'
     AND OBJECT_NAME NOT LIKE 'SYS%'
ORDER BY CREATED DESC;

存储过程

DECLARE 
RETURN_MESSAGE VARCHAR2(2000);
RETURN_CODE NUMBER;
BEGIN
	ICRCONFIG.P_PRO_STOCK_BALANCE_O32('20240402', RETURN_MESSAGE, RETURN_CODE);
END;

Sun_Hongbin

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
ORACLE 中非常有用的语句

【去重】DISTINCTSELECT DISTINCT TRIM(NET_BUS_NET) FROM ZWR_HST_NET_T【去空格】TRIMSELECT DISTINCT TRIM(NET_BUS_NET) FROM ZWR_HST_NET_T【截取某一个字符前的值】SUBSTR INSTRSELECT SUBSTR('AAA-BBB-CCC-DDD', 1, INSTR('A...
复制链接

扫一扫