I just installed the latest 21c version of XE, and having created the system user, I connected to this via SQL developer, using XE as the SID. All fine so far.<\/p>\n
When I then went to create a new schema via the CREATE USER command, I received ORA-65096: invalid common user or role name.\u00a0<\/em>Upon researching this I found that Oracle now uses a hierarchy of pluggable databases or PDBs inside a container database or CDB. Schemas aka users can then sit inside a PDB as normal. Inside the CDB, where I was logged in, the rules are different and common users are required which have their own rules and naming conventions.<\/p>\n I just wanted a simple local develpment solution for JPA use, so did not especially want to learn all the ins and outs of this. I found that an XE installation creates a default PDB called XEPDB1, and managed to create an SQL developer connection to this using the already created system account. I tried using XEPDB1 as the SID rather than XE, but this failed as invalid. However when I tried XEPDB1 as a service name rather than a SID it all worked fine. I did not need to make any other changes to the connection settings – user (system), password, and port (1521) were identical.<\/p>\n Having then connected via this new connection, I was successfully able to create a new schema in the default PDB. As expected, to connect to this new schema I again had to use the service name of XEPDB1 and I was able to connect, once I had granted the CREATE SESSION privilege to the new user after creating it to allow me to log on. I could then proceed to grant the necessary privileges to the user and create\/populate tables etc. Initially, in addition to granting CREATE SESSION, I just simply granted UNLIMITED TABLESPACE to allow the schema user to allow tables to be populated. With this I was successfully able to perform inserts, upates, deletes, and selects on all the tables present.<\/p>\n","protected":false},"excerpt":{"rendered":" I just installed the latest 21c version of XE, and having created the system user, I connected to this via SQL developer, using XE as the SID. All fine so far. When I then went to create a new schema via the CREATE USER command, I received ORA-65096: invalid common user or role name.\u00a0Upon researching […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[8,11,138],"tags":[40,16,15],"_links":{"self":[{"href":"https:\/\/salientsoft.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/2711"}],"collection":[{"href":"https:\/\/salientsoft.co.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/salientsoft.co.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/salientsoft.co.uk\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/salientsoft.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2711"}],"version-history":[{"count":4,"href":"https:\/\/salientsoft.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/2711\/revisions"}],"predecessor-version":[{"id":2715,"href":"https:\/\/salientsoft.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/2711\/revisions\/2715"}],"wp:attachment":[{"href":"https:\/\/salientsoft.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2711"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/salientsoft.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2711"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/salientsoft.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2711"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}