Create users and tablespaces and create user tablespaces

Create users and tablespaces and create user tablespaces

Oracle 11.2.0.1

1. Create a new user default tablespace DCSOPEN_TBS:

Note:

(1) The initial data file size is set to 200 MB, and the AUTOEXTEND attribute increases automatically by default. Each time you apply for a new tablespace, 32 MB is allocated and a maximum of 1024 MB is allocated.

(2) extentmanagement local is not used here (optional), but the default is the LOCAL management mode (EXTENT_MANAGEMENT), the area size is allocated to AUTOALLOCATE (ALLOCATION_TYPE is SYSTEM ), segment space management is automatic AUTO (SEGMENT_SPACE_MANAGEMENT ).

2. Create a new temporary tablespace DCSOPEN_TEMPTBS:

Note:

(1) Unlike the default properties for creating tablespaces, temporary tablespaces cannot be allocated by AUTO. Temporary tablespaces are created by using a locally managed partition with a uniform size (1 MB by default, you can also specify: uniforma size 2 M;, and segment space management is not AUTO, but MANUAL.

(2) As recommended by Oracle, use the temporary tablespace locally managed in 1 MB uniform partition size as the default temporary tablespace.

3. create user DCSOPEN:

Note:

(1) Specify the user's default tablespace and default temporary tablespace. If this parameter is not specified, the default tablespace uses the USERS tablespace created in Oracle, the default temporary tablespace is TEMP (if not created, the SYSTEM tablespace is used ).

(2) You can then grant the user permissions, for example:

GRANT
Create session, create any table, create any view, create any index, create any procedure,
Alter any table, alter any procedure,
Drop any table, drop any view, drop any index, drop any procedure,
Select any table, insert any table, update any table, DELETE ANY TABLE
TO dcsopen;

(3) Here, 10 GB and 11 GB seem to have some differences in the user's quota for using tablespaces.

Symptom:

(3.1) 10 Gb to CREATE a user, by default using USERS tablespace, only grant create table and create table permissions, the user with create table to CREATE a TABLE prompt: ORA-01950: no privileges on tablespace 'users'

(3.2) CREATE a user at 11G. By default, the USERS tablespace is used. Only the create table and create table permissions are granted. This user can CREATE a TABLE.

The table space quota can be used only when the user has resource permission, 11g for the user to use revoke resource from r1; prompt ORA-01951: ROLE 'resource' not granted to 'r1 ', this indicates that the user does not have the resource permission.