Oracle new user executes SQL query statement error ORA-00942: Table or view does not exist

Source: Internet
Author: User

After Oracle creates a new user, the client executes the SQL query with the following error message:

Execute the query statement as follows:

SELECT * from Sm_sales_order;

ORA-00942: Table or view does not exist


Create a new user and specify tablespace and temp table space

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 Execution Statement SELECT * from Sm_sales_order;

Error occurred: ORA-00942: Table or view does not exist


WORKAROUND: You need to specify the schema, modify the statement as follows

SELECT * FROM Tech_scm.sm_sales_order

Perform query Problem resolution

If you don't want to enter the schema every time, 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 are the many similar user names that we see under the Enterprise Manager scenario, These nodes, such as usernames, are actually a schema,schema containing various objects such as tables views sequences stored procedures synonyms indexes clusters and database Links

A user typically corresponds to a schema the user's schema name equals the user name and is the default schema for that user.

The Oracle database cannot create a new schema, and to create a schema, it can only be solved by creating a user's method (although Oracle has the CREATE SCHEMA statement, it is not used to create a schema) Create a schema for this user with the same name as the user name while creating a user and as the default schema for that user. that is, the number of schemas 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 the schema user alias, although this is not accurate, but it is easier to understand some.

A user has a default schema, whose schema name is equal to the user name, and of course a user can use other schemas. If we visit a table and do not indicate 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, and through the select from EMP in fact, this SQL statement is the complete notation of the select from Scott.emp. The full name of an object in the database is Schema.object, not user.object. Similar if we do not specify the schema of the object when creating the object, the schema of the object is the user's default schema. This is like a user with a default tablespace , but the user can also use other tablespaces, and if we do not specify a tablespace when we create the object, the object is stored in the default tablespace, and in order for the object to be stored in another table space, we need to specify the table space for the object when it is created.


This article is from the "Linux Strongest Brains" blog, so be sure to keep this source http://linuxzqdn.blog.51cto.com/6158930/1765896

Oracle new user executes SQL query statement error ORA-00942: Table or view does not exist

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.