How to find Session details for RMAN backups:
select b.sid, b.serial#, a.spid, b.client_info
from
gv$process a, gv$session b
where
a.addr=b.paddr and client_info
like
'rman%';
How to find REDO log generation hour by hour in a database :
set lines 120;
set pages 999;
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
GROUP by
to_char(first_time,'YYYY-MON-DD') order by 1;
How to build Java VM in a databasae:
SQL> @?/javavm/install/update_javavm_db.sql
Reference Doc ID 2189241.1
How to find User lock information by using Audit session: If user is locked due to invalid password attempts then use below query to find the session details like who accessed the user with wrong passwords.
select os_username,username,userhost,terminal,to_char(TIMESTAMP,'DD-MON-YYYY,HH24:MI:SS'),action_name,returncode,to_char(EXTENDED_TIMESTAMP,'DD-MON-YYYY,HH24:MI:SS') from dba_audit_session where returncode not like 0 and username='<user_name>' order by 5
How to find RMAN restore progress (Standby/RMAN Cloning):
TTITLE LEFT '% Completed. Aggregate is the overall progress:' SET LINE 132 SELECT opname, round(sofar/totalwork*100) "% Complete" FROM gv$session_longops WHERE opname LIKE 'RMAN%' AND totalwork != 0 AND sofar <> totalwork ORDER BY 1;
Tablespace current space status:
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (Gb)",
SUM(fs.bytes) / (1024 * 1024* 1024) "Free (Gb)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes;
Along with Temp Tablespaces:
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (Gb)",
SUM(fs.bytes) / (1024 * 1024* 1024) "Free (Gb)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
Top 20 Objects from Database:
select owner, table_name, owner segment_owner, table_name segment_name from dba_tables
union all
select table_owner, table_name, owner segment_owner, index_name segment_name from dba_indexes
union all
select owner, table_name, owner segment_owner, segment_name from dba_lobs
union all
select owner, table_name, owner segment_owner, index_name segment_name from dba_lobs
), ranked_tables as (
select rank() over (order by sum(blocks) desc) rank, sum(blocks) blocks, r.owner, r.table_name
from segment_rollup r, dba_segments s
where s.owner=r.segment_owner and s.segment_name=r.segment_name
group by r.owner, r.table_name
)
select rank, round(blocks*8/1024/1024) GB, table_name, owner
from ranked_tables
where rank<=20
/
With Specific Schema:
with segment_rollup as (
select owner, table_name, owner segment_owner, table_name segment_name from dba_tables
union all
select table_owner, table_name, owner segment_owner, index_name segment_name from dba_indexes
union all
select owner, table_name, owner segment_owner, segment_name from dba_lobs
union all
select owner, table_name, owner segment_owner, index_name segment_name from dba_lobs
), ranked_tables as (
select rank() over (order by sum(blocks) desc) rank, sum(blocks) blocks, r.owner, r.table_name
from segment_rollup r, dba_segments s
where s.owner=r.segment_owner and s.segment_name=r.segment_name
and r.owner=upper('&schema_name')
group by r.owner, r.table_name
)
select rank, round(blocks*8/1024/1024) GB, table_name, owner
from ranked_tables
where rank<=20
/
How to get Table Growth based on interval
column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999
select obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
sum(a.SPACE_USED_DELTA) block_increase_bytes
from dba_hist_seg_stat a,
dba_hist_snapshot sn,
dba_objects obj
where sn.snap_id = a.snap_id
and obj.object_id = a.obj#
and obj.owner not in ('SYS','SYSTEM')
and end_interval_time between to_timestamp('01-JAN-2012','DD-MON-RRRR')
and to_timestamp('02-FEB-2012','DD-MON-RRRR')
group by obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name
/
How to find and disable/enable DBMS scheduler jobs:
SELECT owner, job_name, comments FROM dba_scheduler_jobs;
select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = '<Job_name>';
execute dbms_scheduler.disable('<owner>.<Job_name>');
How to find and generate disable/enable Referential constraints:
select 'alter table '||a.owner||'.'||a.table_name||' enable/disable constraint '||a.constraint_name||';'
from dba_constraints a, all_constraints b
where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
and a.r_owner = '<schema_name>'
and b.table_name = '<table_name>';
How to unregister database from catalog after dropping database:
Connect to rman catalog via RMAN
rman catalog <catalog_user/password>@<catalogtns>;
rman> list incaranation;
cross verify the dbid from present database (select dbid from v$database;)
rman> run {
set dbid <olddbid>;
unregister database <dbname> noprompt;
}
If above method doesn't work then unregister using sqlplus
sqlplus <catalog_user/password>@<catalogtns>
select db_key, dbid, name FROM rc_database WHERE name = '<dbname>';
EXECUTE dbms_rcvcat.unregisterdatabase(<old dbkey>,<old_dbid>);
cross verfiy from rman prompt
rman > list incarnation;
Comments
Post a Comment