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

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