Archive | December 4, 2009

Script to reset sequence begin with 1

Create this procedure:

CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2, -- nama sequence
startvalue IN PLS_INTEGER -- reset ke nilai ini
) AS
cval INTEGER;
inc_by VARCHAR2(25);
BEGIN
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
cval := cval - startvalue + 1;
IF cval < 0 THEN
inc_by := ' INCREMENT BY ';
cval:= ABS(cval);
ELSE
inc_by := ' INCREMENT BY -';
END IF;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by || cval;
EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY 1';
END reset_sequence;
/

Run the procedure with parameter sequence_name
BEGIN reset_sequence('ABSENT_NOTIFICATION_SEQ',1); END;
/

How the procedure works?

  1. Get the last value of the sequence.
  2. Change the increment with – the last value of the sequence so the last value become 0.
  3. Change the increment with 1.
  4. Run the Nextval to set the sequence to 1.

Oracle Trace file (.trc) & Trace map (.trm) cleanup

To clean up old Oracle trace file (.trc) and trace map (.trm) files, use this command on UNIX based system:

find /oracle/diag/rdbms/ssekdb/ssekdb/trace/*.trc -mtime +14 -exec rm {} \;
find /oracle/diag/rdbms/ssekdb/ssekdb/trace/*.trm -mtime +14 -exec rm {} \;

The above command will delete trace files and trace map files which is older than 14 days.