There is no difference for a single SQL statement. The '/' character on its own line tells SQL*Plus to execute the command in the buffer. You can use the semicolon at the end of most SQL statements as a shorthand for the '/'. If you want to execute a PL/SQL block or to execute a handful of SQL statements like CREATE TYPE
, however, you need to use the '/'
SQL> begin
2 null;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> create type foo as object (
2 col1 number,
3 col2 number );
4 /
Type created.
Different tools may have slightly different conventions for how you execute multiple SQL and PL/SQL statements in a script so be aware that this is SQL*Plus specific.
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.
Best Answer
The easiest way to think of it is:
DBA_ / USER_ / ALL_
views are built on the data dictionary - they're not available if the database is not mounted and opened.V$
views tend to run against the instance, and therefore may be available if the database is not mounted, or is not mounted and opened, depending on the nature of the view.Using your example:
V$TABLESPACE
is a view onX$KCCTS
, which is an internal memory structure.DBA_TABLESPACES
is a view on the data dictionary tableSYS.TS$