Data Loss Prevention

 View Only
  • 1.  How to delete a datafile in tablespace - Oracle 11g

    Posted Jan 25, 2018 03:59 PM

    Hello,

    By mistake I create a new datafile in the USERS tablespace and after to restart the enforce and oracle services dlp show me this .

    Its possible to delete this datafile ?



  • 2.  RE: How to delete a datafile in tablespace - Oracle 11g

    Posted Feb 19, 2018 02:38 PM

    WARNING: As with any critical operation like dropping datafiles, ensure that you have a full backup of the database before running the statements and commands highlighted in this article.

    The following example drops an empty datafile O1_MF_USERS_6LD56LRR_.DBF that belongs to the USERS tablespace.

    SQL> alter tablespace users drop datafile 'C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_6LD56LRR_.DBF';
    
    Tablespace altered.

    Use the example above to match your needs. AGAIN, MAKE SURE YOU HAVE A BACKUP BEFORE RUNNING THIS QUERY. 



  • 3.  RE: How to delete a datafile in tablespace - Oracle 11g

    Posted Feb 22, 2018 03:10 PM

    Thank for the example, I have a full backup of the oracle files daily.

    The USERS tablespace what info save?



  • 4.  RE: How to delete a datafile in tablespace - Oracle 11g

    Posted Feb 23, 2018 07:53 AM

    Incidents that are 4KB in size, or less, are retained in the USERS tablespace.



  • 5.  RE: How to delete a datafile in tablespace - Oracle 11g

    Posted Feb 23, 2018 10:12 AM

    Right. And incidents that are larger than 4KB, as well as incident binary attachments, are stored in the LOB_TABLESPACE tablespace. 



  • 6.  RE: How to delete a datafile in tablespace - Oracle 11g

    Posted Apr 13, 2018 01:29 PM

    Once Oracle writes to a datafile deleting that file becomes very difficult.  Looks like you've created a LOB dbf in the wrong directory.  It would probably be best to move the datafile rather than blowing it away.  Check out this:

    https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm#ADMIN11430

     

    Also, with Oracle 12c you can leverage the cool feature of moving the datafile while online!  You could use a simple statement:

    See: https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files

    and look under "Datafiles":

     

    Oracle 12c includes the ALTER DATABASE MOVE DATAFILE command, which performs an online move of a datafile.

    SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' TO '/tmp/system01.dbf';

    HTH

    Mark