SQL: Create a new user/schema in Oracle

Use the following script to create a new user/schema XDUMMY. Run these scripts as the SYS user:

/* Script to create new user with username XDUMMY and password xdummy */

CREATE USER XDUMMY IDENTIFIED BY xdummy
DEFAULT TABLESPACE "TEMP"
TEMPORARY TABLESPACE "TEMP"
QUOTA 20M on TEMP;

/* Script to grant session and create privileges to new user */

GRANT create session TO XDUMMY;
GRANT create table TO XDUMMY;
GRANT create view TO XDUMMY;
GRANT create any trigger TO XDUMMY;
GRANT create any procedure TO XDUMMY;
GRANT create sequence TO XDUMMY;
GRANT create synonym TO XDUMMY;

/* Create new objects in schema */

// Connect to DB as as xdummy/ xdummy

create table dummy_tbl (...);

/* Create synonym so that you can query from other schemas */

create public synonym dummy_tbl for xdummy.dummy_tbl;

/* Query your objects from another schema. Example: APPS*/

// Connect to DB as as apps/<apps_pwd>

select * from dummy_tbl;

Please share your feedback below. Hope this is helpful!

CaptiveCode