Create database user in Oracle 18c XE via ArcGIS Pro

1859
5
04-19-2023 05:10 PM
Labels (1)
Bud
by
Notable Contributor

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:

Bud_0-1681948230226.png

Bud_1-1681948597786.png

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:

Bud_2-1681948836473.png

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/... 

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.

0 Kudos
5 Replies
MarceloMarques
Esri Regular Contributor

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

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
Bud
by
Notable Contributor

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:

Bud_0-1681958314584.png

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).

Bud_3-1681958530225.png

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.

Bud_2-1681958463928.png

How can I connect to the XEPDB1 PDB in a ArcGIS Pro database connection?


Related: 

0 Kudos
MarceloMarques
Esri Regular Contributor

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

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
0 Kudos
Bud
by
Notable Contributor

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:

Bud_0-1682005074048.png

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.

Bud
by
Notable Contributor

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):

Bud_3-1682004458126.png

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:

Bud_4-1682004565329.png

And I can connect to the new user:

Bud_1-1682008590723.png

And create & view tables/FCs via Catalog:

Bud_0-1682008579085.png

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).

Bud_1-1682004324163.png

Bud_2-1682004350900.png

3. Useful websites: