oracle笔记整理16——表空间利用率、锁表、锁包、dbms_job操作

1.表空间使用率查询语句

select * from (select a.TABLESPACE_NAME Tablespace_Name,
                               sum(a.bytes / 1024 / 1024) total_size,
                               sum(nvl(b.free_space1 / 1024 / 1024, 0)) nouse_space,
                               sum(a.bytes / 1024 / 1024) -
                               sum(nvl(b.free_space1 / 1024 / 1024, 0)) used_space,
                               round((sum(a.bytes / 1024 / 1024) -
                                     sum(nvl(b.free_space1 / 1024 / 1024, 0))) * 100 /
                                     sum(a.bytes / 1024 / 1024),
                                     2) used_pres
                          from dba_data_files a,
                               (select sum(nvl(bytes, 0)) free_space1, file_id
                                  from dba_free_space
                                 group by file_id) b
                         where a.file_id = b.file_id(+)
                               and a.tablespace_name not in
                                       (select value
                                          from v$spparameter
                                         where name = 'undo_tablespace')
                         group by a.TABLESPACE_NAME)
--------------------
union all
select tablespace_name,
                               (select sum(bytes_used) / 1024 / 1024
                                  from v$temp_space_header
                                 where tablespace_name = a.tablespace_name) total_size,
                               (select sum(bytes_used) / 1024 / 1024
                                  from v$temp_space_header
                                 where tablespace_name = a.tablespace_name) -
                               nvl((select sum(su.blocks *
                                              to_number(rtrim(p.value))) / 1024 / 1024 as Space
                                     from v$sort_usage su, v$parameter p
                                    where p.name = 'db_block_size'
                                      and su.TABLESPACE = a.tablespace_name),
                                   0) nouse_space,
                               nvl((select sum(su.blocks *
                                              to_number(rtrim(p.value))) / 1024 / 1024 as Space
                                     from v$sort_usage su, v$parameter p
                                    where p.name = 'db_block_size'
                                      and su.TABLESPACE = a.tablespace_name),
                                   0) used_space,
                               round(nvl((select sum(su.blocks *
                                                     to_number(rtrim(p.value))) / 1024 / 1024 as Space
                                            from v$sort_usage su,
                                                 v$parameter  p
                                           where p.name = 'db_block_size'
                                             and su.TABLESPACE =
                                                 a.tablespace_name) /
                                         (select sum(bytes_used) / 1024 / 1024
                                            from v$temp_space_header
                                           where tablespace_name =
                                                 a.tablespace_name) * 100,
                                         0),
                                     2) used_pres
                          from (select distinct tablespace_name
                                  from v$temp_space_header) a
union all
-------------------------
select a.tablespace_name,
                               total_undo total_size,
                               total_undo - used_undo nouse_space,
                               used_undo used_space,
                               trunc(used_undo / total_undo * 100, 2) used_pres
                          from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo,
                                       tablespace_name
                                  from dba_undo_extents
                                 where status = 'ACTIVE'
                                 group by tablespace_name) a,
                               (select tablespace_name,
                                       sum(bytes / 1024 / 1024) total_undo
                                  from dba_data_files
                                 where tablespace_name in
                                       (select value
                                          from v$spparameter
                                         where name = 'undo_tablespace')
                                 group by tablespace_name) b
                         where a.tablespace_name = b.tablespace_name;

2.锁表及解锁

/*查询数据库锁情况*/
select /*+ RULE */
 ls.osuser os_user_name,
 ls.username user_name,
 decode(ls.type,
        'RW',
        'Row wait enqueue lock',
        'TM',
        'DML enqueue lock',
        'TX',
        'Transaction enqueue lock',
        'UL',
        'User supplied lock') lock_type,
 o.object_name object,
 decode(ls.lmode,
        1,
        null,
        2,
        'Row Share',
        3,
        'Row Exclusive',
        4,
        'Share',
        5,
        'Share Row Exclusive',
        6,
        'Exclusive',
        null) lock_mode,
 o.owner,
 ls.sid,
 ls.serial# serial_num,
 ls.id1,
 ls.id2
  from sys.dba_objects o,
       (select s.osuser,
               s.username,
               l.type,
               l.lmode,
               s.sid,
               s.serial#,
               l.id1,
               l.id2
          from v$session s, v$lock l
         where s.sid = l.sid) ls
 where o.object_id = ls.id1
   and o.owner <> 'SYS'
 order by o.owner, o.object_name ;
/*解锁*/
alter system kill session 'sid,serial';

3.锁包及解锁

数据库端
select B.SID,B.SERIAL#
  from dba_ddl_locks a, v$session b
 where a.session_id = b.SID
   and a.name = 'PKG_ZBJK';
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

--服务器端
select spid, osuser, s.PROGRAM
  from v$session s, v$process p
 where s.PADDR = p.ADDR
   and s.SID = '2935';
kill -9 SPID;

4.dbms_job操作

--创建job
DECLARE
  job NUMBER;
BEGIN
  sys.dbms_job.submit(job,
                      'data_chk.main_chk;',
                      sysdate,
                      'TRUNC(SYSDATE)+1');
  COMMIT;
END;

select * from user_jobs u where u.JOB = '1907';

--修改next_date
begin
  dbms_job.next_date(1907, TRUNC(SYSDATE)+1);
end;

--查看正在运行的job
select * from dba_jobs_running;

--停止job
begin
  dbms_job.broken(1907, true);
end;

--查找已经被停止的job 的sid和serial#
select b.SID,b.SERIAL# from v$process a,v$session b where a.ADDR = b.PADDR and b.SID in (select sid from dba_jobs_running);

--杀掉已经被停止的job 的进程
alter system kill session '2277,40421';

--删除job
begin
  dbms_job.remove(1906);
end;