Membuat (kembali) sequence pada SIAK
ORA-02289 (sequence does not exist) terjadi jika sequence tidak ada atau user tidak punya hak terhadap sequence tersebut. Penjelasan dari http://www.oracleerrorcodes.com/oracle-database-error-code-ora-02289-sequence-does-not-exist/ adalah sebagai berikut:
Oracle Database Error Code ORA-2289 Description :
sequence does not exist
Error Cause:
The specified sequence does not exist, or the user does not have the required privilege to perform this operation.
How to Solve :
Make sure the sequence name is correct, and that you have the right to perform the desired operation on this sequence.
Jika penyebabnya karena sequence tidak ada maka buatlah sequence yang dibutuhkan SIAK terlebih dahulu. Pada SIAK 2.2 terdapat 13 sequence yang dibutuhkan. Script yang digunakan untuk membuat sequence-sequence tersebut adalah sebagai berikut:
CREATE SEQUENCE "HIST_BIO_WNA_DETAIL" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
CREATE SEQUENCE "HIST_BIO_WNA_HEADER" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
CREATE SEQUENCE "HIST_BIO_WNI_DETAIL" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
CREATE SEQUENCE "HIST_BIO_WNI_HEADER" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
CREATE SEQUENCE "HIST_DATA_KK_DETAIL" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
CREATE SEQUENCE "HIST_DATA_KK_HEADER" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
CREATE SEQUENCE "SEQ_CRI" MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER CYCLE;
CREATE SEQUENCE "SEQ_KLH" MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER CYCLE;
CREATE SEQUENCE "SEQ_KWN" MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER CYCLE;
CREATE SEQUENCE "SEQ_LAP_MUTASI" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
CREATE SEQUENCE "SEQ_MTI" MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER CYCLE;
CREATE SEQUENCE "SEQ_PERSTUJU" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE "SIAK_USER_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
Jika penyebabnya adalah user tidak punya hak (privilege) untuk mengakses sequence tersebut maka alter user tersebut dan tambahkan privilege yang dibutuhkan CMIIW :)
"Jika penyebabnya adalah user tidak punya hak (privilege) untuk mengakses sequence tersebut maka alter user tersebut dan tambahkan privilege yang dibutuhkan CMIIW :)"
Bagaimana caranya mas???
Dari Wandi Yuldarmawan Bandaro Basa : Disdukcapil Kab. Solok Sumatera Barat
@Wandi : Baca referensinya di sini : http://download.oracle.com/docs/cd/B12037_01/server.101/b10739/views.htm#i1106548.
Ringkasnya : To create a sequence in your schema, you must have the CREATE SEQUENCE system privilege. To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE privilege. To alter a sequence, your schema must contain the sequence, or you must have the ALTER ANY SEQUENCE system privilege. To use a sequence, your schema must contain the sequence or you must have been granted the SELECT object privilege for another user's sequence. You can drop any sequence in your schema. To drop a sequence in another schema, you must have the DROP ANY SEQUENCE system privilege.
Sebenarnya ketika kita membuat user siakoff kita udah kasih semua privilege-nya dengan query GRANT DBA TO SIAKOFF menggunakan user sys.
Jika user katakanlah tidak bisa mengakses sequence karena tidak punya privilege SELECT ANY SEQUENCE makalogin sebagai sys dan jalankan query GRANT SELECT ANY SEQUENCE TO SIAKOFF;