Posts

shelll scrips

  cat TEST_DB https://www.funoracleapps.com/2021/06/shell-script-to-change-sys-and-system.html TEST1 TEST2 TEST3 TEST4 Script: #!/bin/bash ################################################################################# #PURPOSE: TEST DB SYS and SYSTEM password change script # Usage : sys_system_pass_change.sh [PROD]/[TEST] #Author Version Date #Himanshu Singh 1.0 15-AUG-2020 # ################################################################################## #######################Before change system password prefix####################### prefx=dundb ##############Change the below value before canging system password################## newprefx=ebs123 . $HOME/testdb.env checkfile=TNS_DB_CHECK.log passch=PASS_CHANGE.log RECEIPENTS=support@funoracleapps.com RUN_USER=`who am i |awk '{print $1}'` if [ $# -ne 1 ] then echo "No Parameter passed!! ==\>Either Pass TEST or PROD\<== Exiting" exit 1; fi if [ "$1" == "TEST" ] then inst_f
Post a Comment
Read more

tablespace move

 set feedback off set echo off set serverout off set pages 0 set lines 200  col name new_val dbname noprint select name from v$database; spool mvlobs_&&dbname\.sql prompt spool mvlobs_&&dbname select 'alter table ' || owner || '.' || table_name || ' move lob(' || column_name || ') store as (tablespace '||tablespace_name||'_NEW);' from dba_lobs where tablespace_name not in ('SYSTEM', 'SYSAUX', 'UNDOTBS1','USERS','TEMP') order by tablespace_name; prompt spool off spool off set feedback off set echo off set serverout off set pages 0 set lines 200  col name new_val dbname noprint select name from v$database; spool mvtabs_&&dbname\.sql prompt spool mvtabs_&&dbname select 'alter table '||owner||'.'||table_name||' move tablespace '||tablespace_name||'_NEW;'  from dba_tables  where tablespace_name not in ('SYSTEM', 'SYSAUX', 'UNDOTB
Post a Comment
Read more
  https://github.com/nkarag/oracle_ready2run_scripts/ --------------------------------------------------------------------------------------------- -- Find the available execution plans for a specific SQL_ID -- --              Note that the AVG_ET_SECS (average elpased time) will not be accurate for parallel queries.  -- The ELAPSED_TIME column contains the sum of all parallel slaves. So the  --              script divides the value by the number of PX slaves used which gives an  --              approximation.  -- --              Note also that if parallel slaves are spread across multiple nodes on --              a RAC database the PX_SERVERS_EXECUTIONS column will not be set. -- -- author: Nikos Karagiannidis (C) 2013 - http://oradwstories.blogspot.com --------------------------------------------------------------------------------------------- prompt prompt ******************************************************** prompt Find the available execution plans for a specifi
Post a Comment
Read more

top sessions

 http://db.geeksinsight.com/tools-scripts/ REF: set pagesize 100 set linesize 250 col INSTANCE_NUMBER format 99 col SESSION_ID format 99999 col BLOCKING_INST_ID format 99 col event format a20 col WAIT_CLASS format a15 col PROGRAM format a20 col MODULE format a16 col p3 format 99999999999999999999 select sql_id,to_char(SAMPLE_TIME,'YYYYMMDD-HH24:MI:SS'),        INSTANCE_NUMBER,SESSION_ID,PROGRAM,MODULE,EVENT,        CURRENT_OBJ#,WAIT_CLASS,BLOCKING_INST_ID,        BLOCKING_SESSION, p3 from dba_hist_active_sess_history where INSTANCE_NUMBER= 3  and SESSION_ID = 2437  and sample_time between to_date('16-06-18 11:00:00','DD-MM-YY HH24:MI:SS') and to_date('16-06-18 11:04:00','DD-MM-YY HH24:MI:SS') and event ='library cache lock' order by sample_id; col USERNAME for a10 col machine for a30 col Text for a50 wrap on select  se.SID, ss.serial#,  ss.username,   ss.machine,  ss.sql_id,  to_char(s.last_active_time,'DD-MON-YY HH:MI:SS'),  s.la
Post a Comment
Read more

Gather stats on the table

  col TABLE_NAME for a30 col PARTITION_NAME for a20 col SUBPARTITION_NAME for a20 select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_STATISTICS where STALE_STATS='YES'; OR select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where OWNER='&OWNER' AND STALE_STATS='YES'; set linesize 200 trimspool on set pagesize 100 set pause off echo off verify off set head on term on col begin_interval_time format a30 col min_sess_per_day format 99 , 999 head ' MIN|SESS|PER|DAY ' col max_sess_per_day format 99 , 999 head ' MAX|SESS|PER|DAY ' col avg_sess_per_day format 99 , 999 head ' AVG|SESS|PER|DAY ' col min_proc_per_day format 99 , 999 head ' MIN|PROC|PER|DAY ' col max_proc_per_day format 99 , 999 head ' MAX|PROC|PER|DAY ' col avg_proc_per_day format 99 , 999 head ' AVG|PROC|PER|DAY ' with processes as ( select snap_id, CURRENT_UTILIZATION p
Post a Comment
Read more

Day -DBA scripts-fragmentation

set head off set feedback off spool kill_&a_sess_i1.sql select 'spool kill_&a_sess.log' from dual; select 'set feedback on' from dual; select 'alter system kill session '||chr(39)||sid||','||serial#||chr(39)||';' from v$session where username='&a'; select 'spool off' from dual; spool off @kill_&a_sess_i1.sql spool chk_Rajtest_sess_i1.log select 'set feedback on' from dual; select sid, serial# from v$session where username='Rajtest'; spool off Rebuild index set feedback off set echo off set serverout off set pages 0 set lines 200  col name new_val dbname noprint select name from v$database; spool rebuild_idx_&&dbname\.sql prompt spool rebuild_idx_&&dbname select 'alter index '||owner||'.'||table_name||' rebuild tablespace '||tablespace_name||'_NEW;'  from dba_indexes  where tablespace_name not in ('SYSTEM', 'SYSAUX', 'UNDOTBS1',&
1 comment
Read more

Snapshot Standby database and how to convert physical standby database to snapshot standby database (READ WRITE mode)

 Snapshot Standby database and how to convert physical standby database to snapshot standby database (READ WRITE mode) SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,           (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;    THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF ---------- ---------- ----------- -------------------- ----------          1         75          75 04-DEC-2019 11:09:55          0          2         69          69 04-DEC-2019 11:07:26          0 SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS       INSTANCE_NAME    DATABASE
Post a Comment
Read more