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

145. Examine the commands executed in a DBA session:

SQL> CREATE BIGFILE TABLESPACE MRKT

2 DATAFILE '/u01/app/oracle/oradata/orcl/mrkt.dbf' size 10M LOGGING

3 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TABLESPACE MRKT;

Database altered.

Which two statements are true regarding the MRKT tablespace? (Choose two.)

A.No more data files can be added to the tablespace.

B.Segment space is managed by free lists in the tablespace.

C.A user created without being assigned a default tablespace uses this tablespace.

D.The tablespace can be dropped with the current setting with segments present in it.

Answer: AC  

答案解析：

1、BIGFILE只能有一个数据文件。A对

2、ALTER DATABASE DEFAULT TABLESPACE MRKT;设置默认表空间，以后用户创建的数据都会放在这个表空间里。C对。

3、创建BIGFILE时，oracle自动创建本地管理表空间和自动段空间管理（ASSM）。B错

 Oracle Database automatically creates a locally managed tablespace with automatic segment space management.

4、正在使用的默认表空间不能被drop。 D错。

实验验证：

sys@TEST1107> alter database default tablespace TESTTBS;

Database altered.

sys@TEST1107> select PROPERTY_NAME,PROPERTY_VALUE from database_properties;

PROPERTY_NAME                  PROPERTY_VALUE

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

DICT.BASE                      2

DEFAULT_TEMP_TABLESPACE        TEMP

DEFAULT_PERMANENT_TABLESPACE   TESTTBS

DEFAULT_EDITION                ORA$BASE

Flashback Timestamp TimeZone   GMT

TDE_MASTER_KEY_ID

DST_UPGRADE_STATE              NONE

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DEFAULT_TBS_TYPE               SMALLFILE

NLS_LANGUAGE                   AMERICAN

NLS_TERRITORY                  AMERICA

NLS_CURRENCY                   $

NLS_ISO_CURRENCY               AMERICA

NLS_NUMERIC_CHARACTERS         .,

NLS_CHARACTERSET               AL32UTF8

NLS_CALENDAR                   GREGORIAN

NLS_DATE_FORMAT                DD-MON-RR

NLS_DATE_LANGUAGE              AMERICAN

NLS_SORT                       BINARY

NLS_TIME_FORMAT                HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY              $

NLS_COMP                       BINARY

NLS_LENGTH_SEMANTICS           BYTE

NLS_NCHAR_CONV_EXCP            FALSE

NLS_NCHAR_CHARACTERSET         AL16UTF16

NLS_RDBMS_VERSION              11.2.0.3.0

GLOBAL_DB_NAME                 TEST1107

EXPORT_VIEWS_VERSION           8

WORKLOAD_CAPTURE_MODE

WORKLOAD_REPLAY_MODE

NO_USERID_VERIFIER_SALT        75282D7EC73B5C40AFF387B2BF5692A0

DBTIMEZONE                     00:00

36 rows selected.

sys@TEST1107> drop tablespace TESTTBS;

drop tablespace TESTTBS

*

ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

reating a Bigfile Tablespace

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, but need not, specify EXTENT MANAGEMENT LOCAL and SEGMENT SPACEMANAGEMENT AUTO in this statement. However, the database returns an error if you specify EXTENT MANAGEMENT DICTIONARY or SEGMENT SPACE MANAGEMENT MANUAL. The remaining syntax of the statement is the same as for the CREATE TABLESPACE statement, but you can only specify one data file. For example:

CREATE BIGFILE TABLESPACE bigtbs

    DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G

...

You can specify SIZE in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

If the default tablespace type was set to BIGFILE at database creation, you need not specify the keyword BIGFILE in the CREATE TABLESPACE statement. A bigfile tablespace is created by default.

If the default tablespace type was set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATESMALLFILE TABLESPACE statement to override the default tablespace type for the tablespace that you are creating.

官方参考：http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#sthref1527