Oracle11g - Create Spatial type problem

2093
8
02-19-2014 05:00 AM
by
Occasional Contributor
I installed Oracle11g and ran the Create Enterprise Geodatabase tool. It succeeded, but I now need to run the Create Spatial Type tool.

It says I need to be logged in as sys, but when I try to login as sys it tells me I should be logged in as SYSDBA or SYSOPER.

So I created a user and assigned it SYSDBA privileges, but when I got in as this user the tool tells me that I need to be logged in as sys!

I can't add users in ArcCatalog either, it gives the same errors.

What am I missing here?
Reply
0 Kudos
8 Replies
Esri Esteemed Contributor
Why do you need to create ST_GEOMETRY a second time?  (Enterprise geodatabases
include ST_GEOMETRY support.)

When ArcGIS tells you it needs SYS, it needs SYS, not any SYSDBA user.  Certain
privileges can only be granted by SYS.

- V
Reply
0 Kudos
by
Occasional Contributor
Why do you need to create ST_GEOMETRY a second time?  (Enterprise geodatabases
include ST_GEOMETRY support.)

When ArcGIS tells you it needs SYS, it needs SYS, not any SYSDBA user.  Certain
privileges can only be granted by SYS.

- V


Well when I created the geodatabase it came back with a warning about the ST_GEOMETRY not being used. I never moved the DLL before I ran the tool. I was suppose to, right? How can I check it the ST_GEOMETRY was created?

Also, then why when I try to login as SYS it says that I can't?

[ATTACH=CONFIG]31576[/ATTACH]
Reply
0 Kudos
by
Occasional Contributor
I found a way that works, but is this really what I'm suppose to do? Quoted from another forum:

If you want to bypass the SYSDBA or SYSOPER checking step then there is a way .

1. Log in to sqlplus as sys

i.e use the following command in sql prompt
conn / as sysdba

2. Check for parameter named O7_DICTIONARY_ACCESSIBILITY
i.e use the following command in sql prompt
show parameter O7_DICTIONARY_ACCESSIBILITY

It will give you result as shown below
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE

3. Change this parameter using
alter system set O7_dictionary_accessibility=TRUE scope=spfile;

4. It is a static parameter so need to bounce the database . Restart your database .
5. Now connect as SYS without SYSDBA privilege.
conn sys/<your SYS password>

it will be successful .

Note: Setting O7_DICTIONARY_ACCESSIBILITY to TRUE is dangerous
This behavior is because sys objects can be accessed only by user who is logged in as sysdba. EVEN SYS cannot access its own objects unless during logon he authenticate himself as sysdba. After we set O7_DICTIONARY_ACCESSIBILITY=True, this check is no more there and even guys with 'select any table' priv can select tables like user$, obj$, seq$ etc.
Reply
0 Kudos
Esri Esteemed Contributor
You do NOT need to connect through ArcGIS as SYS.  SYS is only used by the Python
tool to create geodatabases.

If you did not copy the DLL, you need to do so, then follow the instructions for
installing the ST_GEOMETRY DLL (search on "CREATE or REPLACE LIBRARY").

- V
Reply
0 Kudos
by
Occasional Contributor
You do NOT need to connect through ArcGIS as SYS.  SYS is only used by the Python
tool to create geodatabases.

If you did not copy the DLL, you need to do so, then follow the instructions for
installing the ST_GEOMETRY DLL (search on "CREATE or REPLACE LIBRARY").

- V


Then why in ArcCatalog when I right click on my Oracle database connection and go to Administration --> Add user, it tells me I must be logged in as SYS?

[ATTACH=CONFIG]31579[/ATTACH]
Reply
0 Kudos
Esri Esteemed Contributor
That's not an ArcGIS connection for mapping, it's an admin login associated with a
management script.  I always manage my database from database tools.

- V
Reply
0 Kudos
by
Occasional Contributor
That's not an ArcGIS connection for mapping, it's an admin login associated with a
management script.  I always manage my database from database tools.

- V


I don't completely understand. I realize that creating the user and setting privileges can be done using SQL Plus for example through SQL statements, but isn't the point of the Add User tool to do the same?

With SQL Server I just login as SA and add the users. And with PostGres I just login as postgres and add the users.

I guess Oracle's default security doesn't allow this to work correctly.
Reply
0 Kudos
by
Occasional Contributor
Also, on a Windows machine, what location should I have moved the st_shapelib.dll to in order for the Create Enterprise Geodatabase tool to find it and work correctly?
Reply
0 Kudos