Oracle Work Records

Job

Describe interval parameter values

Every night 12 o'clock ' TRUNC (sysdate + 1) '

Daily 8:30 A.M. ' TRUNC (sysdate + 1) + (8*60+30)/(24*60) '

Every Tuesday noon 12 o'clock ' Next_day (TRUNC (sysdate), ' Tuesday ') + 12/24 '

Midnight of the first day of the Month 12 o'clock ' TRUNC (Last_day (sysdate) + 1) '

The last day of each quarter 11 o'clock ' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '

Every Saturday and Sunday 6:10 A.M. ' TRUNC (LEAST (Next_day (sysdate, "SATURDAY"), Next_day (Sysdate, "SUNDAY")) + (6X60+10)/(24x60) '

--Tablespace utilization

Select Upper (f.tablespace_name) "Table space name",

D.TOT_GROOTTE_MB "Table space size (M)",

D.tot_grootte_mb-f.total_bytes "used Space (M)",

To_char (Round ((d.tot_grootte_mb-f.total_bytes)/D.TOT_GROOTTE_MB * 100, 2), ' 990.99 ')

|| '% ' "use ratio",

F.total_bytes "free Space (M)",

F.max_bytes "Max Block (M)"

From (SELECT Tablespace_name,

Round (Sum (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,

Round (Max (BYTES)/(1024x768), 2) max_bytes

From SYS. Dba_free_space

GROUP by Tablespace_name) F,

(SELECT DD.) Tablespace_name,

Round (Sum (DD). BYTES)/(1024x768 * 1024x768), 2) TOT_GROOTTE_MB

From SYS. Dba_data_files DD

GROUP by DD. Tablespace_name) D

WHERE D.tablespace_name = F.tablespace_name

ORDER by 1

Alter tablespace arvatowms add datafile '/opt/oracle/oradatas/otwb/wms03.dbf ' size 8000m

Table size

Select round (bytes/1024/1024,2) | | M ' from user_segments where segment_name= ' oms_order_log ';

Calculate Time Difference

(Year (Current_date ()) *365*24+month (Current_date ()) *30*24+day (Current_date ()) *24+hour (Current_time ()) +8+minute ( Current_time ())/60)-(Year (lastreminddate) *365*24+month (lastreminddate) *30*24+day (lastreminddate) *24+hour ( lastreminddate) +minute (lastreminddate)/60)

List dates within intervals

SELECT TRUNC (to_date (' 2014-01-01 ', ' yyyy-mm-dd ') + ROWNUM-1 as DATES

From All_objects

WHERE TRUNC (to_date (' 2014-01-01 ', ' yyyy-mm-dd ') + ROWNUM-1 <

TRUNC (to_date (' 2020-01-01 ', ' yyyy-mm-dd '));

List the months in the interval

SELECT To_char (add_months (to_date (' 2009-03 ', ' yyyy-mm '), ROWNUM-1

), ' yyyy-mm ') day_id

From DUAL

CONNECT by Rownum<=months_between

(To_date (' 2010-03 ', ' yyyy-mm '), to_date (' 2009-03 ', ' yyyy-mm ') +1

Oracle Work Records