ORACLEAGENT BLOG

Database growth per month

select to_char(creation_time, ‘MM-RRRR’) “Month”, sum(bytes)/1024/1024/1024 “Growth in GB
from sys.v_$datafile
where to_char(creation_time,’RRRR’)=’&YEAR_IN_YYYY_FORMAT’
group by to_char(creation_time, ‘MM-RRRR’)
order by to_char(creation_time, ‘MM-RRRR’);

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Generate resize datafile script

select ‘alter database datafile’||’ ”’||file_name||””||’ resize ‘||round(highwater+2)||’ ‘||’m’||’;’ from (
select /+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)d.db_block_size/1024/1024 highwater
from dba_data_files a ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name=’db_block_size’) d
where a.file_id= b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name);

Get database uptime

select to_char(startup_time, ‘DD-MM-YYYY HH24:MI:SS’),floor(sysdate-startup_time) DAYS from v$Instance;

Scn to Timestamp and Timestamp to Scn

— Get current scn value:
select current_scn from v$database;
— Get scn value at particular time:
select timestamp_to_scn(’26-FEB-21:05:10:00′) from dual;
— Get timestamp from scn:
select scn_to_timestamp(298411441)from dual;

Get Database incarnation information in database

set heading off
set feedback off
select ‘Incarnation Destination Configuration’ from dual;
select ‘*************************************’ from dual;
set heading on
set feedback on

select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME,
PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,
FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;

How to get CPU memory info in server

set pagesize 200
set lines 200
col name for a21
col stat_name for a25
col value for a13
col comments for a56
select STAT_NAME,to_char(VALUE) as VALUE ,COMMENTS from v$osstat where
stat_name IN (‘NUM_CPUS’,’NUM_CPU_CORES’,’NUM_CPU_SOCKETS’)
union
select STAT_NAME,VALUE/1024/1024/1024 || ‘ GB’ ,COMMENTS from
v$osstat where stat_name IN (‘PHYSICAL_MEMORY_BYTES’);

Get redo log member info

col member for a56
set pagesize 299
set lines 299

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) “Size (MB)”
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/

Get DDL of all tablespaces

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_tablespace.sql

select dbms_metadata.get_ddl(‘TABLESPACE’,tb.tablespace_name) from dba_tablespaces tb;
spool off

How to purge AWR old snapshot details in database

— Find the AWR snapshot details.
select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id

— Purge snapshot between snapid 439 to 800
execute dbms_workload_repository.drop_snapshot_range(low_snap_id =>439 , high_snap_id =>800);

— Verify again
select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id’

How to purge AWR old snapshot details in database

— Find the AWR snapshot details.
select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id

— Purge snapshot between snapid 439 to 800
execute dbms_workload_repository.drop_snapshot_range(low_snap_id =>439 , high_snap_id =>800);

— Verify again
select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id’