oracle恢复数据，查询锁表语句，清除缓存，数据库连接数

查询当前序列号

select sequence_name ,last_number, min_value,max_value,increment_by
from user_sequences

恢复数据

查询最近删除的操作

SELECT * FROM RECYCLEBIN   ORDER BY DROPTIME DESC;

恢复删除的表

FLASHBACK TABLE  tableName  TO BEFORE DROP;

查询锁表语句

–查看锁表和解锁语句

SELECT object_name, machine, s.sid, s.serial#, 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
FROM gv$locked_object l,
     dba_objects o,
     gv$session s
WHERE l.object_id= o.object_id
AND l.session_id = s.sid;

–查看锁表语句

select l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       s.user#,
       l.os_user_name,
       s.machine,
       s.terminal,
       a.sql_text,
       a.action
from v$sqlarea a,
     v$session s,
     v$locked_object l
where l.session_id = s.sid
  and s.prev_sql_addr = a.address
order by sid, s.serial#;

select c.sid, c.serial#, c.username, c.osuser, b.owner, b.object_name, a.locked_mode, p.spid 
  from v$locked_object a, dba_objects b, v$session c, v$process p 
 where a.object_id = b.object_id 
   and a.session_id = c.sid 
   and c.paddr = p.addr ;

–oracle解锁

alter system kill session 'sid, serial#' ;--填写对应的sid, serial#

清除缓存

ALTER SYSTEM FLUSH SHARED_POOL
ALTER SYSTEM FLUSH BUFFER_CACHE
ALTER SYSTEM FLUSH GLOBAL CONTEXT

查看oracle用户数据库连接数(查看修改数据库最大连接数等)

查看oracle用户数据库连接数

• 查询oracle的连接数: select count(*) from v$session;
• 查询oracle的并发连接数: select count(*) from v$session where status='ACTIVE';
• 查看不同用户的连接数: select username,count(username) from v$session where username is not null group by username;
• 查看所有用户: select * from all_users;
• 查看用户或角色系统权限(直接赋值给用户或角色的系统权限): select * from dba_sys_privs; select * from user_sys_privs;
• 查看角色(只能查看登陆用户拥有的角色)所包含的权限: select * from role_sys_privs;
• 查看用户对象权限: select * from dba_tab_privs; select * from all_tab_privs; select * from user_tab_privs;
• 查看所有角色: select * from dba_roles;
• 查看用户或角色所拥有的角色: select * from dba_role_privs; select * from user_role_privs;
• 查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限): select * from V$PWFILE_USERS;

查看修改数据库最大连接数

• 数据库允许的最大连接数: select value from v$parameter where name = 'processes';
• 查看数据库当前的连接数: select count(*) from v$process;
• 修改数据库允许的最大连接数:alter system set processes = 300 scope = spfile;

查看修改游标数

• 查看游标数量:select * from v$open_cursor Where user_name=''
• 查询数据库允许的最大游标数:select value from v$parameter where name = 'open_cursors'
• 按降序显示用户"SYSTEM"为每个会话打开的游标数:select o.sid, osuser, machine, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'SYSTEM' and o.sid=s.sid group by o.sid, osuser, machine order by num_curs desc;

查看oracle版本

• 查看oracle版本:select banner from sys.v_$version;