Tablespace Utilization Script for Tablespace Space Used % more than 80 %
Oracle Tablespace Utilization Script (including AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)
1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.
1. Check the database details.
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;
2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
3. Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 50000 lines 32767
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;
Note:- If required, can get the DDL of a tablespace as below.
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
How to generate Multiple AWR reports
Multiple AWR report generation script
The following script can be used for AWR reports generation, for specific intervals between the required Snapshots generated.
The below script (awr_report_generate.sh) is to generate AWR reports for the specific intervals between the required Snapshots generated.
Let say, there is a requirement to generate awr reports for every 2 hours of previous day from 00:00 hrs to current date 00:00 hrs ie., 24 hrs.
Reuirements:-
===========
First we need to get the Begin Snap ID and End Snap ID, in order to generate multiple awr reports using the below script.
$ sqlplus "/as sysdba"
SQL> @?/rdbms/admin/awrrpt.sql (For RAC, SQL> @?/rdbms/admin/awrrpti.sql)
.
.
.
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: ---------------> press 'Enter' Key
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for num_days:
Listing the last n days of Completed Snapshots ---------------> here 'n' represents the number of days we have entered for generating awr reports.
NOTE:-
====
Here, for the required period, make a note of the required Begin Snap ID and End Snap ID.
Create a script awr_report_generate.sh:-
======================================
SQL> exit
$ vi awr_report_generate.sh
#!/usr/bin/sh
echo "Enter the value for Begin Snapshot Id :"
read beginid
echo "Enter the value for End Snapshot Id:"
read endid
echo "Enter the value for interval between Snapshot Id's. To generate reports between consecutive Snapshop Id's, Enter '1'. Else, enter desired
value:"
read snapint
echo "Enter the value for report type: html/text"
read repfmt
echo "Enter the path for unix directory to generate the reports. Press 'Enter' to generate the reports in current working directory:"
read repdir
if [ "$repdir" = "" ]
then
repdir=$PWD
fi
while [ $beginid -lt $endid ]
do
tempid=`echo $beginid + $snapint |bc`
sqlplus -s '/as sysdba'<<EOF
set verify off
set feedback off
set pages 0
set serveroutput on
clear break compute;
repfooter off;
ttitle off;
btitle off;
set heading on;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 0 linesize 1500 newpage 1 recsep off;
set trimspool on trimout on define "&" concat "." serveroutput on;
set underline on;
col endid new_value endid;
col repname new_value repname;
col dbid new_value dbid;
col inst_num new_value inst_num;
define beginid=$beginid;
define tempid=$tempid;
variable repname varchar2(60);
variable dbid varchar2(10);
variable inst_num varchar2(2);
select dbid dbid from v\$database;
select instance_number inst_num from v\$instance ;
select '$repdir/AWR_'||(select instance_name inst_name from v\$instance)||'_'||(select to_char(END_INTERVAL_TIME,'DDMONYY_HH24MI')from
dba_hist_snapshot where snap_id='$beginid' and instance_number=(select instance_number from v\$instance))||'_'||(select to_char
(END_INTERVAL_TIME,'DDMONYY_HH24MI')from dba_hist_snapshot where snap_id='$tempid' and instance_number=(select instance_number from v\
$instance))||'.$repfmt' repname from dual;
spool &repname
select output from table(dbms_workload_repository.awr_report_$repfmt(&dbid,&inst_num,&&beginid,&&tempid,0));
spool off
exit
EOF
beginid=`echo $beginid + $snapint |bc`
done
-- Press esc
:wq
$ ls -lrt awr_report_generate.sh
Run the script:-
==============
$ sh awr_report_generate.sh
$ ls -lrt
The following query can be used to check the current settings for the AWR interval and AWR retention.
The query returns the current AWR interval values in minutes.
set pages 50000 lines 32767
col snap_interval format a20
col retention format a20
col topnsql format a20
select extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;
Snapshot Interval Retention Interval
----------------- ------------------
Labels: PERFORMANCE TUNING, SCRIPT
Database health checks in Oracle
Performing Database health checks, when there is an issue reported by Application users.
1. Check the Database details
2. Monitor the consumption of resources
3. Check the Alert Log
4. Check Listener log
5. Check Filesystem space Usage
6. Generate AWR Report
7. Generate ADDM Report
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database
9. Check for alerts in OEM
1. Check the Database details :-
=============================
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v
$database,gv$instance;
For RAC:
-------
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS')
"DB UP TIME" from v$database,gv$instance;
2. Monitor the consumption of resources :-
=======================================
select * from v$resource_limit where resource_name in ('processes','sessions');
The v$session views shows current sessions (which change rapidly),
while the v$resource_limit shows the current and maximum global resource utilization for some system resources.
3. Check the Alert Log :-
======================
$locate alert_<ORACLE_SID>
--- OR ---
UNIX/Linux command to locate the alert log file
-----------------------------------------------
$ find / -name 'alert_*.log' 2> /dev/null
vi <alert_log_location_of_the_above_output>
shift+g
?ORA- ---> press enter key
press 'n' to check backwards/up side and 'N' for forward/down side search.
:q! --and press enter, for exiting vi editor
--- OR ---
11G
===
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col NAME for a15
col VALUE for a60
select name, value from v$diag_info where name = 'Diag Trace';
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/diag/$ORACLE_SID/trace directory
Before 11G
==========
$ sqlplus "/as sysdba"
set pages 9999 lines 300
show parameter BACKGROUND_DUMP_DEST;
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/admin/$ORACLE_SID/bdump directory
4. Check Listener log :-
=====================
$locate listener.log
--- OR ---
UNIX/Linux command to locate the listener log file
--------------------------------------------------
$ find / -name 'listener.log' 2> /dev/null
vi <listener.log>
shift+g
?TNS- ---> press enter key
press 'n' to check backwords and 'N' for forword search.
AND
shift+g
?error ---> press enter key
press 'n' to check backwords and 'N' for forword search.
:q! --and press enter, for exiting vi editor
--- OR ---
$lsnrctl status
from the output you can get the listener log location (see the value for "Listener Log File" in the output).
5. Check Filesystem space Usage :-
===============================
df -h (Linux / UNIX)
df -g (AIX)
6. Generate AWR Report :-
======================
Generate AWR report for current and before to compare
SQL> @?/rdbms/admin/awrrpt.sql (For RAC, @?/rdbms/admin/awrrpti.sql - for each instance)
If Required,
SQL> @?/rdbms/admin/awrddrpt.sql ----> Produces Workload Repository Compare Periods Report
7. Generate ADDM Report :-
=======================
Generate ADDM report for current and before to compare.
ADDM report provides Findings and Recommendations to fix the issue.
SQL> @?/rdbms/admin/addmrpt.sql (For RAC, @?/rdbms/admin/addmrpti.sql - for each instance)
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database :-
========================================================================
Select * from v$lock;
Select * from gv_$lock; (For RAC)
A fast way to check blocking/waiting situations
-----------------------------------------------
SELECT * FROM v$lock WHERE block > 0 OR request > 0;
set pages 50000 lines 32767
select object_name,s.inst_id,s.sid,s.serial#,p.spid,s.osuser,s.program,s.server,s.machine,s.status from gv$locked_object l,gv$session s,gv$process
p,dba_objects o where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;
set pages 50000 lines 32767
col OBJECT_NAME for a40
col USERNAME for a10
col LOCKED_MODE for a15
col OBJECT_OWNER for a15
col OS_USER_NAME for a12
SELECT b.inst_id,b.session_id AS sid,NVL(b.oracle_username, '(oracle)') AS username,a.owner AS object_owner,a.object_name,
Decode(b.locked_mode, 0, 'None',1, 'Null (NULL)',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share (S)',5, 'S/Row-X (SSX)',6, 'Exclusive (X)',
b.locked_mode) locked_mode,b.os_user_name FROM dba_objects a, gv$locked_object b WHERE a.object_id = b.object_id ORDER BY 1, 2, 3, 4;
Blocker Session and Waiting sessions
====================================
column Username format A15 column Sid format 9990 heading SID
column Type format A4 column Lmode format 990 heading 'HELD'
column Request format 990 heading 'REQ' column Id1 format 9999990
column Id2 format 9999990 break on Id1 skip 1 dup
SELECT SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1, M.Id2
FROM V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;
USERNAME SID TY LMODE REQUEST ID1 ID2
---------------- ------- -- ------------- ------------- ---------- --------
ORAPLAYERS 10 TX Exclusive None 123456 200
ORAPLAYERS 100 TX None Exclusive 123456 200
Session 10 is blocking(LMODE=Exclusive)
Session 100 is waiting(REQUEST=Exclusive)
The meaning of ID1 and ID2 depends on the lock TYPE.
• We can see situations where a session is both a Blocker and a Waiter.
• If there are only two sessions and both are Blockers and Waiters then we got a deadlock situation (which Oracle will solve automatically).
To find waiters:
---------------
set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- --------- --------- -------------- -------- --------
Blocking details:
----------------
set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' ||
s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;
set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20
select (select username from v$session where sid = a.sid ) blocker,a.sid, 'is blocking ',(select username from v$session where sid =b.sid)
blockee,b.sid from v$lock a, v$lock b where a.block =1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------- ---------- ---------- ------- --------
set pages 50000 lines 32767
select blocking_session, sid, serial#, wait_class,seconds_in_wait, username, osuser, program, logon_time from v$session where blocking_session is not
NULL order by 1;
9. Check for alerts in OEM :-
============================
Login to Oracle Enterprise Manager with valid username and password
click on "Alerts" tab
then select the below tabs one by one to see the alerts generated
Targets Down/Critical/Warning/Errors/
Labels: DATABASE STATUS, PERFORMANCE TUNING, SCRIPT
How to check RMAN backup job status in Oracle using v$rman_backup_job_details
RMAN backup job details for 'n' number of days:-
=========================================
Monitoring RMAN backup status using v$rman_backup_job_details and v$rman_status.
Note : - Enter the number of days required for status report, for 1 day backup status report provide input as '1'.
RMAN backup status using v$rman_backup_job_details :-
set pages 9999 lines 500
col INSTANCE for a9
col ELAPSED for a30
SELECT ( SELECT instance_name FROM v$instance)
|| ' '
|| ( SELECT instance_number FROM v$instance)
instance,
-- TO_CHAR (start_time, 'YYYY-MM-DD HH24:MI') start_time,
to_date (start_time, 'DD-MM-YYYY HH24:MI:SS') start_time,
TO_CHAR (output_bytes / 1048576, '999,999,999.9') output_mb,
TO_CHAR (output_bytes_per_sec / 1048576, '999,999.9') output_mb_per_sec,
time_taken_display elapsed,input_type,status
FROM v$rman_backup_job_details
where start_time >= sysdate - &NUMBER_OF_DAYS
ORDER BY start_time
/
RMAN backup status using v$rman_backup_job_details , v$rman_status:-
set pages 9999 lines 500
set numformat 99999.99
set trim on
set trims on
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
col INSTANCE for a9
col status for a22
col COMMAND_ID for a20
col INPUT_TYPE for a10
col OUTPUT_DEVICE_TYPE for a10
col OUTPUT_BYTES_PER_SEC_DISPLAY for a9
col status heading "BACKUP|STATUS"
col COMMAND_ID heading "BACKUP NAME"
col STARTED_TIME heading "START TIME"
COL END_TIME heading "END TIME"
col ELAPSED_TIME heading "MINUTES | TAKEN"
col INPUT_TYPE heading "INPUT|TYPE"
col OUTPUT_DEVICE_TYPE heading "OUTPUT|DEVICES"
col INPUT_SIZE heading "INPUT SIZE|GB"
col OUTPUT_SIZE heading "OUTPUT SIZE|GB"
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "OUTPUT | RATE|(PER SEC)"
SELECT (SELECT instance_name FROM v$instance) || ' ' || (SELECT instance_number FROM v$instance) instance,rs.sid,
rj.COMMAND_ID,
rj.STATUS,
max(rj.START_TIME) STARTED_TIME,
rj.END_TIME,
rj.ELAPSED_SECONDS/60 ELAPSED_TIME,
rj.INPUT_TYPE,
rj.OUTPUT_DEVICE_TYPE,
rj.INPUT_BYTES/1024/1024/1024 INPUT_SIZE,
rj.OUTPUT_BYTES/1024/1024/1024 OUTPUT_SIZE,
rj.OUTPUT_BYTES_PER_SEC_DISPLAY
from v$rman_backup_job_details rj, v$rman_status rs
where rj.COMMAND_ID=rs.COMMAND_ID
group by
rs.sid,rj.COMMAND_ID,rj.STATUS,rj.START_TIME,rj.END_TIME,rj.ELAPSED_SECONDS,rj.INPUT_TYPE,rj.OUTPUT_DEVICE_TYPE,rj.INPUT_BYTES,rj.OUTPUT_BYTES,rj.OUT
PUT_BYTES_PER_SEC_DISPLAY
having max(rj.START_TIME) > sysdate-&NUMBER_OF_DAYS order by rj.START_TIME desc
/
BACKUP MINUTES INPUT OUTPUT INPUT SIZE OUTPUT
SIZE OUTPUT RATE
INSTANCE SID BACKUP NAME STATUS START TIME END TIME TAKEN TYPE DEVICES GB
GB (PER SEC)
--------- --- -------------------- ---------------------- ------------------- ------------------- --------- ---------- ---------- ----------
----------- ------------
To get the job details for a specific backup job, use the following query:-
set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;
Where,
CF: Number of controlfile backups included in the backup set
DF: Number of datafile full backups included in the backup set
I0: Number of datafile incremental level-0 backups included in the backup set
I1: Number of datafile incremental level-1 backups included in the backup set
L: Number of archived log backups included in the backup set
Backup set details : -
To get the Backup set details for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, use the following query:
set lines 220
set pages 1000
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
from V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by d.start_time;
Backup job output :-
To get the Backup job output for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, use the following query:
set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by recid;
t
Labels: BACKUP, RMAN, SCRIPT
September 30, 2013
Script to Gather data from the Linux OS
#####################################################################
### Unix script os_stats.sh ###
### Designed to be run periodically to collate information ###
### START OF SCRIPT ###
#####################################################################
#
LOG_FILE="OS_`hostname`_`date '+%m%d%y_%H%M'`.txt"
#
echo "**********************************************" >$LOG_FILE
date >> $LOG_FILE
echo "Running as `id`" >> $LOG_FILE
echo "**********************************************" >>$LOG_FILE
echo "uname -a" >>$LOG_FILE
uname -a >>$LOG_FILE
cat /etc/issue >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "ulimit -a" >>$LOG_FILE
ulimit -a >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "ulimit -Ha" >>$LOG_FILE
ulimit -Ha >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "Netstat -i" >>$LOG_FILE
netstat -i >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "netstat -an">> $LOG_FILE
netstat -an >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "netstat -s">> $LOG_FILE
netstat -s >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "sar -u 5 3">> $LOG_FILE
sar -u 5 3 >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "sar -q 5 3">> $LOG_FILE
sar -q 5 3 >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "ps -e -w -o user,pid,ppid,s,pcpu,pmem,vsz,rss,stime,time,args" >> $LOG_FILE
ps -e -ww -o user,pid,ppid,s,pcpu,pmem,vsz,rss,stime,time,args >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "iostat -t -x" >> $LOG_FILE
iostat -t -x >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "df -h" >> $LOG_FILE
df -h >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "mpstat 5 3" >> $LOG_FILE
/usr/bin/mpstat 5 3 >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "vmstat 5 3" >> $LOG_FILE
vmstat 5 3 >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "free -m -s 5 -c 3" >> $LOG_FILE
free -m -s 5 -c 3 >>$LOG_FILE
grep MemTotal /proc/meminfo >>$LOG_FILE
grep SwapTotal /proc/meminfo >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "IPCS data" >> $LOG_FILE
ipcs -l >> $LOG_FILE
echo " ---------------------------- " >> $LOG_FILE
ipcs -u >> $LOG_FILE
echo " ---------------------------- " >> $LOG_FILE
ipcs >> $LOG_FILE
echo " ---------------------------- " >> $LOG_FILE
ipcs -t >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
#####################################################################
### END OF SCRIPT ###
#####################################################################
Labels: LINUX, PERFORMANCE TUNING, SCRIPT
Script to Gather data from the database for Process information and User connections
REM
REM START OF SQL
REM
REM Filename : mzRunSQL.sql
REM Author : Mike Shaw
REM Date Updated : 29 August 2008
REM Purpose : Script to gather data from the database for process
REM information and user connections
REM
set serveroutput on size 1000000
set echo on
set timing on
set feedback on
set long 10000
set pagesize 132
set linesize 110
col username form a10
col program form a30
col how_many forma 9999
col machine form a25
col module form a50
--
rem show time
select to_char(sysdate, 'DD-MON-RR HH24:MI:SS') START_TIME from dual
/
rem Instance identification
select *
from v$instance
/
REM Summary of database connections
select s.module, s.machine, s.username, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by rollup(s.module,s.machine,s.username)
/
REM Connections by machine and instance
select s.machine, s.username, s.module, s.inst_id, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id
/
REM
REM Get count of number of connected Apps 11i users
REM
col user_name format a15
col first_connect format a18
col last_connect format a18
col How_many_user_sessions format 9999999999
col How_many_sessions format 9999999999
REM
REM Summary of how many users
REM
select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) >
sysdate
and counter < limit_connects
/
REM
REM Number of sessions per user
REM
select user_name, count(*) How_many_sessions
from icx_sessions icx, fnd_user u
where icx.user_id = u.user_id
and disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) >
sysdate
and counter < limit_connects
group by user_name
order by 2 desc
/
REM
REM Quick look for blockers and lockers
REM
col BLOCKER form a5
col Username format A15
col Sid format 9990 heading SID
col Type format A4
col Lmode format 990 heading 'HELD'
col Request format 990 heading 'REQ'
col Id1 format 999999999999
col Id2 format 999999999999
break on Id1 skip 1 dup
--
SELECT M.Sid,
M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',LTRIM(TO_CHAR(Lmode,'990')))
Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1,
M.Id2,
DECODE(block, 0, 'NO', 'YES' ) BLOCKER
FROM V$LOCK M
WHERE
M.Request ! = 0 or (M.Request = 0 and Lmode != 4 and (id1, id2) in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1 = M.Id1 and S.Id2
= M.Id2) )
ORDER BY Id1, Id2, M.Request
/
REM
REM Check for database statement leaks
REM May give symptoms like OutOfMemoryError
REM
set lines 132
set pages 1000
column module format A40
column program format A30
select oc.sid,oc.hash_value,oc.sql_text,count(*) How_Many
from gv$open_cursor oc
group by sid,hash_value,sql_text
having count(*) > 5
order by 4
/
rem show time
select to_char(sysdate, 'DD-MON-RR HH24:MI:SS') END_TIME from dual
/
exit;
REM
REM END OF SQL
REM
Tablespace Utilization Script in Oracle
How to check oracle tablespace report
1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.
1. Check the database details.
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;
2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
Tablespace Utilization Script (including AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)
---------------------------------------------------------------------------------------------------------------------
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
3. Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 50000 lines 32767
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;
Note:- If required, can get the DDL of a tablespace as below.
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
Script to take user password backup in Oracle 11g
Script to take user password backup in Oracle 11g
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
select 'alter user ' || name || ' identified by values "' || password || "';' from sys.user$
where name in ('&name');
USER DDL
SET LONG 99999999
select dbms_metadata.get_ddl('USER','&username') from dual;
Labels: SCHEMA REFRESH, SCRIPT, USER
July 21, 2013
RMAN Backup of Database
How to take RMAN backup with and without incremental level.
RMAN INCREMENTAL BACKUP LEVEL 0
$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 0 database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc' not backed up 1 times;;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>
RMAN INCREMENTAL BACKUP LEVEL 1
$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 1 database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc' not backed up 1 times;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>
RMAN BACKUP OF DATABASE (WITHOUT USING LEVEL 0)
$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>
NOTE:-
If you have taken the RMAN full backup using the command ‘Backup database’, where as a level 0 backup is physically identical to a full backup. The
only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository so it can be used as the parent for a level 1
backup. Simply the ‘full backup without level 0’ can not be considered as a parent backup from which you can take level 1 backup.
Incremental backup levels:-
Level 0 – full backup that can be used for subsequent incrementals
RMAN> backup incremental level 0 database;
Differential Level 1–only the blocks that have changed since the last backup (whether it is level 0 or level 1)
RMAN> backup incremental level 1 differential database;
Cumulative Level 1 – all changes since the last level 0 incremental backup
RMAN> backup incremental level 1 cumulative database;
A full backup cannot be used for a cumulative level 1 backup.
A cumulative level 1 backup must be done on top of an incremental level 0 backup.
RMAN Troubleshooting
RMAN Troubleshooting Queries at Database Level
SET PAGESIZE 20000
SET LINESIZE 1000
SET TRIMSPOOL ON
SET PAUSE OFF
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET ECHO ON
SET NUMFORMAT 999999999999999
COL TABLESPACE_NAME FORMAT A50
COL FILE_NAME FORMAT A50
COL NAME FORMAT A50
COL MEMBER FORMAT A50
col DFILE_CHKP_CHANGE format a40
col DFILE_HED_CHKP_CHANGE format a40
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ARCHIVE LOG LIST;
SELECT * FROM gv$instance;
SELECT * FROM v$recover_file;
SELECT * FROM v$recovery_log;
select distinct status from v$backup;
select distinct(status) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile_header;
select * from v$backup;
SELECT dbid,
name,
TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode,
log_mode,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
TO_CHAR(controlfile_change#, '999999999999999') as controlfile_change#,
TO_CHAR(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time
FROM v$database;
SELECT f.name, b.status, b.change#, b.time
FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';
SELECT name,
file#,
status,
enabled,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(offline_change#, '999999999999999') as offline_change#,
TO_CHAR(online_change#, '999999999999999') as online_change#,
TO_CHAR(online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile
where status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);
SELECT name,
file#,
status,
error,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile_header
WHERE status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);
SELECT status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;
SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
to_char(dd.checkpoint_change#,'999999999999999') dfile_chkp_change,
to_char(dh.checkpoint_change#,'999999999999999') dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#;
SELECT name file_name,
recover,
fuzzy,
checkpoint_change#
FROM v$datafile_header
ORDER BY checkpoint_change#;
SELECT hxfil file_num,
hxfnm file_name,
fhtyp type,
hxerr validity,
fhscn scn,
fhtnm tablespace_name,
fhsta status ,
fhrba_seq sequence
FROM x$kcvfh;
SELECT group#,
thread#,
sequence#,
members,
archived,
status,
TO_CHAR(first_change#, '999999999999999') as first_change#
FROM v$log;
SELECT group#,member
FROM v$logfile;
SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.archived,
a.deleted,
TO_DATE(a.completion_time, 'DD-MON-YYYY HH24:MI:SS') as completed
FROM v$archived_log a, v$log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;
How to run SQL scripts at DB level from OS level
$nohup sqlplus '/ as sysdba' @script_name.sql &
Labels: LINUX, SCRIPT, TIPS
June 01, 2013
How to Drop SCHEMA Objects in Oracle
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by
object_type;
DROP SCHEMA OBJECTS
-------------------
set head off
set pagesize 0
set linesize 300
spool target_schemaname_drop_obj.sql
select 'drop '||object_type||' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner in
('&owner') and object_type not in ('DATABASE LINK','JOB','LOB') order by object_type,object_name
/
spool off;
set head on
@ target_schemaname_drop_obj.sql
exit
DROP OTHER OBJECTS (If required)
------------------
set head off
set pagesize 0
set linesize 300
spool target_schemaname_drop_other_obj.sql
select 'DROP '||decode(object_type, 'PACKAGE BODY', 'PACKAGE',object_type)|| ' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE
CONSTRAINTS;',';')
from dba_objects where owner=upper('&owner') and object_type not in ('TABLE')
/
spool off;
set head on
@target_schemaname_drop_other_obj.sql
exit
ls -lrth target_schemaname_drop*.sql
vi target_schemaname_drop_obj.sql / vi target_schemaname_drop_other_obj.sql
:wq
sqlplus "/as sysdba"
@target_schemaname_drop_obj.sql
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by
object_type;
exit
How to find Invalid Objects and Compile in Oracle
Finding Invalid Objects and Compiling
Objects that requires recompilation are
----------------------------------------------------
VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED , JAVA CLASS, TYPE, TYPE BODY
Find Invalid Objects
---------------------------
set pages 50000 lines 32767
select OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID' and OWNER in ('&OWNER') GROUP BY OWNER, OBJECT_TYPE ORDER BY
OBJECT_TYPE;
SPOOL The Invalids list
---------------------------
spool generate_invalids.lst
set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where status = 'INVALID' and owner in ('&owner') order by
object_type, object_name desc
/
spool off
Recompile Invalid Objects
-----------------------------------
spool recompile_invalids.lst
select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type) || ' ' ||owner|| '."'||object_name||'" '||decode(object_type,'PACKAGE
BODY','COMPILE BODY','compile')|| ' ; '
from dba_objects where object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE','VIEW', 'TRIGGER','FUNCTION','SYNONYM') and status='INVALID' and owner in
('&owner')
/
spool off
@recompile_invalids.lst
Validation
----------
set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where owner in ('&owner') and trunc(last_ddl_time) = trunc
(sysdate) order by object_type, object_name
/
exit
------------------------------------------------------------------------------------------------------------------
MORE QUERIES
Generate Oracle scripts to compile procedure , function , package , package body,trigger, view :-
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME;
------------------------------------------------------------------------------------------------------------------
set heading off;
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set tab on;
set scan off;
set verify off;
--
SPOOL gen_inv_obj.sql;
select
decode (OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || a.OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || a.OWNER||'.'||OBJECT_NAME || '
compile;')
from dba_objects a,
(select max(level) order_number, object_id from public_dependency
connect by object_id = prior referenced_object_id
group by object_id) b
where A.object_id = B.object_id(+)
and STATUS = 'INVALID'
and OBJECT_TYPE in ('PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE','TRIGGER', 'VIEW')
order by
order_number DESC,OBJECT_TYPE,OBJECT_NAME;
SPOOL off;
@gen_inv_obj.sql;
------------------------------------------------------------------------------------------------------------------
set serverouput on
DECLARE
comp_pack VARCHAR2 (100);
comp_pack_body VARCHAR2 (200);
comp_view VARCHAR2 (200);
comp_proc VARCHAR2 (200);
comp_trig VARCHAR2 (200);
comp_func VARCHAR2 (200);
BEGIN
FOR c IN ( SELECT * FROM dba_objects WHERE status = 'INVALID' ORDER BY object_type)
LOOP
BEGIN
--generate compile statement
comp_pack :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile;';
comp_pack_body :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile body;';
comp_view :='alter view ' || c.owner || '.' || c.object_name || ' compile;';
comp_proc :='alter procedure '|| c.owner|| '.'|| c.object_name|| ' compile;';
comp_func :='alter function '|| c.owner|| '.'|| c.object_name|| ' compile;';
comp_trig :='alter trigger '|| c.owner|| '.'|| c.object_name|| ' compile;';
DBMS_OUTPUT.put_line ('Compile -> ' || c.object_name || ' type : ' || c.object_type);
--compile
IF c.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE comp_pack;
ELSIF c.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE comp_pack_body;
ELSIF c.object_type = 'VIEW' THEN
EXECUTE IMMEDIATE comp_view;
ELSIF c.object_type = 'PROCEDURE' THEN
EXECUTE IMMEDIATE comp_proc;
ELSIF c.object_type = 'FUNCTION' THEN
EXECUTE IMMEDIATE comp_func;
ELSIF c.object_type = 'TRIGGER' THEN
EXECUTE IMMEDIATE comp_trig;
END IF;
--catch exception and show
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Compile ERROR : '|| c.owner|| '.'|| c.object_name|| ' type => '|| c.object_type);
END;
END LOOP;
END;
------------------------------------------------------------------------------------------------------------------
ASM Diskgroup Scripts
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Diskgroup Utilization Script
--------------------------------
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 500
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a12 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a45 HEAD 'Path'
COLUMN disk_file_name FORMAT a12 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a12 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a ,v$asm_disk b
where a.group_number (+) =b.group_number
ORDER BY a.name
/
To find the free space in an ASM disk:
--------------------------------------
set pages 9999 lines 900
select group_number, disk_number, name, failgroup, create_date, path, total_mb,free_mb from v$asm_disk;
To find the free space in an ASM diskgroup:
-------------------------------------------
set pages 9999 lines 900
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;
To see the current ASM operations in Progress:
----------------------------------------------
set pages 9999 lines 900
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;
ASM Diskgroup Information
-----------------------------
set pages 9999 lines 900
set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb, round(free_mb/total_mb*100,2) pct_free from
v$asm_diskgroup;
Monitor space used in ASM Disk Groups
-------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
Disk Group Name File Name File Size (MB) Used Size (MB) Free Size (MB) Pct. Used
-------------------- -------------------- -------------------- -------------- -------------- -------
Space used by database files.No other files found for cleanup.
There are 2 undo tablespaces for this database. Reclaimed some freespace from non-default undo tablespace
(i.e:"UNDOTBS1".)
Current status:
---------------
ORADATA diskgroup having xxxgb of freespace out of yyyygb.
-----------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label “” of total_mb used_mb free_mb on disk_group_name
compute sum label “Grand Total: ” of total_mb used_mb free_mb on report
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name,b.path disk_file_path,b.name disk_file_name,
b.total_mb total_mb,(b.total_mb – b.free_mb) used_mb,b.free_mb free_mb,
ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b
USING (group_number) ORDER BY a.nameUSING (group_number) ORDER BY a.name
V$ASM_DISK Header_Status:
------------------------
UNKNOWN - Automatic Storage Management disk header has not been read
CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.
PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP
statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-
specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk
group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP
statement.
CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
$df -h (Linux,AIX)
$df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;
Tablespace Utilization Script
-----------------------------
set pages 9999 lines 300
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(MB)" form 99999999.99
col Current_size heading "Current|Size(MB)" form 99999999.99
col Used_size heading "Used|Size(MB)" form 99999999.99
col Available_size heading "Available|Size(MB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024 Allocated_size
,a.cur_size/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
Tablespace Utilization Script (Detailed)
-----------------------------
set pages 9999 lines 300
set heading off;
set feedback off;
col tablespace_name for a30
select 'Database Name : ' || a.name , 'Host : ' || b.host_name from v$database a,gv$instance b;
select ' ' from dual;
set heading on;
set feedback on;
break on report
compute sum of "TOTAL SIZE in MB" on report
compute sum of "ALLOCATED SIZE in MB" on report
compute sum of "USED %" on report
compute sum of "FREE %" on report
compute sum of "SPACE_NEED_ON_OS_FOR_MAXSIZE" on report
select a.tablespace_name,
round(nvl(a.total,0)) "TOTAL SIZE in MB",
round(nvl(a.asize,0)) "ALLOCATED SIZE in MB",
round(nvl(a.asize-nvl(f.free,0),0)) "USED",
round(nvl(a.total-a.asize+f.free,0)) "FREE",
nvl(f.maxfree,0) "MAX_FREE",
round(((a.total-nvl(a.total-a.asize+f.free,0))/a.total)*100) "USED %",
round((nvl(a.total-a.asize+f.free,0)/a.total)*100) "FREE %",
round(a.total - a.asize) "SPACE_NEED_ON_OS_FOR_MAXSIZE"
from (select tablespace_name, sum(bytes)/1024/1024 "ASIZE",sum(case when maxbytes > bytes
then maxbytes else bytes end)/1024/1024 total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free,round(max(bytes)/1024/1024) maxfree
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by round((nvl(a.total-a.asize+f.free,0)/a.total)*100)
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name, file_name, bytes/1024/1024 SIZE_MB, autoextensible,
maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files
where tablespace_name='&tablespace_name' order by 1,2;
Database file sizes and locations
---------------------------------
set pages 9999 lines 300
set VERIFY OFF FEEDBACK OFF
COLUMN file_name FORMAT A51 HEADING 'File Name'
COLUMN tablespace_name FORMAT A15 HEADING 'Tablespace'
COLUMN meg FORMAT 99,999.90 HEADING 'Megabytes'
COLUMN status FORMAT A10 HEADING 'Status'
COLUMN autoextensible FORMAT A3 HEADING 'Auto Extend'
COLUMN maxmeg FORMAT 99,999 HEADING 'Max|Megabytes'
COLUMN Increment_by FORMAT 9,999 HEADING 'Inc|By'
SPOOL ${ORACLE_SID}_datafile.lst
BREAK ON tablespace_name SKIP 1 ON REPORT
COMPUTE SUM OF meg ON tablespace_name
COMPUTE SUM OF meg ON REPORT
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_data_files
UNION
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_temp_files
ORDER BY tablespace_name
/
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TABLESPACE DDL
--------------
set pagesize 0
set long 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile
--------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
------------------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace
--------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 4G;
If AUTOEXTEND ON
----------------
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;
To Create a new tablespace
-----------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 4096m;
If AUTOEXTEND ON
----------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;
To Create a new tablespace with multipple datafiles
----------------------------------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename_01.dbf' size 4096m;
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename_02.dbf' size 4096m;
Schemas in a Tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "Size in MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a Tablespace
-----------------------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
Total space allocated by Owner
------------------------------
set pages 9999 lines 300
col owner format a15
col segment_name format a30
col segment_type format a15
col tablespace_name format a20
col mb format 999,999,999
select owner
, segment_name
, segment_type
, tablespace_name
, mb
from (
select owner
, segment_name
, segment_type
, tablespace_name
, bytes / 1024 / 1024 "SIZE in MB"
from dba_segments
order by bytes desc
)
/
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Script (In Detail)
---------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;
ASM Disk Database Files Script
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
--------------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
----------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace (ASM)
-------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
Schemas in a tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
