oracle

表空间使用情况统计tablespace

SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN GB)",  FREE.FREE_BYTES AS "FREE SPACE(IN GB)"
FROM
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99')||'G(USERD)' AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED
INNER JOIN
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99')||'G(FREE)' AS FREE_BYTES FROM  USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE

how-to-determine-size-of-tablespace-oracle

paging 分页

SELECT * FROM
(
    SELECT a.*, rownum r__
    FROM
    (
        SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%' --condition 条件　条件　じょうけん
        ORDER BY OrderDate DESC, ShippingDate DESC
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)

参考：paging-with-oracle

like 下滑线

select *
from table_test
where col1 like '%!_%' escape '!'

参考：https://stackoverflow.com/questions/42946040/how-to-escape-underscores-in-oracle-sql-developer/42946524

对比两张表数据差异

(select * from T1 minus select * from T2) -- all rows that are in T1 but not in T2
union all
(select * from T2 minus select * from T1)  -- all rows that are in T2 but not in T1
;

参考：https://stackoverflow.com/questions/688537/oracle-diff-how-to-compare-two-tables

batch update 批量更新表

UPDATE
(
  SELECT 
    t1.NAME t1name, t1.FURIGANA t1furigana, t1.SEIBETSU t1seibetsu,
    t2.NAME t2name, t2.FURIGANA t2furigana, t2.SEIBETSU t2seibetsu
  FROM
    table01 t1
  INNER JOIN 
    table02 t2
  ON 
    t1.id = t2.id
)
SET t1name = t2name, t1furigana = t2furigana
WHERE t1seibetsu = '女';

【Oracle】JOINを使ったUPDATE文で複数レコードを同時に更新する

sql-update-multiple-records-in-one-query/20255203

函数

TRUNC

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt

trunc(sysdate)返回当前日期

创建用户

CREATE USER username IDENTIFIED BY apassword;

GRANT CONNECT TO username;

GRANT EXECUTE on schema.procedure TO username;

GRANT SELECT [, INSERT] [, UPDATE] [, DELETE] on schema.table TO username; -- 授权

REVOKE SELECT [,INSERT] [,UPDATE] [,DELETE] on schema.table FROM username; -- 收回权限

限制远程IP访问

CREATE OR REPLACE TRIGGER system.check_ip_addresses 
AFTER LOGON 
ON DATABASE
DECLARE 
  l_ip_address VARCHAR2(16) := SYS_CONTEXT('USERENV', 'IP_ADDRESS'); 
BEGIN 
  IF USER IN ('ABC') THEN
    IF l_ip_address IN ('192.168.0.10') THEN
      NULL;
    ELSE
      RAISE_APPLICATION_ERROR(-20000, 'Can not log in from this IP address (' || l_ip_address || ')');
    END IF;
  END IF;
END; 

restrict schema access to specific ip address in oracle