Shulou(Shulou.com)06/01 Report--

This article is about how sql deletes a data file in a tablespace. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

There are two situations where you may need to "remove" a data file from a tablespace.

1. You accidentally add a data file to a tablespace, or you set the file size too large, so you want to delete it.

two。 You are in the process of recovery and cannot start the database because a data file is missing.

This article mainly deals with the first case, and there are other articles about the recovery of the database that cannot be used due to the loss of data files.

Before we go into detail, we must make it clear that Oracle does not provide tables such as delete tables. Views are the same way to delete data files, which are part of the tablespace, so you cannot "remove" the tablespace.

Before any offline or deletion of the tablespace / data file, make a full backup of the database.

If the data file is the only data file in the table space, you can simply delete the table space:

DROP TABLESPACE INCLUDING CONTENTS

You can run the following command to find out how many data files the tablespace contains.

Select file_name, tablespace_name

From dba_data_files

Where tablespace_name =''

The DROP TABLESPACE command removes the contents of tablespaces, data files, and tablespaces from the Oracle data dictionary. Oracle no longer accesses anything in this tablespace. Physical deletion of files requires the use of operating system commands (Oracle never physically deletes files), depending on your operating system platform, and some operating systems need to be completely shut down by Oracle before they can be deleted (for example, in WINDOWS NT, you need to shut down the database, stop the corresponding services, and in some cases Oracle may also hold file locks).

If your tablespace has multiple data files and you don't need the contents of the tablespace, or you can easily reproduce the contents of the tablespace, you can use the DROP TABLESPACE INCLUDING CONTENTS; command to delete the contents of the tablespace, data file and tablespace from the Oracle data dictionary. Oracle no longer accesses anything in this tablespace. Then recreate the tablespace and re-import the data.

If you have multiple data files in your tablespace and you need to retain the contents of other data files in that tablespace, you must first export out all the contents of the tablespace. To determine what is contained in the table space, run:

Select owner,segment_name,segment_type

From dba_segments

Where tablespace_name=''

Export gives you what you want to keep. If export ends, you can use DROP TABLESPACE tablespace INCLUDING CONTENTS. Permanently delete the contents of the table space, physically delete the data file using operating system commands, recreate the table space according to the desired data file, and import the data to the table space

Note:

The ALTER DATABASE DATAFILE OFFLINE DROP command does not allow you to delete a data file, its purpose is to take the data file offline to delete the tablespace. If you are in archive mode, use ALTER DATABASE DATAFILE OFFLINE DROP instead of OFFLINE DROP. Once the data file is offline, Oracle no longer accesses the contents of the data file, but it is still part of the tablespace. This data file marks OFFLINE in the control file and does not compare it with the control file SCN when the database is started. Keeping the entry of this data file in the control file is convenient for later recovery.

If you don't want to delete tablespaces as described above, there are other solutions.

1. If the reason you want to delete data files is because you have allocated an inappropriate file size, you can consider the RESIZE command.

two。 If you accidentally add a data file that has not allocated space, you can use the

The ALTER DATABASE DATAFILE RESIZE; command makes it less than 5 Oracle block sizes, and if the data file size is smaller than this, Oracle will not extend the data file. In the future, Oracle can be rebuilt to eliminate this incorrect file.

Thank you for reading! This is the end of the article on "how sql deletes a data file in the tablespace". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.