Groovy DBA

RMAN> crosscheck backup completed between '22-FEB-22' and '24-FEB-22'; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=4431 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=908 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=2228 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=2669 device type=DISK crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/usr/opt/app/dbdump/hot_backup/fsprod/FSPROD_Inc0_T20220222_s11188_p1_1097359666.bak RECID=11175 STAMP=1097359670 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/usr/opt/app/dbdump/hot_backup/fsprod/FSPROD_Inc0_T20220222_s11187_p1_1097359666.bak RECID=11178 STAMP=1097359670 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/usr/opt/app/dbdump/hot_backup/fsprod/FSPROD_Inc0_T20220222_s11190_p1_1097359666.bak RECID=11176 STAMP=1097359668 crosschecked backup piece: found ...

CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/usr/opt/app/oracle/admin/gentest3/temp01/temp_01.dbf' SIZE 10m; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW; Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old temporary tablespace ( i.e. TEMP ) and kill them. set linesize 200 col STATUS format a10 col username format a15 col osuser format a10 SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#, a.username,a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr; Provide above inputs to following query, and kill session’s. alter system kill session 'SID_NUMBER, SERIAL#NUMBER'; For example: alter system kill session '59,57391'; DROP TABLESPACE TEMP including contents and datafiles; CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/usr/opt/app/oracle/admin/gentest3/temp01/temp01.dbf' SIZE 10m autoextend on next 10m maxsize 32024M; ALTER DATABASE DEFAULT TEMPORARY TABLE...

Three  options: (1):  purge the optimizer statistics manually; begin for i in reverse 10..100 loop dbms_stats.purge_stats(sysdate-i); end loop; end; / This takes time? Then let's see our 3rd solution alternative; (2)  use DBMS_STATS.PURGE_ALL to purge all our historical objects statistics. DBMS_STATS.PURGE_ALL just truncates the related table, so all purge activity takes only 5-10 seconds to finish. The tradeoff of this type of purging can be the inability to make forensic sql performance analysis and corrections accordingly. change the retention period from the default(31 days) to 15 days. Initially, the tables are fully loaded. The SYSAUX tablespace is 41 gigs(which is used almost fully) and the WRI$_OPTSTAT_HISTHEAD_HISTORY s almost 14 gigs as seen  below; set linesize 1000; select dbms_stats.get_stats_history_ retention from dual; GET_STATS_HISTORY_RETENTION ---------------------------                       ...