Oracle Database Administration

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

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

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

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

  Exadata interview Getting ready for a Exadata interview? Make sure to refresh your knowledge by reviewing this list of Exadata Interview Questions. What environment is a good fit for Exadata? Exadata was originally designed for the warehouse environment. Later it was enhanced for use in the OLTP databases as well. What are the advantages of Exadata? The Exadata cluster allows for consistent performance while allowing for increased throughput. As load increases on the cluster the performance remains consistent by utilizing inter-instance and intra-instance parallelism. It should not be expected that just moving to Exadata will improve performance. In most cases it will especially if the current database host is overloaded. What is the secret behind Exadata’s higher throughput? Exadata ships less data through the pipes between the storage and the database nodes and other nodes in the RAC cluster. Also it’s ability to do massive parallelism by running parallel processes across all t...