[20230224]改動資料檔案小技巧.txt

[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沒有問題.