oracle常用语句

1.查询表空间的使用情况，以M为单位
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",  
round((f.free/a.total)*100) "% Free"   
from  
(select tablespace_name, sum(bytes/(1024*1024)) total   
       from dba_data_files group by tablespace_name) a,  
(select tablespace_name, round(sum(bytes/(1024*1024))) used   
       from dba_extents group by tablespace_name) u,  
(select tablespace_name, round(sum(bytes/(1024*1024))) free   
       from dba_free_space group by tablespace_name) f  
WHERE a.tablespace_name = f.tablespace_name  
and a.tablespace_name = u.tablespace_name;
2.查询新建用户
select username
  from dba_users
where username not in
      ('TEXT', 'RMAN_USER', 'TEST', 'SCOTT', 'TSMSYS', 'MDDATA', 'DIP',
        'DBSNMP', 'SYSMAN', 'MDSYS', 'ORDSYS', 'EXFSYS', 'DMSYS', 'WMSYS',
        'CTXSYS', 'ANONYMOUS', 'XDB', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
        'OLAPSYS', 'MGMT_VIEW', 'SYS', 'SYSTEM', 'OUTLN');
3、查询那些用户，操纵了那些表造成了锁机
SELECT  s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
from v$session s,v$lock l,all_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null
其中 TM  为表锁定  TX 为行锁定
4、看锁阻塞的方法是
SELECT (select username from v$session WHERE sid=a.sid) blocker,
      a.sid,
      'is blocking',
      (select username from v$session WHERE sid=b.s