160523、Oracle建立表空間和用戶

建立表空間和用戶的步驟：??
用戶??
建立：create?user?用戶名?identified?by?"密碼";??
授權：grant?create?session?to?用戶名;??
????????????grant?create?table?to??用戶名;??
????????????grant?create?tablespace?to??用戶名;??
????????????grant?create?view?to??用戶名;??

?

表空間??
建立表空間(一般建N個存數據的表空間和一個索引空間)：??
create?tablespace?表空間名??
datafile?'?路徑(要先建好路徑)\***.dbf??'?size?*M??
tempfile?'?路徑\***.dbf?'?size?*M??
autoextend?on??--自動增長??
--還有一些定義大小的命令，看需要??
?default?storage(??
?initial?100K,??
?next?100k,??
);??

例子：創建表空間??
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?用戶名?quota?unlimited?on?表空間;??
或?alter?user?用戶名?quota?*M?on?表空間;??

完整例子：

?

--表空間??
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;??

--導入導出命令?????ip導出方式：?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?
--導入導出命令?????
ip導出方式：?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?