Complete steps to create a tablespace in Oracle 10g

This article focuses on the complete steps for creating a tablespace for Oracle 10g, including the creation and deletion of tablespaces, the creation of users for the app, and the granting of permissions, to help you.

AD:WOT2014: User labeling system and user data Operation training session

In Oracle 10g databases, when creating users in a database, it is best to create separate tablespaces for different users based on application performance and management considerations. So how does the steps to create the tablespace be implemented? In this article we mainly introduce this part of the content, then let us look at this part of the content.

1. Create a table space

Whether it is the lnux environment, or the wndows environment, we must first create a good table space storage path, according to their own circumstances to modify. such as:/opt/oracle/oradata/cocis or D:\oracle\oradata\cocis, if the directory path is not created in advance, the table space will be created with an error.

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

CREATE TABLE Space

1. SQL>create tablespace histdb datafile ' D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf ' size 200m autoextend   On next 10m MaxSize Unlimited;
2. SQL>alter database datafile ' D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf ' autoextend on;

1) datafile: Table space data File storage path

2) SIZE: initially set to 200M

3) UNIFORM: The designated area size is 128k, if not specified, the zone size defaults to 64k

4) The space name Histdb and the data file name HISTDB.DBF do not require the same, can be arbitrarily named.

5) Autoextend on/off indicates start/stop auto-expansion table space

6) ALTER DATABASE datafile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\HISTDB.DBF ' resize 500m; Manually modify the data file size to 500M

Delete Table space

1. DROP tablespace histdb including CONTENTS and datafiles;

The tablespace has been created.

2. Create a user for the app

At the same time as the user is created, the user is given a default permanent tablespace and a temporary table space.

2. SQL> Create user Cocis identified by Cocis
4. 2 Default Tablespace Cocis
6. 3 Temporary tablespace temp;
8. The user has created.
10. SQL> select Username,default_tablespace,temporary_tablespace
12. 2 from Dba_users
14. 3 where username=' Cocis ';
16. USERNAME Default_tablespace Temporary_tablespace
18. ------------------------------ ------------------------------ ------------------
20. Cocis Cocis TEMP
22. SQL> select Username,user_id,password,default_tablespace,temporary_tablespace
24. 2 from Dba_users
26. 3 where username=' Cocis ';
28. USERNAME user_id PASSWORD Temporary_tablespace
30. ------------------------------ ---------- ------------------------------ -----------------------
32. Cocis e031f623c0f15d34 Cocis

3. Grant of permission

1. SQL> Grant connect,resource to Cocis;
2. Authorization is successful.

Note: When a user is created, you typically only need to grant connect and resource to both roles. To authorize separately, you need to perform a separate authorization command, such as grant create table to Cocis;

If you want to grant SYSDBA permissions to the user, use the following command:

1. SQL> Grant SYSDBA to Cocis;
2. ---------------------------------------------------
3. SQL> Revoke unlimited tablespace from Cocis;
4. Undo success.
5. SQL> ALTER user cocis quota unlimited on cocis;
6. The user has changed.

Note: For more rigorous management, you can reclaim the user's unlimited tablespace permissions, and then individually authorize the user's space limit.

The complete steps to create a tablespace for Oracle 10g are described here, if you want to learn more about Oracle database, you can take a look at the article here: http://database.51cto.com/oracle/, I believe it will bring you the harvest!

reprinted from:http://database.51cto.com/art/201108/287645.htm