Change the sequence number

CREATE OR REPLACE PROCEDURE SYS.SEQUENCE_NEWVALUE(

seqowner VARCHAR2,

seqname VARCHAR2,

newvalue NUMBER) AS

ln NUMBER;

ib NUMBER;

BEGIN

SELECT last_number, increment_by

INTO ln, ib

FROM dba_sequences

WHERE sequence_owner = upper(seqowner)

AND sequence_name = upper(seqname);

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqowner || '.' || seqname ||

' INCREMENT BY ' || (newvalue - ln);

EXECUTE IMMEDIATE 'SELECT ' || seqowner || '.' || seqname ||

'.NEXTVAL FROM DUAL' INTO ln;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqowner || '.' || seqname

|| ' INCREMENT BY ' || ib;

END;

GRANT EXECUTE ON sequence_newvalue TO gokhan;

EXEC sequence_newvalue( 'GOKHAN', 'SAMPLE_SEQ', 10000 );


delete child record

 alter table test.Table1 enable constraint table1_FK;
alter table test.Table1 enable constraint table1_FK
                                                     *
ERROR at line 1:
ORA-02298: cannot validate (test.table1_fk) - parent keys not
found

select 'delete from '  ||c.owner||'.'||c.table_name ||' a where not exists (select ''x'' from ' ||r.owner||'.'||r.table_name ||' where '||rc.column_name||' = a.'||cc.column_name||')'
 from dba_constraints c,
 dba_constraints r,
 dba_cons_columns cc,
 dba_cons_columns rc
 where c.constraint_type = 'R'
 and c.owner not in ('SYS','SYSTEM')
 and c.r_owner = r.owner
 and c.owner = cc.owner
 and r.owner = rc.owner
 and c.constraint_name = cc.constraint_name
 and r.constraint_name = rc.constraint_name
 and c.r_constraint_name = r.constraint_name
 and cc.position = rc.position
 and c.owner = 'TEST'
 and c.table_name = 'TABLE1'
 and c.constraint_name = TABLE1_FK'