Oracle11g新建用户及用户表空间

/* 建立数据表空间 */
CREATE TABLESPACE SP_TAB
　　DATAFILE
　　　　'/u01/app/oracle/oradata/orcl/tab1_1.dbf' size 1024M
　　DEFAULT STORAGE ( INITIAL 5M NEXT 5M MAXEXTENTS UNLIMITED PCTINCREASE 0 )
　　ONLINE;

 

/* 建立临时表空间 */
CREATE TEMPORARY TABLESPACE SP_TEMP
　　TEMPFILE
　　　　'/u01/app/oracle/oradata/orcl/temp_1.dbf' size 256M;

 

/* 建立回滚段表空间 */
CREATE TABLESPACE SP_RBS
　　DATAFILE
　　　　'/u01/app/oracle/oradata/orcl/rbs_1.dbf' size 256M
　　DEFAULT STORAGE ( INITIAL 5M NEXT 5M MAXEXTENTS UNLIMITED PCTINCREASE 0 )
　　ONLINE;

/* 建立索引表空间 */
CREATE TABLESPACE SP_IND
　　DATAFILE
　　　　'/u01/app/oracle/oradata/orcl/ind_1.dbf' SIZE 128M
　　DEFAULT STORAGE ( INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0 )
　　ONLINE;

 

/* 建立回滚段 */
CREATE PUBLIC ROLLBACK SEGMENT SP_R01
　　TABLESPACE SP_RBS STORAGE (INITIAL 5M NEXT 5M);
CREATE PUBLIC ROLLBACK SEGMENT SP_R02
　　TABLESPACE SP_RBS STORAGE (INITIAL 5M NEXT 5M);
CREATE PUBLIC ROLLBACK SEGMENT SP_R03
　　TABLESPACE SP_RBS STORAGE (INITIAL 5M NEXT 5M);
CREATE PUBLIC ROLLBACK SEGMENT SP_R04
　　TABLESPACE SP_RBS STORAGE (INITIAL 5M NEXT 5M);
CREATE PUBLIC ROLLBACK SEGMENT SP_R05
　　TABLESPACE SP_RBS STORAGE (INITIAL 5M NEXT 5M);
CREATE PUBLIC ROLLBACK SEGMENT SP_R06
　　TABLESPACE SP_RBS STORAGE (INITIAL 5M NEXT 5M);
CREATE PUBLIC ROLLBACK SEGMENT SP_RBS_LARGE
　　TABLESPACE SP_RBS STORAGE (INITIAL 20M NEXT 20M);
ALTER ROLLBACK SEGMENT SP_R01 ONLINE;
ALTER ROLLBACK SEGMENT SP_R02 ONLINE;
ALTER ROLLBACK SEGMENT SP_R03 ONLINE;
ALTER ROLLBACK SEGMENT SP_R04 ONLINE;
ALTER ROLLBACK SEGMENT SP_R05 ONLINE;
ALTER ROLLBACK SEGMENT SP_R06 ONLINE;
ALTER ROLLBACK SEGMENT SP_RBS_LARGE ONLINE;

 

/* 建立用户 */
CREATE USER SPING IDENTIFIED BY sping123 DEFAULT TABLESPACE SP_TAB TEMPORARY TABLESPACE SP_TEMP;

 

/*授权及收回*/

GRANT CONNECT, RESOURCE TO SPING;

REVOKE CONNECT,RESOURCE FROM  SPING;

/*清除SESSION*/

select sid,serial# from v$session where username='SPING';
ALTER SYSTEM KILL SESSION '3511,14153';

 

/* 扩大系统表空间 */
ALTER TABLESPACE SYSTEM ADD DATAFILE '/home/oracle/u01/app/oracle/oradata/orcl/system02.dbf' SIZE 512M;
ALTER TABLESPACE SYSAUX ADD DATAFILE '/home/oracle/u01/app/oracle/oradata/orcl/sysaux02.dbf' SIZE 512M;
ALTER TABLESPACE USERS ADD DATAFILE '/home/oracle/u01/app/oracle/oradata/orcl/users02.dbf' SIZE 512M;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/home/oracle/u01/app/oracle/oradata/orcl/undotbs02.dbf' SIZE 512M;