Skip to Main Content
  • Questions
  • Delete a record after it is inserted

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 29, 2017 - 6:25 am UTC

Last updated: July 01, 2017 - 2:47 am UTC

Version: 11.0

Viewed 1000+ times

You Asked

1)i want delete the inserted record automatically after 10 minute of insertion by using trigger .Is it possible ?if not possible, give possible way how to do?

Otherwise give another different way to do it?

Thank You.

and Connor said...

You could have a job to do it, eg

SQL> create table t ( x int, deletion_time date );

Table created.

SQL> insert into t values (1, sysdate + 10/1440);

1 row created.

SQL> insert into t values (2, sysdate + 10/1440);

1 row created.

SQL> insert into t values (3, sysdate + 10/1440);

1 row created.

SQL> insert into t values (4, sysdate + 10/1440);

1 row created.

SQL> insert into t values (5, sysdate + 10/1440);

1 row created.

SQL> insert into t values (6, sysdate + 10/1440);

1 row created.

SQL>
SQL> select * from t;

         X DELETION_TIME
---------- -------------------
         1 30/06/2017 12:51:59
         2 30/06/2017 12:52:02
         3 30/06/2017 12:52:05
         4 30/06/2017 12:52:08
         5 30/06/2017 12:52:11
         6 30/06/2017 12:52:14

6 rows selected.

SQL> create or replace
  2  procedure del_old_rows is
  3  begin
  4    delete from t where deletion_time < sysdate ;
  5    commit;
  6  end;
  7  /

Procedure created.

SQL>
SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'del_old_rows;',sysdate,'sysdate+1/1440');
  5  end;
  6  /

PL/SQL procedure successfully completed.


Once I commit, that job becomes active, and each minute the job will run and remove any rows that have passed their expiry time.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Stopin DBMS_JOB

santhoshreddy, June 30, 2017 - 5:23 am UTC

Hi,
Is this possible to know how many jobs are active and who submitted them?
Can we stop this job once it becomes active, if stops how?

Thanks in Advance.
Connor McDonald
July 01, 2017 - 2:47 am UTC

select * from dba_jobs;

select * from dba_jobs_running;

SQL> desc dbms_job
FUNCTION BACKGROUND_PROCESS RETURNS BOOLEAN
PROCEDURE BROKEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 BROKEN                         BOOLEAN                 IN
 NEXT_DATE                      DATE                    IN     DEFAULT
PROCEDURE CHANGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 WHAT                           VARCHAR2                IN
 NEXT_DATE                      DATE                    IN
 INTERVAL                       VARCHAR2                IN
 INSTANCE                       BINARY_INTEGER          IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT
PROCEDURE INSTANCE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 INSTANCE                       BINARY_INTEGER          IN
 FORCE                          BOOLEAN                 IN     DEFAULT
PROCEDURE INTERVAL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 INTERVAL                       VARCHAR2                IN
PROCEDURE ISUBMIT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 WHAT                           VARCHAR2                IN
 NEXT_DATE                      DATE                    IN
 INTERVAL                       VARCHAR2                IN     DEFAULT
 NO_PARSE                       BOOLEAN                 IN     DEFAULT
FUNCTION IS_JOBQ RETURNS BOOLEAN
PROCEDURE NEXT_DATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 NEXT_DATE                      DATE                    IN
PROCEDURE REMOVE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
PROCEDURE RUN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 FORCE                          BOOLEAN                 IN     DEFAULT
PROCEDURE SUBMIT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          OUT
 WHAT                           VARCHAR2                IN
 NEXT_DATE                      DATE                    IN     DEFAULT
 INTERVAL                       VARCHAR2                IN     DEFAULT
 NO_PARSE                       BOOLEAN                 IN     DEFAULT
 INSTANCE                       BINARY_INTEGER          IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT
PROCEDURE USER_EXPORT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 MYCALL                         VARCHAR2                IN/OUT
PROCEDURE USER_EXPORT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 MYCALL                         VARCHAR2                IN/OUT
 MYINST                         VARCHAR2                IN/OUT
PROCEDURE WHAT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 WHAT                           VARCHAR2                IN


A job is just a session... if you need to kill it, kill it like any other session with alter system

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library