I'm almost new user to databases, so I think I'm doing something wrong.
- I have the database created.
- I created 3 users, 1 it's an Admin, 2 are just users.
- I created 2 roles, 1 for the Admin and 2 for the other users.
I'm learning that the schemas in
Oracle are different from
SQL Server. In SQL Server I created tables assigned to a schema, for example:
CREATE TABLE schema1.table1 and then I was be able to see them all with users asigned to that DB; with Oracle, I think a schema is the sum of the sequence, synonyms, etc that a user has. (If not, please correct me)
I want to achieve that in Oracle, so I created the users/schemas; after that I got connected with
user1 and created the tables with that user; later, I got connected with
user2 and created the tables related to the user.
Now, when I'm trying to create the alter relating
user1 tables and
user2 tables with admin, it says I don't have enough privileges.
ALTER I'm trying to do:
ALTER TABLE user1.PhoneTable ADD (CONSTRAINT C_001 FOREIGN KEY (Status) REFERENCES user2.ClientTable (Status) ON DELETE SET NULL);
I created those users because if I wanna see all the tables related to the Phone(user1), I got connected to that user and that should be all, I just wanna have the DB with some order.
GRANT CREATE SESSION, UNLIMITED TABLESPACE, CREATE TABLE, DROP ANY TABLE, CREATE CLUSTER, CREATE SYNONYM, CREATE PUBLIC SYNONYM, CREATE VIEW, CREATE SEQUENCE, CREATE DATABASE LINK, CREATE PROCEDURE, CREATE TRIGGER, CREATE MATERIALIZED VIEW, CREATE ANY DIRECTORY, DROP ANY DIRECTORY, CREATE TYPE, CREATE LIBRARY, CREATE OPERATOR, CREATE INDEXTYPE, CREATE DIMENSION, CREATE ANY CONTEXT, SELECT ANY DICTIONARY, CREATE JOB, ALTER ANY TABLE, TO myAdmin;
GRANT CREATE session, CREATE table, CREATE view CREATE procedure, CREATE synonym, ALTER ANY table, ALTER view, ALTER procedure, ALTER synonym, DROP table, DROP view, DROP procedure, DROP synonym TO myUsers;