你们都是我的客户，所以，我对你们是透明的
分类： Oracle
2016-01-13 16:54:24
查看原来表空间
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
ANTY
EPICARD
TEMP
UNDO
7 rows selected.
SQL>
查看临时表空间大小
SQL> select file_name,tablespace_name,bytes/1024/1024,autoextensible from dba_temp_files;
FILE_NAME TABLESPACE_NAME BYTES/1024/1024 AUT
---------------------------------------- ------------------------------ --------------- ---
/oracle/oradata/orcl/temp01.dbf TEMP 20 YES
新建一个临时表空间
SQL> create temporary tablespace temp2 tempfile '/oracle/oradata/orcl/temp2.dbf' size 20m autoextend on;
Tablespace created.
修改默认临时表空间
SQL> alter database default temporary tablespace temp2;
Database altered.
删除原来临时表空间
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
再次查看临时表空间
SQL> select file_name,tablespace_name,bytes/1024/1024,autoextensible from dba_temp_files;
FILE_NAME TABLESPACE_NAME BYTES/1024/1024 AUT
---------------------------------------- ------------------------------ --------------- ---
/oracle/oradata/orcl/temp2.dbf TEMP2 20 YES
SQL>
查看undo表空间大小
SQL> select file_name,tablespace_name,bytes/1024/1024,autoextensible from dba_datA_files where tablespace_name='UNDOTBS1';
FILE_NAME TABLESPACE_NAME BYTES/1024/1024 AUT
---------------------------------------- ------------------------------ --------------- ---
/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 170 YES
新建undo表空间
SQL> create undo tablespace undo2 datafile '/oracle/oradata/orcl/undo2.dbf' size 20m autoextend on;
Tablespace created.
修改undo表空间
SQL> alter system set undo_tablespace=undo2;
System altered.
删除原来undo表空间
SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.
查看一下
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
ANTY
EPICARD
TEMP2
UNDO2
7 rows selected.
SQL>
