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: 錯誤,正在使用的或是資料庫預設表空間不能被刪除.
