==================================
WorkSheets - Oracle Database
==================================
-------------------------
Utilities and How to use:
-------------------------
orapwd file=
password= entries=100
----------------------
How to create logfile:
----------------------
Run SQL file from OS - sqlplus "/ as sysdba" @test.sql > test.$(date +"%d%m%Y%H%M%S").log 2>&1
Select database user process other than system processs - ps -ef|grep -i $ORACLE_SID|egrep -v "_$ORACLE_SID|arch"|grep -v grep
---------------------------------------------------------
-- Shell script to execute script in multiple databases:
---------------------------------------------------------
cat > chkdb.sh
#!/usr/bin/ksh
for i in `ps -ef|grep pmon | grep ora | grep -v grep | awk '{print $NF}' | cut -c10-40`
do
USER=`ps -ef|grep $i | grep pmon | awk '{print $1}'`
DB=$i
echo $DB $USER
su - $USER -c "export ORACLE_SID=$DB;sqlplus '/ as sysdba' @/root/test/chkdbversion.sql" < /dev/null
done
--------------
-- Example 2:
--------------
#!/usr/bin/ksh
cat /sysadm/utils/ora_util.sid.. | awk '{print $2}' > /tmp/dblist
for i in `cat /tmp/dblist`
do
USER=`ps -ef|grep $i | grep pmon | awk '{print $1}'`
DB=$i
echo $DB $USER
su - $USER -c "export ORACLE_SID=$DB;sqlplus '/ as sysdba' @/oracle/product/tbsspace.sql" < /dev/null
done
-------------------
-- Create SQL file:
-------------------
cat >
.sql
spool .sql
set heading off feedback off echo off verify off
;
spool off
@
.sql
@;
=========================
1) Checking oracle error:
=========================
-----------------------------------
1.1) Check oracle error definition:
-----------------------------------
Issue - oerr ora
- as oracle user
Example:
ora_WE@s96md0d1:/oracle/log/MDPWE/bdump : oerr ora 02068
02068, 00000, "following severe error from %s%s"
// *Cause: A severe error (disconnect, fatal Oracle error) received from
// the indicated database link. See following error text.
// *Action: Contact the remote system administrator.
ora_WE@s96md0d1:/oracle/log/MDPWE/bdump :
ora_WE@s96md0d1:/oracle/log/MDPWE/bdump :
-------------------------------
1.2) Find trace file in Oracle:
-------------------------------
The following script returns the path to the trace file that the current session writes.
It returns the path whether or not tracing is enabled.
select u_dump.value
|| '/'
|| db_name.value
|| '_ora_'
|| v$process.spid
|| nvl2(v$process.traceid,'_'
|| v$process.traceid,null)
|| '.trc' trcfile
from v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session
on v$process.addr = v$session.paddr
where u_dump.name = 'user_dump_dest'
and db_name.name = 'db_name'
and v$session.audsid = sys_context('userenv','sessionid')
and v$session.sid =
(select distinct sid from v$mystat)
and v$session.serial# =
(select serial# from v$session where sid =
(select distinct sid from v$mystat))
/
In order to generate a more informative trace file name, the following command can be used:
alter session set tracefile_identifier = here_is_my_session;
A trace file will then have this identifier (here: here_is_my_session) in it's filename.
The trace file's name can also be found with oradebug:
SQL> oradebug setmypid
SQL> oradebug tracefile_name
*/
--------------
-- Last alert:
--------------
set linesize 145 pagesize 1000 trimout on trimspool on feedback off timing off verify off
column ct format a18 heading "Creation Time"
column instance_name format a8 heading "Instance|Name"
column object_type format a14 heading "Object|Type"
column message_type format a9 heading "Message|Type"
column message_level format 9999 heading "Mess.|Lev."
column reason format a30 heading "Reason"
column suggested_action format a75 heading "Suggested|Action"
Select
To_Char(Creation_Time, 'DD-MM-YYYY HH24:MI') ct
, instance_name
, object_type
, message_type
, message_level
, reason
, suggested_action
From
dba_outstanding_alerts
Order
By Creation_Time
;
====================
3) Initial Queries:
====================
------------------------
-- Identify own session:
------------------------
select s.username,
p.spid,
s.sid,
s.serial#
from v$session s,
v$process p
where s.paddr = p.addr
and s.type = 'USER'
and s.sid = sys_context('USERENV', 'SID')
and s.sid =
(select distinct sid from v$mystat)
and s.serial# =
(select serial#
from v$session
where sid=
(select distinct sid from v$mystat))
/
-------------------------------
-- Identify the number of CPUs:
-------------------------------
select value
from v$osstat
where stat_name='NUM_CPUS'
/
-----------------------------------------------
-- Instance details (startup time, status etc:)
-----------------------------------------------
col HOST_NAME for a12
col STARTUP_TIME for a20
col INSTANCE_NAME for a12
SELECT SUBSTR(host_name,0,15) host_name, instance_name, status,TO_CHAR(startup_time,'dd-mm-yy hh24:mi:ss') startup_time
FROM v$instance
/
------------------------------------------------------
-- Instance details ( startup time using pmon process)
------------------------------------------------------
SELECT to_char(logon_time,'Dy dd Mon HH24:MI:SS') "DB Startup Time"
FROM sys.v_$session
WHERE sid=1 /* this is pmon */
/
----------------------------------------------------------------------------------
-- Database details (Open mode, log mode (archive/no archive), force logging etc:)
----------------------------------------------------------------------------------
SELECT name, dbid, open_mode, log_mode, force_logging
FROM v$database
/
--------------------------
-- Database Charactre set:
--------------------------
-- The (ordinary) character set for a database
select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
--The national character set for a database
select value from nls_database_parameters where parameter = 'NLS_NCHAR_CHARACTERSET';
-- Current database character set from system view
select value$ from sys.props$ where name='NLS_CHARACTERSET';
------------------
-- Database size :
------------------
-- Database size (except control files):
col database format a20
col host format a20
select trim(ora_database_name) database,
e.host_name host,
round((a.data_size+b.temp_size+c.redo_size),2) total_gb ,
round((a.data_size+b.temp_size+c.redo_size) - d.free_size,2) used_gb,
round(d.free_size,2) free_gb
from ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) a,
( select nvl(sum(bytes)/1024/1024/1024,0) temp_size from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) c ,
( select sum(bytes)/1024/1024/1024 FREE_SIZE from dba_free_space ) d ,
( select trim(host_name) host_name from v$instance ) e
/
-- Database size (except control files, on max size):
set linesize 100
col database format a20
col host format a20
select trim(ora_database_name) database,
e.host_name host,
round((a.data_size+b.temp_size+c.redo_size),2) total_gb ,
round((a.data_size+b.temp_size+c.redo_size) - d.free_size,2) used_gb,
round(d.free_size,2) free_gb,
round((a.data_size_max+b.temp_size_max+c.redo_size),2) max_gb
from ( select nvl(sum(bytes)/1024/1024/1024,0) data_size, nvl(sum(maxbytes)/1024/1024/1024,0) data_size_max from dba_data_files ) a,
( select nvl(sum(bytes)/1024/1024/1024,0) temp_size, nvl(sum(maxbytes)/1024/1024/1024,0) temp_size_max from dba_temp_files ) b,
( select nvl(sum(bytes)/1024/1024/1024,0) redo_size from sys.v_$log ) c ,
( select nvl(sum(bytes)/1024/1024/1024,0) free_size from dba_free_space ) d ,
( select trim(host_name) host_name from v$instance ) e
/
--------------------
-- Controlfile size:
--------------------
set termout off
column value new_value BlkSz
select value
from sys.v_$parameter
where name = 'db_block_size'
/
set termout on
set verify off
select &BlkSz * (1 + 2 * sum(ceil(record_size * records_total / (&BlkSz - 24)))) bytes
from sys.v_$controlfile_record_section
/
--> Testing
select (f.value * g.bytes) * (select count(*) from v$controlfile) ctrl_size
from
(select value from sys.v_$parameter where name = 'db_block_size') f,
(select (1 + 2 * sum(ceil(record_size * records_total / ((select value from sys.v_$parameter where name = 'db_block_size') - 24)))) bytes from sys.v_$controlfile_record_section)g
/
--< Testing
-------------------------------
-- Background Process details :
-------------------------------
set pagesize 60 lines 1000
col program format a30
col osuser format a30
select p.spid osprocess,
s.osuser osuser ,
s.program ,
s.status ,
to_char(logon_time,'dd-mm-yy hh24:mi:ss') logon_time
from v$process p,
v$session s
where p.addr = s.paddr
and s.type = 'BACKGROUND'
and p.background =1
/
----------------------------------
-- File structure,status and size:
----------------------------------
------------------------------------------------
-- 1.)File count for Control/Data/Redolog files:
------------------------------------------------
SELECT (SELECT COUNT(NAME)
FROM v$controlfile) control_files,
(SELECT COUNT(NAME)
FROM v$datafile) data_files,
(SELECT COUNT(MEMBER)
FROM v$logfile) redolog_fiels,
(SELECT COUNT(NAME)
FROM v$tempfile) temp_files
FROM dual
/
------------------------------------------------
-- 2.)File count for Control/Data/Redolog files:
------------------------------------------------
col file_name for a40
SELECT SUBSTR(NAME,0,55) file_name,status FROM v$controlfile
UNION ALL
SELECT SUBSTR(NAME,0,55) file_name,status FROM v$datafile
UNION ALL
SELECT SUBSTR(MEMBER,0,55) file_name,status FROM v$logfile
UNION ALL
SELECT SUBSTR(file_name,0,55) file_name,status FROM dba_data_files
/
--------------------------------------------------
-- 3.) File status for Control/Data/Redolog files:
--------------------------------------------------
SELECT DISTINCT status FROM v$controlfile
UNION ALL
SELECT DISTINCT status FROM v$datafile
UNION ALL
SELECT DISTINCT status FROM v$tempfile
UNION ALL
SELECT DISTINCT status FROM v$logfile
UNION ALL
SELECT DISTINCT status FROM dba_data_files
/
------------------------------------------------------------------------------
-- 4.) Structure - (File count for Control/Data/Redolog files Including size):
------------------------------------------------------------------------------
SELECT (SELECT COUNT(NAME)
FROM v$controlfile) control_files,
(SELECT COUNT(NAME)||' (Size(GB): '||ROUND(SUM(bytes)/1024/1024/1024,2)||')'
FROM v$datafile) data_files,
(SELECT COUNT(MEMBER)
FROM v$logfile) redolog_files,
(SELECT COUNT(NAME)||' (Size(GB): '||ROUNd(SUM(bytes)/1024/1024/1024,2)||')'
FROM v$tempfile) temp_files
FROM dual
/
------------------------------------------------
-- 5.) Structure - (File location for database):
------------------------------------------------
select distinct substr(name,0,instr(name,'/',-1,1)) file_path from v$datafile;
select distinct substr(name,0,instr(name,'/',-1,1)) file_path from v$controlfile;
select distinct substr(member,0,instr(member,'/',-1,1)) file_path from v$logfile;
select distinct substr(name,0,instr(name,'/',-1,1)) file_path from v$tempfile;
select decode(value,null,(select substr(file_spec,0,instr(file_spec,'/lib/libqsmashr.so' ))||'dbs/'
from dba_libraries
where library_name = 'DBMS_SUMADV_LIB'
),substr(value,0,instr(value,'/',-1,1))) file_path
from sys.v_$parameter
where name = 'spfile';
select distinct file_path
from
(select distinct substr(name,0,instr(name,'/',-1,1)) file_path from v$datafile
union all
select distinct substr(name,0,instr(name,'/',-1,1)) file_path from v$controlfile
union all
select distinct substr(member,0,instr(member,'/',-1,1)) file_path from v$logfile
union all
select distinct substr(name,0,instr(name,'/',-1,1)) file_path from v$tempfile
union all
select destination from v$archive_dest where destination is not null
union all
select decode(value,null,(select substr(file_spec,0,instr(file_spec,'/lib/libqsmashr.so' ))||'dbs/'
from dba_libraries
where library_name = 'DBMS_SUMADV_LIB'
),substr(value,0,instr(value,'/',-1,1))) file_path
from sys.v_$parameter
where name = 'spfile')
/
---------------------------------------------------------------
-- 6.) Structure - (Parameter file using for instance startup):
---------------------------------------------------------------
SELECT NVL(value,
(SELECT SUBSTR(file_spec,0,instr(file_spec,'/lib/libqsmashr.so' ))
FROM DBA_LIBRARIES
WHERE library_name = 'DBMS_SUMADV_LIB')
||'dbs/init'
||lower(trim(ora_database_name))||'.ora') parfile
FROM v$parameter
WHERE name='spfile'
/
-----------------------------------------------------------------------------------
-- Database details (Database open mode, archive log mode etc:)- depends on version
-- ** Wrong query**
-----------------------------------------------------------------------------------
select substr(trim(substr(banner,(select instr(banner,'Release') from v$version where rownum=1),(select instr(banner,'-') from v$version where rownum=1)-(select instr(banner,'Release') from v$version where rownum=1))),0,25) version from v$version where rownum=1;
SELECT NAME,
open_mode,
log_mode,
DECODE((SELECT SUBSTR(banner,(SELECT INSTR(LOWER(banner),'release',1,1)
FROM v$version
WHERE ROWNUM = 1) + LENGTH('release') + 1,
((SELECT INSTR(LOWER(banner),'.',1,1)
FROM v$version
WHERE ROWNUM = 1) - ((SELECT INSTR(LOWER(banner),'release',1,1)
FROM v$version
WHERE ROWNUM = 1) + LENGTH('release') + 1)))
FROM v$version
WHERE ROWNUM = 1),'8','Not applicable','9',force_logging,'10',force_logging) force_logging
FROM v$database
/
--------------------------------------------------
-- Database details(After database copy/recreation)
---------------------------------------------------
SELECT NAME, TO_CHAR(created,'dd-mm-yy hh24:mi:ss') created ,open_mode, log_mode, force_logging
FROM v$database
/
------------------------------------------
-- Before/After a successful upgradation:
------------------------------------------
select * from v$version;
SELECT comp_id, version, status FROM dba_registry;
Identify successful upgradation from alert.log
cat
| grep "SERVER COMPONENT" --> Its status should be VALID
-----------------------------------------------------------------------------------------
-- Database backup information (Database name,backup level,start time,elapsed time etc: )
-----------------------------------------------------------------------------------------
select count(*) from v$backup where status <> 'NOT ACTIVE';
SELECT DECODE(DECODE ((SELECT COUNT (file#)
FROM v$backup
WHERE status = 'ACTIVE'),(SELECT COUNT (file#)
FROM v$datafile)
, 'D', 0, 'N', 'T')
,'D',
(SELECT CHR (9)
|| CHR (10)
|| CHR (10)
|| 'Database '
|| UPPER(TRIM(ora_database_name))
|| ' in full backup mode..'
|| CHR (10)
|| RPAD('~',LENGTH('Database '||upper(trim(ora_database_name))||' in full backup mode..'),'~')
|| CHR (10)
|| 'Start time : '
|| TO_CHAR (MIN (TIME), 'dd-mm-yyyy hh24:mi:ss')
|| CHR (10)
|| 'Current time : '
|| TO_CHAR (SYSDATE, 'dd-mm-yyyy hh24:mi:ss')
|| CHR (10)
|| 'Elapsed time(mins) : '
|| ROUND (TO_NUMBER (SYSDATE - MIN (TIME)) * 1440)
|| CHR (10)
|| 'Elapsed time(Hrs) : '
|| ROUND ((TO_NUMBER (SYSDATE - MIN (TIME)) * 1440) / 60, 2)
|| CHR (10)
FROM v$backup)
,'T',
(SELECT COUNT(DISTINCT ts#)
||' Tablespace(S) in backup mode'
FROM v$datafile
WHERE file# IN
(SELECT file#
FROM v$backup
WHERE STATUS ='ACTIVE'))
,'N','Database/Tablespace not in backup mode') AS bkp_info
FROM DUAL
/
----------------
-- Transaction :
----------------
-- List long running transaction (Here for a RMAN session)
--
SET linesize 1000 pagesize 60 lines 1000
SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "%_COMPLETE",
time_remaining, elapsed_seconds
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> TOTALWORK
/
SET linesize 1000 pagesize 60 lines 1000
SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "%_COMPLETE",
time_remaining, elapsed_seconds
FROM v$session_longops
WHERE sid =
AND totalwork != 0
/
-----------------
-- RMAN Progress:
-----------------
BGRED=`echo "\033[41m"`
NORMAL=`echo "\033[m"`
echo "----------------------------------------------------"
echo "${BGRED} `ps -ef| grep rman|grep -iv grep` ${NORMAL}"
echo "----------------------------------------------------"
tail -4 restore_HREPPRD9.log
sqlplus "/as sysdba" << EOF
@RMAN_prog_monitor.sql
exit
EOF
-----------------------
-- Start RMAN in nohup:
-----------------------
nohup rman cmdfile=hrepprd9_duplicate_rman.rcv log=hrepprd9_duplicate_rman.log &
------------------------
-- Monitor RMAN session:
------------------------
--------------------------------------------------------------
-- Lists ongoing RMAN session
-- Note: Informations like time remaining, session information
-- has been added
--------------------------------------------------------------
set linesize 100 pagesize 60 lines 1000
--
column client_info format a30
column sid format 999999
column spid format 999999
--
prompt
prompt INFO: RMAN session information:
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt
--
select s.sid ,
p.spid,
s.client_info
from v$process p,
v$session s
where p.addr = s.paddr
and client_info like 'rman%'
/
prompt
prompt INFO: RMAN Progress:
prompt ~~~~~~~~~~~~~~~~~~~~
prompt
--
select sid ,
serial# ,
context ,
sofar ,
totalwork ,
round(sofar/totalwork*100,2) "% Complete",
time_remaining "Time remaining(secs)" ,
elapsed_seconds "Elapsed time(secs)" ,
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and totalwork != 0
and sofar <> totalwork
/
prompt
--------------------------------------------------------------
-- Datafile backup mode ( details like file id,file name etc:)
--------------------------------------------------------------
SELECT SUBSTR(TO_CHAR(a.file#),0,4) "File ID",
SUBSTR(b.NAME,0,60) "File name",
SUBSTR(a.status,0,10) "Status"
FROM v$backup a,
v$datafile b
WHERE a.file# = b.file#
/
-----------------------
-- Checking parameters
-----------------------
show parameter background_dump_dest;
archive log list;
================
3) Space management
================
1) Database level:
----------------------------
-- To find the database size
----------------------------
clear columns
clear breaks
set linesize 1000
set termout on
set feedback off
set verify off
set pagesize 0
set echo off
set recsep off
set time off
set timing off
set pagesize 60
set verify off
set lines 1000
set numwidth 11
set heading on
set wrap off
set trimspool on
column name format a9 heading "Database"
column host_name format a12 heading "Host"
column space_granted_gb format a17 heading "Space Granted(GB)"
column max_space_avilable_gb format a22 heading "Max Space Avilable(GB)"
column used_space_gb format a14 heading "Used Space(GB)"
column free_space_gb format a14 heading "Free Space(GB)"
column used_on_granted format a22 heading "%Used on Space Granted"
column used_on_avilable format a27 heading "%Used on Max Space Avilable"
--
--
SELECT SUBSTR(SUBSTR (ora_database_name, 1, LENGTH (ora_database_name)),0,8) name,
SUBSTR(e.host_name,0,15) host_name,
TO_CHAR(SUBSTR(ROUND ((a.data_size + b.temp_size + c.redo_size), 2 ),0,17)) space_granted_gb,
TO_CHAR(SUBSTR(ROUND ((a.data_size_max + b.temp_size + c.redo_size), 2 ),0,22)) max_space_avilable_gb,
TO_CHAR(SUBSTR(ROUND ((a.data_size + b.temp_size + c.redo_size) - d.free_size, 2 ),0,14)) used_space_gb,
TO_CHAR(SUBSTR(ROUND (d.free_size, 2),0,14)) free_space_gb,
TO_CHAR(SUBSTR(ROUND ( ( ((a.data_size + b.temp_size + c.redo_size) - d.free_size)
/ (a.data_size + b.temp_size + c.redo_size) ) * 100, 2 ),0,22)) used_on_granted,
TO_CHAR(SUBSTR(ROUND ( ( ((a.data_size + b.temp_size + c.redo_size) - d.free_size)
/ (a.data_size_max + b.temp_size + c.redo_size) ) * 100, 2 ),0,27)) used_on_avilable
FROM (SELECT SUM (BYTES) / 1024 / 1024 / 1024 data_size, SUM (DECODE (maxbytes, 0, BYTES, maxbytes)) / 1024 / 1024 / 1024 data_size_max
FROM dba_data_files) a,
(SELECT NVL (SUM (BYTES) / 1024 / 1024 / 1024, 0) temp_size
FROM dba_temp_files) b,
(SELECT SUM (BYTES) / 1024 / 1024 / 1024 redo_size
FROM SYS.v_$log) c,
(SELECT SUM (BYTES) / 1024 / 1024 / 1024 free_size
FROM dba_free_space) d,
(SELECT host_name
FROM v$instance) e
/
2) Tablespace level:(Normal tablesapce)
------------------------------------------------
-- To find the max size possible for a datafile:
------------------------------------------------
SELECT TO_CHAR(4194303 * VALUE,'999,999,999,999') max_bytes,
TO_CHAR(TRUNC(4194303 * VALUE / 1024),'999,999,999')||' Kb' max_kb,
TO_CHAR(TRUNC(4194303 * VALUE / 1024 / 1024),'999,999')||' Mb' max_mb
FROM v$parameter
WHERE NAME = 'db_block_size'
/
----------------------------------------------------
-- To find the tablespace usage metrics (In blocks):
----------------------------------------------------
SELECT * FROM dba_tablespace_usage_metrics;
---------------------------------------------------
-- To find the tablespace usage metrics (In bytes):
---------------------------------------------------
-- Applicabale only 10g.x and above
-- With TEMP_TABLESPACE
SELECT b.tablespace_name ,
NVL(ROUND(SUM(DECODE(b.tablespace_name,c.tablespace_name,c.bytes,a.bytes)) /1024/1024,2),0) mb ,
NVL(ROUND(SUM(DECODE(b.tablespace_name,c.tablespace_name,c.maxbytes,a.maxbytes))/1024/1024,2),0) maxmb,
NVL(ROUND(b.used_percent,2),0) used_percent
FROM dba_data_files a ,
dba_tablespace_usage_metrics b,
dba_temp_files c
WHERE a.tablespace_name(+)=b.tablespace_name
AND c.tablespace_name(+)=b.tablespace_name
AND b.tablespace_name=NVL(UPPER(TRIM('&tablespace_name')),a.tablespace_name)
GROUP BY b.tablespace_name,
b.used_percent
ORDER BY used_percent DESC
/
-- Without TEMP_TABLESPACE
SELECT b.tablespace_name ,
NVL(ROUND(SUM(a.bytes) /1024/1024,2),0) mb ,
NVL(ROUND(SUM(a.maxbytes)/1024/1024,2),0) maxmb,
NVL(ROUND(b.used_percent,2),0) used_percent
FROM dba_data_files a,
dba_tablespace_usage_metrics b
WHERE a.tablespace_name=b.tablespace_name
AND b.tablespace_name=NVL(UPPER(TRIM('&tablespace_name')),a.tablespace_name)
GROUP BY b.tablespace_name,
b.used_percent
ORDER BY used_percent DESC
/
* tablespace usage metrics included temporary tablesapce usage
--------------------------------
-- To find the tablesapce quotas
--------------------------------
set linesize 120
col username format a30
col tbs format a30
set linesize 1000 pagesize 60 lines 1000
select username username,
tablespace_name tbs,
round(sum(bytes)/1024/1024,2) usedmb,
decode(max_bytes,-1,'UNLIMITED',round(sum(max_bytes)/1024/1024),2) maxmb
from dba_ts_quotas
where username = NVL(UPPER(TRIM('&username')),username)
group by username, tablespace_name, max_bytes
/
-------------------------------------------------------
-- To find the tablesapce details in a formatted output
-------------------------------------------------------
set linesize 1000;
set termout on;
set feedback off;
set verify off;
set echo off;
set pagesize 60;
set lines 1000;
column tablespace_name format a15 heading "Tablespace";
column "avail MB" format 99999999;
column "free MB" format 99999999;
column "Used %" format 99999999;
--
--
SELECT a.tablespace_name TABLESPACE,
ROUND(a.bytes / 1024 / 1024,0) "avail MB",
ROUND(f.bytes / 1024 / 1024,0) "free MB",
ROUND((a.bytes - f.bytes) / a.bytes * 100,0) "Used %"
FROM sm$ts_avail a,
sm$ts_free f
WHERE a.tablespace_name = f.tablespace_name
AND a.tablespace_name = NVL(UPPER(TRIM('&tablespace_name')),a.tablespace_name)
ORDER BY 4 DESC
/
--------------------------------------------------------------------------------------------
-- To find the tablesapce details (file name,autoextensible,allocated space, max space etc:)
--------------------------------------------------------------------------------------------
SELECT SUBSTR(file_name,0,80) file_name,
autoextensible,
SUM(bytes) / 1024 / 1024 mb,
SUM(maxbytes) / 1024 / 1024 maxmb
FROM dba_data_files
WHERE tablespace_name = NVL(UPPER(TRIM('&tablespace_name')),tablespace_name)
GROUP BY file_name,
autoextensible
ORDER BY file_name
/
---------------------------------------------------
-- To find the tablesapce details (name, contents):
---------------------------------------------------
SELECT tablespace_name,
contents
FROM dba_tablespaces
WHERE tablespace_name IN (SELECT tablespace_name
FROM dba_data_files
WHERE file_name IN ( '&file_name' ))
/
--------------------------------------------------------------------------------------------
-- To find the tablesapce details (file_name,autoextensible,allocated space, max space etc:)
-- % used Calculated on max space)
--------------------------------------------------------------------------------------------
SET linesize 1000 pagesize 60 lines 1000 termout on feedback off verify off echo off;
COL tbs format a30
SELECT a.tbs,
ROUND(a.mb,2) alloc_mb,
ROUND(a.mb - b.mb,2) used_mb,
ROUND(a.maxmb,2) max_mb,
ROUND(a.maxmb - (a.mb - b.mb),2) free_mb,
DECODE(a.mb,0,0,ROUND(((a.mb - b.mb) / a.mb) * 100,2)) pct_on_alloc,
DECODE(a.maxmb,0,0,ROUND(((a.mb - b.mb) / a.maxmb) * 100,2)) pct_on_max,
ROUND((((a.mb - b.mb)*100)/80),0) target_size
-- ROUND((((GREATEST(a.mb,a.maxmb) - b.mb)*100)/85),0) target_size_test
FROM (SELECT tablespace_name tbs,
NVL(SUM(bytes),0) / (1024 * 1024) mb,
NVL(SUM(GREATEST(bytes,maxbytes)),0) / (1024 * 1024) maxmb
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name tbs,
NVL(SUM(bytes),0) / (1024 * 1024) mb
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tbs = b.tbs (+)
AND a.tbs = NVL(UPPER(TRIM('&tbs')),a.tbs)
/
SELECT SUBSTR (a.tbs, 0, 20) tbs,
ROUND (NVL (a.bytes, 0) / 1024 / 1024, 2) alloc_mb,
ROUND ((NVL (a.bytes, 0) - NVL (b.bytes, 0)) / 1024 / 1024, 2) used_mb,
ROUND (NVL (a.maxbytes, 0) / 1024 / 1024, 2) max_mb,
ROUND (NVL (a.maxbytes - (NVL (a.bytes, 0) - NVL (b.bytes, 0)), 0) / 1024 / 1024, 2) free_mb,
DECODE (NVL (a.bytes, 0),0,0,ROUND (((NVL (a.bytes, 0) - NVL (b.bytes, 0)) / NVL (a.bytes, 0)) * 100, 2)) pct_on_alloc,
DECODE (NVL (a.maxbytes, 0),0,0,ROUND (((NVL (a.bytes, 0) - NVL (b.bytes, 0)) / NVL (a.maxbytes, 0)) * 100, 2)) pct_on_max
FROM (SELECT tablespace_name tbs, NVL(SUM (bytes),0) bytes,
NVL(SUM
(CASE
WHEN (DECODE (maxbytes, 0, bytes, maxbytes) >= bytes)
THEN DECODE (maxbytes, 0, bytes, maxbytes)
ELSE bytes
END
),0) maxbytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name tbs,
NVL (SUM (bytes), 0) bytes
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tbs = b.tbs(+)
AND a.tbs = NVL(UPPER(TRIM('&tablespace_name')),a.tbs)
ORDER BY DECODE (NVL (a.maxbytes, 0),0,0,ROUND (((NVL (a.bytes, 0) - NVL (b.bytes, 0)) / NVL (a.maxbytes, 0)) * 100, 2)) DESC
/
2) Tablespace level:(Temporary tablesapce)
--------------------------------
-- For ORA-1652 specific errors:
--------------------------------
SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE'
/
SELECT used_percent
FROM dba_tablespace_usage_metrics
WHERE tablespace_name=
(SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE')
/
SELECT TABLESPACE_NAME,
USED_PERCENT
FROM dba_tablespace_usage_metrics
WHERE TABLESPACE_NAME IN
(SELECT tablespace_name
FROM dba_tablespaces
WHERE contents='TEMPORARY')
/
select name,sum(bytes)/1024/1024 mb from v$tempfile group by name;
-------------------------------------------------------
-- To calculate the free space in temporary tablespace:
-------------------------------------------------------
SELECT tablespace_name, bytes_used/1024/1024 used_mb, bytes_free/1024/1024 free_mb FROM v$temp_space_header;
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS,MAX_BLOCKS from v$sort_segment;
select sum(BLOCKS) from v$sort_usage where username='SYS';
/* DBA_FREE_SPACE does not record free space for temporary tablespaces.Use V$TEMP_SPACE_HEADER instead.*/
SELECT SUM( u.blocks * blk.block_size)/1024/1024 "Mb. in sort segments",
(hwm.max * blk.block_size)/1024/1024 "Mb. High Water Mark"
FROM v$sort_usage u,
(SELECT block_size
FROM dba_tablespaces
WHERE contents = 'TEMPORARY') blk ,
(SELECT segblk#+blocks MAX
FROM v$sort_usage
WHERE segblk# =
(SELECT MAX(segblk#)
FROM v$sort_usage)) hwm
GROUP BY hwm.max * blk.block_size/1024/1024
/
SELECT tablespace_name,
extent_size ,
total_extents ,
used_extents ,
free_extents ,
max_used_size
FROM v$sort_segment
/
SELECT a.tablespace,
b.sid ,
b.serial# ,
a.blocks ,
c.sql_text
FROM v$tempseg_usage a,
v$session b ,
v$sqltext c
WHERE a.session_addr = b.saddr
AND b.sql_address = c.address
AND a.tablespace = NVL(UPPER(TRIM('&tablespace_name')),
(SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE'))
ORDER BY a.tablespace,
b.sid ,
b.serial# ,
c.address ,
c.piece
/
-- Monitoring the temporary tablespace:
SELECT segtype,
extents,
blocks
FROM v$tempseg_usage
WHERE TABLESPACE = NVL(UPPER(TRIM('&tablespace_name')),
(SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE'))
/
------------------------------------------------------------------------------------------------------
-- To find the temporary tablesapce details (file name,autoextensible,allocated space, max space etc:)
------------------------------------------------------------------------------------------------------
set linesize 1000;
set termout on;
set feedback off;
set verify off;
set echo off;
set pagesize 60;
set lines 1000;
column tablespace_name format a30;
column mb format 999999;
column increment_by format 999999;
column maxmb format 999999;
column autoextensible format a15;
--
--
SELECT tablespace_name,
SUM(bytes) / 1024 / 1024 mb,
autoextensible,
SUM(increment_by) / 1024 / 1024 increment_by,
SUM(maxbytes) / 1024 / 1024 maxmb
FROM dba_temp_files
WHERE tablespace_name = NVL(UPPER(TRIM('&tablespace_name')),(SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE'))
GROUP BY tablespace_name,
autoextensible
/
------------------------------------------------------------------------------------------------------------
-- To find the temporary tablesapce details (file name,autoextensible,allocated space, max space, file etc:)
------------------------------------------------------------------------------------------------------------
SELECT SUBSTR(tablespace_name,0,20) TABLESPACE,
SUBSTR(file_name,0,55) file_name,
SUM(bytes) / 1024 / 1024 mb,
SUBSTR(autoextensible,0,4) autoextensible,
SUM(increment_by) / 1024 / 1024 increment_by,
SUM(maxbytes) / 1024 / 1024 maxmb
FROM dba_temp_files
WHERE tablespace_name = (SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE')
GROUP BY tablespace_name,
file_name,
autoextensible
/
---------------------------------------------------------------------
-- To report true free space within the used portion of the TEMPFILE:
---------------------------------------------------------------------
SELECT a.tablespace_name tablespace,
d.mb_total,
SUM(a.used_blocks * d.block_size) / 1024 / 1024 mb_used,
d.mb_total - SUM(a.used_blocks * d.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment a,
(SELECT b.NAME,
c.block_size,
SUM(c.bytes) / 1024 / 1024 mb_total
FROM v$tablespace b,
v$tempfile c
WHERE b.ts# = c.ts#
GROUP BY b.NAME,
c.block_size) d
WHERE a.tablespace_name = d.NAME
GROUP BY a.tablespace_name,
d.mb_total
/
----------------------------
-- To find HWM in datafiles:
----------------------------
/*
Note:
-----
The following script to generate alter database datafile '......' resize
commands to shrink files down to their smallest possible size. Just bear in mind that
unless you have autoextend on -- if you shrink the file you just gave up all of that free
space. You might not be able to extend your tables into new extents!
*/
--
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
--
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
--
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
--
column cmd format a75 word_wrapped
--
select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
=================
4) Schema management
=================
--------------------------------------------
-- Count total rows in each tables in schema
--------------------------------------------
-- Identify the users, created after db creation
--
SELECT username,
created
FROM dba_users
WHERE TO_DATE(TO_CHAR(created, 'ddmmyy'), 'ddmmyy') <> (SELECT TO_DATE(TO_CHAR(created, 'ddmmyy'), 'ddmmyy')
FROM v$database)
/
-- Count number of rows in Each Table (User Wise)
-- Run in SYS Schema
--
SELECT owner,
table_name,
To_number(Extractvalue(Xmltype(dbms_xmlgen.Getxml('select count(*) c from '
||owner
||'.'
||table_name)),'/ROWSET/ROW/C')) COUNT
FROM dba_tables
WHERE owner = Nvl(Upper('&owner'),owner)
AND owner NOT IN ('PERFSTAT','TSMSYS','BI','PM','MDDATA','IX','SH','DIP','OE','DBSNMP','SYSMAN','MDSYS',
'ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGI NS',
'SI_INFORMTN_SCHEMA','OLAPSYS','MGMT_VIEW','SYS','SYSTEM','OUTLN')
ORDER BY 1
/
-- Query 2
--
SELECT
table_name,
TO_NUMBER(extractvalue(XMLTYPE(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) COUNT
FROM DBA_TABLES
where owner=NVL(UPPER(TRIM('&owner')),ora_login_user)
ORDER BY 1
/
---------------------------
-- Top-5 tables in a schema
---------------------------
SELECT *
FROM (SELECT SUBSTR(segment_name,0,30) "Segment name",
SUBSTR(segment_type,0,15) "Segment type",
SUM(bytes) / 1024 / 1024 "Mb"
FROM dba_segments
WHERE owner='PERFSTAT'
GROUP BY segment_name, segment_type, owner
ORDER BY 3 DESC)
WHERE ROWNUM <= 5
/
=========================
5) Object/Segment management
=========================
------------------------
-- To find dependencies:
------------------------
define obj_name = &object
SELECT name FROM dba_dependencies WHERE referenced_name = upper(trim('&&obj_name'))
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = upper(trim('&&obj_name'))
/
undefine obj_name = &object
----------------------------
-- To find HWM in a segment:
----------------------------
SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table)
/
ANALYZE TABLE owner.table ESTIMATE STATISTICS
/
SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND TABLE_NAME = UPPER(table)
/
/*
Note:
-----
Calculation : Thus, the tables' HWM = (query result 1) - (query result 2) - 1
NOTE : We can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.
*/
===============================
6) How to manage block corruption:
===============================
------------------------------------------
-- To find the corrupted file and segment:
------------------------------------------
-- We can observe this error message in alert.log
-- Check the dbv log file for "Block Checking: DBA =
, Block Type = KTB-managed data block"
-- Eg: Block Checking: DBA = 229422437, Block Type = KTB-managed data block
--
--
-- Gets the file number part of a data block address
select dbms_utility.data_block_address_file('') from dual ;
-- Eg:
-- select dbms_utility.data_block_address_file('229422437') from dual ;
-- Gets the datafile name
select name from v$datafile where file#=
;
-- Gets the block number part of a data block address
select dbms_utility.data_block_address_block('
') from dual ;
-- Eg:
-- select dbms_utility.data_block_address_block('229422437') from dual ;
-- Gets the corrupted schema,segment, segment type
set linesize 100;
column owner format a15;
column segment_name format a30;
column segment_type format a30;
SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &file_id
AND &block_no BETWEEN block_id AND block_id + blocks - 1
/
-- Analyze segment
analyze
. validate structure [cascade];
-- Eg:
-- analyze table SAPPF1.SWW_CONTOB validate structure;
--> Need to edit ************
SQL> select object_id from dba_objects where object_name='SWW_CONTOB' and owner='SAPPF1';
OBJECT_ID
----------
63835
SQL> select dbms_rowid.rowid_create(1,63835,54,2930021,0)min_rid,dbms_rowid.rowid_create(1,63835,54,2930021+1,0)max_rid from dual;
MIN_RID MAX_RID
------------------ ------------------
AAAPlbAA2AALLVlAAA AAAPlbAA2AALLVmAAA
select count(*) cnt from SAPPF1.SWW_CONTOB where rowid between 'AAAPlbAA2AALLVlAAA' and 'AAAPlbAA2AALLVmAAA';
select count(*) cnt from SAPPF1.SWW_CONTOB where rowid between 'AAAPlbAA2AALLVlAAA' and 'AAAPlbAA2AALLVmAAA';
Link-
http://forums.oracle.com/forums/thread.jspa?threadID=1052408&tstart=30
--< Need to edit ************
/*
----------
Example 1:
----------
SELECT segment_type, owner ||'.'||segment_name
FROM dba_extents
WHERE file_id = 9 AND 25759 BETWEEN block_id AND block_id + blocks - 1
/
----------
Example 2:
----------
SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = 8 AND 82172 BETWEEN block_id AND block_id + blocks - 1
/
*/
====================
6) Backup and Recovery
====================
-------------------------------------------------------
Data movement from one site to another (Export-Import):
-------------------------------------------------------
nohup exp "'/ as sysdba'" file=
.dmp log=.log full=y buffer=1000000 consistent=y statistics=none &
nohup imp '"/ as sysdba"' file=.dmp log=.log full=y commit=y buffer=1000000 ignore=y feedback=100000 &
@?/rdbms/admin/utlrp.sql
exec DBMS_STATS.GATHER_DATABASE_STATS (degree => 4,cascade => true ,estimate_percent => dbms_stats.auto_sample_size);
-------------
---------------
-- Watch Import
---------------
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate - to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1) minutes,
trunc(rows_processed / ((sysdate - to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0
/
Data pump:
----------
Estimate dump size
------------------
expdp "'/ as sysdba'" full=y estimate_only=y nologfile=y job_name=exp_dump_size
expdp "'/ as sysdba'" schemas=scott estimate_only=y nologfile=y job_name=exp_dump_size
---------------------------------
-- Monitor datapump Export/import
---------------------------------
select OWNER_NAME,JOB_NAME,OPERATION,STATE,JOB_MODE,ATTACHED_SESSIONS,DATAPUMP_SESSIONS from dba_datapump_jobs;
SELECT sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_COMPLETE",time_remaining, elapsed_seconds FROM v$session_longops WHERE opname LIKE '%EXP%';
select sid,
serial#
from v$session s,
dba_datapump_sessions d
where s.saddr = d.saddr;
select sid,
serial#,
sofar,
totalwork
from v$session_longops;
Example for invoking expdp using par file
------------------------------------------
File name = export_full_METADATA_ONLY_10042010.par
Content
directory=datapump_dir
dumpfile=exp_icustp_full_10042010_%U.dmp
filesize=4G
full=y
CONTENT=METADATA_ONLY
job_name=icustp_full_export
logfile=exp_icustp_full_10042010.log
status=300
parallel=8
Calling methode: nohup expdp
/ parfile=export_full_METADATA_ONLY_10042010.par &
-------------------------------------
Full db Export with nohup option -
nohup exp "'/ as sysdba'" file=
.dmp log=.log full=y rows=y indexes=y constraints=y grants=y buffer=1000000 &
Example:
--------
nohup exp "'/ as sysdba'" file=exp_pac-mt_091009.dmp log=exp_pac-mt_091009.log owner=pac-mt direct=y buffer=1000000 consistent=y &
Checking log files After import -
egrep "^IMP-^J^ORA-" imp_full_nncc_20090129.log| grep -v "already exists" | grep -v "compilation warnings"
Import:
-------
nohup imp '"/ as sysdba"' file=exp_sops4_091209.dmp log=imp_sops4_091209.log fromuser=sops4 touser=sops6 commit=y ignore=y &
Depending on the size of the export data we have to use pipe command to export with compress and split:
Example:
--------
1) mkdir for export:
Here
2) Change directory to the EXPORT_DIR:
cd
3) Remove previous pipe (if any):
rm -f export_pipe
4) Make new pips (Compress):
/usr/sbin/mknod mknod export_pipe p
chmod 666 export_pipe
5) Start the Compress backgroud processes:
nohup cat export_pipe | gzip -9 > expdat
.dmp.gz &
6) Finally, start the export to pipe:
exp "'/ as sysdba'" file=export_pipe owner=
consistent=y direct=y log=.log
7) Remove the pipe:
rm -f export_pipe
Example:
--------
1)
orauis@s96uis01:/dump_2/exp/TT11236224 : /usr/sbin/mknod /dump_2/exp/TT11236224/exp_pipe p
orauis@s96uis01:/dump_2/exp/TT11236224 : ls
exp_pipe
orauis@s96uis01:/dump_2/exp/TT11236224 : nohup gzip -c6 < /dump_2/exp/TT11236224/exp_pipe > export_uis.dmp.gz &
[1] 4309
orauis@s96uis01:/dump_2/exp/TT11236224 : nohup exp edcadm/+ora4uss file=/dump_2/exp/TT11236224/exp_pipe full=y direct=y log=export_uis.log statistics=none consistent=yes &
[2] 10972
orauis@s96uis01:/dump_2/exp/TT11236224 : Sending output to nohup.out
Sending output to nohup.out
orauis@s96uis01:/dump_2/exp/TT11236224 : ls
exp_pipe export_uis.dmp.gz export_uis.log nohup.out
2)
make pipe -
ora_qda8@s96qdap4:/oracle/data/TT9967439 : /usr/sbin/mknod /oracle/data/TT9967439/exp_pipe p
define gzip of dump file -
ora_qda8@s96qdap4:/oracle/data1/TT9967439 : nohup gzip -c6 < /oracle/data/TT9967439/exp_pipe > export_pac-mt..dmp.gz &
export command -
ora_qda8@s96qdap4:/oracle/data1/TT9967439 : nohup exp "'/ as sysdba'" file=/oracle/data/TT9967439/exp_pipe owner= PAC-MT consistent=y direct=y log=export_pac-mt.log &
---> ref link:
http://www.idevelopment.info/data/Oracle/DBA_tips/Export_Import/EXP_2.shtml
DB level export:
----------------
nohup exp "'/ as sysdba'" file = WFS_280110.dmp log = WFS_280110.log full=y rows=y buffer=1000000 &
Schema level export/import:
---------------------------
nohup exp "'/ as sysdba'" file=exp_sops4_091209.dmp log=exp_sops4_091209.log owner=sops4 direct=y buffer=1000000 consistent=y &
nohup imp '"/ as sysdba"' file=exp_sops4_091209.dmp log=imp_sops4_091209.log fromuser=sops4 touser=sops6 commit=y ignore=y &
nohup imp '"/ as sysdba"' file=leadp_full_201002010200.dmp log=imp_leadp_full_201002010200.log fromuser=EDISON touser=PROTOKOLL tables=TBLLEADSESSIONS_2009,TBLCPMSPROTOKOLL_2009,TBLLEADPROTOKOLL_2009_PART1,TBLLEADPROTOKOLL_2009_1 commit=y ignore=y buffer=1000000 &
Note:
-----
User the command to copy the schema-
set long 100000
select dbms_metadata.get_ddl('USER','SOPS6') user_ddl from dual;
7) ==================
Process managemant
==================
-- List the number of user sessions , OS processes that has been idle
-- from the past 12 Hrs.except Background processes.
--
SELECT s.SID "Sid", s.serial# "Serial#", p.spid " OS Process", s.osuser " OS User"
, s.program "Program", s.logon_time "Log on time",last_call_et "Last call"
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.status = 'INACTIVE'
AND s.username IS NOT NULL
AND p.background IS NULL
AND ROUND (s.last_call_et / 3600, 2) > 12
/
-------------------------------------------------------------
-- How to kill oracle session and corresponding OS process -
-------------------------------------------------------------
-- Run the SQLs in the following order
-- 1)
SELECT p.spid, s.sid, s.serial#
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.type='USER'
AND s.username= NVL(UPPER(TRIM('&user')),s.username);
-- 2)
SELECT 'ALTER SYSTEM KILL SESSION '''||s.sid||','||s.serial#||''' IMMEDIATE;'
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.type='USER'
AND s.username= NVL(UPPER(TRIM('&user')),s.username);
-- alter system kill session '39,44214' immediate;
-- 3)
SELECT 'kill -9 '||p.spid os_cmd
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.type='USER'
AND s.username= NVL(UPPER(TRIM('&user')),s.username);
=================================================
-- EDCADM/Keypass-Y:\05_DB_TEAM S/w pwd: +ora4uss
=================================================
Scripting in oracle
===================
for i in audit_e cognos82 cognos8e crne crni dwhgse pcrepoe pcrepoi pcrepou dwhxas_e
do
export ORACLE_SID=$i
echo
$ORACLE_SID@`hostname`
echo '-----------------------'
echo "select count(*) from v\$session;"| sqlplus "/ as sysdba"
echo "select count(username) from dba_users;"| sqlplus "/ as sysdba"
done
for i in hraap9fg hrepbas9 hrepdev9 hrepdmo9 hrepfg9 hrepint9 hrepipw9 hrepmiga hrepmod9 hrepmst9 hrepply9 hrepupg9 hrwt9dm2 upkit
do
export ORACLE_SID=$i
sqlplus -S "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
done
script for User creation in icust
=================================
for i in
do
echo "--------------- $i ---------------" >> icust_user_cre_.log
sqlplus /nolog @mic_create_user_dcag.sql edcadm icustp $i $i MIC_APP_ALLG
echo "select count(*) from MIC_CCS_WAEPOS;"|sqlplus -S $i/$i >> icust_user_cre.log
echo "----------------------------------" >> cust_user_cre_.log
done
Example:
--------
for i in ANDMEIE BSTROBU XV12K1S YVOORWO MANKOCK DAJESS DGEWERT MBAJIC AMILSKI MLAMBER HESCHM1 TISIMON MARGFIS MEDENZL JKOLLEW JULIA7S
do
echo "--------------- $i ---------------" >> icust_user_cre_.log
sqlplus /nolog @mic_create_user_dcag.sql edcadm XXXX icustp $i $i MIC_APP_ALLG
echo "select count(*) from MIC_CCS_WAEPOS;"|sqlplus -S $i/$i >> icust_user_cre.log
echo "----------------------------------" >> cust_user_cre_.log
done
==============
Typical Errors
==============
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL2'
Check :
http://www.dbasupport.com/forums/archive/index.php/t-56974.html
oracle@s96gssi5z1:/home/oracle : for i in spas asrapubl tic aps cas ppt repa sac tic ticon
do
export ORACLE_SID=$i
sqlplus "/ as sysdba" <
select * from v\$instance;
exit
;
EOF
done
=======================================
RMAN
=====
Trouble shooting RMAN (10g) -
http://cs.scu.edu.cn/~zuojie/oracle/server.101/b10734/rcmtroub.htm
Links
=====
http://www.orafaq.com/wiki/Oracle_database_FAQ
To add (Verify and Add)
=======================
Rename a tablespace
-------------------
ALTER TABLESPACE ts1 RENAME TO ts2;
However, you must adhere to the following restrictions:
* COMPATIBILITY must be set to at least 10.0.1
* Cannot rename SYSTEM or SYSAUX
* Cannot rename an offline tablespace
* Cannot rename a tablespace that contains offline datafiles
For older releases, use the following workaround:
* Export all of the objects from the tablespace
* Drop the tablespace including contents
* Recreate the tablespace
* Import the objects
To find used/free space in a TEMPORARY tablespace
-------------------------------------------------
Unlike normal tablespaces, true temporary tablespace information is not listed
in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:
SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;
To report true free space within the used portion of the TEMPFILE:
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
To know used space of temp tablespace:
-------------------------------------------------
SELECT property_value
2 FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE'
SELECT used_percent
FROM dba_tablespace_usage_metrics
WHERE tablespace_name=
(SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE')
/
To monitor who is using a temporary segment
-------------------------------------------
1) For every user using temporary space, there is an entry in SYS.V$_LOCK with type
'TS'.
2) All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in
and 'bbbb' is first block of the segment.
3) If your temporary tablespace is set to TEMPORARY, all sorts are done in one
large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT
From Oracle 8, one can just query SYS.v$sort_usage. Look at these examples:
select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from sys.v_$session s, sys.v_$sort_usage u
where s.saddr = u.session_addr
/
select s.osuser, s.process, s.username, s.serial#,
sum(u.blocks)*vp.value/1024 sort_size
from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp
where s.saddr = u.session_addr
and vp.name = 'db_block_size'
and s.osuser like '&1'
group by s.osuser, s.process, s.username, s.serial#, vp.value
/
To monitor who all are using which UNDO or TEMP segment
-------------------------------------------------------
Execute the following query to determine who is using a particular UNDO or Rollback Segment:
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,r.name undoseg,t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,sys.v_$session s,sys.v_$transaction t,sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
/
SID_SERIAL ORAUSER PROGRAM UNDOSEG Undo
---------- ---------- ------------------------------ --------------- -------
260,7 SCOTT
sqlplus@localhost.localdomain _SYSSMU4$ 8K
(TNS V1-V3)
Execute the following query to determine who is using a TEMP Segment:
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,sys.v_$sort_usage b,sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks
/
TABLESPACE SIZE SID_SERIAL USERNAME PROGRAM
---------- ------- ---------- -------- ------------------------------
TEMP 24M 260,7 SCOTT
sqlplus@localhost.localdomain
(TNS V1-V3)
To get the view definition of fixed views/tables
------------------------------------------------
Query v$fixed_view_definition. Example:
SELECT * FROM v$fixed_view_definition WHERE view_name='V$SESSION';
To monitor the fill level of current redo log file
--------------------------------------------------
Here is a query that can tell you how full the current redo log file is.
Handy for when you need to predict when the next log file will be archived out.
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8
/
Current log sequence No Percent Full Current Block No Size of Log in Blocks
----------------------- ------------ ---------------- ---------------------
416 48.6669922 49835 102400
or
Finding how much percentage of current redo log is filled is bit tricky since
the information is not exposed in any V$ views. We have to directly query the
X$tables to get that information. The X$views we use here are x$kccle (Kernel
Cache ControlfileComponent Log file Entry) and x$kcccp (Kernel Cache Checkpoint Progress).
select le.leseq current_log_sequence#,
100 * cp.cpodr_bno / le.lesiz percentage_full
from x$kcccp cp, x$kccle le
where le.leseq =cp.cpodr_seq
and le.ledup != 0
/
Setting alias for sqlplus '/as sysdba' in OS level
--------------------------------------------------
If you are tired of typing [i]sqlplus "/as sysdba"[/i] every time you want to
perform some DBA task, implement the following shortcut:
On Unix/Linux systems:
Add the following alias to your .profile or .bash_profile file:
alias sss='sqlplus "/as sysdba"'
On Windows systems:
Create a batch file, sss.bat, add the command to it, and place it somewhere in
your PATH. Whenever you now want to start sqlplus as sysdba, just type "sss".
Much less typing for ya lazy DBA's.
Note: From Oracle 10g you don't need to put the "/AS SYSDBA" in quotes anymore.
To check what all are the patches are installed in Oracle Home
--------------------------------------------------------------
DBA's often do not document the patches they install. This may lead to
situations where a feature works on machine X, but not on machine Y. This FAQ
will show how you can list and compare the patches installed within your Oracle
Homes.
All patches that are installed with Oracle's OPatch Utility (Oracle's Interim
Patch Installer) can be listed by invoking the opatch command with the lsinventory
option. Here is an example:
$ cd $ORACLE_HOME/OPatch
$ opatch lsinventory
Invoking OPatch 10.2.0.1.0
Oracle interim Patch Installer version 10.2.0.1.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
...
Installed Top-level Products (1):
Oracle Database 10g 10.2.0.1.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
OPatch succeeded.
NOTE: If OPatch is not installed into your Oracle Home ($ORACLE_HOME/OPatch), you may need to download it from Metalink and install it yourself.
How does one give developers access to trace files (required as input to tkprof)
--------------------------------------------------------------------------------
The alter session set sql_trace=true command generates trace files in
USER_DUMP_DEST that can be used by developers as input to tkprof. On Unix the
default file mask for these files are "rwx r-- ---".
There is an undocumented INIT.ORA parameter that will allow everyone to read
(rwx r-- r--) these trace files:
_trace_files_public = true
Include this in your INIT.ORA file and bounce your database for it to take effect.
Oracle SYSDATE and calculating time
===================================
One of the confounding problems with Oracle DATE datatypes is the computation of
elapsed time. Oracle SYSDATE functions can be used to convert an elapsed time
into rounded elapsed minutes, and place the value inside an Oracle table.
SQL> select sysdate-(sysdate-3) from dual;
SYSDATE-(SYSDATE-3)
-------------------
3
Here we see that elapsed times are expressed in days. Hence, we can use easy
conversion functions to convert this to hours or minutes:However, when the
minutes are not a whole number, we have the problem of trailing decimal places:
select (sysdate-(sysdate-3.111))*1440 from dual;
(SYSDATE-(SYSDATE-3.111))*1440
------------------------------
4479.83333
Of course, we can overcome this with the ROUND function with Oracle SYSDATE,
remembering that we must first convert the DATE datatype to a NUMBER type:
select round(to_number(sysdate-(sysdate-3.111))*1440) from dual;
ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)
----------------------------------------------
4480
SQLs to view OS process and Oracle session info
===============================================
-- This script provides information on active SQL. (This was built for use on Oracle9i Database.)
--
SELECT s.sid, s.status, s.process, s.osuser, a.sql_text, p.program
FROM v$session s, v$sqlarea a, v$process p
WHERE s.sql_hash_value = a.hash_value
AND s.sql_address = a.address
AND s.paddr = p.addr
AND s.schemaname = '&user'
AND s.sid=&sid
AND s.serial#=&serial
AND s.status = 'ACTIVE'
/
Use this commands and sql query to show information about OS process and Oracle user session.
Useful if for example you notice a single process is chewing up CPU on the server.
At the Unix prompt (replace 22910 with the actual process id):
$ ps -ef | grep 22910
oracle 22910 1 14 09:16:59 ? 32:09 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
In sqlplus or TOAD run this query:
SELECT s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (22910);
If otherwise you need to identify Unix process for a specific Oracle database session run SQL
statement below (assuming SIDs are 39 and 24, can also put username or osuser etc in whe where
clause):
SELECT p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (39, 24);
PROGRAM SPID
------------------------------ --------- ...
oracle@hostname (TNS V1-V3) 590 ...
oracle@hostname (TNS V1-V3) 6190 ...
At the OS prompt get the process information
ps -ef | egrep '590|6190'
oracle 18232 14573 0 10:16:17 pts/4 0:00 egrep 590|6190
oracle 6190 1 0 Jul 28 ? 14:40 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 590 1 0 Jul 30 ? 12:10 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
-- SQL to view SQL text for user session
-- Replace DB_USER with the actuall database username and os_user with the OS username.
select a.sql_text, b.username, b.osuser, b.logon_time, b.program,b.machine, b.sid, b.serial#
from v$sql a, v$session b
where a.address = b.sql_address
and b.username = 'SYS'
and b.osuser = 'os_user';
Statistics
==========
Gather statistics example:
begin
dbms_stats.gather_table_stats (ownname => 'MICAPP',tabname => 'MIC_EINKOP',partname => null,estimate_percent => NULL,block_sample => false,method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',degree => 4,granularity => 'ALL',cascade => true);
end;
/
SR update templet
=================
Hello,
Thank you for your patience.
Please keep this session active for few more days.
We are still waiting for the feedback from customer.
Regards,
EDC_Admin
To Check
========
Note 352907.1:Script To Run DBV On All Datafiles Of the Database
+ Please review Instance memory parameter & try to accomodate all oracle SGA in physical memory.
+ Refer following metalink note for memory related parameter on AIX.
Note 316533.1 AIX: Database performance gets slower the longer the database is running
Swap Activities
Using: lsps -a
Page Space Physical Volume Volume Group Size %Used Active Auto Type
hd6 hdisk1 rootvg 12288MB 25 yes yes lv
==============================================
------------------------------------------------
-- Extracting the active SQL a user is executing
------------------------------------------------
SELECT sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
FROM v$sqlarea sqlarea,
v$session sesion
WHERE sesion.sql_hash_value = sqlarea.hash_value
AND sesion.sql_address = sqlarea.address
AND sesion.username IS NOT NULL
AND sesion.sid = &sid AND sesion.serial# = &serial
----------------------------------------
-- Find most active SQL in the last hour
----------------------------------------
1)
SELECT session_id,
session_serial#,
sql_id,
COUNT(* ),
ROUND(COUNT(* ) / SUM(COUNT(* ))
OVER(),2) pctload
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1 / 24
AND session_type = 'BACKGROUND'
GROUP BY sql_id,
session_id,
session_serial#
ORDER BY COUNT(* ) DESC;
2)
SELECT session_id,
session_serial#,
sql_id,
COUNT(* ),
ROUND(COUNT(* ) / SUM(COUNT(* ))
OVER(),2) pctload
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1 / 24
AND session_type = 'FOREGROUND'
GROUP BY sql_id,
session_id,
session_serial#
ORDER BY COUNT(* ) DESC;
--------------------------------------------------
-- To find the scheduled jobs using dbms_scheduler
--------------------------------------------------
SELECT job_name,
failure_count,
To_char(start_date,'dd-mm-yy hh:mi:ss') start_date,
repeat_interval,
job_action
FROM dba_scheduler_jobs
WHERE job_name = (SELECT object_name
FROM dba_objects
WHERE object_id = 1678766);
-- Ref:
http://forums.oracle.com/forums/thread.jspa?threadID=651890
-- Ref:
http://www.oradev.com/dbms_scheduler.jsp
--------------------------------------------------------
-- To find all the privileges granted for a user in db:
--------------------------------------------------------
SET serveroutput on;
execute DBMS_OUTPUT.ENABLE (1000000);
BEGIN
---------------------
-- system privileges:
---------------------
dbms_output.PUT_LINE(RPAD('System privileges',30,' '));
dbms_output.PUT_LINE(RPAD('=',30,'='));
FOR i IN (SELECT privilege
FROM dba_sys_privs
WHERE grantee = 'TESTER')
LOOP
dbms_output.PUT_LINE(RPAD(i.privilege,30,' '));
END LOOP;
dbms_output.PUT_LINE(CHR(10));
--------------------
-- Table privileges:
--------------------
dbms_output.PUT_LINE(RPAD('Object privileges',30,' '));
dbms_output.PUT_LINE(RPAD('=',30,'='));
dbms_output.PUT_LINE(RPAD('OWNER',15,' ')||CHR(32)||RPAD('TABLE NAME',30,' ')||CHR(32)||RPAD('PRIVILEGE',30,' '));
dbms_output.PUT_LINE(RPAD('-',15,'-')||CHR(32)||RPAD('-',30,'-')||CHR(32)||RPAD('-',30,'-'));
FOR i IN (SELECT owner,
table_name,
privilege
FROM dba_tab_privs
WHERE grantee = 'TESTER')
LOOP
dbms_output.PUT_LINE(RPAD(i.owner,15,' ')||CHR(32)||RPAD(i.table_name,30,' ')||CHR(32)||RPAD(i.privilege,30,' '));
END LOOP;
dbms_output.PUT_LINE(CHR(10));
--------------------
-- Role privileges:
--------------------
dbms_output.PUT_LINE(RPAD('Roles granted',30,' '));
dbms_output.PUT_LINE(RPAD('=',30,'='));
FOR i IN (SELECT granted_role
FROM dba_role_privs
WHERE grantee = 'TESTER')
LOOP
dbms_output.PUT_LINE(RPAD(i.granted_role,30,' '));
END LOOP;
END;
/
-----------
Dependency:
-----------
col REFERENCED_NAME format a20
col REFERENCED_TYPE format a15
SELECT NAME,
TYPE,
referenced_name,
referenced_type
FROM dba_dependencies
WHERE NAME = 'PROC_GET_DB_SIZE'
OR referenced_name = 'PROC_GET_DB_SIZE'
UNION
SELECT NAME,
TYPE,
referenced_name,
referenced_type
FROM dba_dependencies
WHERE NAME = 'GET_DB_SIZE'
OR referenced_name = 'GET_DB_SIZE';
col object_name format a20
col object_type format a15
SELECT object_name,
status
FROM dba_objects
WHERE object_name IN ('PROC_GET_DB_SIZE','GET_DB_SIZE');
-----------------------------------
-- Checking the background process:
-----------------------------------
SELECT COUNT(* ) FROM v$process WHERE background = 1
/
SELECT program FROM v$process WHERE background = 1
/
---------------------------------------------------------------------------
-- Checking archive log history/log switch history (time of archive, etc:):
---------------------------------------------------------------------------
SELECT sequence# seq,
TO_CHAR(first_time,'dd-mm-yy hh24:mi:ss') archived_on
FROM v$log_history
WHERE first_time > TRUNC(SYSDATE) - 1
/
SELECT sequence# seq,
TO_CHAR(first_time,'dd-mm-yy hh24:mi:ss') archived_on
FROM v$log_history
WHERE first_time > TRUNC(SYSDATE) - 1;
set lines 120;
set pages 999;
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM < 8
/
**** !!! Need to check !!! ****
select substr(first_time,1,9) day,
to_char(sum(decode(substr(first_time,10,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(first_time,10,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(first_time,10,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(first_time,10,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(first_time,10,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(first_time,10,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(first_time,10,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(first_time,10,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(first_time,10,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(first_time,10,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(first_time,10,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(first_time,10,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(first_time,10,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(first_time,10,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(first_time,10,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(first_time,10,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(first_time,10,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(first_time,10,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(first_time,10,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(first_time,10,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(first_time,10,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(first_time,10,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(first_time,10,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(first_time,10,2),'23',1,0)),'99') "23"
from v$log_history
group by substr(first_time,1,9);
------------------------------------
-- To find tablespace creation time:
------------------------------------
SELECT a.ts#,
a.name tbsname,
TO_CHAR(MIN(b.creation_time),'dd-mm-yy hh24:mi:ss') created
FROM v$tablespace a,
v$datafile b
WHERE a.ts# = b.ts#
GROUP BY a.name,
a.ts#
ORDER BY a.ts#
/
------------------------------------------------------
- To find tablespaces created after database creation:
------------------------------------------------------
SELECT p.tbs#,
p.tbsname,
TO_CHAR(p.created,'dd-mm-yyyy') created
FROM (SELECT a.ts# tbs#,
a.name tbsname,
TO_DATE(TO_CHAR(MIN(b.creation_time),'dd-mm-yy'),
'dd-mm-yy') created
FROM v$tablespace a,
v$datafile b,
v$database c
WHERE a.ts# = b.ts#
GROUP BY a.name,
a.ts#
ORDER BY a.ts#) p,
(SELECT TO_DATE(TO_CHAR(created,'dd-mm-yy'),'dd-mm-yy') created
FROM v$database) q
WHERE p.created > q.created
ORDER BY p.created
/
------------------------------------------
- Datafiles added after database creation:
------------------------------------------
SELECT a.file#,
a.name,
TO_CHAR(a.creation_time,'dd-mm-yyyy') created
FROM v$datafile a,
v$database b
WHERE TO_DATE(TO_CHAR(a.creation_time,'dd-mm-yy'),'dd-mm-yy') > TO_DATE(TO_CHAR(b.created,'dd-mm-yy'),'dd-mm-yy')
ORDER BY TO_DATE(TO_CHAR(a.creation_time,'dd-mm-yy'),'dd-mm-yy')
/
-- With database creation date
SELECT DECODE(ROWNUM,1,TO_CHAR(b.created,'dd-mm-yy'),
NULL) dbcreated,
a.file#,
a.name,
TO_CHAR(a.creation_time,'dd-mm-yyyy') filecreated
FROM v$datafile a,
v$database b
WHERE TO_DATE(TO_CHAR(a.creation_time,'dd-mm-yy'),'dd-mm-yy') > TO_DATE(TO_CHAR(b.created,'dd-mm-yy'),'dd-mm-yy')
ORDER BY TO_DATE(TO_CHAR(a.creation_time,'dd-mm-yy'),'dd-mm-yy')
/
----------------------------------------------
-- List users created after database creation:
----------------------------------------------
SELECT username,
TO_CHAR(created,'dd-mm-yy hh24:mi:ss') created
FROM dba_users
WHERE TO_DATE(TO_CHAR(created,'dd-mm-yy'),'dd-mm-yy') > (SELECT TO_DATE(TO_CHAR(created,'dd-mm-yy'),'dd-mm-yy')
FROM v$database);
---------------------------------------------
-- List users created with database creation:
---------------------------------------------
SELECT username
FROM dba_users
WHERE username NOT IN (SELECT username
FROM dba_users
WHERE TO_DATE(TO_CHAR(created,'dd-mm-yy'),'dd-mm-yy') > (SELECT TO_DATE(TO_CHAR(created,'dd-mm-yy'),'dd-mm-yy')
FROM v$database));
--------------------------------------------------------------------
-- In UNIX environment, fetch processed rows in long running query
--------------------------------------------------------------------
-- 1) Find spid / pid from OS
-- 2) Issue the below command
SELECT rows_processed
FROM v$sql
WHERE hash_value = (SELECT sql_hash_value
FROM v$session
WHERE paddr = (SELECT addr
FROM v$process
WHERE spid = '&spid'));
---------------------------------
-- How to Relink oracle binaries:
---------------------------------
1) login to oracle user and Find oracle homes
echo $ORACLE_HOME
2) cd $ORACLE_HOME/bin
3) Apply relink with option all
relink all
** Other parameters: all, oracle, network, client, client_sharedlib, interMedia,ctx, precomp, utilities, oemagent, ldap
-----------------------
-- Purge listener log :
------------------------
> fuser *
> lsnrctl set log_status off
> mv listener.log listener.log.$(date +"%d%m%Y")
> lsnrctl set log_status on
--------------------------
-- Locking and find locks:
--------------------------
col "Object" format a20
set numwidth 12
set lines 132
set pages 50
@title132 'Object Wait Statistics'
spool rep_out\&&db\obj_stat_xtab
SELECT *
FROM (SELECT DECODE(GROUPING(a.object_name),1,'All Objects',
a.object_name) AS "Object",
SUM(CASE
WHEN a.statistic_name = 'ITL Waits'
THEN a.VALUE
ELSE NULL
END) "ITL Waits",
SUM(CASE
WHEN a.statistic_name = 'buffer busy waits'
THEN a.VALUE
ELSE NULL
END) "Buffer Busy Waits",
SUM(CASE
WHEN a.statistic_name = 'row lock waits'
THEN a.VALUE
ELSE NULL
END) "Row Lock Waits",
SUM(CASE
WHEN a.statistic_name = 'physical reads'
THEN a.VALUE
ELSE NULL
END) "Physical Reads",
SUM(CASE
WHEN a.statistic_name = 'logical reads'
THEN a.VALUE
ELSE NULL
END) "Logical Reads"
FROM v$segment_statistics a
WHERE a.owner LIKE UPPER('&owner')
GROUP BY ROLLUP(a.object_name)) b
WHERE (b."ITL Waits" > 0
OR b."Buffer Busy Waits" > 0);
========================================
-- Command to print 2 consicutive lines:
========================================
select 'ALTER TABLE '||owner||'.'||table_name||' ENABLE ROW MOVEMENT;'||chr(10)||'ALTER TABLE '||owner||'.'||table_name||' SHRINK SPACE CASCADE;' from dba_tables where tablespace_name in ('CBFC_E_DATA','CBFC_E_INDEX') and owner='KINTANA';
================================================
Files to be backed up for OFFLINE/ONLINE backup:
================================================
Offline Backup:
---------------
Generate the list of DB files for the backup ( Datafiles, online redolog files,
controlfile trace backup, controlfiles, init parameter file, password file)
Shutdown the database
SELECT SUBSTR(host_name,0,10) host_name, instance_name, status,TO_CHAR(startup_time,'dd-mm-yy hh24:mi:ss') startup_time
FROM v$instance
/
SELECT name, dbid, open_mode, log_mode, force_logging
FROM v$database
/
SELECT name
FROM v$controlfile
/
SELECT name
FROM v$datafile
/
SELECT member
FROM v$logfile
/
ALTER database backup controlfile TO trace
/
pfile;
Online Backup:
--------------
Generate the filelist for backup ( Data files, controlfile, controlfile trace
backup, init parameter file, password file) Determine the range of offline
redologs necessary for a first consistency recovery for the backup by checking
the alert.log (Generate the archived redo log sequence range by identifying
“seq#” no. which was before the first “begin backup” statement and after the
last “end backup” statement in alert log file during that backup)
SELECT SUBSTR(host_name,0,10) host_name, instance_name, status,TO_CHAR(startup_time,'dd-mm-yy hh24:mi:ss') startup_time
FROM v$instance
/
SELECT name, dbid, open_mode, log_mode, force_logging
FROM v$database
/
SELECT name
FROM v$datafile
/
ALTER database backup controlfile TO trace
/
pfile;
show parameter archive log list;
----------------- TEST for Object movement--------------------
> Check tablespaces and space availability
> select distinct segment_type from dba_segments where owner=UPPER(TRIM('&owner'));
> select sum(bytes)/1024/1024 mb from dba_segments where owner=UPPER(TRIM('&owner')) and segment_type in ('TABLE','INDEX');
> Create new tablespace with the minimum size of above output
> select tablespace_name,segment_type,count(segment_name) cnt from dba_segments where owner=UPPER(TRIM('&owner')) group by tablespace_name,segment_type;
> select 'ALTER TABLE '||owner||'.'||segment_name||' MOVE TABLESPACE '||tablespace_name||';' from dba_segments where owner=UPPER(TRIM('&owner')) and segment_type ='TABLE';
> select 'ALTER INDEX '||owner||'.'||segment_name||' REBUILD ONLINE TABLESPACE '||tablespace_name||';' from dba_segments where owner=UPPER(TRIM('&owner')) and segment_type ='INDEX';
>
http://decipherinfosys.wordpress.com/2007/09/05/moving-tables-to-a-different-tablespace-in-oracle/ - Moving objects
+++++++++++++++++++++++++++++++++++++++++++++++
How to find ORACLE_HOME from SQL prompt-
+++++++++++++++++++++++++++++++++++++++++++++++++
select substr(file_spec,0,instr(file_spec,'/lib/libqsmashr.so' )) from DBA_LIBRARIES where library_name = 'DBMS_SUMADV_LIB' ;
+++++++++++++++++++++++++++++++++++++++++++++++++++
How to find Password file of database
++++++++++++++++++++++++++++++++++++++++++++++++++
SET pagesize 60 lines 1000 serveroutput ON feedback OFF;
EXECUTE dbms_output.ENABLE(1000000);
DECLARE
v_orahome varchar2(4000);
BEGIN
DBMS_SYSTEM.GET_ENV('ORACLE_HOME', v_orahome);
DBMS_OUTPUT.PUT_LINE (CHR(10)||'(5). Password file:'
||CHR(10)||'-------------------'||CHR(10)||v_orahome||'/dbs/orapw'||lower(trim(ora_database_name)));
DBMS_OUTPUT.PUT_LINE (CHR(10));
END;
/
----------------------------
- How to use SLEEP function:
----------------------------
begin
for i in 1 ..10 loop
dbms_backup_restore.sleep(30);
dbms_output.put_line('Current time: '||to_char(sysdate,'mi:ss'));
end loop;
end;
/
declare
v_file utl_file.file_type;
begin
for i in 1 ..10 loop
dbms_backup_restore.sleep(1);
v_file := utl_file.fopen (location => 'EXPDP_DIR',
filename => lower(trim(ora_database_name))|| to_char(sysdate, 'ddmmyyhhmiss')|| '.log',
open_mode => 'a',
max_linesize => 32767
);
utl_file.put_line (v_file, 'Current time: '||to_char(sysdate,'mi:ss'));
end loop;
utl_file.fclose (v_file);
exception
when others then
utl_file.fclose (v_file);
end;
/
Ref:
----
http://www.jlcomp.demon.co.uk/faq/sleep.html
http://www.chrispoole.co.uk/tips/plsqltip1.htm
------------------------
- For purging snapshots:
------------------------
-- Check snapcount in month wise
--
col snapdate format a8
select to_char(snap_time,'Mon-yyyy') snapdate,
count(snap_id) snapcnt
from perfstat.stats$snapshot
group by to_char(snap_time,'Mon-yyyy')
order by to_date(to_char(snap_time,'Mon-yyyy'),'Mon-yyyy')
/
-- Check begin snap and end snap from first n snaps
--
select snap_id
from (
select s.snap_id snap_id, rank() over (ORDER BY s.snap_id) snaprank
from perfstat.stats$snapshot s
, perfstat.stats$database_instance di
, v$database d
, v$instance i
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.startup_time = s.startup_time
order by snap_id
) where snaprank in (1,&no);
-- Check the snaps
select s.snap_id
, to_char(s.snap_time,'dd-mm-yy hh24:mi:ss') snap_date
from perfstat.stats$snapshot s
, perfstat.stats$database_instance di
, v$database d
, v$instance i
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.startup_time = s.startup_time
order by snap_id
/
-- Check Snap cout in month wise
--
break on report
compute sum of snap_cnt on report
select to_char(s.snap_time,'Mon-yyyy') snap_month
, min(s.snap_id) min_snapid
, max(s.snap_id) max_snapid
, count(s.snap_id) snap_cnt
from perfstat.stats$snapshot s
, perfstat.stats$database_instance di
, v$database d
, v$instance i
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.startup_time = s.startup_time
group by to_char(s.snap_time,'Mon-yyyy')
order by to_date(to_char(s.snap_time,'Mon-yyyy'),'Mon-yyyy')
/
-- Check days cout for retention
--
select to_char(min(s.snap_time),'dd-Mon-yyyy') begin_snap_date
, to_char(max(s.snap_time),'dd-Mon-yyyy') end_snap_date
, max(s.snap_time)-min(s.snap_time) days_retention
from perfstat.stats$snapshot s
, perfstat.stats$database_instance di
, v$database d
, v$instance i
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.startup_time = s.startup_time
/
@?/rdbms/admin/sppurge;
-- Check Tablespace usage
--
SELECT b.tablespace_name ,
NVL(ROUND(SUM(a.bytes) /1024/1024,2),0) mb ,
NVL(ROUND(SUM(a.maxbytes)/1024/1024,2),0) maxmb,
NVL(ROUND(b.used_percent,2),0) used_percent
FROM dba_data_files a,
dba_tablespace_usage_metrics b
WHERE a.tablespace_name=b.tablespace_name
AND b.tablespace_name in ((SELECT UPPER(value) FROM v$parameter WHERE name ='undo_tablespace'),
(SELECT default_tablespace FROM dba_users WHERE username='PERFSTAT'))
GROUP BY b.tablespace_name,
b.used_percent
ORDER BY used_percent DESC
/
select object_name,object_type from dba_objects where owner ='PERFSTAT' and status <> 'VALID';
UTL_RECOMP
EXECUTE UTL_RECOMP.RECOMP_SERIAL('SCOTT');
exec dbms_utility.compile_schema(schema=>'PERFSTAT',compile_all=>false);
Ref:
-----
http://www.oracle.com/technology/oramag/oracle/06-may/o36plsql.html
http://www.oracle-database-tips.com/compile_oracle_schema.html
--------------------------------------------------------
-- SQL to generate script for compiling invalid objects:
--------------------------------------------------------
set heading off linesize 100
spool compileinvalidobjs.sql
select decode (object_type, 'SYNONYM','alter public '
||lower(object_type)
||' '
||lower(object_name)
||' compile;' , 'PACKAGE BODY','alter package '
||' '
||lower(owner)
||'.'
||lower(object_name)
||' compile body;' , 'alter '
||lower(object_type)
||' '
||lower(owner)
||'.'
||lower(object_name)
||' compile;') cmd
from dba_objects
where status='INVALID'
/
spool off
---------------------
Session memory usage-
---------------------
1. To understand where memory is wasted, please check the status column in
v$session view. Are there INACTIVE views? If yes, to automate cleanup of
INACTIVE sessions you can create a profile with an appropriate IDLE_TIME setting
and assign that profile to the users. - Note:159978.1: How To Automate
Disconnection of Idle Sessions, outlines the steps to setup IDLE_TIME for this.
2. If there no inactive sessions, please contact the application provider to
find if connection pooling is used and to check why connections are not closed
gracefully.
3. Regarding the memory consumption - pllease check if there is a certain
session for which the memory consumption increases consistently. This is because
you mentioned that from 12 sessions consuming 515M, it has increased to 42
session with 1.5G which is basically a proportional increase.
You can use:
SQL> col name format a30
SQL> select sid,name,value
from
v$statname n,v$sesstat s
where n.STATISTIC# = s.STATISTIC#
and name like 'session%memory%'
order by 3 asc;
Please refer to :
- Note.233869.1 Ext/Pub Diagnosing and Resolving ORA-4030 errors Some operations
will require lots of process memory like huge PL/SQL tables or big sort
operations. In these cases, the processes will run for a certain period of time
before getting the ora-4030 error, hopefully allowing us to find out where and
why the memory is allocated. You can use the following query to find out
oracle's idea of PGA and UGA size for the oracle processes.
- Note.822527.1 Int/Pub How To Find Where The Memory Is Growing For A Process
----------------------------
-- Checking after migration:
----------------------------
cat > chkdbversion.sql
set linesize 100
select name "Database" from v$database;
select banner "Database version" from v$version;
prompt Listing registry components:
select comp_id, version, status from dba_registry;
prompt Listing database connection status using TNSPING:
prompt
host tnsping $ORACLE_SID
cat > chkdbversion.sh
#!/usr/bin/ksh
for i in `ps -ef|grep pmon | grep ora | grep -v grep | awk '{print $NF}' | cut -c10-40`
do
USER=`ps -ef|grep $i | grep pmon | awk '{print $1}'`
DB=$i
echo $DB $USER
su - $USER -c "export ORACLE_SID=$DB;sqlplus '/ as sysdba' @/
/chkdbversion.sql" < /dev/null
done
echo ' '
echo ' '
ps -ef|egrep -i "inherit|pmon"
echo ' '
