Are you
prepared for the release of Oracle Database
18c ... the first autonomous database? We are here at the Library. It is
time for DBAs to stop fighting robots with their fingers and losing ...
time to start using our intelligence and winning.
dbms_ddl_internal.check_trigger_firing_property(
trig_owner IN VARCHAR2,
trig_name IN VARCHAR2,
canon_owner OUT VARCHAR2,
canon_oname OUT VARCHAR2,
p_property IN OUT NUMBER,
unsupported_trig OUT BOOLEAN);
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE TRIGGER t_trig
BEFORE UPDATE
ON airplanes
BEGIN
NULL;
END t_trig;
/
conn sys@pdbdev as sysdba
set serveroutput on
DECLARE
co VARCHAR2(30);
cn VARCHAR2(30);
pp NUMBER(10) := 0;
ut BOOLEAN;
BEGIN
dbms_ddl_internal.check_trigger_firing_property('UWCLASS', 'T_TRIG', co, cn, pp, ut);
Returns true of the schema owner has the name privilege
dbms_ddl_internal.has_alter_any_trigger_priv(
p_user IN VARCHAR2,
p_trig_property IN NUMBER)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba
set serveroutput on
BEGIN
IF dbms_ddl_internal.has_alter_any_trigger_priv('UWCLASS', 1) THEN
dbms_output.put_line('Granted');
ELSE
dbms_output.put_line('Not Granted');
END IF;
END;
/
GRANT alter any trigger TO uwclass;
BEGIN
IF dbms_ddl_internal.has_alter_any_trigger_priv('UWCLASS', 1) THEN
dbms_output.put_line('Granted');
ELSE
dbms_output.put_line('Not Granted');
END IF;
END;
/
Returns true if the schema owner has export and/or import full database privileges
dbms_ddl_internal.has_exp_imp_priv(
p_uid IN NUMBER,
p_privs_to_check IN VARCHAR2)
RETURN BOOLEAN;
-- the following does not work ... and no working combination has been discovered. Solutions will be gratefully accepted.
conn sys@pdbdev as sysdba
SELECT user_id
FROM dba_users
WHERE username = 'UWCLASS';
set serveroutput on
BEGIN
IF dbms_ddl_internal.has_exp_imp_priv(82,'EXPORT FULL DATABASE') THEN
dbms_output.put_line('Granted');
ELSE
dbms_output.put_line('Not Granted');
END IF;
END;
/
Returns true of the trigger is a DDL Event Trigger
dbms_ddl_internal.is_ddl_trigger(sys_evts IN NUMBER) RETURN BOOLEAN;
conn sys@pdbdev as sysdba
desc trigger$
SELECT sys_evts, type#, COUNT(*)
FROM trigger$
GROUP BY sys_evts, type#
ORDER BY 1;
-- from sql.bsq
type# number not null, /* trigger type: */
/* 0 = BEFORE TABLE, 1 = BEFORE ROW, 2 = AFTER TABLE, 3 = AFTER ROW */
/* 4 = INSTEAD OF TRIGGER */
-- also see dba_triggers in catprc.sql
set serveroutput on
BEGIN
IF dbms_ddl_internal.is_ddl_trigger(31) THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/
BEGIN
IF dbms_ddl_internal.is_ddl_trigger(32) THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/
