oracle数据库维护常用视图

select sid,
       v$session.username 用户名,
       last_call_et       持续时间,
       status             状态,
       LOCKWAIT           等待锁,
       machine            用户电脑名,
       logon_time         开始登入时间,
       sql_fulltext
  from v$session, v$process, v$sqlarea
 where paddr = addr
   and sql_hash_value = hash_value
   and status = 'ACTIVE'
   and v$session.username is not null
 order by last_call_et desc;

select b.file_id 文件ID号,
       b.tablespace_name 表空间名,
       b.bytes / 1024 / 1024 || 'M' 字节数,
       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 || 'M' 已使用,
       sum(nvl(a.bytes, 0)) / 1024 / 1024 || 'M' 剩余空间,
       100 - sum(nvl(a.bytes, 0)) / (b.bytes) * 100 占用百分比
  from dba_free_space a, dba_data_files b
 where a.file_id = b.file_id
 group by b.tablespace_name, b.file_id, b.bytes
 order by b.file_id;

select * from dba_data_files;
select 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;

select b.username, b.sid, b.serial#, logon_time
  from v$locked_object a, v$session b
 where a.session_id = b.sid
 order by b.logon_time;

SELECT s.sid,
       s.serial#,
       s.username,
       s.schemaname,
       s.osuser,
       s.process,
       s.machine,
       s.terminal,
       s.logon_time,
       l.type
  FROM v$session s, v$lock l
 WHERE s.sid = l.sid
   AND s.username IS NOT NULL
 ORDER BY sid;

---sql锁货
select sid,
       v$session.username 用户名,
       last_call_et       持续时间,
       status             状态,
       LOCKWAIT           等待锁,
       machine            用户电脑名,
       logon_time         开始登入时间,
       sql_fulltext
  from v$session, v$process, v$sqlarea
 where paddr = addr
   and sql_hash_value = hash_value
   and status = 'ACTIVE'
   and v$session.username is not null
 order by last_call_et desc;
 
select 'alter system kill session ''' || c.SID || ',' || c.SERIAL# || ''';'
  from v$session c
 where c.SID in (select a.SESSION_ID
                   from v$locked_object a
                  where a.SESSION_ID = c.SID);
                  
--应用锁货
select sess.sid,
       sess.serial#,
       lo.oracle_username,
       lo.os_user_name,
       ao.object_name,
       lo.locked_mode
  from v$locked_object lo, dba_objects ao, v$session sess
 where ao.object_id = lo.object_id
   and lo.session_id = sess.sid;
--
select 'alter system kill session ''' || sess.sid || ',' || sess.serial# ||
       ''';'
  from v$session sess
 where sess.SID in (select lo.SESSION_ID
                      from v$locked_object lo
                     where lo.SESSION_ID = sess.SID);
--杀死进程(强行停止正在运行的job)
alter system kill session '498,16887';
alter system kill session '516,3094';

select NAME, DESCRIPTION
  from v$system_parameter
 where ISSYS_MODIFIABLE = 'FALSE';

alter system kill session '465,11191';
alter system kill session '475,19138';
alter system kill session '510,21110';
alter system kill session '516,3040';

select * from user_jobs;

select object_name,object_type,status from all_objects where  owner='';

Select * from v$access where object='PKG_COMMISSION_CONTROL';
select sid,serial#,status,process from v$session where sid='480';

select spid,osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=480;

SELECT s.username,
       l.OBJECT_ID,
       l.SESSION_ID,
       s.SERIAL#,
       l.ORACLE_USERNAME,
       l.OS_USER_NAME,
       l.PROCESS,
       'alter system kill session (' || l.SESSION_ID || ',' || s.SERIAL# || ');'
  FROM V$LOCKED_OBJECT l, V$SESSION S
 WHERE l.SESSION_ID = S.SID;

select sid,
       v$session.username 用户名,
       last_call_et       持续时间,
       status             状态,
       LOCKWAIT           等待锁,
       machine            用户电脑名,
       logon_time         开始登入时间,
       sql_fulltext
  from v$session, v$process, v$sqlarea
 where paddr = addr
   and sql_hash_value = hash_value
   and status = 'ACTIVE'
   and v$session.username is not null
 order by last_call_et desc;