OCP-1Z0-052-V8.02-143题

143. You execute a command to resize a data file, sales.dbf, of size 200 MB in your database:

ALTER DATABASE DATAFILE '/remorse/sales.dbf' RESIZE 150M;

Which statement is true about this command?

A.The command must be issued in MOUNT state.

B.The command succeeds only if the data file is offline.

C.The command must be issued when the database is in the archivelog mode.

D.The command succeeds if no extent in the tablespace is currently allocated above the 150 MB

boundary of the file.

Answer: D

答案解析：

sys@TEST0924> select NAME,LOG_MODE,OPEN_MODE from v$database;

NAME LOG_MODE OPEN_MODE

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

TEST0924 NOARCHIVELOG READ WRITE

如果是mount，即open_mode为mount，此处表示为open。

sys@TEST0924> select file_name,status,online_status,bytes/1024/1024 m from dba_data_files;

FILE_NAME STATUS ONLINE_ M

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

/u01/app/oracle/oradata/test0924/users01.dbf AVAILABLE ONLINE 5

/u01/app/oracle/oradata/test0924/undotbs01.dbf AVAILABLE ONLINE 100

/u01/app/oracle/oradata/test0924/sysaux01.dbf AVAILABLE ONLINE 650

/u01/app/oracle/oradata/test0924/system01.dbf AVAILABLE SYSTEM 730

/u01/app/oracle/oradata/test0924/example01.dbf AVAILABLE ONLINE 345.625

/u01/app/oracle/oradata/test0924/lxtb01.dbf AVAILABLE ONLINE 10

6 rows selected.

在数据库read write即open状态下，非归档模式下 NOARCHIVELOG ，数据文件lxtb01.dbf online下，尝试改变它的大小

sys@TEST0924> alter database datafile '/u01/app/oracle/oradata/test0924/lxtb01.dbf' resize 15m;

Database altered.

sys@TEST0924> select file_name,status,online_status,bytes/1024/1024 m from dba_data_files;

FILE_NAME STATUS ONLINE_ M

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

/u01/app/oracle/oradata/test0924/users01.dbf AVAILABLE ONLINE 5

/u01/app/oracle/oradata/test0924/undotbs01.dbf AVAILABLE ONLINE 100

/u01/app/oracle/oradata/test0924/sysaux01.dbf AVAILABLE ONLINE 650

/u01/app/oracle/oradata/test0924/system01.dbf AVAILABLE SYSTEM 730

/u01/app/oracle/oradata/test0924/example01.dbf AVAILABLE ONLINE 345.625

/u01/app/oracle/oradata/test0924/lxtb01.dbf AVAILABLE ONLINE 15

6 rows selected.

可以改变成功，故ABC错误。

D答案：正确

Specify RESIZE

if you want Oracle Database to attempt to increase or decrease the size of the data file to the specified absolute size in bytes. There is no default, so you must specify a size.

If sufficient disk space is not available for the increased size, or if the file contains data beyond the specified decreased size, then Oracle Database returns an error.

指定resize来使数据库增加或减少指定的绝对大小的数据文件，没有默认值，所有必须指定一个size。

如果没有足够的空间来增加size或者原有的文件大小已经超过要减少的size，则数据库会报错。

官方参考： http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_1004.htm#sthref2349