Scripts

Identifying index fragmentation for a schema

select (case when b.partitioned='NO'
then 'alter index testbuf.' || b.index_name || ' rebuild online; ' || b.table_name
when b.partitioned='YES'
then ' alter index testbuf.' || e.index_name || ' rebuild partition ' || e.partition_name ||
' online; '|| b.table_name end) object_info,
(case when b.partitioned='NO' then b.num_rows* (c.avg_col_len + 25)
when b.partitioned='YES' then e.num_rows* (c.avg_col_len + 25) end) actual_size,
a.bytes real_size
from user_segments a, user_indexes b,user_ind_partitions e,
(select sum(x.avg_col_len)avg_col_len,z.index_name,w.partition_name
from user_tab_columns x, user_ind_columns y,user_indexes z,user_ind_partitions w
where x.column_name = y.column_name
and x.table_name = z.table_name
and y.index_name = z.index_name
and w.index_name(+) = z.index_name
group by z.index_name,w.partition_name)c
where a.segment_name = b.index_name
and a.segment_name = e.index_name(+)
--and a.owner = b.owner
--and a.owner = e.index_owner (+)
and a.partition_name = e.partition_name(+)
and c.index_name=a.segment_name
and nvl(c.partition_name,'grt')=nvl(e.partition_name,'grt')
--and (a.owner = 'TESTBUF')
--and a.bytes > 1024*1024
and a.bytes > b.initial_extent + (b.initial_extent * .1)
and a.bytes > 2 * (case when b.partitioned='NO' then b.num_rows* (c.avg_col_len + 25)
when b.partitioned='YES' then e.num_rows* (c.avg_col_len + 25) end);


RMAN BACKUP JOB DETAILS

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,
INPUT_BYTES_PER_SEC/1024/1024 "INPUTMBPS_TO_RMAN",
OUTPUT_BYTES_PER_SEC/1024/1024 "OUTPUTMBPS_FROM_RMAN",
INPUT_BYTES/1024/1024  "TOTAL_DATA_SIZE_MB",
OUTPUT_BYTES/1024/1024 "BACKUP_SIZE_IN_MB"
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

To FIND THE TIME TAKEN FOR RMAN BACKUP TO COMPLETE


select round(sofar/totalwork,2) pct,sofar,totalwork,message from v$session_longops where totalwork !=0 and sofar!=totalwork and opname like 'RMAN%' and sid in (select sid from v$session where status = 'ACTIVE');

FINDING THE CONSOLIDATED BACKUP SIZE FOR RMAN

select ctime "Date", decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type, bsize "Size GB"
from (select trunc(bp.completion_time) ctime, backup_type, round(sum(bp.bytes/1024/1024/1024),2) bsize from v$backup_set bs, v$backup_piece bp
where bs.set_stamp = bp.set_stamp and bs.set_count  = bp.set_count and bp.status = 'A' group by trunc(bp.completion_time), backup_type)

order by 1, 2;

SCRIPT FOR SHRINKING DATAFILES

COLUMN SHRINK_DATAFILES FORMAT A75 WORD_WRAPPED

set pages 1000 lines 200

COLUMN VALUE NEW_VAL BLKSIZE

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size'

/

SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) || 'M;'

SHRINK_DaTAFILES FROM DBA_DATA_FILES DBADF ,(SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID )

DBAFS WHERE

DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS*&&BLKSIZE/1024/1024)- CEIL((NVL(HWM,1)* &&BLKSIZE)/1024/1024 ) > 0 AND DBADF.

tablespace_name='&tbs_name'

/