日期:2014-05-16  浏览次数:20428 次

ORACLE创建数据库表空间文件,创建表空间,创建用户并为用户指定表空间

1、创建表空间及表空间文件,表空间文件放在哪里可以从select * from dba_data_files ; 表中查询
create tablespace TBS_CH_YY0A datafile '/opt/CRM_yaceData/TBS_CH_YY0A.dbf' size 50m autoextend on next 50m maxsize unlimited;
create tablespace TBS_CH_SETTLE datafile '/opt/CRM_yaceData/TBS_CH_SETTLE.dbf' size 50m autoextend on next 50m maxsize unlimited;
--查看表空间信息
select * from dba_data_files;
select * from dba_tablespaces;

2、创建用时指定表空间
--yy0a
create user YY0A
identified by "yy0a"
default tablespace TBS_CH_YY0A
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to YY0A;
grant dba to YY0A;
grant resource to YY0A;
-- Grant/Revoke system privileges
grant execute any procedure to YY0A;
grant execute any type to YY0A;
grant select any dictionary to YY0A;
grant select any sequence to YY0A;
grant select any table to YY0A;
grant unlimited tablespace to YY0A;
--settle
create user settle
identified by "settle"
default tablespace TBS_CH_SETTLE
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to settle;
grant dba to settle;
grant resource to settle;
-- Grant/Revoke system privileges
grant execute any procedure to settle;
grant execute any type to settle;
grant select any dictionary to settle;
grant select any sequence to settle;
grant select any table to settle;
grant unlimited tablespace to settle;

?