oracle表空间管理总结,以后表空间问题有了这个材料基本上都可以解决,请保留
1、查看每个表空间剩余空间
select TABLESPACE_NAME, sum(bytes)/1024/1024,sum(blocks) from dba_free_space group by tablespace_name
2、查看每个表空间总的尺寸
select tablespace_name, sum(bytes) / 1048576 from dba_data_files group by tablespace_name
3、创建表空间
create tablespace "TBSLBIDW"
nologging
datafile '/lbiapp/dbs/TBSLBIDW.ora' size 1000M reuse autoextend on next 100m maxsize 5000M
extent management local segment space management auto;
reuse表示如果该文件已经存在,则清除该文件并重新创建该文件
extent management local表示所创建的表空间为本地化管理表空间
segment space management atuo表示Oracle将使用位图来管理以用数据块和空闲数据块
创建临时表空间
create temporary tablespace TBS_TEMP_01 tempfile
'/home/oracle/archive/lbidb/TBS_TEMP_01.ora' size 1000m reuse autoextend on next 10m maxsize 2000M;
注意:如果是裸设备,建立表空间的时候autoextend必须off,建立在文件系统上时可打开,可关闭
logging是只指表空间插入数据的时候必须将改变记录在ONLINE REDO LOG 里,这是系统默认的
-----创建表空间(裸设备)----临时表空间的方式一致
--使用root用户登录服务器,进入目录/dev/vx/rdsk/vgora/
--使用命令 ls 查看数据文件名称 lbi 的数据文件一般为 lbi后面加序号
4.表空间加文件
给表空间添加数据文件
alter tablespace service_core_dat add datafile
'/dev/vgdata/rlv_dat010' size 4000M,
'/dev/vgdata/rlv_dat011' size 4000M
临时表空间增加文件
alter tablespace TBS_TEMP_01 add tempfile
'/ora/oracle/oradata/LBIDBS/TBS_TEMP_02.ora' size 100M;
5.修改表空间大小
ALTER DATABASE DATAFILE '/tellin/oracle/db/oradata/LBIDBS/TBS_KR_01.ora' RESIZE 500M
修改临时表空间大小
ALTER DATABASE tempfile '/home/oracle/archive/lbidb/TBS_TEMP_01.ora ' RESIZE 5000M:
6.删除表空间及文件
drop tablespace 表空间名 including contents and datafiles;
--删除表空间内指定的文件 必须为online
alter tablespace TBS_DW_SMS drop datafile '/opt/oracle/oradata/tbs_dw_sms_02.dbf';
alter tablespace users drop datafile '/opt/oracle/oradata/eygle/users02.dbf';
--查看表空间
select * from dba_data_files where tablespace_name = 'TBS_DW_SMS'
--修改文件为offline
ALTER DATABASE datafile '/opt/oracle/oradata/tbs_dw_sms_02.dbf' offline drop;
--恢复文件 如果修改为online报错,需要及时恢复文件按
recover datafile 24;--不可以使用recover datafile '/opt/oracle/oradata/tbs_dw_sms_02.dbf';
--修改文件为online
ALTER DATABASE datafile '/opt/oracle/oradata/tbs_dw_sms_02.dbf' online;
--修改表空间的状态
alter tablespace TBS_DW_SMS online;
alter tablespace TBS_DW_SMS offline immediate;
--查看file id
select file#,status,name from v$datafile;
--将数据文件设置成自动扩展:alter database datafile '/oracle/app/oradata/ora11g/tbs_dw_maxis.dbf' autoextend on next 1M maxsize 10M;
取消自动扩展:alter database datafile '/oracle/app/oradata/ora11g/tbs_dw_maxis.dbf' autoextend off;
--移动一个表空间的数据文件:alter tablespace tas_dw_maxis offline;host move /oracle/app/oradata/ora11g/tbs_dw_maxis.dbf /oracle/tbs_dw_maxis.dbf;
alter tablespace tbs_dw_maxis rename datafile '/oracle/app/oradata/ora11g/tbs_dw_maxis.dbf'to '/oracle/tbs_dw_maxis.dbf';alter tablespace online;
--查询表空间中数据文件的位置,名称,大小,是否可以自动扩展,能够扩展到的最大大小:select tablespace_name,file_name,blocks,bytes,autoextensible, maxbyte from dba_data_files;
--查询表空间的空闲空间:select tablespace_name, sum(bytes/1024/1024) from dba_free_space group by tablespace_name;
--查看用户表、索引、分区表占用空间
select segment_name, sum(bytes)/1024/1024 Mbytes from user_segments group by segment_name;
1、表占用空间
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;
2、索引占用空间
select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX' group by segment_name;
3、分区表TABLE PARTITION占用空间
select segment_name,sum(bytes)/1024/1024 Mbytes from user_segments where segment_type='TABLE PARTITION' group by segment_name;
--查看当前临时表使用空间大小与正在占用临时表空间的sql语句
select sess.SID, segtype, blocks*8/1000 "MB" ,sql_text
from v$sort_usage sort, v$session sess,v$sql sql
WHERE sort.SESSION_ADDR = sess.SADDR
AND sql.ADDRESS = sess.SQL_ADDRESS
ORDER by blocks desc;
select'the ' || name || ' temp tablespaces ' || tablespace_name ||
' idle ' ||
round(100 - (s.tot_used_blocks / s.total_blocks) * 100, 3) ||
'% at ' || to_char(sysdate, 'yyyymmddhh24miss')
from (select d.tablespace_name tablespace_name,
nvl(sum(used_blocks), 0) tot_used_blocks,
sum(blocks) total_blocks
from v$sort_segment v, dba_temp_files d
where d.tablespace_name = v.tablespace_name(+)
GROUP by d.tablespace_name) s,
v$database;
--查询在sort排序区使用的执行耗时的SQL
SELECT SE.USERNAME,SE.SID,SU.EXTENTS,SU.BLOCKS *
TO_NUMBER(RTRIM(P.VALUE))AS SPACE,TABLESPACE,SEGTYPE,SQL_TEXT
FROM V$SORT_USAGE SU,V$PARAMETER P,V$SESSION SE,V$SQL S
WHERE P.NAME = 'db_block_size'
AND SU.SESSION_ADDR = SE.SADDR
AND S.HASH_VALUE = SU.SQLHASH
AND S.ADDRESS = SU.SQLADDR
ORDER BY SE.USERNAME,SE.SID
--查看表空间的大小:
SELECT a.tablespace_name as "表空间名称",
to_char(b.total/1024/1024,999999.99)||'M' as "总容量",
to_char((b.total-a.free)/1024/1024,'9999990D99')||'M' as "已使用容量",
to_char(a.free/1024/1024,'9999990D99')||'M' as "剩余容量",
to_char(round((total-free)/total,4)*100,'9999990D99')||'%' as "已使用百分比"
FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY round((total-free)/total,4) DESC;
--查看某个表空间大小:
select aa.tablespace_name,
trunc(aa.total_space / 1024 / 1024) || ' M' as total_space,
trunc(bb.free_space / 1024 / 1024) || ' M' as free_space,
trunc((1 - (bb.free_space / aa.total_space)) * 100, 2) as used_percent,
trunc((aa.total_space - bb.free_space) / 1024 / 1024) || ' M' as used_space,
sysdate as check_time,
(select name from v$database) as db_name
from (select a.tablespace_name, sum(a.bytes) as total_space
from dba_data_files a
where a.tablespace_name like upper('&tablespace_name') || '%'
group by a.tablespace_name) aa,
(select b.tablespace_name, sum(b.bytes) as free_space
from dba_free_space b
where b.tablespace_name like upper('&tablespace_name') || '%'
group by b.tablespace_name) bb
where aa.tablespace_name = bb.tablespace_name(+)
order by used_percent desc;