Oracle 10G cleanup Garbage meter

A learning side of the oralce operation of the experience, step is the pit AH.

Cause: A system that has been running for more than 5 years has been slow for customers recently.

Understand the operating environment: windows2003 (16G + 4 Core) + JDK (+) +tomcat and window2003 (4G + duo) + oracle10g

Scene phenomenon: Customer operation out of storage documents, ie a blank for a long time, experience is very bad, with the customer's words: This day to do a single workload worthy of this salary.

Preliminary judgment: Tomcat connection database is very slow, and tomcat various CPU, memory display is relatively normal, so the problem is located in the database, and then found that the database driven by a lot of garbage data table, probably 4~5w Zhang.

So I started to delete the tables because I was not familiar with Oracle, so I took it for granted that the drop table was just like SQL Server.

Select table_name from user_tables where table_name like ' t\_% ' escape ' \ '

First, the table name is queried by a total of 4.5w. Then using a text editor, the 4.5W statement format is generated as follows:

DROP TABLE TableName;

Using a frog to execute a script file 5 hours finally executed, thought everything OK. It was the customer's reminder that he vaguely remembered that the drop table seemed to need to execute a statement, otherwise the table would still be in the database.

After listening to more stunned, degree Niang under, the result is really. After the table was drop, actually just put in a place called trash bin, but also to do garbage collection. Again Niang under:

4.5w table names converted statements:

PURGE TABLE  tablename;

or drop it.

DROP TABLE tablename PURGE;

Using the frog to execute the script file 20-hour statement execution 2.7w, Oralce direct downtime. This time found that the hard drive is full.

At this time customers want to see what occupy space is relatively large, deleted so many tables, still so large space. It's not normal.

SELECT * from Dba_tablespaces;

Found a callThe UNDOTBS01 table space 99% usage, occupies 33G.

Then asked the Niang, degrees Niang said:

This table space is: Roll back the table space, the record used to hold the undo Operation

I take a go, really up posture, before the most is oralce inside write a compatible oralce statement, and really did not do this operation of the work. That Khan ~~~~~~~~~, but fortunately has the degree Niang @#@#￥@

For this reason, the hard disk is full and the table space cannot be increased and no other operations can be performed. Slow, downtime is normal.

--create undo tablespace undotBS2 DataFile ' C:\UNDOTBS1. DBF ' size 100m;--alter system set Undo_tablespace=undotbs2;--drop tablespace undotbs1 including contents;

Execute these statements to restart the Oralce instance. And then execute

SELECT * from Dba_tablespaces;

FoundUNDOTBS2 effective, accounting for 100M.

At this time, the oralce operation has become noticeably faster. Landing system, doing business is also significantly increased speed.

Summarize:

Oralce Clean up the table, you need to add purge parameters to take effect, otherwise just put to the garbage collection station:

DROP TABLE tablename PURGE;

The rollback table also needs to be cleaned up in time, although it can be self-growing, but this table space is too large, affecting the efficiency of ORALCE operation:

--create undo tablespace undotBS2 DataFile ' C:\UNDOTBS1. DBF ' size 100m;--alter system set Undo_tablespace=undotbs2;--drop tablespace undotbs1 including contents;

Oracle 10G cleanup Garbage meter