Clean up junk tables for oracle 10G and oracle10 G

Clean up junk tables for oracle 10G and oracle10 G

An Oracle O & M experience that I learned and worked on is a pitfall step by step.

Cause: a set of systems that have been running for five years have been slow to be reflected by customers in the near future.

I learned about the runtime environment: windows2003 (16G + 4 Core) + jdk (32) + tomcat and window2003 (4G + dual-core) + oracle10G

On-site phenomenon: when the customer operates the warehouse receiving and receiving documents, the ie blank space lasts for a long time and the experience is very poor. In the customer's words, the workload of making a ticket on this day is up to the salary.

Preliminary judgment: tomcat is very slow to connect to the database, and tomcat cpu and memory display are relatively normal, so the problem is located on the database, and then found that the database end is composed of a lot of junk data tables, about 4 ~ 5 million sheets.

So I began to delete these tables first, because I am not very familiar with oracle, So I naturally want to do it like SQL server, drop table.

select table_name from user_tables where table_name like 'T\_%' escape '\'

First, a total of table names are queried through the statement. Then, a statement format is generated in the text editor as follows:

DROP TABLE tablename；

It took five hours for the frog to execute the script file and thought everything was okay. This is a reminder from the customer that He vaguely remembers, so that the drop table seems to need to execute a statement, or the table is still in the database.

After hearing this, the result is true. After the table is dropped, it is actually placed in a place called a garbage bin, and garbage collection is also required. Again:

4.5w table names are converted into statements:

PURGE TABLE  tablename;

Or drop

DROP TABLE tablename PURGE；

When the frog executes the script file for 20 hours and the statement is executed for 2.7, oralce goes down directly. At this time, the hard disk is full.

At this time, the customer wants to see what occupies a large amount of space. After deleting so many tables, the space is still so large. Abnormal.

select * from dba_tablespaces;

A tablespace named UNDOTBS01 is found to have a usage of 99%, occupying 33 GB.

Then I asked du Niang, and Du Niang said:

This tablespace is a rollback tablespace used to store records of the Undo operation.

I went here, and it was really a rising posture. In the past, at most I wrote a statement compatible with oralce in oralce, and I have never done this kind of O & M work. That sweat ~~~~~~~~~, Fortunately, du Niang @ ###￥ @

The reason is that the hard disk is full, and the tablespace cannot grow and other operations cannot be performed. It is normal to be slow or shut down.

--create undo tablespace undotBS2 datafile 'C:\UNDOTBS1.DBF' size 100m  autoextend on next 100m;--alter system set undo_tablespace=undotBS2;--drop tablespace undotbs1 including contents;

Run these statements to restart the oralce instance. Then execute

select * from dba_tablespaces;

It is found that UNDOTBS2 takes effect, accounting for 100 MB.

At this time, the oralce operation is significantly faster. Logging into the system and doing business are also significantly faster.

Summary:

Oralce to clear the table, you must add the PURGE parameter to take effect, otherwise it will only be placed in the recycle bin:

DROP TABLE tablename PURGE；

Rollback tables also need to be cleaned up in a timely manner. Although the table space can be increased by itself, this tablespace is too large, which affects the operational efficiency of oralce:

--create undo tablespace undotBS2 datafile 'C:\UNDOTBS1.DBF' size 100m  autoextend on next 100m;--alter system set undo_tablespace=undotBS2;--drop tablespace undotbs1 including contents;