How do I create tables not "owned by sys" in Oracle?

Question

I tried to create a trigger:

create trigger afterupdate 
after insert on friends
for each row 


begin 
dbms_output.put_line('hello world');
end afterupdate;

However got the following error:

"cannot create triggers on objects owned by SYS"

Answer 1

Given the error, I am assuming that you are logging in to the database as SYS to create your tables and to write your code. You do not want to use the SYS schema for that-- you should never create objects in the SYS schema. You'll need to log in to the database as a different user. In general, if you are building a brand new application, you would create a new user to own all the objects for the new application.

For example, if you're building a Facebook clone and you want to use the USERS tablespace for your data

CREATE USER facebook_appid
  IDENTIFIED BY <<password>>
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP;

GRANT CREATE SESSION,
      CREATE TABLE,
      CREATE TRIGGER
   TO facebook_appid;

You would then connect to the database as facebook_appid using the password you specified.

sqlplus facebook_appid/<<password>>@<<TNS alias>>

Once you've done that, you can create the table and the trigger.