Its an interesting question, to be sure. Most people who are familiar with Oracle development wouldn't give it a thought but when you come down to it, its sometimes confusing to define the demarcation between SQL and PL/SQL.
By looking at the definition of the acronyms, you start to get an idea of what areas of functionality each covers:
SQL - Structured Query Language
PL/SQL - Procedural Language / Structured Query Language
The observant reader might notice how SQL shows up twice 8) That's because SQL is often embedded within PL/SQL - PL/SQL is a language that was made to provide a proprietary 4th generation language (4GL) that plays very well with database objects in Oracle.
Wikipedia has some pretty good material on both SQL and PL/SQL
The confusing part is where PL/SQL and SQL overlap a bit. SQL's purview includes data insert, query, update and delete, the so-called DML, or data manipulation language operations, but it also includes create, alter, rename, drop which are DDL or data definition language operations. Its here where some might get confused. The operation to create a stored procedure, something written using PL/SQL, is actually SQL - you use SQL to create the database object that represents a block of PL/SQL.
Likewise, you can embed SQL code inside your PL/SQL. A FOR loop in PL/SQL can be based upon a SQL query, for example. Blows your mind a little, eh? You create a procedure using SQL that actually internally uses SQL to perform some action on records from the database.
Cool stuff if you ask me.
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 foobar's trigger logged the table creation that was run by bar himself, even though bar created a table in foo's schema.
Best Answer
Check the source of both views:
PUBLIC_DEPENDENCYqueries directly from the dictionary tabledependency$and nothing else.DBA_DEPENDENCIESalso queriesdependency$, but joins some other tables and views as well, for exampledisk_and_fixed_objects. This view does not contain all the objects fromdependency$, so this join eliminates some rows fromdependency$related to the missing objects. For example on my database:The source of
disk_and_fixed_objectsis:But the above 13 objects are not accounted for in the above underscore view or X$ tables, they are in another X$ table (
X$KQFDT):Here you can see the name of these objects in the
KQFDTNAMcolumn. Another way to find them is querying theV$FIXED_TABLEview:To sum up:
DBA_DEPENDENCIESdoes not contain some dependencies related to some fixed objects. You can also check which objects are related to these "missing" dependencies, e.g.: