创建普通表空间并维护

----创建普通表空间：

---查看目前的表空间：

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/