February 4, 2008 by cmarkus
CREATE PUBLIC DATABASE LINK TESTING
CONNECT TO dbaname
IDENTIFIED BY “dbconn”
USING ‘@ORA’;
Untuk mengetahui database link tersebut sudah berhasil atau belum adalah sebagai berikut:
Select * from [TABLE NAME]@[DATABASE LINK]; CREATE SNAPSHOT [TABLE ALIAS NAME] NOLOGGING AS SELECT * FROM [TABLE NAME]@[DATABASE LINK];
Sample:
CREATE SNAPSHOT snapName NOLOGGING AS SELECT * FROM tblname@TESTING ; Refresh group berfungsi untuk mengontrol schedule Replikasi terhadap banyak.
Untuk membuat Refresh group dapat dilakukan dengan menggunakan perintah seperti dibawah ini:
BEGIN
DBMS_REFRESH.MAKE(
Name => ‘”[REFRESH GROUP NAME]”’,
list => '',
Next_date => SYSDATE,
Interval => ‘sysdate + [menit]/1440’
)
END;
/
List adalah bagian-bagian refresh group, yaitu terdiri dari banyak SNAPSHOT.
Sample:
BEGIN
DBMS_REFRESH.MAKE(
name => '"snapshot_name"',
list => '',
next_date => SYSDATE,
interval => '/*30:minutes*/ sysdate + 30/1440 ');
END;
/ --** select to_char(add_months(sysdate,-1),'monthyear') from dual // buat itung interval dalam (hari,bulan,tahun) CREATE OR REPLACE function DATEDIFF (
dateFrom in date,
dateTo in date,
interval in char
)
return number
AS
result number;
begin
--
select
case lower(interval)
when 'm' then round(MONTHS_BETWEEN(dateTo, dateFrom))
when 'd' then round(dateTo - dateFrom)
when 'y' then round(MONTHS_BETWEEN(dateTo, dateFrom)/12)
else 0
end into result
from dual;
--
return(result);
--
end DATEDIFF;
/ **************************************************************************** Fungsi : Itung jumlah bulan **************************************************************************** CREATE OR REPLACE FUNCTION BRITRUST.datediff (
p_what IN VARCHAR2,
p_d1 IN DATE,
p_d2 IN DATE
)
RETURN NUMBER
IS
l_result NUMBER;
BEGIN
SELECT DECODE (UPPER (p_what),
'SS', (p_d2 - p_d1) * 24 * 60 * 60,
'MI', (p_d2 - p_d1) * 24 * 60,
'HH', (p_d2 - p_d1) * 24,
'DD', TRUNC ( TO_NUMBER (TO_CHAR (TO_DATE (p_d2,
'DD-MON-YYYY'
),
'j'
)
)
- TO_NUMBER (TO_CHAR (TO_DATE (p_d1,
'DD-MON-YYYY'
),
'j'
)
)
),
'MM', TRUNC
(MONTHS_BETWEEN (TO_DATE (TO_CHAR (p_d2,
'DD-MON-YYYY'
),
'DD-MON-YYYY'
),
TO_DATE (TO_CHAR (p_d1,
'DD-MON-YYYY'
),
'DD-MON-YYYY'
)
)
),
'YY', TRUNC
( MONTHS_BETWEEN
(TO_DATE (TO_CHAR (p_d2,
'DD-MON-YYYY'
),
'DD-MON-YYYY'
),
TO_DATE (TO_CHAR (p_d1,
'DD-MON-YYYY'
),
'DD-MON-YYYY'
)
)
/ 12
),
NULL
)
INTO l_result
FROM DUAL;
RETURN (l_result);
END datediff;
/
. Kekompakan & spirit ,loyalitas sudah menjadi kebiasaannn untuk bekerja. 
