A brief introduction to Oracle table space management and user management

This article brings you relevant knowledge about Oracle. It mainly talks about the introduction of Oracle table space management and user management. Table space is an important component of the logical deconstruction of Oracle database. Table space It can store various application objects, such as tables, indexes, etc. If you are interested, please take a look. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

1. Oracle table space

The table space is Oracle An important component of database logical deconstruction, table space can store various application objects, such as tables, indexes, etc. Each tablespace consists of one or more data files.

1.1: Classification of table spaces

Table spaces can be divided into three categories

Category	Description
Permanent table space	Generally saves data of tables, views, procedures, indexes, etc. Oracle will install some permanent table spaces by default, such as: SYSTEM, USERS, etc.
Temporary table spaces	are only used to save data of short-term activities in the system, such as Sorting data, etc.
Undo table space	is used to help roll back uncommitted transaction data. Committed data cannot be recovered here.

1.2: Purpose of table space

(1) Allocate different table spaces to different users and different schema objects Allocate different table spaces to facilitate the operation of user data and the management of schema objects.

(2) Different data files can be created on different disks, which is helpful for managing disk space, improving I/O performance, and backing up and restoring data.

1.3: Create table space

Create table space, the syntax is as follows.

 CREATE TABLESPACE tbs1  					 --表空间名称
 DATAFILE 'E:\TeachCase\OracleDB\tbs1.DBF'   -- 数据文件存放的位置
 SIZE 1M									 -- 初始大小
 AUTOEXTEND ON;								 -- 自动增长（开，关的话就是 OFF）

1.4: Delete table space

Drop TABLESPACE tbs1 --表空间名称;

Note: When the table space is not DROPed, it cannot be deleted from the disk, only if it is executed The data file can be deleted only after issuing the DROP TABLESPACE command, otherwise Oracle will not be able to execute it.

2. Oracle user rights management

When creating a new database, Oracle will create some default database users, such as Sys, System and Scott.
2.1: Sys
The Sys user is a super user in Oracle. All data dictionaries and views in the database are stored in the SYS schema. The Sys user is mainly used to maintain system information and manage instances. Sys users can only log in to the system with the SYSOPER or SYSDBA role.
2.2: System
The System user is the default system administrator in Oracle and has DBA authority. This user owns the internal tables and views used by Oracle management tools. The System user cannot log in to the system with the SYSOPER or SYSDBA role and can only log in in the default mode.
2.3: Scott
The Scott user is a demonstration user of the Oracle database and is generally created during database installation. Scott user mode contains 3 demonstration tables, one of which is the Emp table. Use the USERS tablespace to store schema objects.
2.4: Create a custom user
Normally, for security reasons, different access permissions need to be set for different data tables. At this point, you need to create different users.
A newly created user must go through the following steps to access and operate the database:

First step: Create user

CREATE USER accp			-- 指定用户名
IDENTIFIED BY accp			-- 指定登录密码
DEFAULT TABLESPACE tbs1		-- 指定默认表空间，如果不指定将用SYSTEM表空间作为默认表空间
TEMPORARY TABLESPACE TEMP;		-- 指定临时表空间（可选）

Second step: Assign roles

GRANT CONNECT TO accp		-- CONNECT角色将允许用户连接数据库
GRANT RESOURCE TO accp		-- RESOURCE角色允许用户使用数据库中的空间

Step 3: Grant operation permissions

GRANT CREATE SEQUENCE TO accp	-- 允许用户创建序列
GRANT SELECT ON EMP TO accp	    -- 赋予用户查询 EMP 表的权限(从scott用户授权给accp用户)
GRANT ALL ON EMP TO accp;		-- 赋予操作表的所有权限

In this way, the user will be successfully created. There are some extended operations below.

Modify password

ALTER USER accp			-- 指定修改的用户名
IDENTIFIED BY aptech		-- 指定新密码

Delete user

DROP USER accp [CASCADE];		
-- 当该用户下有任何的模式对象（表，试图）时，必须加 CASCADE,否则不能删除用户

Oracle Service Description

OracleServiceORA9				-- 启动数据库，必须启动
OracleOraHome90TNSListener		-- 启动服务器的监听程序，从外部客户端连接数据库所必须
OracleOraHome90HTTPServer		-- 启动Oracle HTTP服务器，从网络服务数据库所必须，如用iSQL*plus访问数据库

3. View user and permission information

1. View all users:

select * from dba_user;
select * from all_users;
select * from user_users;

2. View user system permissions:

select * from dba_sys_privs;
select * from all_sys_privs;
select * from user_sys_privs;

3. View user object permissions:

select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

4. View all roles:

select * from dba_roles;

5. View the roles owned by the user:

select * from dba_role_privs;
select * from user_role_privs;

6. View the current user’s default table space

select username,default_tablespace from user_users;

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of A brief introduction to Oracle table space management and user management. For more information, please follow other related articles on the PHP Chinese website!