Summarize and organize knowledge points on creating users and table spaces in Oracle

This article brings you relevant knowledge about Oracle. The Oracle database is commonly used in JAVAEE projects. As a background program developer, you can master how to create users and table spaces in Oracle. It is an essential skill. The following introduces the relevant information about creating users and table spaces in Oracle. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

Oracle-Create User

View table space

select 
tablespace_name, file_id, file_name, 
round(bytes/(1024*1024),0) total_space_MB 
from 
dba_data_files 
order 
by 
tablespace_name;

Oracle Create table space

create tablespace 表空间名字 --空间名
datafile '数据存储路径\***.dbf' --路径
size 5M --初始大小
autoextend on next 5M --表空间每次增长大小
maxsize 3000M --表空间最大内存

Oracle Modify table space size

alter database datafile 'E:\DATA\ORACLE\.DBF' resize 20480m

—Create user

create user "****" identified by xyx2021 default tablespace "****";

—User authorization

grant connect, resource to "***";
grant create session to "***";

—Modify user BEE table space

Alter user BEE default tablespace BEE

-Delete table space

drop tablespace fish;

Summary

Oracle installation completed Finally, there is a default database. In addition to this default database, we can also create our own database.

In order to avoid trouble, you can use the 'Database Configuration Assistant' wizard to create the database (this step must be created, because if it is not done well here, an error will occur when creating the table space - I spent a few days here. hours, dizzy).

After creating the database, you cannot create a table in the database immediately. You must first create a user for the database and specify a table space for the user.

The following is the specific process of creating a database user:

• 1. If the name has been created now It is the database of 'test'. At this time, the test directory already exists in the d:\oracle\oradata\ directory (Note: My Oracle11g is installed under d:\oracle. If your Oracle is installed in another directory, Then your newly created database directory will be under the *\oradata\ directory).

• #2. Before creating a user, you must first create a table space:

The format is: Format: create tablespace inter-table name datafile 'data file name' size table space size;

For example: SQL> create tablespace test_tablespace datafile 'd:\oracle\oradata\test\test.dbf' size 100M;

Where 'test_tablespace' is the name of your customized table space, you can name it arbitrarily;

'd:\oracle\oradata\test\test.dbf' is the storage location of the data file, 'test. The dbf' file name is also arbitrary;

'size 100M' specifies the size of the data file, which is the size of the table space.

Delete namespace

DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

• 3. Now create a file named 'test_tablespace ' tablespace, the user can be created as follows:

The format is: Format: create user username identified by password default tablespace tablespace table;

For example: SQL> create user testone identified by testone default tablespace test_tablespace;

The default tablespace 'default tablespace' uses the tablespace created above.

• 4. Then authorize the newly created user:

##SQL> grant connect, resource to testone; – means Grant connect and resource permissions to the testone user

SQL> grant dba to testone; – Indicates that the authorization of dba permissions to the testone user is successful.

ok! The database user is created. Now you can use this user to create data tables!

Recommended tutorial: "

Oracle Video Tutorial"

The above is the detailed content of Summarize and organize knowledge points on creating users and table spaces in Oracle. For more information, please follow other related articles on the PHP Chinese website!