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

oracle

Solution

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.

Problem

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" ```

Original source