Where is Oracle home user being stored

You can use DROP TABLE <schema_owner>.<table_name> CASCADE CONSTRAINTS to drop the referential integrity too. Notice that there must be a reason to have a referential constraint in this table. Check it twice before drop. (I recommend to read documentation first http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9003.htm )

SYS is a special user, has some restrictions (like objects owned by SYS cannot be exported), but can alter any object in the database, including droping or alter some DB engine objects. That's why (and many other reasons) you should NEVER create user objects in SYS schema. Objects created in SYS schema has a different treatment, and that's why you couldn't drop the column (or constraint, but I've never tried this).

I can say that the source of the problem was not the DROP TABLE syntax. The problem is that your objects where created in SYS schema ;). Oracle was designed to create and manipulate user objects within user's schemas. It's like if you have a hammer and try to use it grabbing it by the head. It's not effective , It wont work as expected and you could brake it.

The good practice is to create USERS, and then create objects within user's schema. You can create a new user just with:

CREATE USER <username> IDENTIFIED BY <password> ;

This will create a User (and a Schema), but it wont have any privileges. Logged in as SYS you can create and manipulate objects in this (and any) user schema, but this user will never be able to connect nor create objects. This are system privileges that can be easily granted with the roles CONNECT (just grant the CREATE SESSION privilege) and RESOURCE (grant a bunch of privilege to create several DB objects in his own schema):

GRANT CONNECT TO <username>;
GRANT RESOURCE TO <username>;

With this, you'll have a user with the DEFAULT profile (search in documentation for User Profiles) which can log in and can create user objects in his schema. A typical oracle db user.

These are all Oracle Administration basics. I've recommend to read the 2 Day DBA, DB Concepts, and DB Administrator's Guide documentation. All available at http://www.oracle.com/pls/db112/homepage (or, easy to remember, http://tahiti.oracle.com )

Regards.

-- edit- The OC4J_DBConsole_SID is related with EM configuration. I wouldn't mess around with this until you dominate Oracle's concepts. A GUI won't teach you the basis and it will progressively make you dependent of it.

If you want to create a user that can admin any of the pluggable databases, prefix the username with C##.

For example:

create user c##name identified by password;