Datafiles Disk IO
set pagesize 100
set linesize 200
col PERC_READS format a10
col name format a40
col PERC_WRITES like PERC_READS
SELECT df.NAME, phyrds Physical_READS,
ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,
phywrts Physical_WRITES,
ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,
phyrds + phywrts total
FROM v$datafile df, v$filestat fs, ts$ t
WHERE df.FILE# = fs.FILE#
AND df.ts# = t.ts#
-- AND t.NAME = ' xxxx '
ORDER BY phyrds DESC;
Tuesday, 3 May 2011
Who is using TEMP tablespace?
SQL Running out of Temporary tablespace
TEMP tablespace currently using
1.
set linesize 200
col sid format a12
col sql_text format a50
select s.sid || ',' || s.serial# sid,s.username,u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb,s.sql_id
from v$sort_usage u, v$session s, v$sqlarea a,v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
group by s.sid || ',' || s.serial#, s.username,
substr(a.sql_text, 1, (instr (a.sql_text, ' ')-1)),u.tablespace,round(((u.blocks*p.value)/1024/1024),2),s.sql_id;
2
select
s.INST_ID node,
segfile#,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb,s.sql_id
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
segfile#,s.sql_id
order by 1,2
;
3.
set linesize 140
set pagesize 400
set head on
col iid for 990
col sid_serial for a11
column username format a10
col module for a13
col action for a15
col state for a10
column segfile# format 9,999 heading 'File|ID'
col orapid for 999999 Heading "orapid||Oracle|pid"
col dbpid for a6 HEADING "spid|Unix|pid"
col apppid for a10;
column program format a55
column segblk# format 999,999,999 heading 'Block|ID'
set timi off;
CLEAR COMPUTES;
break on iid
select distinct
s.INST_ID iid,
lpad(s.sid,5)||','|| Lpad(s.serial#,5) sid_serial,
p.pid orapid,
p.spid dbpid,
s.USERNAME,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb,
s.status,
s.state,
u.SQL_ID,
segfile#,
s.module,
s.action
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
lpad(s.sid,5)||','|| Lpad(s.serial#,5),
p.pid,
p.spid,
s.USERNAME,
s.status,
s.state,
u.SQL_ID,
segfile#,
s.module,
s.action
order by 1,2
;
4
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
5.
PROMPT consuption by instances, tempfiles:
select
s.INST_ID iid,
segfile#,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
segfile#
order by 1,2
;
TEMP tablespace currently using
1.
set linesize 200
col sid format a12
col sql_text format a50
select s.sid || ',' || s.serial# sid,s.username,u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb,s.sql_id
from v$sort_usage u, v$session s, v$sqlarea a,v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
group by s.sid || ',' || s.serial#, s.username,
substr(a.sql_text, 1, (instr (a.sql_text, ' ')-1)),u.tablespace,round(((u.blocks*p.value)/1024/1024),2),s.sql_id;
2
select
s.INST_ID node,
segfile#,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb,s.sql_id
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
segfile#,s.sql_id
order by 1,2
;
3.
set linesize 140
set pagesize 400
set head on
col iid for 990
col sid_serial for a11
column username format a10
col module for a13
col action for a15
col state for a10
column segfile# format 9,999 heading 'File|ID'
col orapid for 999999 Heading "orapid||Oracle|pid"
col dbpid for a6 HEADING "spid|Unix|pid"
col apppid for a10;
column program format a55
column segblk# format 999,999,999 heading 'Block|ID'
set timi off;
CLEAR COMPUTES;
break on iid
select distinct
s.INST_ID iid,
lpad(s.sid,5)||','|| Lpad(s.serial#,5) sid_serial,
p.pid orapid,
p.spid dbpid,
s.USERNAME,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb,
s.status,
s.state,
u.SQL_ID,
segfile#,
s.module,
s.action
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
lpad(s.sid,5)||','|| Lpad(s.serial#,5),
p.pid,
p.spid,
s.USERNAME,
s.status,
s.state,
u.SQL_ID,
segfile#,
s.module,
s.action
order by 1,2
;
4
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
5.
PROMPT consuption by instances, tempfiles:
select
s.INST_ID iid,
segfile#,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
segfile#
order by 1,2
;
sqlplus Command Line History
sqlplus Command Line History
Doskey functionality in sqlplus on linux platform
sqlplus History
sql doskey ( like old days on dos )
sqlplus doskey
rlwrap is a Command Line History and Editing in SQL*Plus and RMAN on Linux
http://utopia.knoware.nl/~hlub/uck/rlwrap/
wget http://utopia.knoware.nl/~hlub/uck/rlwrap/rlwrap-0.37.tar.gz
apt-amd-02:/home/anujs/Downloads/rlwrap-0.37 # wget http://utopia.knoware.nl/~hlub/uck/rlwrap/rlwrap-0.37.tar.gz
--2011-05-03 12:44:25-- http://utopia.knoware.nl/~hlub/uck/rlwrap/rlwrap-0.37.tar.gz
Resolving utopia.knoware.nl... 213.197.30.29
Connecting to utopia.knoware.nl|213.197.30.29|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 251438 (246K) [application/x-gzip]
Saving to: `rlwrap-0.37.tar.gz'
100%[==========================================================================================================================================>] 251,438 1.12M/s in 0.2s
2011-05-03 12:44:25 (1.12 MB/s) - `rlwrap-0.37.tar.gz' saved [251438/251438]
or
rpm
Download your RPM according to your OS
apt-amd-02:/home/anujs/Downloads # cat /etc/issue
Welcome to openSUSE 11.3 "Teal" - Kernel \r (\l).
rlwrap-0.30-56.1.x86_64.rpm
Download software from this site as per your OS
http://rpm.pbone.net/index.php3?stat=3&search=rlwrap&srodzaj=3
OpenSuSE ftp.opensuse.org/distribution/11.2/repo/oss/suse/x86_64/rlwrap-0.30-56.1.x86_64.rpm
install this RPM
apt-amd-02:/home/anujs/Downloads # rpm -Uvh rlwrap-0.30-56.1.x86_64.rpm
Preparing... ########################################### [100%]
package rlwrap-0.30-56.1.x86_64 is already installed
go to oracle user
su - oracle
edit your .bash_profile with following line
alias hsqlplus='rlwrap sqlplus'
alias hman='rlwrap rman'
alias hsql='rlwrap sqlplus'
alias sql="rlwrap -i -f ~/.sqlplus_history -H ~/.sqlplus_history -s 30000 sqlplus"
touch ~/.sqlplus_history
this will store 30000 line in .sqlplus_history file
or
export RLWRAP_EDITOR="vim +%L -c 'syntax on' -c 'set filetype=sql'"
alias sysdba='rlwrap -m -s5000 sqlplus / as sysdba'
oracle@apt-amd-02:~> hsqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 3 11:54:26 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: scott/tiger
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
T TABLE
sql>
now if you press arrow key, you will able to see previous commands !!!!!!!!!!!!!!!!!!!!!!!!
=====================================================================================================================================
download !!
wget ftp://ftp.pbone.net/mirror/archive.fedoraproject.org/epel/6/x86_64/Packages/r/rlwrap-0.42-1.el6.x86_64.rpm
--2022-03-10 07:51:15-- ftp://ftp.pbone.net/mirror/archive.fedoraproject.org/epel/6/x86_64/Packages/r/rlwrap-0.42-1.el6.x86_64.rpm
=> ‘rlwrap-0.42-1.el6.x86_64.rpm’
Resolving ftp.pbone.net (ftp.pbone.net)... 93.179.225.212
Connecting to ftp.pbone.net (ftp.pbone.net)|93.179.225.212|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done. ==> PWD ... done.
==> TYPE I ... done. ==> CWD (1) /mirror/archive.fedoraproject.org/epel/6/x86_64/Packages/r ... done.
==> SIZE rlwrap-0.42-1.el6.x86_64.rpm ... 95240
==> PASV ... done. ==> RETR rlwrap-0.42-1.el6.x86_64.rpm ... done.
Length: 95240 (93K) (unauthoritative)
100%[===================================================================================================================================================>] 95,240 166KB/s in 0.6s
2022-03-10 07:51:17 (166 KB/s) - ‘rlwrap-0.42-1.el6.x86_64.rpm’ saved [95240]
rpm -ivh rlwrap-0.42-1.el6.x86_64.rpm
warning: rlwrap-0.42-1.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:rlwrap-0.42-1.el6 ################################# [100%]
su - oracle
touch /home/oracle/.oracle_keywords
alias sqlplus='/usr/bin/rlwrap -if $HOME/.oracle_keywords $ORACLE_HOME/bin/sqlplus'
rlwrap sqlplus / as sysdba
rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 10 07:54:51 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from dual;
D
-
X
Subscribe to:
Posts (Atom)
