Shulou(Shulou.com)06/02 Report--

This article will explain in detail the example analysis of Oracle tablespace management and user management. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Oracle introduction

Oracle (Oracle) Company

In 1977, the trio co-founded (Software Development Laboratories,SDL)

In 1979, it changed its name to Relational Software Inc.,RSI

In 1983, to highlight the core product, RSI changed its name to Oracle

On April 26, 2002, Oracle was used as a registered trademark in Chinese.

1. Oracle tablespace

Table space is an important component of Orcale database logic deconstruction. Table space can store all kinds of application objects, such as tables, indexes and so on. Each tablespace consists of one or more data files.

1.1: classification of tablespaces

Table spaces can be divided into three categories

Category description permanent tablespaces generally store data for tables, views, procedures, indexes, and so on. Oracle installs some permanent tablespaces by default. Temporary tablespaces such as SYSTEM and USERS are only used to store data of short-term activities in the system. Undo tablespaces such as sorted data are used to help roll back uncommitted transaction data. Committed data cannot be recovered here.

1.2: purpose of tablespace

(1) assign different table spaces to different users and different table spaces to different schema objects 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 beneficial to the management of disk space, to improve the performance of IWeiO, to backup and restore data, and so on.

1.3: create tablespaces

Create a tablespace with the following syntax.

CREATE TABLESPACE tbs1-- tablespace name DATAFILE'E:\ TeachCase\ OracleDB\ tbs1.DBF'-- location of the data file SIZE 1m-- initial size AUTOEXTEND ON -- automatic growth (OFF if on or off)

1.4: delete tablespace

Drop TABLESPACE tbs1-- tablespace name

Note: when the tablespace is not DROP, it cannot be deleted from the disk, and the data file can only be deleted after the DROP TABLESPACE command has been executed, otherwise Oracle will not be able to execute.

2. Oracle user rights management

When creating a new database, Orcale creates some default database users, such as Sys, System, Scott, and so on.

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. Sys users are mainly used to maintain system information and manage instances. Sys users can only log in to the system as SYSOPER or SYSDBA roles.

2.2:System

The System user is the default system administrator in Oracle and has DBA privileges. The user has internal tables and views used by the Oracle management tool. System users cannot log in to the system in the role of SYSOPER or SYSDBA, and can only log in by default.

2.3:Scott

The Scott user is a demonstration user of the Oracle database and is typically created when the database is installed. The Scott user mode contains three demonstration tables, one of which is the Emp table. Use USERS tablespaces to store schema objects.

2.4: create a custom user

In general, for security reasons, different access permissions need to be set for different data tables. At this point, you need to create different users.

A new user can access and operate the database through the following steps:

Step 1: create a user

CREATE USER accp-specify username IDENTIFIED BY accp-specify login password DEFAULT TABLESPACE tbs1-specify default tablespace, if not specified SYSTEM tablespace will be used as default tablespace TEMPORARY TABLESPACE TEMP;-specify temporary tablespace (optional)

Step 2: assign roles

The GRANT CONNECT TO accp-CONNECT role will allow users to connect to the database GRANT RESOURCE TO accp-the RESOURCE role will allow users to use the space in the database

Step 3: give operation permission

GRANT CREATE SEQUENCE TO accp-- allows the user to create a sequence GRANT SELECT ON EMP TO accp-- gives the user the right to query the EMP table (granted to the accp user from the scott user) GRANT ALL ON EMP TO accp;-- grants all permissions to the operation table

In this way, the user is considered to be created successfully. There are also some extension operations below.

Change password

ALTER USER accp-- specify the modified user name IDENTIFIED BY aptech-- specify the new password

Delete user

DROP USER accp [CASCADE];-- when there are any schema objects (tables, attempts) under the user, CASCADE must be added, otherwise the user cannot be deleted

Oracle service description

OracleServiceORA9-- to start the database, you must start OracleOraHome90TNSListener-- to start the listener of the server, OracleOraHome90HTTPServer to connect to the database from an external client-- to start the Oracle HTTP server, and to access the database from the network, such as accessing database 3 with iSQL*plus. View user and permission information

1. View all users:

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

two。 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 the user has:

Select * from dba_role_privs;select * from user_role_privs

6. View the current user's default tablespace

Select username,default_tablespace from user_users; on "Oracle tablespace management and user management example analysis" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.