oracle改默认表空间,修改用户默认表空间和临时表空间

1.先创两个表空间,注意表空间的属性.

SYS>create tablespace  perfstat1 datafile '/u01/app/oracle/stat/perfstat1.dbf ' size 500m extent management local uniform size 256k;

SYS>create temporary tablespace tmp1 datafile '/u02/tmp/tmp1.dbf ' size 100m extent management local;    注意在"tablespace"前面加上属性词"temporary".

同样的,还可以创建另外两个表空间.

SYS>create tablespace  perfstat2 datafile '/u01/app/oracle/stat/perfstat2.dbf ' size 500m extent management local uniform size 256k;

SYS>create temporary tablespace tmp2 datafile '/u02/tmp/tmp2.dbf ' size 100m extent management local;

2.创建用户并指定默认表空间和临时表空间.

SYS>create user statpack identified by oracle default tablespace perfstat temporary tablespace tmp1;

3.修改用户的默认表空间和临时表空间.

SYS>conn statpack/oracle;切换到用户statpack.

STATPACK>alter database  default tablespace perfstat2;

STATPACK>alter database default temporary tablespace tmp2; 注意在"tablespace"前面加上属性词"temporary".

4.要想删除表空间,可以如下操作.

SYS>drop tablespace perfstat1 including contents and datafile ;  一定要包括"contents"和"datafile"才能删得彻底.

不能只删表空间内容哟!如操作

SYS>drop tablespace perfstat1;     就不能彻底删除表空间

同样删除临时表空间.

SYS>drop tablespace tmp1 including contents and datafile;