oracle11g建立表空间和用户,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;