查询死锁
select sess.sid,
sess.serial#,
sql_text,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode,
SESS.machine
from v$locked_object lo, dba_objects ao, v$session sess, v$sqlarea area
where ao.object_id = lo.object_id
and lo.session_id = sess.sid
and area.address = sess.sql_address;
干掉死锁
alter system kill session '738,1429';
查看当前表空间
select username,default_tablespace from user_users;
查看表空间使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
round(a.bytes/1024/1024,2) "total M",
round(b.bytes/1024/1024,2) "used M",
round(c.bytes/1024/1024,2) "free M",
round((b.bytes * 100) / a.bytes,2) "% USED ",
round((c.bytes * 100) / a.bytes,2) "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
创建表空间（未测试）
create tablespace tablespacename
datafile 'D:\ORACLE\Administrator\oradata\orcl\zsl.dbf'size 128M
autoextend on next 2048K
maxsize 2048M
logging
extent management local autoallocate segment space management auto
flashback on
添加数据文件
alter tablespace USERS add datafile
'D:\APP\LUWEI\ORADATA\ORCL\MLK.DBF' size 128M
autoextend on next 2048K
maxsize 8192M
表空间扩容
--查看表空间路径
select * from dba_data_files;
--修改表空间大小
alter database datafile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF' resize 1024M;
删除表空间
drop tablespace tablespacename
给用户指定表空间
--给已存在的用户指定表空间
alter user zsl default tablespace zsl;
-- 新建用户的时候创建表空间
create user username identified by userpassword default tablespace userspace;
