Error occurred in oracle new user executing sql query ORA-00942: table or view does not exist

An error message appears after the client executes the SQL query after oracle creates a new user:

Execute the query statement as follows:

Select * from sm_sales_order

ORA-00942: table or view does not exist

Create new users and specify tablespaces and temporary tablespaces

CREATE USER xxx IDENTIFIED BY xxxx

DEFAULT TABLESPACE LMS

TEMPORARY TABLESPACE TEMP

Grant system permissions connect

Grant connect to xxx

Grant object permissions, query only

Grant select any table to xxx

Client executes the statement select * from sm_sales_order

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

Solution: you need to specify schema, and modify the statement as follows

Select * from tech_scm.sm_sales_order

Execute query problem solving

If you don't want to enter schema every time, just set the default schema

Alter session set current_schema = tech_scm

Schema is a collection of database objects, we need to give this collection a name, these names are many of the user name-like nodes we see in the enterprise manager scheme, these user name-like nodes are actually a schema,schema that contains various objects such as tables views sequences stored procedures synonyms indexes clusters and database links.

A user generally corresponds to a schema whose schema name is equal to the user name and is the default schema for that user.

You cannot create a new schema in the oracle database. To create a schema, you can only create a user (although there is a create schema statement in oracle, but it is not used to create a schema), create a schema for the user with the same name as the user and use it as the default schema for the user. That is, the number of schema is the same as the number of user, and the schema name corresponds to the user name one by one and the same, so we can call schema an alias for user, although this is not accurate, but it is easier to understand.

A user has a default schema, whose schema name is equal to the user name, and of course a user can also use other schema. If we access a table without indicating which schema the table belongs to, the system automatically adds the default sheman name to the table. For example, when we access the database, we access the emp table under the scott user. Through select from emp, the complete sql statement is written as select from scott.emp. The full name of an object in the database is schema.object, not user.object. Similarly, if we do not specify the schema of the object when we create the object, the schema of the object is the user's default schema. This is like a user who has a default tablespace, but the user can also use other tablespaces. If we do not specify a tablespace when we create an object, the object is stored in the default tablespace. If we want the object to be stored in another tablespace, we need to specify the object's tablespace when we create the object.