This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Feb 26, 2013 2:38 AM by Mihael RSS

truncate command not working

860802 Newbie
Currently Being Moderated
truncate command is not working in 11g on solaris 10. insert,update,delete and all other commands are working but none of the table can be truncated.

{
SQL*Plus: Release 9.0.1.3.0 - Production on Sun Feb 24 11:39:27 2013

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table ism_test ( no number);

Table created.


SQL> insert into ism_test values(2);

1 row created.

SQL> truncate table ism_test;
truncate table ism_test
*
ERROR at line 1:
ORA-00372: file 8 cannot be modified at this time



SQL> select STATUS,ONLINE_STATUS from DBA_DATA_FILES where FILE_ID=8;

STATUS ONLINE_
--------- -------
AVAILABLE OFFLINE

SQL> select TS#,STATUS,ENABLED from V$DATAFILE where FILE#=8;

TS# STATUS ENABLED
---------- ------- ----------
5 OFFLINE READ WRITE

SQL> select STATUS,CONTENTS from DBA_TABLESPACES where TABLESPACE_NAME = (select TABLESPACE_NAME fro
m DBA_DATA_FILES where FILE_ID=8);

STATUS CONTENTS
--------- ---------
ONLINE PERMANENT

}

Edited by: user11976716 on Feb 24, 2013 1:21 AM
  • 1. Re: truncate command not working
    Mihael Pro
    Currently Being Moderated
    SQL> recover datafile 8;
    SQL> alter database datafile 8 online;
  • 2. Re: truncate command not working
    860802 Newbie
    Currently Being Moderated
    as per below query there is nothing on the file 8, and also am able to do any operation on the tables except truncate am confused
    why oracle is referring to a datafile which was taken offline years ago for newly created tables.

    SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE from DBA_SEGMENTS where HEADER_FILE=8;

    no rows selected

    SQL>
    SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE from DBA_EXTENTS where FILE_ID=8;

    no rows selected

    regards,
  • 3. Re: truncate command not working
    Mihael Pro
    Currently Being Moderated
    You can drop empty datafile :

    alter tablespace ... drop datafile 8;
  • 4. Re: truncate command not working
    860802 Newbie
    Currently Being Moderated
    datafile drop is not working since it is a locally managed tablespace.
  • 5. Re: truncate command not working
    Mihael Pro
    Currently Being Moderated
    In documentation:

    To drop a data file or temp file, the data file or temp file:

    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 that was migrated from dictionary managed to locally managed. Dropping a data file from all other read-only tablespaces is supported.

    Cannot be offline.
  • 6. Re: truncate command not working
    860802 Newbie
    Currently Being Moderated
    it is an offline file on locally managed tablespace.

    regards,
  • 7. Re: truncate command not working
    Mihael Pro
    Currently Being Moderated
    Then it is strange and seems to be a bug or inconsistency of data dictionary. Try to open SR.

    For workaround try "REUSE STORAGE" of truncate command.

    Edited by: Mihael on 24.02.2013 5:22
  • 8. Re: truncate command not working
    Aman.... Oracle ACE
    Currently Being Moderated
    This is what the error message says,
    [oracle@edmtr5p7-orcl trace]$ oerr ora 00372
    00372, 00000, "file %s cannot be modified at this time"
    // *Cause:  attempting to modify the contents of a file that cannot be
    //          modified. The file is most likely part of a read only tablespace
    //          but may be in the process of going offline, or the database may
    //          be in the process of closing.
    // *Action: check the status of the file and its tablespace
    [oracle@edmtr5p7-orcl trace]$
    So as you said that the file is offline, what happens when you try to make it online? Can you show us an output from sqlplus indicating that you are able to do DML's on an offline file ?

    Aman....
  • 9. Re: truncate command not working
    860802 Newbie
    Currently Being Moderated
    SQL> create table test_2(no number);

    Table created.

    SQL> insert into test_2 values(1);

    1 row created.

    SQL> truncate table test_2;
    truncate table test_2
    *
    ERROR at line 1:
    ORA-00372: file 8 cannot be modified at this time


    SQL> drop table test_2;

    Table dropped.

    SQL>

    this issue happened after we upgrade from 10g to 11g. the file 8 was offline from 2007 and there was no issue till last month when we are in 10g. none of the tables can
    be truncated on this that specific tablespace. and can make all other dmls insert,update,delete...
    to make it online we dont have all the archives from 2007.
  • 10. Re: truncate command not working
    moreajays Pro
    Currently Being Moderated
    Hi,

    What i understood is , datafile 8 is offline since 2007 & tablespace is Online . You don't have all archives since 2007 to recover it (but obvious) also you are unable to perform any truncate operation on the segments part of this particular Table-space.

    If Yes then you have to migrate objects to new tablespace

    1. Get all Object structure/DDL & export backup from this tablespace
    2. Change Table-space name in dump file created in imp indexfile to new tablespace
    3. Import Data use remapping of tablespace
    4. take care invalid objects & privileges
    5. take trasportable tablespace backup , verify if any dependency exists
    6. Make Tablespace offline & check for any impact
    7. Drop tablespace inculding contents & datafiles


    Thanks,
    Ajay More
    http://www.moreajays.com
  • 11. Re: truncate command not working
    860802 Newbie
    Currently Being Moderated
    Hi,

    Thank you for the response. we are planning to do the same as advised by SR as well.
    but still i am confused why oracle is referring to that offline file even for the tables created today.
    is it a bug?? anyone faced such an issue ...

    regards,
  • 12. Re: truncate command not working
    Aman.... Oracle ACE
    Currently Being Moderated
    I am really not sure how you are saying that in an offline tablespace. you are able to do DML's but not truncate.
    SQL> create tablespace testtbs datafile '/u01/app/oracle/oradata/testtbs.dbf' size 5m;
    
    Tablespace created.
    
    SQL> create table t (a number) tablespace testtbs;
    
    Table created.
    
    SQL> insert into t values(1);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> alter tablespace testtbs offline;
    
    Tablespace altered.
    
    SQL> create table t1 (b number) tablespace testtbs;
    create table t1 (b number) tablespace testtbs
    *
    ERROR at line 1:
    ORA-01542: tablespace 'TESTTBS' is offline, cannot allocate space in it
    
    
    SQL>
    So, back to you, how are you saying that oracle is able to create table and etc in an offline tablespace? This demo was done on 11201. What's your db release? Can you reproduce what you are saying?

    Aman....
  • 13. Re: truncate command not working
    Aman.... Oracle ACE
    Currently Being Moderated
    moreajays wrote:
    Hi,

    What i understood is , datafile 8 is offline since 2007 & tablespace is Online . You don't have all archives since 2007 to recover it (but obvious) also you are unable to perform any truncate operation on the segments part of this particular Table-space.

    If Yes then you have to migrate objects to new tablespace

    1. Get all Object structure/DDL & export backup from this tablespace
    2. Change Table-space name in dump file created in imp indexfile to new tablespace
    3. Import Data use remapping of tablespace
    4. take care invalid objects & privileges
    5. take trasportable tablespace backup , verify if any dependency exists
    6. Make Tablespace offline & check for any impact
    7. Drop tablespace inculding contents & datafiles

    Can you do an export from an offline tablespace?

    Aman....
  • 14. Re: truncate command not working
    860802 Newbie
    Currently Being Moderated
    Hi Aman,

    Tablespace is online and in read write. one datafile (datafile 8) is offline from long before. but after the database upgrade to 11.2.0.3.0 from 10g this issue arised.
    that datafile was offline even in 10g but there was no issue on truncate.
    tablespace was never offline.

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production on solaris 10.

    thank you.

    Edited by: user11976716 on Feb 25, 2013 2:33 AM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points