select file_id,file_name,autoextensible,ceil(maxbytes/(1024*1024))
maxsize_mb, ceil(bytes/(1024*1024)) cursize_mb,status from dba_data_files where
tablespace_name=’&TABLESPACE_NAME’;
Script_Retrieving contents
from sysaux:
set lines 300 pages 300
col OWNER for a10
col OBJECT_NAME for a25
col ORIGINAL_NAME for a25
col TYPE for a10
col TS_NAME for a8
col PARTITION_NAME for a25
select
OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE,TS_NAME,CREATETIME,DROPTIME,CAN_PURGE
from DBA_RECYCLEBIN where TS_NAME='SYSAUX' order by CREATETIME,DROPTIME
SQL> select
OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE,TS_NAME,CREATETIME,DROPTIME,CAN_PURGE
from DBA_RECYCLEBIN where TS_NAME='SYSAUX' order by CREATETIME,DROPTIME;
no rows selected
TEMP
tablespace usage:
SQL> select
tablespace_name,current_users,round (USED_BLOCKS*8/1024/1024,2) "GB in
Use",
round(FREE_BLOCKS*8/1024/1024,2)
"Free GB",round(MAX_SIZE/1024,2) "Msize GB",
round(MAX_USED_BLOCKS*8/1024/1024,2)
"Max used GB"
from v$sort_segment;
SQL> SELECT s.sid,
s.username,s.status, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value,
u.segtype, u.contents, u.blocks
FROM v$session s,
v$tempseg_usage u
WHERE
s.saddr=u.session_addr
order by u.blocks;
select
sid,serial#,USERNAME,STATUS,PROGRAM,SQL_ADDRESS,SQL_HASH_VALUE,SQL_ID,CLIENT_INFO,SECONDS_IN_WAIT,STATE
from v$session where sid=&sid;
Query
to find the ongoing transactions :
select start_time, username,
r.name,
ubafil, ubablk, t.status,
(used_ublk*p.value)/1024 blk, used_urec
from v$transaction t, v$rollname r,
v$session s, v$parameter p
where xidusn=usn
and s.saddr=t.ses_addr
and p.name='db_block_size'
order by 1;
Undo Tablespace Usage by each user:
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';
select start_time, username,
r.name,
ubafil, ubablk, t.status,
(used_ublk*p.value)/1024 blk, used_urec
from v$transaction t, v$rollname r,
v$session s, v$parameter p
where xidusn=usn
and s.saddr=t.ses_addr
and p.name='db_block_size'
order by 1;
Script
Tablespace Fragmentation:
Reorganization ensures
that all tables and indexes do not have row fragmentation, and that they reside
in a single extent, with all free space in a tablespace in a single, contiguous
chunk. How a tablespace may become fragmented. At initial load time, all Oracle
tables within the tablespace are contiguous--that is, only one chunk of free
space resides at the end of the tablespace. As tables extend and new
extents are added to the tablespace, the free space becomes smaller but it
still remains contiguous.
Basically, a table can
fragment in two ways:
* A table
extends (without row chaining)--Contrary to popular belief, this is not a
problem and performance will not suffer.
* Rows
fragment within the tablespace (due to SQL UPDATES)--This causes a serious
performance problem, and the offending tables must be exported, dropped, and
re-imported.
set linesize 150
column tablespace_name format a20 heading 'Tablespace'
column sumb format 999,999,999
column
extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999,999 Heading 'Total| Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(MB)'
column Pct_Free format 999.99 heading '% Free'
column Chunks_Free format 99999999 heading 'No Of Ext.'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
set
echo off
PROMPT FREE SPACE AVAILABLE IN TABLESPACES
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1048576) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from
dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by pct_free;
Script_Finding
SPID:
set line 200
col PROGRAM for a45
col username for a12
col machine for a50
select
sid,s.serial#,spid,s.username,s.machine,s.program,status from v$process
p,v$session s where addr=paddr and sid in(880);
Find
Report User SID, PID, and User Name:
Script to Report User
SID, PID, and User Name
This script provides a
list of SID's, System PID's and usernames of current users.
Sample Output
Copy Script to Clipboard
REM LOCATION:
System Monitoring\Reports
REM FUNCTION:
Provide list of SID, System PID and usernames of
REM
current users
REM TESTED ON:
7.3.3.5, 8.0.4.1, 8.1.5, 8.1.7, 9.0.1, 10.2.0.3, 11.1.0.6
REM PLATFORM:
non-specific
REM REQUIRES:
v$session a, v$process b
REM
REM This is a part
of the Knowledge Xpert for Oracle Administration library.
REM Copyright (C)
2008 Quest Software
REM All rights
reserved.
REM
REM********************
Knowledge Xpert for Oracle Administration ********************
COLUMN sid
format 9999 heading "SID"
COLUMN spid
format 9999 heading
"PID"
COLUMN username
format a30 heading "User
Name"
COLUMN osuser
format a30 heading "OS
User"
SET FEEDBACK OFF VERIFY
OFF
SET lines 132 pages 59
TTITLE left _date center
'Oracle System Users' skip 2
SELECT a.sid, b.spid,
a.username, a.osuser
FROM v$session a,
v$process b
WHERE a.paddr =
b.addr(+)
/
TTITLE off
CLEAR columns
SID PID
User Name
OS
User
-----
------------------------ ------------------------------
------------------------------
262 29728
RRSPICER
rrspicer
Script_Find
SQL being executed by a OS Process ID (PID)_SPID:
Script – Find SQL being executed by
a OS Process ID (PID)
prompt "Please Enter The UNIX
Process ID"
set pagesize 50000
set linesize 30000
set long 500000
set head off
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and
s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;
Script_Locks
on a table:
column oracle_username format a18
column os_user_name format a15
column object_name format a28
column object_type format a25
select
a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT
OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID,
ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner,
object_name,object_type from dba_objects) b
where a.object_id=b.object_id;
Script_long_running_query:
SELECT SID,
ROUND(TIME_REMAINING
/60,2)"time remaining(min)",
ROUND(ELAPSED_SECONDS/60,2)"elapsed_mins",
SOFAR,
TOTALWORK-SOFAR "Work
Remaining" ,
SQL_PLAN_OPTIONS||'
'||SQL_PLAN_OPERATION "PLAN OPERATION",
MESSAGE
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING>0;
Script_PGA
Usage Script:
set head off
set head on
column name format a60
column value/1024/1024 as MB format
9,999,999,999,999,999
select * from gv$pgastat order by
inst_id,name;
COLD
backup using RMAN Script:
run{
sql 'alter system checkpoint';
shutdown immediate;
startup mount;
sql “create pfile=”/u01/app/oracle/backup/pfile`date
+%d%m%Y`.ora” from spfile”;
RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE
DISK FORMAT ‘/u01/app/oracle/backup/%U’;
ALLOCATE CHANNEL disk2 DEVICE TYPE
DISK FORMAT ‘/u01/app/oracle/backup/%U’;
ALLOCATE CHANNEL disk3 DEVICE TYPE
DISK FORMAT ‘/u01/app/oracle/backup/%U’;
BACKUP AS COMPRESSED BACKUPSET
DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT
‘/u01/app/oracle/backup/cntrl_%s_%p_%t’;
RELEASE CHANNEL disk1;
RELEASE CHANNEL disk2;
RELEASE CHANNEL disk3;
}
RMAN
Backup Failure:
select
operation,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,to_char(END_TIME,'mm/dd/yy
hh24:mi') end_time,status,object_type from v$rman_status order by 2;
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy
hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')
end_time,
elapsed_seconds/3600
hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
Archive
logs generated per every one hour:
SELECT TO_CHAR (COMPLETION_TIME,
'DD/MM/YYYY') DAY,
SUM
(DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
"00-01",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
"01-02",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
"02-03",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
"03-04",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
"04-05",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
"05-06",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
"06-07",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
"07-08",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
"08-09",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
"09-10",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
"10-11",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
"11-12",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
"12-13",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
"13-14",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
"14-15",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
"15-16",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
"16-17",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
"17-18",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
"18-19",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
"19-20",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
"20-21",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
"21-22",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
"22-23",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
"23-00",
COUNT (*) TOTAL
FROM V$ARCHIVED_LOG
WHERE ARCHIVED='YES'
GROUP BY TO_CHAR (COMPLETION_TIME,
'DD/MM/YYYY')
ORDER BY TO_DATE (DAY,
'DD/MM/YYYY');
DAY
00-01
01-02 02-03 03-04
04-05 05-06 06-07
07-08 08-09
---------- ---------- ----------
---------- ---------- ---------- ---------- ---------- ---------- ----------
09-10
10-11 11-12 12-13
13-14 14-15 15-16
16-17 17-18 18-19
19-20
---------- ---------- ----------
---------- ---------- ---------- ---------- ---------- ---------- ----------
----------
20-21
21-22 22-23 23-00
TOTAL
---------- ---------- ----------
---------- ----------
05/05/2015
16 22
12 31
109 95 117
107 103
78
80 36
6 4
35 71 38
10
970
Number
of log switches per day and Size of Archive logs:
set pagesize 10000 linesize 10000
set feedback 0
column date format a18
select to_char(first_time,
'YYYY-MM-DD Dy') as "date",
round( sum((blocks + 1) *
block_size)
/ 1024 / 1024 / 1024) as "size_gb",
count (*) as
"number_of_switches_per_day"
from v$archived_log
where dest_id = 1
group by
to_char(first_time,'YYYY-MM-DD Dy')
order by
to_char(first_time,'YYYY-MM-DD Dy');
date
size_gb number_of_switches_per_day
------------------ ----------
--------------------------
2015-07-12 Sun
2
23
select trunc(COMPLETION_TIME) TIME,
SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc
(COMPLETION_TIME) order by 1;
TIME SIZE_MB
---------- ----------
2011-02-12 71797.87
2011-02-13 75880.52
2011-02-14 73569.37
2011-02-15 76776.81
Number of log switches per week and total Size of Archive logs:
select round(sum((blocks + 1)
* block_size)
/ 1024 / 1024 / 1024) as
"weekly_size_gb"
count (*) as
"number_of_switches_per_week"
from v$archived_log
where dest_id = 1
and first_time >= sysdate - 7;
weekly_size_gb
number_of_switches_per_week
--------------
---------------------------
6
88
Calculating
Estimated Restoration completion time:
Estimated time of Backup / Restore
via RMAN
Query to estimate the time of Backup
/ Restore via RMAN
Below, a query to be executed on
target database or database auxiliary ( in the case of duplicate) to estimate
the time of restore and the completed percentage .
col OPNAME for a30
select OPNAME,SOFAR/TOTALWORK*100
PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES,
trunc(ELAPSED_SECONDS/60)
MIN_ATEAGORA
from v$session_longops where
TOTALWORK>0 and OPNAME like '%RMAN%';
Output example of a duplicate:
OPNAME PCT MIN_RESTANTES
MIN_ATEAGORA
------------------------------
---------- ------------- ------------
RMAN: aggregate input 67.6037414 176
368
RMAN: full datafile restore 100 0 26
RMAN: full datafile restore 100 0 10
RMAN: full datafile restore 100 0 21
RMAN: full datafile restore 100 0 22
RMAN: full datafile restore 100 0 20
RMAN: full datafile restore 100 0 51
RMAN: full datafile restore
49.1620893 26 25
RMAN: full datafile restore 100 0 32
RMAN: full datafile restore 100 0 26
RMAN: full datafile restore 100 0 20
RMAN: full datafile restore 100 0 27
RMAN: full datafile restore 100 0 26
RMAN: full datafile restore 100 0 19
RMAN: full datafile restore 100 0 52
RMAN: full datafile restore
49.4441805 25 24
Reading the Output:
The line ( RMAN: aggregate input )
is the sum of total and it is showing that was executed 67,60% of total of
restore, and took until now, 368 minutes, and estimates that rest 176 minutes.
The other lines are of partials (
RMAN: full datafile restore ), we have many completed ( pct=100) , and two
restore of pieces in execution, that will still take 26 minutes, the first, and
25 minutes the second.
Script_Retriving
Information from Listener.log:
timestamp*connect info* protocol
tcp/ip , spx, port * sid * return value
grep "08-FEB"
listener.log | awk '{ if ( $NF != 0 ) print $0 }'
https://martincarstenbach.wordpress.com/2010/02/09/check-for-non-successful-connection-attemps-in-listener-log/
grep "05-SEP" listener.log
| awk '{ if ( $NF != 0) print $0}'
Oracle
Management Service (OMS) Commands:
emctl list oms // The command will
prompt for the SYSMAN user's password and list the Server where OMS is running.
emctl stop oms -all
The above command will stop all the
OMS services Web Tier, Oracle Management Server and Admin Server.
emctl stop oms
-all
Oracle Enterprise Manager Cloud
Control 12c Release 5
Copyright (c) 1996, 2015 Oracle
Corporation. All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Already
Stopped
AdminServer Successfully Stopped
Oracle Management Server is Dow
emctl start oms
emctl start oms
Oracle Enterprise Manager Cloud
Control 12c Release 5
Copyright (c) 1996, 2015 Oracle
Corporation. All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier
Successfully Started
Oracle Management
Server Successfully Started
Oracle Management
Server is Up
emctl status oms
~]$ emctl status oms
Oracle Enterprise Manager Cloud
Control 12c Release 5
Copyright (c) 1996, 2015 Oracle
Corporation. All rights reserved.
WebTier is Up
Oracle Management Server is Up
Scheduler
Job Queries:
job_schedules.sql:
SET LINESIZE 250
COLUMN owner FORMAT A20
COLUMN schedule_name FORMAT A30
COLUMN start_date FORMAT A35
COLUMN repeat_interval FORMAT A50
COLUMN end_date FORMAT A35
COLUMN comments FORMAT A40
SELECT owner,
schedule_name,
start_date,
repeat_interval,
end_date,
comments
FROM dba_scheduler_schedules
ORDER BY owner, schedule_name;
job_classes:
SET LINESIZE 200
COLUMN service FORMAT A20
COLUMN comments FORMAT A40
SELECT job_class_name,
resource_consumer_group,
service,
logging_level,
log_history,
comments
FROM dba_scheduler_job_classes
ORDER BY job_class_name;
job_programs:
SET LINESIZE 250
COLUMN owner FORMAT A20
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A50
COLUMN comments FORMAT A40
SELECT owner,
program_name,
program_type,
program_action,
number_of_arguments,
enabled,
comments
FROM dba_scheduler_programs
ORDER BY owner, program_name;
jobs_running:
SET LINESIZE 200
COLUMN owner FORMAT A20
SELECT owner,
job_name,
running_instance,
elapsed_time
FROM dba_scheduler_running_jobs
ORDER BY owner, job_name;
Disabling
Scheduler Jobs:
exec
dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
PL/SQL procedure successfully completed.
exec
dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');
PL/SQL procedure successfully
completed.
select name from v$database;
NAME
---------
<DB_NAME>
select JOB_NAME,STate from
ALL_SCHEDULER_JOBS where owner='ORACLE_OCM';
JOB_NAME
STATE
------------------------------
---------------
MGMT_CONFIG_JOB
DISABLED
MGMT_STATS_CONFIG_JOB
DISABLED
Partitioning
Commands:
set lines 300
col OWNER for a10
col TABLE_NAME for a10
select
OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,PCT_FREE,NUM_ROWS,PARTITIONED from
dba_tables where TABLE_NAME='&TABLE_NAME';
set lines 300
col PARTITION_NAME for a16
col TABLESPACE_NAME for a10
col HIGH_VALUE for a50
select table_name
,PARTITION_NAME,PARTITION_POSITION,TABLESPACE_NAME,COMPOSITE,HIGH_VALUE,PCT_FREE,LAST_ANALYZED
from dba_tab_partitions where TABLE_NAME='&TABLE_NAME';
select TABLE_NAME,PARTITION_COUNT,PARTITIONING_TYPE,PARTITIONING_KEY_COUNT,STATUS
from dba_part_tables where TABLE_NAME='&TABLE_NAME';
select view_name from dba_views
where view_name like '%PARTITION%';
Script_to
find dumpfile version:
