oracle表空间创建

创建临时表空间：

    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;