nohup sql script

$ nohup sqlplus system/sys @hwm2.sql>hwm2.txt 2>&1 &


nohup sqlplus '/ as sysdba' @stats.sql &

cat stats.sql

spool stats.spool
set time on timing on
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
prompt dbms_stats.gather_dictionary_stats
EXECUTE dbms_stats.gather_dictionary_stats;

prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
spool off
exit

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

--  stats1.sql

spool stats1.spool

alter session set nls_date_format='dd-MON-YYYY hh24:mi'; 

set time on timing on feedback on 

select sysdate from dual;

set echo on feedback on time on timing on verify on

-- show con_name;

set pages 300 lines 350

col open_mode for a10

col host_name for a10

col database_role for a15

col db_unique_name for a10

col controlfile_type for a10

col database_role for a17

col host_name for a35

col open_mode for a20

select name,db_unique_name,instance_name,host_name,open_mode,log_mode,database_status,database_role,switchover_status,protection_mode,protection_level,controlfile_type,status,logins,force_logging,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') "db up time" from v$database,gv$instance;

SET VERIFY OFF

select sysdate from dual;

prompt ========================================

prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()

prompt ========================================

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();

prompt ========================================

prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM')

prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');

prompt ========================================

prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS')

prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

prompt ========================================

prompt DBMS_STATS.GATHER_DICTIONARY_STATS

prompt ========================================

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ();

select sysdate from dual;

spool off

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

statsx.sql

spool stats1.spool
alter session set nls_date_format='dd-MON-YYYY hh24:mi';


set time on timing on feedback on
select sysdate from dual;
set echo on feedback on time on timing on verify on

-- show con_name;
set pages 300 lines 350
col open_mode for a10
col host_name for a10
col database_role for a15
col db_unique_name for a10
col controlfile_type for a10
col database_role for a17
col host_name for a35
col open_mode for a20
select distinct d.name,db_unique_name,instance_name,host_name,open_mode,log_mode,database_status,database_role,switchover_status,protection_mode,protection_level,controlfile_type,status,logins,force_logging,
startup_time "db up time" from v$database d ,gv$instance i
--,gv$pdbs p
;


SET VERIFY OFF

select sysdate from dual;


prompt ========================================
prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()
prompt ========================================

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();


prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM')
prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM', Degree => 8);


prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS' , Degree => 8)
prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS', Degree => 8);

prompt ========================================
prompt DBMS_STATS.GATHER_DICTIONARY_STATS
prompt ========================================

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$');
exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_OPR');
exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_IND_HISTORY');
exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_HISTGRM_HISTORY');
exec dbms_stats.lock_table_stats('SYS', 'X$UNIFIED_AUDIT_TRAIL');

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS(Degree => 8);

exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$');
exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_OPR');
exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_IND_HISTORY');
exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_HISTGRM_HISTORY');
exec dbms_stats.unlock_table_stats('SYS', 'X$UNIFIED_AUDIT_TRAIL');

select sysdate from dual;

===========

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d /home/oracle -b statsx statsx.sql




$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
-d /home/oracle/scripts \
-b MyScript \
-l /home/oracle/logs \
-u mustafa/mustafa \
-z mydbserver.mydomain:1521/CDBX \
-n 2 \
-c 'PDB1 PDB2 PDB3 PDB4 PDB5 PDB6 PDB7 PDB8'
myfile.sql



$ORACLE_HOME/perl/bin/perl => PERL binary to run catcon.pl
$ORACLE_HOME/rdbms/admin/catcon.pl => catcon.pl path
-d /home/oracle => directory where script is located.
-b statsx => Prefix for the log files so you can separate them from other scripts logs.
statsx.sql => actual script name

to check 

alter session set nls_date_format='DD-MM-YYYY';
col last_analyzed for a13
set termout off  trimspool off feedback off
--spool dictionary_statistics

prompt 'Statistics for SYS tables'
SELECT NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) dictionary_tables
FROM dba_tables
WHERE owner in ('SYS')
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY')
ORDER BY 1 DESC;

prompt 'Statistics for SYSTEM tables'

SELECT NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) dictionary_tables
FROM dba_tables
WHERE owner in ('SYSTEM')
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY')
ORDER BY 1 DESC;



prompt 'Statistics for Fixed Objects'
select NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY')
ORDER BY 1 DESC;

--spool off

cat gather.sh
#!/bin/ksh
#
export ORACLE_SID=vihcdbd8
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d /home/oracle -l /home/oracle/logs -b statsx statsx.sql

 nohup ./gather.sh &

in log dir 

find . | xargs grep 'Container:' -sl
./statsx0.log
./statsx1.log
./statsx2.log
./statsx3.log

or

 find . | xargs grep 'Container:' -sl | xargs cat {} \;

check above log files 

Oracle CPU intensive SQL

sql On cpu




col "Oracle User" format a15
col "Stat CPU" format a15
col "PROC" format a10
col "OS User" format a10
select s.username "Oracle User",
s.osuser "OS User",
i.consistent_gets "Consistent Gets",
i.physical_reads "Physical Reads",
s.status "Status",
s.sid "SID",
s.serial# "Serial#",
--s.machine "Machine",
-- s.program "Program",
to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
w.seconds_in_wait "Idle Time",
P.SPID "PROC",
name "Stat CPU", value,sql_id
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and 'SQL*Net message from client' = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like '%cpu%'
and t.SID = s.sid
order by 6 asc, 3 desc, 4 desc;

col "Oracle User" format a15
col "Stat CPU" format a15
col "PROC" format a10
col "OS User" format a10
select s.username "Oracle User",
s.osuser "OS User",
i.consistent_gets "Consistent Gets",
i.physical_reads "Physical Reads",
s.status "Status",
s.sid "SID",
s.serial# "Serial#",
--s.machine "Machine",
-- s.program "Program",
to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
w.seconds_in_wait "Idle Time",
P.SPID "PROC",
name "Stat CPU", value,sql_id
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and 'SQL*Net message from client' = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like '%cpu%'
and t.SID = s.sid
order by 6 asc, 3 desc, 4 desc;

How to insert ampersand ( & ) into a table?

Oracle insert ampersand
Oracle insert &
insert ampersand into a table


SQL> create table anuj (text varchar2(100));

Table created.


SQL> insert into anuj values ('Vihaan & singh');
Enter value for singh:

1 row created.

SQL> select * from anuj;

TEXT
---------------------------------------
Vihaan

1 row selected.

SQL> set define off
SQL> insert into anuj values ('Vihaan & singh');

1 row created.

SQL> select * from anuj;

TEXT
-------------------------------------------
Vihaan
Vihaan & singh

2 rows selected.

SQL> set define on

SQL> set escape \
SQL> insert into anuj values ('Vihaan \& singh');

1 row created.


SQL> insert into anuj values ('Vihaan &'||'singh');

1 row created.



SQL> select * from anuj;

TEXT
---------------------------------------------
Vihaan
Vihaan & singh
Vihaan & singh

3 rows selected.

Oracle Script to find out Oracle version

versions.sql


set showmode off echo off
set heading off pagesize 0 linesize 240 timing off feedback off recsep off
set termout off verify off
column sortfield noprint
spool version1.sql
select rpad('1',25) sortfield,
'select chr(10) || ''               Host_Name: '' || host_name from v$instance' || chr(10) ||
'union all' || chr(10) ||
'select ''             DB_Instance: '' || rpad(name,10) || ''DBID: '' || dbid
from v$database' || chr(10) ||
'union all'
from dual
union all
select '2' || substr(substr(comments,1,instr(comments,' Version')-1),1,24),
'select ''' || lpad(substr(comments,1,instr(comments,' Version')-1),24) ||': '' || substr(max(to_char(' || table_name ||
'_stage_date,''YYYYMMDD'') || rpad(' || table_name ||'_release,8) || ''  '' || ' || table_name || '_stage_date),9) from ' ||
table_name || chr(10) ||
'union all'
from dba_tab_comments where table_name like '%VERS'
and table_type = 'TABLE'
union all
select '3',
'select ''          Oracle_Version: '' || version || chr(10) || chr(10) from v$instance;'
from dual order by 1;
spool off
set linesize 80 termout on
@version1
set heading on pagesize 24 timing on feedback 6 recsep wrap
set verify on echo on showmode both



SQL> @version

SQL> set showmode off echo off
old: showmode BOTH

               Host_Name: apt-amd-02
             DB_Instance: ORCL      DBID: 1267852645
          Oracle_Version: 11.2.0.1.0

new: showmode BOTH