Oracle – Difference Between Database, User, and Schema

A trigger on schema (with no schema specified) doesn't fire for all schemas. It only fires when the triggering action is run by the user who owns that trigger.

So they are quite different, and are not interchangeable.

Here's an example that sets up a logging table, and three create triggers: on database, and on schema for users foo and bar:

Connected. -- as mat
SQL> create table mat.log (dt timestamp, who varchar(3),
  2      cur varchar(10), own varchar(42), obj varchar(42));
Table created.

SQL> create or replace trigger db_trig
  2  after create on database
  3  begin
  4    insert into mat.log values (systimestamp, 'db', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /
Trigger created.

SQL> connect foo/foo
Connected.
SQL> create or replace trigger foo_trig
  2  after create on schema
  3  begin
  4    insert into mat.log values (systimestamp, 'foo', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /
Trigger created.

SQL> connect bar/bar
Connected.
SQL> create or replace trigger bar_trig
  2  after create on schema
  3  begin
  4    insert into mat.log values (systimestamp, 'bar', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /

Trigger created.

Now let's create a table in foo's schema, as foo:

SQL> connect foo/foo
Connected.
SQL> create table foo.foo_stuff (id number);
Table created.

And let's create a table in foo's schema, as bar:

SQL> connect bar/bar
Connected.
SQL> create table foo.bar_stuff (id number);
Table created.

Here's what we've logged:

SQL> select * from mat.log order by dt;

DT                             WHO CUR        OWN        OBJ
------------------------------ --- ---------- ---------- ---------------
25-NOV-12 07.52.03.797794 PM   db  FOO        FOO    FOO_TRIG
25-NOV-12 07.52.03.828670 PM   db  BAR        BAR    BAR_TRIG
25-NOV-12 07.52.03.865334 PM   foo FOO        FOO    FOO_STUFF
25-NOV-12 07.52.03.865579 PM   db  FOO        FOO    FOO_STUFF
25-NOV-12 07.52.03.894672 PM   bar BAR        FOO    BAR_STUFF
25-NOV-12 07.52.03.894911 PM   db  BAR        FOO    BAR_STUFF

6 rows selected.

So:

• the two create trigger statements were logged by the "global" after create on database trigger. That trigger also logged everything else.
• foo's after create on schema logged the table creation that was done by foo
• bar's trigger logged the table creation that was run by bar himself, even though bar created a table in foo's schema.

A logical schema won't exist in your database. A logical schema is a design-centric database structure built to meet your business requirements. It is a model that exists on a white board or in a diagraming tool. It is like the architect's drawings of your database.

A physical model is what is actually implemented in your DBMS.

The two can be different for a variety of reasons and in several ways:

• Your logical model should be properly normalized, but your physical model may have denormalization which you've added deliberately and for all the right reasons.

• Your physical model may have different naming conventions. Some people use plain English (or the language of choice) for their logical models and impose a more "system-ish" naming convention in their physical model.

• Your logical model may have many-to-many relationships. Physical models implement m:n using intersection tables.

• Your logical model may only use natural or business keys. Physical models may also add surrogate keys.

You should have a logical model because it lets you think about your database design without having to get bogged down in physical constraints. You need to have the physical model because that will be where your data eventually lives.