oracle 常用操作

查询死锁

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;