V$session & username

[mane]

After setting the parameters for the sid..C:\> set oracle_sid=(sid) and set oracle_home=d:\orant, i connected as sysdba and opened the database. I queried v$database to make sure i was in the right database and then queried username in v$session and to my surprise, the user was SYS. Isn't my name supposed to be in there instead of sys since i logged in with my name. Also, when i try to log through SQL*Plus, i'm told to run Pupbld though i have run that after creating the database. I ran that connected as system in both SQL plus and svrmgr. Could someone please help.

[nabaig]

Mane,

That is because a SYSDBA connection always connects the user whatever the usename might be to the SYS schema.
So connectng as internal/sys/user as sysdba will connect to SYS schema.

Nizar

[mane]

Thanks Nizar, but does that mean that any object i create will default to the system tablespace even if i specify another tablespace?

[rcaballe]

No, if you specify the tablespace, the default tablespace is always override.
And, you are supposed to connect as sysdba to do DBA maintenance, not to create objects. Leave that to developer's scripts

[mane]

Thanks Ramon, but if i want to create users, tables and some other database objects, do i have to log in as sysdba, my username or what from svrmgr/sql *plus.

[maachan]

The practice I follow is to create a DBA user as soon as I create a database.
That is the user I use to create users, roles, and objects if needed.
I set that DBA user's default tablespace to something other than System so I don't need to worry about things falling into SYSTEM tablespace.
I only use sysdba users for startup/shutdown, recovery, and other heavy duty jobs that requires sysdba access. DBA user does not need sysdba access to do its job.

hope this helps

maachan

[mane]

Thanks a bunch Maachan. Good advice.