Using your schema in Oracle

You follow an example of how to create an external table in Oracle using your own schema.

In Oracle, dividing the tables into different namespaces/schemas is achieved through different users. The CREATE SCHEMA statement exists in Oracle, but has different semantics from those defined by SQL Standard and those adopted in other DBMS.

To create "local" users in Oracle you need to be connected to the Pluggable Database (PDB), not to the Container Database (CDB). The following example was tested in Oracle XE edition, using only PDB -- XEPDB1.

Using Oracle XE edition, connect to the PDB.

ALTER SESSION SET CONTAINER = XEPDB1;

Create the bob schema/user and give appropriate connections to be able to connect to the database.

CREATE USER bob IDENTIFIED BY bobpass;
ALTER USER bob QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO bob;

CREATE USER bob IDENTIFIED BY bobpass;
ALTER USER bob QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO bob;

Create the alice schema/user, give appropriate connections to be able to connect to the database, and create an external table.

CREATE USER alice IDENTIFIED BY alicepass;
ALTER USER alice QUOTA UNLIMITED ON users;

GRANT CREATE SESSION TO alice;

CREATE TABLE alice.country
(
    id   int,
    name varchar(20)
);

insert into alice.country
values (4, 'Italy');
insert into alice.country
values (5, 'Greece');
insert into alice.country
values (6, 'China');
insert into alice.country
values (7, 'Japan');

Grant the SELECT ANY privilege to client users.
Without the SELECT ANY privilege, a user cannot see the tables/views of another user. When a user connects to the database using a specific user and schema it is not possible to refer to tables in another user/schema -- namespace.
```
GRANT SELECT ANY TABLE TO bob;
GRANT SELECT ANY TABLE TO alice;
```
Allow the users to perform inserts on any table/view in the database, not only those present on their own schema.
```
GRANT INSERT ANY TABLE TO bob;
GRANT INSERT ANY TABLE TO alice;
```

Using your schema in Oracle

We want your opinion

How can we improve this page?