建立表空间和用户

来源：互联网发布：简单编程教程编辑：程序博客网时间：2024/04/30 07:46

创建用户
create user zmxy identified by "zmxy";
grant create session to zmxy;
grant create table to zmxy;
grant create tablespace to zmxy;
grant create view to zmxy;

表空间
建立表空间(一般建N个存数据的表空间和一个索引空间)：
create tablespace zmxy
datafile ' F:\oracle\oradata\orcl.dbf ' size 10M
tempfile ' F:\oracle\oradata\orcl.dbf ' size 10M
autoextend on --自动增长
--还有一些定义大小的命令，看需要
default storage(
initial 100K,
next 100k,
);

eg:创建表空间
create tablespace zmxy
datafile 'F:\oracle\oradata\orcl.dbf'
size 1500M
autoextend on next 5M maxsize 3000M;
--删除表空间
drop tablespace DEMOSPACE including contents and datafiles

eg：完整例子：
--表空间
CREATE TABLESPACE zmxy
DATAFILE 'F:\tablespace\zmxy' size 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--索引表空间
CREATE TABLESPACE sdt_Index
DATAFILE 'F:\tablespace\zmxy' size 512M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

--建用户
create user zmxy identified by zmxy default tablespace zmxy;

--赋权
grant connect,resource to zmxy;
grant create any sequence to zmxy;
grant create any table to zmxy;
grant delete any table to zmxy;
grant insert any table to zmxy;
grant select any table to zmxy;
grant unlimited tablespace to zmxy;
grant execute any procedure to zmxy;
grant update any table to zmxy;
grant create any view to zmxy;

--导入导出命令
ip导出方式： exp zmxy/zmxy@127.0.0.1:1521/orcl file=f:/demo.dmp full=y
exp zmxy/zmxy@orcl file=f:/demo.dmp full=y
imp zmxy/zmxy@orcl file=f:/demo.dmp full=y ignore=y

0 0