SCRIPTS FOR MONITORING ORACLE DATABASES
run
{
crosscheck archivelog all;
delete noprompt expireD archivelog all;
}
col current_scn form 999999999999999999
col TIMESTAMP_TO_SCN form 9999999999999999
col NOW10G form 99999999999999999999
col NOW9I form 99999999999999999999
col SCN form 99999999999999999999
col TIMESTAMP form 99999999999999999999
**************FOR SQL PLAN ***************
select * from table(dbms_xplan.display_awr('&sql_id'));
col begin_interval_time for a30
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where s.sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0 --and plan_hash_value=3099046451
order by 1, 2, 3
/
select * from table(dbms_xplan.display)
/
************locking ***************
SELECT/*+RULE */ DECODE(a.request, 0,'HOLDER','WAITER'), a.sid , a.lmode, a.TYPE, a.ctime,substr(b.program,1,17),b.event
FROM v$lock a,v$session b
WHERE a.sid=b.sid and
(a.id1, a.id2, a.TYPE ) IN (SELECT c.id1, c.id2, c.TYPE FROM v$lock c WHERE c.request>0)
ORDER BY a.id1, a.request
/
*************************************************************************
*********** oracle last analysed********
select owner,to_char(trunc(last_analyzed ),'DD-MON-YY') last_analyzed,count(*) from dba_tables where owner not in('APEX_030200','APEX_030200','CTXSYS','EXFSYS','FLOWS_FILES','FCDB_MIS','FCPROD_FCR_READ','FCPROD_FCR_BACKUP','OWBSYS','SYS','XDB','MDSYS','OLAPSYS','ORDDATA','ORDSYS','SYSMAN','WMSYS','XDB''SYS','APPQOSSYS','BACKUP','DBSNMP','OUTLN','PERFSTAT', 'SYSTEM') and temporary='N'
group by owner,trunc(last_analyzed )
order by 1
/
********************************************************************
**************** last analysed index************
select owner,to_char(trunc(last_analyzed ),'DD-MON-YY') last_analyzed,count(*) from dba_tables where owner not in('APEX_030200','APEX_030200','CTXSYS','EXFSYS','FLOWS_FILES','FCDB_MIS','FCPROD_FCR_READ','FCPROD_FCR_BACKUP','OWBSYS','SYS','XDB','MDSYS','OLAPSYS','ORDDATA','ORDSYS','SYSMAN','WMSYS','XDB''SYS','APPQOSSYS','BACKUP','DBSNMP','OUTLN','PERFSTAT', 'SYSTEM') and temporary='N'
group by owner,trunc(last_analyzed )
order by 1
/
**************** Index_stat.sql***************
select index_name, num_rows, distinct_keys, blevel, leaf_blocks, clustering_factor, last_analyzed
from dba_indexes
where table_owner='&owner'
and table_name='&table'
order by 1
/
************** Index_columns.sql********************
break on index_name skip 1
column column_name format a30
column column_expression for a30
set lines 120 pages 50000
select c.index_name,
c.column_name "COLUMN_NAME", c.column_position,
a.column_expression
from dba_ind_columns c,
dba_ind_expressions a
where c.table_owner=upper('&SCHEMA')
and c.table_name=upper('&table_name')
and a.index_name(+) = c.index_name
and a.table_owner(+) = c.table_owner
and a.index_owner(+) = c.index_owner
and a.table_name(+) = c.table_name
and a.column_position(+) = c.column_position
order by 1,3
/
**************** last analysed index************
select owner,to_char(trunc(last_analyzed ),'DD-MON-YY') last_analyzed,count(*) from dba_tables where owner not in('APEX_030200','APEX_030200','CTXSYS','EXFSYS','FLOWS_FILES','FCDB_MIS','FCPROD_FCR_READ','FCPROD_FCR_BACKUP','OWBSYS','SYS','XDB','MDSYS','OLAPSYS','ORDDATA','ORDSYS','SYSMAN','WMSYS','XDB''SYS','APPQOSSYS','BACKUP','DBSNMP','OUTLN','PERFSTAT', 'SYSTEM') and temporary='N'
group by owner,trunc(last_analyzed )
order by 1
/
**************** Index_stat.sql***************
select index_name, num_rows, distinct_keys, blevel, leaf_blocks, clustering_factor, last_analyzed
from dba_indexes
where table_owner='&owner'
and table_name='&table'
order by 1
/
************** Index_columns.sql********************
break on index_name skip 1
column column_name format a30
column column_expression for a30
set lines 120 pages 50000
select c.index_name,
c.column_name "COLUMN_NAME", c.column_position,
a.column_expression
from dba_ind_columns c,
dba_ind_expressions a
where c.table_owner=upper('&SCHEMA')
and c.table_name=upper('&table_name')
and a.index_name(+) = c.index_name
and a.table_owner(+) = c.table_owner
and a.index_owner(+) = c.index_owner
and a.table_name(+) = c.table_name
and a.column_position(+) = c.column_position
order by 1,3
/
*** FOR CHECKING TABLE STATISTICS ****
set lines 300 pages 50000
select owner, table_name, num_rows, blocks, last_analyzed, temporary, partitioned, sample_size, round(num_rows*avg_row_len/8192) "ACTUAL_BLOCKS" ,degree from dba_tables where table_name=upper('&tablename')
/
*******************************************************************************
*** GET THE SQL TEXT ***
[oracle@rcdbdbadm01 cch]$ cat text.sql
set long 9999999
select sql_text from v$sqltext where sql_id='&sqlid' order by piece;
*******************************************************************************
***************** FOR INDEX REBUILD*****************
alter index FINNHFCLEA.NBFC_CHEQUE_DTL_IDX21 rebuild parallel 50;
alter index FINNHFCLEA.NBFC_CHEQUE_DTL_IDX21 noparallel;
*********************************************************************************
***For gettting Long Active Sessions***select owner, table_name, num_rows, blocks, last_analyzed, temporary, partitioned, sample_size, round(num_rows*avg_row_len/8192) "ACTUAL_BLOCKS" ,degree from dba_tables where table_name=upper('&tablename')
/
*******************************************************************************
*** GET THE SQL TEXT ***
set long 9999999
select sql_text from v$sqltext where sql_id='&sqlid' order by piece;
*******************************************************************************
alter index FINNHFCLEA.NBFC_CHEQUE_DTL_IDX21 rebuild parallel 50;
alter index FINNHFCLEA.NBFC_CHEQUE_DTL_IDX21 noparallel;
alter index FINNONELEA.NBFC_TXN_ADVICE_DTL_IDX5 rebuild PARTITION NBFC_TXN_ADVICE_DTL_FO_P5 parallel 50;
alter index FINNONELEA.NBFC_TXN_ADVICE_DTL_IDX5 noparallel;
*********************************************************************************
****** GET ALL SQL INFORMATION**********
set verify off
set heading off
define hash_value=.
column hash_value NEW_VALUE hash_value
set linesize 132
SELECT
/*+ RULE */ hash_value, SQL_TEXT
FROM gV$SESSION, gV$SQLtext
WHERE gV$SESSION.SQL_ADDRESS = gV$SQLtext.ADDRESS
-- AND gV$SESSION.STATUS = 'ACTIVE'
AND gV$SESSION.SID = &SID
order by piece
/
select '--------------------------------------------------------------------------------' from dual
union all
select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:"
from dual
union all
select '--------------------------------------------------------------------------------' from dual
union all
select /*+ RULE */ *
from (select
rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----'
, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v$sql_plan
where hash_value= nvl('&hash_value','0')
order by id
)
union all
select '--------------------------------------------------------------------------------' from dual;
set verify on
set heading on
undefine hash_value
set heading on
*********************************************************************************
col sql_text for a40
col module for a30
col sql_profile for a10
select inst_id,sql_id, sql_text, plan_hash_value, executions, (elapsed_time/executions)/1000000 avg_time_sec, buffer_gets/executions avg_lio, rows_processed/executions avg_row,sql_profile,module
from gv$sqlarea
where sql_id='&sql_id'
order by inst_id;
SELECT USERNAME,
TERMINAL,
PROGRAM,
SQL_ID,
LOGON_TIME,
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From v$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL
ORDER BY MINUTES_LOGGED_ON DESC; *****************************************************************************************
select username users, round(DISK_READS/Executions) DReadsExec,Executions Exec, DISK_READS DReads,sql_text
from sys.v_$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
--and Executions < 4
and b.username like upper('%&user_name%')
-- and BUFFER_GETS > 1000000
and Executions > 0
and DISK_READS > 100000
order by 2 desc;
col host_name form a10 heading "Host"
col instance_name form a8 heading "Instance" newline
col stime form a40 Heading "Database Started At" newline
col uptime form a60 heading "Uptime" newline
set heading off
select 'Hostname : ' || host_name
,'Instance Name : ' || instance_name
,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from v$instance
/
**** All Locks in Oracle Database****
select /*+ ordered */ c.username||'('||c.sid||')' blocker,
d.username||'('||d.sid||')' blockee
from v$lock a, v$lock b,v$session c,v$session d
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
and a.sid = c.sid
and b.sid = d.sid;
*****To check Lob Objects*****
COL OWNER FOR A30
COL SEGMENT_NAME FOR A30
COL SEGMENT_TYPE FOR A40
SET LINE 150 PAGESIZE 1000
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 "GB" ,BYTES/1024/1024 "MB" FROM DBA_SEGMENTS ORDER BY BYTES; where TABLESPACE_NAME='FINN_LOS_DAT' ORDER BY BYTES;
***** Tablespace Utilization *****
***** Tablespace Utilization *****
select ts_status.tbname,round(ts_status.allocated_bytes_MB),round(ts_status.free_bytes_MB),
round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100)) as PERCENTAGE
from
(SELECT a.tablespace_name as tbname, sum(a.bytes/1024/1024) allocated_bytes_MB,
sum(b.free_bytes_MB) free_bytes_MB
FROM dba_data_files a,
(SELECT file_id, SUM(bytes/1024/1024) free_bytes_MB
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
group by a.tablespace_name
ORDER BY a.tablespace_name) ts_status
where round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100))>=90
order by PERCENTAGE desc;
------------------------------------------
select FILE_NAME,AUTOEXTENSIBLE,bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='OIM_MDS';
***** Current User Activity *****
set pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
from v$sqltext a, v$session b, v$process c
where a.address = b.sql_address and b.paddr = c.addr and a.hash_value = b.sql_hash_value order by c.spid,a.hash_value,a.piece ;
***** Rman Time Remaining *****
set lines 150 pages 3000
col OPNAME for a30
select OPNAME,SOFAR/TOTALWORK*100 PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES,
trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA
from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN%';
--------------------
col dbsize_mbytes for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes for 99,999,990.00 justify right head "READ_MB"
col output_mbytes for 99,999,990.00 justify right head "WRITTEN_MB"
col output_device_type for a10 justify left head "DEVICE"
col complete for 990.00 justify right head "COMPLETE %"
col compression for 990.00 justify right head "COMPRESS|% ORIG"
col est_complete for a20 head "ESTIMATED COMPLETION"
col recid for 9999999 head "ID"
select recid
, output_device_type
, dbsize_mbytes
, input_bytes/1024/1024 input_mbytes
, output_bytes/1024/1024 output_mbytes
, (output_bytes/input_bytes*100) compression
, (mbytes_processed/dbsize_mbytes*100) complete
, to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
from v$rman_status rs
, (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile)
where status='RUNNING'
and output_device_type is not null
/
***** Locked Object Hiding *****
select s.sid, s.serial#, p.spid , l.LOCKED_MODE , l.ORACLE_USERNAME ,L.OS_USER_NAME
from
v$session s,
v$process p ,
gv$locked_object l
where
s.paddr = p.addr
and
s.sid in (select SESSION_ID from v$locked_object);
***** All Tablespace Size In Oracle *****
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
***** Dynamic Query To Resize Tablespace *****
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);
***** Some Dynamic Quries *****
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='_____';
select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='INACTIVE';
Select 'GRANT SELECT ON LOS_REL.'||Table_Name||' TO FINNLEA_REL;' From dba_Tables Where Owner='______';
***** Standby database Checking *****
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’;
select a.dest_id,a.thread#,C.CURRENT_SEQ, a.recv received,b.apply applied from
(select dest_id,thread#,max(sequence#) RECV from v$archived_log
where archived = 'YES'
and dest_id in (2,3,4)
group by dest_id,thread#) a,
(select dest_id,thread#,max(sequence#) APPLY from v$archived_log
where applied = 'YES'
group by dest_id,thread#) b,
(select dest_id,thread#,max(sequence#) CURRENT_SEQ from v$archived_log
group by dest_id,thread#) c
where a.dest_id = b.dest_id
and a.thread# = b.thread#
and a.thread#=c.thread#
and c.dest_id=b.dest_id;
SELECT PROCESS,STATUS,SEQUENCE#,THREAD# FROM V$MANAGED_STANDBY;
***** Rman Backup Common Scripts *****
rman target /
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
release channel prmy1;
release channel prmy2;
release channel prmy3;
release channel prmy4;
}
INCREMENTAL BKP SCRIPT
run
{
allocate channel c1 type DISK MAXPIECESIZE 60000M;
backup as compressed backupset incremental from scn 12979453037600 database FORMAT '/dbfs_direct/FS1/backup/TEMPARC_BKP/finnrcl_scn_%T_%U.bak';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/dbfs_direct/FS1/backup/TEMPARC_BKP/backscnstby_ctl_finnrcl_%U.bak';
release channel c1;
}
RelIance#CapItal123
OEM PATH:
https://10.65.7.220:7799/em/
----------------------SID AND SERIAL FIND------------------------
SELECT s.sid, s.serial#, s.status, p.spid
FROM v$session s, v$process p
WHERE s.username = 'CEPORTAL2'
AND p.addr(+) = s.paddr
set pages 9999 lines 300 long 999 trimspool on echo off
col username for a10
col machine for a30
col osuser for a20
col KILL_SPID for a16
col
LOGON_TIME for a20
select 'kill -9 ' || p.spid "KILL_SPID", s.username, s.sql_id, s.sid, s.serial#, s.status,s.sql_hash_value,
s.last_call_et/60 active_mins, to_char(s.logon_time,'dd-mon-yy hh24:mi') "LOGON_TIME",
s.program, s.module,
s.osuser, s.machine
from v$session s, v$process p
where s.paddr = p.addr
and s.username is not null
and sid='&sid'
order by
s.status desc
/
select sql_text from v$sql where hash_value='&hash_value'
/
COL OWNER FOR A30
COL SEGMENT_NAME FOR A30
COL SEGMENT_TYPE FOR A40
SET LINE 150 PAGESIZE 1000
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 "GB" ,BYTES/1024/1024 "MB" FROM DBA_SEGMENTS ORDER BY BYTES; where TABLESPACE_NAME='FINN_LOS_DAT' ORDER BY BYTES;
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 "GB" FROM DBA_SEGMENTS where SEGMENT_NAME='
SELECT OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE SEGMENT_NAME='SYS_LOB0000094493C00023$$';
[root@rcdbdbadm02 ~]# chage -l oracle
Last password change : May 11, 2017
Password expires : Aug 09, 2017
Password inactive : never
Account expires : never
Minimum number of days between password change : 1
Maximum number of days between password change : 90
Number of days of warning before password expires : 7
[root@rcdbdbadm02 ~]# pam_tally2 -r -u oracle
Login Failures Latest failure From
oracle 11 05/11/17 14:25:17 10.65.7.26
[root@rcdbdbadm02 ~]#
COL DB_LINK FOR A30
COL USERNAME FOR A30
COL PASSWORD FOR A30
COL HOST FOR A30
SET LINE 150 PAGESIZE 1000
----------------------------------TABLESPACE UTILIZATION---------------------
SQL> set linesize 150;
SQL> set pagesize 150;
SQL> column "tablespace_name" heading "Tablespace | Name" format a20
SQL> column "FileCount" heading "File | Count" format 999999
SQL> column "Size(MB)" heading "Size | (MB)" format 999,999,999.99
SQL> column "Free(MB)" heading "Free | (MB)" format 999,999,999.99
SQL> column "Used(MB)" heading "Used | (MB)" format 999,999,999.99
SQL> column "Max Ext(MB)" heading "Max Ext | (MB)" format 999,999,999
SQL> column "%Free" heading "% | Free" format 999.99
SQL> column "%Free Ext" heading "% | Free Ext" format 999.99
SQL> column "Graph" heading "Graph" format a11
SQL> column tablespace_name heading "Tablespace | Name" format a20
SQL> SELECT
ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",
RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph"
FROM
(SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free" desc;
rcf@4321
--------------------------------------SCHEMA SIZE------------------------------------
select sum(BYTES/1024/1024/1024) from dba_segments where OWNER='IDEASERVOUAT';
ajayk
feb@12345
-------------------------------------SELECT USERNAME---------------------------------
SET LINE 120 PAGESIZE 1000
select USERNAME from dba_users where username not in ('SYS','SYSTEM','OUTLN','MGMT_VIEW','DBSNMP','FLOWS_FILES',
'SYS','WMSYS','ORDDATA','CTXSYS','ANONYMOUS','SI_INFORMTN_SCHEMA','ORDSYS','EXFSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT'
,'XDB','ORDPLUGINS','OWBSYS','OLAPSYS','SCOTT','XS$NULL','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','ORACLE_OCM','SPA
L_WFS_ADMIN_USR','DIP','MDDATA','SPATIAL_WFS_ADMIN_USR','MDSYS','SYSMAN','GSMCATUSER','SYSDG','GSMUSER','SYSKM','SYSBACKUP','GSMADMIN_INTERNAL') group by username;
-----------------------------------TABLESPACE UTILIZATION----------------------------------
select ts_status.tbname,round(ts_status.allocated_bytes_MB),round(ts_status.free_bytes_MB),
round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100)) as PERCENTAGE
from
(SELECT a.tablespace_name as tbname, sum(a.bytes/1024/1024) allocated_bytes_MB,
sum(b.free_bytes_MB) free_bytes_MB
FROM dba_data_files a,
(SELECT file_id, SUM(bytes/1024/1024) free_bytes_MB
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
group by a.tablespace_name
ORDER BY a.tablespace_name) ts_status
where round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100))>=90
order by PERCENTAGE desc;
select FILE_NAME,AUTOEXTENSIBLE,bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='PROD2_SOAINFRA';
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;
---------------------------------------OBJECT COUNT SCHEMA----------------------------------
select owner,count(object_type) "count", object_type from ALL_OBJECTS where owner='IDEASERVOUAT'
group by owner, object_type;
select owner,count(object_name) from dba_objects where owner in ('SERVOUAT','DEVSERVOUAT') group by owner;
---------------------------------------CREATE DIRECTORY------------------------------------
create directory SUNGUARDEXP as '/finnrest/sunguard_expdp/';
grant read,write on directory SUNGUARDEXP to public;
---------------------------------------CREATE USER SCRIPT--------------------------------------
FIRST FIND DATAFILE LOC: SELECT file_name from dba_data_files;
create tablespace SOAUATTBS datafile '/oradata/NEWUATDB/NEWUATDB/datafile/soauat.dbf' size 2G;
create user soauatrcf identified by soauatrcf2015 default tablespace SOAUATTBS;
grant connect ,resource to soauatrcf;
grant create view to soauatrcf;
grant create synonym to soauatrcf;
grant create database link to soauatrcf;
select * from dba_sys_privs where GRANTEE='SOAUATRCF';
------------------------------------------FREE ASM DISK------------------------------
select a.name DiskGroup, b.disk_number Disk#, b.total_mb, b.free_mb from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number order by b.group_number, b.disk_number;
SELECT NAME, TOTAL_MB, FREE_MB, USABLE_FILE_MB FROM V$ASM_DISKGROUP;
------------------------------------------TABLESPACE UTILIZATION ON DR----------------------
select a.BYTES/1024/1024,a.TS#,b.NAME from v$datafile a, v$tablespace b where a.TS#=b.TS#;
--------------------------------------------------DR SYNC on STANDBY ---------------------------------------
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;
-------------------------------------------------Check process------------------------
set line 120
select * from v$resource_limit;
-----------------------------------------------------------------------------------------
select 'alter system kill session '''||a.sid||','||a.serial#||',@'||a.inst_id||''' immediate;'
from gv$session a
where a.event like '%enq: TX%'
----------------------------------------------GENERIC SESSION KILL QUERY---------------------
select 'alter system kill session '''||a.sid||','||a.serial#||',@'||a.inst_id||''' immediate;'
from gv$session a where a.sql_id='7884ztk77wxcz'
-----------------------------------------------------CURRENT USER ACTIVITY-----------------------------
set pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
from v$sqltext a, v$session b, v$process c
where a.address = b.sql_address and b.paddr = c.addr and a.hash_value = b.sql_hash_value order by c.spid,a.hash_value,a.piece ;
---------------------------------------------------_UNDO PARAMETER --------------------------------------
SELECT a.ksppinm Param , b.ksppstvl SessionVal ,c.ksppstvl InstanceVal,a.ksppdesc Descr
FROM x$ksppi a , x$ksppcv b , x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_U%' escape '/'ORDER BY 1
---------------------------------------------- problem latches--------------
since database startup, run the following query:
SELECT n.name, l.sleeps FROM v$latch l, v$latchname n WHERE n.latch#=l.latch# and l.sleeps > 0 order by l.sleeps;
To see latches that are currently a problem on the database run:
SELECT n.name, SUM(w.p3) Sleeps FROM V$SESSION_WAIT w, V$LATCHNAME n WHERE w.event = `latch free' AND w.p2 = n.latch#
GROUP BY n.name;
--------------------------------------------------------------------------------------
select 'alter '||object_type||' '||object_name||' compile;'From user_objects Where status <> 'VALID' And object_type IN ('VIEW','SYNONYM','PROCEDURE','FUNCTION','PACKAGE','TRIGGER');
SELECT 'GRANT DELETE,INSERT,UPDATE ON '||OBJECT_NAME||' tO EBMS_SAVV;' from user_objects WHERE object_type='TABLE';
select 'alter system kill session '''||a.sid||','||a.serial#||''' immediate;' from gv$session a where username like 'DEVSERVOUAT';
--------------------------------------------------DR SYNC-----------------------
COL HOSTNAME FOR A15
COL APPLIED_TIME FOR A15
compute SUM of LOG_GAP on report
SELECT DISTINCT GVD.INST_ID,
(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')-1))))) FROM GV$INSTANCE WHERE INST_ID=GVI.INST_ID) HOSTNAME,
GVD.NAME "DATABASE",
(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE ARCHIVED='YES' AND DEST_ID=1 AND THREAD# = GVI.THREAD#) LOG_ARCHIVED,
(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' AND THREAD# = GVI.THREAD#) LOG_APPLIED,
(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE ARCHIVED='YES' AND DEST_ID=1 AND THREAD# = GVI.THREAD#)-(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' AND THREAD# = GVI.THREAD#) LOG_GAP,
(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI')
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES') APPLIED_TIME
FROM GV$DATABASE GVD, GV$INSTANCE GVI, V$ARCHIVED_LOG GVA
WHERE GVI.THREAD#=GVA.THREAD#
AND GVI.INST_ID=GVD.INST_ID
ORDER BY GVD.INST_ID;
-------------------------------------------------------------------------------------------------------------------
select
ind.table_name,
ind.uniqueness,
col.index_name,
col.column_name,
ind.distinct_keys,
ind.sample_size
from
dba_ind_columns col,
dba_indexes ind
where
ind.table_owner = 'TSUTTON'
and
ind.table_name in ('FILE_HISTORY','PROP_CAT')
and
col.index_owner = ind.owner
and
col.index_name = ind.index_name
and
col.table_owner = ind.table_owner
and
col.table_name = ind.table_name
order by
col.table_name,
col.index_name,
col.column_position;
-----------------------------------------------------------------------------------------------
SELECT SUM(bytes)/1024/1024 gb
FROM dba_segments
WHERE (owner = 'COLLECTION_DUMP' and
segment_name = 'T07_LOAN_DATA')
OR (owner, segment_name) IN (
SELECT owner, segment_name
FROM dba_lobs
WHERE owner = 'COLLECTION_DUMP'
AND table_name = 'T07_LOAN_DATA' );
----------------------------------------------------------------------------------------
select min(FHSCN) "LOW FILEHDR SCN" , max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN" from X$KCVFH ;
select 'SET NEWNAME for DATAFILE '||file_id||' to "/rcltest/datafile/'||tablespace_name||'_'||file_id||'.dbf"' from dba_data_files order by tablespace_name
------------------------------------------------------------------------------OPEN CURSOR-----------------
SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;
select * from ( select ss.value, sn.name, ss.sid
from v$sesstat ss, v$statname sn
where ss.statistic# = sn.statistic#
and sn.name like '%opened cursors current%'
order by value desc) where rownum < 11 ;
select sid, status, event, seconds_in_wait "wait(s)" , blocking_session "blk_sesn", prev_sql_id "SQL_ID" from v$session where sid=131;
select s.username, max(a.value)
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#
and s.sid (+)= a.sid
and b.name = 'opened cursors current'
group by s.username;
-----------------------------------------------TEMP SEGMERN CLEAR-----------------
select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;
select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
from gv$sort_segment;
------------------------------------resources busy---------------------------
select a.session_id,a.object_id,b.object_name,b.owner,b.object_type,a.oracle_username from gv$locked_object a,dba_objects b,gv$session c
where a.object_id=b.object_id and oracle_username='FINNLEA_REL' and b.object_id in (72559,72324) and c.status='INACTIVE'
select distinct (a.session_id) from gv$locked_object a,dba_objects b,gv$session c
where a.object_id=b.object_id and oracle_username='FINNLEA_REL' and b.object_id in (72559,72324) and c.status='INACTIVE'
--------------------------------------------------TIME REMANING----------------------
set lines 150 pages 3000
col OPNAME for a30
select OPNAME,SOFAR/TOTALWORK*100 PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES,
trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA
from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN%';
=========================================locked object hiding=================
select s.sid, s.serial#, p.spid , l.LOCKED_MODE , l.ORACLE_USERNAME ,L.OS_USER_NAME
from
v$session s,
v$process p ,
gv$locked_object l
where
s.paddr = p.addr
and
s.sid in (select SESSION_ID from v$locked_object);
=====================USED THIS============
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
gv$locked_object a ,
gv$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
select * from v$session where sid='1686' and serial#='14380'
select d.object_name,d.object_id,g.SESSION_ID,g.PROCESS,g.LOCKED_MODE ,g.INST_ID from dba_objects d , gv$locked_object g where g.object_id = d.object_id order by object_name;
select s.sid, s.serial#, l.LOCKED_MODE , l.ORACLE_USERNAME ,L.OS_USER_NAME,L.object_id , d.object_name
from gv$session s, gv$locked_object l , dba_objects d
where -- d.inst_id=l.inst_id and
d.object_id = l.object_id and object_name like '%PMI%'
=====================================CONSTRAINT ON TABLES ====================
select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
from all_constraints a, all_constraints b
where a.constraint_type = 'R' and a.status='ENABLED'
and a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and b.table_name = upper('LEA_ASSET_M');
============================================DELETE THE RUNNING EXPDP THROUGH SCHEDULE===================
DECLARE
h1 NUMBER;
BEGIN
h1:=DBMS_DATAPUMP.ATTACH('SYS_EXPORT_FULL_01','SYS');
DBMS_DATAPUMP.STOP_JOB (h1, 1, 0);
END;
/
===============================================================================================================
all tablespace size in oracle
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
============================================================================================================
dynamic query to resize tablespace
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);
===============================================================================================================================
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
9823098230
select ts_status.tbname,round(ts_status.allocated_bytes_MB),round(ts_status.free_bytes_MB),
round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100)) as PERCENTAGE
from
(SELECT a.tablespace_name as tbname, sum(a.bytes/1024/1024) allocated_bytes_MB,
sum(b.free_bytes_MB) free_bytes_MB
FROM dba_data_files a,
(SELECT file_id, SUM(bytes/1024/1024) free_bytes_MB
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
group by a.tablespace_name
ORDER BY a.tablespace_name) ts_status
where round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100))>=90
order by PERCENTAGE desc;
set lines 150 pages 1000
select FILE_NAME,AUTOEXTENSIBLE,bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='SYSTEM';
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='UNDOTBS1';
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='UNDOTBS1';
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='UNDOTBS1';
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='UNDOTBS1';';
===============================================================================
to kill all session of particular user
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='UNDOTBS1';
set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
----------------------------------------------------------------------------------------------
select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='ACTIVE';
============================================================================================================
Select 'GRANT SELECT ON ETRCFPROD.'||Table_Name||' TO ETCSSPPROD;' From dba_Tables Where Owner='LOS_REL';
Select 'GRANT SELECT ON ETRHFPROD.'||Table_Name||'TO ETCSSPPROD' From dba_Tables Where Owner='ETRCFPROD';
============================================================================================================================
show parameter instance
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;
==================================================================================================================
on dr
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’;
on prod
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
============================
show parameter instance
select a.dest_id,a.thread#,C.CURRENT_SEQ, a.recv received,b.apply applied from
(select dest_id,thread#,max(sequence#) RECV from v$archived_log
where archived = 'YES'
and dest_id in (2,3,4)
group by dest_id,thread#) a,
(select dest_id,thread#,max(sequence#) APPLY from v$archived_log
where applied = 'YES'
group by dest_id,thread#) b,
(select dest_id,thread#,max(sequence#) CURRENT_SEQ from v$archived_log
group by dest_id,thread#) c
where a.dest_id = b.dest_id
and a.thread# = b.thread#
and a.thread#=c.thread#
and c.dest_id=b.dest_id;
SELECT PROCESS,STATUS,SEQUENCE#,THREAD# FROM V$MANAGED_STANDBY;
=============================================================================================
rman target /
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
release channel prmy1;
release channel prmy2;
release channel prmy3;
release channel prmy4;
}
========================================================================
SELECT DBMS_METADATA.get_ddl ('DB_LINK', MAIA_HS_LINK,MAIA_READ) FROM dba_db_links;
CREATE SYNONYM MONTHDEBTORS_HST FOR MONTHDEBTORS_HST@MAIA_HS_LINK;
=========================================================================
tbs size
SELECT /* + RULE */
df.tablespace_name AS "Tablespace"
,df.bytes / (1024 * 1024 * 1024) AS "Size (GB)"
,Trunc(fs.bytes / (1024 * 1024 * 1024)) AS "Free (GB)"
FROM (
SELECT tablespace_name
,Sum(bytes) AS bytes
FROM dba_free_space
GROUP BY tablespace_name
) fs
,(
SELECT tablespace_name
,SUM(bytes) AS bytes
FROM dba_data_files
GROUP BY tablespace_name
) df
WHERE fs.tablespace_name = df.tablespace_name
ORDER BY 3 DESC
==================================================================
invalid objects count
select object_type,count(*) from user_objects where status = 'INVALID' group by object_type;
================================================================================================================
unnaed file
select * from v$recover_file where error like '%FILE%';
=========================================================================
TO CHECK FRA SIZE
set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/
=============================================================================
rman task completed
col dbsize_mbytes for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes for 99,999,990.00 justify right head "READ_MB"
col output_mbytes for 99,999,990.00 justify right head "WRITTEN_MB"
col output_device_type for a10 justify left head "DEVICE"
col complete for 990.00 justify right head "COMPLETE %"
col compression for 990.00 justify right head "COMPRESS|% ORIG"
col est_complete for a20 head "ESTIMATED COMPLETION"
col recid for 9999999 head "ID"
select recid
, output_device_type
, dbsize_mbytes
, input_bytes/1024/1024 input_mbytes
, output_bytes/1024/1024 output_mbytes
, (output_bytes/input_bytes*100) compression
, (mbytes_processed/dbsize_mbytes*100) complete
, to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
from v$rman_status rs
, (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile)
where status='RUNNING'
and output_device_type is not null
/
chown -R oracle:oinstall /u01
chmod -R 775 /u01/oracle
========================================
ddl for jobss
select dbms_metadata.get_ddl('PROCOBJ','RTRM_DATA_INTEGRATION_JOB','RHFRTRM_PROD') from dual;
====================================================================================================
create job
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"RHFHMS"."HINDSIGHT_DATA_INTEGRATION_JOB"',
job_type => 'STORED_PROCEDURE',
job_action => '"RHFHMS"."HINDSIGHT_DATA_INTEGRATION"',
repeat_interval => 'FREQ=DAILY; BYHOUR=6; BYMINUTE=00; BYSECOND=0;',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'HINDSIGHT_DATA_INTEGRATION',
auto_drop => FALSE,
enabled => FALSE);
END;
exec dbms_scheduler.set_attribute( name => '"RHFHMS"."HINDSIGHT_DATA_INTEGRATION_JOB"',attribute => 'restartable', value => TRUE);
exec dbms_scheduler.enable( '"RHFHMS"."HINDSIGHT_DATA_INTEGRATION_JOB"' );
-------------------------------------------------------------------------------------------------------
standby_gap
SELECT high.thread#, "LowGap#", "HighGap#"
FROM
(
SELECT thread#, MIN(sequence#)-1 "HighGap#"
FROM
(
SELECT a.thread#, a.sequence#
FROM
(
SELECT *
FROM v$archived_log
) a,
(
SELECT thread#, MAX(next_change#)gap1
FROM v$log_history
GROUP BY thread#
) b
WHERE a.thread# = b.thread#
AND a.next_change# > gap1
)
GROUP BY thread#
) high,
(
SELECT thread#, MIN(sequence#) "LowGap#"
FROM
(
SELECT thread#, sequence#
FROM v$log_history, v$datafile
WHERE checkpoint_change# <= next_change#
AND checkpoint_change# >= first_change#
)
GROUP BY thread#
) low
WHERE low.thread# = high.thread#
The transactions belonging to these undo extents are committed, but the retention time has not passed: You still want/need these for Read Consistency!
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
===============================================================================================
set echo off
set pagesize 0
set linesize 1000
spool create.sql
select 'create synonym ' || table_name || ' for ' || owner || '.' || table_name || ';' FROM all_tables WHERE owner='DLPSERVO';
spol off
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'FINNSEC_REL');
exec dbms_scheduler.disable('"ETCSSP"."CUSTOMER_UNIFIED_JOB2"');
EXEC DBMS_SCHEDULER.DROP_PROGRAM(program_name=>'"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB1"',FORCE=>TRUE);
EXEC DBMS_SCHEDULER.DROP_JOB (job_name => 'CUSTOMER_UNIFIED_JOB2');
JOB-1 --PROCEDURE
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB1"',
job_type => 'STORED_PROCEDURE',
job_action => '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED"',
repeat_interval => 'FREQ=DAILY; BYHOUR=22; BYMINUTE=30; BYSECOND=0;',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'UNVERIFIED LOAN MAPPED',
auto_drop => FALSE,
enabled => FALSE);
END;
exec dbms_scheduler.set_attribute( name => '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB"',attribute => 'restartable', value => TRUE);
exec dbms_scheduler.enable( '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB"' );
JOB-2
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ETCSSP"."CUSTOMER_UNIFIED_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_MVIEW.REFRESH("CUSTOMER_UNIFIED_VIEW",''C''); END;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=1',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'CUSTOMER_UNIFIED_JOB',
auto_drop => FALSE,
enabled => TRUE);
END;
exec dbms_scheduler.set_attribute( name => '"ETCSSP"."CUSTOMER_UNIFIED_JOB"',attribute => 'restartable', value => TRUE);
exec dbms_scheduler.enable( '"ETCSSP"."CUSTOMER_UNIFIED_JOB"' );
round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100)) as PERCENTAGE
from
(SELECT a.tablespace_name as tbname, sum(a.bytes/1024/1024) allocated_bytes_MB,
sum(b.free_bytes_MB) free_bytes_MB
FROM dba_data_files a,
(SELECT file_id, SUM(bytes/1024/1024) free_bytes_MB
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
group by a.tablespace_name
ORDER BY a.tablespace_name) ts_status
where round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100))>=90
order by PERCENTAGE desc;
------------------------------------------
select FILE_NAME,AUTOEXTENSIBLE,bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='OIM_MDS';
***** Current User Activity *****
set pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
from v$sqltext a, v$session b, v$process c
where a.address = b.sql_address and b.paddr = c.addr and a.hash_value = b.sql_hash_value order by c.spid,a.hash_value,a.piece ;
***** Rman Time Remaining *****
set lines 150 pages 3000
col OPNAME for a30
select OPNAME,SOFAR/TOTALWORK*100 PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES,
trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA
from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN%';
--------------------
col dbsize_mbytes for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes for 99,999,990.00 justify right head "READ_MB"
col output_mbytes for 99,999,990.00 justify right head "WRITTEN_MB"
col output_device_type for a10 justify left head "DEVICE"
col complete for 990.00 justify right head "COMPLETE %"
col compression for 990.00 justify right head "COMPRESS|% ORIG"
col est_complete for a20 head "ESTIMATED COMPLETION"
col recid for 9999999 head "ID"
select recid
, output_device_type
, dbsize_mbytes
, input_bytes/1024/1024 input_mbytes
, output_bytes/1024/1024 output_mbytes
, (output_bytes/input_bytes*100) compression
, (mbytes_processed/dbsize_mbytes*100) complete
, to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
from v$rman_status rs
, (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile)
where status='RUNNING'
and output_device_type is not null
/
***** Locked Object Hiding *****
select s.sid, s.serial#, p.spid , l.LOCKED_MODE , l.ORACLE_USERNAME ,L.OS_USER_NAME
from
v$session s,
v$process p ,
gv$locked_object l
where
s.paddr = p.addr
and
s.sid in (select SESSION_ID from v$locked_object);
***** All Tablespace Size In Oracle *****
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
***** Dynamic Query To Resize Tablespace *****
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);
***** Some Dynamic Quries *****
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='_____';
select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='INACTIVE';
Select 'GRANT SELECT ON LOS_REL.'||Table_Name||' TO FINNLEA_REL;' From dba_Tables Where Owner='______';
***** Standby database Checking *****
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’;
select a.dest_id,a.thread#,C.CURRENT_SEQ, a.recv received,b.apply applied from
(select dest_id,thread#,max(sequence#) RECV from v$archived_log
where archived = 'YES'
and dest_id in (2,3,4)
group by dest_id,thread#) a,
(select dest_id,thread#,max(sequence#) APPLY from v$archived_log
where applied = 'YES'
group by dest_id,thread#) b,
(select dest_id,thread#,max(sequence#) CURRENT_SEQ from v$archived_log
group by dest_id,thread#) c
where a.dest_id = b.dest_id
and a.thread# = b.thread#
and a.thread#=c.thread#
and c.dest_id=b.dest_id;
SELECT PROCESS,STATUS,SEQUENCE#,THREAD# FROM V$MANAGED_STANDBY;
***** Rman Backup Common Scripts *****
rman target /
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
release channel prmy1;
release channel prmy2;
release channel prmy3;
release channel prmy4;
}
INCREMENTAL BKP SCRIPT
run
{
allocate channel c1 type DISK MAXPIECESIZE 60000M;
backup as compressed backupset incremental from scn 12979453037600 database FORMAT '/dbfs_direct/FS1/backup/TEMPARC_BKP/finnrcl_scn_%T_%U.bak';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/dbfs_direct/FS1/backup/TEMPARC_BKP/backscnstby_ctl_finnrcl_%U.bak';
release channel c1;
}
RelIance#CapItal123
OEM PATH:
https://10.65.7.220:7799/em/
----------------------SID AND SERIAL FIND------------------------
SELECT s.sid, s.serial#, s.status, p.spid
FROM v$session s, v$process p
WHERE s.username = 'CEPORTAL2'
AND p.addr(+) = s.paddr
set pages 9999 lines 300 long 999 trimspool on echo off
col username for a10
col machine for a30
col osuser for a20
col KILL_SPID for a16
col
LOGON_TIME for a20
select 'kill -9 ' || p.spid "KILL_SPID", s.username, s.sql_id, s.sid, s.serial#, s.status,s.sql_hash_value,
s.last_call_et/60 active_mins, to_char(s.logon_time,'dd-mon-yy hh24:mi') "LOGON_TIME",
s.program, s.module,
s.osuser, s.machine
from v$session s, v$process p
where s.paddr = p.addr
and s.username is not null
and sid='&sid'
order by
s.status desc
/
select sql_text from v$sql where hash_value='&hash_value'
/
COL OWNER FOR A30
COL SEGMENT_NAME FOR A30
COL SEGMENT_TYPE FOR A40
SET LINE 150 PAGESIZE 1000
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 "GB" ,BYTES/1024/1024 "MB" FROM DBA_SEGMENTS ORDER BY BYTES; where TABLESPACE_NAME='FINN_LOS_DAT' ORDER BY BYTES;
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 "GB" FROM DBA_SEGMENTS where SEGMENT_NAME='
SELECT OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE SEGMENT_NAME='SYS_LOB0000094493C00023$$';
[root@rcdbdbadm02 ~]# chage -l oracle
Last password change : May 11, 2017
Password expires : Aug 09, 2017
Password inactive : never
Account expires : never
Minimum number of days between password change : 1
Maximum number of days between password change : 90
Number of days of warning before password expires : 7
[root@rcdbdbadm02 ~]# pam_tally2 -r -u oracle
Login Failures Latest failure From
oracle 11 05/11/17 14:25:17 10.65.7.26
[root@rcdbdbadm02 ~]#
COL DB_LINK FOR A30
COL USERNAME FOR A30
COL PASSWORD FOR A30
COL HOST FOR A30
SET LINE 150 PAGESIZE 1000
----------------------------------TABLESPACE UTILIZATION---------------------
SQL> set linesize 150;
SQL> set pagesize 150;
SQL> column "tablespace_name" heading "Tablespace | Name" format a20
SQL> column "FileCount" heading "File | Count" format 999999
SQL> column "Size(MB)" heading "Size | (MB)" format 999,999,999.99
SQL> column "Free(MB)" heading "Free | (MB)" format 999,999,999.99
SQL> column "Used(MB)" heading "Used | (MB)" format 999,999,999.99
SQL> column "Max Ext(MB)" heading "Max Ext | (MB)" format 999,999,999
SQL> column "%Free" heading "% | Free" format 999.99
SQL> column "%Free Ext" heading "% | Free Ext" format 999.99
SQL> column "Graph" heading "Graph" format a11
SQL> column tablespace_name heading "Tablespace | Name" format a20
SQL> SELECT
ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",
RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph"
FROM
(SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free" desc;
rcf@4321
--------------------------------------SCHEMA SIZE------------------------------------
select sum(BYTES/1024/1024/1024) from dba_segments where OWNER='IDEASERVOUAT';
ajayk
feb@12345
-------------------------------------SELECT USERNAME---------------------------------
SET LINE 120 PAGESIZE 1000
select USERNAME from dba_users where username not in ('SYS','SYSTEM','OUTLN','MGMT_VIEW','DBSNMP','FLOWS_FILES',
'SYS','WMSYS','ORDDATA','CTXSYS','ANONYMOUS','SI_INFORMTN_SCHEMA','ORDSYS','EXFSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT'
,'XDB','ORDPLUGINS','OWBSYS','OLAPSYS','SCOTT','XS$NULL','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','ORACLE_OCM','SPA
L_WFS_ADMIN_USR','DIP','MDDATA','SPATIAL_WFS_ADMIN_USR','MDSYS','SYSMAN','GSMCATUSER','SYSDG','GSMUSER','SYSKM','SYSBACKUP','GSMADMIN_INTERNAL') group by username;
-----------------------------------TABLESPACE UTILIZATION----------------------------------
select ts_status.tbname,round(ts_status.allocated_bytes_MB),round(ts_status.free_bytes_MB),
round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100)) as PERCENTAGE
from
(SELECT a.tablespace_name as tbname, sum(a.bytes/1024/1024) allocated_bytes_MB,
sum(b.free_bytes_MB) free_bytes_MB
FROM dba_data_files a,
(SELECT file_id, SUM(bytes/1024/1024) free_bytes_MB
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
group by a.tablespace_name
ORDER BY a.tablespace_name) ts_status
where round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100))>=90
order by PERCENTAGE desc;
select FILE_NAME,AUTOEXTENSIBLE,bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='PROD2_SOAINFRA';
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;
---------------------------------------OBJECT COUNT SCHEMA----------------------------------
select owner,count(object_type) "count", object_type from ALL_OBJECTS where owner='IDEASERVOUAT'
group by owner, object_type;
select owner,count(object_name) from dba_objects where owner in ('SERVOUAT','DEVSERVOUAT') group by owner;
---------------------------------------CREATE DIRECTORY------------------------------------
create directory SUNGUARDEXP as '/finnrest/sunguard_expdp/';
grant read,write on directory SUNGUARDEXP to public;
---------------------------------------CREATE USER SCRIPT--------------------------------------
FIRST FIND DATAFILE LOC: SELECT file_name from dba_data_files;
create tablespace SOAUATTBS datafile '/oradata/NEWUATDB/NEWUATDB/datafile/soauat.dbf' size 2G;
create user soauatrcf identified by soauatrcf2015 default tablespace SOAUATTBS;
grant connect ,resource to soauatrcf;
grant create view to soauatrcf;
grant create synonym to soauatrcf;
grant create database link to soauatrcf;
select * from dba_sys_privs where GRANTEE='SOAUATRCF';
------------------------------------------FREE ASM DISK------------------------------
select a.name DiskGroup, b.disk_number Disk#, b.total_mb, b.free_mb from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number order by b.group_number, b.disk_number;
SELECT NAME, TOTAL_MB, FREE_MB, USABLE_FILE_MB FROM V$ASM_DISKGROUP;
------------------------------------------TABLESPACE UTILIZATION ON DR----------------------
select a.BYTES/1024/1024,a.TS#,b.NAME from v$datafile a, v$tablespace b where a.TS#=b.TS#;
--------------------------------------------------DR SYNC on STANDBY ---------------------------------------
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;
-------------------------------------------------Check process------------------------
set line 120
select * from v$resource_limit;
-----------------------------------------------------------------------------------------
select 'alter system kill session '''||a.sid||','||a.serial#||',@'||a.inst_id||''' immediate;'
from gv$session a
where a.event like '%enq: TX%'
----------------------------------------------GENERIC SESSION KILL QUERY---------------------
select 'alter system kill session '''||a.sid||','||a.serial#||',@'||a.inst_id||''' immediate;'
from gv$session a where a.sql_id='7884ztk77wxcz'
-----------------------------------------------------CURRENT USER ACTIVITY-----------------------------
set pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
from v$sqltext a, v$session b, v$process c
where a.address = b.sql_address and b.paddr = c.addr and a.hash_value = b.sql_hash_value order by c.spid,a.hash_value,a.piece ;
---------------------------------------------------_UNDO PARAMETER --------------------------------------
SELECT a.ksppinm Param , b.ksppstvl SessionVal ,c.ksppstvl InstanceVal,a.ksppdesc Descr
FROM x$ksppi a , x$ksppcv b , x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_U%' escape '/'ORDER BY 1
---------------------------------------------- problem latches--------------
since database startup, run the following query:
SELECT n.name, l.sleeps FROM v$latch l, v$latchname n WHERE n.latch#=l.latch# and l.sleeps > 0 order by l.sleeps;
To see latches that are currently a problem on the database run:
SELECT n.name, SUM(w.p3) Sleeps FROM V$SESSION_WAIT w, V$LATCHNAME n WHERE w.event = `latch free' AND w.p2 = n.latch#
GROUP BY n.name;
--------------------------------------------------------------------------------------
select 'alter '||object_type||' '||object_name||' compile;'From user_objects Where status <> 'VALID' And object_type IN ('VIEW','SYNONYM','PROCEDURE','FUNCTION','PACKAGE','TRIGGER');
SELECT 'GRANT DELETE,INSERT,UPDATE ON '||OBJECT_NAME||' tO EBMS_SAVV;' from user_objects WHERE object_type='TABLE';
select 'alter system kill session '''||a.sid||','||a.serial#||''' immediate;' from gv$session a where username like 'DEVSERVOUAT';
--------------------------------------------------DR SYNC-----------------------
COL HOSTNAME FOR A15
COL APPLIED_TIME FOR A15
compute SUM of LOG_GAP on report
SELECT DISTINCT GVD.INST_ID,
(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')-1))))) FROM GV$INSTANCE WHERE INST_ID=GVI.INST_ID) HOSTNAME,
GVD.NAME "DATABASE",
(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE ARCHIVED='YES' AND DEST_ID=1 AND THREAD# = GVI.THREAD#) LOG_ARCHIVED,
(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' AND THREAD# = GVI.THREAD#) LOG_APPLIED,
(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE ARCHIVED='YES' AND DEST_ID=1 AND THREAD# = GVI.THREAD#)-(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' AND THREAD# = GVI.THREAD#) LOG_GAP,
(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI')
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES') APPLIED_TIME
FROM GV$DATABASE GVD, GV$INSTANCE GVI, V$ARCHIVED_LOG GVA
WHERE GVI.THREAD#=GVA.THREAD#
AND GVI.INST_ID=GVD.INST_ID
ORDER BY GVD.INST_ID;
-------------------------------------------------------------------------------------------------------------------
select
ind.table_name,
ind.uniqueness,
col.index_name,
col.column_name,
ind.distinct_keys,
ind.sample_size
from
dba_ind_columns col,
dba_indexes ind
where
ind.table_owner = 'TSUTTON'
and
ind.table_name in ('FILE_HISTORY','PROP_CAT')
and
col.index_owner = ind.owner
and
col.index_name = ind.index_name
and
col.table_owner = ind.table_owner
and
col.table_name = ind.table_name
order by
col.table_name,
col.index_name,
col.column_position;
-----------------------------------------------------------------------------------------------
SELECT SUM(bytes)/1024/1024 gb
FROM dba_segments
WHERE (owner = 'COLLECTION_DUMP' and
segment_name = 'T07_LOAN_DATA')
OR (owner, segment_name) IN (
SELECT owner, segment_name
FROM dba_lobs
WHERE owner = 'COLLECTION_DUMP'
AND table_name = 'T07_LOAN_DATA' );
----------------------------------------------------------------------------------------
select min(FHSCN) "LOW FILEHDR SCN" , max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN" from X$KCVFH ;
select 'SET NEWNAME for DATAFILE '||file_id||' to "/rcltest/datafile/'||tablespace_name||'_'||file_id||'.dbf"' from dba_data_files order by tablespace_name
------------------------------------------------------------------------------OPEN CURSOR-----------------
SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;
select * from ( select ss.value, sn.name, ss.sid
from v$sesstat ss, v$statname sn
where ss.statistic# = sn.statistic#
and sn.name like '%opened cursors current%'
order by value desc) where rownum < 11 ;
select sid, status, event, seconds_in_wait "wait(s)" , blocking_session "blk_sesn", prev_sql_id "SQL_ID" from v$session where sid=131;
select s.username, max(a.value)
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#
and s.sid (+)= a.sid
and b.name = 'opened cursors current'
group by s.username;
-----------------------------------------------TEMP SEGMERN CLEAR-----------------
select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;
select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
from gv$sort_segment;
------------------------------------resources busy---------------------------
select a.session_id,a.object_id,b.object_name,b.owner,b.object_type,a.oracle_username from gv$locked_object a,dba_objects b,gv$session c
where a.object_id=b.object_id and oracle_username='FINNLEA_REL' and b.object_id in (72559,72324) and c.status='INACTIVE'
select distinct (a.session_id) from gv$locked_object a,dba_objects b,gv$session c
where a.object_id=b.object_id and oracle_username='FINNLEA_REL' and b.object_id in (72559,72324) and c.status='INACTIVE'
--------------------------------------------------TIME REMANING----------------------
set lines 150 pages 3000
col OPNAME for a30
select OPNAME,SOFAR/TOTALWORK*100 PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES,
trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA
from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN%';
=========================================locked object hiding=================
select s.sid, s.serial#, p.spid , l.LOCKED_MODE , l.ORACLE_USERNAME ,L.OS_USER_NAME
from
v$session s,
v$process p ,
gv$locked_object l
where
s.paddr = p.addr
and
s.sid in (select SESSION_ID from v$locked_object);
=====================USED THIS============
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
gv$locked_object a ,
gv$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
select * from v$session where sid='1686' and serial#='14380'
select d.object_name,d.object_id,g.SESSION_ID,g.PROCESS,g.LOCKED_MODE ,g.INST_ID from dba_objects d , gv$locked_object g where g.object_id = d.object_id order by object_name;
select s.sid, s.serial#, l.LOCKED_MODE , l.ORACLE_USERNAME ,L.OS_USER_NAME,L.object_id , d.object_name
from gv$session s, gv$locked_object l , dba_objects d
where -- d.inst_id=l.inst_id and
d.object_id = l.object_id and object_name like '%PMI%'
=====================================CONSTRAINT ON TABLES ====================
select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
from all_constraints a, all_constraints b
where a.constraint_type = 'R' and a.status='ENABLED'
and a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and b.table_name = upper('LEA_ASSET_M');
============================================DELETE THE RUNNING EXPDP THROUGH SCHEDULE===================
DECLARE
h1 NUMBER;
BEGIN
h1:=DBMS_DATAPUMP.ATTACH('SYS_EXPORT_FULL_01','SYS');
DBMS_DATAPUMP.STOP_JOB (h1, 1, 0);
END;
/
===============================================================================================================
all tablespace size in oracle
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
============================================================================================================
dynamic query to resize tablespace
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);
===============================================================================================================================
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
9823098230
select ts_status.tbname,round(ts_status.allocated_bytes_MB),round(ts_status.free_bytes_MB),
round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100)) as PERCENTAGE
from
(SELECT a.tablespace_name as tbname, sum(a.bytes/1024/1024) allocated_bytes_MB,
sum(b.free_bytes_MB) free_bytes_MB
FROM dba_data_files a,
(SELECT file_id, SUM(bytes/1024/1024) free_bytes_MB
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
group by a.tablespace_name
ORDER BY a.tablespace_name) ts_status
where round((((ts_status.allocated_bytes_MB-ts_status.free_bytes_MB)/ts_status.allocated_bytes_MB)*100))>=90
order by PERCENTAGE desc;
set lines 150 pages 1000
select FILE_NAME,AUTOEXTENSIBLE,bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='SYSTEM';
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='UNDOTBS1';
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='UNDOTBS1';
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='UNDOTBS1';
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='UNDOTBS1';';
===============================================================================
to kill all session of particular user
SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';' FROM v$session where username='UNDOTBS1';
set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
----------------------------------------------------------------------------------------------
select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='ACTIVE';
============================================================================================================
Select 'GRANT SELECT ON ETRCFPROD.'||Table_Name||' TO ETCSSPPROD;' From dba_Tables Where Owner='LOS_REL';
Select 'GRANT SELECT ON ETRHFPROD.'||Table_Name||'TO ETCSSPPROD' From dba_Tables Where Owner='ETRCFPROD';
============================================================================================================================
show parameter instance
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;
==================================================================================================================
on dr
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’;
on prod
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
============================
show parameter instance
select a.dest_id,a.thread#,C.CURRENT_SEQ, a.recv received,b.apply applied from
(select dest_id,thread#,max(sequence#) RECV from v$archived_log
where archived = 'YES'
and dest_id in (2,3,4)
group by dest_id,thread#) a,
(select dest_id,thread#,max(sequence#) APPLY from v$archived_log
where applied = 'YES'
group by dest_id,thread#) b,
(select dest_id,thread#,max(sequence#) CURRENT_SEQ from v$archived_log
group by dest_id,thread#) c
where a.dest_id = b.dest_id
and a.thread# = b.thread#
and a.thread#=c.thread#
and c.dest_id=b.dest_id;
SELECT PROCESS,STATUS,SEQUENCE#,THREAD# FROM V$MANAGED_STANDBY;
=============================================================================================
rman target /
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
release channel prmy1;
release channel prmy2;
release channel prmy3;
release channel prmy4;
}
========================================================================
SELECT DBMS_METADATA.get_ddl ('DB_LINK', MAIA_HS_LINK,MAIA_READ) FROM dba_db_links;
CREATE SYNONYM MONTHDEBTORS_HST FOR MONTHDEBTORS_HST@MAIA_HS_LINK;
=========================================================================
tbs size
SELECT /* + RULE */
df.tablespace_name AS "Tablespace"
,df.bytes / (1024 * 1024 * 1024) AS "Size (GB)"
,Trunc(fs.bytes / (1024 * 1024 * 1024)) AS "Free (GB)"
FROM (
SELECT tablespace_name
,Sum(bytes) AS bytes
FROM dba_free_space
GROUP BY tablespace_name
) fs
,(
SELECT tablespace_name
,SUM(bytes) AS bytes
FROM dba_data_files
GROUP BY tablespace_name
) df
WHERE fs.tablespace_name = df.tablespace_name
ORDER BY 3 DESC
==================================================================
invalid objects count
select object_type,count(*) from user_objects where status = 'INVALID' group by object_type;
================================================================================================================
unnaed file
select * from v$recover_file where error like '%FILE%';
=========================================================================
TO CHECK FRA SIZE
set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/
=============================================================================
rman task completed
col dbsize_mbytes for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes for 99,999,990.00 justify right head "READ_MB"
col output_mbytes for 99,999,990.00 justify right head "WRITTEN_MB"
col output_device_type for a10 justify left head "DEVICE"
col complete for 990.00 justify right head "COMPLETE %"
col compression for 990.00 justify right head "COMPRESS|% ORIG"
col est_complete for a20 head "ESTIMATED COMPLETION"
col recid for 9999999 head "ID"
select recid
, output_device_type
, dbsize_mbytes
, input_bytes/1024/1024 input_mbytes
, output_bytes/1024/1024 output_mbytes
, (output_bytes/input_bytes*100) compression
, (mbytes_processed/dbsize_mbytes*100) complete
, to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
from v$rman_status rs
, (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile)
where status='RUNNING'
and output_device_type is not null
/
chown -R oracle:oinstall /u01
chmod -R 775 /u01/oracle
========================================
ddl for jobss
select dbms_metadata.get_ddl('PROCOBJ','RTRM_DATA_INTEGRATION_JOB','RHFRTRM_PROD') from dual;
====================================================================================================
create job
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"RHFHMS"."HINDSIGHT_DATA_INTEGRATION_JOB"',
job_type => 'STORED_PROCEDURE',
job_action => '"RHFHMS"."HINDSIGHT_DATA_INTEGRATION"',
repeat_interval => 'FREQ=DAILY; BYHOUR=6; BYMINUTE=00; BYSECOND=0;',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'HINDSIGHT_DATA_INTEGRATION',
auto_drop => FALSE,
enabled => FALSE);
END;
exec dbms_scheduler.set_attribute( name => '"RHFHMS"."HINDSIGHT_DATA_INTEGRATION_JOB"',attribute => 'restartable', value => TRUE);
exec dbms_scheduler.enable( '"RHFHMS"."HINDSIGHT_DATA_INTEGRATION_JOB"' );
-------------------------------------------------------------------------------------------------------
standby_gap
SELECT high.thread#, "LowGap#", "HighGap#"
FROM
(
SELECT thread#, MIN(sequence#)-1 "HighGap#"
FROM
(
SELECT a.thread#, a.sequence#
FROM
(
SELECT *
FROM v$archived_log
) a,
(
SELECT thread#, MAX(next_change#)gap1
FROM v$log_history
GROUP BY thread#
) b
WHERE a.thread# = b.thread#
AND a.next_change# > gap1
)
GROUP BY thread#
) high,
(
SELECT thread#, MIN(sequence#) "LowGap#"
FROM
(
SELECT thread#, sequence#
FROM v$log_history, v$datafile
WHERE checkpoint_change# <= next_change#
AND checkpoint_change# >= first_change#
)
GROUP BY thread#
) low
WHERE low.thread# = high.thread#
The transactions belonging to these undo extents are committed, but the retention time has not passed: You still want/need these for Read Consistency!
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
===============================================================================================
set echo off
set pagesize 0
set linesize 1000
spool create.sql
select 'create synonym ' || table_name || ' for ' || owner || '.' || table_name || ';' FROM all_tables WHERE owner='DLPSERVO';
spol off
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'FINNSEC_REL');
# oracle scheduler job creation #
EXEC DBMS_SCHEDULER.DROP_PROGRAM(program_name=>'"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB1"',FORCE=>TRUE);
EXEC DBMS_SCHEDULER.DROP_JOB (job_name => 'CUSTOMER_UNIFIED_JOB2');
JOB-1 --PROCEDURE
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB1"',
job_type => 'STORED_PROCEDURE',
job_action => '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED"',
repeat_interval => 'FREQ=DAILY; BYHOUR=22; BYMINUTE=30; BYSECOND=0;',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'UNVERIFIED LOAN MAPPED',
auto_drop => FALSE,
enabled => FALSE);
END;
exec dbms_scheduler.set_attribute( name => '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB"',attribute => 'restartable', value => TRUE);
exec dbms_scheduler.enable( '"ETCSSP"."SP_UNVERIFIEDLOANS_MAPPED_JOB"' );
JOB-2
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ETCSSP"."CUSTOMER_UNIFIED_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_MVIEW.REFRESH("CUSTOMER_UNIFIED_VIEW",''C''); END;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=1',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'CUSTOMER_UNIFIED_JOB',
auto_drop => FALSE,
enabled => TRUE);
END;
exec dbms_scheduler.set_attribute( name => '"ETCSSP"."CUSTOMER_UNIFIED_JOB"',attribute => 'restartable', value => TRUE);
exec dbms_scheduler.enable( '"ETCSSP"."CUSTOMER_UNIFIED_JOB"' );
SCRIPTS TO TAKE ARCHIVE BACKUP BETWEEN SCN FOR RAC DB
run
{
allocate channel c1 type DISK MAXPIECESIZE 5000M;
allocate channel c2 type DISK MAXPIECESIZE 5000M;
allocate channel c3 type DISK MAXPIECESIZE 5000M;
backup format '/dbfs_direct/FS1/arch/arch_back_%T_%U.bak' archivelog from sequence 380154 until sequence 380730 thread 1;
backup format '/dbfs_direct/FS1/arch/arch_back_%T_%U.bak' archivelog from sequence 335926 until sequence 336500 thread 2;
BACKUP CURRENT CONTROLFILE FORMAT '/dbfs_direct/FS1/arch/ctl_back_%T_%U.bak';
release channel c1;
release channel c2;
release channel c3;
}
Comments
Post a Comment