Oracle 日常操作
查看表空间对应的数据文件
1
select tablespace_name,file_id,file_name from dba_data_files order by file_id;
查询用户和表空间的对应关系
1
select username,default_tablespace from dba_users;
查看当前数据库实例
1
show parameter name;
查看数据库目录
1
select * from dba_directories;
查询会话信息
1
select sid,serial# from v$session where username = 'AHWMS';
杀掉会话
1
alter system kill session '1283,211';
其中 1283 为
sid, 211 为serial#.查看那张表被锁
1
2
3select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;查看是哪个session引起的
1
2
3
4
5select a.OS_USER_NAME, c.owner, c.object_name, b.sid, b.serial#, logon_time
from v$locked_object a, v$session b, dba_objects c
where a.session_id = b.sid
and a.object_id = c.object_id
order by b.logon_time;杀掉进程，执行命令
alter system kill session '1025,41';。需要用户有权限操作，其中 1025 为sid, 41 为serial#.查看是否锁表以及自动生成释放表语句
1
2
3
4
5select object_name,machine,s.sid,'alter system kill session ' || '''' || s.sid || ',' || s.serial# || ''';'
from v$locked_object l,dba_objects o ,v$session s
where l.object_id = o.object_id
and l.session_id=s.sid
and object_name = 'SY_BUSINESS';查看表结构
1
2
3
4desc table_name;
# 或者
select * from user_tab_columns where table_name = '大写的表名称';oracle 如何终止存储过程的运行
- 在 v$access 视图中查看要停止进程的 SID
1
SELECT SID,OWNER,OBJECT FROM V$ACCESS WHERE OBJECT='SP_WM_MOVE_INVENTORY_CONFIRM';
- 根据查找到的 SID 在 v$session 查询中查询 SID 以及 SERIAL#
1
SELECT SID,SERIAL# FROM V$SESSION WHERE SID='1044';
- 杀掉查找出来的进程，进程都有唯一的进程id(SID)和序列号(SERIAL#),之后通过 kill 命令即可强制停止进程.
1
alter system kill session 'SID,SERIAL#' immediate;
查看全局配置文件
1
2set wrap off;
select * from dba_profiles;查看用户的配置文件
1
2
3select username, b.*
from dba_users a, dba_profiles b
where a.profile = b.profile and username='QDMWMS_PRE';修改 IDLE_TIME 配置
1
alter profile default limit idle_time 30;
查询存储过程的锁
1
2
3
4
5
6
7select 'alter system kill session '''||b.SID||','||b.SERIAL# || ''' immediate;'
from v$session b
where b.SID in (
select session_id
from dba_ddl_locks
where name = upper('SP_AC_GET_NO')
);根据PID查询Oracle执行进程的情况
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24-- 根据PID查找对应正在执行的sql语句
SELECT sql_text FROM v$sqltext a
WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b, v$process c
WHERE b.paddr = c.addr AND c.spid = 'PID')
ORDER BY piece ASC;
-- 根据PID查找正在执行的进程
SELECT s.username, s.sid, s.serial# FROM v$session s, v$process p
WHERE s.paddr = p.addr AND p.spid = 'PID';
-- 或者使用以下语句
SELECT /*+ ORDERED */ sql_text FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '10342'))
ORDER BY piece ASC
/
-- 或者
SELECT c.spid,b.sql_text, a.sid, a.serial#, osuser, machine
FROM v$session a, v$sqlarea b ,v$process c
WHERE a.sql_address = b.address and a.paddr=c.addr and spid=9975;
用户相关的操作
创建表空间
1
create bigfile tablespace ahwms datafile '/u01/app/oracle/oradata/ahwms/ahwms.dbf' size 1G autoextend on next 1G maxsize unlimited;
创建用户并指定默认表空间
1
create user ahwms identified by "xxxxxxx" default tablespace ahwms;
授权用户角色
1
grant connect,resource to ahwms;
授权用户对备份目录的读写权限
1
grant read,write on directory dump_dir to ahwms;
删除用户
1
drop user qdmwms_pre cascade;
删除表空间以及数据文件
1
drop tablespace qdmwms including contents and datafiles;
禁用用户
1
alter user ahwms account lock;
查看 Schema 相关的会话信息
1
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='QDMWMS';
用户角色权限查看
查看某用户拥有的权限
1
select * from dba_sys_privs where grantee='SCOTT';
查看某用户拥有的角色
1
select * from dba_role_privs where grantee='SCOTT';
查看一个用户所有的权限及角色
1
2
3
4select 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');查看哪些用户有 sysdba 或 sysoper 系统权限(查询时需要相应权限)
1
select * from V$PWFILE_USERS;
查看所有的角色
1
select * from dba_roles;
授予用户 DEBUG 权限
1
GRANT debug any procedure, debug connect session TO XXX;
用户对象相关
查看用户无效的对象
1
2
3col object_name for a30;
select owner,object_name,object_type,status from dba_objects where status !='VALID' and owner not in ('SYS','SYSTEM')
/编译用户对象
- view 视图
1
alter view view_name compile;
- function 函数
1
alter function function_name compile;
- procedure 存储过程
1
alter procedure procedure_name compile;
- trigger 触发器
1
alter trigger trigger_name compile;
查看编译错误
1
show errors trigger "SAL"."TG_PURCHASE_INVITE";
外键约束管理
禁用所有外键约束
1
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
启动所有外键约束
1
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
删除所有外键约束
1
select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R';
备份相关查询
查询快速恢复区
1
2
3
4
5
6
7SQL> show parameter db_recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 150G查看 FRA (Flash Recovery Area) 使用率
1
2
3
4
5
6
7
8
9
10
11
12
13SQL> set wrap off;
SQL> set linesize 1000;
SQL> select * from V$RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 19.99 0 841
BACKUP PIECE 9.48 0 8
IMAGE COPY 0 0 0
FLASHBACK LOG 30.55 .67 638
FOREIGN ARCHIVED LOG 0 0 0
清空用户表的数据
禁用所有外键约束
1
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
清空表数据库
1
2
3select 'truncate table PUR.'|| t.table_name ||';'
from user_tables t
where t.table_name not in ('HR_EMP','HR_DEPARTMENT','SY_BUSINESS','hr_login_record');启动所有外键约束
1
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';