精华内容
下载资源
问答
  • oracle 删除表空间及数据文件方法
    2021-05-07 05:54:37

    -删除空的表空间,但是不包含物理文件

    drop tablespace tablespace_name;

    --删除非空表空间,但是不包含物理文件

    drop tablespace tablespace_name including contents;

    --删除空表空间,包含物理文件

    drop tablespace tablespace_name including datafiles;

    --删除非空表空间,包含物理文件

    drop tablespace tablespace_name including contents and datafiles;

    --如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段,就要加上CASCADE CONSTRAINTS

    drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

    以system用户登录,查找需要删除的用户:

    --查找用户

    select * from dba_users;

    --查找工作空间的路径

    select * from dba_data_files;

    --删除用户

    drop user 用户名称 cascade;

    --删除表空间

    drop tablespace 表空间名称 including contents and datafiles cascade constraint;

    例如:删除用户名成为ABC,表空间名称为ABC

    --删除用户,及级联关系也删除掉

    drop user ABC cascade;

    --删除表空间,及对应的表空间文件也删除掉

    drop tablespace ABC including contents and datafiles cascade constraint;

    删除无任何数据对象的表空间:

    首先使用PL/SQL界面化工具,或者使用oracle自带的SQL PLUS工具,连接需要删除的表空间的oracle数据局库。

    确认当前用户是否有删除表空间的权限,如果没有 drop tablespace,请先用更高级的用户(如sys)给予授权或者直接用更高级的用户。

    用drop tablespace xxx ,删除需要删除的表空间。

    删除有任何数据对象的表空间

    使用drop tablespace xxx including contents and datafiles;来删除表空间。

    注意事项:

    如果drop tablespace语句中含有datafiles,那datafiles之前必须有contents关键字,不然会提示ora-01911错误

    1、以system用户登录查找需要删除的用户(普通用户没有删除权限)

    select * from dba_users;

    2、查询需要删除用户对应的表空间

    select * from dba_data_files;

    3、删除用户和表空间

    drop user usernamecascade;

    drop tablespace tablespacename including contents and datafiles cascade constraint;

    在删除用户时可能会碰到无法删除当前连接的用户,这是由于还有数据库连接到该用户,有会话存在,需要先删除会话。

    最暴力的做法是直接shutdown数据库,然后重启即可。。。

    一般的操作是通过查询SessionID,手动杀掉会话再删除用户:

    1)查询连接情况:select username,sid,serial# from v$session;

    2)找到要删除用户的sid和serial并删除:alter system kill session 'sid,serial';

    再执行删除用户的操作,如果还是无法删除说明还有连接的会话,继续执行删除会话的操作。

    ---------------------

    更多相关内容
  • 回滚段是数据库的一部分,它记录数据库变更的信息。使用这些信息实现数据库的读一致性及其恢复。若回滚段出现故障,则数据库不能正常启动,导致数据库瘫痪,...本文将为大家介绍Oracle回滚空间数据文件删除处理。
  • 这里主要说的场景是磁盘空间本身很大,但空间对应的数据文件初始化的时候就直接顶满了磁盘空间,导致经常收到磁盘空间满的报警。 一、错误信息 告警内容如下: 【发现异常】地产客储系统数据库Oracle_192.168.xx....
  • oracle删除数据文件

    千次阅读 2021-05-07 11:26:58
    在我们详细介绍之前,我们必须说清楚一点:Oracle不提供如删除表、视图一样删除数据文件的方法,数据文件空间的一部分,所以不能“移走”空间。一、使用offline数据文件的方法非归档模式使用:alter database ...

    在我们详细介绍之前,我们必须说清楚一点:Oracle不提供如删除表、视图一样删除数据文件的方法,数据文件是表空间的一部分,所以不能“移走”表空间。

    一、使用offline数据文件的方法

    非归档模式使用:alter database datafile '...' offline drop;

    归档模式使用:  alter database datafile '...' offline;

    说明:

    1)         以上命令只是将该数据文件OFFLINE,而不是在数据库中删除数据文件。该数据文件的信息在控制文件种仍存在。查询v$datafile,仍显示该文件。

    2)         归档模式下offline和offline drop效果是一样的

    3)         offline后,存在此datafile上的对象将不能访问

    4)         noarchivelog模式下,只要online redo日志没有被重写,可以对这个文件recover后进行online操作

    实际使用案例:

    直接删除数据文件后无法进入系统的解决方案

    正常情况下,删除表空间的正确方法为:

    DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

    如果没有通过以上命令删除而直接删除了数据文件,将导致数据库无法打开。

    如果直接删除了数据文件

    普通用户登录时,则报错:

    ORA-01033: ORACLE initialization or shutdown in progress

    sys用户可以正常登录

    但进行操作时(SELECT count(1) FROM user_tables),则会报错:

    ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询

    如果执行命令alter database open以打开数据库时,又报如下错:

    ORA-01157: 无法标识/锁定数据文件 12 - 请参阅 DBWR 跟踪文件

    ORA-01110: 数据文件 12: 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TSTEST001.DBF'

    说明数据库没找到这个数据文件

    因为数据文件在没有被offline的情况下物理删除了,导致oracle的数据不一致,因此启动失败.

    通过以下方法即可解决

    解决方法:

    sqlplus sys/orcl@orcl as sysdba;

    SQL> alter database datafile 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TSTEST001.DBF' offline drop;

    SQL> alter database open;

    SQL> drop tablespace CTBASEDATA;

    二、Oracle 10G R2开始,可以采用:Alter tablespace tablespace_name drop datafile file_name;来删除一个空数据文件,并且相应的数据字典信息也会清除:

    sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

    2  where tablespace_name='USERS';

    FILE_ID  FILE_NAME                        TABLESPACE_NAME

    -------    --------------------------------------------       ------------------

    4        /u01/app/oracle/oradata/orcl/users01.dbf    USERS

    sys@ORCL>alter tablespace users add datafile

    2  '/u01/app/oracle/oradata/orcl/users02.dbf' size 5M autoextend off;

    Tablespace altered.

    sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

    2  where tablespace_name='USERS';

    FILE_ID  FILE_NAME                          TABLESPACE_NAME

    -------    --------------------------------------------        -------------------

    4        /u01/app/oracle/oradata/orcl/users01.dbf     USERS

    9        /u01/app/oracle/oradata/orcl/users02.dbf     USERS

    sys@ORCL>drop table test;

    Table dropped.

    sys@ORCL>create table test tablespace users

    2  as

    3  select * from dba_objects;

    Table created.

    sys@ORCL>select SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents

    2  where file_id=9;

    SEGMENT_NAME                      FILE_ID     BLOCKS

    ------------------------------                  ----------      ----------

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9          8

    TEST                                    9        128

    TEST                                    9        128

    17 rows selected.

    sys@ORCL>alter table test move tablespace PERFSTAT; --把表移动到其它表空间

    Table altered.

    sys@ORCL>select SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents

    2  where file_id=9;

    no rows selected

    sys@ORCL>alter tablespace users drop datafile

    2  '/u01/app/oracle/oradata/orcl/users02.dbf';

    Tablespace altered.

    2  where tablespace_name='USERS';

    FILE_ID  FILE_NAME                       TABLESPACE_NAME

    -------    --------------------------------------------      ---------------------

    4        /u01/app/oracle/oradata/orcl/users01.dbf   USERS

    三、oracle 10g可以删除临时表空间的文件

    alter database tempfile '/home/oracle/temp01.dbf' drop including datafiles;

    Oracle 删除数据后释放数据文件所占磁盘空间

    测试的时候向数据库中插入了大量的数据,测试完成后删除了测试用户以及其全部数据,但是数据文件却没有缩小.经查阅资料之后发现这是 Oracle “高水位”所致,那么怎么把这些数据文件的大小降下来呢?解决办 ...

    linux删除数据文件无备份恢复

    参考 : http://www.lunar2013.com/2013/06/linux-%E8%AF%AF%E5%88%A0%E9%99%A4%E6%96%87%E4%BB%B6%E6%81%A2%E ...

    oracle删除日志文件

    oracle删除日志文件 删除日志文件的语法例如以下: alter database drop logfile member logfile_name; 删除日志文件须要注意例如以下几点: 1.该日志 ...

    Oracle-11g 从表空间删除数据文件

    从表空间删除数据文件前提条件 如果欲从表空间中删除数据文件,那么该数据文件必须为空,否则将报出"ORA-03262: the file is non-empty"的错误.   从表 ...

    Oracle单个数据文件超过32G后扩容

    Oracle单个数据文件超过32G后扩容   表空间数据文件容量与DB_BLOCK_SIZE的设置有关,而这个参数在创建数据库实例的时候就已经指定.DB_BLOCK_SIZE参数可以设置为4K.8K. ...

    oracle删除数据后表空间仍过大问题解决方法

    -----亲测有效------- --一.备份原始数据库库--1.备份空表--在plsql里面执行一下这句话 然后把结果集 再执行一把 再导数据select 'alter table '||table ...

    oracle rename数据文件的两种方法

    oracle rename数据文件的两种方法 2012-12-11 20:44 10925人阅读 评论(0) 收藏 举报  分类: oracle(98)  版权声明:本文为博主原创文章,未经博主允许不 ...

    Oracle误删数据文件后出现oracle initialization or shutdown in progress解决

    一.错误分析 1.首先本人在出现这种情况的背景是执行如下SQL语句后生成的表空间 --自定义表空间 数据表空间 临时表空间 CREATE TEMPORARY TABLESPACE HOUSE_TEMP ...

    随机推荐

    计算Div标签内Checkbox个数或已被disabled的个数

    先看下面的html: 计算div内的checkbox个数:$('#divmod input[type="checkbox"]').length 计算div内checkbox被dis ...

    UVA10325 The Lottery(容斥原理)

    题意: 给n,m,和m个数(k1~km).求1~n中有多少个数不是(k1~km)中任意一数的倍数. 题解: 容斥模板题.反面考虑,a的倍数有n/a个:既是a,也是b的倍数,即lcm(a,b)的倍数有n ...

    PHP 图片上传工具类(支持多文件上传)

    ====================ImageUploadTool======================== <?php class ImageUploadTool { private ...

    工厂食堂3D指纹考勤系统解决方案

    指纹考勤就餐管理系统利用3D活体指纹技术完成对正式员工就餐管理.就餐者只需办理完入职手续,并登记考勤指纹,就可通过考勤指纹在工厂食堂领餐. 大多数工厂食堂就餐是福利性的,只准员工就餐,不准员工带亲戚朋 ...

    轻量级ORM框架 Dapper快速学习

    好在有师兄师姐一起带着做,所以开始没那么困难,但是由于大学涉猎范围有限,往往有很尴尬的时候,不懂构造方法重载,去“请教”,本来以为师兄会帮忙写好,结果“我念,你来写”,被深深的激励了一把,后来就早出晚 ...

    WordPress翻译中 &lowbar;&lowbar;&lpar;&rpar;、&lowbar;e&lpar;&rpar;、&lowbar;x、&lowbar;ex 和 &lowbar;n 的用法及区别

    编译函数 WordPress使用了下面几个函数来方便语言本地化. __() _e() _x() _ex() _n() 以上所列的函数是用来包含所需翻译的字符串的,根据字符串的不同参数和输出类型,需要使 ...

    java读取properties配置文件信息

    一.Java Properties类 Java中有个比较重要的类Properties(Java.util.Properties),主要用于读取Java的配置文件,各种语言都有自己所支持的配置文件,配置 ...

    SpringContextHolder 静态持有SpringContext的引用&lpar;如何取得Spring管理的bean &rpar;

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 3 ...

    重新安装Ubuntu12&period;04

    重新安装Ubuntu12.04 之所以我重新安装Ubuntu,因为我第一次给根目录分配的空间过小,好像是20GB吧~结果编译Android的时候,编译了3个小时候直接中止掉了.郁闷.这个也告诉我们一定 ...

    C&num;代码实现,确保windows程序只有一个实例(instance)

    static class Program { /// /// 应用程序的主入口点. /// [STAThread] static vo ...

    展开全文
  • 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表空间权限会正常导入,否则报错

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

    展开全文
  • Oracle7.X 回滚空间数据文件删除处理方法
  • 如何正确的删除表空间数据文件

    千次阅读 2021-05-02 07:05:54
    如何正确的删除表空间数据文件应该使用如下的命令删除:ALTER TABLESPACE TEST DROP DATAFILE3;参考mos文章:Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command (文档ID 1050261.1)...

    如何正确的删除表空间数据文件

    应该使用如下的命令删除:

    ALTER TABLESPACE TEST DROP DATAFILE3;

    参考mos文章:

    Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command (文档ID 1050261.1)

    违反下列任何一个条件,该datafile均不能被drop:

    1)必须为空,否则会报:ORA-03262: the file is non-empty。值得注意的是,non-empty的含义是有extent被分配给了table,而不是该table中有无rows,此时若是使用drop table xxx是不行的,必须使用drop table xxx purge;或者在已经使用了drop table xxx的情况下,再使用purge table “xxx表在回收站中的名称”来purge该表,否则空间还是不释放,datafile依然drop不掉。

    2)不能是所属表空间的第一个file

    以上两者可以通过drop tablespace来达到目的。

    3)不能在read-only表空间中。---经测试是可以的

    4)不能被offline,否则会报:ORA-03264: cannot drop offline datafile of locally managed tablespace

    针对该报错,解决方法为:

    [oracle@rhel6 u01]$ oerr ora 3264

    03264, 00000, "cannot drop offline datafile of locally managed tablespace"

    // *Cause:  Trying to drop offline datafile in lmts

    // *Action: Try to drop file afetr making it online

    5)Cannot be a datafile that is part of the system tablespace, even if it is not the first datafile of the system tablespace--该条来源于How to Drop a Datafile From a Tablespace (文档ID 111316.1)

    若使用alter database datafile 3 offline drop;并不会删除数据文件,这个时候可以先online后再用alter tablespace test drop datafile 3;删除,若执行alter database  datafile 3 offline drop;后并OS级别删除了数据文件,那么需要使用alter database create datafile 3 as '/u03/app/oracle/oradata/ora1024g/sysaux01.dbf';来添加一个数据文件,然后再执行recover并online后再用alter tablespace test drop datafile 3;命令删除。

    1. alter database datafile 'file_name' offline drop

    该命令不会删除数据文件,只是将数据文件的状态更改为recover。 offline drop命令相当于把一个数据文件至于离线状态,并且需要恢复,并非删除数据文件。 数据文件的相关信息还会存在数据字典和控制文件中。

    1.1 对于归档模式:

    alter database datafile 'file_name' offline 和 offline drop 没有什么区别。 因为offline 之后多需要进行recover 才可以online。

    如:

    SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

    SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

    1.2 对于非归档模式:

    如果是非归档模式,只能是offline drop. 因为非归档模式没有归档文件来进行recover操作,当然,如果offline 之后,速度足够块,online redo里的数据还没有被覆盖掉,那么这种情况下,还是可以进行recover的。

    oracle 11g:

    SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;

    2. alter tablesapce ‘tablespace_name’ drop datafile 'datafile_name'

    该语句会删除磁盘上的文件并更新控制文件和数据字典中的信息,删除之后的原数据文件序列号可以重用。

    注意,该语句只能是datafile online的时候才可以使用。如果说对应的数据文件已经是offline for drop,那么仅针对 dictionary managed tablespaces 可用。

    OFFLINESpecify OFFLINE to take the data file offline. If the database is open,

    then you must perform media recovery on the data file before bringing it back

    online, because a checkpoint is not performed on the data file before it is

    taken offline.

    FOR DROPIf

    the database is in NOARCHIVELOG mode, then you must specify FOR DROP clause to take a data file offline. However,

    this clause does not remove the data file from the database. To do that, you

    must use an operating system command or drop the tablespace in which the data

    file resides. Until you do so, the data file remains in the data dictionary with

    the status RECOVER or OFFLINE.

    If the database is in ARCHIVELOG mode, then Oracle Database

    ignores the FOR DROP clause.

    Bringing Data Files Online or Taking Offline in ARCHIVELOG

    Mode

    To bring an individual data file online,

    issue the ALTER DATABASE statement and include the DATAFILE clause. The following statement brings the specified data

    file online:

    ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

    To take the same file offline, issue the following statement:

    ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

    Note:

    To use this form of the ALTER DATABASE statement, the database must be in ARCHIVELOG mode. This

    requirement prevents you from accidentally losing the data file, since taking

    the data file offline while in NOARCHIVELOG mode is likely to

    result in losing the file.

    Taking Data Files Offline in NOARCHIVELOG Mode

    To take a data file offline when the database

    is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE FOR DROP clauses.

    The OFFLINE keyword causes the database to mark the data file OFFLINE, whether or not it is corrupted, so that you can open the

    database.

    The FOR DROP keywords mark the data file for

    subsequent dropping. Such a data file can no longer be brought back online.

    Note:

    This operation does not actually drop the data file. It

    remains in the data dictionary, and you must drop it yourself using one of the

    following methods:

    An ALTER TABLESPACE ... DROP DATAFILE statement.

    After an OFFLINE FOR DROP, this method works for dictionary managed

    tablespaces only.

    A DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES statement

    If the preceding methods fail, an operating system command to delete the data

    file. This is the least desirable method, as it leaves references to the data

    file in the data dictionary and control files.

    The following statement takes the specified data file offline and marks it to

    be dropped:

    ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;

    点击(此处)折叠或打开

    SYS@ora10g> create tablespace ts_dd_lhr datafile '/tmp/ts_dd_lhr01.dbf' size 10M;

    Tablespace created.

    SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';

    alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'

    *

    ERROR at line 1:

    ORA-03261: the tablespace TS_DD_LHR has only one file

    SYS@ora10g> alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;

    Tablespace altered.

    SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';

    alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'

    *

    ERROR at line 1:

    ORA-03263: cannot drop the first file of tablespace TS_DD_LHR

    SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

    Tablespace altered.

    SYS@ora10g> ! ls -l /tmp/ts_dd_lhr0*

    -rw-r----- 1 oracle oinstall 10493952 Jun 29 14:58 /tmp/ts_dd_lhr01.dbf

    --------------------------------------------------

    SYS@ora10g> alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;

    Tablespace altered.

    SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' offline drop;

    Database altered.

    SYS@ora10g> ! ls -l /tmp/ts_dd_lhr02.dbf

    -rw-r----- 1 oracle oinstall 10493952 Jun 29 15:17 /tmp/ts_dd_lhr02.dbf

    SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

    alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'

    *

    ERROR at line 1:

    ORA-03264: cannot drop offline datafile of locally managed tablespace

    SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' online;

    alter database datafile '/tmp/ts_dd_lhr02.dbf' online

    *

    ERROR at line 1:

    ORA-01113: file 9 needs media recovery

    ORA-01110: data file 9: '/tmp/ts_dd_lhr02.dbf'

    SYS@ora10g> recover datafile 9;

    Media recovery complete.

    SYS@ora10g> alter database datafile '/tmp/ts_dd_lhr02.dbf' online;

    Database altered.

    SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

    Tablespace altered.

    SYS@ora10g> ! ls -l /tmp/ts_dd_lhr02.dbf

    ls: cannot access /tmp/ts_dd_lhr02.dbf: No such file or directory

    SYS@orclasm > create table t_ts_dd_lhr tablespace ts_dd_lhr as select * from dual;

    Table created.

    SYS@orclasm > truncate table t_ts_dd_lhr;

    Table truncated.

    SYS@orclasm >

    SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

    alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'

    *

    ERROR at line 1:

    ORA-03262: the file is non-empty

    SYS@orclasm > drop table t_ts_dd_lhr;

    Table dropped.

    SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

    alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf'

    *

    ERROR at line 1:

    ORA-03262: the file is non-empty

    SYS@orclasm > purge recyclebin;

    Recyclebin purged.

    SYS@orclasm > alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

    Tablespace altered.

    SYS@ora10g> create tablespace ts_dd_lhr datafile '/tmp/ts_dd_lhr01.dbf' size 10M;

    alter tablespace ts_dd_lhr add datafile '/tmp/ts_dd_lhr02.dbf' size 10M;

    Tablespace created.

    SYS@ora10g> SYS@ora10g> SYS@ora10g>

    Tablespace altered.

    SYS@ora10g>

    SYS@ora10g> alter tablespace ts_dd_lhr read only;

    Tablespace altered.

    SYS@ora10g> select * from dba_tablespaces;

    TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG

    ------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---

    SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO

    UNDOTBS1 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO

    SYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO

    TEMP 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO

    USERS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO

    EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT NOLOGGING NO LOCAL SYSTEM YES AUTO DISABLED NOT APPLY NO

    TS10GTEST 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO

    HHRIS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO

    TS_DD_LHR 8192 65536 1 2147483645 65536 READ ONLY PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO

    9 rows selected.

    SYS@ora10g>

    SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf';

    alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr01.dbf'

    *

    ERROR at line 1:

    ORA-03263: cannot drop the first file of tablespace TS_DD_LHR

    SYS@ora10g>

    SYS@ora10g> alter tablespace ts_dd_lhr drop datafile '/tmp/ts_dd_lhr02.dbf';

    Tablespace altered.

    SQL> select * from v$version;

    BANNER

    ----------------------------------------------------------------

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

    PL/SQL Release 10.2.0.1.0 - Production

    CORE    10.2.0.1.0      Production

    TNS for Linux: Version 10.2.0.1.0 - Production

    NLSRTL Version 10.2.0.1.0 - Production

    SQL> select name from v$datafile;

    NAME

    ------------------------------------------------------------------------------

    +DATA/rac/datafile/system.256.746634087

    +DATA/rac/datafile/undotbs1.258.746634089

    +DATA/rac/datafile/sysaux.257.746634087

    +DATA/rac/datafile/users.259.746634089

    +DATA/rac/datafile/undotbs2.264.746634255

    SQL> create tablespace test datafile '+DATA/rac/datafile/test01.dbf' size 10M;

    Tablespace created.

    SQL> alter tablespace test add datafile '+DATA/rac/datafile/test02.dbf' size 10M;

    Tablespace altered.

    SQL> select file#,status,name from v$datafile;

    FILE# STATUS  NAME

    ---------- ------- -----------------------------------------------------------

    1 SYSTEM  +DATA/rac/datafile/system.256.746634087

    2 ONLINE  +DATA/rac/datafile/undotbs1.258.746634089

    3 ONLINE  +DATA/rac/datafile/sysaux.257.746634087

    4 ONLINE  +DATA/rac/datafile/users.259.746634089

    5 ONLINE  +DATA/rac/datafile/undotbs2.264.746634255

    6 ONLINE +DATA/rac/datafile/test01.dbf

    7 ONLINE  +DATA/rac/datafile/test02.dbf

    SQL> alter database datafile '+DATA/rac/datafile/test01.dbf' offline;

    Database altered.

    SQL> set wrap off;

    SQL> select file#,status,name from v$datafile;

    FILE# STATUS  NAME

    ---------- ------- -----------------------------------------------------------

    1 SYSTEM  +DATA/rac/datafile/system.256.746634087

    2 ONLINE  +DATA/rac/datafile/undotbs1.258.746634089

    3 ONLINE  +DATA/rac/datafile/sysaux.257.746634087

    4 ONLINE  +DATA/rac/datafile/users.259.746634089

    5 ONLINE  +DATA/rac/datafile/undotbs2.264.746634255

    6 RECOVER +DATA/rac/datafile/test01.dbf

    7 ONLINE  +DATA/rac/datafile/test02.dbf

    7 rows selected.

    SQL> alter tablespace test drop datafile 6;

    alter tablespace test drop datafile 6

    *

    ERROR at line 1:

    ORA-03263: cannot drop the first file of tablespace TEST

    这里报错了,因为datafile 6是test表空间第一个数据文件不让删(这种情况只能删表空间了)。 我们删除test02.dbf 看看

    SQL> alter tablespace test drop datafile 7;

    Tablespace altered.

    -- 删除成功。

    SQL> select file#,status,name from v$datafile;

    FILE# STATUS  NAME

    ---------- ------- -----------------------------------------------------------

    1 SYSTEM  +DATA/rac/datafile/system.256.746634087

    2 ONLINE  +DATA/rac/datafile/undotbs1.258.746634089

    3 ONLINE  +DATA/rac/datafile/sysaux.257.746634087

    4 ONLINE  +DATA/rac/datafile/users.259.746634089

    5 ONLINE  +DATA/rac/datafile/undotbs2.264.746634255

    6 RECOVER +DATA/rac/datafile/test01.dbf

    6 rows selected.

    去ASM 里看下物理文件是否删除掉了:

    [oracle@rac1 ~]$ export ORACLE_SID=+ASM1

    [oracle@rac1 ~]$ asmcmd

    ASMCMD> ls

    DATA/

    FRA/

    ASMCMD> cd DATA

    ASMCMD> ls

    TEST/

    DB_UNKNOWN/

    RAC/

    ASMCMD> cd RAC

    ASMCMD> ls

    CONTROLFILE/

    DATAFILE/

    TEMPFILE/

    spfiletest.ora

    spfilerac.ora

    ASMCMD> cd DATAFILE

    ASMCMD> ls

    SYSAUX.257.746634087

    SYSTEM.256.746634087

    UNDOTBS1.258.746634089

    UNDOTBS2.264.746634255

    USERS.259.746634089

    test01.dbf

    --对应的物理文件test02.dbf 已经被删除了

    我们将datafile 6 online 看看:

    SQL> alter database datafile 6 online;

    alter database datafile 6 online

    *

    ERROR at line 1:

    ORA-01113: file 6 needs media recovery

    ORA-01110: data file 6: '+DATA/rac/datafile/test01.dbf'

    --提示需要recover。 这也就是需要归档文件的原因。

    SQL> recover datafile 6;

    Media recovery complete.

    SQL> alter database datafile 6 online;

    Database altered.

    SQL> select file#,status,name from v$datafile;

    FILE# STATUS  NAME

    ---------- ------- -----------------------------------------------------------

    1 SYSTEM  +DATA/rac/datafile/system.256.746634087

    2 ONLINE  +DATA/rac/datafile/undotbs1.258.746634089

    3 ONLINE  +DATA/rac/datafile/sysaux.257.746634087

    4 ONLINE  +DATA/rac/datafile/users.259.746634089

    5 ONLINE  +DATA/rac/datafile/undotbs2.264.746634255

    6 ONLINE  +DATA/rac/datafile/test01.dbf

    6 rows selected.

    最后把整个表空间test drop 掉:

    SQL> drop tablespace test including contents and datafiles;

    Tablespace dropped.

    SQL> select file#,status,name from v$datafile;

    FILE# STATUS  NAME

    ---------- ------- -----------------------------------------------------------

    1 SYSTEM  +DATA/rac/datafile/system.256.746634087

    2 ONLINE  +DATA/rac/datafile/undotbs1.258.746634089

    3 ONLINE  +DATA/rac/datafile/sysaux.257.746634087

    4 ONLINE  +DATA/rac/datafile/users.259.746634089

    5 ONLINE  +DATA/rac/datafile/undotbs2.264.746634255

    去ASM里看一下:

    ASMCMD> ls

    SYSAUX.257.746634087

    SYSTEM.256.746634087

    UNDOTBS1.258.746634089

    UNDOTBS2.264.746634255

    USERS.259.746634089

    对应的物理文件没有了。

    SQL>alter tablespace test drop datafile 8;

    不能drop 非空的数据文件, 如果要drop 某个数据文件,需要先把对象移除走。

    SELECT owner ownr,

    segment_name name,

    segment_type TYPE,

    extent_id exid,

    file_id fiid,

    block_id blid,

    blocks blks

    FROM dba_extents

    WHERE file_id = 8

    ORDER BY block_id;

    alter table temp move tablespace test2; 重建索引。

    Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command (文档 ID 1050261.1)

    In this Document

    This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

    APPLIES TO:

    Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.1.0 [Release 10.2 to 11.2]

    Information in this document applies to any platform.

    ***Checked for relevance on 01-Nov-2011***

    ***Checked for relevance on 27-Aug-2014***

    SYMPTOMS

    Attempting to drop a datafile from a tablespace using the 10.2 and higher feature:

    alter tablespace ... drop datafile ... ;

    fails. The errors reported may differ depending on the actual situation.

    ORA-3262: the file is non-empty

    ORA-3263: cannot drop the first file of tablespace

    ORA-3264: cannot drop offline datafile of locally managed tablespace

    ORA-60

    Some errors are rather straightforward. However some errors are not, like the ORA-60. While the answer to the most common errors are self explanatory, this note focuses on the ORA-60 self-deadlock error received..

    CHANGES

    Datafile was lost at the Operating System level, causing the datafile to be in an OFFLINE status. Because the datafile contained some temporary segments and extents, these became invalid or stray. This causes an ORA-60 when trying to drop the datafile from the tablespace.

    The datafile may be listed as MISSING in the datafile name, the reason for this is explained in:

    Note 1050268.1: Explanation of MISSING keyword in datafile name

    CAUSE

    To drop a data file or temp file, it:

    - Must be empty.

    - Cannot be the first file that was created in the tablespace.

    In such cases, drop the tablespace instead.

    - Cannot be in a read-only tablespace.

    - Cannot be offline.

    SOLUTION

    The missing datafile has left this tablespace in an indeterminate status. The tablespace itself can actually still be used. But when an object located in the missing datafile is accessed, the statement will error-out with:

    ORA-376: file 7 cannot be read at this time

    The tablespace will need to be dropped as well. The 10gR2 feature to drop a single datafile from a tablespace cannot be use in this situation..

    The following notes can be used to recover the data:

    Note 216683.1: How to Recover Data from a Tablespace When One or Several Datafiles are Lost.

    Note 286355.1: How to Recover OFFLINE Dropped Datafile in ARCHIVELOG MODE

    REFERENCES

    NOTE:1050268.1- Explanation of MISSING keyword in datafile name

    NOTE:286355.1- HOW TO RECOVER OFFLINE DROPPED DATAFILE IN ARCHIVELOG MODE

    How to Drop a Datafile From a Tablespace (文档 ID 111316.1)

    PURPOSE

    This note explains how a datafile can be removed from a database.

    Since there can be confusion as to how a datafile can be dropped because of

    the ALTER DATABASE DATAFILE OFFLINE DROP command, this note explains the

    steps needed to delete a datafile and, in contrast, when the OFFLINE DROP

    command is used.

    SCOPE & APPLICATION

    There are two situations where people may want to 'remove' a datafile from a

    tablespace:

    1. You have just mistakenly added a file to a tablespace, or perhaps you

    made the file much larger than intended and now want to remove it.

    2. You are involved in a recovery scenario and the database will not start

    because a datafile is missing.

    This article is meant to discuss situation 1 above. There are other

    articles that discuss recovery scenarios where a database cannot be brought

    online due to missing datafiles. Please see the 'Related Documents' section

    at the bottom of this article.

    Restrictions on Dropping Datafiles:

    - Datafile Must be empty.

    - Cannot be the first file in the tablespace. In such cases, drop the tablespace instead.

    - Cannot be a datafile that is part of the system tablespace, even if it is not the first datafile of the system tablespace.

    - Cannot be in a read-only tablespace.

    - The datafile cannot be offline.

    How to 'DROP' a Datafile from a Tablespace:

    ===========================================

    Version 9.2 and earlier

    Before we start with detailed explanations of the process involved, please note

    that Oracle does not provide an interface for dropping datafiles in the same

    way that you could drop a schema object such as a table, a view, a user, etc.

    Once you make a datafile part of a tablespace, the datafile CANNOT be removed,

    although we can use some workarounds.

    Before performing certain operations such as taking tablespaces/datafiles

    offline, and trying to drop them, ensure you have a full backup.

    If the datafile you wish to remove is the only datafile in that tablespace,

    simply drop the entire tablespace using:

    DROP TABLESPACE INCLUDING CONTENTS;

    You can confirm how many datafiles make up a tablespace by running the

    following query:

    select file_name, tablespace_name

    from dba_data_files

    where tablespace_name ='';

    The DROP TABLESPACE command removes the tablespace, the datafile, and the

    tablespace's contents from the data dictionary. Oracle will no longer have

    access to ANY object that was contained in this tablespace. The physical

    datafile must then be removed using an operating system command (Oracle NEVER

    physically removes any datafiles). Depending on which platform you try this

    on, you may not be able to physically delete the datafile until Oracle is

    completely shut down. (For example, on Windows NT, you may have to shutdown

    Oracle AND stop the associated service before the operating system will allow

    you to delete the file - in some cases, file locks are still held by Oracle.)

    If you have more than one datafile in the tablespace, and you do NOT need the

    information contained in that tablespace, or if you can easily recreate the

    information in this tablespace, then use the same command as above:

    DROP TABLESPACE INCLUDING CONTENTS;

    Again, this will remove the tablespace, the datafiles, and the tablespace's

    contents from the data dictionary. Oracle will no longer have access to ANY

    object that was contained in this tablespace. You can then use CREATE

    TABLESPACE and re-import the appropriate objects back into the tablespace.

    If you have more than one datafile in the tablespace and you wish to keep the

    objects that reside in the other datafile(s) which are part of this tablespace,

    then you must export all the objects inside the affected tablespace. Gather

    information on the current datafiles within the tablespace by running this

    query:

    select file_name, tablespace_name

    from dba_data_files

    where tablespace_name ='';

    Make sure you specify the tablespace name in capital letters.

    In order to allow you to identify which objects are inside the affected

    tablespace for the purposes of running your export, use the following query:

    select owner,segment_name,segment_type

    from dba_segments

    where tablespace_name=''

    Now, export all the objects that you wish to keep.

    Once the export is done, issue the DROP TABLESPACE tablespace INCLUDING

    CONTENTS.

    Note that this PERMANENTLY removes all objects in this tablespace. Delete the

    datafiles belonging to this tablespace using the operating system. (See the

    comment above about possible problems in doing this.) Recreate the tablespace

    with the datafile(s) desired, then import the objects into that tablespace.

    (This may have to be done at the table level, depending on how the tablespace

    was organized.)

    NOTE:

    The ALTER DATABASE DATAFILE OFFLINE DROP command, is not meant

    to allow you to remove a datafile. What the command really means is that you

    are offlining the datafile with the intention of dropping the tablespace.

    If you are running in archivelog mode, you can also use:

    ALTER DATABASE DATAFILE OFFLINE;

    instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer

    attempts to access it, but it is still considered part of that tablespace. This

    datafile is marked only as offline in the controlfile and there is no SCN

    comparison done between the controlfile and the datafile during startup (This

    also allows you to startup a database with a non-critical datafile missing).

    The entry for that datafile is not deleted from the controlfile to give us the

    opportunity to recover that datafile.

    New functionality was added with the release of version 10.1 and higher

    You can now specify drop tablespace inlcluding contents AND DATAFILES

    Refer to Oracle? Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01

    Chapter 8 managing tablespaces for more detailed explination

    Starting with version 10.2 and higher

    You can now alter tablespace drop datafile (except first datafile

    of a tablespace)

    Refer to the following Oracle Documentation for more details regarding this operation:

    For Oracle 10g Release 2:

    Oracle? Database Administrator's Guide 10g Release 2 (10.2)Part Number B14231-02 Chapter 9: Dropping Datafiles.

    For Oracle 11g:

    Oracle? Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-04 Chapter 12: Dropping Datafiles.

    If you do not wish to follow any of these procedures, there are other things

    that can be done besides dropping the tablespace.

    - If the reason you wanted to drop the file is because you mistakenly created

    the file of the wrong size, then consider using the RESIZE command.

    See 'Related Documents' below.

    - If you really added the datafile by mistake, and Oracle has not yet allocated

    any space within this datafile, then you can use ALTER DATABASE DATAFILE

    RESIZE; command to make the file smaller than 5 Oracle blocks. If

    the datafile is resized to smaller than 5 oracle blocks, then it will never

    be considered for extent allocation. At some later date, the tablespace can

    be rebuilt to exclude the incorrect datafile.

    RELATED DOCUMENTS

    ----------------- Note 30910.1 - Recreating database objects Note 1013221.6 - Recovering from a lost datafile in a ROLLBACK tablespace Note 198640.1 - How to Recover from a Lost Datafile with Different Scenarios Note 1060605.6 - Recover A Lost Datafile With No Backup Note 1029252.6 - How to resize a datafile

    HOW TO RECOVER OFFLINE DROPPED DATAFILE IN ARCHIVELOG MODE (文档 ID 286355.1)

    The information in this document applies to:

    Symptoms

    You have offline dropped a datafile in archivelog mode

    You know that once you drop a datafile you need to recreate the tablespace containing that datafile

    You can not do that as this is a Big tablespace

    You want that datafile to be again part of the database

    even though you do not want that datafile to contain any objects

    Changes

    You can recover the offline datafile and then make it online

    further as you do not want any objects to be allocated to that datafile

    you can resize it to a very small size ( remember it can only be done if the datafile is empty .........

    you can not resize a datafile below it's high water mark)

    Example

    ==========

    SQL> archive log list

    Database log mode Archive Mode

    Automatic archival Enabled

    Archive destination /h01/app/oracle/product/9.2.0/dbs/arch

    Oldest online log sequence 207

    Next log sequence to archive 209

    Current log sequence 209

    =======================

    shows DB is in archivelog mode

    =======================

    SQL> alter database datafile 44 offline drop;

    Database altered.

    SQL> select file#,status from v$datafile where file#=44;

    FILE# STATUS

    ---------- -------

    44 RECOVER ======> status in controlfile is recover

    SQL> c/datafile/datafile_header

    1* select file#,status from v$datafile_header where file#=44

    SQL> /

    FILE# STATUS

    ---------- -------

    44 OFFLINE ========> status in file_header is offline

    switch some log file

    sql> alter system switch logfile ;

    system altered

    .

    .

    .

    apply the log ( it will only ask for the log/ corresponding archivelog which was

    online at that time

    It wont ask you to apply any other archivelog

    SQL> recover datafile 44;

    ORA-00279: change 8252199007514 generated at 10/18/2004 14:21:47 needed for thread 1

    ORA-00289: suggestion : /h01/app/oracle/product/9.2.0/dbs/arch1_216.dbf

    ORA-00280: change 8252199007514 for thread 1 is in sequence #216

    Specify log: {=suggested | filename | AUTO | CANCEL}

    Log applied.

    Media recovery complete.

    SQL> select * from v$log;

    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

    ---------- ---------- ---------- ---------- ---------- --- ----------- ------------- ---------

    1 1 248 104857600 1 YES INACTIVE 8.2522E+12 18-OCT-04

    2 1 250 104857600 1 NO CURRENT 8.2522E+12 18-OCT-04

    3 1 249 104857600 1 YES ACTIVE 8.2522E+12 18-OCT-04

    SQL> select file#,status from v$datafile where file#=44;

    FILE# STATUS

    ---------- -------

    44 OFFLINE

    SQL> select file#,status from v$datafile_header where file#=44;

    FILE# STATUS

    ---------- -------

    44 OFFLINE

    SQL> alter database datafile 44 online;

    Database altered.

    SQL> select file#,status from v$datafile_header where file#=44;

    FILE# STATUS

    ---------- -------

    44 ONLINE

    SQL> select file#,status from v$datafile where file#=44;

    FILE# STATUS

    ---------- -------

    44 ONLINE

    SO the datafile is online

    The only case in which the offline dropped datafile can not be online is

    when you have added to many datafiles in the database after offline drop

    Cause

    From Documentation

    ====================

    If the database is in NOARCHIVELOG mode, you must specify the DROP clause to take a datafile

    offline. However, the DROP clause does not remove the datafile from the database.

    To do that, you must drop the tablespace in which the datafile resides. Until you

    do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE.

    If the database is in ARCHIVELOG mode, Oracle ignores the DROP keyword.

    展开全文
  • set line 199 col file_name format a50 select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files; alter tablespace temp add tempfile size 100m;...
  • 网上搜了好多都是连同数据文件连同空间一起删除,通过自己学习,整理出一个删除表空间单个数据文件的方法,仅供参考。在没有数据写入空间的时候操作比较好。 第一部、先创建一个零时的空间,用来存放要移出的...
  • 不小心把空间数据文件删除,导致drop tablespace的时候报以下错误: 看了一下网上教程说要执行:shutdown immediate;结果报: 于是换另外一种方式: 4.1.shutdown abort ---强制关闭数据库 显示: -- oracle...
  • 内容包括: 1. 创建空间 2. 改变空间可用性和读写模式 3. 删除表空间 4. 创建和添加数据文件 5. 数据文件可用性 6. 移动和重命名数据文件 7. 删除数据文件 详细代码步骤;
  • 文档中详细叙述了丢失系统数据文件或一般数据文件2种情况下,如何利用RMAN恢复丢失的数据文件
  • ORACLE 收缩空间的数据文件

    千次阅读 2021-05-07 09:19:25
    方法一:在实际的应用中经常...通过下面的SQL语句查看空间总大小及实用大小,然后拼出来一个SQL语句将空间的数据文件重新设定大小select 'alter database datafile ''' || a.file_name || ''' resize ' ||round(...
  • oracle删除表数据(delete与truncate)

    千次阅读 2021-05-07 11:50:02
    中的数据不需要时,则应该删除数据并释放所占用的空间,删除表中的数据可以使用Delete语句或者Truncate语句,下面分别介绍。一、delete语句(1)有条件删除语法格式:delete [from] table_name [where condition...
  • 删除Oracle数据文件/临时文件

    千次阅读 2020-06-25 16:58:43
    有些时候,想删除一个数据文件(临时文件),在10g之前的版本,要删除一个数据文件,必须删除数据文件所属的空间(特殊处理方法除外)。不太懂数据库的朋友直接os级别删除数据文件,导致数据库不能正常启动;稍微等点...
  • oracle数据文件删除命令,方便学习oracle的新手自己处理错误
  • 您可能感兴趣的文章:Oracle删除表数据后的数据恢复详解Oracle7.X 回滚空间数据文件删除处理方法Oracle7.X 回滚空间数据文件删除处理方法Oracle7.X 回滚空间数据文件删除处理方法误删除$ORACLE_HOME/...
  • Oracle删除数据文件

    千次阅读 2021-05-04 10:37:39
    alter tablespace test drop datafile '/oradata/june/test1.dbf' * 第 1 行出现错误: ORA-03262: 文件非空 可以看到,只有非空的数据文件才能进行删除,已经写入数据的数据文件不能进行删除。 尝试 alter database...
  • oracle表空间删除数据文件删除

    千次阅读 2019-04-17 19:38:24
    清理老旧数据的时候,不太熟悉操作,直接把空间了,未删除数据文件, HIS20170927此空间已删除数据文件还在,这时新创建一个空间直接指定到这个数据文件上. create tablespace 空间名 datafile '/app/49382...
  • 1.删除表数据两种方法a. delete * from My_Table;b. truncate table My_Table;2.删除整个drop table My_Table;如何恢复不小心 Drop 掉的呢,其实 Oracle 中也有类似的 "回收站"比如不小心删除 My_Tabledrop...
  • oracle删除表空间及数据文件方法

    千次阅读 2020-06-01 22:23:58
    -删除空的空间,但是不包含物理文件 drop tablespace tablespace_name; --删除非空空间,但是不包含物理文件 drop tablespace tablespace_name including contents; --删除空间,包含物理文件 drop ...
  • 如果是在Oracle10g之前,删除一个空间中的数据文件后,其文件在数据库数据字典中会仍然存在,除非你删除表空间,否则文件信息不会清除。  但是从Oracle10gR2开始,Oracle允许我们彻底删除一个空文件,不留痕迹。 ...
  • 今天做了一个实验,先创建空间及数据文件,然后在操作系统下删除空间的数据文件(前提是该空间已经备份了,方便恢复)。STEP1: 创建空间 ,数据文件, 用户并赋权:SQL> create tablespace TBS1 datafile...
  • 测试的时候向数据库中插入了大量的数据,测试完成后删除了测试用户以及其全部数据,但是数据文件却没有缩小。经查阅资料之后发现这是 Oracle “高水位”所致,那么怎么把这些数据文件的大小降下来呢?解决办法如下:...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 231,098
精华内容 92,439
关键字:

oracle删除表数据文件