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;--查看角色(只能查看登陆用户拥有的角色)所包含的权限
- 查看用户的系统权限(直接赋值给用户或角色的系统权限)
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