Stuck with Unable to create logfile system tables error

[BharathiChandran]

We have a set of users with read-only access and two other users with edit access to the Geodatabase running in Oracle 11g.
The users are unable to select more than 100 features or 2 sets of features. The error pops up saying "Unable to create logfile system tables error. User perhaps lacks permissions or resources to create tables ORA01031". The edit users are able to perform the operation.

Searching through the forums led me to the log file settings.
First tried to set up as per "Shared log files" settings, the error still persists. The current sde_config settings is attached. In the user1 (read only access) schema, i could see 2 tables - sde_logfiles & sde_logfile_data.
Next, i attempted with the option of Pools of log files owned by the ArcSDE administrator too but to no avail.

Could someone point me in the right direction and suggest which is the best possible log file setting for this situation? (Me not being a DBA, bear with me if it sounds silly)

Bharathi

[JonDeRose · Accepted solution]

Bharathi,

Using the default of shared logfiles in Oracle you should see the SDE_LOGFILES and SDE_LOGFILE_DATA tables within each schema that will perform selections et al following the creation of a selection set >100. The choice of which log file type to use is dependent upon various factors yet for most configurations within Oracle shared will suffice.

ArcSDE log file tables in a geodatabase in Oracle

From a review of your sdeconfig.txt file it appears as though your config file is set to use both shared, session, and standalone log files to some extent.  It could be that due to the settings within this table your instance is trying to use standalone log files which require more elevated permissions than the other types.

In what order do ArcSDE log file types get used?

The following settings are needed in the SERVER_CONFIG table to create shared log file tables:

MAXSTANDALONELOGS = 0
ALLOWSESSIONLOGFILE = FALSE
LOGFILEPOOLSIZE = 0

Granting the following permissions to the user schema in question should enable the logfile tables to be created:

CREATE SESSION
CREATE TABLE
CREATE SEQUENCE

After the log file tables are created within the user's schema the CREATE TABLE permissions can be revoked.


- Jon