This content has been marked as final. Show 6 replies
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.1 person found this helpful
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 ...
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.
$ net localgroup ora_dba ...
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
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.1 person found this helpful
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.
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!
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.
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.