Oracle Date How to Add Day , Hour, Minute, Second to a Date Value

 

Oracle Date How to Add Day , Hour, Minute, Second to a Date Value

 

Oracle Date How to Add Day , Hour, Minute, Second to a Date Value in Oracle

 

Oracle add Days
Oracle add Hour
Oracle add Minute
Oracle add second



-- # Add a day

select  to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1,'dd-mm-yyyy hh24:mi:ss') One_day  from dual ;


Todays-date         ONE_DAY
------------------- -------------------
05-01-2012 12:55:52 06-01-2012 12:55:52


-- # Add an hour

select  to_char(sysdate,'dd-mm-yyyy hh24:mi:ss')  "Todays-date" , to_char(sysdate +1/24,'dd-mm-yyyy hh24:mi:ss') One_hour from dual ;


Todays-date         ONE_HOUR
------------------- -------------------
05-01-2012 12:56:06 05-01-2012 13:56:06


-- # Add an Minute

select  to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/(24*60),'dd-mm-yyyy hh24:mi:ss') One_minute from dual ;


Todays-date         ONE_MINUTE
------------------- -------------------
05-01-2012 12:56:18 05-01-2012 12:57:18



-- # Add Second

select  to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/(24*60*60),'dd-mm-yyyy hh24:mi:ss') One_second from dual ;


Todays-date         ONE_SECOND
------------------- -------------------
05-01-2012 12:56:27 05-01-2012 12:56:28

 

 

 

 

 

if we want to insert into the table then

 

to_date(sysdate +1/(24*60*60),'dd-mm-yyyy hh24:mi:ss')

or

 

sysdate +1/(24*60*60)

 

or

 

SQL> select to_char(sysdate,'DD-MM-YYYY HH:MI:SS'), to_char(sysdate+interval '15' minute,'DD-MM-YYYY HH:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD TO_CHAR(SYSDATE+INT
------------------- -------------------
05-01-2012 04:36:39 05-01-2012 04:51:39

 

 

Oracle minact-scn master-status in trace file

minact-scn master-status


error message in Trace file " minact-scn master-status "




In On Oracle Version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


In my case file location

/****us/oracle/Diag/diag/rdbms/aptdb/aptdb/trace



-rw-r-----. 1 oracle oinstall 125969 Jan 3 09:40 aptdb_mmon_2433.trc <<<<< trace file in



-bash-4.1$ tail -100 aptdb_mmon_2433.trc

*** 2012-01-03 09:30:01.182
minact-scn master-status: grec-scn:0x0000.0029e860 gmin-scn:0x0000.0029e860 gcalc-scn:0x0000.0029e860
minact-scn master-status: grec-scn:0x0000.0029e912 gmin-scn:0x0000.0029e912 gcalc-scn:0x0000.0029e912

*** 2012-01-03 09:40:07.680
minact-scn master-status: grec-scn:0x0000.0029e9b3 gmin-scn:0x0000.0029e9b3 gcalc-scn:0x0000.0029e9b3

*** 2012-01-03 09:45:08.186
minact-scn master-status: grec-scn:0x0000.0029ea76 gmin-scn:0x0000.0029ea76 gcalc-scn:0x0000.0029ea76







metalink note ID...

Minact-Scn Master-Status: Grec-Scn Messages In Trace File [ID 1361567.1]




Cause
Bug 11891463

The issue is similar with BUG 11891463 - MINACT-SCN MASTER-STATUS MESSAGES WRITTEN TO MMON TRACE FILE


solution ...
-----------


SQL> alter system set "_enable_minscn_cr"=false scope=spfile; ----- restart the database ..

System altered.


or


"_smu_debug_mode=134217728"



After setting above parameter the message will not be generated but This will disable min active SCN optimization feature.

"min active scn" introduced in 11g which is supposed to enhance delayed block cleanout operations.
Note : - Disabling min active scn optimization feature will not have any impact on database.

Oracle Subtract dates times

select trunc(mydate / 3600) myhour,trunc(mod(mydate, 3600) / 60) myminute,mod(mydate, 60) mysecond
from (select round((to_date('02-01-2012 11:00:34','dd-mm-yyyy hh24:mi:ss') - to_date('02-01-2012 5:12:00','dd-mm-yyyy hh24:mi:ss')) * 86400) mydate from dual) 

   MYHOUR   MYMINUTE   MYSECOND
---------- ---------- ----------
         5         48         34

Oracle ASM Master Note

Master Note for Automatic Storage Management (ASM) [ID 1187723.1]

ORA-00845: MEMORY_TARGET not supported on this system

SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 968M
memory_target big integer 968M
shared_memory_address integer 0


alter system set memory_max_target=1200m scope=spfile;


SQL> alter system set memory_max_target=1200m scope=spfile;

System altered.



SQL> alter system set memory_target=1200m scope=spfile;

System altered.


SQL> startup force;
ORA-00845: MEMORY_TARGET not supported on this system


SQL> !df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
 21G 17G 2.9G 86% /
tmpfs 1000M 420K 1000M 1% /dev/shm <<<<------------------ increase this value
/dev/sda1 485M 41M 419M 9% /boot




from root

[root@localhost ~]# mount -t tmpfs shmfs -o size=2048m /dev/shm


vi /etc/fstab


/dev/mapper/VolGroup-lv_root / ext4 defaults 1 1
UUID=9a9b0c22-cacf-491c-ad27-b636c69da3cf /boot ext4 defaults 1 2
/dev/mapper/VolGroup-lv_swap swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
shmfs /dev/shm tmpfs size=2048m 0 0 <<<<<<<<<---- add following line 


then init 6 from reboot <<<<<--- if you can other wise leave 



SQL> startup ;
ORACLE instance started.

Total System Global Area 1255473152 bytes
Fixed Size 1344652 bytes
Variable Size 754977652 bytes
Database Buffers 486539264 bytes
Redo Buffers 12611584 bytes
Database mounted.

SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1200M
memory_target big integer 1200M
shared_memory_address integer 0

Oracle validate rpm location

wget http://oss.oracle.com/el5/oracle-validated/oracle-validated-1.0.0-5.el5.i386.rpm

http://oss.oracle.com/el5/oracle-validated/

Oracle flashback on

how to start flashback ?
how to start oracle flashback ?
Oracle flashback start




SQL> select VERSION from v$instance ;

VERSION
-----------------
11.2.0.3.0




LOG_ARCHIVE_FORMAT must be in the format: %s,%t,%r.

%s log sequence number
%S log sequence number, zero filled
%t Thread number
%T Thread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database



start from here <<<<<<<<<<<<<<<<<<<<<<<<<<<




SQL> create pfile='/tmp/init_aptdb.txt' from spfile ;

File created.



in pfile <<<<<

*.log_archive_dest_1="LOCATION=/ptus/oracle/Arch/Aptdb"
*.log_archive_format='AptOra_%t_%s_%r.arc'
*.db_recovery_file_dest='/ptus/oracle/Diag/Flashback'
*.db_recovery_file_dest_size=3221225472 --- 3gb
*.db_flashback_retention_target=7200 --- 5 days



SQL> startup mount pfile='/tmp/init_aptdb.txt' ;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2227944 bytes
Variable Size 939524376 bytes
Database Buffers 301989888 bytes
Redo Buffers 8921088 bytes
Database mounted.
SQL> Alter database flashback on;

Database altered.



SQL> alter database open ;

Database altered.


SELECT name,current_scn ,flashback_on FROM v$database;

NAME CURRENT_SCN FLASHBACK_ON
--------- ----------- ------------------
APTDB 1278076 YES


SQL> create spfile from pfile='/tmp/init_aptdb.txt' ;

File created.



SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ptus/oracle/Arch/Aptdb
Oldest online log sequence 90
Next log sequence to archive 92
Current log sequence 92

SQL> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /ptus/oracle/Diag/Flashback
db_recovery_file_dest_size big integer 3G
recovery_parallelism integer 0

SQL> show parameter retention

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 7200
undo_retention integer 900




/ptus/oracle/Diag/Flashback/APTDB/flashback

-bash-4.1$ ls -lh
total 101M
-rw-r-----. 1 oracle oinstall 51M Dec 22 14:46 o1_mf_7h6hht6d_.flb
-rw-r-----. 1 oracle oinstall 51M Dec 22 14:26 o1_mf_7h6hhwnt_.flb