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' ;