一篇文章深入了解Oracle常用命令

1. 数据库

1.1. 数据库启动 & 关闭

–启动数据库

SQL> startup nomount; 
SQL> alter database mount; 
SQL> alter database open;

–关闭数据库

SQL> shutdown immediate；

1.2. 连接数据库

–登录普通用户
SQL>sqlplus 用户名/密码@实例名–登陆普通用户，@实例名可省略

–登陆普通用户 scott

SQL>sqlplus scott/tiger； 

–登陆sys帐户

SQL>sqlplus / as sysdba；
SQL>sqlplus sys as sysdba；

说明：

sys ：系统管理员，拥有最高权限
system ：本地管理员，次高权限

1.3. 查看数据库名

SELECT NAME FROM V$DATABASE;

1.4. 查看实例

select * from global_name;

2. 用户

2.1. 创建用户

create user 用户名 identified by 密码;

–创建用户user1，密码为123

create user user1 identified by 123;

2.2. 重置密码

alter user 用户名 identified by 密码;

–重置用户user1的密码为456
alter user user1 identified by 456;

2.3. 账号解锁

alter user 用户名 account unlock;

–解锁user1账号

alter user user1 account unlock;

2.4. 账号赋权

–授予scott用户创建session权限，即登录权限

grant create session to scott；

–授予scott用户使用表空间的权限

grant unlimited session to scott；

–授予创建表的权限

grant create table to scott；

–授予删除表的权限

grante drop table to scott；

–授予插入表的权限

grant insert table to scott；

–授予修改表的权限

grant update table to scott；

–授予scott用户alert任意表的权限

grant alert all table to scott;

–授予所有权限(all)给所有用户(public)

grant all to public； 

oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权。

–授予scott用户查看指定表的权限

grant select on tablename to scott；

–授予指定表的删除表权限

grant drop on tablename to scott；

–授予指定表的插入权限

grant insert on tablename to scott；

–授予修改指定表的权限

grant update on tablename to scott；

–授予对指定表特定字段的插入权限

grant insert(id) on tablename to scott；

–授予对指定表特定字段的修改权限

grant update(id) on tablename to scott;

–授予角色role1（role1为已存在的角色）

grant role1 to test;

2.5. 账号撤销权限

基本语法同 grant，关键字为 revoke

2.6. 删除用户

drop user 用户名称 cascade;

–删除scott用户

drop user scott cascade;

2.7. 查询所有用户（DBA账号执行）

select * from all_users;

2.8. 查看当前用户连接

select * from v$Session;

2.9. 查看用户角色

SELECT * FROM USER_ROLE_PRIVS;

2.10.角色授权

--GRANT 数据库角色 TO 用户
grant connect to jack;--授权connect角色（必须）
grant resource to jack;--授予resource角色
grant dba to jack; -- 授予管理员dba角色 


--GRANT 用户的系统权限 to 用户
grant create session to jack; -- 授予用户登录数据库的权限



-- 授予用户操作表空间的权限：
grant unlimited tablespace to jack;  -- 授予用户无限制的操作表空间的权限
grant create tablespace to jack;
grant alter tablespace to jack;
grant drop tablespace to jack;
grant manage tablespace to jack;



-- 授予用户操作表的权限：
grant create table to jack; (包含有create index权限, alter table, drop table权限)

-- 授予用户操作视图的权限:
grant create view to jack; (包含有alter view, drop view权限)

-- 授予用户操作触发器的权限：
grant create trigger to jack; (包含有alter trigger, drop trigger权限)

-- 授予用户操作存储过程的权限：
grant create procedure to jack;(包含有alter procedure, drop procedure 和function 以及 package权限)

-- 授予用户操作序列的权限：
grant create sequence to jack; (包含有创建、修改、删除以及选择序列)

-- 授予用户回退段权限：
grant create rollback segment to jack;grant alter rollback segment to jack;

-- 授予用户同义词权限：
grant create synonym to jack;(包含drop synonym权限)
grant create public synonym to jack;
grant drop public synonym to jack;


-- 授予用户关于用户的权限：
grant create user to jack;
grant alter user to jack;
grant become user to jack;
grant drop user to jack;

-- 授予用户关于角色的权限：
grant create role to jack;

-- 授予用户操作概要文件的权限
grant create profile to jack;
grant alter profile to jack;
grant drop profile to jack;

-- 允许从sys用户所拥有的数据字典表中进行选择
grant select any dictionary to jack;

 --GRANT 用户的对象权限 on 对象 TO 用户
grant select, insert, update, delete on JSQUSER to STUDENT;

--登陆scott用户把emp表的全部操作权限授予jack用户
grant all on scott.emp to jack;

2.11. 查看用户的权限和角色

（一）、查看用户

show user; //查看当前用户名

1.查看所有用户：

select * from dba_users;select * from all_users;
select * from user_users;    //查看当前用户 

（二）、查看角色

1.当前用户被激活的全部角色

select * from session_roles;

2.当前当前用户被授予的角色

select * from user_role_privs;

3.全部用户被授予的角色

select * from dba_role_privs;

4.查看某个用户所拥有的角色

select * from dba_role_privs where grantee='用户名';

5.一个角色包含的系统权限

select * from dba_sys_privs where grantee='角色名'  select * from dba_sya_privs where grantee='COONNECT';

connect要大写
或者

select * from role_sys_privs where role='角色名'  select * from role_sys_privs where grantee='COONNECT'; 

connect要大写
6.一个角色包含的对象权限

select * from dba_tab_privs where grantee='角色名' 

7.查看所有角色

select * from dba_roles; 

（三）、查看权限

1.基本权限查询：

select * from session_privs; --当前用户所拥有的全部权限
select * from user_sys_privs;--当前用户的系统权限
select * from user_tab_privs;--当前用户的对象权限
select * from dba_sys_privs ;--查询某个用户所拥有的系统权限
select * from role_sys_privs;--查看角色(只能查看登陆用户拥有的角色)所包含的权限

2. 查看用户的系统权限(直接赋值给用户或角色的系统权限)

select * from dba_sys_privs;select * from user_sys_privs;

3.查看用户的对象权限：

select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

4.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from v$pwfile_users; 

补充
1、以下语句可以查看Oracle提供的系统权限

select name from sys.system_privilege_map

2、查看一个用户的所有系统权限(包含角色的系统权限)

select privilege from dba_sys_privs where grantee='SCOTT'  
union  
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SCOTT' ); 

3、 查询当前用户可以访问的所有数据字典视图。

select * from dict where comments like '%grant%';   

4、显示当前数据库的全称

select * from global_name;

2.12. 查看所有用户拥有的角色（DBA账号执行）

SELECT * FROM DBA_ROLE_PRIVS;

2.13. 创建用户并指定表空间

create user 用户名 identified by 密码 
default tablespace 默认表空间名 
temporary tablespace 默认临时表空间名;

2.14. 查看当前用户的缺省表空间

select username,default_tablespace from user_users;

3. 视图

3.1. 创建视图

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name 
 [(alias[, alias]...)] 
AS subquery 
 [WITH CHECK OPTION [CONSTRAINT constraint]] 
 [WITH READ ONLY]

参数说明：

OR REPLACE ：若所创建的试图已经存在，则替换旧视图；

FORCE：不管基表是否存在ORACLE都会自动创建该视图(即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用)；

NOFORCE ：如果基表不存在，无法创建视图，该项是默认选项(只有基表都存在ORACLE才会创建该视图)。

alias：为视图产生的列定义的别名；

subquery ：一条完整的SELECT语句，可以在该语句中定义别名；

WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；

WITH READ ONLY ：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制(比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作)，WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

3.2. 删除视图

DROP VIEW schema_name.view_name
[CASCADE CONSTRAINT];

参数说明 ：

schema_name ：指定包含视图的模式的名称。如果跳过模式名称，则Oracle假定该视图位于当前用户模式中。
view_name：指定要删除的视图的名称。如果一个视图被其他视图，物化视图或同义词所引用，Oracle将把这些对象标记为INVALID，但不会将其移除。
CASCADE CONSTRAINT ：如果视图有任何约束，则必须指定CASCADE CONSTRAINT子句以删除引用视图中的主键和唯一键的所有参照完整性约束。如果不这样做，存在这样的约束时，DROP VIEW语句将会失败。

4. 角色

4.1. 创建角色

create role 角色名；
create role myrole；

4.2. 给角色赋权

–给角色 role1 赋予查询表 table1 的权限

grant select on table1 to role1;

4.3. 将角色赋予某用户

grant 角色名 to 用户名;

–将角色role1赋予用户scott

grant role1 to scott;

4.4. 删除角色

drop role 角色名；
drop role myrole；

4.5. 查看角色权限

select * from dba_role_privs where grantee ='role1'
select * from dba_role_privs where grantee like'%role1%'

5. 表空间

5.1. 创建表空间

create tablespace 表空间名 datafile '/oracle/.../数据文件名.dbf' size 100m autoextend on next 100m maxsize 400m extent management local;

5.2. 删除表空间

drop tablespace 表空间名;

5.3. 查看数据库的表空间使用状态

select a.tablespace_name tnm,b.FILE_PATH,--b.autoextensible,
       b.cnt,
       trunc(a.bytes/1024/1024/1024) total_G,
       trunc(a.bytes/1024/1024/1024/b.cnt) avg_G,
       trunc(c.bytes/1024/1024/1024) free_G,
       trunc((a.bytes-c.bytes)*100/a.bytes,2) used--,(c.bytes*100)/a.bytes "% FREE"
     from SYS.SM$TS_AVAIL A,SYS.SM$TS_FREE C,
         (select tablespace_name,substr(file_name,1,instr(file_name,'/',2)) FILE_PATH, --f.autoextensible,
         count(*) cnt 
          from dba_data_files  f group by tablespace_name,substr(file_name,1,instr(file_name,'/',2))--,autoextensible
          ) b
     WHERE  A.TABLESPACE_NAME=C.TABLESPACE_NAME(+)
          AND A.TABLESPACE_NAME=B.TABLESPACE_NAME
       --   AND A.TABLESPACE_NAME IN (select distinct tablespace_name from dba_tablespaces)
          order by  avg_g desc;

5.4. 查看表空间的datafile

select * from dba_data_files where tablespace_name like '表空间名' order by 1 desc;

5.5. 表空间扩容

5.5.1. 添加数据文件，以达扩大表空间

ALTER TABLESPACE 表空间名 ADD DATAFILE '/oradata/.../数据文件名.dbf'
SIZE 4000M AUTOEXTEND ON NEXT 100M;

5.5.2. resize 数据文件方式，以达扩大表空间

alter database datafile FILE_ID resize BYTES字段目标值;

5.6. 查看表空间使用量（全库 ）

select SUM(a.BYTES/1024/1024) "Size" from dba_segments a

5.7. 查看特定数据库的表空间使用率

select SUM(a.BYTES/1024/1024) "Size" from dba_segments a where a.owner = '数据库名';

5.8. 查看 temp tablespace（临时表空间）

select t.tablespace_name,t.contents,t.* from dba_tablespaces t

5.9. 查看 temp tablespace 的 datafile

select * from dba_temp_files

5.10. 添加temp tablespace的datafile

alter tablespace TEMP add tempfile '/oradata/.../数据文件名.dbf' size 2000m autoextend on next 100m maxsize unlimited;

6. 表

6.1. 创建新表

6.1.1. 直接创建

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

6.1.2. 根据已有旧表创建

–创建与旧表结构完全一致的新表

create table tab_new like tab_old

–选择旧表的某些字段创建新表

create table tab_new as select col1,col2… from tab_old definition only

6.2. 表结构

6.2.1. 修改表结构

–增加一列

Alter table 表名 add column 列名;

–添加主键（Create/Recreate primary）

alter table 表名 add constraint 主键名 primary key(字段名);

–删除表主键

ALTER TABLE 表名 DROP CONSTRAINT 主键名;

–添加索引（Create/Recreate indexes）

create unique index 索引名 on 表名 (字段名);

–删除索引

drop index schema.indexname;

6.2.2. 查询表数据

select * from tabname （where 字句）;

6.3. 表数据

–插入数据

insert into table1(field1,field2) values(value1,value2)

–更新数据

update table1 set field1=value1 where 条件;

–删除表满足条件的数据

delete from table_name where子句；

–删除表中所有数据

truncate table table_name；

6.4. 删除表

drop table table_name；

6.5. 索引

–根据索引名，查询表索引字段

select * from user_ind_columns where index_name='索引名';

–根据表名，查询一张表的索引

select * from user_indexes where table_name='表名';

–通过列名查询表名

select table_name from user_tab_columns where column_name='列名';

7. 系统监控

7.1. 查询正在执行的sql语句及执行该语句的用户

SELECT b.sid oracleID,
    b.username 登录Oracle用户名,
    b.serial#,
    spid 操作系统ID,
    paddr,
    sql_text 正在执行的SQL,
b.machine 计算机名 
v$sql.sql_id,
s.terminal,
s.program,
from v$process a, v$session b, v$sqlarea c 
WHERE a.addr = b.paddr
    AND b.sql_hash_value = c.hash_value; 
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT from v$session a, v$sqlarea b where a.sql_address = b.address

7.2. 锁（lock）

7.2.1. 查看当前被锁对象

SELECT l.session_id sid,
    s.serial#,
    l.locked_mode 锁模式,
    l.oracle_username 登录用户,
    l.os_user_name 登录机器用户名,
    s.machine 机器名,
    s.terminal 终端用户名,
    o.object_name 被锁对象名,
    s.logon_time 登录数据库时间 FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id
  AND l.session_id = s.sid ORDER BY sid, s.serial#;

7.2.2. 查看死锁语句

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID
select * from v$lock where block=1

7.2.3. kill死锁

–实际操作时，将SID和serial#替换成实际数值

alter system kill session 'SID,serial#';

7.2.4. 查询DML死锁会话sid

select sid,
       blocking_session,
       LOGON_TIME,
       sql_id,
       status,
       event,
       seconds_in_wait,
       state,
       BLOCKING_SESSION_STATUS
  from v$session
 where event like 'enq%'
   and state = 'WAITING'
   and BLOCKING_SESSION_STATUS = 'VALID'
select
  (select username from v$session where sid=a.sid) blocker,
         a.sid,a.id1,a.id2,
       ' is blocking ' "IS BLOCKING",
         (select username from v$session where sid=b.sid) blockee,
             b.sid
    from v$lock a, v$lock b
   where a.block = 1
     and b.request > 0
     and a.id1 = b.id1
     and a.id2 = b.id2;

7.2.5. 查询锁住的DDL对象

select d.session_id, s.SERIAL#, d.name
  from dba_ddl_locks d, v$session s
 where d.owner = 'zhangsan'
   and d.SESSION_ID = s.sid

7.2.6. 查询等待事件

select event,
       sum(decode(wait_time, 0, 0, 1)) "之前等待次数",
       sum(decode(wait_time, 0, 1, 0)) "正在等待次数",
       count(*)
  from v$session_wait
 group by event
 order by 4 desc

7.2.7. 根据 sid 查 spid 或根据 spid 查 sid

select s.sid, s.serial#, s.LOGON_TIME, s.machine, p.spid, p.terminal
  from v$session s, v$process p
 where s.paddr = p.addr
   and s.sid = xxx
    or p.spid = yyy

7.2.8. 根据sid查看具体的sql语句

select username, sql_text, machine, osuser
  from v$session a, v$sqltext_with_newlines b
 where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value) =
       b.hash_value
   and a.sid = &sid
 order by piece;

7.2.9. 根据spid查询具体的sql语句

select ss.SID,
       ss.SERIAL#,
       ss.LOGON_TIME,
       pr.SPID,
       ss.action,
       sa.SQL_FULLTEXT,
       ss.machine,
       ss.TERMINAL,
       ss.PROGRAM,
       ss.USERNAME,
       ss.STATUS,
       ss.OSUSER,
       ss.last_call_et
  from v$process pr, v$session ss, v$sqlarea sa
 where ss.status = 'ACTIVE'
   and ss.username is not null
   and pr.ADDR = ss.PADDR
   and ss.SQL_ADDRESS = sa.ADDRESS
   and ss.SQL_HASH_VALUE = sa.HASH_VALUE
   and pr.spid = xxx

7.2.10. 查询执行过的sql语句及执行该语句的用户

select a.USERNAME 登录Oracle用户名,
       a.MACHINE 计算机名,
       SQL_TEXT,
       b.FIRST_LOAD_TIME,
       b.SQL_FULLTEXT
  from v$sqlarea b, v$session a
 where a.sql_hash_value = b.hash_value
   and b.FIRST_LOAD_TIME between '2024-07-01/09:00:00' and
       '2024-07-10/15:00:00'
 order by b.FIRST_LOAD_TIME desc;

7.2.11. 查看正在执行sql的发起者的发放程序

SELECT OSUSER 电脑登录身份,
    PROGRAM 发起请求的程序,
    USERNAME 登录系统的用户名,
    SCHEMANAME,
    B.Cpu_Time 花费cpu的时间,
    STATUS,
    B.SQL_TEXT 执行的sql FROM V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
    AND A.SQL_HASH_VALUE = B.HASH_VALUE ORDER BY b.cpu_time DESC

7.2.12. 查看占io较大的正在运行的session

SELECT se.sid,
       se.serial#,
       pr.SPID,
       se.username,
       se.status,
       se.terminal,
       se.program,
       se.MODULE,
       se.sql_address,
       st.event,
       st. p1text,
       si.physical_reads,
       si.block_changes
  FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
 WHERE st.sid = se.sid
   AND st. sid = si.sid
   AND se.PADDR = pr.ADDR
   AND se.sid > 6
   AND st. wait_time = 0
   AND st.event NOT LIKE '%SQL%'
 ORDER BY physical_reads DESC