Can't query dba_users 1

[ponderdj]

Hi, all,

I've done some searching but haven't found anything that helps, only people getting the same error on tables they've just created. I've just installed 10g on Oracle Linux 5, and I'm trying to get off the ground.

Although I can see dba_users exists in Enterprise Manager, I can't query it in SQL_Plus. I get:

SQL> select * from dba_users;
select * from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist

I'm logging into SQL Plus using sys as sysdba. I also log into Enterprise Manager using sys as sysdba.

Thanks in advance

 

[LKBrwnDBA]

You need SELECT ANY DICTIONARY privilege.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

If you are logging into SQL Plus using sys as sysdba, you should be able to query any table/view.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ponderdj]

Thanks, LK,

Is is possible I'm not using the correct db or something? I'm used to MySQL and the USE DATABASE command, but am learning that's not the way Oracle works. What are the startup steps I should take on a newly installed DB on Oracle Linux 5 to make sure my db is started correctly, I can use Enterprise Manager, and I can use isqlplus? Any other troubleshooting ideas?

(As a side note I'm having a hard time logging into isqlplus, can't figure out what to put in the three login boxes).

Thanks!

 

[yelworcm]

Just a shot in the dark, but have you run catalog.sql and catproc.sql (catalog would be more important in this case) after creating the database?

 

[ponderdj]

I think I'm on to the root of the problem (thanks to yelworcm!). I initially created the 'oracle10' database using the DBCA, launched by the Oracle Universal Installer during install. Not knowing what I was doing, I later issued the CREATE DATABASE command. I'm fairly sure I just used "CREATE DATABASE oracle10", but I'm not sure.

I'm going to follow the "Creating the Database" section in the document at:

http://docs.oracle.com/cd/B19306_01/server.102/b14231.pdf,

which does get into using catalog.sql and catproc.sql at step 9.

Do I sound like I'm on the right track? I'll post progress.

Thanks

 

[ponderdj]

Well, I skipped right to step 9 and ran the following command:

@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql

I can now run the SQL select statement I was initially trying on dba_users. Only the OUTLN, SYS, and SYSTEM users are present. Should I be good to go or do you suggest that I perform the full database creation procedure or use the DBCA?

Thanks

 

[yelworcm]

DBCA should be doing all of these steps in the background. If you did need catalog.sql, you will likely need catproc.sql as well. The first creates all of the views (dba_users is in fact a view, not a table), while catproc.sql creates most of the packages you will likely use during the life of the database (update stats package, dbms_output, etc.). I am not sure why DBCA would have skipped those, unless it had some sort of error.