Which two ALTER statements are correct in this scenario? (Choose two.)

You created a tablespace with the following statement:

CREATE BIGFILE TABLESPACE adtbs
DATAFILE ‘/probdb/data/adtbs.dbf’ SIZE 10G;

There is now a requirement to increase the size of the tablespace. Which two ALTER statements are correct in this scenario? (Choose two.)

You created a tablespace with the following statement:

CREATE BIGFILE TABLESPACE adtbs
DATAFILE ‘/probdb/data/adtbs.dbf’ SIZE 10G;

There is now a requirement to increase the size of the tablespace. Which two ALTER statements are correct in this scenario? (Choose two.)

A.
ALTER TABLESPACE adtbs RESIZE 20G;

B.
ALTER TABLESPACE adtbs ADD DATAFILE;

C.
ALTER TABLESPACE adtbs AUTOEXTEND ON;

D.
ALTER TABLESPACE adtbs ADD DATAFILE’/proddb/data/adtbs1.dbf’ SIZE 10G;

E.
ALTER TABLESPACE adtbs MODIFY DATAFILE ‘/proddb/data/adtbs.dbf’ AUTOEXTEND ON;

Explanation:
C:\sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Sat Jan 28 20:34:23 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> CREATE BIGFILE TABLESPACE adtbs
DATAFILE’C:\adtbs.dbf’ SIZE 1G;
Tablespace created.
(A)
SQL> ALTER TABLESPACE adtbs RESIZE 2G
Tablespace altered.

(B) and (D)
SQL> ALTER TABLESPACE adtbs ADD DATAFILE’C:\adtbs.dbf’ SIZE 1G; ALTER TABLESPACE adtbs ADD DATAFILE ‘C:\adtbs.dbf’ SIZE 1G ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
(C)
SQL> ALTER TABLESPACE adtbs AUTOEXTEND ON;
Tablespace altered.
(E)
SQL> ALTER TABLESPACE adtbs MODIFY DATAFILE ‘C:\adtbs.dbf’ AUTOEXTEND ON; ALTER TABLESPACE adtbs MODIFY DATAFILE ‘C:\adtbs.dbf’ AUTOEXTEND ON ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option



Leave a Reply 0

Your email address will not be published. Required fields are marked *