Unix Pid to Oracle  Sql ..

Unix Command 

top -U oracle -d 5 -n 25
iotop
iotop -user oracle
alias topmem='ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head'

SET LINESIZE 80 HEADING OFF FEEDBACK OFF
SELECT
RPAD('USERNAME : ' || s.username,80) ||
RPAD('OSUSER   : ' || s.osuser,  80) ||
RPAD('PROGRAM  : ' || s.program, 80) ||
RPAD('SPID     : ' || p.spid,    80) ||
RPAD('SID      : ' || s.sid,     80) ||
RPAD('SERIAL#  : ' || s.serial#, 80) ||
RPAD('MACHINE  : ' || s.machine, 80) ||
RPAD('TERMINAL : ' || s.terminal,80) ||
RPAD('SQL TEXT : ' || q.sql_text,80)
FROM v$session s
,v$process     p
,v$sql         q
WHERE s.paddr        = p.addr
AND p.spid           = '&PID_FROM_OS'
AND s.sql_address    = q.address(+)
AND s.sql_hash_value = q.hash_value(+);
  

@pid
Enter value for pid_from_os: 10165
old  15: AND p.spid = '&PID_FROM_OS'
new  15: AND p.spid = '10165'

USERNAME :
OSUSER   : oracle
PROGRAM  : oracle@apt-amd-02 (VKTM)
SPID     : 10165
SID      : 3
SERIAL#  : 1
MACHINE  : apt-amd-02
TERMINAL : UNKNOWN
SQL TEXT :

*****************************************

SET LINES 200 PAGES 0 HEAD OFF LONG 100000
COL dummy_value NOPRINT
--
SELECT 'dummy1' dummy_value,
'USERNAME   : ' || s.username       || CHR(10) ||
'SCHEMA     : ' || s.schemaname     || CHR(10) ||
'OSUSER     : ' || s.osuser        || CHR(10) ||
'MODULE     : ' || s.program        || CHR(10) ||
'ACTION     : ' || s.schemaname       || CHR(10) ||
'CLIENT INFO   : ' || s.osuser        || CHR(10) ||
'PROGRAM   : ' || s.program        || CHR(10) ||
'MACHINE   : ' || s.machine        || CHR(10) ||
'TYPE    : ' || s.type         || CHR(10) ||
'TERMINAL   : ' || s.terminal        || CHR(10) ||
'CPU     : ' || q.cpu_time/1000000      || CHR(10) ||
'ELAPSED_TIME  : ' || q.elapsed_time/1000000   || CHR(10) ||
'BUFFER_GETS   : ' || q.buffer_gets       || CHR(10) ||
'SQL_ID   : ' || q.sql_id        || CHR(10) ||
'CHILD_NUM   : ' || q.child_number       || CHR(10) ||
'STATUS   : ' || s.status            || CHR(10) ||
'SPID    : ' || p.spid         || CHR(10) ||
'SID     : ' || s.sid         || CHR(10) ||
'SERIAL#   : ' || s.serial#        || CHR(10) ||
'KILL STRING   : ' || '''' || s.sid || ',' || s.serial# || ''''  || CHR(10) ||
'START_TIME   : ' || TO_CHAR(s.sql_exec_start,'dd-mon-yy hh24:mi')  || CHR(10) ||
'SQL_TEXT   : ' || q.sql_fulltext
FROM v$session s
JOIN v$process p ON (s.paddr = p.addr)
LEFT OUTER JOIN v$sql q ON (s.sql_id = q.sql_id)
WHERE 1=1 
AND s.username IS NOT NULL -- eliminates background procs
AND NVL(q.sql_text,'x') NOT LIKE '%dummy1%' -- eliminates this query from output
AND p.spid = '&PID_FROM_OS'
ORDER BY q.cpu_time;

Oracle Tablespace metadata

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

select tablespace_name,dbms_metadata.get_ddl(object_type => 'TABLESPACE', name=> tablespace_name) metadata from dba_tablespaces;

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON





TABLESPACE_NAME                METADATA
------------------------------ ----------------------------------------------------------------------------------------------------
SYSTEM
                                 CREATE TABLESPACE "SYSTEM" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/system01.dbf' SIZE 524288000
                                 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT MANUAL
                                  ALTER DATABASE DATAFILE
                                 '/opt/app/oracle/oradata/orcl/system01.dbf' RESIZE 880803840


SYSAUX
                                 CREATE TABLESPACE "SYSAUX" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 419430400
                                 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
                                  ALTER DATABASE DATAFILE
                                 '/opt/app/oracle/oradata/orcl/sysaux01.dbf' RESIZE 870318080


TEMP
                                 CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
                                 '/opt/app/oracle/oradata/orcl/temp01.dbf' SIZE 341835776
                                 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
                                 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576


USERS
                                 CREATE TABLESPACE "USERS" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/users01.dbf' SIZE 5242880
                                 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
                                  ALTER DATABASE DATAFILE
                                 '/opt/app/oracle/oradata/orcl/users01.dbf' RESIZE 672399360


EXAMPLE
                                 CREATE TABLESPACE "EXAMPLE" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/example01.dbf' SIZE 104857600
                                 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
                                 NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


ANUJTEST
                                 CREATE TABLESPACE "ANUJTEST" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/anujtest.dbf' SIZE 10485760
                                 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


TSAPEXF
                                 CREATE TABLESPACE "TSAPEXF" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/tsapexf01.dbf' SIZE 52428800
                                 AUTOEXTEND ON NEXT 10485760 MAXSIZE 314572800
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


TSAPEXU
                                 CREATE TABLESPACE "TSAPEXU" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/tsapexu01.dbf' SIZE 52428800
                                 AUTOEXTEND ON NEXT 10485760 MAXSIZE 314572800
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
                                  ALTER DATABASE DATAFILE
                                 '/opt/app/oracle/oradata/orcl/tsapexu01.dbf' RESIZE 115343360


TEST
                                 CREATE TABLESPACE "TEST" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/test.dbf' SIZE 20971520
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


RMAN
                                 CREATE TABLESPACE "RMAN" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/rman.dbf' SIZE 52428800
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


DROP1
                                 CREATE TABLESPACE "DROP1" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/drop.dbf' SIZE 2097152
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


PERFSTAT
                                 CREATE TABLESPACE "PERFSTAT" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/anuj_perfstat.dbf' SIZE 1048576000
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


UNDOTBR
                                 CREATE UNDO TABLESPACE "UNDOTBR" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/undotbR.dbf' SIZE 524288000
                                 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
                                 BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

CREATE TABLESPACE tbs_anuj DATAFILE '+DATA' SIZE 1M;
alter tablespace TBS_ANUJ add datafile '+DATA' size 32767m;



   FILE_ID BLOCK_SIZE TABLESPACE_NAME             File Name                                            SIZE_GB     MAX_GB AUTOEXTENSIBLE     BIGFILE         ENCRYPTED  COMPRESS_F CONTENTS              STATUS
---------- ---------- --------------------------- ------------------------------------------------- ---------- ---------- ------------------ --------------- ---------- ---------- --------------------- ---------
        95       8192 TBS_ANUJ                    +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3779.1202914077       31.99          0 NO                 NO              NO                    PERMANENT             AVAILABLE
        94       8192 TBS_ANUJ                    +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527           0          0 NO                 NO              NO                    PERMANENT             AVAILABLE



alter database datafile '+DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527' autoextend on maxsize unlimited;



   FILE_ID BLOCK_SIZE TABLESPACE_NAME             File Name                                            SIZE_GB     MAX_GB AUTOEXTENSIBLE     BIGFILE         ENCRYPTED  COMPRESS_F CONTENTS              STATUS
---------- ---------- --------------------------- ------------------------------------------------- ---------- ---------- ------------------ --------------- ---------- ---------- --------------------- ---------
        95       8192 TBS_ANUJ                    +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3779.1202914077       31.99          0 NO                 NO              NO                    PERMANENT             AVAILABLE
        94       8192 TBS_ANUJ                    +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527           0      31.99 YES                NO              NO                    PERMANENT             AVAILABLE




undefine tablespace_name
define tablespace_name='TBS_ANUJ'
set pages 500 lines 400 term off
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;
col file_name           for a60
col AUTOEXTENSIBLE      for a18
col BIGFILE             for a15
col ENCRYPTED           for a10
col TABLESPACE_NAME     for a27
col COMPRESS_FOR        for a10
col file_name 	        for &fname   heading "File Name"
SELECT FILE_ID, b.BLOCK_SIZE, a.TABLESPACE_NAME,a.FILE_NAME, trunc(a.BYTES/(1024*1024*1024),2) SIZE_GB, trunc(a.MAXBYTES/(1024*1024*1024),2) Max_GB , a.AUTOEXTENSIBLE,BIGFILE,ENCRYPTED,COMPRESS_FOR,CONTENTS,a.STATUS from DBA_DATA_FILES a ,DBA_TABLESPACES b 
where 1=1  
and a.TABLESPACE_NAME=b.TABLESPACE_NAME
--and a.TABLESPACE_NAME in ('ENCRYPT_256')
and a.tablespace_name = upper( decode('&&tablespace_name',null,a.tablespace_name,'&&tablespace_name'))
--and FILE_ID=31
order by 2;



   FILE_ID BLOCK_SIZE TABLESPACE_NAME             File Name                                            SIZE_GB     MAX_GB AUTOEXTENSIBLE     BIGFILE         ENCRYPTED  COMPRESS_F CONTENTS              STATUS
---------- ---------- --------------------------- ------------------------------------------------- ---------- ---------- ------------------ --------------- ---------- ---------- --------------------- ---------
        94       8192 TBS_ANUJ                    +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527           0          0 NO                 NO              NO                    PERMANENT             AVAILABLE

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 

while true; do sqlplus "/ as sysdba" @anuj.sql;  sleep 10; done

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