Oracle表空间常用操作

1、查出指定表空间使用空间最大的前20对象

Select OWNER || ',' || SEGMENT_NAME || ',' || SEGMENT_TYPE || ',' || total
  from (select OWNER, SEGMENT_NAME, SEGMENT_TYPE, bytes / 1024 / 1024 total
          from dba_segments
         where TABLESPACE_NAME = '表空间名'
         order by bytes / 1024 / 1024 desc)
 where rownum < 21;

2、查看用户及默认表空间

select username, default_tablespace
  from dba_users
 where username = 'JT_JUDITSPACE';


select segment_name || ',' || segment_type || ',' || TABLESPACE_NAME
  from user_segments;
select distinct TABLESPACE_NAME from user_segments;
--查看自己所有的对象是否全存储在自己的默认表空间--
alter user scott quota 1M on system;
create table tt(id int) tablespace system;
insert into tt
  select object_id from all_objects where rownum < 1000;
commit;

3、查看指定表空间文件位置

select FILE_NAME || ',' || TABLESPACE_NAME
  from dba_data_files
 where TABLESPACE_NAME = '表空间名';
select FILE_NAME || ',' || TABLESPACE_NAME
  from dba_data_files
 where TABLESPACE_NAME = 'AUDIT_TABLESPACE';

4、为表空间扩容(增加数据文件)

alter tablespace 表空间 add datafile '\指定路径\表空间名02.dbf' size 大小 AUTOEXTEND OFF;  

alter tablespace USERS add datafile '/oracle/app/oradata/ecom/users02.dbf' size 1M AUTOEXTEND OFF;  

select * from dba_data_files;
alter tablespace AUDIT_TABLESPACE add datafile '/data/oradata/dwca/AUDIT_TABLESPACE33.dbf' size 30000M  autoextend on;

5、查看指定表空间的数据文件是否自动增长

SELECT FILE_NAME || ',' || TABLESPACE_NAME || ',' || AUTOEXTENSIBLE
  FROM DBA_DATA_FILES
 where TABLESPACE_NAME = '表空间名';

SELECT FILE_NAME || ',' || TABLESPACE_NAME || ',' || AUTOEXTENSIBLE
  FROM DBA_DATA_FILES
 where TABLESPACE_NAME = 'USERS';

6、让某个表空间的数据文件不自动增加

 alter database datafile '/oracle/app/oradata/ecom/users01.dbf' autoextend off;
 alter database datafile '/oracle/app/oradata/ecom/users01.dbf' autoextend on;

7、查看表空间大小

(1)查看表空间使用情况

set pages 500
set lines 500
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 desc ;

(2)查看表空间使用情况

SELECT d.tablespace_name "TB Name",
       d.status "Status",
       TO_CHAR((a.bytes / 1024 / 1024), '99,999,990.900') "Size (M)",
       TO_CHAR(((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1024 / 1024),
               '99,999,990.900') "Used (M)",
       ((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1024 / 1024)) /
       ((a.bytes / 1024 / 1024))) * 100 "Percent USED"
  FROM sys.dba_tablespaces d, sys.sm$ts_avail a, sys.sm$ts_free f
 WHERE d.tablespace_name = a.tablespace_name
   AND f.tablespace_name(+) = d.tablespace_name
 order by "Percent USED";

(3)查看临时表空间

set lines 300

 SELECT temp_used.tablespace_name,
           used as "Used(M)",
           total as "Total(M)",
           (100 - round(nvl(total - used, 0) * 100 / total, 3))||'%' "percent Used"
      FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
              FROM GV_$TEMP_SPACE_HEADER
             GROUP BY tablespace_name) temp_used,
           (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
              FROM dba_temp_files
             GROUP BY tablespace_name) temp_total
     WHERE temp_used.tablespace_name = temp_total.tablespace_name;

8、表被锁时解锁脚本

(1)查看临时表空间查看数据库锁,诊断锁的来源及类型：

SELECT OBJECT_ID, SESSION_ID, LOCKED_MODE FROM V$LOCKED_OBJECT;

(2)找出数据库的serial#,以备杀死：

SELECT T2.USERNAME, T2.SID, T2.SERIAL#, T2.LOGON_TIME
  FROM V$LOCKED_OBJECT T1, V$SESSION T2
 WHERE T1.SESSION_ID = T2.SID
 ORDER BY T2.LOGON_TIME;

SELECT l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       l.os_user_name,
       s.machine,
       s.terminal,
       o.object_name,
       s.logon_time
  FROM v$locked_object l, all_objects o, v$session s
 WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
 ORDER BY sid, s.serial#;

select l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       s.user#,
       l.os_user_name,
       s.machine,
       s.terminal,
       a.sql_text,
       a.action
  from v$sqlarea a, v$session s, v$locked_object l
 where l.session_id = s.sid
   and s.prev_sql_addr = a.address
 order by sid, s.serial#;

(3)杀死该session

 alter system kill session '6282,32562' ;