• Oracle中的用户和表空间

    文章目录

    Oracle中的用户和表空间

    一、用户和表空间简介

    命令和SQL语句

    • 命令可以不用 ; 结尾，SQL语句需要 ; 结尾

    用户(库)和表空间(表)关系

    • Orcale中用户(账号)就是MySQL中的库
    • Orcale中的表空间就是MySQL中的表

    用户

    • system等同于root用户，最大权限用户，使用时最好单独创建一个账号，单独分配权限

    理解表空间

    • 数据库与表空间（表空间用于存放数据库中的对象）
    • 表空间与数据文件（表空间是由一个或多个数据文件构成的）
    • 表和数据库都是存放在表空间的数据文件中

    表空间分类

    • 永久表空间（用于存放数据库中永久存放对象）
    • 临时表空间（用于存放数据库操作过程中的数据，执行结束后释放）
    • UNDO表空间（用于保存事务所修改的旧值）

    二、用户

    1、系统用户登录

    1.1、数据库在本机时
    • 用户名/密码 as sysdba|sysoper
    1.2、数据库在远程时

    • 需要修改 tnsnames.ora配置文件，通常在 C:\app\本机用户名\product\11.2.0\dbhome_1\NETWORK\ADMIN\ 目录下

      XEremote =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.18)(PORT = 1252))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

    • 用户名/密码 @服务名/IP地址 as sysdba|sysoper

    1.3、案例
    # system用户登录（本机）
SQL> system/123123

# sys用户登录（本机）
SQL> connect sys/123123 as sysdba

    • 1
    • 2
    • 3
    • 4
    • 5

    2、查看登录用户

    2.1、命令
    • show user：查看登录用户
    • desc dba_users：用户数据字典（就是数据库提供的表，用来查看数据库的信息）
    • desc user_users：用户数据字典
    2.2、案例
    SQL> show user
USER 为 "SYS"
SQL> desc dba_users
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)
 PASSWORD_VERSIONS                                  VARCHAR2(8)
 EDITIONS_ENABLED                                   VARCHAR2(1)
 AUTHENTICATION_TYPE                                VARCHAR2(8)
SQL> desc user_users
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33

    3、解锁与锁定用户

    3.1、命令
    • 锁定：alter user [username] account lock;
    • 解锁：alter user [username] account unlock;
    3.2、案例
    # 锁定 scott 用户
SQL> alter user scott account lock;

用户已更改。

# 锁定后，无法连接
SQL> connect scott/123123
ERROR:
ORA-28000: the account is locked


警告: 您不再连接到 ORACLE。

# 查看当前用户为空
SQL> show user
USER 为 ""

# 尝试解锁，发现未连接
SQL> alter user scott account unlock;
SP2-0640: 未连接

# 退出当前 sqlplus
SQL> exit

# 重新登录 sqlplus
C:\Users\Shen-Childe>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 9月 17 20:57:01 2022

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

请输入用户名:  system/123123

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

# 解锁
SQL> alter user scott account unlock;

用户已更改。

# 连接 scott 用户
SQL> connect scott/123123
已连接。

# 查看当前登录用户 scott
SQL> show user
USER 为 "SCOTT"

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49

    4、使用scott用户登录

    4.1、命令
    • scott默认密码时：tiger
    • connect scott/tiger
    4.2、案例
    # 登录 scott 用户
SQL> connect scott/123123
已连接。

# 查看当前登录用户
SQL> show user
USER 为 "SCOTT"

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    三、表空间

    1、表空间数据字典

    • desc dba_tablespaces：表空间数据字典
    • desc user_tablespaces：表空间数据字典
    SQL> desc dba_tablespaces
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 MAX_SIZE                                           NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                             VARCHAR2(9)
 CONTENTS                                           VARCHAR2(9)
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 EXTENT_MANAGEMENT                                  VARCHAR2(10)
 ALLOCATION_TYPE                                    VARCHAR2(9)
 PLUGGED_IN                                         VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
 DEF_TAB_COMPRESSION                                VARCHAR2(8)
 RETENTION                                          VARCHAR2(11)
 BIGFILE                                            VARCHAR2(3)
 PREDICATE_EVALUATION                               VARCHAR2(7)
 ENCRYPTED                                          VARCHAR2(3)
 COMPRESS_FOR                                       VARCHAR2(12)
 
SQL> desc user_tablespaces
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 MAX_SIZE                                           NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                             VARCHAR2(9)
 CONTENTS                                           VARCHAR2(9)
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 EXTENT_MANAGEMENT                                  VARCHAR2(10)
 ALLOCATION_TYPE                                    VARCHAR2(9)
 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
 DEF_TAB_COMPRESSION                                VARCHAR2(8)
 RETENTION                                          VARCHAR2(11)
 BIGFILE                                            VARCHAR2(3)
 PREDICATE_EVALUATION                               VARCHAR2(7)
 ENCRYPTED                                          VARCHAR2(3)
 COMPRESS_FOR                                       VARCHAR2(12)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52

    2、查看用户的表空间

    2.1、查看表空间
    SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEMPTEST1_TABLESPACE

已选择7行。

SQL> select tablespace_name from user_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEMPTEST1_TABLESPACE

已选择7行。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    2.2、查看默认表空间
    SQL> select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM';

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM                         TEMP


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    2.3、修改默认表空间
    • 设置用户的默认或临时表空间（更改system用户默认表空间）
    • alter user 用户名 default | temporary tablespace 表空间名
    SQL> ALTER USER system DEFAULT TABLESPACE system;

用户已更改。

    • 1
    • 2
    • 3

    3、创建表空间

    • create [temporary] tablespace 表空间名 tempfile | datafile ‘xxx.dbf’ size xxx
    # 创建永久表空间
SQL> create tablespace test1_tablespace datafile 'test1.dbf'size 10m;

表空间已创建。

# 创建临时表空间
SQL> create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10m;

表空间已创建。

# 查看永久表空间
SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
C:\APP\SHEN-CHILDE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1.DBF

# 查看临时表空间
SQL> select file_name from dba_temp_files where tablespace_name='TEMPTEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
C:\APP\SHEN-CHILDE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEMPFILE1.DBF

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    4、修改表空间

    4.1、修改联机或脱机状态
    • alter tablespace 表空间名 online|offline;
    SQL> alter tablespace test1_tablespace offline;

表空间已更改。

SQL> desc dba_tablespaces
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 MAX_SIZE                                           NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                             VARCHAR2(9)
 CONTENTS                                           VARCHAR2(9)
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 EXTENT_MANAGEMENT                                  VARCHAR2(10)
 ALLOCATION_TYPE                                    VARCHAR2(9)
 PLUGGED_IN                                         VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
 DEF_TAB_COMPRESSION                                VARCHAR2(8)
 RETENTION                                          VARCHAR2(11)
 BIGFILE                                            VARCHAR2(3)
 PREDICATE_EVALUATION                               VARCHAR2(7)
 ENCRYPTED                                          VARCHAR2(3)
 COMPRESS_FOR                                       VARCHAR2(12)
 
SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

STATUS
---------
OFFLINE

SQL> alter tablespace test1_tablespace online;

表空间已更改。

SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

STATUS
---------
ONLINE

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46

    在这里插入图片描述

    4.2、修改只读或可读写状态（默认为读写状态online）
    • alter tablespace 表空间名 read only|write
    SQL> alter tablespace test1_tablespace read only;

表空间已更改。

SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

STATUS
---------
READ ONLY

SQL> alter tablespace test1_tablespace read write;

表空间已更改。

SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

STATUS
---------
ONLINE

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    5、表空间增加数据文件

    • alter tablespace 表空间名 add datafile ‘xxx.dbf’ size xxx;
    SQL> alter tablespace test1_tablespace add datafile 'test2_file.dbf' size 10m;

表空间已更改。

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
C:\APP\SHEN-CHILDE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1.DBF
C:\APP\SHEN-CHILDE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST2_FILE.DBF

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    6、表空间删除数据文件

    • 不能删除创建时的第一个表数据文件
    • alter tablespace 表空间名 drop datafile ‘filename.dbf’
    SQL> alter tablespace test1_tablespace drop datafile 'test2_file.dbf';

表空间已更改。

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
C:\APP\SHEN-CHILDE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1.DBF

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    7、删除表空间

    • drop tablespace 表空间名 [including contents];
    • [删除包含的内容]
    SQL> drop tablespace test1_tablespace including contents;

表空间已删除。

    • 1
    • 2
    • 3
  • 相关阅读:
    【基于Matlab GUI的语音降噪系统设计】
    MyBatis快速入门
    vue3与vue2之全局变量的使用
    c++day2
    二叉树和堆
    【自学前端】HTML篇已完结（附14节视频）
    【EtherCAT】二、下载并使用TwinCAT
    【随笔】Git 高级篇 -- 分离 HEAD（十一）
    CentOS7日志文件及journalctl日志查看
    java毕业设计诚越园区垃圾分类信息科普系统Mybatis+系统+数据库+调试部署
  • 原文地址：https://blog.csdn.net/s17856147699/article/details/126909487