创建表空间
CREATE SMALLFILE TABLESPACE TUNING
DATAFILE
'/u01/oracle/oradata/orcl/tuning.dbf'
SIZE
100
M
AUTOEXTEND ON NEXT
1
M
MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE
1
M
SEGMENT SPACE MANAGEMENT AUTO
;
表空间改名
SQL> ALTER TABLESPACE TUNING RENAME TO TUNE ;
TABLESPACE ALTERED.
表空间只读
SQL> ALTER TABLESPACE USERS READ ONLY;
TABLESPACE ALTERED.
表空间恢复读写
SQL> ALTER TABLESPACE USERS READ WRITE ;
TABLESPACE ALTERED
删除表空间及数据文件
DROP TABLESPACE TS1 INCLUDING CONTENTS AND DATAFILES;
创建临时表空间
SQL> CREATE TEMPORARY TABLESPACE temp_tbs
2
TEMPFILE
'/u01/oracle/oradata/orcl/temp_tbs.dbf'
3
SIZE
10
M
4
AUTOEXTEND ON NEXT
1
M
5
MAXSIZE UNLIMITED
6
EXTENT MANAGEMENT LOCAL UNIFORM SIZE
1
M
7
;
Tablespace created.
为临时表空间添加临时数据文件
SQL> alter tablespace temp_tbs add tempfile '/u01/oracle/oradata/orcl/temp_tbs02.dbf' size 12M AUTOEXTEND ON NEXT 1M
2 MAXSIZE 10G ;
Tablespace altered.
删除临时表空间临时数据文件
SQL> alter tablespace temp_tbs DROP tempfile '/u01/oracle/oradata/orcl/temp_tbs02.dbf' ;
Tablespace altered
##############################################################
数据文件操作
为表空间添加数据文件
ALTER TABLESPACE TUNING ADD DATAFILE
'/u01/oracle/oradata/orcl/tuning02.dbf'
SIZE
100
M AUTOEXTEND ON NEXT
1
M MAXSIZE UNLIMITED ;
重置数据文件大小
ALTER DATABASE DATAFILE
'/u01/oracle/oradata/orcl/tuning02.dbf'
RESIZE
101
M ;
数据文件离线
SQL> ALTER DATABASE DATAFILE
'/u01/oracle/oradata/orcl/tuning02.dbf'
OFFLINE ;
ALTER DATABASE DATAFILE
'/u01/oracle/oradata/orcl/tuning02.dbf'
OFFLINE
*
ERROR AT LINE
1
:
ORA-
01145
: OFFLINE IMMEDIATE DISALLOWED UNLESS MEDIA RECOVERY ENABLED
数据文件强制离线
SQL> ALTER DATABASE DATAFILE
'/u01/oracle/oradata/orcl/tuning02.dbf'
OFFLINE FOR DROP ;
DATABASE ALTERED.
再次上线 提示需要恢复
SQL> RECOVER DATAFILE
'/u01/oracle/oradata/orcl/tuning02.dbf'
;
MEDIA RECOVERY COMPLETE.
SQL> ALTER DATABASE DATAFILE
'/u01/oracle/oradata/orcl/tuning02.dbf'
ONLINE ;
DATABASE ALTERED.
数据文件重命名,改变位置
SQL> ALTER DATABASE DATAFILE
'/u01/oracle/oradata/orcl/tuning02.dbf'
OFFLINE FOR DROP;
DATABASE ALTERED.
SQL> !
[ORACLE@SUNBLAZE ~]$ cp /u01/oracle/oradata/orcl/tuning02.dbf .
SQL> ALTER DATABASE RENAME FILE
'/u01/oracle/oradata/orcl/tuning02.dbf'
TO
'/home/oracle/tuning02.dbf'
;
DATABASE ALTERED.
SQL> RECOVER DATAFILE
'/home/oracle/tuning02.dbf'
;
MEDIA RECOVERY COMPLETE.
SQL> ALTER DATABASE DATAFILE
'/home/oracle/tuning02.dbf'
ONLINE ;
DATABASE ALTERED.
SQL> SELECT * FROM V$DATFILE ;
SELECT * FROM V$DATFILE
*
ERROR AT LINE
1
:
ORA-
00942
: TABLE OR VIEW DOES NOT EXIST
SQL> SELECT * FROM V$DATAFILE ;
进行验证
第二种方法
1
表空间离线
2
移动数据文件
3
在数据库中重命名数据文件
4
表空间上线
5
表空间上线
SQL> ALTER TABLESPACE TUNING OFFLINE ;
TABLESPACE ALTERED.
SQL> !
[ORACLE@SUNBLAZE ~]$ cp ./tuning02.dbf /u01/oracle/oradata/orcl/tuning02.dbf
[ORACLE@SUNBLAZE ~]$ EXIT
EXIT
SQL> ALTER DATABASE RENAME FILE
'/home/oracle/tuning02.dbf'
TO
'/u01/oracle/oradata/orcl/tuning02.dbf'
;
DATABASE ALTERED.
SQL> ALTER TABLESPACE TUNING ONLINE ;
TABLESPACE ALTERED.
SQL> SELECT * FROM V$DATAFILE ;