About "alter session set container = orclpdb" in Oracle 19c-CodePudding

Greeting folks;

Just installed Oracle 19c on my laptop (Win10 64bit). Below is what I did:

Step1: Login SQL plus as SYS AS SYSDBA
Step2: Run the statement alter session set container = orclpdb;
Step3: Create a user, some tables, and insert some data into the tables.

Within the session, I can query the tables and see the results. But when I logged out and re-logged in, I don't see the tables anymore. I must run the statement alter session set container = orclpdb;, then I could see the tables.

I couldn't create a user without running the Alter session... statement. I got the below error:

SQL> CREATE USER tester IDENTIFIED BY tester;

CREATE USER tester IDENTIFIED BY tester
            *
ERROR at line 1:

ORA-65096: invalid common user or role name

I just want to see the tables without running the statement alter session set container = orclpdb;, what should I do?

Thank you!

CodePudding user response：

As your edited post, you are create a user (schema), not table.

When you create user in CDB not PDB, you have to create a common user (start with C##, can exists for both CDB and all PDBs).

So either you change user to C##tester, or you should switch to pdb orclpdb as your ALTER SESION statement to create a user only for that PDB.

And your problem is that you're not yet familier with new Oracle CDB, you should read more doc about that.

CodePudding user response：

Bach,

Thanks for the info. It's my fault, I really meant to create a user not a table.

Below is exactly what I want in the 2nd link:

UNDOCUMENTED If you really, really, really want to create a user that can log into ANY database (not just the root database, but an actual global user) but without the “C##” prefix, you can change an underscore parameter:

alter system set "_common_user_prefix" = '' scope=spfile;

If you do that, and reboot your database, you’ll be allowed to create a global user without the C## prefix.

grant dba to kaley identified by ThisIsMyPassword container = all;