oracle12c 常见视图,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;