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:

[sourcecode]
sqlplus /nolog
[/sourcecode]

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

[sourcecode]
connect / as sysdba
[/sourcecode]

The only response I could get was:

[sourcecode]
ERROR:
ORA-01031: insufficient privileges
[/sourcecode]

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:

[sourcecode]
ALTER DATABASE
TEMPFILE ‘<path to file>\pstemp01.dbf’
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
[/sourcecode]

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

[sourcecode]
ALTER DATABASE
DATAFILE ‘<path to file>\<data file>’
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
[/sourcecode]

Leave a Comment

Your email address will not be published. Required fields are marked *