6 Replies Latest reply: Apr 13, 2012 1:33 PM by damorgan RSS

    Connect Problems with Oracle Database Express Edition 11g Release 2

    WD
      Hello,

      I am a student trying to install Oracle Database Express Edition 11g Release 2 and SQL Developer on my home system, Win7 64Bit, in order to practice some things I've learned from me DBA class and Developer classes.

      Anyway, I have everything installed, but I am having difficulty connecting as SYS or SYSDBA in the 'Run SQL Command Line', I keep getting the ORA-01017: invalid username/password: logon denied.

      However, If i select the 'Start Database' I get this:

      C:\oraclexe\app\oracle\product\11.2.0\server\bin>

      and I can type sqlplus / as sysdba and it starts up just fine and show user lists me as "SYS".

      but if I go back to 'Run SQL Command Line' I still cannot connect as SYS or SYSDBA...I find this both confusing and frustrating. I don't know if I am in different instances or something like that, but I seem to be limited to connecting only as "SYSTEM". I need SYS because I want to practice creating datafiles, instances and things like that, but I seem to be lost.

      Also, I am trying to create a new DB connection with SQL Developer and I can only us SYSTEM for my login which, if I understand correctly, will limit my privileges. Again When I try to sign in with SYS or SYSDBA I get error'd out. When I installed Ora11gDBExpress I was prompted in input a single password that was supposed to grant me access as SYS or SYSTEM, but I am limited to only SYSTEM for some reason.

      So, I am looking for help/guidance as to what to do.

      Thanks in advance for any and all help,

      Warren
        • 1. Re: Connect Problems with Oracle Database Express Edition 11g Release 2
          clcarter
          General rule of thumb, don't use sysdba unless you want to shut down the database, or grant a database user privileges on a sys object.

          A SYSTEM connection is not "limiting", it has the DBA role which means a user with a system connection can do most anything needed, including select/update/delete/drop any user's objects as well as change parameters in the instance.

          The system user can indeed add datafiles, tablespaces, etc. The instance and database should already be created as long as the installer completed all its chores correctly. For XE, per the license agreement only one instance can run on one host. If you want to try creating a database, it will require shutting down the XE instance and creating a new database service, creating the database, and installing the system catalog and any other optional components desired. Good practice indeed, but a bit advanced for the new user.

          Do create users for schemas ... connect system; create user <username> identified by <password> and connect <username> for the schemas (a collection of objects) within the database. Grant the resource and create session privilege to <username> to allow the database user the ability to create tables, indexes, stored procedures, etc.

          There is no "or" in a sys as sysdba connection, from 10g onwards a sys connection requires using the sysdba privilege. To enable a sysdba connection, add your host user to the ORA_DBA group on the host. To verify the OS users in the ORA_DBA group, this might work for win7, in a cmd box ...
          $ net localgroup ora_dba
          ...
          If your OS user is in the ora_dba group the sys as sysdba password is not relevant, you can in fact type anything for a password. If you wish to connect with the sysdba privilege from a remote client, that is a bit different and requires knowing the password set in the instance password file. Which should be set the same as the system password defined in the installer, but you can change that by creating a new password file. Another slightly advanced topic.

          In Windows IMHO its better to leave the listener and database set to Manual start (in the services applet, Start/Run/services.msc) and start the listener, then the database, when its needed. At least for an XE instance, as its intended for practice and learning RDBMS management.

          Edited by: clcarter on Mar 2, 2012 6:19 PM
          fix typos
          • 2. Re: Connect Problems with Oracle Database Express Edition 11g Release 2
            damorgan
            SYSDBA should only be used for patching and backups (RMAN). Otherwise best practice is to create a DBA schema and not grant the DBA role to it but rather grant to that role those privs actually required for the day-to-day management of the database which includes granting privs.
            • 3. Re: Connect Problems with Oracle Database Express Edition 11g Release 2
              WD
              Sorry for the really late reply. Thanks for your help I created a user with sysdba privs. Everything is connected and working so far since.
              • 4. Re: Connect Problems with Oracle Database Express Edition 11g Release 2
                930541
                I am having problems creating a tablespace and user. I try creating the tablespace and user in sql command line but get the error not connected. Please help!
                • 5. Re: Connect Problems with Oracle Database Express Edition 11g Release 2
                  clcarter
                  Please create a new thread, this one is already marked Answered. You're more likely to get questions noticed that way.

                  And posting specific ORA-n or TNS-n error number details will also help in getting quicker responses as well, a generic "it doesn't work" and "... got an error" questions will require more questions about your particulars.
                  • 6. Re: Connect Problems with Oracle Database Express Edition 11g Release 2
                    damorgan
                    Sorry if I wasn't clear ... best practice is not only to not log in as SYS but to not use SYSDBA.

                    Grant the specific privileges you actually need to a role and then the role to the administrative user.