Oracle 常用命令大全（持续更新）

2023-08-08 16:47:58

数据库

----数据库启动 & 关闭

启动数据库

SQL> startup nomount;

SQL> alter database mount;

SQL> alter database open;

关闭数据库

SQL> shutdown immediate；

更多内容请参考：Oracle数据库启动和关闭

----连接数据库

登陆普通用户

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

例：

SQL>sqlplus scott/tiger； //登陆普通用户 scott

登陆sys帐户

SQL>sqlplus / as sysdba； //登陆 sys 帐户

SQL>sqlplus sys as sysdba；//登陆 sys 帐户

说明：

sys ：系统管理员，拥有最高权限

system ：本地管理员，次高权限

scott ：普通用户，密码默认为tiger，默认未解锁

----查看数据库名

SELECT NAME FROM V$DATABASE;

----查看实例

Linux下查看oracle上已启动实例

$ ps -ef|grep smon

查看当前启动的实例

SQL>select * from global_name;

查看默认实例

$ echo $ORACLE_SID

切换实例

$ export $ORACLE_SID=实例名

用户

----创建用户：create user 用户名 identified by 密码;

例：

create user user1 identified by 123;

----重置密码：alter user 用户名 identified by 密码;

例：

alter user user1 identified by 456;

----账户解锁：alter user 用户名 account unlock;

例：

alter user user1 account unlock;

----账号赋权：

grant create session to zhangsan； //授予zhangsan用户创建session的权限，即登陆权限

grant unlimited session to zhangsan；//授予zhangsan用户使用表空间的权限

grant create table to zhangsan；//授予创建表的权限

grante drop table to zhangsan； //授予删除表的权限

grant insert table to zhangsan；//插入表的权限

grant update table to zhangsan；//修改表的权限

grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限

grant all to public； //这条比较重要，授予所有权限(all)给所有用户(public)

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

grant select on tablename to zhangsan；//授予zhangsan用户查看指定表的权限

grant drop on tablename to zhangsan；//授予删除表的权限

grant insert on tablename to zhangsan；//授予插入的权限

grant update on tablename to zhangsan；//授予修改指定表的权限

grant insert(id) on tablename to zhangsan；//授予对指定表特定字段的插入权限

grant update(id) on tablename to zhangsan;//授予对指定表特定字段的修改权限

grant role1 to test;//授予角色role1（role1为已存在的角色）

----账户撤销权限

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

----删除用户：drop user 用户名称 cascade;

例：

drop user zhangsan cascade;

----查询所有用户（DBA账号执行）：

select * from all_users;

----查看当前用户连接：

select * from v$Session;

----查看用户角色：

SELECT * FROM USER_ROLE_PRIVS;

----查看当前用户权限：

select * from session_privs;

select * from user_sys_privs；//查看当前用户所有权限

select * from user_tab_privs；//查看所用用户对表的权限

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

SELECT * FROM DBA_ROLE_PRIVS;

----创建用户并指定表空间

create user 用户名 identified by 密码

default tablespace 默认表空间名

temporary tablespace 默认临时表空间名;

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

select username,default_tablespace from user_users;

视图

----创建视图

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说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

例：基于EMP表和DEPT表创建视图

CREATE OR REPLACE VIEW EMPDETAIL AS

SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME

FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO

WITH READ ONLY

----删除视图

DROP VIEW schema_name.view_name

[CASCADE CONSTRAINT];

参数说明：

* schema_name ：指定包含视图的模式的名称。如果跳过模式名称，则Oracle假定该视图位于当前用户模式中。

* view_name：指定要删除的视图的名称。如果一个视图被其他视图，物化视图或同义词所引用，Oracle将把这些对象标记为INVALID，但不会将其移除。

* CASCADE CONSTRAINT ：如果视图有任何约束，则必须指定CASCADE CONSTRAINT子句以删除引用视图中的主键和唯一键的所有参照完整性约束。如果不这样做，存在这样的约束时，DROP VIEW语句将会失败。

角色

----创建角色：create role 角色名；

例：

create role myrole；

----给角色赋权：

例：

grant select on table1 to role1;//给角色 role1 赋予查询表 table1 的权限

----将角色赋予某用户：grant 角色名 to 用户名;

例：

grant role1 to zhangsan;//role1为已存在的角色

----删除角色：drop role 角色名；

例：

drop role myrole；

----查看角色权限：

例：

select * from dba_role_privs  where grantee ='role1'

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

表空间

----创建表空间

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

----删除表空间

drop tablespace 表空间名 ;

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

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;

----查看表空间的datafile

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

----表空间扩容

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

ALTER TABLESPACE 表空间名 ADD DATAFILE '/oradata/.../数据文件名.dbf'

SIZE 4000M AUTOEXTEND ON NEXT 100M;

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

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

例：

alter database datafile  179 resize 8194304000;

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

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

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

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

----查看 temp tablespace（临时表空间）

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

----查看 temp tablespace 的 datafile

select * from dba_temp_files

----添加temp tablespace的datafile

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

表

----创建新表

直接创建

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

根据已有旧表创建

create table tab_new like tab_old //创建与旧表结构完全一致的新表

create table tab_new as select col1,col2… from tab_old definition only//选择旧表的某些字段创建新表

----表结构

修改表结构

* 增加一列

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;

----查询表数据

select * from tabname （where 字句）;

----表数据

插入数据

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

更新数据

update table1 set field1=value1 where 条件;

删除表中数据：

delete from table_name where子句；//删除筛选数据

 truncate table table_name；//删除表中所有数据

----删除表

drop table table_name；

----索引

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

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='列名';

系统监控

----查询正在执行的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

----锁（lock）

查看当前被锁对象

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#;

查看死锁语句

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

或

select * from v$lock where block=1

kill死锁

alter system kill session 'SID,serial#';//实际操作时，将SID和serial#替换成实际数值

例：

ALTER SYSTEM KILL SESSION '2210,2769';

补：操作系统级别杀掉进程：

#top

#kill -9 SPID

查询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;

查询锁住的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

查询等待事件

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

根据 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 = xx

    or p.spid = yy

根据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;

根据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 = xx

查询执行过的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 '2020-06-01/09:00:00' and

       '2020-06-30/15:39:00'

 order by b.FIRST_LOAD_TIME desc;

查看正在执行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

查看占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

----监听

查看监听状态

lsnrctl status

启动监听器

lsnrctl start

关闭监听器

lsnrctl stop

----防火墙

查看防火墙状态

#firewall-cmd --state

关闭防火墙

#systemctl stop firewalld.service

启动防火墙

#systemctl start firewalld.service

码农公寓

数据库