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 ?
[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, 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?
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>
moreajays wrote:Can you do an export from an offline tablespace?
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