topic Re: Create database user in Oracle 18c XE via ArcGIS Pro in Data Management Questions

Create database user in Oracle 18c XE via ArcGIS Pro

Bud — Thu, 20 Apr 2023 15:33:54 GMT

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).
https://community.esri.com/t5/geodatabase-questions/qery-about-oracle-and-sde/m-p/1197738/highlight/true#M7804

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.

Re: Create database user in Oracle 18c XE via ArcGIS Pro

MarceloMarques — Thu, 20 Apr 2023 00:33:12 GMT

If you have a CDB and you are connected to the cdb$root container then you can only create common users in the cdb$root.

You need to use a PDB, then you will be able to create normal users, and the ArcGIS Pro "Create Data User" shall work.

Managing Security for Oracle Database Users

Re: Create database user in Oracle 18c XE via ArcGIS Pro

Bud — Thu, 20 Apr 2023 09:29:42 GMT

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?

Re: Create database user in Oracle 18c XE via ArcGIS Pro

MarceloMarques — Thu, 20 Apr 2023 15:31:15 GMT

ArcGIS Pro use for "Instance" the Oracle easy connect syntax "192.168.1.3:1521/XEPDB1"

You can test this via Oracle SQL Plus first, if able to connect with SQL Plus then ArcGIS Pro will be able as well.

Connect to Oracle from ArcGIS—ArcGIS Pro | Documentation

Re: Create database user in Oracle 18c XE via ArcGIS Pro

Bud — Fri, 21 Apr 2023 04:53:32 GMT

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:

Re: Create database user in Oracle 18c XE via ArcGIS Pro

Bud — Thu, 20 Apr 2023 15:39:48 GMT

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.