0

I created some tables as a normal user, and when I change the privilege to sysdba I didn't fiund my tables!
I get this message: table or view does not exist

This is what I do:
First of all, with a normal user's privilege I create tab1:

create table tab1 …;

and I insert some values, when I

select * from tab1;

all my rows are displayed, but when I connect as sysdba; with this current user, no row is displayed!!

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Kati Dev
  • 25
  • 1
  • 3
  • 10

2 Answers2

1

When you login as sysdba, you literally become the the sys user, and as such, you're connected to the sys schema, not your own:

sqlplus kjohnston as sysdba
*connected*
SQL> show user;
USER is "SYS"

Since you are in the sys schema, you have to reference your tables in your schema by prefixing the tablename with the schema name, as in: 

select * from kati_ais.tab1;  //assuming kati_ais is your schema name

As a side note, you should not get in the habit of logging in as sysdba unless you really need those higher level privileges for that session.

Kris Johnston
  • 718
  • 5
  • 15
0

From the oracle documentation https://docs.oracle.com/database/121/ADMIN/dba.htm#ADMIN11048 , here's what sysdba provide to your user:

Perform STARTUP and SHUTDOWN operations

ALTER DATABASE: open, mount, back up, or change character set

CREATE DATABASE

DROP DATABASE

CREATE SPFILE

ALTER DATABASE ARCHIVELOG

ALTER DATABASE RECOVER

Includes the RESTRICTED SESSION privilege

What you need to see your tables with your other user is either:

grant select on *normal_user*.table1 to *your_sysdba_user*

and repeat it for each table

or you can go bar open with

grant select any table to *your_sysdba_user*

but be advised that select any table include everything from everyone including data dictionary view which may be more than you truly wish to allow

I would assume that your sysdba user would be close to database god like sys so that would be ok but the disclaimer is there so you know

Mike
  • 89
  • 4
  • Thanks for your response , but maybe you don't understand my question… First of all, with a normal user's privilege I create tab1 create table tab1 … ; and I insert some values, when I select * from tab1; all my rows are displayed, but when I put connect as sysdba; with this current user , not row display !! – Kati Dev Dec 12 '16 at 20:26
  • Ok, i might have misunderstood indeed. Did you commit the insert done by your normal privilege user? Because inside your session if you add row to a table and then select from that table, you will see your added rows but until you commit the changes, they will remain invisible to the other users/sessions – Mike Dec 13 '16 at 15:18