Errors creating Oracle DDL objects from script: "invalid common user or role name", "file already part of database" etc

Question

I have a purpose: create some small model in Oracle consisting of two schemas.

Every schema will contain 2-3 tables and 2-3 packages for functions. And I tried to write a script of creation required DDL objects, using DBForge.

And here I have some big troubles when I've restarted DBForge, deleted existing connection, and made a new one.

Now, when I try to create new user, it shows error "Invalid common user or role name":

What is this? When I hadn't delete a connection before, this script had been executed with no errors.

When I try to create a tablespace, it shows errors: cannot add file 'my_datafile_name' - file is already part of database.

Maybe I do some wrong in re-creation of tablespace? I tried to use an instruction "ALTER DATABASE DATAFILE xxx OFFLINE DROP", but it doesn't work.

I understand not very good, how to recreate right 1) tablespace with datafiles 2) user (schema) 3) packages.

If someone has a small working example, it would be great. But if not, I ask to tell where I am wrong at least.

I think i made big mistakes in code, but can't find.

I've just started to learn Oracle, in MS SQL it seems to be much simplier. Big thanks.

ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;

ALTER SESSION SET CURRENT_SCHEMA = system;

alter database datafile 'tbspc_01.dat' offline drop;

CREATE BIGFILE TABLESPACE tbspc_01
  DATAFILE 'tbspc_01.dat'
    SIZE 20M
    REUSE
    AUTOEXTEND ON;

CREATE USER gateway
  IDENTIFIED BY pass4gateway
  DEFAULT TABLESPACE tbspc_01
  QUOTA UNLIMITED ON tbspc_01;

CREATE BIGFILE TABLESPACE tbspc_02
  DATAFILE 'tbspc_02.dat'
    SIZE 20M
    REUSE
    AUTOEXTEND ON;

CREATE USER domain
  IDENTIFIED BY pass4domain
  DEFAULT TABLESPACE tbspc_02
  QUOTA UNLIMITED ON tbspc_02;

Answer 1

Run your commands from the pluggable container, not the root container. (Or you can re-install the entire database and choose the "traditional architecture" instead of the more confusing multi-tenant option.) Unless you're a DBA, all you care about is the pluggable container.

Unfortunately, when you Google the errors you received, most "solutions" for these problems recommend running the undocumented command ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE; That command solves the immediate error, sort of, but ignores the fact that you're connected to the wrong database.

Answer 2

An error really appeared because I've not chosen a right container and user before execution DDL commands of creation my database.

Example of creating pluggable database from beginning:

Flush existing DB:

/*
SELECT
  name
  , open_mode
FROM v$pdbs;

-- PDB$SEED READ ONLY
-- DBNEW    READ WRITE    <<---
*/

ALTER SESSION SET CURRENT_SCHEMA = sys;

BEGIN
  EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE DBNEW OPEN';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP USER gateway CASCADE';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP USER domain CASCADE';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLESPACE tbspc_01 INCLUDING CONTENTS AND DATAFILES';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLESPACE tbspc_02 INCLUDING CONTENTS AND DATAFILES';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE DBNEW CLOSE';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

ALTER SESSION SET CONTAINER = CDB$ROOT;

BEGIN
  EXECUTE IMMEDIATE 'DROP PLUGGABLE DATABASE DBNEW INCLUDING DATAFILES';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

Then create users:

/*
SELECT
  name
  , open_mode
FROM v$pdbs;

-- PDB$SEED READ ONLY
-- DBNEW    READ WRITE    <<---
*/

ALTER SESSION SET CURRENT_SCHEMA = sys;
ALTER SESSION SET CONTAINER = CDB$ROOT;

CREATE PLUGGABLE DATABASE DBNEW ADMIN USER admin IDENTIFIED BY password FILE_NAME_CONVERT=('e:\Oracle\db\oradata\orcl\pdbseed','e:\Oracle\db\oradata\orcl\dbnew');

ALTER PLUGGABLE DATABASE DBNEW OPEN;    -- Меняет режим доступа к данным подключаемой базы данных на режим чтения и записи. Статус у нашей подключаемой БД DBNEW изменится с MOUNTED на READ WRITE.

ALTER SESSION SET CONTAINER = DBNEW;     -- Меняет текущий сеанс на нужную подключаемую БД.



CREATE BIGFILE TABLESPACE tbspc_01
  DATAFILE 'tbs_01.dat'
    SIZE 20M
    REUSE
    AUTOEXTEND ON;

CREATE BIGFILE TABLESPACE tbspc_02
  DATAFILE 'tbs_02.dat'
    SIZE 20M
    REUSE
    AUTOEXTEND ON;



CREATE USER gateway
  IDENTIFIED BY pass4gateway
  DEFAULT TABLESPACE tbspc_01
  QUOTA UNLIMITED ON tbspc_01;

CREATE USER domain
  IDENTIFIED BY pass4domain
  DEFAULT TABLESPACE tbspc_02
  QUOTA UNLIMITED ON tbspc_02;



/*
SELECT * FROM DBA_USERS
ORDER BY created DESC;
*/

Then do anything we need in different user schemas:

BEGIN
  EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE DBNEW OPEN';    -- Меняет режим доступа к данным подключаемой базы данных на режим чтения и записи. Статус у нашей подключаемой БД DBNEW изменится с MOUNTED на READ WRITE.
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

ALTER SESSION SET CONTAINER = DBNEW;     -- Меняет текущий сеанс на нужную подключаемую БД.

ALTER SESSION SET CURRENT_SCHEMA = domain;

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE regions';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

CREATE TABLE regions (
    OBJECT_ID NUMBER GENERATED BY DEFAULT AS IDENTITY,
    SOURCE_ID VARCHAR2(100) NOT NULL,
    GUID RAW(16) NOT NULL,
    NAME VARCHAR2(100) NOT NULL,
    TYPE INTEGER NULL,
    --ACTION CHAR NOT NULL,
    PRIMARY KEY(OBJECT_ID)
);