Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2016
    Posts
    1

    Unhappy Unanswered: TRIGGER not dropping user in Oracle 11g?

    Hi ,
    i have database 11g , and i have user X and want to drop this user just once database startup .
    so i used this trigger :
    ----------------------------------------------------------------------------------------------------------

    CREATE OR REPLACE TRIGGER SYS.TW_drop_user
    AFTER STARTUP ON DATABASE


    DECLARE
    created_date_schema DATE;
    total_days NUMBER;
    schema_name VARCHAR2 (100);
    open_count number;

    BEGIN
    SELECT TO_DATE (created, 'dd/mm/rrrr')
    INTO created_date_schema
    FROM v$database;

    SELECT owner
    INTO schema_name
    FROM dba_tables
    WHERE table_name = 'RT_LICENSE' AND ROWNUM <= 1;

    SELECT TRUNC (SYSDATE) - TO_DATE (created_date_schema, 'dd/mm/rrrr')
    DAYS
    INTO total_days
    FROM DUAL;


    IF total_days > 14
    THEN

    EXECUTE IMMEDIATE ('alter USER "' || schema_name || '" account lock ');

    FOR session IN (SELECT sid, serial#
    FROM v$session
    WHERE username = schema_name)
    LOOP
    EXECUTE IMMEDIATE 'alter system disconnect session ''' || session.sid || ',' || session.serial# || ''' immediate';
    END LOOP;

    LOOP
    SELECT COUNT(*)
    INTO open_count
    FROM v$session WHERE username = schema_name;
    EXIT WHEN open_count = 0;
    dbms_lock.sleep(0.5);
    END LOOP;

    EXECUTE IMMEDIATE ('DROP USER "' || schema_name || '" CASCADE ');
    END IF;
    EXCEPTION
    WHEN OTHERS
    THEN
    NULL;
    END;

    --------------------------------------------------------------------------------------------------------------------
    but nothing happen , i login to sqlplus and connect to this user and still not dropped , shutdown and start database , login with sys i even change trigger to be after log AFTER LOGON.. user not dropped !!
    but if i'm running this script alone without trigger , its working fine .
    ---------------
    any help please , thanks in Advance.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, I'd remove the (useless) EXCEPTION section which is successfully hiding everything that might be happening and - potentially - interesting to you.

    Then, I'd move that code into a (stored) procedure and schedule a database JOB which would execute once a day (choose exact time) and drop that user if condition(s) are met. I wouldn't use a trigger at all for this purpose.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •