精华内容
下载资源
问答
  • oracle删除数据释放空间
    2021-05-01 03:14:14

    Oracle 在删除数据后,因为高水位线的问题,并不会 自动释放空间。

    所有的oracle段(segments,在此,为了理解方便,建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限,我们把这个上限称为”high water mark”或HWM。这个HWM是一个标记,用来说明已经有多少没有使用的数据块分配给这个segment。HWM通常增长的幅度为一次5个数据块,原则上HWM只会增大,不会缩小,即使将表中的数据全部删除,HWM还是为原值,由于这个特点,使HWM很象一个水库的历史最高水位,这也就是HWM的原始含义,当然不能说一个水库没水了,就说该水库的历史最高水位为0。但是如果我们在表上使用了truncate命令,则该表的HWM会被重新置为0。

    --

    --查看hwm

    select file_name, ceil((nvl(hwm, 1) * 8192) / 1024 / 1024) as "HWM(MB)"

    from dba_data_files a,

    (select file_id, max(block_id + blocks - 1) hwm

    from dba_extents

    group by file_id) b

    where a.file_id = b.file_id(+);

    --查看hwm 信息全

    SELECT *

    FROM (SELECT /*+ ordered use_hash(a,b,c) */

    a.file_id,

    a.file_name,

    a.filesize,

    b.freesize,

    (a.filesize - b.freesize) usedsize,

    c.hwmsize,

    c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,

    a.filesize - c.hwmsize canshrinksize

    FROM (SELECT file_id,

    file_name,

    round(bytes / 1024 / 1024) filesize

    FROM dba_data_files) a,

    (SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize

    FROM dba_free_space dfs

    GROUP BY file_id) b,

    (SELECT file_id, round(MAX(block_id) * 8 / 1024) HWMsize

    FROM dba_extents

    GROUP BY file_id) c

    WHERE a.file_id = b.file_id

    AND a.file_id = c.file_id

    ORDER BY unsedsize_belowhwm DESC)

    WHERE file_id IN (SELECT file_id

    FROM dba_data_files

    WHERE tablespace_name = 'DMSB_01')

    ORDER BY file_id;

    --释放

    alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\D_TEST1' resize 600m;

    高水位详解,引自:http://www.cnblogs.com/linjiqin/archive/2012/01/15/2323030.html

    更多相关内容
  • Oracle 定时删除数据释放空间 ,创建存储过程并使用job完成。
  • 1)批量将niptest表空间中的表move到USERS表空间,再删除空间niptest首先看下此表空间内的表move到其他表空间防止数据丢失select * from dba_tables where tablespace_name='NIPTEST';select * from dba_extents ...

    1)批量将niptest表空间中的表move到USERS表空间,再删除表空间niptest首先看下此表空间内的表move到其他表空间防止数据丢失

    select * from dba_tables where tablespace_name='NIPTEST';

    select * from dba_extents where tablespace_name='NIPTEST';

    select * from dba_segments where tablespace_name='NIPTEST';

    SELECT 'alter table '||owner||'.'||table_name||' move tablespace USERS;' FROM DBA_tables WHERE TABLESPACE_NAME='NIPTEST'; 批量把表移动到其他表空间

    ******move(降低高水位)

    优点:可以移动表到其他表空间,在执行命令时不需要执行alter table table_name enable row movement

    缺点:表move会导致表中的索引失效,要rebuild;同时表会产生行级锁......;在此如果表中有LOB字段时要用一下命令来实现表空间移动:alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lob segment  tablespace tablespace_name;也可以单独move lob,index要rebuild

    ******shrink space

    优点:降低高水位时索引不会失效

    缺点:不能将表移动到其他表空间;高水位降低效果没有move明显;同时在执行命令前要先执行(alter table table_name enable row movement允许行移动)也会表会产生行级锁.......,shrink比move更耗费cpu,产生很多current block这样生成巨大的redo与undo如果表中索引很少可以建议使用move降低高水位

    2)移动完表发现主键和索引还是在源表空间

    SELECT * FROM DBA_extents WHERE TABLESPACE_NAME='USERS';   -->查看原表空间的主键和索引alter index XX rebuild tablespace ;

    ---> 批量执行索引重建

    SELECT 'alter index  '||owner||'.'||segment_name||' rebuild tablespace USERS;' FROM DBA_extents WHERE TABLESPACE_NAME='NIPTEST';批量将主键索引重建到其他表空间select * from dba_segments where tablespace_name='NIPTEST';弄完上面的操作,这里还有数据,不要在意,那是回收站的,无需修改直接删表空间即清空

    (3) 表都移动完了之后先把数据文件offline drop再删除数据文件

    alter database datafile '/home/oracle/app/oracle/oradata/kfdb/niptest1' offline drop;

    ---> 查看下数据文件状态

    select status from dba_tablespaces v$datafile where tablespace_name='NIPTEST'; -----offline--> 再删除表空间drop tablespace niptest; --> 删除表空间,但不删除其文件

    drop tablespace niptest including contents; -->删除表空间同时删除表空间的数据对象drop tablespace niptest including contents and datafiles;

    -->删除表空间时删除数据对象及其OS系统文件一起删除,,以便释放空间,前提表空间不能是数据库默认表空间否则会 报错:ORA-12919: Can not drop the default permanent tablespace

    (4)怎么查看下数据库的默认表空间是什么:select * from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

    如果您删除的表空间是数据库默认表空间要用一下命令来

    更换数据库默认表空间:alter database default tablespace users;再执行: drop tablespace niptest including contents and datafiles;

    [root@kfdb49 kfdb]# df -hl  --> 看下OS系统空间情况  --- 表空间释放

    ------创建表空间的语句:create tablespace niptest datafile '/home/oracle/app/oracle/oradata/kfdb/niptest1' size 10G autoextend on next XXM maxsize XXm extent management local;

    有的人会想着再创建一个niptest 为10G的表空间,避免后期imp时源表的表空间是niptest

    : 其实不需要,如果库中没有niptest表空间,就算imp源表的表空间是niptest,也会导入到用户的默认表空间,有niptest表空间的话则会导入到niptest表空间(如果后续不想拥有niptest表空间 就要斩草除根的将niptest删除之后不要创建)

    回收unlimited tablespace 给予额外权限niptest表空间权限会正常导入,否则报错

    额外权限和用户默认表空间是一个的话可以正常导入,否则知道表结构到用户默认表空间

    展开全文
  • oracle删除数据释放空间流程

    千次阅读 2020-05-22 11:12:18
    (高水位HWM “High Water Mark “:oracle中block有没有使用的分界线,它会随着数据的insert而上升,但它并不会随数据的delete而下降,因此全表扫描的时间并不因数据的delete而减少,相反可能由于块清除反而全表扫描...

    生产环境:数据库里空间不足,niptest 表空间251G,只使用了17G

    再alter database datafile '...../niptest1' resize 10G; 的时候说超出了范围要求 

    由于表变动比较频繁,高水位值比较大

    (高水位 HWM “High Water Mark “:oracle中block有没有使用的分界线,它会随着数据的insert而上升,但它并不会随数据的delete而下降,因此全表扫描的时间并不因数据的delete而减少,相反可能由于块清除反而全表扫描时间增加)

    删除表空间步骤:

    1)  批量将niptest表空间中的表move 到USERS表空间,再删除表空间niptest
    首先看下此表空间内的表  move到其他表空间  防止数据丢失

    select * from dba_tables where tablespace_name='NIPTEST';
    select * from dba_extents where tablespace_name='NIPTEST';
    select * from dba_segments where tablespace_name='NIPTEST';
    SELECT 'alter table '||owner||'.'||table_name||' move tablespace USERS;' FROM DBA_tables WHERE TABLESPACE_NAME='NIPTEST'; 批量把表移动到其他表空间

     

    ******  move (降低高水位)

    优点:可以移动表到其他表空间,在执行命令时不需要执行alter table table_name enable row movement

    缺点:表move 会导致表中的索引失效,要rebuild;同时表会产生行级锁......;在此如果表中有LOB字段 时要用一下命令来实现表空间移动:alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lob segment  tablespace tablespace_name;也可以单独move lob,index要rebuild

     

    ******  shrink space

    优点:降低高水位时索引不会失效

    缺点:不能将表移动到其他表空间;高水位降低效果没有move明显;同时在执行命令前要先执行(alter table table_name enable row movement允许行移动)也会表会产生行级锁.......,shrink比move更耗费cpu,产生很多current block这样生成巨大的redo与undo 如果表中索引很少可以建议使用move降低高水位

     

    2) 移动完表发现 主键和索引还是在源表空间

    SELECT * FROM DBA_extents WHERE TABLESPACE_NAME='USERS';   -->查看原表空间的主键和索引
    alter index XX rebuild tablespace ;

    ---> 批量执行索引重建
    SELECT 'alter index  '||owner||'.'||segment_name||' rebuild tablespace USERS;' FROM DBA_extents WHERE TABLESPACE_NAME='NIPTEST';   批量将主键索引重建到其他表空间
    select * from dba_segments where tablespace_name='NIPTEST';弄完上面的操作,这里还有数据,不要在意,那是回收站的,无需修改直接删表空间即清空

     

    (3) 表都移动完了  之后 先把数据文件offline drop再删除数据文件

    alter database datafile '/home/oracle/app/oracle/oradata/kfdb/niptest1' offline drop; 

    ---> 查看下数据文件状态

    select status from dba_tablespaces v$datafile where tablespace_name='NIPTEST'; -----offline
    --> 再删除表空间
    drop tablespace niptest; --> 删除表空间,但不删除其文件
    drop tablespace niptest including contents; -->删除表空间同时删除表空间的数据对象
    drop tablespace niptest including contents and datafiles; 

     --> 删除表空间时删除数据对象及其OS系统文件一起删除,,以便释放空间,前提表空间不能是数据库默认表空间
    否则会 报错:ORA-12919: Can not drop the default permanent tablespace

     

    (4) 怎么查看下数据库的默认表空间是什么:
    select * from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';  

    如果您删除的表空间是数据库默认表空间要用一下命令来

    更换数据库默认表空间:alter database default tablespace users;
     再执行: drop tablespace niptest including contents and datafiles;  

    [root@kfdb49 kfdb]# df -hl  --> 看下OS系统空间情况  --- 表空间释放

    ------创建表空间的语句:
    create tablespace niptest datafile '/home/oracle/app/oracle/oradata/kfdb/niptest1' size 10G autoextend on next XXM maxsize XXm extent management local;

    有的人会想着再创建一个niptest 为10G的表空间,避免后期imp时源表的表空间是niptest

    : 其实不需要,如果库中没有niptest表空间,就算imp源表的表空间是niptest,也会导入到用户的默认表空间,有niptest表空间的话则会导入到niptest表空间(如果后续不想拥有niptest表空间 就要斩草除根的将niptest删除之后不要创建)

    回收unlimited tablespace 给予额外权限niptest表空间权限会正常导入,否则报错

    额外权限和用户默认表空间是一个的话可以正常导入,否则知道表结构到用户默认表空间

    展开全文
  • oracle数据已经删除,但是表空间不能释放的情况;支持查询单表占用空间大小,释放掉占用的无效空间;支持批量生成释放脚本,释放掉空表占用的表空间
  • oracle定时删除空间数据释放空间(oracle编程高手总结编写)
  • 转载自: ... ... 一现象描述 ...Delete是oracle数据库中的常用操作,尤其是在自动化测试中,初始化环境、前置准备都不可避免的进行增删操作,但持续一时间后,可能会碰到表空间不足这类报错现象,这...

    转载自:

    https://www.cnblogs.com/gaoyuchuanIT/articles/4151231.html

     

    https://blog.51cto.com/2681882/2133524

    一 现象描述

    Delete是oracle数据库中的常用操作,尤其是在自动化测试中,初始化环境、前置准备都不可避免的进行增删操作,但持续一时间后,可能会碰到表空间不足这类报错现象,这就不禁纳闷儿了,明明插入数据前会有删除的,数据总量并没有呈现明显的量级变化,为什么表占用空间却在偷偷增大呢?

     

    二 现象分析

    出现上述现象的原因是Delete操作并不会释放占用的空间。在讲解原因之前,先了解下oracle中高水位线的概念,有助于理解delete操作产生的这种现象。

    所谓的高水位(HWM),通俗的讲就是一个标记,用来记录已经有多少数据块(Block)分配给表,可以拿水库的历史最高水位来类比,当使用delete操作后,数据虽然被删除了,但这个高水位的标记并没有降低,就好比水库的历史最高水位不会因为水被释放了而降低。因而,原则上在没有外部干预的条件下,这个高水位标记值只会增大,不会降低。

     

    三 实战模拟重现现象

    根据上面的现象描述和分析,接下来,我会用具体的实例模拟该现象,使大家可以更直观的了解。

    第1,创建一张测试表test,具体字段不需要关心,只要知道初始了存储空间为100M,如图所示:

    1.jpg

     

    第2,创建完成后,我们查看下数据表占用的空间,如图所示:

     2-记住三个关键数据.jpg

     

    其中,查询前需要对表进行分析,使用命令为:ANALYZE TABLE test ESTIMATE STATISTICS;查询语句为:SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 'TEST';

    注意上面三个字段的结果:BLOCKS=0;  EMPTY_BLOCKS=13312;  NUM_ROWS=0,即当前表占用的块数为0,默认1 BLOCK = 8kb,预分配的块为13312,行数为0。

    一切都没有问题,新创建的表,没有数据嘛,当然行数为0,占用块数为0喽。

     

    第3,写一个语句块,循环插入1000条语句,再次对test表进行分析、查询,结果如下:

    3插入数据.jpg

     

     

    从图中可以看到,占用BLOCKS=222,NUM_ROWS=1000,合乎逻辑,插入了1000条数据,占用了空间嘛。

     

    第4,使用Delete语句删除1000条数据,再次对test表进行分析、查询,结果却是如下:

    4删除后行数为0空间还占用.jpg

     

    从上图中可以清楚的看到,数据被删除后,NUM_ROWS=0了,但是BLOCKS并没有被置为0,也就是这部分数据块仍然被认为是占用的。

    因此,就出现了本文一开始就提到的现象,随着不断的插入、删除数据,BLOCKS也会不断扩大,尽快delete操作后,可能表中数据量很少,但表占用的存储空间未减少。

     

    四 解决方法

    针对delete操作引起的空间不释放现象,或者,更正式一点的说法,如何降低高水位线,方法有很多种,如,shrink space;move tablespace;create table xxx as select * from xxx 重建表等。使用这些方法前,我们的原则是:

    如果可以truncate,直接truncate,该操作会重置高水位线,BLOCKS会被置为0,NUM_ROWS置为0;否则,优先使用shrink space,该方法不需要重建索引。

    接着上面第4步,我们使用shrink space降低高水位线,释放空间,其中,使用shrink space命令前,需要先alter table test enable row movement;开启行移动,再次对表进行分析、查询,结果如下:

     5.jpg

     

    从图中可以看出,此时BLOCKS已经被置为0了,但是,细心的你可能也发现, EMPTY_BLOCKS已经不是初始的13312,而是此时的40,这说明shrink space不仅会释放高水位线以下的空间,也会释放申请的空间,即高水位线上下都有操作,这也是与move、truncate的不同,它们只能释放高水位线以下的空间。

     

    五 shrink space常用操作命令

    Shrink space的常用命令如下:

    alter table tablename enable row movement;--打开行移动

    alter table tablename shrink space cascade;--压缩表及相关数据段,并下调HWM

    alter table tablename shrink sapce compact;--只压缩,不下调HWM

    alter table tablename shrink sapce ;--下调HWM

    alter table tablename disable row movement;--关闭行移动

    6 常用命令.jpg

     

    六 Delete操作的潜在影响

    根据上述分析,delete操作产生的潜在影响如下:

    1. 全表扫描通常要读出直到HWM标记的所有属于该表的数据块,即使该表中没有任何数据;(造成查询变慢)

    2. 插入操作时使用append关键字,即使HWM以下有空闲的数据库块,插入时使用HWM以上的数据块;(造成HWM自动增大)

     

    七 总结

    通过上文的现象描述和分析,随着insert的不断操作,高水位线也随着不断增加,尽管delete了数据,但高水位线并没有下降,导致表占用的空间没有释放。因此,在实际应用中,如果可能,尽量使用truncate,而且该操作高效、快速;否则要考虑下delete操作遗留的影响,使用合适的方法整理空间

    展开全文
  • 测试的时候向数据库中插入了大量的数据,测试完成后删除了测试用户以及其全部数据,但是数据文件却没有缩小。经查阅资料之后发现这是 Oracle “高水位”所致,那么怎么把这些数据文件的大小降下来呢?解决办法如下:...
  • oracle清理数据释放空间
  • 来源于:http://www.itpub.net/thread-513609-1-1.html ... ... 1、首先要得到USERS01.DBF的file_id SELECT file_name,file_id FROM dba_data_files WHERE file_name like '%USER%';...2、统计这个数据文件有
  • linux删除oracle空间文件后空间释放2014-10-20linux删除oracle空间文件后空间释放[oracle@server140 data8]$ df -h /data8Filesystem Size Used Avail Use% Mounted on/dev/sda3 414G 375G 19G 96% /data8.....
  • 索引文件迁移步骤:准备工作:1)备份GBOS用户表...1.查看索引表空间 具有那些数据文件select file_id,file_name,tablespace_name,bytes/1024/1024 M,blocks from dba_data_fileswhere TABLESPACE_NAME='USERINDEX...
  • 我们都知道数据表的庞大导而致其查询速度的降低是水到渠成的,所以我们只有将相关的数据表的数据相应的移走,但是如果使用Oracle delete之后,相关的数据删除了,但是速度没有多大改善,忧闷了。使用备份表再drop掉...
  • Oracle亿万级别数据清理,表空间释放 背景 风控项目运行三年,相关数据源对接产生的数据不断累积,随着业务量的攀升,表数据量急剧增长,经过几轮delete后,目前数据表三个月存量热数据为4kw+。一方面影响数据查询...
  • drop后的表被放在回收站(dba_recyclebin)里,而不是直接删除掉。这样,回收站里的表信息就可以被恢复,或彻底清除。 通过查询回收站dba_recyclebin获取被删除的表信息,然后使用语句 flashback table STUDENT_INFO_...
  • ORACLE解决表空间不释放空间

    千次阅读 2021-11-14 18:12:30
    由于数据库中有一个日志表记录7000万条日志,造成数据库占用磁盘283G之多,所以现在要求给磁盘瘦身,但是直接用DELETE语句删除,不能释放空间,经过查资料学习,得知这是"HWM"(高水位)造成.经过查找资料总结解决方案有两种...
  • Oracle中关于清除数据释放空间

    千次阅读 2021-05-07 09:08:38
    一、表的重命名flashback table test2 to before... --彻底删除表二、清除表中的数据truncate操作 同没有where条件的delete操作十分相似,只是把表里的信息全部删除,但是表依然存在。例如:truncate table XXTrunca...
  • 而后在导库的时候又没进行正确操作导致新建的表空间并未使用,而把数据又全部导入users表空间了,这样就造成数据库服务器磁盘空间的浪费,对于这种情况有两种解决方案,把表空间减小或者直接删除。 首先附一个查询表...
  • oracle删除数据文件

    千次阅读 2021-05-07 11:26:58
    在我们详细介绍之前,我们必须说清楚一点:Oracle不提供如删除表、视图一样删除数据文件的方法,数据文件是表空间的一部分,所以不能“移走”表空间。一、使用offline数据文件的方法非归档模式使用:alter database ...
  • -删除空的表空间,但是不包含物理文件drop tablespace tablespace_name;--删除非空表空间,但是不包含物理文件drop tablespace tablespace_name including contents;--删除空表空间,包含物理文件drop tablespace ...
  • 我们都知道数据表的庞大导而致其查询速度的降低是水到渠成的,所以我们只有将相关的数据表的数据相应的移走,但是如果使用之后,相关的数据删除了,但是速度没有多大改善,忧闷了。使用备份表再drop掉原表。的确可以...
  • 当表中的数据不需要时,则应该删除数据释放所占用的空间删除表中的数据可以使用Delete语句或者Truncate语句,下面分别介绍。 一、delete语句 (1)有条件删除 语法格式:delete [from] table_name [where ...
  • 但是,采用了很多办法才释放掉表空间,主要是系统中大量使用分区表,而针对分区表清除数据,不会释放表空间,必须把分区drop掉,才会释放空间。记录一下当时操作时学习和使用的一些语句:一、drop表执行drop table ...
  • drop执行drop table xx 语句drop后的表被放在回收站(user_recyclebin)里,而不是直接删除掉。这样,回收站里的表信息就可以被恢复,或彻底清除。通过查询回收站user_recyclebin获取被删除的表信息,然后使用语句...
  • 1.删除表中所有的行,释放数据所占用的自由空间;同时删除索引数据释放该表上的index所占用的自由空间。TRUNCATE TABLE employees_demo;但是,但是,truncate不会释放由 MINEXTENTS storage parameter指定的存储...
  • truncate table tablename DROP STORAGE; 解释: 直接删除表,并且释放存储空间。truncate的意思是清空表数据, “DROP STORAGE”是释放存储空间

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 43,487
精华内容 17,394
热门标签
关键字:

oracle删除数据释放空间