2017年Oracle認證考試題庫

Oracle認證證書只是讓僱主瞭解到來應聘的人通過了Oracle數據庫方面的技術考試。下面是關於Oracle認證考試題庫，歡迎大家參考!

　　QUESTION 19

You executed the following command to create a password file in the database server:

$ orapwd file = orapworcl entries = 5 ignorecase=N

Which statement describes the purpose of the above password file?

A. It records usernames and passwords of users when granted the DBA role

B. It contains usernames and passwords of users for whom auditing is enabled

C. It is used by Oracle to authenticate users for remote database administrator

D. It records usernames and passwords of ALl users when they are added to OSDBA or OSOPER operating groups

Correct Answer: C

【考點分析】sys用户身份驗證,口令文件

【題意簡述】創建口令文件的目的是?

【原理概念】

sysdba身份遠程連接數據庫時,使用口令文件來驗證授權用户.

　　【答案剖析】

A: 錯誤,授予用户sysdba身份時,用户口令記錄在口令文件,不是dba角色.

B: 錯誤,審計用户的'密碼不會記錄在口令文件.

C: 正確, sysdba身份遠程連接數據庫時,使用口令文件來驗證授權用户.

D: 錯誤,把用户添加到操作系統OSDBA 或OSOPER組不會把口令記錄到口令文件.

QUESTION 20

SQL> CREATE BIGFILE TABLESPACE MRKT

DATAFILE '/u01/app/oracle/oradata/orcl/' 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.

Correct Answer: AC

【考點分析】大文件和小文件表空間區別, 數據默認表空間,段空間管理方式

【題意簡述】創建大文件表空間MRKT為數據庫的默認表空間,哪個描述是正確的?

　　【原理概念】

創建BIGFILE時,oracle自動創建本地管理和自動段空間管理(ASSM)的表空間.

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

Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace.

This clause overrides any default tablespace type setting for the database.

· A bigfiletablespace containsonly one datafile or tempfile, which can contain up to approximately 4 billion(232) blocks.大文件表空間只能包含一個數據文件或臨時文件.

· A smallfiletablespace is atraditional Oracle tablespace, which can contain 1022 datafiles or tempfiles,each of which can contain up to approximately 4 million (222)blocks.

使用大文件表空間的好處:

大文件表空間可以化最小表空間文件數量,從而簡化DBA管理,在大型數據庫中的表空間可能包含幾十或幾百個數據文件,隨着文件數據的增加,一些數據庫參數需要做出相應的調整比如db_files,這個靜態參數還要重啟數據庫才能生效,這都給數據庫的維護帶來不便,大文件表空間可以解決這個問題.由於文件數量減少,控制文件的空間使用也隨之減少,檢查點等數據庫內部操作需要同步的文件頭數量也大大減少.

　　【實驗參考】

(1)創建大文件表空間mrkt

SYS@ENMOEDU> create bigfile tablespace mrkt datafile '/u01/app/oracle/oradata/ENMOEDU/' size 10m;

Tablespace created.

(2)查看錶空間類型

SYS@ENMOEDU>select tablespace_name,bigfile from dba_tablespaces;

TABLESPACE_NAME BIG

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

SYSTEM NO

SYSAUX NO

TEMP NO

USERS NO

EXAMPLE NO

UNDOTBS2 NO

MRKT YES

(3)添加文件或刪除數據庫默認表空間會報錯

SYS@ENMOEDU> alter tablespace mrkt add datafile '/u01/app/oracle/oradata/ENMOEDU/' size 10m;

alter tablespace mrkt add datafile '/u01/app/oracle/oradata/ENMOEDU/' size 10m

*

ERROR at line 1:

ORA-32771: cannot add file to bigfile tablespace

SYS@ENMOEDU> alter database default tablespace mrkt;

Database altered.

SYS@ENMOEDU> drop tablespace mrkt including contents and datafiles;

drop tablespace mrkt including contents and datafiles

*

ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

(4)查看數據庫默認創建表空間類型

SYS@ENMOEDU> col PROPERTY_NAME for a20

SYS@ENMOEDU> col PROPERTY_VALUE for a15

SYS@ENMOEDU> col DESCRIPTION for a30

SYS@ENMOEDU>select * from database_properties where property_name='DEFAULT_TBS_TYPE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

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

DEFAULT_TBS_TYPE SMALLFILE Default tablespace type

(5)修改默認表空間類型:

SYS@ENMOEDU> alter database set default bigfile tablespace;

Database altered.

SYS@ENMOEDU> select * from database_properties where property_name='DEFAULT_TBS_TYPE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

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

DEFAULT_TBS_TYPE BIGFILE Default tablespace type

SYS@ENMOEDU> alter database set default smallfile tablespace;

Database altered.

SYS@ENMOEDU> select * from database_properties where property_name='DEFAULT_TBS_TYPE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

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

DEFAULT_TBS_TYPE SMALLFILE Default tablespace type

　　【答案剖析】

A: 正確,大文件表空間只能包含一個文件;

B: 錯誤,段空間是用位圖方式自動管理的不是通過free lists手工管理;

C: 正確,MRKT是數據庫默認表空間,創建用户時不指定默認表空間,則使用數據庫默認的;

D: 錯誤,正在使用的或是數據庫默認表空間不能被刪除.