Oracle deletes (releases) data files/tablespaces

Oracle Delete (release) data file tablespace process production environment: there is not enough space in the database. The niptest tablespace is 251 GB, and only 17 GB is used before alterdatabasedatafile ..... at the time of the niptest1resize10G, the table was out of range and the table was frequently changed. The high water level was relatively large (the high water level HWM "HighWaterMark":

Oracle Delete (release) data files/tablespace process production environment: the database space is insufficient. The niptest tablespace is 251 GB, only 17 GB is used, and then alter database datafile '..... /niptest1 'resize 10G; when it is said that the table is out of the range, because the table changes frequently, the High Water level is relatively large (High Water level HWM "High Water Mark":

Oracle deletes (releases) data files/tablespaces

Production Environment: the database space is insufficient. The space in the niptest table is 251 GB, and only 17 GB is used.

Then alter database datafile '.../niptest1' resize 10G; the request is out of range.

Because the table changes frequently, the high water level value is relatively large.

(High Water level HWM "High Water Mark": Does the block in oracle have a division line used? It will increase with the data insert, but it will not decrease with the data delete, therefore, the full table scan time does not decrease due to data delete. On the contrary, the full table scan time may increase due to block cleanup)

To delete a tablespace:

1) Batch move the tables in the niptest tablespace to the USERS tablespace, and then delete the tablespace (niptest)
First, you can move the table in the tablespace to another tablespace to prevent data loss.

Select * from dba_tables where tablespace_name = 'nottest ';
Select * from dba_extents where tablespace_name = 'nottest ';
Select * from dba_segments where tablespace_name = 'iptest ';
SELECT 'alter table' | owner | '.' | table_name | 'move tablespace USERS; 'FROM DBA_tables WHERE TABLESPACE_NAME = 'nottest'; batch move tables to other tablespaces

******Move (reduce high water level)

Advantage: You can move tables to other tablespaces without executing alter table table_name enable row movement when executing commands.

Disadvantage: Moving the table will cause the index to fail and rebuild the table. At the same time, the table will generate row-level locks ......; if the table contains a LOB field, run the following command to move the table space: alter table owner. table_name move tablespace tablespace_name lob (lob_column) store as lob segment tablespace tablespace_name; you can also separately move lob, index to rebuild

******Shrink space

Advantage: The index will not become invalid when the water level is reduced.

Disadvantage: the table cannot be moved to other tablespaces; the effect of lowering the high water level is not obvious; at the same time, execute the command before executing the command (alter table table_name enable row movement allows rows to move) the table will also generate row-level locks ......., shrink consumes more cpu than move, and generates a lot of current Blocks to generate a huge redo and undo. If there are few indexes in the table, it is recommended to use move to reduce the high level.

2) After moving the table, we 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 batches
SELECT 'alter Index' | owner | '. '| segment_name | 'rebuild tablespace USERS;' FROM DBA_extents WHERE TABLESPACE_NAME = 'nottest'; rebuild primary key indexes to other tablespaces in batches
Select * from dba_segments where tablespace_name = 'nottest'; after completing the above operation, there is still data here. Don't worry, it's the recycle bin. Delete the tablespace without modification.

(3) After the tables are moved, the offline drop data file will be deleted.

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

---> View the status of the data file

Select status from dba_tablespaces v $ datafile where tablespace_name = 'nottest'; ----- offline
--> Delete the tablespace.
Drop tablespace niptest; --> delete a tablespace, but not its files.
Drop tablespace niptest including contents; --> delete a tablespace and delete data objects in the tablespace.
Drop tablespace niptest including contents and datafiles;

--> When deleting a tablespace, delete the data object and its OS files to release the space. The tablespace cannot be the default tablespace of the database.
Otherwise an error is reported: ORA-12919: Can not drop the default permanent tablespace

(4) how to check the default tablespace of the database:
Select * from database_properties where property_name = 'default _ PERMANENT_TABLESPACE ';

If the tablespace you delete is the default tablespace of the database, use the following command

Change the default tablespace of a database: alter database default tablespace users;
Run: drop tablespace niptest including contents and datafiles;

[Root @ kfdb49 kfdb] # df-hl --> check the OS space --- release the tablespace

------ Statement for creating 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 a new tablespace with the name of the master test as 10 Gb, so that the source table's tablespace is not in the later stage of the imp.

: Actually, this is not required. If the database does not contain the "niptest" tablespace, even if the table space in the imp source table is "niptest", it will be imported to the user's default tablespace, if you have a tablespace named "niptest", it will be imported to the tablespace named "niptest". (If you do not want to have a tablespace named "niptest" in the future, you must remove it from the root and delete it before creating it)

Revoke the unlimited tablespace and grant the additional permission "niptest". The tablespace permission will be imported normally. Otherwise, an error will be reported.

If the additional permission is the same as the user's default tablespace, it can be imported normally. Otherwise, the table structure is known to the user's default tablespace.

Experiment reference: http://space.itpub.net/28602568/viewspace-760169#xspace-tracks

Oracle deletes (releases) data files/tablespaces

Production Environment: the database space is insufficient. The space in the niptest table is 251 GB, and only 17 GB is used.

Then alter database datafile '.../niptest1' resize 10G; the request is out of range.

Because the table changes frequently, the high water level value is relatively large.

(High Water level HWM "High Water Mark": Does the block in oracle have a division line used? It will increase with the data insert, but it will not decrease with the data delete, therefore, the full table scan time does not decrease due to data delete. On the contrary, the full table scan time may increase due to block cleanup)

To delete a tablespace:

1) Batch move the tables in the niptest tablespace to the USERS tablespace, and then delete the tablespace (niptest)
First, you can move the table in the tablespace to another tablespace to prevent data loss.

Select * from dba_tables where tablespace_name = 'nottest ';
Select * from dba_extents where tablespace_name = 'nottest ';
Select * from dba_segments where tablespace_name = 'iptest ';
SELECT 'alter table' | owner | '.' | table_name | 'move tablespace USERS; 'FROM DBA_tables WHERE TABLESPACE_NAME = 'nottest'; batch move tables to other tablespaces

******Move (reduce high water level)

Advantage: You can move tables to other tablespaces without executing alter table table_name enable row movement when executing commands.

Disadvantage: Moving the table will cause the index to fail and rebuild the table. At the same time, the table will generate row-level locks ......; if the table contains a LOB field, run the following command to move the table space: alter table owner. table_name move tablespace tablespace_name lob (lob_column) store as lob segment tablespace tablespace_name; you can also separately move lob, index to rebuild

******Shrink space

Advantage: The index will not become invalid when the water level is reduced.

Disadvantage: the table cannot be moved to other tablespaces; the effect of lowering the high water level is not obvious; at the same time, execute the command before executing the command (alter table table_name enable row movement allows rows to move) the table will also generate row-level locks ......., shrink consumes more cpu than move, and generates a lot of current Blocks to generate a huge redo and undo. If there are few indexes in the table, it is recommended to use move to reduce the high level.

2) After moving the table, we 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 batches
SELECT 'alter Index' | owner | '. '| segment_name | 'rebuild tablespace USERS;' FROM DBA_extents WHERE TABLESPACE_NAME = 'nottest'; rebuild primary key indexes to other tablespaces in batches
Select * from dba_segments where tablespace_name = 'nottest'; after completing the above operation, there is still data here. Don't worry, it's the recycle bin. Delete the tablespace without modification.

(3) After the tables are moved, the offline drop data file will be deleted.

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

---> View the status of the data file

Select status from dba_tablespaces v $ datafile where tablespace_name = 'nottest'; ----- offline
--> Delete the tablespace.
Drop tablespace niptest; --> delete a tablespace, but not its files.
Drop tablespace niptest including contents; --> delete a tablespace and delete data objects in the tablespace.
Drop tablespace niptest including contents and datafiles;

--> When deleting a tablespace, delete the data object and its OS files to release the space. The tablespace cannot be the default tablespace of the database.
Otherwise an error is reported: ORA-12919: Can not drop the default permanent tablespace

(4) how to check the default tablespace of the database:
Select * from database_properties where property_name = 'default _ PERMANENT_TABLESPACE ';

If the tablespace you delete is the default tablespace of the database, use the following command

Change the default tablespace of a database: alter database default tablespace users;
Run: drop tablespace niptest including contents and datafiles;

[Root @ kfdb49 kfdb] # df-hl --> check the OS space --- release the tablespace

------ Statement for creating 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 a new tablespace with the name of the master test as 10 Gb, so that the source table's tablespace is not in the later stage of the imp.

: Actually, this is not required. If the database does not contain the "niptest" tablespace, even if the table space in the imp source table is "niptest", it will be imported to the user's default tablespace, if you have a tablespace named "niptest", it will be imported to the tablespace named "niptest". (If you do not want to have a tablespace named "niptest" in the future, you must remove it from the root and delete it before creating it)

Revoke the unlimited tablespace and grant the additional permission "niptest". The tablespace permission will be imported normally. Otherwise, an error will be reported.

If the additional permission is the same as the user's default tablespace, it can be imported normally. Otherwise, the table structure is known to the user's default tablespace.

Experiment reference: http://space.itpub.net/28602568/viewspace-760169#xspace-tracks