What were you calling the purge stats proc with?
There is a magic flag DBMS_STATS.PURGE_ALL which should truncate/drop the
partitions but very briefly some time ago I noticed one oddity in what it was
doing wrt indexes on 12.1
https://orastory.wordpress.com/2015/02/16/dbms_stats-purge_stats/
I would ditch all of it. I have almost never needed the history data and to
solve an immediate problem, I wouldn’t be overly concerned about retaining it.
Regards,
Dominic
Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10
From: Mohamed Houri<mailto:mohamed.houri@xxxxxxxxx>
Sent: 16 April 2021 09:37
To: ORACLE-L<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: purge of AWR historical tables
Hello
At one project (Oracle 12.1) two of the AWR historical tables are occupying 1,8
TB.
• WRIS_OPSTAT_HISTHEAD_HISTORY : 81GB
• WRI$_OPSTAT_HISTGRM_HISTORY : 1,012TB with almost 16 billion of rows
The last savtime from WRI$_OPSTAT_HISTGRM_HISTORY is from November 2018
Based upon the first suggestion of Oracle support the team has started by
running the purge stats procedure which they have canceled after 3 hours.
Now the Oracle support is suggesting the following
• Drop indexes for those two tables
• Drop partitions older than 31 days
• Recreate indexes
Have you already been in such situations? If so, what strategy have you adopted
to reduce the size of those two tables as quickly as possible?
Thanks
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Visit My -
Blog<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.hourim.wordpress.com%2F&data=04%7C01%7C%7C8f9274111d7c4ec6619e08d900b2cdea%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637541590225260810%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=jtpnXXBur6e2upm0hOx%2F4Xyjcv7XoAjQj7eM1GfCgiU%3D&reserved=0>
Let's Connect -
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffr.linkedin.com%2Fpub%2Fmohamed-houri%2F11%2F329%2F857%2F&data=04%7C01%7C%7C8f9274111d7c4ec6619e08d900b2cdea%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637541590225270800%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=5AyYcb8cptWBX3qEJW48FAfW8CGWFc8OBKI7MIfZbac%3D&reserved=0>
Linkedin
Profile<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffr.linkedin.com%2Fpub%2Fmohamed-houri%2F11%2F329%2F857%2F&data=04%7C01%7C%7C8f9274111d7c4ec6619e08d900b2cdea%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637541590225280798%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=MxV4CFE22NuxdNsLD0tlhiC%2B%2FoJ7YMPiT1Pig6o8Bp0%3D&reserved=0>
My
Twitter<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2FMohamedHouri&data=04%7C01%7C%7C8f9274111d7c4ec6619e08d900b2cdea%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637541590225280798%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=mXVC8%2BdwVJKSmoPJ%2FP2g19JAp%2FMhmD9%2FNMKE7cqIg2c%3D&reserved=0>
-
MohamedHouri<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2FMohamedHouri&data=04%7C01%7C%7C8f9274111d7c4ec6619e08d900b2cdea%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637541590225280798%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=mXVC8%2BdwVJKSmoPJ%2FP2g19JAp%2FMhmD9%2FNMKE7cqIg2c%3D&reserved=0>