ORACLE database space organizing experience
- 2020-05-30 21:13:51
- OfStack
Recently due to the unit database hard disk space shortage, sorting out the time to check a lot of articles, also carried out a test, sorting out some experience for your reference.
First of all, I read an article on the Internet about how to use Shrink Undo table space to free up excessive space. After reading 1 of the above instructions, I found that the UNDO space on our hard disk also takes up 2G. Therefore, I thought of clearing this space first to solve the urgent problem, so I immediately tested it.
The test passed, but there was one problem, drop tablespace undotbs1 including contents; Command, undotbs1.dbf file is not cleared, the hard disk is still full. (later I wondered if I could use drop tablespace undotbs1 including contents and datafiles; ? Simply delete the files on your hard drive. The article talks about waiting for all UNDO SEGMENT OFFLINE tablespaces in the original undo tablespace. I don't know how to do this, nor can I see the results of the above instructions. But using the command check, make sure the UNDO tablespace has been transferred to undotbs2.
In order to delete the UNDO file, a test was performed.
selecct name from v $datafile; The undotbs1.dbf file is no longer in the listed data files.
Stop the ORACLE service and stop listening. Move the undotbs1.dbf file to another space. Open the ORACLE service and turn on listening. Service is normal. Prove that the file can be deleted.
First of all, I read an article on the Internet about how to use Shrink Undo table space to free up excessive space. After reading 1 of the above instructions, I found that the UNDO space on our hard disk also takes up 2G. Therefore, I thought of clearing this space first to solve the urgent problem, so I immediately tested it.
The test passed, but there was one problem, drop tablespace undotbs1 including contents; Command, undotbs1.dbf file is not cleared, the hard disk is still full. (later I wondered if I could use drop tablespace undotbs1 including contents and datafiles; ? Simply delete the files on your hard drive. The article talks about waiting for all UNDO SEGMENT OFFLINE tablespaces in the original undo tablespace. I don't know how to do this, nor can I see the results of the above instructions. But using the command check, make sure the UNDO tablespace has been transferred to undotbs2.
In order to delete the UNDO file, a test was performed.
selecct name from v $datafile; The undotbs1.dbf file is no longer in the listed data files.
Stop the ORACLE service and stop listening. Move the undotbs1.dbf file to another space. Open the ORACLE service and turn on listening. Service is normal. Prove that the file can be deleted.