Segments with more IO read writes

set line 200
col segment_name for a60


WITH segment_stats 
            AS (SELECT  ss.owner || '.' || ss.object_name 
                                || DECODE (ss.subobject_name,NULL, '',
                                '(' || ss.subobject_name || ')') segment_name,
                                ss.object_type, 
                                SUM ( CASE WHEN statistic_name LIKE 'physical reads%' 
                                        THEN VALUE ELSE 0 END) reads, 
                                SUM ( CASE WHEN statistic_name LIKE 'physical writes%' 
                                        THEN VALUE ELSE 0 END) writes, 
                                ROUND (SUM (bytes) / 1048576) mb 
                    FROM     v$segment_statistics ss 
                    JOIN      dba_segments s 
                    ON     (s.owner = ss.owner AND s.segment_name = ss.object_name 
                             AND NVL (ss.subobject_name, 'x') =NVL (s.partition_name, 'x') )
                    WHERE    statistic_name LIKE 'physical reads%' 
                    OR          statistic_name LIKE 'physical writes' 
                    GROUP BY ss.owner, ss.object_name, ss.subobject_name, ss.object_type) 
SELECT segment_name, object_type, reads, writes, 
            ROUND (reads * 100 / SUM (reads) OVER (), 2) pct_reads, 
            ROUND (writes * 100 / SUM (writes) OVER (), 2) pct_writes, mb 
FROM segment_stats 
ORDER BY reads DESC 

Sample Result:

Ref: http://guyharrison.squarespace.com

Oracle SQL*Plus banner

How to set the SQL *Plus banner as in below image.?
















Append below lines to $ORACLE_HOME\sqlplus\admin\glogin.sql

SELECT 'This system is  for the  use of  authorized users  only. Individuals using this computer  system with  authority,  without  authority, or  in  excess  of their  authority, are  subject  to  having  all of  their activities on  this  system monitored  and  recorded  by  system  personnel.  In  the  course of monitoring individuals improperly using this system, or in the course of system maintenance, the activities of authorized  users also  may be monitored. Anyone using this system  expressly  consents  to such  monitoring  and  is  advised that if such monitoring reveals possible evidence of criminal activity, system personnel may provide the evidence of such monitoring  to  law enforcement  officials and will result in prosecution to the maximum extent allowable by applicable law.'  "********** WARNING ***********" FROM dual;

Note: 

This glogin.sql will be invoked immediately after connecting to database.

This sqlplus\admin\glogin.sql file should be available in Oracle database home and Oracle client home too.

Oracle express client may not have sqlplus\admin\ directory.

Delete archivelogs on standby [ tested ]

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

----------------------------------------------------------------

-r-x------ 1 oracle oinstall  654 Dec 19 06:30 delarch.sh
-r-------- 1 oracle oinstall  240 Dec 19 05:49 Maxseq.sql

[oracle@orastby ~]$ cat delarch.sh
----------------------------------------------------------------
#!/bin/bash

export ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1
export ORACLE_SID=FIN2
export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
 rm -f /home/oracle/log/MAXSEQ.tmp
 $ORACLE_HOME/bin/sqlplus -S /nolog < /home/oracle/Maxseq.sql 
 seq1=`cat /home/oracle/log/MAXSEQ.tmp | awk 'NR==1{print $2}'`
#seq2=`cat /home/oracle/log/MAXSEQ.tmp | awk 'NR==2{print $2}'`
#seq3=`cat /home/oracle/log/MAXSEQ.tmp | awk 'NR==3{print $2}'`
 echo $seq1 
$ORACLE_HOME/bin/rman msglog /home/oracle/log/STANDBY_ARC_DEL.${TODAY}_${TIME}.log << EOF
 connect target /
 run{
 crosscheck archivelog all;
 delete noprompt archivelog until sequence $seq1 thread 1;
#delete noprompt archivelog until sequence $seq2 thread 2;
#delete noprompt archivelog until sequence $seq3 thread 3;
 }
 EOF
find /home/oracle/log/ -name "STANDBY_ARC_DEL.*" -mtime +7  -exec rm -rf {}  \;
----------------------------------------------------------------

[oracle@orastby ~]$ cat Maxseq.sql 
----------------------------------------------------------------
connect / as sysdba 
set heading off pages 0 echo off feedback off; 
spool /home/oracle/log/MAXSEQ.tmp; 
select thread#, max(sequence#)-10 from v$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ; 
spool off; 
exit; 
----------------------------------------------------------------
Note: For RAC please uncomment the lines for thread 2,3,..

delete files older than x days linux

find . -name "*.trc" -mtime +35 -exec ls -lrt {} \; -- list
find . -name "*.trc" -mtime +35 -exec rm -rf  {} \; -- remove

---# above commands will find in current directory and
---# all sub directories for files older than 35 days, with .trc 


find /u01/ -name "*.log" -mtime +7 -exec ls -lrt {} \; --list
find /u01/ -name "*.log" -mtime +7 -exec rm -rf {} \; --remove

---# above commands will find in /u01/ directory and
---# all sub directories for files older than 7 days, with .log

Linux rsync, scp without password prompting

On source server:

[oracle@oradbpr] $ ls -rlt /home/oracle/.ssh/id_rsa.pub

if above file exists, copy id_rsa.pub to destination server, else generate with below ssh-keygen command.

[oracle@oradbpr] $ ssh-keygen -t rsa

Hit enter key, when prompted for passphrase.
private key and public key generated at /home/oracle/.ssh/id_rsa.pub
copy id_rsa.pub file to remote server.(destination server)

[oracle@oradbpr]$ scp /home/oracle/.ssh/id_rsa.pub oracle@oradbdr:/home/oracle
oracle@oradbdr's password:

On destination server:

[oracle@oradbdr] $ pwd
/home/oracle
[oracle@oradbdr] $ cat id_rsa.pub >> /home/oracle/.ssh/authorized_keys
[oracle@oradbdr] $ chmod 700 /home/oracle/.ssh/authorized_keys

On source server:

[oracle@oradbpr] $ rsync -avH /orabkp/ARC_PROD* oracle@oradbdr:/backup/archbkp

now rsync without password prompting.