Feeds:
Posts
Comments

Posts Tagged ‘Script’

RMAN> run
{
Allocate channel d1 type disk  format
‘/u01/backupdb/orcl_full_%T_%s_%p.bkp’;
Backup database plus  archivelog;
}
RMAN> list backup summary;
Advertisements

Read Full Post »

The following script calculates the storage allocated to an Oracle database, i.e., its overall size. You will need DBA privilege to run this script.

select ROUND( ( a.data_size + b.temp_size +
c.redo_size + d.cf_size +
e.bct_size)
/1024/1024/1024
) “total_GB_size”
from
( select SUM(bytes) data_size from v$datafile) a,
( select NVL(sum(bytes),0) temp_size from v$tempfile) b,
( select SUM(bytes) redo_size from v$log) c,
( select SUM(block_size*file_size_blks) cf_size from v$controlfile) d,
( select NVL(bytes,0) bct_size from v$block_change_tracking) e
select a.data_size+b.temp_size+c.redo_size “total_size”
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files )a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c

Read Full Post »

set pagesize 100
set heading off
set feedback off
select ‘create tablespace ‘ || df.tablespace_name || chr(10)
|| ‘ datafile ”’ || df.file_name || ”’ size ‘ || df.bytes
|| decode(autoextensible,’N’,null, chr(10) || ‘ autoextend on maxsize ‘
|| maxbytes)
|| chr(10)
|| ‘default storage ( initial ‘ || initial_extent
|| decode (next_extent, null, null, ‘ next ‘ || next_extent )
|| ‘ minextents ‘ || min_extents
|| ‘ maxextents ‘ || decode(max_extents,’2147483645′,’unlimited’,max_extents)
|| ‘) ;’
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name
/

Read Full Post »

set pagesize 100
set linesize 120
ttitle –
center ‘Submitted DBMS Jobs’ skip 2

col job format 99999 heading ‘job#’
col subu format a10 heading ‘Submitter’ trunc
col lsd format a5 heading ‘Last|Ok|Date’
col lst format a5 heading ‘Last|Ok|Time’
col nrd format a5 heading ‘Next|Run|Date’
col nrt format a5 heading ‘Next|Run|Time’
col fail format 999 heading ‘Errs’
col ok format a2 heading ‘Ok’

select
job,
log_user subu,
what proc,
to_char(last_date,’MM/DD’) lsd,
substr(last_sec,1,5) lst,
to_char(next_date,’MM/DD’) nrd,
substr(next_sec,1,5) nrt,
failures fail,
decode(broken,’Y’,’N’,’Y’) ok
from
sys.dba_jobs;

Read Full Post »

This script will list the top 10 segments in the database that have the most number of
physical reads against them.

set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10

select segment_name,object_type,total_physical_reads
from ( select owner||’.’||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in (‘physical reads’)
order by total_physical_reads desc)
where rownum <=10;

Read Full Post »

This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical reads

set serverout on size 1000000

declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select buffer_gets,substr(sql_text,1,4000)
from v$sqlarea
order by buffer_gets desc;
begin
dbms_output.put_line(‘Reads’||’ ‘||’ Text’);
dbms_output.put_line (‘—–‘||’ ‘||’—————————————————‘);
dbms_output.put_line(‘ ‘);
open c1;
for i in 1 .. 5 loop
fetch c1 into top5, text1;
dbms_output.put_line(rpad(to_char(top5),9)|| ‘ ‘||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line(‘” ‘||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/

Read Full Post »

This script will list the top 5 SQL statements sorted by the most number of physical reads

set serverout on size 1000000
set feedback off
declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select disk_reads,substr(sql_text,1,4000)
from v$sqlarea
order by disk_reads desc;
begin
dbms_output.put_line(‘Reads’||’ ‘||’ Text’);
dbms_output.put_line (‘—–‘||’ ‘||’—————————————————-‘);
dbms_output.put_line(‘ ‘);
open c1;
for i in 1 .. 5 loop
fetch c1 into top5, text1;
dbms_output.put_line(rpad(to_char(top5),9)|| ‘ ‘||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line(‘” ‘||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/

Read Full Post »

Older Posts »