oracle 数据文件管理

绝地灬酷狼 2022-07-12 10:17 161阅读 0赞

author：skate

time：2010-05-25

--------------------

数据文件管理

**1. datafile add  
2. datafile delete  
3. datafile resize  
4. datafile rename**

**1. datafile add**

添加表空间，从而添加datafile

SQL> create tablespace tbs\_skate  
  2  datafile '+datagroup' size 20m;

Tablespace created

SQL>  select tablespace\_name,status,extent\_management,segment\_space\_management  from dba\_tablespaces  
  2  ;

TABLESPACE\_NAME                STATUS    EXTENT\_MANAGEMENT SEGMENT\_SPACE\_MANAGEMENT  
\------------------------------ --------- ----------------- ------------------------  
SYSTEM                         ONLINE    LOCAL             MANUAL  
UNDOTBS1                       ONLINE    LOCAL             MANUAL  
SYSAUX                         ONLINE    LOCAL             AUTO  
TEMP                           ONLINE    LOCAL             MANUAL  
USERS                          ONLINE    LOCAL             AUTO  
GPTBS                          ONLINE    LOCAL             AUTO  
TBS\_SKATE                      ONLINE    LOCAL             AUTO

7 rows selected

SQL>  select name,status,bytes from v$datafile;

NAME                                                                             STATUS       BYTES  
\-------------------------------------------------------------------------------- ------- ----------  
\+DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240  
\+DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160  
\+DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640  
\+DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880  
\+DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600  
\+DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760  
\+DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745                                 ONLINE    20971520

7 rows selected

SQL>

给表空间新增加datafile

SQL> alter tablespace tbs\_skate add  
  2  datafile '+datagroup' size 20m;

Tablespace altered

SQL>  select name,status,bytes from v$datafile;

8 rows selected

SQL>

**2. datafile delete**

SQL> alter database datafile 8 offline;

alter database datafile 8 offline

**ORA-01145: offline immediate disallowed unless media recovery enabled**

出现这个错误的原因是因为这个库是noarchivelog模式的不能直接offline，要用offline drop，而如果是archivelog模式，使用哪个那就无所谓了。再用offline drop删除datafile时候，在dba\_data\_files和v$datafile视图里都存在，只是相应的字段信息已经改变；并且在v$recover\_file也有相应的信息，目的是为了恢复。

SQL> alter database datafile 8 offline drop;

Database altered

SQL>  select file\#,name,status,bytes from v$datafile;

FILE\# NAME                                                                             STATUS       BYTES  
\---------- -------------------------------------------------------------------------------- ------- ----------  
         1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240  
         2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160  
         3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640  
         4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880  
         5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600  
         6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760  
         7 +DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745                                 ONLINE    20971520  
         8 +DATAGROUP/dbgp/datafile/tbs\_skate.273.719949047                                 RECOVER   20971520

8 rows selected

SQL> select \* from v$recover\_file;

FILE\# ONLINE  ONLINE\_STATUS ERROR                 CHANGE\# TIME  
\---------- ------- ------------- ------------------ ---------- -----------  
         8 OFFLINE OFFLINE                             5283160 2010-5-25 1

SQL>

视图v$recover\_file里存在记录，说明有需要恢复的文件，需要recover

SQL> alter tablespace tbs\_skate drop datafile 8;

alter tablespace tbs\_skate drop datafile 8

ORA-03264: cannot drop offline datafile of locally managed tablespace

这个错误提示不能删除本地管理的offline的datafile，那我就把它online

SQL> alter database datafile 8  online;

alter database datafile 8  online

ORA-01113: file 8 needs media recovery  
ORA-01110: data file 8: '+DATAGROUP/dbgp/datafile/tbs\_skate.273.719949047'

结果提示需要恢复文件，那就恢复数据文件8

SQL> recover datafile 8;  
Media recovery complete.  
SQL>

恢复完后确定datafile 8的状态，然后online数据文件

SQL> select \* from v$recover\_file;

FILE\# ONLINE  ONLINE\_STATUS ERROR                 CHANGE\# TIME  
\---------- ------- ------------- ------------------ ---------- -----------

SQL>  select file\#,name,status,bytes from v$datafile;

8 rows selected

SQL> alter database datafile 8 online;

Database altered

SQL>

这回就可以删除数据文件8了

SQL> alter tablespace tbs\_skate  drop datafile 8;

Tablespace altered

SQL> select \* from v$recover\_file;

FILE\# ONLINE  ONLINE\_STATUS ERROR                 CHANGE\# TIME  
\---------- ------- ------------- ------------------ ---------- -----------

SQL>  select file\#,name,status,bytes from v$datafile;

7 rows selected

SQL>

还可以通过数据文件名字直接删除数据文件

SQL>  select file\#,name,status,bytes from v$datafile;

7 rows selected

SQL> alter tablespace tbs\_skate drop datafile '+DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745';

alter tablespace tbs\_skate drop datafile '+DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745'

ORA-03261: the tablespace TBS\_SKATE has only one file

这个错误说明表空间里必须要有至少一个数据文件

SQL> alter tablespace tbs\_skate add datafile '+datagroup' size 20m;

Tablespace altered

SQL>  select file\#,name,status,bytes from v$datafile;

FILE\# NAME                                                                             STATUS       BYTES  
\---------- -------------------------------------------------------------------------------- ------- ----------  
         1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240  
         2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160  
         3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640  
         4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880  
         5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600  
         6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760  
         7 +DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745                                 ONLINE    20971520  
         8 +DATAGROUP/dbgp/datafile/tbs\_skate.273.719951265                                 ONLINE    20971520

8 rows selected

SQL> alter tablespace tbs\_skate drop datafile '+DATAGROUP/dbgp/datafile/tbs\_skate.273.719951265';

Tablespace altered

SQL>

**3. datafile resize**

SQL>  select file\#,name,status,bytes from v$datafile;

FILE\# NAME                                                                             STATUS       BYTES  
\---------- -------------------------------------------------------------------------------- ------- ----------  
         1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240  
         2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160  
         3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640  
         4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880  
         5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600  
         6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760  
       **  7 +DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745                                 ONLINE    20971520**

7 rows selected

SQL> alter database datafile '+DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745' **resize** 40m;

Database altered

SQL>  select file\#,name,status,bytes from v$datafile;

FILE\# NAME                                                                             STATUS       BYTES  
\---------- -------------------------------------------------------------------------------- ------- ----------  
         1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240  
         2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160  
         3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640  
         4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880  
         5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600  
         6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760  
         **7 +DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745                                 ONLINE    41943040**

7 rows selected

SQL> alter database datafile '+DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745' **resize** 10m;

Database altered

SQL>  select file\#,name,status,bytes from v$datafile;

FILE\# NAME                                                                             STATUS       BYTES  
\---------- -------------------------------------------------------------------------------- ------- ----------  
         1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240  
         2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160  
         3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640  
         4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880  
         5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600  
         6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760  
         **7 +DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745                                 ONLINE    10485760**

7 rows selected

SQL>

**4. datafile rename**

SQL> alter database rename file '/tmp/tbs\_skate02' to '/tmp/tbs\_skate03';

alter database rename file '/tmp/tbs\_skate02' to '/tmp/tbs\_skate03'

ORA-01511: error in renaming log/data files  
ORA-01121: cannot rename database file 8 - file is in use or recovery  
ORA-01110: data file 8: '/tmp/tbs\_skate02'

错误提示文件在使用

SQL> alter tablespace tbs\_skate offline;

Tablespace altered

SQL> alter database rename file '/tmp/tbs\_skate02' to '/tmp/tbs\_skate03';

alter database rename file '/tmp/tbs\_skate02' to '/tmp/tbs\_skate03'

ORA-01511: error in renaming log/data files  
ORA-01141: error renaming data file 8 - new file '/tmp/tbs\_skate03' not found  
ORA-01110: data file 8: '/tmp/tbs\_skate02'  
ORA-27037: unable to obtain file status  
Linux-x86\_64 Error: 2: No such file or directory  
Additional information: 3

要先把文件'/tmp/tbs\_skate02' 物理的copy到'/tmp/tbs\_skate03' ，然后在继续执行就会ok

SQL> alter database rename file '/tmp/tbs\_skate02' to '/tmp/tbs\_skate03';

Database altered

SQL>  select file\#,name,status,bytes from v$datafile;

FILE\# NAME                                                                             STATUS       BYTES  
\---------- -------------------------------------------------------------------------------- ------- ----------  
         1 +DATAGROUP/dbgp/datafile/system.256.714060783                                    SYSTEM   513802240  
         2 +DATAGROUP/dbgp/datafile/undotbs1.258.714060783                                  ONLINE    36700160  
         3 +DATAGROUP/dbgp/datafile/sysaux.257.714060783                                    ONLINE   408944640  
         4 +DATAGROUP/dbgp/datafile/users.259.714060783                                     ONLINE     5242880  
         5 +DATAGROUP/dbgp/datafile/gptbs.270.714914383                                     ONLINE   104857600  
         6 +DATAGROUP/dbgp/datafile/gptbs.271.719943181                                     ONLINE    10485760  
         7 +DATAGROUP/dbgp/datafile/tbs\_skate.272.719947745                                 OFFLINE          0  
      **   8 /tmp/tbs\_skate03                                                                 OFFLINE          0**

8 rows selected

SQL> alter tablespace tbs\_skate online;

Tablespace altered

SQL>

要是asm文件，除了recover外，我还真不知道如何直接重命名，因为在rename之前，要先物理的把文件cp过去，asm在10g里没有相应的命令  
在11g里有了cp命令。

**相关文档：**

[http://blog.csdn.net/wyzxg/archive/2008/03/25/2218130.aspx][http_blog.csdn.net_wyzxg_archive_2008_03_25_2218130.aspx]

[http://blog.csdn.net/wyzxg/archive/2008/04/15/2294654.aspx][http_blog.csdn.net_wyzxg_archive_2008_04_15_2294654.aspx]

\------end------

[http_blog.csdn.net_wyzxg_archive_2008_03_25_2218130.aspx]: http://blog.csdn.net/wyzxg/archive/2008/03/25/2218130.aspx
[http_blog.csdn.net_wyzxg_archive_2008_04_15_2294654.aspx]: http://blog.csdn.net/wyzxg/archive/2008/04/15/2294654.aspx