Oracle Gurus,
I know we can't create triggers on sys owned objects, but I have this requirement that I need to capture info of anyone who is creating database links in the PDBs and want this trigger at the CDB level so that I don't have to redeploy everytime a new PDB is spun up.
My current trigger serves just the purpose, but it has to be deployed on all PDBs. What modification do I need to deploy this at the CDB level?
CREATE OR REPLACE TRIGGER sys.dba_dblinks_created
BEFORE CREATE
ON DATABASE
DECLARE
vobjtype varchar2(19);
v_db_name varchar2(30);
v_os_user varchar2(30);
v_date varchar2(30);
BEGIN
SELECT ora_dict_obj_type into vobjtype
FROM DUAL;
select sys_context('USERENV','SESSION_USER') into v_os_user from dual;
select sys_context('USERENV','DB_NAME') into v_db_name from dual;
select sysdate into v_date from dual;
if vobjtype = 'DATABASE LINK' then
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''mailrelay.xyz.com''';
UTL_MAIL.send(sender => 'noreply@xyz.co',
recipients => 'abby007@xyz.co',
subject => 'A DBLink was created on ' ||v_db_name,
message => 'Datbase Link was created by '||v_os_user || ' on ' || v_date,
mime_type => 'text; charset=us-ascii');
END;
end if;
END dblink_created_trig;
aBBy007