SysDBA Connection Woes — Simple Fix



I needed SYSDBA privileges in order to extend a data file, and I could not make that happen for some reason.  I felt rather silly when I figured it out.  I’ve got to write it down in case I ever need to come back to it.

First, I opened SQLPlus without logging in:

sqlplus /nolog

Then, in SQLPlus, I tried to connect using OS authentication with SYSDBA access.  Here’s the command:

connect / as sysdba

The only response I could get was:

ERROR:
ORA-01031: insufficient privileges

I jumped through hoops trying to change the password for sys.  I regenerated the file password file.  Nothing helped.  Finally, I ran across this post that led me to check the OS group. All I had to do was put my Windows User into the Windows group for database administration.

I opened the start menu and right clicked on “My Computer”.  Then, I chose Manage from the menu.  In the Manage utility I went to “Local Users and Groups”, then I clicked on the Groups folder.  In the groups folder I double clicked on the “ORA_DBA” group to open the properties.  On the “Members” tab, I added my Windows username to the list.

From there, the connect worked fine.  To complete the story, here’s the resize SQL that I used:

ALTER DATABASE
TEMPFILE '<path to file>\pstemp01.dbf'
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

For me, the temp space was causing me trouble.  If you want to use a regular tablespace, the syntax is similar:

ALTER DATABASE
DATAFILE '<path to file>\<data file>'
 AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

Share