oracle 创建表空间，用户及给用户赋权限

最新推荐文章于 2023-11-16 21:18:19 发布

原创最新推荐文章于 2023-11-16 21:18:19 发布 · 2.1k 阅读

本内容遵循CC 4.0 BY-SA版权协议

本文详细介绍了如何在Oracle数据库中创建临时表空间、数据表空间及索引表空间，并通过具体SQL语句展示了如何创建用户并分配相应的表空间权限。

CREATE TEMPORARY TABLESPACE TS_MES_TEMP
              TEMPFILE 'D:\Oracle\oradata\xxx\TS_MES_TEMP_01.DBF' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED,
                              'D:\Oracle\oradata\xxx\TS_MES_TEMP_02.DBF' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED,
                              'D:\Oracle\oradata\xxx\TS_MES_TEMP_03.DBF' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED,
                              'D:\Oracle\oradata\xxx\TS_MES_TEMP_04.DBF' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED,
                              'D:\Oracle\oradata\xxx\TS_MES_TEMP_05.DBF' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
          EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;

CREATE TABLESPACE TS_MES_TAB
               DATAFILE 'D:\Oracle\oradata\xxx\TS_MES_TAB_01.DBF' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
               LOGGING
               EXTENT MANAGEMENT LOCAL
               SEGMENT SPACE MANAGEMENT AUTO
               UNIFORM SIZE 256K ;

CREATE TABLESPACE TS_MES_IDX
               DATAFILE 'd:\oracle\oradata\xxx\TS_MES_IDX_01.DBF' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
               LOGGING
               EXTENT MANAGEMENT LOCAL
               SEGMENT SPACE MANAGEMENT AUTO
               UNIFORM SIZE 256K ;

create user xx(用户名) identified by xx(密码) default tablespace ts_mes_tab temporary tablespace ts_mes_temp account unlock ;

GRANT CONNECT,RESOURCE,DBA TO xx(对应的用户名);

先要创建相应的文件夹D:\Oracle\oradata\xxx