★★怎么让Oracle表空间缩小■■ 本帖最后由 yo_yo2005 于 2011-02-18 14:41:19 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 alter database datafile 'filename' resize size; 两个办法:1 、把表空间删了,重建2、减小数据文件的大小 select file_name from dba_data_files where TABLESPACE_NAME ='tbs_name'; alter database datafile 'filename' resize size; 同意。 顺便说一下。 这些东西都能google到. 在提问之前可以尝试google一下。 把关键字写上,就能查到了。 2楼说的办法能起一部分作用alter database datafile 'filename' resize size;但是前提是,表空间里没有任何数据假设表空间靠前的碎片里有点数据,靠后的片段里有点数据,那么,这种方法是压缩不了的他只能释放前头和后头的空间 释放不了中间的下边的方法是我以前百度后整理的 ,你可以试试--删除表 释放表空间 压缩数据文件--找到数据文件对应的文件号select file#,name from v$datafile where name like '%MWS_FILE.ORA';select * from v$datafile--找到文件中最大的块号select max(block_id) from dba_extents where tablespace_name = 'MWS_FILE' and file_id=8--查看数据库块大小--cmd下执行 show parameter db_block_size--计算一下文件中最大使用块占用的位置 129是最大的块号,8192是数据库块大小select 4873*8192/1024/1024 from dual;--重设数据文件大小,只要resize的值超过上一条语句select出的数据就应该可以alter database datafile 'F:\oracle\product\10.2.0\oradata\yaocongying\UNDOTBS01.DBF' resize 100m;--如果还报03297的错误 可能是回收站没有清空 可以先清空回收站的记录再重新操作--用dba登陆,执行以下语句清空回收站purge dba_recyclebin;--查出block_id比较高的几个表select owner, segment_name, segment_type, tablespace_name, extent_id, bytes, block_id,blocks from dba_extents where file_id = 8 and block_id > '4000' --and segment_type = 'TABLE' and tablespace_name = 'MWS_FILE'--重建索引,默认为原表空间Alter INDEX MW_APP.PK_MWT_GRADEINFO REBUILD [TABLESPACE target_tablesapce_name];alter index mw_app.pk_mwt_sm_humanmsg rebuild [TABLESPACE target_tablesapce_name];alter index mw_app.pk_mwt_sm_datecontrol rebuild [TABLESPACE target_tablesapce_name];--将表移到指定表空间,默认为原表空间ALTER TABLE MW_APP.MWT_GRADEINFO MOVE [TABLESPACE target_tablesapce_name]; 以下是我自己粗略整理的关于压缩表空间的文档——适合ORACLE 10G或以上。如有错误请指出。[code=HTM]由于通过ORACLE EXP工具导出的数据库文件会压缩空间,但是释放后往往会惊人的大,从几M到几十G,造成这个原因是由于原导出数据库没有整理表空间——其中主要包括两方面,一是用户产生太多的DELETE,致使表的高位线(HWM)在很高的位置,所以尽管数据量很小,但是占据的表空间很大,二是索引没有重建,频繁的删除以及更新使得索引越来越大,REBUILD索引是个很必要的事情。一. 查看表空间占用情况语句。select a.TABLESPACE_NAME, a.total, nvl(b.used, 0) USED, nvl((b.used / a.total) * 100, 0) PCT_USED from (select TABLESPACE_NAME, sum(bytes) / (1024 * 1024) total from sys.dba_data_files group by TABLESPACE_NAME) a, (select TABLESPACE_NAME, bytes / (1024 * 1024) used from sys.SM$TS_USED) b where a.TABLESPACE_NAME = b.TABLESPACE_NAME(+);此语句需要有BDA权限的用户才能运行。二. 当前用户所有索引重建语句。declare cursor v_cur is select SEGMENT_NAME from user_segments where segment_type = 'INDEX'; v_indexname varchar2(100); v_sql varchar2(1000);begin open v_cur; loop fetch v_cur into v_indexname; v_sql := 'alter index ' || v_indexname || ' rebuild online'; dbms_output.put_line(v_indexname); execute immediate v_sql; exit when v_cur%notfound; end loop;close v_cur;end; 此索引重建必须在原库上重建才有效果,否则您将会看到,重建后,索引依然还是很忙大,例如表只有几百K,但是表中的索引居然是几百M。原因是新库的索引创建语句是根据原索引的大小而生成的。 select dbms_lob.substr(dbms_metadata.get_ddl('INDEX', v_indexname), 1000, 1) from dual; 从这里获取到的DDL索引创建语句跟原来数据库的创建语句是不一样的,差别在于索引的起始大小,用原库导到新库后,索引创建的初始大小已经改变了。如果要获得当前用户所有的索引(表,或者表空间等企图的对象,是类似的)创建语句,可以运行如下语句。declare cursor v_cur is select SEGMENT_NAME from user_segments where segment_type = 'INDEX'; -- AND SEGMENT_NAME NOT LIKE 'PK_%'; v_indexname varchar2(100); v_sql varchar2(1000); v_sqldel varchar2(1000);begin open v_cur; loop fetch v_cur into v_indexname; select dbms_lob.substr(dbms_metadata.get_ddl('INDEX', v_indexname), 1000, 1) into v_sql from dual; dbms_output.put_line(v_sql||’;’); exit when v_cur%notfound; end loop;close v_cur;end;三. 整理表的高位线(HWM)。 declare cursor v_cur is select table_name from tabs; v_tablename varchar2(100); v_sql1 varchar2(1000); v_sql2 varchar2(1000);begin open v_cur; loop fetch v_cur into v_tablename; v_sql1 := 'alter table ' || v_tablename || ' enable row movement'; v_sql2 := 'alter table ' || v_tablename || ' shrink space'; dbms_output.put_line(v_tablename); execute immediate v_sql1; execute immediate v_sql2; exit when v_cur%notfound; end loop;end;四. 表段占用数据块的情况。可以通过USER_SEGMENT表查看——里面包括了表和索引段。[/code] 自己解决了问题把10G的表空间缩小到了1G把主要的表里的数据清空 再重建表然后exp把数据导出再重建表空间再imp即可谢谢大家帮忙 求高人指点 问个问题,分不多,关于序列的。 不完全恢復出現問題,無法打開數據庫-->在線等... 怎样优化这个表? 我公司招ORACLE DBA 北京 135个字段的数据表,单纯插入很慢 oracle8.05 数据库回复问题 oracle如何调用 C/C++ DLL中的输出函数?? 时间格式有误,不知是何原因? 请问高手在oracle中有没有数据仓库的建模工具啊!! 使用sqlplus / as sysdba 连接数据库后,执行sql语句提示not logged on 求助:如何求两个日期之间差几天?
1 、把表空间删了,重建
2、减小数据文件的大小
select file_name from dba_data_files where TABLESPACE_NAME ='tbs_name'; alter database datafile 'filename' resize size;
同意。 顺便说一下。 这些东西都能google到. 在提问之前可以尝试google一下。 把关键字写上,就能查到了。
alter database datafile 'filename' resize size;
但是前提是,表空间里没有任何数据
假设表空间靠前的碎片里有点数据,靠后的片段里有点数据,那么,这种方法是压缩不了的
他只能释放前头和后头的空间 释放不了中间的
下边的方法是我以前百度后整理的 ,你可以试试
--删除表 释放表空间 压缩数据文件
--找到数据文件对应的文件号
select file#,name from v$datafile where name like '%MWS_FILE.ORA';
select * from v$datafile
--找到文件中最大的块号
select max(block_id) from dba_extents where tablespace_name = 'MWS_FILE' and file_id=8
--查看数据库块大小
--cmd下执行
show parameter db_block_size
--计算一下文件中最大使用块占用的位置 129是最大的块号,8192是数据库块大小
select 4873*8192/1024/1024 from dual;
--重设数据文件大小,只要resize的值超过上一条语句select出的数据就应该可以
alter database datafile 'F:\oracle\product\10.2.0\oradata\yaocongying\UNDOTBS01.DBF' resize 100m;
--如果还报03297的错误 可能是回收站没有清空 可以先清空回收站的记录再重新操作
--用dba登陆,执行以下语句清空回收站
purge dba_recyclebin;
--查出block_id比较高的几个表
select owner, segment_name, segment_type, tablespace_name,
extent_id, bytes, block_id,blocks
from dba_extents
where file_id = 8
and block_id > '4000'
--and segment_type = 'TABLE'
and tablespace_name = 'MWS_FILE'
--重建索引,默认为原表空间
Alter INDEX MW_APP.PK_MWT_GRADEINFO REBUILD [TABLESPACE target_tablesapce_name];
alter index mw_app.pk_mwt_sm_humanmsg rebuild [TABLESPACE target_tablesapce_name];
alter index mw_app.pk_mwt_sm_datecontrol rebuild [TABLESPACE target_tablesapce_name];--将表移到指定表空间,默认为原表空间
ALTER TABLE MW_APP.MWT_GRADEINFO MOVE [TABLESPACE target_tablesapce_name];
[code=HTM]
由于通过ORACLE EXP工具导出的数据库文件会压缩空间,但是释放后往往会惊人的大,从几M到几十G,造成这个原因是由于原导出数据库没有整理表空间——其中主要包括两方面,一是用户产生太多的DELETE,致使表的高位线(HWM)在很高的位置,所以尽管数据量很小,但是占据的表空间很大,二是索引没有重建,频繁的删除以及更新使得索引越来越大,REBUILD索引是个很必要的事情。
一. 查看表空间占用情况语句。
select a.TABLESPACE_NAME,
a.total,
nvl(b.used, 0) USED,
nvl((b.used / a.total) * 100, 0) PCT_USED
from (select TABLESPACE_NAME, sum(bytes) / (1024 * 1024) total
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME, bytes / (1024 * 1024) used
from sys.SM$TS_USED) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME(+);此语句需要有BDA权限的用户才能运行。
二. 当前用户所有索引重建语句。
declare
cursor v_cur is
select SEGMENT_NAME from user_segments where segment_type = 'INDEX';
v_indexname varchar2(100);
v_sql varchar2(1000);
begin
open v_cur;
loop
fetch v_cur
into v_indexname;
v_sql := 'alter index ' || v_indexname || ' rebuild online';
dbms_output.put_line(v_indexname);
execute immediate v_sql;
exit when v_cur%notfound;
end loop;
close v_cur;
end; 此索引重建必须在原库上重建才有效果,否则您将会看到,重建后,索引依然还是很忙大,例如表只有几百K,但是表中的索引居然是几百M。原因是新库的索引创建语句是根据原索引的大小而生成的。
select dbms_lob.substr(dbms_metadata.get_ddl('INDEX', v_indexname), 1000, 1)
from dual;
从这里获取到的DDL索引创建语句跟原来数据库的创建语句是不一样的,差别在于索引的起始大小,用原库导到新库后,索引创建的初始大小已经改变了。
如果要获得当前用户所有的索引(表,或者表空间等企图的对象,是类似的)创建语句,可以运行如下语句。declare
cursor v_cur is
select SEGMENT_NAME from user_segments where segment_type = 'INDEX';
-- AND SEGMENT_NAME NOT LIKE 'PK_%';
v_indexname varchar2(100);
v_sql varchar2(1000);
v_sqldel varchar2(1000);
begin
open v_cur;
loop
fetch v_cur
into v_indexname;
select dbms_lob.substr(dbms_metadata.get_ddl('INDEX', v_indexname),
1000,
1)
into v_sql
from dual;
dbms_output.put_line(v_sql||’;’);
exit when v_cur%notfound;
end loop;
close v_cur;
end;
三. 整理表的高位线(HWM)。
declare
cursor v_cur is
select table_name from tabs;
v_tablename varchar2(100);
v_sql1 varchar2(1000);
v_sql2 varchar2(1000);
begin
open v_cur;
loop
fetch v_cur
into v_tablename;
v_sql1 := 'alter table ' || v_tablename || ' enable row movement';
v_sql2 := 'alter table ' || v_tablename || ' shrink space';
dbms_output.put_line(v_tablename);
execute immediate v_sql1;
execute immediate v_sql2;
exit when v_cur%notfound;
end loop;
end;
四. 表段占用数据块的情况。
可以通过USER_SEGMENT表查看——里面包括了表和索引段。[/code]
把10G的表空间缩小到了1G把主要的表里的数据清空 再重建表
然后exp把数据导出
再重建表空间
再imp即可谢谢大家帮忙