Complete step example of creating an Oracle10g tablespace

Complete steps for creating an Oracle10g tablespace

When creating a user in a database, it is best to create an independent tablespace for different users based on application performance and management considerations.

1. Create a tablespace

In both Linux and Windows environments, you must first create a storage path for the tablespace, such:

/Opt/Oracle/oradata/cocis or D: "oracle" oradata "cocis

If you do not create the directory path in advance, an error occurs when creating the tablespace.

Run the following command:

SQL> Create tablespace cocis

2 datafile 'd: "oracle" oradata "cocis" cocis01.dbf'

3 size 100 m autoextend on next 10 m maxsize 2048 m

4 extent management local uniform size 128 K

5 segment space management auto

6/

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.

 

View users

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.

 

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, the unlimited tablespace permission of the user can be revoked, and the user's space quota can be separately authorized.

 

Bytes -----------------------------------------------------------------------------------------

 

View table space usage

Select DF. tablespace_name "tablespace name", totalspace "total space M", freespace "remaining space M", round (1-freespace/totalspace) *, 2) "usage %"

From

(Select tablespace_name, round (sum (bytes)/1024/1024) totalspace

From dba_data_files

Group by tablespace_name) DF,

(Select tablespace_name, round (sum (bytes)/1024/1024) freespace

From dba_free_space

Group by tablespace_name) FS

Where DF. tablespace_name = FS. tablespace_name;