Problem Description:
Today, in Saturday, I didn't expect to receive a call from a developer who said it was necessary to add an index field to the partition table (this is a large partition table with more than 1T of data more than 100 million rows, 1 days to do 1 partitions a total of 190 partitions), perhaps because it is a rookie bar, Oracle did not understand enough, so you will encounter a variety of problems, There are a lot of things to learn. I think it will take a long time to delete the rebuild index for the entire table, so think about whether you can add a field index to a single partition, check some information on the Internet, find no information, and then call the developer, whose advice is to add a field using the Pl/sql tool (pl/ SQL in the large amount of data to increase the field index to use caution, easy to cause the temp file abnormally large , so I did, the beginning is very slow reaction, after some time to pl/. SQL error, prompt for a death. So check Oracle found login does not go in, and then check the disk, space full, du-sh/u01/* listed temp01.dbf more than 40 g.
Environment Description:
Rhel6.2+oracle11_r2
Partition:/200g,/u01 50G,/archvielog 500G,/app 3.0T
Zoning also from the planning of disk space use, in fact, before planning to think that the/u01 space is too small, this thought 50G is enough, but later proved to be wrong, mainly temp,undo files occupy too much space (some methods can be free from the creation of undo, such as: 1, insert/*+ Append/into TABLE_NAME, 2, set the table to not write the log, the temp file must be free of the method has not yet to study
Workaround:
1, if the establishment is LVM, you can enlarge the size of the partition (specific operations to find Google or the Niang)
2, create a temporary table space and set it to the user's default temporary tablespace.
3, copy the Oracle directory below the/U01 to a larger partition (this is done using a mobile directory for soft link implementation).
1), Cp-r-p/u01/oracle/u01_bak/#-r directory-P reserved permission Note: Copy with root
Watch-n 1 Du-sh/u01_bak # This command allows you to view the copy.
2), Rm-rf/u01/oracle #确定拷贝完, or move it to another place
3), ln-s/u01_bak/oracle/u01/oracle #这是重要的一步建立软链接
Note: RM-RF delete soft link name plus "/" and do not add "/" difference, add '/' to delete soft links and soft link directory of all the files below, here must be noted.
=========================================================================================================== ==
Copyright, the article is allowed to reprint, but must be linked to the source address, otherwise held legal responsibility! Thank you for your cooperation.
Qq:164798858@qq.com
Sina:weibo.com/kaijunfeng
yahoo:fffygapl@yahoo.com.cn