basic question on querying oracle tables in SAP ERP

[Former Member]

Hi,

I have some basic question. This question is related to SAP ERP and Oracle Database. 

I can query the "dba_users" table....

***********

SQL> select username from dba_users where rownum < 2;

USERNAME

------------------------------

SYS

SQL>

******

But when I get all the tables, dba_users table is not found......

SQL> select tablespace_name, table_name from all_tables where table_name='dba_users';

no rows selected

SQL>

I am not sure why "all_tables" didn't return the "dba_users" table, though the "dba_users" table exists?

If you've any idea regarding this, can you please let us know.

Thanks,

Mahesh

[KayKa · Accepted solution]

Hi Mahesh,

try this

select object_name, object_type from dba_objects where object_name like '____USERS';

And now you see that dba/all_users is "only" a view not a table as most of the dictionary.

You have to use upper case name if you retrieve the dictionary.

The DBA_xxx views represent all objects form the database, the USER_xxx only your own objects and the ALL_xxx show all objects you have the right to see.

regards
Kay

[Former Member · Accepted solution]

Thanks for the answer, Kay.

I have one more question on the similar lines...

Though the below link is specific to "SQL Command Editor", but, I still think that these tables(mentioned in Examples) should exist in Oracle database.. 

http://help.sap.com/saphelp_tm80/helpdata/en/45/76de5b97ab401fe10000000a1553f6/content.htm

When I query for "t100" and "v$sysstat", I can't find in oracle database. I am not sure why

SQL> select tablespace_name, table_name from all_tables where table_name='t100';

no rows selected

SQL> select tablespace_name, table_name from all_tables where table_name='v$sysstat';

no rows selected

SQL>

If you've any idea on how to query these tables from "sqlplus" command prompt, can you please let me know.

Thanks,

Mahesh

[KayKa · Accepted solution]

Hi Mahesh,

as mentioned above use UPPER CASE. If you query 'T100' you get a result.

On the other hand most objects of the Oracle data dictionary are views especially the v$-views. So have a look at dba_views or dba_objects and again use UPPER CASE if you look for table / view / object names 😉

regards
Kay

[Former Member · Accepted solution]

Thanks Kay. 

I can query from "all_tables" now.

But when I query the data in "T100", I get table/view does not exist. 

***

SQL> select tablespace_name, table_name from all_tables where table_name='T100';

TABLESPACE_NAME                TABLE_NAME

------------------------------ ------------------------------

PSAPSR3702                     T100

SQL> select rowid from T100;

select rowid from T100

                  *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

***

Thanks,

[Former Member · Accepted solution]

hi

try with

select rowid from <ownername>.T100;

you can get the owner name from dba_users; (you default SAP schema.

Regards
Dishant.

[KayKa · Accepted solution]

Hi Mahesh,

it will work in st04 / SQL Command Editor because you work with SAP schema owner.

How do you connect via SQL*Plus ?

If you are not connected as SAP Schema owner you have to full qualify your select with something like this

select * from <Schema_owner>.<table_name>

And perhaps it will be helpfull to not select the whole table with

where rownum < 50
In my SQL Editor Limit of rows doesn't work correctly and in SQL*Plus there is no build-in Limit.

regards

Kay

[Former Member · Accepted solution]

Thanks Dishant.

I have tried with user name(SAPSR3) and I can query the table now:

SQL> select rowid from SAPSR3.T100 where rownum<2;

ROWID

------------------

AAAO89AANAAAibdAAA

SQL>

[Former Member · Accepted solution]

Hi Kay,

Yes, I have connected using the "SQL *Plus"

***

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 17 01:22:08 201

****

>select * from <Schema_owner>.<table_name>

Yes, this worked. I can query the table now.

Thanks,

[Former Member · Accepted solution]

Hi Kay,

>So have a look at dba_views or dba_objects a

The view(V$SYSSTAT) has mentioned in link that I shared in my earlier comment.

When I look for the view(V$SYSSTAT) , I can't find it.  Can you please share your thought

SQL> select VIEW_NAME from dba_views where VIEW_NAME='V_SYSSTAT';

no rows selected

SQL> select VIEW_NAME from dba_views where VIEW_NAME='V$SYSSTAT';

no rows selected

SQL>

But, I can query other view(s).

SQL> select VIEW_NAME from dba_views where VIEW_NAME='V_WSTEXT';

VIEW_NAME

------------------------------

V_WSTEXT

SQL>

Thanks,

Mahesh

[KayKa · Accepted solution]

Hi Mahesh,

it's a little bit tricky to deal with v$-views.

V$SYSSTAT  is a public synonym for the view V_$SYSSTAT from user SYS.
But you don't find V$SYSSTAT in dba_views but in dba_synoynms.

Try this

select * from dba_synonyms where synonym_name = 'V$SYSSTAT';

OWNER           SYNONYM_NAME         TABLE_OWNER        TABLE_NAME       
--------------- -------------------- ------------------ --------------
PUBLIC          V$SYSSTAT            SYS                V_$SYSSTAT

And don't worry about the column Name 'TABLE_NAME' it's still a view.

Have a look at the Oracle documentation  About Dynamic Performance Views

regards
Kay

[Former Member · Accepted solution]

Thanks alot, Kay. I have tried that and it did work.

You've a good day.

[Former Member · Accepted solution]

Hi Kay,

Do you know if SAP has JDBC drivers to query the data from the SAP db tables?

I need to write an external application to query the SAP database statistics information.

Thanks in advance.

-Mahesh

[KayKa · Accepted solution]

Hi Mahesh,

sorry, i don't know if SAP offers JDBC Drivers.

I use my SQL*Plus to get what i want from the database under my SAP systems.

regards
Kay

[Former Member]

Hi Mahesh,

The following are the only fields available in the dba_users table which you can querry.

USERNAME                                 

USER_ID                                  

PASSWORD                                          

ACCOUNT_STATUS                           

LOCK_DATE                                        

EXPIRY_DATE                                      

DEFAULT_TABLESPACE                      

TEMPORARY_TABLESPACE                     

CREATED                                  

PROFILE                                  

INITIAL_RSRC_CONSUMER_GROUP                       

EXTERNAL_NAME

regards

suresh

[KayKa]

Hi Suresh,

dba_users is not a table it's a view. That's the problem why Mahesh didn't find it in dba_tables or all_tables.

regards

Kay