CREATE table spaces in Oracle, create tables, modify tables, authorize

CREATE TABLE Space


/* divided into four steps */
/* Step 1th: Create a temporary tablespace */
Create temporary tablespace user_temp
Tempfile ' D:\oracle\oradata\Oracle9i\user_temp.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m//If the maximum value is not capped, use unlimited
Extent management Local; Extent indicates that interval setting interval management mode is local mode another way is dictionary dictionary, extent can be understood as the way to expand the table

/* Step 2nd: Create a data table space */
Create Tablespace User_data
Logging//= Create log nologging means log not created
DataFile ' D:\oracle\oradata\Oracle9i\user_data.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m
Extent management Local;

/* 3rd step: Create user and specify Tablespace */
Create user username identified by password
Default Tablespace user_data
Temporary tablespace user_temp;

/* Step 4th: Grant permissions to users */
Grant CONNECT,RESOURCE,DBA to username;
Grant CREATE TABLE to username






1, first, grant XXX to User;,grant is the role of authorization, where XXX can be a role roles, can also be permissions, such as Grant role to user, or grant insert on the table to user;.


Standard SQL statements given in MOS:


To create a role:


Create role <role name> [identified by <password>/using <package>/externally/globally];


To assign a role permission:


Grant <object/system privilege> to <role name>;


To reclaim permissions from a role:


Revoke <privilege> from <role name>;


To assign a role to another role or user:


Grant <role> to <username or role>;


2. Second, connect and resource are the built-in roles of the two systems, and the DBA is tied to the relationship.


Referring to some of the posts, permissions can be divided into two categories:


System permissions: The system specifies the user's permission to use the database. (System permissions are for users).


Entity permissions: A permission user's access to other users ' tables or views. (For a table or view).


Next look at the system permissions,


DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.


RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.


Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.


For normal users: Grant Connect, resource permissions.


For DBA administration users: Grant Connect,resource, dba authority.


And the system permissions can only be granted by the DBA User: sys, system (which can only be the first two users). An ordinary user can have the same user rights as the system through authorization.


However, the same permissions as the SYS user can never be reached, and the permissions of the system user may be recycled.


In addition, for the issue of cascading authorizations with the ADMIN option,


1) If you use with ADMIN option to grant system permissions to a user, then for all users who are granted the same permissions by this user, the user's


System permissions do not cascade the same permissions that are removed by these users.


2) There is no cascade of system permissions, that is, a grants B permission, b grants C permissions, if a reclaims the permissions of B, the permissions of C are not affected; System permissions can be recycled across users, i.e. a can


Directly retract the permissions of the C user.



Create a table
Create tables in Oracle:
CREATE TABLE Users (
ID Number (9) NOT null primary key,
Username VARCHAR2 (+) NOT NULL,
Age int Check (AGE&GT;18 and age<50)
)


CREATE TABLE Orders (
OID VARCHAR2 (TEN) is not NULL,
UserID Number (9),
Primary KEY (OID),
FOREIGN KEY (userid) references users (ID)
)


Delete tables: drop table Orders


Modify table name: ALTER TABLE orders
Rename to Orderss


Add column: ALTER TABLE orders
Add price number (9)


To modify the data type of a column: ALTER TABLE orders
Modify price Number (10)


Delete column: ALTER TABLE orders
Drop Column Price


Modify column name: ALTER TABLE orders
Rename column price to PRI


Add foreign key: ALTER TABLE orders
Add constraint Fkuid foreign key (userid) references users (ID)


Delete constraint: ALTER TABLE orders
Drop constraint Fkuid

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

CREATE table spaces in Oracle, create tables, modify tables, authorize