----创建普通表空间:
---查看目前的表空间:
system@PROD>select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TS_XXF
TS_CTL
8 rows selected.
---创建一个新的表空间:
system@PROD>create tablespace myspace datafile
2 '/u01/app/oracle/oradata/PROD/myspace01.dbf' size 50M
3 autoextend on next 2M maxsize 100M
4 segment space management auto;
Tablespace created.
system@PROD>
#表空间创建成功。
--查看表空间与状态:
system@PROD>select tablespace_name,status
2 from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
MYSPACE ONLINE
TS_XXF ONLINE
TS_CTL ONLINE
9 rows selected.
#可知MySpace表空间添加成功,并且在线。
--修改新添加表空间到只读状态:
system@PROD>alter tablespace myspace read only;
Tablespace altered.
--查看表空间MySpace的状态:
system@PROD>select tablespace_name,status
2 from dba_tablespaces
3 where tablespace_name='MYSPACE';
TABLESPACE_NAME STATUS
------------------------------ ---------
MYSPACE READ ONLY
--把表空间重新调回读写状态:
system@PROD>alter tablespace myspace read write;
Tablespace altered.
--再次查看表空间的状态:
system@PROD>select tablespace_name,status
2 from dba_tablespaces
3 where tablespace_name='MYSPACE';
TABLESPACE_NAME STATUS
------------------------------ ---------
MYSPACE ONLINE
--调整表空间数据文件的初始大小:
system@PROD>
system@PROD>alter database datafile
2 '/u01/app/oracle/oradata/PROD/myspace01.dbf'
3 resize 60M;
Database altered.
#已经修改成功:
--查看该表空间数据文件的大小:
system@PROD>select file_name,bytes/1024/1024 as fsize
2 from dba_data_files;
FILE_NAME FSIZE
-------------------------------------------------- ----------
/u01/app/oracle/oradata/PROD/ts_ctl01.dbf 5
/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf 10
/u01/app/oracle/oradata/PROD/example01.dbf 346.25
/u01/app/oracle/oradata/PROD/users01.dbf 5
/u01/app/oracle/oradata/PROD/undotbs01.dbf 100
/u01/app/oracle/oradata/PROD/sysaux01.dbf 580
/u01/app/oracle/oradata/PROD/system01.dbf 800
/u01/app/oracle/oradata/PROD/myspace01.dbf 60
8 rows selected.
#可看到myspace01.dbf数据文件的初始大小为60M。
--查看表空间数据文件的在线状态:
system@PROD>select file_name,status
2 from dba_data_files;
FILE_NAME STATUS
-------------------------------------------------- ---------
/u01/app/oracle/oradata/PROD/ts_ctl01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/example01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/users01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/undotbs01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/sysaux01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/system01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/myspace01.dbf AVAILABLE
8 rows selected.
---创建一个测试表放在myspace01.dbf表空间:
suxing@PROD>create table sutab(id number(2),name varchar2(4),created date)
2 tablespace myspace;
Table created.
--往测试表中插入一条数据:
suxing@PROD>insert into sutab values(12,'yyuu',sysdate);
1 row created.
suxing@PROD>commit;
Commit complete.
---删除表空间:
--尝试删除表空间MySpace:
--直接删除,不附带条件:
system@PROD>drop tablespace myspace;
drop tablespace myspace
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
--添加附加条件删除表空间:
system@PROD>drop tablespace myspace including contents;
Tablespace dropped.
--查看表空间:
system@PROD>select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TS_XXF
TS_CTL
8 rows selected.
#表空间已经删除。
--查看数据文件是否删除:
[oracle@enmo ~]$ cd /u01/app/oracle/oradata/PROD/
[oracle@enmo PROD]$ ls
control01.ctl myspace01.dbf redo02.log sysaux01.dbf temp01.dbf ts_xxf_01.dbf users01.dbf
example01.dbf redo01.log redo03.log system01.dbf ts_ctl01.dbf undotbs01.dbf
[oracle@enmo PROD]$
[oracle@enmo PROD]$ du -ah
9.7M ./control01.ctl
51M ./redo03.log
3.2M ./temp01.dbf
51M ./redo01.log
5.1M ./users01.dbf
61M ./myspace01.dbf
581M ./sysaux01.dbf
801M ./system01.dbf
347M ./example01.dbf
51M ./redo02.log
101M ./undotbs01.dbf
5.1M ./ts_ctl01.dbf
11M ./ts_xxf_01.dbf
2.1G .
[oracle@enmo PROD]$
--查看用户的表是否还存在:
suxing@PROD>select tname from tab;
TNAME
------------------------------
MYTEST
T1
T2
T3
YOURTEST
#表已经删除了。
---表空间状态与对应数据文件状态的关系:
--修改表空间的状态:
system@PROD>alter tablespace myspace offline;
Tablespace altered.
--查看表空间的状态:
system@PROD>select tablespace_name,status
2 from dba_tablespaces
3 where tablespace_name='MYSPACE';
TABLESPACE_NAME STATUS
------------------------------ ---------
MYSPACE OFFLINE
--查看表空间对应的数据文件:
system@PROD>select file_name,status
2 from dba_data_files;
FILE_NAME STATUS
-------------------------------------------------- ---------
/u01/app/oracle/oradata/PROD/ts_ctl01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/example01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/users01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/undotbs01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/sysaux01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/system01.dbf AVAILABLE
/u01/app/oracle/oradata/PROD/myspace01.dbf AVAILABLE
8 rows selected.
#数据文件的状态没有变化。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2128324/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2128324/