ORA-23412: master table's primary key columns have changed

On Snapshot DB  - try I:
DROP MATERIALIZED VIEW GSM_SERVICE_MERGED_NETWORKS_SS
CREATE MATERIALIZED VIEW GSM_SERVICE_MERGED_NETWORKS_SS
REFRESH FAST ON DEMAND
NEXT NULL
AS
SELECT merge_network_id, item_id, active_ind
FROM GSM_SERVICE_MERGED_NETWORKS@TONPPROV;

CREATE MATERIALIZED VIEW GSM_SERVICE_MERGED_NETWORKS_SS
*
ERROR at line 1:
ORA-23412: master table's primary key columns have changed


On Master DB:
DROP MATERIALIZED VIEW LOG ON GSM_SERVICE_MERGED_NETWORKS; 
CREATE MATERIALIZED VIEW LOG ON GSM_SERVICE_MERGED_NETWORKS TABLESPACE IGT_TABLE;

On Snapshot DB - try II:
CREATE MATERIALIZED VIEW GSM_SERVICE_MERGED_NETWORKS_SS
REFRESH FAST ON DEMAND
NEXT NULL
AS
SELECT merge_network_id, item_id, active_ind
FROM GSM_SERVICE_MERGED_NETWORKS@TONPPROV;

Done

Oracle Space Stats sql

COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999
SET LINESIZE 140
SET PAGESIZE 200
COL tablespace_name FOR A30
COL file_name FOR A60
COL Mb FOR 9999999999999
COL MAX_MB FOR 9999999999999

spool tbs.txt

SELECT TABLESPACE_NAME, 
  (MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB,                                   
  MAX(USED_SPACE) AS USED_SPACE_MB, 
  MAX(MAX_SPACE) AS MAX_SPACE_MB, 
  ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT, 
  CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE 
FROM ( 
SELECT tablespace_name,  
   ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE, 
   0 AS MAX_SPACE, 
   0 AS USED_SPACE   
 FROM DBA_FREE_SPACE
 WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX' 
 GROUP BY tablespace_name  
 UNION ALL 
 SELECT tablespace_name, 
   0 AS FREE_SPACE, 
   ROUND( SUM(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE, 
     0 AS USED_SPACE 
 FROM DBA_DATA_FILES
  WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX' 
 GROUP BY tablespace_name 
 UNION ALL 
 SELECT tablespace_name, 
     0 AS FREE_SPACE, 
     0 AS MAX_SPACE, 
     ROUND(SUM(bytes/1024/1024)) AS USED_SPACE 
  FROM DBA_SEGMENTS
 WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX' 
GROUP BY tablespace_name 
) 
GROUP BY tablespace_name;

spool off



spool segments.txt

SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%TABLE%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%INDEX%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;

SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSTEM%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSAUX%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;

spool off


spool datafile.txt
SELECT tablespace_name, file_name, ROUND(bytes/1024/1024) AS Mb, ROUND(maxbytes/1024/1024) AS MAX_MB
  FROM DBA_DATA_FILES;

spool off

Code example: String replacement in PL/SQL

CREATE OR REPLACE PACKAGE QA_UTIL AS
  PROCEDURE imsi_update;
END QA_UTIL;

/

CREATE OR REPLACE PACKAGE BODY QA_UTIL AS
-------------------------------------------
  PROCEDURE imsi_update AS

    CURSOR imsi_rows_cur IS
    SELECT QAT2_TESTS_RAN.*, rowid FROM QAT2_TESTS_RAN
     WHERE generalrange1 LIKE '% IMSI = %'
       AND generalrange1 NOT LIKE '% IMSI = ''%'
       AND test_name LIKE 'VFES%';


    v_generalrange      QAT2_TESTS.generalrange1%TYPE;
    v_generalrange_out  QAT2_TESTS.generalrange1%TYPE;
    v_part_1_ind        NUMBER;
    v_part_1            QAT2_TESTS.generalrange1%TYPE;

    v_part_imsi_ind     NUMBER;
    v_part_imsi         QAT2_TESTS.generalrange1%TYPE;

    v_part_2_ind        NUMBER;
    v_part_2         QAT2_TESTS.generalrange1%TYPE;

  BEGIN
    FOR imsi_rows_rec IN imsi_rows_cur LOOP
      --select * from DEBUG_IPN_W_EDR where IMSI = 214010000000010 and DESCRIPTION='GPRS' and ACTION_TYPE_NAME= 'RELAY' and ACTION_REASON_NAME='Sub: SubInact'
      v_generalrange := imsi_rows_rec.generalrange1;

      v_part_1_ind := INSTR(v_generalrange,'IMSI = ') + LENGTH ('IMSI = ')-1;
      v_part_1 := SUBSTR(v_generalrange,0,v_part_1_ind);

      v_part_imsi_ind := v_part_1_ind+1;
      v_part_imsi  := SUBSTR(v_generalrange,v_part_imsi_ind,LENGTH('214010000000010'));

      v_part_2_ind := v_part_imsi_ind + LENGTH('214010000000010');
      v_part_2 := SUBSTR(v_generalrange,v_part_2_ind);

      v_generalrange_out := v_part_1||''''||v_part_imsi||''''||v_part_2;

      UPDATE QAT2_TESTS_RAN 
         SET generalrange1 = v_generalrange_out 
       WHERE QAT2_TESTS_RAN.rowid = imsi_rows_rec.rowid;
      commit;

    END LOOP;

  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END imsi_update;
-------------------------------------------
END QA_UTIL;

/

SQLSERVER, change Database Isolation Level. Change database Collation. sp_change_users_login

==============================
Changing Database Isolation Level
==============================
How to resolve deadlock on Database level when changing Database Isolation Level

USE Jiratempdb
SELECT CASE transaction_isolation_level 
  WHEN 0 THEN 'Unspecified' 
  WHEN 1 THEN 'ReadUncommitted' 
  WHEN 2 THEN 'ReadCommitted' 
  WHEN 3 THEN 'Repeatable' 
  WHEN 4 THEN 'Serializable' 
  WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
  FROM sys.dm_exec_sessions 
where session_id = @@SPID

result: ReadCommitted
USE Jiratempdb
SELECT is_read_committed_snapshot_on 
  FROM sys.databases 
 WHERE name = 'Jiratempdb'

result = 0

In case of a deadlock error:

use master
go

EXEC sp_who2
SELECT * from sys.dm_exec_requests 
 WHERE command like 'ATRER%' 
ORDER BY command

kill <pid>


SET DEADLOCK_PRIORITY HIGH
GO

ALTER DATABASE XXX SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE XXX set READ_COMMITTED_SNAPSHOT ON
GO

ALTER DATABASE XXX SET MULTI_USER
GO

USE Jiratempdb
SELECT is_read_committed_snapshot_on 
  FROM sys.databases 
 WHERE name = 'Jiratempdb'

result = 1



==============================
Changing Collation
==============================

USE master;  
GO

--Verify the collation setting.
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'jiradbdev';  
GO  
jiradbdev; SQL_Latin1_General_CP1_CI_AS

ALTER DATABASE XXX SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE XXX COLLATE SQL_Latin1_General_CP437_CI_AI;
GO  

ALTER DATABASE XXX SET MULTI_USER
GO

  
--Verify the collation setting.  
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'jiradbdev';  
GO  
jiradbdev; SQL_Latin1_General_CP437_CI_AI

==============================
sp_change_users_login 'AUTO_FIX', 'my_user'
==============================
This command Maps database user to a SQL Server login.

It will be needed, if migrating database from InstanceA to InstanceB, via detach+attach, and using same user, which is a local user on InstanceA and on InstanceB.

Auto_Fix option -  Links a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. 

If a login with the same name does not exist, one will be created. 

Call to TRUNCATE table from crontab

oracle@my_server:~/scripts>% crontab -l
0 5 * * * /usr/local/etc/starhome/oracle_purge/oracle_purge.sh igt
5 5 * * * /software/oracle/oracle/scripts/clean_IPN_IBR_CELL_REPORT_DATA.sh
6 6 * * * /software/oracle/oracle/scripts/clean_IPN_SUBSCRIBER_USAGE.sh

======================================
Example A - Truncate non active table
======================================

clean_IPN_SUBSCRIBER_USAGE.sh
#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
cd /software/oracle/oracle/scripts/

echo ===================
echo RUS_BEELN_IPNQQ
echo ===================
sqlplus RUS_BEELN_IPNQQ/RUS_BEELN_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo UKR_KYIVS_IPNQQ
echo ===================
sqlplus UKR_KYIVS_IPNQQ/UKR_KYIVS_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo KAZ_KARTE_IPNQQ
echo ===================
sqlplus KAZ_KARTE_IPNQQ/KAZ_KARTE_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo KGZ_SKYMO_IPNQQ
echo ===================
sqlplus KGZ_SKYMO_IPNQQ/KGZ_SKYMO_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo PAK_MOBIL_IPNQQ
echo ===================
sqlplus PAK_MOBIL_IPNQQ/PAK_MOBIL_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo BGD_BANGA_IPNQQ
echo ===================
sqlplus BGD_BANGA_IPNQQ/BGD_BANGA_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo DONE
echo ===================


call_to_handle_ipn_subs_usage_local.sql
BEGIN
  handle_ipn_subs_usage_local;
END;
/
EXIT;


handle_ipn_subs_usage_local;
CREATE OR REPLACE PROCEDURE handle_ipn_subs_usage_local IS
  v_table_name  VARCHAR2(30);
  v_sql_str     VARCHAR2(1000);
  v_module_name VARCHAR2(100);
  v_msg_text    VARCHAR2(1000);
BEGIN
  v_module_name := 'handle_ipn_subs_usage_local';

  v_msg_text := 'Starting';
  INSERT INTO SGA_W_LOG(PROCEDURE_NAME, DATA, TS_LAST_MODIFIED)
  VALUES (v_module_name,v_msg_text, SYSDATE);
  commit;

 v_sql_str := 'ALTER SESSION SET DDL_LOCK_TIMEOUT = 600';
  EXECUTE IMMEDIATE v_sql_str;

  v_sql_str := 'TRUNCATE TABLE TABLE_XXX';
  SELECT table_name INTO v_table_name FROM USER_SYNONYMS WHERE synonym_name = 'SUBSCRIBER_USAGE_INACTIVE';
  v_sql_str := REPLACE(v_sql_str,'TABLE_XXX',v_table_name);

  v_msg_text := 'Running SQL : '||v_sql_str;
  INSERT INTO SGA_W_LOG(PROCEDURE_NAME, DATA, TS_LAST_MODIFIED)
  VALUES (v_module_name,v_msg_text, SYSDATE);
  commit;

  EXECUTE IMMEDIATE v_sql_str;

  v_msg_text := 'Cleanup Finished Successfully. Run SQL : '||v_sql_str;
  INSERT INTO SGA_W_LOG(PROCEDURE_NAME, DATA, TS_LAST_MODIFIED)
  VALUES (v_module_name,v_msg_text, SYSDATE);
  commit;

EXCEPTION
  WHEN OTHERS THEN
    v_msg_text := 'Unexpected Error: '||SQLERRM;
    INSERT INTO SGA_W_LOG(PROCEDURE_NAME, DATA, TS_LAST_MODIFIED)
    VALUES (v_module_name,v_msg_text, SYSDATE);
    commit;
END;
/


======================================
Example B - Delete old data from a table
======================================

clean_IPN_IBR_CELL_REPORT_DATA.sh

#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
cd /software/oracle/oracle/scripts/
sqlplus system/Xen86Pga@igt @clean_IPN_IBR_CELL_REPORT_DATA.sql

clean_IPN_IBR_CELL_REPORT_DATA.sql
CREATE TABLE KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK AS SELECT * FROM KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE 1=2;
ALTER TABLE KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK NOLOGGING;
INSERT /*+ APPEND */INTO KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK
SELECT * FROM KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE TS_START > SYSDATE -2;
commit;
TRUNCATE TABLE KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA;
INSERT /*+ APPEND */INTO KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA
SELECT * FROM KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;
commit;
DROP TABLE KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;

CREATE TABLE UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK AS SELECT * FROM UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE 1=2;
ALTER TABLE UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK NOLOGGING;
INSERT /*+ APPEND */INTO UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK
SELECT * FROM UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE TS_START > SYSDATE -2;
commit;
TRUNCATE TABLE UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA;
INSERT /*+ APPEND */INTO UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA
SELECT * FROM UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;
commit;
DROP TABLE UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;


CREATE TABLE KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK AS SELECT * FROM KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE 1=2;
ALTER TABLE KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK NOLOGGING;
INSERT /*+ APPEND */INTO KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK
SELECT * FROM KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE TS_START > SYSDATE -2;
commit;
TRUNCATE TABLE KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA;
INSERT /*+ APPEND */INTO KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA
SELECT * FROM KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;
commit;
DROP TABLE KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;


======================================
Example B - Delete old data from a table
======================================
GA_W_COUNTERS_HISTORY

crontab
6 6 * * * /software/oracle/oracle/scripts/clean_ga_w_counters_history.sh

clean_ga_w_counters_history.sh
#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
cd /software/oracle/oracle/scripts/
echo ===================
echo CAN_ROGER_OVMDQ
echo ===================
sqlplus CAN_ROGER_OVMDQ/CAN_ROGER_OVMDQ@igt @call_to_clean_ga_w_counters_history.sql

call_to_clean_ga_w_counters_history.sql
CREATE TABLE CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY_BAK AS SELECT * FROM CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY WHERE 1=2;
ALTER TABLE CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY_BAK NOLOGGING;
INSERT /*+ APPEND */INTO CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY_BAK
SELECT * FROM CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY WHERE ts_last_modified > SYSDATE -5;
commit;
TRUNCATE TABLE CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY;
INSERT /*+ APPEND */INTO CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY
SELECT * FROM CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY_BAK;
commit;
DROP TABLE CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY_BAK;
EXIT;


crontab
6 6 * * * /software/oracle/oracle/scripts/clean_ga_w_counters_history.sh


clean_ga_w_counters_history.sh
#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
cd /software/oracle/oracle/scripts/
echo ===================
echo CAN_ROGER_OVMDQ
echo ===================
sqlplus CAN_ROGER_OVMDQ/CAN_ROGER_OVMDQ@igt @clean_ga_w_counters_history.sql


CREATE TABLE GA_W_COUNTERS_HISTORY_BAK AS SELECT * FROM GA_W_COUNTERS_HISTORY WHERE 1=2;
ALTER TABLE GA_W_COUNTERS_HISTORY_BAK NOLOGGING;
INSERT /*+ APPEND */INTO GA_W_COUNTERS_HISTORY_BAK
SELECT * FROM GA_W_COUNTERS_HISTORY WHERE ts_last_modified > SYSDATE -5;
commit;
TRUNCATE TABLE GA_W_COUNTERS_HISTORY;
INSERT /*+ APPEND */INTO GA_W_COUNTERS_HISTORY
SELECT * FROM GA_W_COUNTERS_HISTORY_BAK;
commit;
DROP TABLE GA_W_COUNTERS_HISTORY_BAK;

EXIT;


======================================
Example C - Delete old data from a table - basic
======================================
CREATE TABLE XXX_BAK  AS SELECT * FROM XXX WHERE 1=2;
ALTER TABLE XXX_BAK NOLOGGING;
INSERT /*+ APPEND */ INTO XXX_BAK SELECT * FROM XXX WHERE tmtst > TRUNC(SYSDATE) -10;
commit;
ALTER SESSION SET DDL_LOCK_TIMEOUT = 600;
TRUNCATE TABLE XXX;
INSERT /*+ APPEND */ INTO XXX SELECT * FROM XXX_BAK;
commit;

DROP TABLE XXX_BAK;


======================================
Example D - TRUNCATE Table of UserA from crontab of UserB 

======================================

As user A:

CREATE OR REPLACE PROCEDURE TRUNCATE_AUD AS

BEGIN

  EXECUTE IMMEDIATE 'TRUNCATE TABLE AUD$';

END;

/

GRANT EXECUTE ON TRUNCATE_AUD TO SYSTEM;

As user B

crontab

/software/oracle/oracle/scripts/remote_db/bgd_robi/clean_aud.sh

clean_aud.sh

#!/bin/bash

export ORA_VER=920

export ORACLE_SID=igt

export ORACLE_BASE=/software/oracle

export ORACLE_HOME=/software/oracle/920

export PATH=${PATH}:${ORACLE_HOME}/bin

. /etc/profile

. /software/oracle/oracle/.bash_profile

. /etc/sh/orash/oracle_login.sh igt

RUN_DATE=`date "+%Y%m%d_%H%M%S"`

PWD=/software/oracle/oracle/scripts/remote_db/bgd_robi

cd ${PWD}

LOG_FILE=/software/oracle/oracle/scripts/remote_db/bgd_robi/clean_aud.log

touch ${LOG_FILE}

echo =================== >> ${LOG_FILE}

echo "Starting at ${RUN_DATE} " >> ${LOG_FILE}

${PWD}/clean_aud_impl.sh 2>> ${LOG_FILE}

cat truncate_aud.log >> ${LOG_FILE}

echo =================== >> ${LOG_FILE}

clean_aud_impl.sh

#!/bin/bash

export ORA_VER=920

export ORACLE_SID=igt

export ORACLE_BASE=/software/oracle

export ORACLE_HOME=/software/oracle/920

export PATH=${PATH}:${ORACLE_HOME}/bin

. /etc/sh/orash/oracle_login.sh igt

sqlplus system/Xen86Pga@BGD_ROBI @/software/oracle/oracle/scripts/remote_db/bgd_robi/truncate_aud.sql

truncate_aud.sql

SET HEADING OFF

SET VERIFY OFF

SET PAGESIZE 0

SET LINESIZE 80

SET NEWPAGE NONE

SET FEEDBACK OFF

spool truncate_aud.log

SELECT 'Start TRUNCATE SYS.AUD$ at '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') FROM DUAL;

alter session set ddl_lock_timeout=600;

BEGIN

  SYS.TRUNCATE_AUD;

END;

/

SELECT 'Finished TRUNCATE SYS.AUD$ at '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') FROM DUAL;

spool off

exit

crontab task to collect Database stats and log to file

crontab
7 6 1 * * /software/oracle/oracle/scripts/run_collect_stats.sh



run_collect_stats.sh
#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

LOG_FILE=/software/oracle/oracle/scripts/history_stats/history_stats.log
WORK_DIR=/software/oracle/oracle/scripts
HIST_DIR=/software/oracle/oracle/scripts/history_stats

cd ${WORK_DIR}

sqlplus system/Xen86Pga@igt @tbs.sql
sqlplus system/Xen86Pga@igt @segments.sql
sqlplus system/Xen86Pga@igt @files.sql

RUN_DATE=`date +"%Y%m%d_%H%M%S"`
touch $LOG_FILE

echo "===================================" >> $LOG_FILE
echo "Run Date: $RUN_DATE" >>  $LOG_FILE
echo "===================================" >>  $LOG_FILE
mv -f ${WORK_DIR}/tbs.txt ${HIST_DIR}/tbs_${RUN_DATE}.txt
mv -f ${WORK_DIR}/segments.txt ${HIST_DIR}/segments_${RUN_DATE}.txt
mv -f ${WORK_DIR}/datafiles.txt ${HIST_DIR}/datafiles_${RUN_DATE}.txt

chmod 666 ${HIST_DIR}/tbs_${RUN_DATE}.txt
chmod 666 ${HIST_DIR}/segments_${RUN_DATE}.txt
chmod 666 ${HIST_DIR}/datafiles_${RUN_DATE}.txt

tbs.sql

SET LINESIZE 120
SET PAGESIZE 200
spool tbs.txt

SELECT TABLESPACE_NAME,
          (MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB,
          MAX(USED_SPACE) AS USED_SPACE_MB,
          MAX(MAX_SPACE) AS MAX_SPACE_MB,
          ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
          CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
           ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
           0 AS MAX_SPACE,
           0 AS USED_SPACE
 FROM DBA_FREE_SPACE
 WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX'
 GROUP BY tablespace_name
 UNION ALL
 SELECT tablespace_name,
           0 AS FREE_SPACE,
           ROUND( SUM(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE,
     0 AS USED_SPACE
 FROM DBA_DATA_FILES
  WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX'
 GROUP BY tablespace_name
 UNION ALL
 SELECT tablespace_name,
     0 AS FREE_SPACE,
     0 AS MAX_SPACE,
     ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
  FROM DBA_SEGMENTS
 WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;

spool off
EXIT;


segments.sql
SET LINESIZE 120
SET PAGESIZE 200
COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999

spool segments.txt

SELECT owner, tablespace_name, segment_name, USED_MB
   FROM (
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%TABLE%'
  GROUP BY owner, tablespace_name,segment_name
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB
   FROM (
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%INDEX%'
  GROUP BY owner, tablespace_name,segment_name
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;

SELECT owner, tablespace_name, segment_name, USED_MB
   FROM (
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSTEM%'
  GROUP BY owner, tablespace_name,segment_name
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB
   FROM (
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSAUX%'
  GROUP BY owner, tablespace_name,segment_name
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;

spool off
EXIT;


files.sql
SET LINESIZE 140
SET PAGESIZE 200
COL tablespace_name FOR A30
COL file_name FOR A60
COL Mb FOR 9999999999999
COL MAX_MB FOR 9999999999999

spool datafiles.txt
SELECT tablespace_name, file_name, ROUND(bytes/1024/1024) AS Mb, ROUND(maxbytes/1024/1024) AS MAX_MB
  FROM DBA_DATA_FILES
ORDER BY tablespace_name, file_name;
spool off

EXIT;

Delete deadlock files

=======================
General
=======================
Delete large trace file with "ORA-00060 deadlock detected" error.

Due to wrong application setup, deadlocks are coming in database.

Large trc file are being generated, and these fill up disk space to 100%

As a workaround, script was scheduled from crontab, to delete these larhe trace file + write first 1000 lines to a history directory.

=======================
Code Example
=======================
crontab
5,25,45,55 * * * * /software/oracle/oracle/scripts/delete_deadlock_files.sh

bash code
#!/bin/bash

#Delete archive files
LOG_FILE=/software/oracle/oracle/scripts/delete_deadlock_trace.log
WORK_DIR=/software/oracle/diag/rdbms/igt/igt/trace
HIST_DIR=/software/oracle/oracle/scripts/history

touch $LOG_FILE

RUN_DATE=`date +"%Y%m%d_%H%M%S"` 
echo "===================================" >> $LOG_FILE
echo "Run Date: $RUN_DATE" >>  $LOG_FILE
echo "===================================" >>  $LOG_FILE

FILES=`find /software/oracle/diag/rdbms/igt/igt/trace/*.trc | xargs grep -l ORA-00060`
for file in $FILES 
do
  echo "Found deadlock ORA-00060 in File $file " >>  $LOG_FILE
  basefile_name=`basename $file`
  ls -ltr $file >> $LOG_FILE
  echo "deleting File..... $file " >>  $LOG_FILE
  head -2000 $file > ${HIST_DIR}/${basefile_name}_header.trc
  rm -f $file 
  echo "Done" >> $LOG_FILE
  echo >> $LOG_FILE  
done

FILES=`find /software/oracle/diag/rdbms/igt/igt/trace/*.trm | xargs grep -l ORA-00060`
for file in $FILES
do
  echo "Found deadlock ORA-00060 in File $file " >>  $LOG_FILE
  ls -ltr $file >> $LOG_FILE
  echo "deleting File..... $file " >>  $LOG_FILE
  rm -f $file
  echo "Done" >> $LOG_FILE
  echo >> $LOG_FILE
done