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