Replace duplicate values only in consecutive records with NULL

-- Replace duplicate values only in consecutive records with NULL [duplicate]-- SELECT CASE WHEN lag(tran_id) over(order by NULL) = tran_id THEN NULL ELSE tran_id END tran_id, CASE WHEN lag(tran_name) over(order by NULL) = tran_name THEN NULL ELSE tran_name END tran_name, flag FROM t; TRAN_ID TRAN_N F ---------- ------ - 101 Lend A B C D … Continue reading Replace duplicate values only in consecutive records with NULL

Restore RMAN to new server and refresh with Archivelog apply

Source Database --------------- ::: Directory Structure ::: ORACLE_HOME : E:\oracle\product\10.2.0\db_1 RMAN Backup : E:\rman_backup_full Control Files : E:\oradata\ecc DataFiles: E:\oradata\ECC Archivelog Destination: E:\archivelogs adump : e:\oracle\product\10.2.0\admin\ecc\adump bdump : e:\oracle\product\10.2.0\admin\ecc\bdump cdump : e:\oracle\product\10.2.0\admin\ecc\cdump udump : e:\oracle\product\10.2.0\admin\ecc\udump 1. create pfile='E:\eccprod.ora' from spfile;   2. rman target / backup incremental level 0 TAG 'ECC_DAILY_FULL' database filesperset 4; backup … Continue reading Restore RMAN to new server and refresh with Archivelog apply

ORA-29548 release of java system classes in the database (12.2.0.2.160419.1.6 ) does not match that of oracle executable (12.1.0.2.170418 1.6)

This error is mostly due to a mismatch in the Java system classes. It happened to us when we cloned a 12c Database which was on a lower home version to a 12c home on a higher version. It requires data patch to be run and rollback the old patch and apply the new JVM … Continue reading ORA-29548 release of java system classes in the database (12.2.0.2.160419.1.6 ) does not match that of oracle executable (12.1.0.2.170418 1.6)

Rebuild All Indexes on a Schema in Oracle

spool index_rebuild.sql select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;' from dba_indexes where owner=upper('SAMPLE'); spool off

First Day and Last Day of Current Month Oracle

Query to Select the First Day and Last Day of the Current Month   select trim(to_date(last_day(sysdate),'DD/MM/YYYY')) as lastday from dual; select trunc((sysdate),'month') as FirstDay from dual;

Move Table to Another Tablespace Online Oracle Database 12c

The new PL/SQL procedure REDEF_TABLE in Oracle 12c  lets you redefine a table online in a single step. It can do the below changes Move table,partition or index to another tablespace Compression type change on a table including for table,partition and index More information on DBMS_REDFINITION can be accessed on Oracle Support : Master Note:Overview of Online … Continue reading Move Table to Another Tablespace Online Oracle Database 12c

Monitor Tablespace Script Oracle 12c 11g 10g

The Below Script will generate output if percentage of tablespace space left free is less tan 20%. You can easily put in a shell script and schedule it on crontab to send alerts on a daily basis.   select df.tablespace_name tspace, round(sum(fs.bytes)/(df.bytes) * 100) "%_free", round(sum(fs.bytes)/(1024*1024)) free_ts_size, df.bytes/(1024*1024) tot_ts_size from dba_free_space fs, (select tablespace_name, sum(bytes) … Continue reading Monitor Tablespace Script Oracle 12c 11g 10g

Generate Excel and PDF in Oracle and Send Report by Email

First Install UTL_MAIL ### To install UTL_MAIL: [code language="sql"] sqlplus sys/**** @$ORACLE_HOME/rdbms/admin/utlmail.sql @$ORACLE_HOME/rdbms/admin/prvtmail.plb [/code] ### Create a Reporting User ### [code language="sql"] create user tctreport identified by tctreport; grant connect to tctreport; grant dba to tctreport; sqlplus tctreport/tctreport [/code] ## Grant TCTREPORT User permission to execute UTL_MAIL ## [code language="sql"] sqlplus "/as sysdba" grant execute … Continue reading Generate Excel and PDF in Oracle and Send Report by Email

Query to check sessions writing to flashcache on Exadata

Query to check sessions writing to flashcache on Exadata select se.sid, sn.name, s.value, se.program from v$sesstat s natural join v$statname sn left join v$session se on (s.sid = se.sid) where sn.name in ('physical write requests optimized', 'cell writes to flash cache', 'cell overwrites in flash cache') and s.value <> 0 order by s.sid,name;

Add Members for Online Relog File Group on RAC ASM

Let's say you have already created logfiles from Group 1 to Group 8 on a 2 node RAC cluster on Diskgroup :+RECOC1. Now it is always highly recommended to mutiplex the relog files. This is how we will add new members to existing groups. First let us see the commands to create the logfile groups … Continue reading Add Members for Online Relog File Group on RAC ASM