oracle 创建表空间建立用户-优快云博客

本文详细介绍了一种在Oracle数据库中创建表空间、用户及分配权限的方法。具体步骤包括定义不同用途的表空间如当前数据表空间、历史数据表空间等，并设置自动扩展属性；创建用户并指定默认表空间，赋予DBA等角色及多种系统权限，如创建视图、存储过程等，同时设置表空间配额不受限制。

CREATE TABLESPACE TS_ECARD_CUR DATAFILE
'D:\oracle\product\10.1.0\oradata\orcl\TS_ECARDCUR01.ORA' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TABLESPACE TS_ECARD_HIS DATAFILE
'D:\oracle\product\10.1.0\oradata\orcl\TS_ECARDHIS01.ORA' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE 1000M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TABLESPACE TS_ECARD_PHOTO DATAFILE
'D:\oracle\product\10.1.0\oradata\orcl\TS_ECARDPHOTO01.ORA' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE 1000M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE USER ECARD
IDENTIFIED BY VALUES '980541344ADA6695' --ecard
DEFAULT TABLESPACE TS_ECARD_CUR
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for ECARD
GRANT DBA TO ECARD;
GRANT RESOURCE TO ECARD WITH ADMIN OPTION;
GRANT CONNECT TO ECARD WITH ADMIN OPTION;
ALTER USER ECARD DEFAULT ROLE ALL;
-- 12 System Privileges for ECARD
GRANT DROP ANY VIEW TO ECARD WITH ADMIN OPTION;
GRANT DROP PUBLIC SYNONYM TO ECARD WITH ADMIN OPTION;
GRANT SELECT ANY DICTIONARY TO ECARD WITH ADMIN OPTION;
GRANT CREATE VIEW TO ECARD WITH ADMIN OPTION;
GRANT CREATE ANY SYNONYM TO ECARD WITH ADMIN OPTION;
GRANT CREATE PROCEDURE TO ECARD WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO ECARD WITH ADMIN OPTION;
GRANT DROP ANY SYNONYM TO ECARD WITH ADMIN OPTION;
GRANT CREATE TABLE TO ECARD WITH ADMIN OPTION;
GRANT DROP ANY PROCEDURE TO ECARD WITH ADMIN OPTION;
GRANT CREATE PUBLIC SYNONYM TO ECARD WITH ADMIN OPTION;
GRANT DROP ANY TABLE TO ECARD WITH ADMIN OPTION;
-- 2 Tablespace Quotas for ECARD
ALTER USER ECARD QUOTA UNLIMITED ON TS_ECARD_HIS;
ALTER USER ECARD QUOTA UNLIMITED ON TS_ECARD_CUR;