Date time logic in Oracle - Scheduling jobs or events

This is for my note only, some times I need these schedule data time logic for Jobs in Oracle but can't recall quickly. so just put these things together here.


Execute daily      -                        'SYSDATE + 1'
Execute every 6 hours     -           'SYSDATE + 6/24'
Execute every 20 minutes -         'SYSDATE + 20/1440'
Execute every 40 seconds -         'SYSDATE + 40/86400'
Execute every 7 days -                'SYSDATE + 7'
Every day at 12:00 midnight -    'TRUNC(SYSDATE + 1)'
Every day at 10:00 p.m. -             'TRUNC(SYSDATE + 1) + 22/24'
Every Tuesday at 11:00 noon -   'NEXT_DAY(TRUNC(SYSDATE), "TUESDAY") + 11/24'
First day of the month at midnight-     'TRUNC(LAST_DAY(SYSDATE) + 1)'
Last day of the quarter at 11:00 p.m.-     'TRUNC(ADD_MONTH(SYSDATE + 2/24,3),'Q') - 1/24'

Last day of the year at 11:00 p.m.-     'TRUNC(ADD_MONTH(SYSDATE + 2/24,12),'Y') - 1/24'

Every Monday, Wednesday and Friday at 10:00 p.m. -  
         'TRUNC(LEAST(NEXT_DAY(SYSDATE, "MONDAY"), NEXT_DAY(SYSDATE, "WEDNESDAY"), NEXT_DAY(SYSDATE, "FRIDAY"))) + 22/24'

Delete large number of rows by chunking with BULK COLLECT and FORALL in PL/SQL block

Just putting this here for my reference or anyone who is looking for logic.

This approach will be best if following requirements are  meet- 

a)  Partition wise data remove not possible
b)  Deleting around 5-20%  (less % ) of data from a very large table .

 if you are planing to delete most of the record of a table then you can go for approach of CTAS (Create Table As Select). but quickest approach will be deleting data based on Partition if possible. 

Consideration for best performance: 

 a)  Need to disable referential integrity constraints if any (if is not OLTP system)
 b)  Need to mark all extra indexes unusable (if is not OLTP system)
 c)  Stop transactions while deleting if possible.



1)  Drop existing table if any

  SQL> drop table halim_table ;

2)  Creating a large table 

 SQL> create table halim_table
    as select a.* from all_Objects a,
       ( select 1 from dual connect by level <= 100 );


3) Delete code block using forall 


--set serveroutput on
--set timing on
--set time on

SQL>

declare
   type record_list is table of rowid index by pls_integer;
      l_list record_list;
      no_of_time number :=0 ;
      no_of_records number :=10000 ; ----change this value if you want to reduce or increase
   begin
     select rowid bulk collect into l_list
     from halim_table 
     where owner = 'SYSTEM';
     dbms_output.put_line('Total Records: '||l_list.count);
     for x in 0 .. trunc(l_list.count / no_of_records) loop
      --dbms_output.put_line('x:'||' '||x||' '||'l_rid: '||l_rid.count);
       forall i in x*no_of_records+1 .. least( (x+1)*no_of_records, l_list.count )
         delete from halim_table where rowid = l_list(i);
         no_of_time:=x+1 ; 
       dbms_output.put_line('Deleted'||'('||no_of_time||') '||sql%rowcount||' rows');
     end loop;
     commit;
   end;

How to configure shared folder with VM and HOST machine in oracle virtual machine?

For easily transferring files between VM and HOST, you need to setup a shared folder between them. this is very convenient while working in VM environment.

You can do that, by just following below few steps -


1)   Need to add the current user in /etc/group  file  (for Centons 6.7 or redhat ) 

cmd>  sudo gedit /etc/group

vboxsf:x:474:(currect_user_name)

example:   vboxsf:x:474:halim

save.
 
2)  From VM menu-   

Devices--> shared folders --> shared folder settings--> add you folder location

  Mark it as :   auto-mount  and Make Permanent

3) Restart the VirtualBox machine.  

4) Find in file browser   sf_folder_name

             

Or  Try Another way as below-

1) First of all, for getting VBoxLinuxAdditions.run , need to install VBOXADDITIONS_CD   as like below- (very simple, straigt forward)

--Devices ---> insert guest addition CD image ----> install

2) Then follow below steps in the VM (commands) - 
(vm_share is our folder/mount point name in both vm and host) for host we create later.

[root@cs6010grp1 ~]#
[root@cs6010grp1 ~]# mkdir /vm_share
[root@cs6010grp1 ~]#
[root@cs6010grp1 ~]# mount /dev/cdrom /vm_share
mount: block device /dev/sr0 is write-protected, mounting read-only
[root@cs6010grp1 ~]#
[root@cs6010grp1 ~]# cd /vm_share
[root@cs6010grp1 vm_share]#
[root@cs6010grp1 vm_share]# sudo ./VBoxLinuxAdditions.run
Verifying archive integrity... All good.
Uncompressing VirtualBox 4.3.30 Guest Additions for Linux............
VirtualBox Guest Additions installer
Removing installed version 4.3.30 of VirtualBox Guest Additions...
Copying additional installer modules ...
add_symlink: link file /usr/lib/VBoxGuestAdditions already exists
Installing additional modules ...
Removing existing VirtualBox non-DKMS kernel modules       [  OK  ]
Building the VirtualBox Guest Additions kernel modules
The headers for the current running kernel were not found. If the following
module compilation fails then this could be the reason.
The missing package can be probably installed with
yum install kernel-uek-devel-2.6.39-200.24.1.el6uek.i686

Building the main Guest Additions module                   [  OK  ]
Building the shared folder support module                  [  OK  ]
Building the OpenGL support module                         [  OK  ]
Doing non-kernel setup of the Guest Additions              [  OK  ]
You should restart your guest to make sure the new modules are actually used

Installing the Window System drivers
Installing X.Org Server 1.10 modules                       [  OK  ]
Setting up the Window System to use the Guest Additions    [  OK  ]
You may need to restart the the Window System (or just restart the guest system)
to enable the Guest Additions.

Installing graphics libraries and desktop services componen[  OK  ]
[root@cs6010grp1 vm_share]#
[root@cs6010grp1 vm_share]#
[root@cs6010grp1 vm_share]#

3) Create a folder in your host windows machine as below

C:\vm_share

4) In VM, add a link in the vm by selecting below menus  
see in below image- 

Devices--> shared_folder_setting--> Machine Folders --> vm_share
Auto-mount , Access should be YES.



5) Restart the VM





(vm_share (folder name), need to be correct folder name in below command)

6) Then Mount it with vboxsf command with root user- done
[root@cs6010grp1 Desktop]#
[root@cs6010grp1 Desktop]# mount -t vboxsf vm_share /vm_share

7) check it -

[root@cs6010grp1 Desktop]# cd /vm_share
[root@cs6010grp1 Desktop]# ls

Done. cheers ..Halim