ORACLE learning notes-create a user and a table, and create an oracle learning note

Source: Internet
Author: User

ORACLE learning notes-create a user and a table, and create an oracle learning note

I. User-related SQL statements

/* Create a user */

create user SA identified by 2013;

Description: SA user name and 2013 Password

/* Authorize connect, resource to sa */grant connect, resource to sa;

Note: The CONNECT role is the most basic and typical right granted to end users.

ESOURCE role: -- It is granted to developers

/* Query all users */select * from all_users;

/* GRANT the table STU query permission to sa */grant select on stu to sa/* GRANT the table STU add permission to sa */grant insert on stu to sa/* update the table STU grant sa */grant update on stu to sa/* grant sa */GRANT DELETE ON STU TO SA

Ii. Table Creation

/* Create table specified tablespace */create table KC (KC_NO varchar2 (10) not null, KC_NAME varchar2 (50) not null) tablespace TEST; create table STU (STU_ID varchar2 (10) not null, STU_NAME varchar2 (50) not null, STU_AGE number (2) not null, STU_SET number (2) not null, KC_NO varchar2 (10) not null) tablespace TEST;

Note: two tables are used in this article, namely the student table STU and the course table KC. The two tables are stored in the TEST table space.

/* Create constraints, Set PRIMARY keys */alter table stu add primary key (STU_ID) alter table kc add primary key (KC_NO)/* View TABLE space */Select table_name, tablespace_name from dba_tables where tablespace_name = 'test'

/* Query all tables */select table_name, tablespace_name, temporary from user_tables


How Does oracle create a new user and grant permissions?

Create user username identified by password; -- this is the creation USER (this is the simplest creation statement without specifying the tablespace)
Grant create session to username; -- this ensures that the user can log on TO the database
Grant create table to username; -- this allows the user to create a TABLE
There are still a lot of permissions. These are some basic syntaxes.

Users created using command lines in oracle cannot create tables.

The tablespace is not defined.
Create user abc identified by abc default tablespace users temporary tablespace temp;
Grant connect, resource to abc

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.