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,
(selectsum(nvl(bytes, 0)) free_space1, file_id
from dba_free_space
groupby file_id) b
where a.file_id = b.file_id(+)
and a.tablespace_name notin
(selectvaluefrom v$spparameter
where name = 'undo_tablespace')
groupby a.TABLESPACE_NAME)
--------------------
unionallselect tablespace_name,
(selectsum(bytes_used) / 1024 / 1024from v$temp_space_header
where tablespace_name = a.tablespace_name) total_size,
(selectsum(bytes_used) / 1024 / 1024from v$temp_space_header
where tablespace_name = a.tablespace_name) -
nvl((selectsum(su.blocks *
to_number(rtrim(p.value))) / 1024 / 1024asSpacefrom v$sort_usage su, v$parameter p
where p.name = 'db_block_size'and su.TABLESPACE = a.tablespace_name),
0) nouse_space,
nvl((selectsum(su.blocks *
to_number(rtrim(p.value))) / 1024 / 1024asSpacefrom v$sort_usage su, v$parameter p
where p.name = 'db_block_size'and su.TABLESPACE = a.tablespace_name),
0) used_space,
round(nvl((selectsum(su.blocks *
to_number(rtrim(p.value))) / 1024 / 1024asSpacefrom v$sort_usage su,
v$parameter p
where p.name = 'db_block_size'and su.TABLESPACE =
a.tablespace_name) /
(selectsum(bytes_used) / 1024 / 1024from v$temp_space_header
where tablespace_name =
a.tablespace_name) * 100,
0),
2) used_pres
from (selectdistinct tablespace_name
from v$temp_space_header) a
unionall
-------------------------
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'groupby tablespace_name) a,
(select tablespace_name,
sum(bytes / 1024 / 1024) total_undo
from dba_data_files
where tablespace_name in
(selectvaluefrom v$spparameter
where name = 'undo_tablespace')
groupby 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'orderby 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_datebegin
dbms_job.next_date(1907, TRUNC(SYSDATE)+1);end;--查看正在运行的jobselect * from dba_jobs_running;--停止jobbegin
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';--删除jobbegin
dbms_job.remove(1906);end;