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
/

******************************************************************************
***     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;


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;

*********************************************************************************

***For gettting Long   Active Sessions***

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;      
*****************************************************************************************
*** Top Disk Reads for Users in Oracle***

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;



***Database Uptime Scripts ***

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 *****

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');



# oracle scheduler job creation #

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"' );
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

Popular posts from this blog

ORACLE GOLDENGATE

-
Image
1)  GoldenGate Setup & Configuration On Source & Target Hosts Perform the Following On Source: Unzip the GG Software and Perform the installation & Configuration [oracle@targethost]/data # unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip [oracle@targethost]/data # tar -xvf ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip [oracle@sourcehost]/data/ggate # ./ggsci GGSCI (targethost) 1> create subdirs 1. Ensure The database is in archive log mode 2. Create Necessary tablespace and goldengate user in database SQL> create tablespace ggs_data datafile '/data/GGS/Base/ggs_data01.dbf' size 200m; SQL> create user ggs_owner identified by ggs_owner default tablespace ggs_data temporary tablespace temp; SQL> grant connect,resource to ggs_owner; SQL> grant select any dictionary, select any table to ggs_owner; SQL> grant create table to ggs_owner; SQL> grant flashback any table to ggs_owner; SQL> grant execute on dbms_flashback to
Read more

ORACLE RAC QUESTION

-
Image
                                  RAC               What   is   Cluster ? A cluster provides an optional method of storing table data. A cluster is made up of a group of tables that share the same data blocks. The tables are grouped together because they share common columns and are often used together          OR Two or more computers that share resources and work together to form a larger logical computing unit. RAC and Oracle Parallel Server can be used to access Oracle from multiple nodes of a clustered system. What is   oracle clusterware ? Oracle Clusterware is software that enables servers to operate together as if they are one server. Each server looks like any standalone server. However, each server has additional processes that communicate with each other so the separate servers appear as if they are one server to applications and end users. Why RAC, what are the benifits of using RAC? The benefits of Real Application Clusters: Ability to spread CPU
Read more

EXADATA ARCHITECTURE IN ORACLE

-
Image
OVERVIEW :                You can use Exadata to consolidate your storage environment Using Exadata multiple  databases can use storage from a single pool. Exadata uses Oracle Automatic Storage Management (ASM) to evenly distribute the storage load for every database across every available disk in the storage pool. Every database can use all the available disks to maximize performance. Exadata requires the use of Oracle Database 11g Release 2. Exadata works equally well with single-instance or Oracle Real Application Clusters (RAC) databases. Users and database administrators use the same tools and knowledge they are already familiar with. Being based on industry-standard components and technologies, Exadata is inexpensive to deploy. In addition, tight integration with the full suite of Oracle Database high-availability features, ensures that the reliability and integrity needs of mission-critical environments are met.              A key advantag
Read more