n00b oracle question

Question

I am using Oracle Sql Developer

I have a huge script that creates tables, indexes, primary key constraints and such.

my DB name is: dbo_other

I logged into this dbo_other as sysdba.

If I run my script then tables do not show up on left panel under 'Tables'

However, if I append the script by adding 'dbo_other.' in front of every table name then the tables show up.

This is very tedious and time consuming.

Is there a way to avoid this? why wont they show up in dbo_other without adding dbo_other. in front of every table name?? When I run the query on the upper right corner the drop down has dbo_other selected!!

I can even do a select * from the table created (but dont see it in left sidebar) Furthermore, I can see the table in pl/sql developer.

Why does oracle sql developer want me to create it with dbo_other.??

Also, is there a way to avoid adding it for each table? maybe something can be done on top of the script so it takes effect on everything that follows?

Answer 1

Why are you logging in to your database using the SYSDBA account? This is very powerful, and it will allow you to do terrible damage to your database if you don't know what you're doing. Which I am afraid you don't.

The interesting thing about AS SYSDBA is that it overrides the username part of the login: if your OS user has the privileges, you're in. As SYS. Check it out:

SQL> conn apc
Enter password:
Connected.
SQL> show user
USER is "APC"
SQL> conn apc as sysdba
Enter password:
Connected.
SQL> show user
USER is "SYS"
SQL>

So, when you ran that script you created all those objects in the SYS schema. Which will prove to be a massive pain in the neck. I hope you have an equal and opposite reversion script.

WRT to running the script properly, all you need to do is connect as DBO_OTHER (normal - i.e. without SYSDBA or SYSOPER). This is the default after all.

Answer 2

The "Tables" tree on the left-hand panel only includes tables the logged-in user owns in Oracle SQL Developer. If your script creates tables in another user's schema, you need to click the + next to "Other Users", find the appropriate user, and click the + on their tables.

As others have said, you shouldn't use SYSDBA unless you need to, and it sounds very much like your script should be executed as a normal user based on its rough description.

Answer 3

I just stumbled upon this little jem which lets you perform actions on a schema/user by default for which you are not logged in as. That is, by default your select statements, etc will operate on this new schema instead of your own.

alter session set current_schema =

Example: Myself + table1 + table2 SomeoneElse + SuperTable1 + SuperTable2

log in as "Myself" 
select * from SuperTable1

Error: ORA-00942: table or view does not exist

alter session set current_schema = SomeoneElse 
select * from SuperTable1 <This will work.>