一.创建新的表空间TSGROUP1
[root@www ~]# mkdir /u01/ts
[root@www ~]# chown -R oracle:oinstall /u01/ts
[root@www ~]# chmod 755 /u01/ts
[root@www ~]# su - oracle
[oracle@www ~]$ ll
总计 440
-rw-rw---- 1 oracle oinstall 446080 05-15 13:40 liuwei
[oracle@www ~]$ cd /u01
[oracle@www u01]$ ll
总计 12
drwxr-xr-x 3 root root 4096 05-15 12:25 app
drwxrwxr-x 2 oracle oinstall 4096 05-15 12:26 flash_recovery_area
drwxr-xr-x 2 oracle oinstall 4096 05-21 11:08 ts
[oracle@www u01]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 21 11:10:26 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create tablespace TSGROUP1
2 datafile '/u01/ts/group1db.dbf'
3 size 20m;
Tablespace created.
SQL>
二.创建用户user1
create user user1 identified by user1;
alter user user1 default tablespace TSGROUP1;
SELECT * FROM DBA_USERS WHERE USERNAME='user1';
三.授权
GRANT CREATE SESSION TO user1;
GRANT CREATE TABLE TO user1;
ALTER USER user1 QUOTA 10M ON TSGROUP1;
ALTER USER user1 QUOTA 5M ON USERS;
SELECT OWNER,TABLE_NAME,TABLESPACE_NAME,DNAME
FROM
(SELECT B.TS#,B.NAME,A.FILE#,A.NAME DNAME
FROM V$DATAFILE A, V$TABLESPACE B
WHERE A.TS# = B.TS#) T,DBA_TABLES B
WHERE OWNER='user1' AND T.NAME=B.TABLESPACE_NAME;
四.创建角色MYROLE（完）
CREATE ROLE MYROLE;
GRANT CREATE SESSION TO MYROLE;
GRANT CREATE TABLE TO MYROLE;
GRANT CREATE PROCEDURE TO MYROLE;
GRANT MYROLE TO user1;
附；sequence序列号
CREATE SEQUENCE sq_xxx
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
通过序列的伪列来访问序列的值
NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值
