Delete data files in oracle databases [delete a data file physically but its information

Source: Internet
Author: User
There is no simple way to delete the data file of a tablespace. The only way is to delete the entire defined tablespace. The steps are as follows (provided that the data in this data file is unnecessary ): because alterdatabasedatafilenameofflinedrop is used, a data file can also be physically deleted, but its information is still recorded in the data dictionary (no impact

There is no simple way to delete the data file of a tablespace. The only way is to delete the entire defined tablespace. The steps are as follows (provided that the data in this data file is unnecessary ): because alter database datafile 'name' offline drop can also be used to physically delete a data file, but its information is still recorded in the data dictionary (no effect

There is no simple way to delete the data file of a tablespace. The only way is to delete the entire defined tablespace. The steps are as follows (provided that the data in this data file is unnecessary ): because alter database datafile 'name' offline drop can also be used to physically delete a data file, but its information is still recorded in the data dictionary (without affecting the operation of the database ).

If the database runs in non-archive mode:

1. MOUNT database-startup mount
2. Delete the data file-alter database datafile xxx offline drop
3. OPEN database-alter database open
4. view all objects in the tablespace:
Select owner, segment_name, segment_type
From dba_segments
Where tablespace_name = 'tbs _ name'
5. Export all objects in the tablespace ------ use the exp command
6. Delete the tablespace-drop tablespace tbs_name including contents
7. Delete all physical data files in the tablespace Delete the physical datafiles belonging to the tablespace
8. Recreate the tablespace and import the exported DMP file.


If the database is running in archive mode:

1. MOUNT database-startup mount
2. Delete the data file-alter database datafile xxx offline
(Note: The offline data file, which is still part of the database, is only marked as offline in the control file .)
3. Delete physical data files at the operating system level
4. OPEN database-alter database open
5. The following operations can be performed:
Export the objects of the tablespace
Delete a tablespace
Recreate the tablespace and import the object

If the database runs in archive mode and data files are backed up:

1. MOUNT the database
2. OFFLINE data file: alter database datafile xxx offline;
3. Copy the backup data file to the original data file location.
4. Put all archiving logs from the backup data files to the archive directory.
5. recover the data file: recover automatic datafile xxx (enter the full path name)
6. Then the ONLINE data file: alter database datafile xxx online;
7. OPEN the database: alter database open;
8. perform a full backup of database shutdown.


Note:

Used for remote cold recovery


Refer:

Www5.1616.net/

Delete data files oracle Google

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.