I have about 25 databases and 1 "command center" database (CC). My goal is to keep CC informed of any changes made in other databases. The CC contains an application that keeps track of databases, users, their privileges, roles, objects, etc. Currently this task is done manually and is a big loophole. What I would like to do use database triggers to accomplish this. I want to use AFTER DDL statement and within that separate GRANT from REVOKE, CREATE from DROP, etc. How can I do that?
You can create such a trigger. It is not hard to do. But before you create a trigger, you'll need to create a database link to your CC database. On your database server, set up a TNS alias that points to your CC database. You should be able to sign on to the CC database as follows:
Then sign on to your database and create a database link to the CC database.
CREATE DATABASE LINK cc_link CONNECT TO user IDENTIFIED BY pass USING 'cc';
You'll be connecting to the CC database with the userid and password you specify. The USING clause is where you tell the database link which TNS alias to use. If the link is set up correctly, then the following will return a value:
SELECT sysdate FROM dual@cc_link;
Next you need to create a trigger that will insert information into a table in your CC database. The trigger can look similar to the following:
CREATE TRIGGER populate_cc AFTER DDL ON DATABASE BEGIN INSERT INTO change_table@cc_link VALUES ('DDL occurred'); END; /
There are many options on such a database trigger, so I would recommend reading the Oracle documentation so that you know what type of triggers to create to meet your requirements.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.