Groovy DBA

  set pagesize 0 set long 90000 set feedback off set echo off  spool scott_schema.sql  connect scott/tiger; SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)      FROM USER_TABLES u; SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)      FROM USER_INDEXES u; spool off;

 to get create time and alter time for DB2 tables: db2 "select tabname, create_time from syscat.tables where tabschema = <schema> and tabname = <table> with ur" db2 "select tabname, alter_time from syscat.tables where tabschema = <schema> and tabname = <table> with ur" select * from syscat.tables where tabname = 'XTMW_CHANNELADVISOR' with ur; select tabname, create_time from syscat.tables where tabschema = 'WCS' and  tabname = 'APPEASEMENT' with ur

SELECT TABNAME  FROM SYSCAT.TABLES  WHERE TABSCHEMA = 'WCS';    db2 list tables for schema wcs show detail; Column headings  Table/View    Type       Creation time           Schema  Table/View                                                                                                                       Schema                                                                                    ...

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 ---------------------------                       ...

  after running `opatch util cleanup', you can do more cleanup: --- begin quote --- Additionally, further cleanup of the $ORACLE_HOME/.patch_storage is possible if there are directories from patches applied to previous versions. This can be done manually as follows: 1.  run command: $ opatch lsinventory 2.  Remove all the sub-directories from $ORACLE_HOME/.patch_storage that are not present in the list of installed patches. Directory names would be prefaced with the patchid for example: 13343438_<timestamp> eg: >$ORACLE_HOME/OPatch/opatch lsinventory > lsinventorytxt.txt oracle-psfststdb602.tmw.com::/usr/opt/app/oracle/product/19.3.0/.patch_storage>ls -lrt total 144 drwxr-xr-x.  3 oracle dba    74 Apr 18  2019 29517242_Apr_17_2019_23_27_10 drwxr-xr-x.  3 oracle dba    74 Apr 18  2019 29585399_Apr_9_2019_19_12_47 drwxr-xr-x.  4 oracle dba    87 Jun  8  2021 32545013_Apr_16_2021_07_40_05 drwxr-x...