oracle增加表空间大小

第一步:查看表空间的名字及文件所在位置

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name

第二步:增大所需表空间大小

方法一:
alter database datafile '表空间位置’resize 新的尺寸

alter database datafile '\oracle\oradata\anita_2008.dbf' resize 4000m  

方法二:增加数据文件个数
alter tablespace 表空间名称add datafile ‘新的数据文件地址’ size 数据文件大小

alter tablespace ESPS_2008 add datafile '\oracle\oradata\anita_2010.dbf' size 1000m

方法三:设置表空间自动扩展。
alter database datafile ‘数据文件位置’ autoextend on next 自动扩展大小maxsize 最大扩展大小

alter database datafile '\oracle\oradata\anita_2008.dbf' autoextend on next 100m maxsize 10000m

第三步:查询表空间使用情况


SELECT TABLESPACE_NAME "表空间",
       To_char(Round(BYTES / 1024, 2), '99990.00')
       || ''           "实有",
       To_char(Round(FREE / 1024, 2), '99990.00')
       || 'G'          "现有",
       To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
       || 'G'          "使用",
       To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
       || '%'          "比例"
FROM   (SELECT A.TABLESPACE_NAME                             TABLESPACE_NAME,
               Floor(A.BYTES / ( 1024 * 1024 ))              BYTES,
               Floor(B.FREE / ( 1024 * 1024 ))               FREE,
               Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
        FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      BYTES
                FROM   DBA_DATA_FILES
                GROUP  BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      FREE
                FROM   DBA_FREE_SPACE
                GROUP  BY TABLESPACE_NAME) B
        WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)
--WHERE TABLESPACE_NAME LIKE 'name%' 
ORDER  BY Floor(10000 * USED / BYTES) DESC;

查看文件位置

SELECT TABLESPACE_NAME as "表空间",
   FILE_ID as "表空间ID",
   FILE_NAME as "文件",
   ROUND(BYTES / (1024 * 1024), 0) || 'M'  as "大小"
FROM SYS.DBA_DATA_FILES
ORDER BY TABLESPACE_NAME

会话删除

select a_s.owner,
  a_s.object_name,
  a_s.object_type,
  vn.sid,
  vn.serial#,
  vs.spid "os_pid",
  vn.process "client_pid",
  vn.username,
  vn.osuser,
  vn.machine "hostname" ,
  vn.terminal,
  vn.program,
  to_char(vn.logon_time,'yyyy-mm-dd hh24:mi:ss')"login_time",
  'alter system kill session '''||vn.sid||','||vn.serial#||''';' "oracke_kill",
  'kill -9 '|| vs.spid "os_kill"
from all_objects a_s,
  v$locked_object v_t,
  v$session vn,
  v$process vs
where a_s.object_id=v_t.object_id
and v_t.session_id =vn.sid
and vs.addr=vn.paddr
and vn.username not in('SYSMAN','SYS');