Oracle建立表空间和用户
1.建立表空间和用户的步骤:
用户:
建立:create user leon identified by 111111;
授权:grant connect,resource,dba to leon;
表空间:
建立表空间(一般建N个存数据的表空间和一个索引空间):
create tablespace leon_space
datafile 'D:\Oracle\oradata\orcl\leon_space.dbf' size 500m
tempfile 'D:\Oracle\oradata\orcl\leon_temp.dbf' size 100m
autoextend on next 50m maxsize 2048m;
查看当前用户默认表空间:
select username,default_tablespace from user_users;
创建用户同时指定表空间:
create user leon identified by 111111 default tablespace leon_space;
例子:
创建表空间
create tablespace DEMOSPACE
datafile 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf'
size 1500M autoextend on next 5M maxsize 3000M;
删除表空间
drop tablespace DEMOSPACE including contents and datafiles
用户权限
授予用户使用表空间的权限:
alter user leon quota unlimited on leon_space;
或 alter user leon quota 500M on leon_space;
修改用户的默认表空间:
alter user leon default tablespace leon_space;
完整例子:
--表空间
CREATE TABLESPACE sdt
DATAFILE 'F:\tablespace\demo' size 800M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--索引表空间
CREATE TABLESPACE sdt_Index
DATAFILE 'F:\tablespace\demo' size 512M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--2.建用户
create user demo identified by demo
default tablespace demo;
--3.赋权
grant connect,resource to demo;
grant create any sequence to demo;
grant create any table to demo;
grant delete any table to demo;
grant insert any table to demo;
grant select any table to demo;
grant unlimited tablespace to demo;
grant execute any procedure to demo;
grant update any table to demo;
grant create any view to demo;
四步法创建用户及表控件:
/*第1步:创建临时表空间 */
create temporary tablespace user_temp
tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第2步:创建数据表空间 */
create tablespace user_data
logging
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第3步:创建用户并指定表空间 */
create user username identified by password
default tablespace user_data
temporary tablespace user_temp;
/*第4步:给用户授予权限 */
grant connect,resource,dba to username;
导入导出命令:
exp demo/demo@127.0.0.1:1521/orcl file=f:/f.dmp full=y
exp demo/demo@orcl file=f:/f.dmp full=y
imp demo/demo@orcl file=f:/f.dmp full=y ignore=y