ORACLE資料庫相關操作

表操作

-- 截斷表

TRUNCATE TABLE TABLE_NAME;

-- 刪除表

DROP TABLE TABLE_NAME;

-- 查詢表

SELECT * FROM TABLE_NAME;

-- 添加一條記錄

INSERT INTO TABLE_NAME(COLUMN) VALUES(VALUE);

-- 刪除記錄

DELETE FROM TABLE_NAME WHERE COLUMN=VALUE;

-- 修改記錄

UPDATE TABLE_NAME SET COLUMN1='VALUE1' WHERE COLUMN2=VALUE2;

-- 鎖表查詢

SELECT SESS.SID,SESS.SERIAL#,LO.ORACLE_USERNAME,LO.OS_USER_NAME,AO.OBJECT_NAME,LO.LOCKED_MODE FROM V$LOCKED_OBJECT LO,DBA_OBJECTS AO,V$SESSION "SESS" WHERE AO.OBJECT_ID=LO.OBJECT_ID AND LO.SESSION_ID=SESS.SID;

-- 解除鎖表

ALTER SYSTEM KILL SESSION 'XXX,XXX';

時間

-- 查詢系統時間

SELECT SYSDATE FROM DUAL;
SELECT CURRENT_DATE FROM DUAL;
SELECT SYSTIMESTAMP FROM DUAL;

-- 字元串轉時間

SELECT TO_DATE('1970-01-01 01:00:00','SYYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- 拼接當天第一秒和當天最後一秒

SELECT TO_DATE(TO_CHAR(SYSDATE,'yyyy-mm-dd') || ' 00:00:01','yyyy-mm-dd hh24:mi:ss') FROM DUAL; -- 當天第一秒
SELECT TO_DATE(TO_CHAR(SYSDATE,'yyyy-mm-dd') || ' 23:59:59','yyyy-mm-dd hh24:mi:ss') FROM DUAL; -- 當天最後一秒

-- 查詢系統資料庫時區

SELECT DBTIMEZONE FROM DUAL;

-- 修改系統資料庫時區為 +8:00

ALTER DATABASE SET TIME_ZONE='+8:00';

-- 查看session所屬時區

SELECT SESSIONTIMEZONE FROM DUAL;

-- 修改session所屬時區

ALTER SESSION SET TIME_ZONE='+8:00'; --原本為-UTC

系統

-- 查詢資料庫版本

SELECT * FROM V$VERSION;

-- 系統游標修改

ALTER SYSTEM SET OPEN_CURSORS =2000 SCOPE=BOTH; --改為2000

-- 查詢當前SID

SELECT NAME FROM V$DATABASE;

-- 查看連接數

SELECT COUNT(*) FROM V$PROCESS;

-- 查看最大連接數

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'processes';

-- 修改最大連接數 sqlplus 需要重啟

ALTER SYSTEM SET PROCESSES = 650 SCOPE = SPFILE;

用戶

-- 查詢用戶信息

SELECT * FROM DBA_USERS;
SELECT * FROM DBA_USERS WHERE ACCOUNT_STATUS='OPEN';
SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS='OPEN';

-- 查看當前登錄用戶

-- SHOW USER;

-- 創建用戶

CREATE USER USERNAME IDENTIFIED BY USERPASSWORD;

-- 用戶授權

GRANT CONNECT,RESOURCE,DBA TO USERNAME; --連接許可權，資源訪問許可權，DBA許可權

-- 存儲過程授權

GRANT CREATE ANY PROCEDURE TO USERNAME; --創建許可權
GRANT EXECUTE ANY PROCEDURE TO USERNAME; --執行許可權
GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO USERNAME; --DEBUG許可權
GRANT SELECT ON SYS.V_$SESSION TO USERNAME; --對單一用戶打開
GRANT SELECT ON SYS.V_$SESSTAT TO USERNAME; --對單一用戶打開
GRANT SELECT ON SYS.V_$STATNAME TO USERNAME; --對單一用戶打開
GRANT SELECT ANY DICTIONARY TO PRACTICE; --不採用
GRANT SELECT ON V$SESSION TO PUBLIC; --對所有用戶打開，所有用戶生效
GRANT SELECT ON V$SESSTAT TO PUBLIC; --對所有用戶打開，所有用戶生效
GRANT SELECT ON V$STATNAME TO PUBLIC; --對所有用戶打開，所有用戶生效
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='USERNAME'; --查詢角色許可權
SELECT * FROM ROLE_SYS_PRIVS; ----系統許可權
SELECT * FROM ROLE_TAB_PRIVS; --對象許可權
SELECT * FROM ROLE_ROLE_PRIVS; --角色許可權

-- 撤銷用戶授權

REVOKE CONNECT,RESOURCE,DBA FROM USERNAME;

-- 用戶空間分配

ALTER USER USERNAME QUOTA UNLIMITED ON USERS; --不做用戶空間資源限制
ALTER USER USERNAME QUOTA 300M ON USERS; --限制用戶最大空間資源為300M

-- 查詢用戶密碼過期策略

SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

-- 修改用戶密碼過期策略

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; --設置密碼永不過期

-- 查詢用戶密碼失敗策略

SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS';

-- 修改用戶密碼失敗策略

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED; --不限制認證失敗次數
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 30; --30累計認證失敗鎖定賬戶

-- 查詢 CONNECT_TIME 所在資源文件名

SELECT RESOURCE_NAME,PROFILE FROM DBA_PROFILES;

-- 修改資源限制

ALTER PROFILE DEFAULT LIMIT CONNECT_TIME UNLIMITED; --不限制連接超時
ALTER PROFILE DEFAULT LIMIT CONNECT_TIME 60; --限制連接60分鐘超時
ALTER PROFILE DEFAULT LIMIT IDLE_TIME UNLIMITED; --不限制會話超時
ALTER PROFILE DEFAULT LIMIT IDLE_TIME 20; --限制會話最長連續空閑時間為20分鐘

-- 查詢資源限制值

SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT';

-- 查詢用戶使用PROFILE文件

SELECT USERNAME,PROFILE FROM DBA_USERS;

-- 查詢用戶資源使用監控是否開啟

SELECT NAME,VALUE FROM GV$PARAMETER WHERE NAME='resource_limit';

-- 開啟用戶資源使用監控

ALTER SYSTEM SET resource_limit=TRUE;

存儲過程

-- 創建存儲過程

CREATE OR REPLACE PROCEDURE PROCNAME
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('STORED PROCEDURE PROCNAME'); --控制台輸出
END;

-- 查看存儲過程

SELECT TEXT FROM USER_SOURCE WHERE NAME = 'PROCNAME' ORDER BY LINE;

-- 調用存儲過程

CALL PROCNAME();
或
BEGIN
    PROCNAME;
END;

-- 刪除存儲過程

DROP PROCEDURE PROCNAME;

視圖

-- 創建視圖

CREATE VIEW VIEW_NAME AS
    SELECT SYSDATE FROM DUAL
WITH READ ONLY;

-- 查看視圖

SELECT * FROM VIEW_NAME;

-- 視圖授權

GRANT SELECT ON VIEW_NAME TO USERNAME;

-- 撤銷視圖授權

REVOKE SELECT ON VIEW_NAME FROM USERNAME;

-- 刪除視圖

DROP VIEW USERNAME;

外部ORACLE連接

-- 創建一個外部ORACLE連接

create database link dblink_name
  connect to USERNAME identified BY "USERPASSWORD"
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = "IPADDR")(PORT = "PORT"))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = "ORCL")
    )
  )';

-- 查詢資料庫相關連接

SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='DATABASE LINK';
SELECT * FROM DBA_DB_LINKS;

-- 刪除資料庫連接

DROP DATABASE LINK dblink_name;

-- 從外部ORACLE連接查詢數據

SELECT * FROM USERNAME.TABLENAME@dblink_name;
SELECT * FROM TABLENAME@dblink_name;