samar mishra, April 24, 2007 - 3:15 pm UTC
thanks thomas ,
for your quick reply
but i got some problem......
1>you have create it on sql ,Would i make it on pl/sql , i have tried it on pl/sql the error details are
2> when i run it in after, editing the code it says that , insufficient privillege
as
SQL> @ shut_trig
create or replace trigger shut_trig before shutdown on database
*
ERROR at line 1:
ORA-01031: insufficient privileges
so i compile it by using user as sys . it compiled with compilation error.
as
SQL> connect sys
Enter password: ******
Connected.
SQL> @ shut_trig
Warning: Trigger created with compilation errors.
SQL> set termout on
SQL> shutdown
ORA-01031: insufficient privileges
3>why we use @connect"/as sysdba" and set termout off stands for.
4> when i shutdown under sys user it says insuffiecent privillege.
so i could not make it in pl/sql
with warm regards
from samar
April 24, 2007 - 3:42 pm UTC
@connect is just running a script, to update my sqlplus prompt.
just use connect / as sysdba
you do not have the privileges needed to create the trigger, get them.
thank' s thomas , i learned so many things but i will glad if you make me convinient.
samar mishra, April 25, 2007 - 1:53 am UTC
fisrt- could i make it on pl/sql?
second -if i make it i tried it and the result is it did not insert into the table .(named as shut_trig_det )
i am giving the total details i had tried so far.
------
i had tried it pl/sql by creating the below shut_trig trigger in editer.
create or replace trigger shut_trig before shutdown on database
begin
insert into shut_trig_det (user_id,log_date,action) values (user,sysdate,'shutting down');
end;
/
i have an table shut_trig_det as
SQL> desc SHUT_TRIG_DET;
Name Null? Type
------------ -------- -----------------
USER_ID VARCHAR2(15)
LOG_DATE DATE
ACTION VARCHAR2(15)
and then tried in sql as below
SQL> select * from SHUT_TRIG_DET; -- in user scott
no rows selected
SQL> connect /as sysdba
Connected.
SQL> @ shut_trig
Warning: Trigger created with compilation errors.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 128665628 bytes
Fixed Size 75804 bytes
Variable Size 57839616 bytes
Database Buffers 70672384 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SQL> select * from SHUT_TRIG_DET;
select * from SHUT_TRIG_DET
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect
Enter user-name: scott
Enter password: *****
Connected.
SQL> select * from SHUT_TRIG_DET; -- it was not inserted
no rows selected
that means it does not inserted pl/sql?
thanking you thomas
from
samar
April 25, 2007 - 9:58 am UTC
first - that makes no sense to me, triggers are coded in plsql, so it is plsql already...
second - your trigger is invalid, you cannot insert into that table, do the right grants and qualify the table name with a schema.
of course nothing was inserted, your trigger never even compiled. scott owns the table, if SYS owns the trigger, then you need to insert into scott.tablename.
shutdown Trigger
Sagar, April 25, 2007 - 4:00 am UTC
The trigger has compilation error.Need to use scott.shut_trig_det instead of shut_trig_det.
But why we have to use autonomous transaction ? Can't that trigger work sucessfully without autonomous transaction.
April 25, 2007 - 9:59 am UTC
I was demonstrating the concept was sound, I wanted the record to be committed in the trigger itself - regardless of anything else that happened. Hence I used an autonomous transaction.
Andy, April 25, 2007 - 9:35 am UTC
Samar,
The trigger that you created in SYS needs to have access to the table that you are writing to - you seem to have created that table in SCOTT? Create it in SYS and recompile your trigger.
You need to use an autonomous transaction because the session firing the trigger will not commit - the database is shutting down.
Andy, April 26, 2007 - 4:26 am UTC
Tom,
From your:
Followup:
I was demonstrating the concept was sound, I wanted the record to be committed in the trigger itself - regardless of anything else that happened. Hence I used an autonomous transaction.
I see that my thought that the autonomous transaction was required because the database was shutting down is incorrect. Is this trigger the only case in which a shutdown immediate/abort will commit this transaction when other transactions would be rolled back?
April 26, 2007 - 12:04 pm UTC
it was just a matter of transactional logic.
My trigger is by itself a standalone transaction, it should commit, it is just stating the intent. If it runs, it should commit - right then, right there.
you don't "need it", it would have committed after firing.
shutdown trigger not working
Kandy Train, October 09, 2007 - 2:49 pm UTC
I wanted to create a database shutdown trigger to disable several triggers and run a procedure, before the shutdown.
I created the trigger as sys(Is this a must?? or can I create this as scott??)
--Login as sys
CREATE OR REPLACE TRIGGER scott_shutdown_integ_trig
BEFORE SHUTDOWN ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR cGetScheduleIds IS
SELECT schedule_id
FROM scott.batch_schedule
WHERE schedule_name IN
('scott Process Err Mess', 'scott Get All Work Orders');
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_insert_service_run DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_insert_work_order_note DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_insert_customer_info DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_insert_finished_wonote DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_insert_service_site DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_tax_rate DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_service_site DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_cust_hier_upd DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_cust_hier_ins DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_cust_credit_upd DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_delete_service_run DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_delete_cust_hier_upd DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_customer_info DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER scott.scott_update_cust_credit_ins DISABLE';
FOR rec_ IN cGetScheduleIds LOOP
scott.Batch_Schedule_API.Deactivate__(rec_.schedule_id);
END LOOP;
COMMIT;
END;
I was able to create the trigger successfully. But it doesn't really disable the triggers or do anything inside the procedure
when I bounce the database back.
we are on Oracle 10gR2 and Windows server 2003, if that matters.
I appreciate your help to see what's wrong here.
THanks,
Kandy Train
Trancate table before shutdown
Suraj Sharma, January 16, 2015 - 12:55 pm UTC
Hi,
We have a requirement to truncate few tables before shutting down the database. I created trigger as under, but this is not working:
CREATE OR REPLACE TRIGGER TETS_PURGE BEFORE SHUTDOWN ON DATABASE
begin
execute immediate 'TRUNCATE TABLE TEST_TABLE';
END;
/
Please suggest