oracle修正表空间(转)ITeye - 凯发娱乐

oracle修正表空间(转)ITeye

2019年02月18日11时34分07秒 | 作者: 浩皛 | 标签: 空间,姓名,简略 | 浏览: 2693

简略介绍
在 Oracle 10g 曾经的版别,更改表空间姓名是简直不可能的作业,除非删去,从头创立,大费周章。Oracle 10g 新添加了一项更改表空间姓名的功用,使得更改表空间姓名瞬间即可完结。是个较为人性化的功用。

SQL COL FILE_NAME format a70
SQL SET linesize 120
SQL SET pagesize 99
SQL COL TABLESPACE_NAME format a10
SQL
SQL SELECT file_name, tablespace_name FROM dba_data_files;

FILE_NAME TABLESPACE

/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/users01.dbf USERS
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/sysaux01.dbf SYSAUX
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/undotbs01.dbf UNDOTBS1
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/system01.dbf SYSTEM
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/example01.dbf EXAMPLE
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/foo1.dbf FOO


6 rows selected.

该指令的语法很简略:
/********************************语法在这里!!!*************************************************/

ALTER TABLESPACE tablespacename RENAME TO newtablespacename;

tablespacename 和newtablespacename 别离对应本来的表空间姓名和更改后的表空间姓名:

实战演练
留意:在操作前后都请做好操控文件的备份作业

SQL ALTER TABLESPACE foo RENAME TO test;

Tablespace altered.

SQL SELECT file_name, tablespace_name FROM dba_data_files;

FILE_NAME TABLESPACE

/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/users01.dbf USERS
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/sysaux01.dbf SYSAUX
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/undotbs01.dbf UNDOTBS1
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/system01.dbf SYSTEM
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/example01.dbf EXAMPLE
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/foo1.dbf TEST

由于 system 和 sysaux 这两个表空间的特殊性,是不能够更名的:

SQL ALTER TABLESPACE system RENAME TO mysystem;
ALTER TABLESPACE system RENAME TO mysystem
*
ERROR at line 1:
ORA-00712: cannot rename system tablespace

SQL ALTER TABLESPACE sysaux RENAME TO mysysaux;
ALTER TABLESPACE sysaux RENAME TO mysysaux
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace

能够对 undo tablespace 从头命名,假如运用的是 spfile ,而不是 pfile, Oracle 会主动对 spfile 中的 undo_tablespace 进行更改(不过要在数据库从头启动之后才能够观察到), 假如运用的是 pfile ,要对其进行手艺更改。咱们看看 spfile 的改变状况:

SQL ALTER tablespace undotbs1 RENAME TO undotbs;

Tablespace altered.

SQL
SQL show parameter pfile

NAME TYPE VALUE
-
spfile string /u01/app/oracle/product/10.1.0
/db_1/dbs/spfileTEST.ora
SQL show parameters undo

NAME TYPE VALUE
-
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL startup
ORACLE instance started.

Total System Global Area 180355072 bytes
Fixed Size 777996 bytes
Variable Size 128983284 bytes
Database Buffers 50331648 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL show parameters undo

NAME TYPE VALUE
-
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS
SQL

对脱机表空间的更名是不允许的:

SQL ALTER TABLESPACE TEST OFFLINE;

Tablespace altered.

SQL ALTER TABLESPACE test RENAME TO testoffline;
ALTER TABLESPACE test RENAME TO testoffline
*
ERROR at line 1:
ORA-01135: file 6 accessed for DML/query is offline
ORA-01110: data file 6:
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/foo1.dbf

给出的提示信息很有参阅价值:更名操作是要对表空间进行 DML/query 操作的,表空间offline的话,则不能够。

那么假如表空间是只读的会怎么样呢?

SQL ALTER TABLESPACE TEST ONLINE;

Tablespace altered.

SQL ALTER TABLESPACE TEST READ ONLY;

Tablespace altered.

SQL ALTER TABLESPACE test RENAME TO testreadonly;

Tablespace altered.

SQL list
1* SELECT file_name, tablespace_name FROM dba_data_files
SQL /

FILE_NAME TABLESPACE

/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/users01.dbf USERS
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/sysaux01.dbf SYSAUX
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/undotbs01.dbf UNDOTBS
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/system01.dbf SYSTEM
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/example01.dbf EXAMPLE
/u01/app/oracle/product/10.1.0/db_1/oradata/TEST/foo1.dbf TESTREADONLY


6 rows selected.

SQL

看来数据字典现已更新,不过Oracle会向alert_SID.log 中写入相似如下的日志:

ALTER TABLESPACE test RENAME TO testreadonly
Sat Nov 13 16:15:21 2004
Tablespace TEST is renamed to TESTREADONLY.
Tablespace name change is not propagated to file headersbecause the tablespace is read only.
Completed: ALTER TABLESPACE test RENAME TO testreadonly

留意Log里有个纤细的小Bug:headersbecause。这是两个词,应该空开的 :-)

更名对 Bigfile 表空间相同有用。

约束条件
使用这个特性有个首要的约束条件:COMPATIBLE 初始化参数要求为 10.0 或许更高才能够

参阅信息

Oracle Database Administrators Guide 10g Release 1 (10.1) Part Number B10739-01 ( Note 62294.1 )


版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章