oracle常用脚本

1. 查询锁定进程：

SELECT L.SESSION_ID, S.SERIAL#, L.ORACLE_USERNAME, L.OS_USER_NAME, S.MACHINE, S.TERMINAL, O.OBJECT_NAME
FROM V$LOCKED_OBJECT L
INNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_ID
INNER JOIN V$SESSION S ON S.SID = L.SESSION_ID;

2. 解锁进程（需替换’SESSION_ID, SERIAL#'）：

ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';

3. 查询enqueue等待事件：

SELECT b.SID, b.serial#, b.username, machine, event, wait_time, ...
FROM v$session_wait a, v$session b
WHERE a.event = 'enqueue' ...;

4. 查询表空间读写情况：

SELECT df.tablespace_name, f.phyrds, f.phywrts ...
FROM v$filestat f, dba_data_files df
WHERE f.file# = df.file_id;

5. 查看用户拥有的表、视图、索引等：

SELECT object_name FROM user_objects WHERE object_type = 'TABLE';  -- 示例为表

6. 查询用户所拥有的权限：

SELECT * FROM session_privs;

7. 修改用户密码：

ALTER USER username IDENTIFIED BY newpassword;

8. 新建用户：

CREATE USER username IDENTIFIED BY password;

9. 删除用户（级联删除）：

DROP USER username CASCADE;

10. 给用户分配权限：

GRANT CREATE SESSION, CREATE TABLE TO username;