Complete steps for creating a tablespace in Oracle 10 GB

Oracle 10gIn databases, when creating users in databases, it is best to create independent tablespaces for different users based on application performance and management considerations. SoCreate a tablespaceHow is the process implemented? This article mainly introduces this part of content. Next let's take a look at this part.

1. Create a tablespace

In both the Lnux environment and the Wndows environment, you must first create a storage path for the tablespace and modify it according to your own situation. For example,/opt/oracle/oradata/cocis or D: \ oracle \ oradata \ cocis. If this directory path is not created in advance, an error will occur when creating the tablespace.

Log on to the Oracle database through pl/SQL, and then execute the menu: File/New/command window, open a command window, and then execute the script in the Command window to create and delete tablespaces.

Create a tablespace

 
 

  
  
Sql>create tablespace histdb datafile 'D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf' size 200m autoextend on next 10m maxsize unlimited;   
  
  
Sql>alter database datafile 'D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf' autoextend on;  
 
 

1) DATAFILE: path for storing tablespace data files

2) SIZE: initially set to 200 M

3) UNIFORM: the size of the specified area is 128 k. If not specified, the default area size is 64 k.

4) The space name histdb is not required to be the same as the data file name histdb. dbf.

5) autoextend on/OFF indicates that automatic table space expansion is started/stopped.

6) alter database datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ histdb. dbf' resize 500 m; // manually modify the data file size to 500 M

Delete a tablespace

 
 

  
  
DROP TABLESPACE histdb INCLUDING CONTENTS AND DATAFILES;  
 
 

The tablespace has been created.

2. Create a user for the Application

When creating a user, specify the default permanent tablespace and temporary tablespace for the user.

 
 

  
  
SQL> create user cocis identified by cocis
  
  
2 default tablespace cocis
  
  
3 temporary tablespace temp;
  
  
The user has been created.
  
  
SQL> select username, default_tablespace, temporary_tablespace
  
  
2 from dba_users
  
  
3 where username = 'cocis ';
  
  
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
  
  
------------------------------------------------------------------------------
  
  
COCIS TEMP
  
  
SQL> select username, user_id, password, default_tablespace, temporary_tablespace
  
  
2 from dba_users
  
  
3 where username = 'cocis ';
  
  
USERNAME USER_ID PASSWORD TEMPORARY_TABLESPACE
  
  
---------------------------------------------------------------------------------------------
  
  
COCIS 61 E031F623C0F15D34 COCIS
 
 

3. permission granting

 
 

  
  
SQL> grant connect, resource to cocis;
  
  
Authorization successful.
 
 

Note: After a user is created, you only need to grant the CONNECT and RESOURCE roles. To grant permissions separately, you need to execute separate authorization commands, such as grant create table to cocis.

To grant SYSDBA permissions to users, run the following command:

 
 

  
  
SQL> grant SYSDBA to cocis;
  
  
---------------------------------------------------
  
  
SQL> revoke unlimited tablespace from cocis;
  
  
Withdrawing successful.
  
  
SQL> alter user cocis quota unlimited on cocis;
  
  
The user has changed.
 
 

Note:For more rigorous management, You can revoke the unlimited tablespace permission of the user, and then separately authorize the user's space quota.

The complete steps for creating a tablespace in Oracle 10 Gb are described here. If you want to know