You created a tablespace with this statement:
CREATE BIGFILE TABLESPACE adtbs
DATAFILE ‘/proddb/data/adtbs.dbf’ SIZE 10G;
The tablespace is nearly full and you need to avoid any out of space errors for the load of a
5 gig table.
Which two alter statements will achieve this?
A.
ALTER TA3LESPACE adtbs RESI2E 20G;
B.
ALTER TA3LESPACE adtbs ADD DATAFILE;
C.
ALTER TABLESPACE adtbs AUTOEXTEND ON;
D.
ALTER TA3LESPACE adtbs ADD DATAFILE ‘/proddb/data/adtbsl.dbf’ SIZE 1QG;
E.
ALTER TA3LESPACE adtbs MODIFY DATAFILE ‘/proddb/data/adtbs.dbf AUTOEXTEND
ON;
B is not a correct answer. Throw out D also. You can’t add a datafile to a bigfile tablespace. A and C seem the most probably. The syntax for E doesn’t seem correct. See here: http://www.techonthenet.com/oracle/tablespaces/alter_tablespace.php
A & C are the correct answers.
SQL> CREATE BIGFILE TABLESPACE bigfile_tbs DATAFILE ‘/orabin/nonerp/12102_home/data/CDB/PDB1/bigfile.dbf’ SIZE 1G;
Tablespace created.
SQL> ALTER TABLESPACE bigfile_tbs resize 1G;
Tablespace altered.
SQL> ALTER TABLESPACE bigfile_tbs AUTOEXTEND ON;
Tablespace altered.
SQL> ALTER TABLESPACE bigfile_tbs ADD DATAFILE;
ALTER TABLESPACE bigfile_tbs ADD DATAFILE
*
ERROR at line 1:
ORA-02236: invalid file name
SQL> ALTER TABLESPACE bigfile_tbs ADD DATAFILE ‘/orabin/nonerp/12102_home/data/CDB/PDB1/bigfile2.dbf’ size 10M;
ALTER TABLESPACE bigfile_tbs ADD DATAFILE ‘/orabin/nonerp/12102_home/data/CDB/PDB1/bigfile2.dbf’ size 10M
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
SQL> ALTER TABLESPACE bigfile_tbs MODIFY DATAFILE ‘/orabin/nonerp/12102_home/data/CDB/PDB1/bigfile.dbf’ AUTOEXTEND ON;
ALTER TABLESPACE bigfile_tbs MODIFY DATAFILE ‘/orabin/nonerp/12102_home/data/CDB/PDB1/bigfile.dbf’ AUTOEXTEND ON
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option
A y C
A and C?
AC
A and C
A, C tested.
A, C again tested 🙂
SQL> create bigfile tablespace test datafile ‘/u01/test.dbf’ size 10m;
Tablespace created.
SQL>
SQL>
SQL> desc dba_data_files;
Name Null? Type
—————————————– ——– —————————-
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> select AUTOEXTENSIBLE from dba_data_files where file_name=’/u01/test.dbf’;
AUT
—
NO
SQL> ALTER TABLESPACE TEST autoextend on;
Tablespace altered.
SQL> select AUTOEXTENSIBLE from dba_data_files where file_name=’/u01/test.dbf’;
AUT
—
YES
SQL> alter tablespace test resize 20m;
Tablespace altered.
SQL>
AC
https://docs.oracle.com/cd/E16338_01/server.112/b56299/statements_3002.htm
Great ¡V I should definitely pronounce, impressed with your website. I had no trouble navigating through all tabs as well as related info ended up being truly easy to do to access. I recently found what I hoped for before you know it in the least. Quite unusual. Is likely to appreciate it for those who add forums or anything, website theme . a tones way for your client to communicate. Nice task..
http://www.Qge2DqVF0v.com/Qge2DqVF0v
AC