Oracle常用命令大全

ITPro进化论 2024-07-10

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. 查看当前用户权限

select * from session_privs;

--查看当前用户所有权限
select * from user_sys_privs;

--查看所有用户对表的权限
select * from user_tab_privs;

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

SELECT * FROM DBA_ROLE_PRIVS;

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

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

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

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

文章转载自ITPro进化论，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle常用命令大全

1. 数据库

1.1. 数据库启动 & 关闭

1.2. 连接数据库

1.3. 查看数据库名

1.4. 查看实例

2. 用户

2.1. 创建用户

2.2. 重置密码

2.3. 账号解锁

2.4. 账号赋权

2.5. 账号撤销权限

2.6. 删除用户

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

2.8. 查看当前用户连接

2.9. 查看用户角色

2.10. 查看当前用户权限

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

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

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

3. 视图

3.1. 创建视图

3.2. 删除视图

4. 角色

4.1. 创建角色

4.2. 给角色赋权

4.3. 将角色赋予某用户

4.4. 删除角色

4.5. 查看角色权限

5. 表空间

5.1. 创建表空间

5.2. 删除表空间

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

5.4. 查看表空间的datafile

5.5. 表空间扩容

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

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

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

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

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

5.9. 查看 temp tablespace 的 datafile

5.10. 添加temp tablespace的datafile

6. 表

6.1. 创建新表

6.1.1. 直接创建

6.1.2. 根据已有旧表创建

6.2. 表结构

6.2.1. 修改表结构

6.2.2. 查询表数据

6.3. 表数据

6.4. 删除表

6.5. 索引

7. 系统监控

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

7.2. 锁（lock）

7.2.1. 查看当前被锁对象

7.2.2. 查看死锁语句

7.2.3. kill死锁

7.2.4. 查询DML死锁会话sid

7.2.5. 查询锁住的DDL对象

7.2.6. 查询等待事件

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

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

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

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

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

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

评论

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