MSSQL version: SQL Server 2019
Oracle version: 19c
SQL Developer version: 24.3.1.347
Hello!
I have an odd problem that I can't explain and hopefully someone has run across.
I'm migrating a MSSQL DB to Oracle using SQL Developer Migration. The migration script fails because the target Oracle user cannot create a ROLE or USER.
Log snippet:
SQL> CREATE ROLE ROLE_TP__AvantisPROD_Gold_test ;
Error starting at line : 18 File @ C:\Temp\SQL Developer Output\TP__AvantisPROD_Gold_test\generated\2025-03-15_10-18-44\master.sql
In command -
CREATE ROLE ROLE_TP__AvantisPROD_Gold_test
Error report -
ORA-01031: insufficient privileges
The user permissions were created per the SQL Developer documentation and clearly show that it has those permissions via the DDL and DBA GUI. But when I connect to the user via SQL Developer and check session_privs they are missing:
And I predictably get an ORA-01031 (insufficient privileges) when I manually try to create a ROLE or USER.
However, I can connect to the user via SQL*Plus and display the session_privs and they include ROLE and USER creation. And I can create a role or user with no error.
SQL*Plus :
[oracle@ ~]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 18 08:46:39 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Enter user-name: AVANTIS_GOLD
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL> set lin 1000;
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
SET CONTAINER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE TYPE
CREATE MATERIALIZED VIEW
CREATE TRIGGER
CREATE PROCEDURE
DROP ANY ROLE
CREATE ROLE
CREATE SEQUENCE
CREATE VIEW
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE TABLE
DROP USER
CREATE USER
CREATE SESSION
16 rows selected.
SQL> CREATE ROLE ROLE_TP__AvantisPROD_Gold_test;
Role created.
SQL>
I want to use the Online option, but this user issue is killing the migration.