Oracle tablespace growth exception solution: Another example

Source: Internet
Author: User

Oracle tablespace growth exception solution: Another example

Description: The temporary tablespace of a database in the customer environment increases abnormally (with a random date). It may be caused by the scheduled jobs of the following two Oracle databases,
 
Time: around, January 14
 
AM
 
The known growth time of the temporary tablespace is consistent with the error in the trace file. Please analyze the cause and solutions.
 
If you need to log on to the server, please contact Lu yaoqi by phone and he will call to inform you of the relevant information.
 
 
 
The attachment is related logs and a custom tracking data file size log, with an interval of 1 hour.

 

Investigation: We found that the running of an SQL statement in the customer's environment caused an abnormal growth of the temporary tablespace.
 
This SQL statement looks like querying the DB time and DB CPU in the last week.
 
Check with the developer to determine whether the statement can be optimized.

 

Follow-up investigation:
 
Check that the status of CATPROC in dba_registry is invalid. At the same time, dbms_stats that collects statistics is also invalid in dba_objects.
 
Run @? /Rdbms/admin/catproc. SQL compile this component (execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC ;);
 
Then re-compile the dbms_stats package.
 
Recompile the three base tables of the preceding three WRH $ statements.

 

Cause: the problem has been identified. The SQL statement is executed by the SYS user during scheduled task execution at night.
 
The reason for occupying a large temporary tablespace is that the statement involves three tables:
 
WRH $ _ STAT_NAME
 
WRM $ _ SNAPSHOT
 
WRH $ _ SYS_TIME_MODEL
 
Inaccurate statistics lead to a poor execution plan.
 
After statistics are collected manually, the statement can be completed quickly and the occupied temporary tablespace is small.
 
The CATPROC components (Oracle Database Packages and Types) of these two databases are in the INVALID status, causing the dbms_stats stored procedure to be unavailable and thus the statistics cannot be collected automatically. Currently, dbms_stats is available.

Solution to abnormal growth of Oracle tablespace

Oracle Tutorial: instance fault recovery

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.