Examine these two statements:
Which three are true about the MRKT tablespace?
A.
The MRKT tablespace is created as a small file tablespace, because the file size is less than
the minimum required for big file files.
B.
The MRKT tablespace may be dropped if it has no contents.
C.
Users who were using the old default tablespace will have their default tablespaces changed to
the MRKT tablespace.
D.
No more data files can be added to the tablespace.
E.
The relative file number of the tablespace is not stored in rowids for the table rows that are
stored in the MRKT tablespace.
Explanation:
incorrect:
Not A: To create a bigfile tablespace, specify the BIGFILE keyword of the CREATE TABLESPACE
statement (CREATE BIGFILE TABLESPACE …). Oracle Database automatically creates a locally
managed tablespace with automatic segment space management.
You can specify SIZE in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).
Not D: Although automatic segment space management is the default for all new permanent,
locally managed tablespaces, you can explicitly enable it with the SEGMENT SPACE
MANAGEMENT AUTO clause.
SQL> create bigfile tablespace mrkt datafile ‘/appl/oradata/cdbroot/ORA12C1/datafile/bigfile_01.dbf’ size 10M logging extent management local segment space management auto;
Tablespace created.
SQL> select tablespace_name,bigfile from dba_tablespaces where tablespace_name=’MRKT’;
TABLESPACE_NAME BIG
—————————— —
MRKT YES
A is wrong
B is wrong, since you cannot drop default tablespace unless you are setting another default tablespace
SQL> alter tablespace MRKT add datafile ‘/appl/oradata/cdbroot/ORA12C1/datafile/bigfile_02.dbf’ size 100M;
alter tablespace MRKT add datafile ‘/appl/oradata/cdbroot/ORA12C1/datafile/bigfile_02.dbf’ size 100M
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
SQL> alter database default tablespace users;
Database altered.
SQL> drop tablespace MRKT;
Tablespace dropped.
c,D,E are correct
SQL> alter tablespace MRKT add datafile ‘/appl/oradata/cdbroot/ORA12C1/datafile/bigfile_02.dbf’ size 100M;
alter tablespace MRKT add datafile ‘/appl/oradata/cdbroot/ORA12C1/datafile/bigfile_02.dbf’ size 100M
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
SQL> drop tablespace MRKT;
drop tablespace MRKT
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
SQL> alter database default tablespace users;
Database altered.
SQL> drop tablespace MRKT;
Tablespace dropped.
A – FALSE – this is TS bigfile like in command
B – FALSE :
You cannot drop a tablespace that has been designated as the default tablespace for the database. You must first reassign another tablespace as the default tablespace and then drop the old default tablespace.
CDE
C, D and E
CDE
CDE
CDE
Regarding C – if user has personal default tablespace, setting new database-level default tablespace does not change user’s default tablespace.
SQL> select property_value
2 from database_properties
3 where property_name like ‘DEFAULT_PERMANENT_TABLESPACE’;
PROPERTY_VALUE
——-
SYSAUX
SQL> create user u1 identified by u1 default tablespace system;
User created.
SQL> alter database default tablespace users;
Database altered.
SQL> select property_value
2 from database_properties
3 where property_name like ‘DEFAULT_PERMANENT_TABLESPACE’;
PROPERTY_VALUE
——-
USERS
SQL> select default_tablespace from dba_users where username = ‘U1’;
DEFAULT_TABLESPACE
——————————
SYSTEM
SQL>
CDE
C is correct. Tested.
Here is an excellent Weblog You might Obtain Intriguing that we encourage you to visit.
usually posts some incredibly fascinating stuff like this. If youre new to this site
the time to read or go to the content or internet sites we have linked to below the
Here are a few of the web pages we suggest for our visitors
please stop by the web-sites we stick to, like this one particular, as it represents our picks in the web
we like to honor lots of other online web pages on the web, even if they arent linked to us, by linking to them. Beneath are some webpages really worth checking out
The data talked about in the article are a number of the top obtainable