Shulou(Shulou.com)05/31 Report--

Editor to share with you oracle10g how to delete data files / tablespaces, I believe that most people do not understand, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. Get enabled

Select 'alter table' | | owner | |'. | | table_name | | 'enable row movement;' from dba_tables where tablespace_name='USERS'

Select 'alter table' | | owner | |'. | | table_name | | 'shrink space CASCADE;' from dba_tables where tablespace_name='USERS'

Oracle delete (release) data file / tablespace process

Production environment: insufficient space in the database, niptest table space 251G, using only 17G

When alter database datafile'. / niptest1' resize 10G;, it is said to be out of range.

Due to frequent changes in the table, the high water level is relatively large.

(high water HWM "High Water Mark": there is no dividing line for block in oracle, which increases with the insert of the data, but does not decrease with the delete of the data, so the full table scan time is not reduced by the delete of the data, on the contrary, the full table scan time may increase because of block removal.)

To delete a tablespace:

1) batch move the tables in the niptest table space to the USERS table space, and then delete the table space niptest

First, look at the move of tables in this table space to other table spaces to prevent data loss.

Select * from dba_tables where tablespace_name='NIPTEST'

Select * from dba_extents where tablespace_name='NIPTEST'

Select * from dba_segments where tablespace_name='NIPTEST'

SELECT 'alter table' | | owner | |. | | table_name | | 'move tablespace USERS;' FROM DBA_tables WHERE TABLESPACE_NAME='NIPTEST'; moves tables to other tablespaces in batches

* move (lowering the high water level)

Advantages: you can move tables to other tablespaces, and you don't need to execute alter table table_name enable row movement when executing commands

Disadvantages: table move will cause the index in the table to fail, and the table will generate row-level locks at the same time. Here, if there is a LOB field in the table, use the following command to achieve tablespace movement: alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lob segment tablespace tablespace_name; can also move lob,index alone to rebuild.

* shrink space

Advantage: the index will not fail when lowering the high water level.

Disadvantages: the table cannot be moved to another table space; the lowering effect of the high water level is not as obvious as that of move; at the same time, executing before executing the command (alter table table_name enable row movement allows row movement) will also produce row-level locks on the table. Shrink consumes more cpu than move and generates a lot of current block so generating huge redo and undo. If there are few indexes in the table, it is recommended to use move to reduce the high water level.

2) after moving the table, it is found that the primary key and index are still in the source tablespace

SELECT * FROM DBA_extents WHERE TABLESPACE_NAME='USERS';-- > View the primary key and index of the original tablespace

Alter index XX rebuild tablespace

-> perform index reconstruction in batch

SELECT 'alter index' | | owner | |'. | | segment_name | | 'rebuild tablespace USERS;' FROM DBA_extents WHERE TABLESPACE_NAME='NIPTEST'; rebuilds the primary key index to other tablespaces in batch

After select * from dba_segments where tablespace_name='NIPTEST'; finishes the above operation, there is still data here. Don't worry about it. It belongs to the Recycle Bin. You can delete the table space directly without modification.

(3) after the tables have been moved, offline drop the data file and then delete the data file

Alter database datafile'/ home/oracle/app/oracle/oradata/kfdb/niptest1' offline drop

-> check the status of the data file

Select status from dba_tablespaces v$datafile where tablespace_name='NIPTEST';-offline

-- > delete the tablespace

Drop tablespace niptest;-- > Delete the tablespace, but not its files

Drop tablespace niptest including contents;-- > Delete tablespaces while deleting tablespace data objects

Drop tablespace niptest including contents and datafiles

-- > when deleting a tablespace, delete the data object and its OS system files together to free up the space. The preceding tablespace cannot be the database default tablespace.

Otherwise, an error will be reported: ORA-12919: Can not drop the default permanent tablespace

(4) how to check what the default tablespace of the database is:

Select * from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE'

If the tablespace you deleted is the database default tablespace, use the command to

Change the database default tablespace: alter database default tablespace users

Execute again: drop tablespace niptest including contents and datafiles

[root@kfdb49 kfdb] # df-hl-- > take a look at the OS system space-tablespace release

-statement to create a tablespace:

Create tablespace niptest datafile'/ home/oracle/app/oracle/oradata/kfdb/niptest1' size 10G autoextend on next XXM maxsize XXm extent management local

Some people will want to create another table space with a niptest of 10G to avoid later imp when the source table space is niptest

: in fact, it is not necessary. If there is no niptest table space in the library, even if the table space of the imp source table is niptest, it will be imported into the user's default table space, and if there is a niptest table space, it will be imported into the niptest table space (if you do not want to own the niptest table space later, do not create it after deleting the niptest)

Recycling unlimited tablespace gives extra permissions niptest tablespace permissions will be imported normally, otherwise an error will be reported

If the extra permissions and the user default tablespace are the same, you can import normally, otherwise you know the table structure to the user default tablespace.

The above is all the contents of the article "how to delete data files / tablespaces by oracle10g". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.