创建临时表空间:
CREATE SMALLFILE TEMPORARY TABLESPACE "XXX_TEMP"
TEMPFILE '/home/oracle/oradata/xxx/XX_TEMP01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 1M
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
创建数据表空间
CREATE SMALLFILE TABLESPACE "XXX_DATA"
DATAFILE '/home/oracle/oradata/xxx/xx_DATA01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 1M
MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT
LOCAL SEGMENT SPACE MANAGEMENT AUTO;
创建索引表空间
CREATE SMALLFILE TABLESPACE "XXX_INDEX"
DATAFILE '/home/oracle/oradata/XX/XX_INDEX01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
创建大对象表空间
CREATE SMALLFILE TABLESPACE "XX_BLOB"
DATAFILE '/home/oracle/oradata/XX/XX_BLOB01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
创建用户并制定表空间
CREATE USER PZ IDENTIFIED BY USE01
DEFAULT TABLESPACE XXX_DATA
TEMPORARY TABLESPACE XXX_TEMP;
给用户分配权限
-- Grant/Revoke role privileges
grant connect to use01;
grant dba to use01 with admin option; --管理员权限
grant resource to PZ;
-- Grant/Revoke system privileges
grant create any sequence to user01; --创建序列
grant create any table to user01; --创建表
grant drop any sequence to user01; --删除序列
grant select any table to user01; --查询表
grant unlimited tablespace to user01 with admin option;
--回收UNLIMITED TABLESPACE权限
REVOKE UNLIMITED TABLESPACE FROM user01;
--设置默认表空间
alter user user01 default tablespace xxxx_DATA;
--设置默认的表空间无限配额
alter user user01 quota unlimited on xxxx_DATA;
alter user user01 quota unlimited on xxxx_INDEX;
alter user user01 quota unlimited on xxxx_BLOB;
--设置user01用户对其他表空间的quota为0
alter user user01 quota 0 on users;
alter user user01 quota 0 on sysaux;
alter user user01 quota 0 on system;