[20230224]改动数据文件小技巧.txt

--//有时候需要resize数据文件,我一般使用文件名,今天看别人操作可以直接使用文件号完成,这样简单明了,不用输入文件名.减少出错
--//的概率,测试看看.

--//例子:
alter database tempfile 1 resize 1024M;
alter database tempfile 1 autoextend off;

1.环境:
SYS@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试:
SYS@book> alter database tempfile 1 autoextend off;
Database altered.

SYS@book> select * from V$TEMPFILE
  2  @ pr
==============================
FILE#                         : 1
CREATION_CHANGE#              : 925793
CREATION_TIME                 : 2015-11-24 09:11:14
TS#                           : 3
RFILE#                        : 1
STATUS                        : ONLINE
ENABLED                       : READ WRITE
BYTES                         : 158334976
BLOCKS                        : 19328
CREATE_BYTES                  : 20971520
BLOCK_SIZE                    : 8192
NAME                          : /mnt/ramdisk/book/temp01.dbf
PL/SQL procedure successfully completed.
--//158334976/1024/1024 = 151M.

SYS@book> alter database tempfile 1 resize 500M;
Database altered.

SYS@book> select * from V$TEMPFILE
  2  @ pr
==============================
FILE#                         : 1
CREATION_CHANGE#              : 925793
CREATION_TIME                 : 2015-11-24 09:11:14
TS#                           : 3
RFILE#                        : 1
STATUS                        : ONLINE
ENABLED                       : READ WRITE
BYTES                         : 524288000
BLOCKS                        : 64000
CREATE_BYTES                  : 20971520
BLOCK_SIZE                    : 8192
NAME                          : /mnt/ramdisk/book/temp01.dbf
PL/SQL procedure successfully completed.

--//修改回来.
SYS@book> alter database tempfile 1 resize 151M;
Database altered.

--//测试改动数据文件可以吗?测试如下:
SYS@book> alter database datafile 4 resize 380M;
Database altered.

--//OK没有问题.