Create database user in Oracle 18c XE via ArcGIS Pro
I have the following installed on my home computer:
- Oracle 18c Express Edition (it's not a geodatabase since I don't have an ArcGIS Server/Enterprise license). Purpose: personal/learning.
- ArcGIS Pro 3.1.1 Personal Use
In ArcGIS Pro, I can connect to the Oracle database's SYS user using a database connection in Catalog:
Now, I want to create a database user called TEST_USER so that I can create Oracle tables/FCs using ArcGIS Pro/Catalog (I'll import the data from a mobile or file GDB for testing purposes).
I tried using the Create Database User geoprocessing tool, but I get an error:
Failed to create the new user in the database. [Success] ERROR: Failed to create new Oracle user TEST_USER (-51).
Error: Underlying DBMS error (-51).
Extended error code: (65096)
ORA-65096: invalid common user or role name
Error creating database user.
Failed to execute (CreateDatabaseUser).
Question:
How can I create a database user in an Oracle XE database using ArcGIS Pro?
I found a blurb from @MarceloMarques :
99.9% of the time the error ORA-65096: invalid common user or role name means you are logged into the CDB when you should be logged into a PDB. For example, if you used the default 19c installation settings, you should login to ORCLPDB (the PDB) instead of ORCL (the CDB).
But I'm not sure how to log into the PDB via my ArcGIS Pro database connection, if that's the problem.
Any help would be appreciated.
Regarding:
You need to use a PDB, then you will be able to create normal users, and the ArcGIS Pro "Create Data User" shall work.
How can I connect to a PDB in a ArcGIS Pro database connection? (such as XEPDB1, the default PDB)
I was able to connect to the XEPDB1 PDB in SQL Developer and create a new user:
But I don't know how to connect to XEPDB1 in ArcGIS Pro.
This is my existing SYS connection, which works as expected. I used Instance=XE (XE is my database name/SID).
But as we mentioned, that connects to the CDB, not the PDB.
So I tried creating a similar connection, but this time using Instance=XEPDB1. But that didn't work.
Failed to connect tot he specified DBMS instance.
A database connection exception has occurred. Underlying DBMS error[ORA-12152:could not resolve the connect identifier specified No extended error.].
I suspect I got that error because XEPDB1 is a Service Name, not a SID. But I'm just guessing.
How can I connect to the XEPDB1 PDB in a ArcGIS Pro database connection?
Related:
Thanks, just saw this message now.
I tried 192.168.1.3:1521/XEPDB1 as you suggested, and it worked in an ArcGIS Pro database connection:
Although I made that connection after adding XEPDB1 to TNS.
So I'm not sure if XEPDB1 in TNS is needed or not for the easy connect syntax.
I figured it out (before I saw MarceloMarques' easy connect syntax solution above).
My TNS wasn't set up correctly. I added a TNS entry for XEPDB1. (Previously, it only had an entry for XE.)
XEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = XEPDB1)
)
)Now, I'm able to connect to XEPDB1 in an ArcGIS Pro database connection (reminder: restart ArcGIS Pro after editing tnsnames.ora):
Likewise, I'm now able to create a new user using the Create Database User geoprocessing tool, because I'm connected to the XEPDB1 PDB:
And I can connect to the new user:
And create & view tables/FCs via Catalog:
Complete.
Notes:
1. If I understand correctly: I didn't need that TNS entry when connecting to XEPDB1 via SQL Developer because SQL Developer using a different connection mechanism -- JDBC, not the database client (ArcGIS Pro uses the database client).
2. Side note: In SQL Developer, I think I can connect to either the SID (XE) or the Service Name (also XE).
3. Useful websites:
