Questions:
1. What dose BASH stand for? Who is its original developer?
2. How to set your prompt so that it displays your login name, hostname and your location in the file system hierarchy?
3. Someone wrote the following code snippet.
while read LINE
do
echo $LINE
done < `tail -f /var/log/messages`
He wished to write a script tracking changes to the system log file, /var/log/messages. Unfortunately, the above code block hangs and does nothing useful. Why? Fix this so it does work. (Hint: rather than redirecting the stdin of the loop, try a pipe.)
4. How to convert hexadecimal to decimal number in BASH scripting and vice versa?
5. Analyze the following script and explain what it does.
for f in *.foo; do
mv $f ${f%foo}bar
done
Answers:
1. BASH is an acronym which stands for Bourne-again shell. It was originally written by Brian Fox in 1987.
2. add this line to ~/.bashrc:
export PS1="\u@\h \w> "
3.
"<" is the operator of input redirection, it can accept input from a file.
example: grep search-word <filename
tail -f /var/log/messages |while read LINE
do
echo $LINE
done
CRON ERROR failed to open PAM security session
定时任务出错
解决因用户密码过期导致无法自动运行定时任务
今天发现oracle用户定时任务很久没有自动运行
检查/var/log/cron
...
Jun 21 04:30:01 DEV24-DB-1 crond[4769]: Authentication token is no longer valid; new one required
Jun 21 04:30:01 DEV24-DB-1 crond[4769]: CRON (oracle) ERROR: failed to open PAM security session: Success
Jun 21 04:30:01 DEV24-DB-1 crond[4769]: CRON (oracle) ERROR: cannot set security context
...
检查/var/log/secure
...
Jun 23 23:05:01 DEV24-DB-1 crond[8429]: pam_unix(crond:account): expired password for user oracle (password aged)
Jun 23 23:10:01 DEV24-DB-1 crond[8674]: pam_unix(crond:account): expired password for user oracle (password aged)
Jun 23 23:15:01 DEV24-DB-1 crond[8923]: pam_unix(crond:account): expired password for user oracle (password aged)
...
1.
31. You have specified the warning and critical threshold values of an application tablespace to be 60% and 70% respectively. From the tablespace space usage metrics, you find that the actual space usage has reached the specified warning threshold value, but no alerts have been generated. What could be the reason for this?
A. The EVENT parameter was not set.
B. The SQL_TRACE parameter is set to FALSE.
C. The Enterprise Manager Grid Control is not used.
D. The STATISTICS_LEVEL parameter is set to BASIC.
E. The TIMED_STATISTICS parameter is set to FALSE.
2.
49. You execute the following command to enable a session in resumable mode:
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 60;
What is the impact of a timeout on the statements being suspended?
A. The statements remain suspended for at least 60 seconds.
B. The statements are suspended for 60 seconds and then they are executed.
C. The suspended statements error out if the problem is not rectified within 60 seconds.
D. The statements are automatically suspended 60 seconds after an error is received, and then attempt to execute normally again.
C
C
教材11-21
"A resumable statement that is suspended for the time-out interval (the default is 7,200 seconds (2 hours)) reactivates itself and returns the exception to the user."
3.
58. Exhibit: Using Database Control, you have scheduled a job to shrink the TRANS table residing on the TT tablespace. The job would run at 5:00 p.m. every Friday. When you examine the space usage of the table after the completion of the job, you find that the table has not been shrunk. What could have been the reason for this?
A. The tablespace that contains the TRANS table is online.
B. The tablespace that contains the TRANS table is permanent.
C. The tablespace that contains the TRANS table is locally managed.
D. The segment space management of the tablespace that contains the TRANS table is manual in nature.
D
D
教材11-14
"You cannot execute a shrink operation on segments managed by free lists. Segments in automatic segment-space managed tablespaces can be shrunk."
4.
59. The warning and critical threshold values have been set to 85% and 97%, respectively, for one of the tablespaces. The current tablespace space usage is 54%. You modify the warning threshold to be 50 % and critical threshold to be 53% in Database Control. Which statement is true?
A. The new setting would be applied but no alerts would be raised immediately.
B. The new setting would be applied and an alert would be raised immediately.
C. The new setting would be ignored because the tablespace space usage is more than the specified threshold value.
D. The new setting would cause an error because the tablespace space usage is more than the specified threshold value.
A
A
教材11-11
"This information is aggregated every 10 minutes by the MMON process. An alert is triggered when the threshold for a tablespace has been reached or cleared. "
5.
74. While setting up the threshold for tablespace space usage metrics for one of your tablespaces, you define the critical threshold as 60% and warning threshold as 75%. What would be the result of this setting?
A. It would result in an error because the warning threshold cannot be more than the critical threshold.
B. The warning threshold is ignored and alerts would be generated when space usage exceeds the critical threshold.
C. The critical threshold is ignored and alerts would be generated when space usage exceeds the warning threshold.
D. Both threshold values are used and alerts are generated when space usage exceeds the respective threshold values.
A
A
没找到
6.
84. Exhibit: View the Exhibit and examine the characteristic of the USERS tablespace. You observe that a large volume of inserts and deletes are happening on the TRANS table in the USERS tablespace and you suspect that the TRANS table is fragmented. Which advisory component would you refer to, in order to find information about table fragmentation?
A. Memory Advisor
B. Segment Advisor
C. SQL Tuning Advisor
D. SQL Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)
B
B
教材11-19
7.
106. Which type of PL/SQL construct would you use to automatically correct the error resulting from a statement that was suspended due to a space-related problem?
A. functions
B. package
C. procedure
D. database trigger
E. anonymous PL/SQL block
D
D
教材11-25
8.
107. In an Oracle 10g database, the in-memory statistics are gathered at regular intervals and used to perform growth-trend analysis and capacity planning of the database. Which component stores these statistics?
A. Recovery catalog
B. Oracle Enterprise Manager Repository
C. Automatic Workload Repository (AWR)
D. Oracle 10g Enterprise Manager Grid Control
E. Automatic Database Diagnostic Monitor (ADDM)
C
C
教材11-9
"Information gathered is stored in the Automatic Workload Repository (AWR) and is used to perform growth trend analysis and capacity planning of the database."
9.
116. In one of your online transaction processing (OLTP) applications, users are manipulating and querying a database table simultaneously. From the Segment Advisor, you find that one of the tables is highly fragmented and you want to shrink the table immediately without affecting the currently active queries. Which option would you use with the ALTER TABLE command to achieve this objective?
A. REBUILD
B. CASCADE
C. TRUNCATE
D. ROW MOVEMENT
E. SHRINK SPACE COMPACT
F. SHRINK SPACE CASCADE
E
E
教材11-12
"The first phase does the compaction. During this phase, rows are moved to the left part of the segment as much as possible. Internally, rows are moved by packets to avoid locking issues."
10.
118. Users are performing a large volume of inserts and deletes on the application tables in the APPS tablespace. You observe that there are several warning alerts being generated for the APPS tablespace space usage metrics. Currently, the warning threshold for the tablespace usage metrics is set to 70%. To make the generated alerts more useful as a problem identification tool, you want to reduce the frequency of alert generation for the tablespace usage metrics for the APPS tablespace. What should you do?
A. Disable SQL tracing for the APPS tablespace.
B. Disable logging attributes for the APPS tablespace.
C. Modify the tablespace to be a dictionary-managed tablespace.
D. Increase the critical threshold value for the tablespace space usage metrics for the APPS tablespace.
E. Increase the warning threshold value for the tablespace space usage metric for the APPS tablespace.
E
E
教材11-10
"When the tablespace limits are reached, an appropriate alert is raised."
11.
127. In your test database, you have created the ORDERS table as an index-organized table (IOT). To facilitate faster querying, you have created a mapping table and a bitmap index on the ORDER_FILLED column. You observe that the query performance degrees when users perform a large volume of transactions. While investigating the reason, you find that the mapping table segment is fragmented, leading to poor performance. Which option would you use to defragment the mapping table without affecting the original table data?
A. Export and import the mapping table.
B. Drop and re-create the mapping table.
C. Truncate the mapping table and reinsert the values.
D. Use the ALTER TABLE .. REBUILD command to defragment the mapping table.
B
忘了, C?
A
教材11-14
However, the following objects stored in ASSM tablespaces cannot be shrunk:
Tables in clusters
Tables with LONG columns
Tables with on-commit materialized views
Tables with ROWID-based materialized views
IOT mapping tables
Tables with function-based indexes
见第9课(http://mrxiesdatabase.blogspot.com/2009/05/1z0-043-lesson-9.html)
----
PDF教材13-38
"To rebuild the mapping table, it must be dropped and re-created."
问题:
alter table xxx move nomapping;
alter table xxx move mapping table;
会锁表吗?
----
12.
142. View the Exhibit and examine the characteristics of the USERS tablespace. You observe that a large volume of inserts and deletes are happening on the TRANS table in the USERS tablespace and you suspect that the TRANS table is fragmented. Which advisory component would you refer to, in order to find information about table fragmentation? Exhibit:
A. Segment Advisor
B. SQL Access Advisor
C. SQL Tuning Advisor
D. Memory Advisor
E. Automatic Database Diagnostic Monitor (ADDM)
A
A
教材11-15
13.
154. Exhibit: View the Exhibit and examine the properties of the USERS tablespace. You execute the following statement to shrink the TRANS table existing on the USERS tablespace:
SQL> ALTER TABLE trans SHRINK SPACE CASCADE;
Which objects would be affected by this command? (Choose all that apply.)
A. the materialized views based on the TRANS table
B. the materialized views log of the TRANS table
C. the TRANS table
D. the B*Tree indexes on the TRANS table
E. the large object (LOB) segments of the TRANS table
CD
又忘了
CD
ABE为何不对?
教材11-14
However, the following objects stored in ASSM tablespaces cannot be shrunk:
Tables in clusters
Tables with LONG columns
Tables with on-commit materialized views
Tables with ROWID-based materialized views
IOT mapping tables
Tables with function-based indexes
教材11-20
Use the ALTER command to invoke segment shrink on an object. The object’s type can be one of the following: table (heap- or index-organized), partition, subpartition, LOB (data and index segment), index, materialized view, or materialized view log.
===
14.
47. Consider the index HR.IDX_PK_EMP on the table HR.EMPLOYEES and the following ALTER INDEX command:
ALTER INDEX HR.IDX_PK_EMP COALESCE;
Which of the following commands accomplishes the same task as this command? (Choose the best answer.)
A. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
B. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
C. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
A
47. A. Using the CASCADE keyword in any segment shrink operation will shrink the free space in any dependent objects such as indexes. Chapter 13 discusses segment shrink functionality.
教材11-20
"If CASCADE is specified, the shrink behavior is cascaded to all the dependent segments that support a shrink operation, except materialized views, LOB indexes, and IOT (index-organized tables) mapping tables."
15.
50. Which of the following statements is not true about segment shrink operations?
A. The compaction phase of segment shrink is done online.
B. During the compaction phase, the entire segment is locked but only for a very short period of time.
C. When the second phase of segment shrink occurs, the high watermark (HWM) is adjusted.
D. User DML can block the progress of the compaction phase until the DML is committed or rolled back.
E. Using the COMPACT keyword, the movement of the HWM can occur later during non-peak hours by running the command without the COMPACT keyword.
B
50. B. During the compaction phase, locks are held only on individual rows, causing some minor serialization with concurrent DML operations. For more information about segment shrink, see Chapter 13.
教材11-12
===
16.
1. Which data dictionary view provides the recommended action, as a SQL statement, from the Segment Advisor?
A. DBA_ADVISOR_FINDINGS
B. DBA_ADVISOR_RECOMMENDATIONS
C. DBA_ADVISOR_ACTIONS
D. DBA_ADVISOR_RATIONALE
C
1. C. The data dictionary view DBA_ADVISOR_ACTIONS contains the SQL statement(s) that the Segment Advisor supplies to implement its recommendation for segment maintenance. DBA_ADVISOR_FINDINGS contains the results of the analysis, but no SQL. DBA_ADVISOR_RECOMMENDATIONS presents one or more findings and the benefits for performing the recommendation. DBA_ADVISOR_RATIONALE provides a more detailed set of reasons why the recommendation should be implemented, along with the impact of not performing the recommendation.
17.
3. Consider the following scenario. The user SCOTT runs a query at 8:25 A.M. that receives an ORA-01555: Snapshot too old error after running for 15 minutes. An alert is sent to the DBA that the undo tablespace is incorrectly sized. At 10:15 A.M., the DBA checks the initialization parameter UNDO_RETENTION, and its value is 3600; the parameter is sized correctly. The DBA doubles the size of the undo tablespace by adding a second datafile. At 1:15 P.M., the user SCOTT runs the same query and once again receives an ORA-01555: Snapshot too old error. What happens next? (Choose the best answer.)
A. The DBA receives another alert indicating that the undo tablespace is still undersized.
B. The user SCOTT calls the DBA to report that the query is still failing.
C. The second datafile autoextends so that future queries will have enough undo space to complete when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds another datafile to the undo tablespace, and then the query runs to completion.
B
3. B. Even if the size of the undo tablespace is adjusted after an undo space problem, only one alert is sent for each 24-hour period. Therefore, the only way that the problem will be resolved promptly is for SCOTT to call the DBA, because the DBA will not receive another alert until the next day when another query fails.
见
Viewing Information About Undo(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1510)
In addition to the proactive undo space alerts, Oracle Database also provides alerts if your system has long-running queries that cause SNAPSHOT TOO OLD errors. To prevent excessive alerts, the long query alert is issued at most once every 24 hours. When the alert is generated, you can check the Undo Advisor Page of Enterprise Manager to get more information about the undo tablespace.---
18.
4. The background process __________ checks for tablespace threshold violation or clearance every __________ minutes.
A. MMON, 10
B. SMON, 10
C. TMON, 30
D. PMON, 15
E. MMON, 30
E
猜的
4. A. The new background process MMON checks for threshold violations every 10 minutes. An alert is triggered when the threshold is reached or is cleared.
教材11-11
"This information is aggregated every 10 minutes by the MMON process."
19.
6. Which of the following is not a benefit of segment shrink?
A. Full table scans will take less time.
B. Better index access takes place because of a smaller b-tree.
C. Space is freed up for other database objects.
D. All chained rows are fixed.
E. Space below the HWM is released, and the HWM is moved down.
D
6. D. While some chained rows may be fixed with segment shrink functionality, it is not guaranteed that all chained rows will be fixed, because not all blocks may be read in a segment shrink operation.
不是很理解
见第10课
---
D 见Space Reclamation(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2240)
Although segment shrink reduces row chaining, and the Oracle Database recommends online redefinition to remove chained rows, the Segment Advisor actually detects certain chained rows that are above a threshold. For example, if a row size increases during an update such that it not longer fits into the block, then the Segment Advisor recommends that the segment be reorganized to improve I/O performance.
Note: The Segment Advisor does not detect chained rows created by inserts.
---
20.
9. In the following scenario, the DBA wants to reclaim a lot of wasted space in the HR.EMPLOYEES table by using the segment shrink functionality. Which of the following is the correct order of the steps?
1. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
2. ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
3. ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
4. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
5. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
A. 3, 4, 1, 5, 2
B. 4, 1, 3, 2, 5
C. 5, 2, 1, 3, 4
D. 4, 1, 2, 3, 5
A
9. A. While the segment shrink operation could combine steps 1 and 4, the impact to the users will most likely be lessened by performing two smaller operations instead of one by specifying SHRINK SPACE COMPACT before specifying SHRINK SPACE in a subsequent operation.
见教材11-12,11-20
21.
10. Which of the following calls to DBMS_SERVER_ALERT.SET_THRESHOLD will set the thresholds for the UNDOTBS1 tablespace to 60 percent and 90 percent? (Choose the best answer.)
A.
10. D. The call to DBMS_SERVER_ALERT.SET_THRESHOLD must specify the metric TABLESPACE_PCT_FULL, the two thresholds, an object type of tablespace, and the tablespace name itself. Specifying NULL for the tablespace name will set the threshold for all tablespaces, not just the UNDOTBS1 tablespace.
22.
11. Which of the following statements is not true about segment shrink operations? (Choose the best answer.)
A. Tables with ROWID-based materialized views are maintained.
B. Segment shrink is allowed only on segments whose space is automatically managed.
C. Heap-organized and index-organized tables can be shrunk.
D. ROW MOVEMENT must be enabled for heap-organized segments.
E. Chained rows may be repaired during a segment shrink operation.
F. Triggers are not fired during a segment shrink operation.
C
忘了 A?
11. A. Because the ROWIDs are changed with a segment shrink operation, tables with ROWID-based materialized views cannot be shrunk unless the materialized views are dropped and re-created after the segment shrink operation.
教材11-14
However, the following objects stored in ASSM tablespaces cannot be shrunk:
Tables in clusters
Tables with LONG columns
Tables with on-commit materialized views
Tables with ROWID-based materialized views
IOT mapping tables
Tables with function-based indexes
23.
12. Which of the following is not a feature of the Segment Advisor within EM Database Control?
A. Growth trend analysis
B. Segment Resource Estimation
C. Finding candidates for segment shrink
D. Finding table segments with chained rows
D
?
12. D. The Segment Advisor is not used to find tables with chained rows, but instead is used for finding segments that are good candidates for segment shrink or may be growing too fast.
24.
14. Which of the following statements is not true about segment shrink operations in tablespaces with automatic segment space management?
A. Clustered tables cannot be shrunk.
B. LOB segments can be shrunk.
C. IOT mapping tables and overflow segments cannot be shrunk.
D. Tables with function-based indexes cannot be shrunk.
E. ROW MOVEMENT must be enabled for heap-based segments.
C
14. B. For segments in tablespaces with automatic segment space management, LOB segments cannot be shrunk. In addition, tables with LONG columns, on-commit materialized views, and ROWID-based materialized view cannot be shrunk. In all cases, shrink operations cannot be performed on segments managed by freelists.
有疑问, 可以收缩LOB类型的呀
教材11-20
"The object’s type can be one of the following: table (heap- or index-organized), partition, subpartition, LOB (data and index segment), index, materialized view, or materialized view log."
25.
15. Which of the following is a disadvantage of rebuilding an index instead of coalescing an index?
A. You temporarily need twice as much disk space to rebuild the index.
B. You cannot move the index to another tablespace.
C. The storage characteristics of the index cannot be changed.
D. The rebuild operation only coalesces index leaf blocks within each branch instead of re-creating the entire tree.
A
15. A. Whether you rebuild the index offline or online, you temporarily need twice as much disk space. If you rebuild the index online, you also need disk space to support a journal table to hold the intermediate changes to the index while the index is being rebuilt.
没找到
26.
18. Which of the following scenarios will never trigger Resumable Space Allocation? (Choose all that apply.)
A. SELECT * FROM HR.EMPLOYEES;
B. DELETE FROM HR.EMPLOYEES;
C. DROP TABLE HR.EMPLOYEES;
D. SELECT * FROM HR.DEPARTMENTS ORDER BY DEPARTMENT_NUMBER;
E. ALTER INDEX HR.EMP_NAME_IX REBUILD ONLINE;
F. ALTER INDEX HR.EMP_NAME_IX REBUILD;
AC
18. A, C. Unless an ORDER BY clause forces a sort operation and uses up temporary space, a SELECT command will not otherwise trigger Resumable Space Allocation. Dropping a table does not use any additional disk space in a tablespace; it frees up disk space. Therefore, it will not trigger Resumable Space Allocation.
见第10课
---
A 查询可能发生ora-1555错误, 无法恢复
---
27.
19. An AFTER SUSPEND trigger can be defined at what level? (Choose all that apply.)
A. Instance
B. Schema
C. Table
D. Session
E. Database
BE
19. B, E. The AFTER SUSPEND system-level trigger can be defined only at the database level or at the schema level.
教材11-25
"Users can register triggers for this event at both the database and schema level."
conn / as sysdba
set pages 50000 line 130
set serveroutput on size unlimited
var v_pat varchar2(1000);
var v_ns varchar2(1000);
exec :v_pat := '/var/log'
exec dbms_backup_restore.searchfiles(:v_pat, :v_ns)
select fname_krbmsft as name from x$krbmsft;
create global temporary table DIR_LIST ( filename varchar2(255) ) on commit delete rows;
create or replace
and compile java source named "DirList"
as
import java.io.*;
import java.sql.*;
public class DirList
{
public static void getList(String directory)
throws SQLException
{
File path = new File( directory );
String[] list = path.list();
String element;
for(int i = 0; i < list.length; i++)
{
element = list[i];
#sql { INSERT INTO DIR_LIST (FILENAME)
VALUES (:element) };
}
}
}
/
create or replace procedure get_dir_list( p_directory in varchar2 )
as language java
name 'DirList.getList( java.lang.String )';
/
exec get_dir_list( '/var/log' );
select * from dir_list where rownum < 10;
vi test.sh
#!/bin/bash
stime=`date +'%Y-%m-%d %H:%M:%S'`
etime=`date -d '1 day' +'%Y-%m-%d %H:%M:%S'`
echo $stime
echo $etime
echo "mysql –hx.x.x.x -utest -ptest -P3306 --column-names=false -e 'select * from test where ttime >= '"$stime"' and ttime <= '"$etime"';' dbtest"
mysql –hx.x.x.x -utest -ptest -P3306 --column-names=false -e 'select * from test where ttime >= "$stime" and ttime <= "$etime";' dbtest
bash test.sh
2009-06-11 18:25:21
2009-06-12 18:25:21
mysql –hx.x.x.x -utest -ptest -P3306 --column-names=false -e 'select * from test where ttime >= '2009-06-11 18:25:21' and ttime <= '2009-06-12 18:25:21';' dbtest
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '18:26:17 and ttime <= 2009-06-12 18:26:17' at line 1
CREATE TABLE commit_test (
id NUMBER(10),
description VARCHAR2(50),
CONSTRAINT commit_test_pk PRIMARY KEY (id)
);
CONN A/A
SET SERVEROUTPUT ON
DECLARE
function get_waits(p_event in varchar2) return number
is
l_waits NUMBER;
begin
select total_waits
into l_waits
from v$session_event
where event = p_event
and sid = (select sid from v$mystat where rownum=1);
return l_waits;
exception
when no_data_found then return 0;
end;
PROCEDURE do_loop (p_type IN VARCHAR2) AS
l_start NUMBER;
l_loops NUMBER := 1000;
l_lfs NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';
l_lfs := get_waits('log file sync');
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
INSERT INTO commit_test (id, description)
VALUES (i, 'Description for ' || i);
CASE p_type
WHEN ' ' THEN COMMIT;
WHEN 'WRITE' THEN COMMIT WRITE;
WHEN 'WRITE WAIT' THEN COMMIT WRITE WAIT;
WHEN 'WRITE NOWAIT' THEN COMMIT WRITE NOWAIT;
WHEN 'WRITE BATCH' THEN COMMIT WRITE BATCH;
WHEN 'WRITE IMMEDIATE' THEN COMMIT WRITE IMMEDIATE;
WHEN 'WRITE BATCH WAIT' THEN COMMIT WRITE BATCH WAIT;
WHEN 'WRITE BATCH NOWAIT' THEN COMMIT WRITE BATCH NOWAIT;
WHEN 'WRITE IMMEDIATE WAIT' THEN COMMIT WRITE IMMEDIATE WAIT;
WHEN 'WRITE IMMEDIATE NOWAIT' THEN COMMIT WRITE IMMEDIATE NOWAIT;
END CASE;
END LOOP;
DBMS_OUTPUT.put_line(RPAD('COMMIT ' || p_type, 30)
|| ': ' || (DBMS_UTILITY.get_time - l_start)
|| ': ' || (get_waits('log file sync') - l_lfs)
);
END;
BEGIN
do_loop(' ');
do_loop('WRITE');
do_loop('WRITE WAIT');
do_loop('WRITE NOWAIT');
do_loop('WRITE BATCH');
do_loop('WRITE IMMEDIATE');
do_loop('WRITE BATCH WAIT');
do_loop('WRITE BATCH NOWAIT');
do_loop('WRITE IMMEDIATE WAIT');
do_loop('WRITE IMMEDIATE NOWAIT');
END;
/
新增的系统参数是 COMMIT_WRITE
语法: COMMIT_WRITE = '{IMMEDIATE | BATCH},{WAIT |NOWAIT}'
可以在系统级或会话级设置, ALTER SYSTEM, ALTER SESSION
比如:
SQL> show parameter commit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength integer 1
commit_write string
max_commit_propagation_delay integer 0
SQL> alter system set commit_write='batch,nowait';
System altered.
SQL> show parameter commit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength integer 1
commit_write string batch,nowait
max_commit_propagation_delay integer 0
SQL>
conn a/a
SET SERVEROUTPUT ON
DECLARE
function get_waits(p_event in varchar2) return number
is
l_waits NUMBER;
begin
select total_waits
into l_waits
from v$session_event
where event = p_event
and sid = (select sid from v$mystat where rownum=1);
return l_waits;
exception
when no_data_found then return 0;
end;
PROCEDURE do_loop (p_type IN VARCHAR2) AS
l_start NUMBER;
l_loops NUMBER := 1000;
l_lfs NUMBER;
BEGIN
if p_type is not null then
EXECUTE IMMEDIATE 'ALTER SESSION SET COMMIT_WRITE=''' || p_type || '''';
end if;
EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';
l_lfs := get_waits('log file sync');
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
INSERT INTO commit_test (id, description)
VALUES (i, 'Description for ' || i);
COMMIT;
END LOOP;
DBMS_OUTPUT.put_line(RPAD('COMMIT_WRITE=' || p_type, 30)
|| ': ' || (DBMS_UTILITY.get_time - l_start)
|| ': ' || (get_waits('log file sync') - l_lfs)
);
END;
BEGIN
do_loop(NULL);
do_loop('WAIT');
do_loop('NOWAIT');
do_loop('BATCH');
do_loop('IMMEDIATE');
do_loop('BATCH,WAIT');
do_loop('BATCH,NOWAIT');
do_loop('IMMEDIATE,WAIT');
do_loop('IMMEDIATE,NOWAIT');
END;
/
conn a/a
set serveroutput on size unlimited
truncate table commit_test;
select total_waits
from v$session_event
where event = 'log file sync'
and sid = (select sid from v$mystat where rownum=1);
declare
l_loops number := 1000;
begin
FOR i IN 1 .. l_loops LOOP
INSERT INTO commit_test (id, description)
VALUES (i, 'Description for ' || i);
COMMIT;
END LOOP;
end;
/
select total_waits
from v$session_event
where event = 'log file sync'
and sid = (select sid from v$mystat where rownum=1);
1.
24. You are designing an application for a database and you have been asked to design a database table to facilitate monthly bill generation. The bill would include details of customer calls, listed in chronological order.Which method would you follow to achieve this objective without increasing the overhead of sorting the rows?
A. create a hash cluster to store the data
B. create an index cluster to store the data
C. create a partitioned table to store the data
D. create a sorted hash cluster to store the data
E. create a heap table with rowid to store the data
D
概念不清
D
其它选项都不行
教材10-17
2.
27. In which scenarios would you rebuild an index? (Choose all that apply.)
A. when you need to disable the index usage
B. when you need to change storage options
C. when you need to enable index monitoring
D. when you need to move the index to another tablespace
BD
BD
教材10-24
A 取消使用不就是no monitoring吗, alter index ... nomonitoring usage;
SQL> select * from v$object_usage;
no rows selected
SQL> alter index ind_t1_oid monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING END_MONITORING
------------------- -------------------
IND_T1_OID T1 YES NO
06/07/2009 05:04:56
SQL> select object_id from t1 where object_id=1;
no rows selected
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING END_MONITORING
------------------- -------------------
IND_T1_OID T1 YES YES
06/07/2009 05:04:56
SQL> alter index ind_t1_oid nomonitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING END_MONITORING
------------------- -------------------
IND_T1_OID T1 NO YES
06/07/2009 05:04:56 06/07/2009 05:05:44
SQL>
C alter index即可
3.
35. While designing your database, you have created the EMPLOYEES table as an index-organized table(IOT). You want to create a bitmap index on the JOD_ID column to make queries faster. Which task must have been completed so that you are able to create the bitmap index?
A. A primary key must have been created.
B. A mapping table must have been created.
C. An overflow tablespace must have been specified.
D. The PCTTHRESHOLD option must have been specified.
4.
49. You execute the following command to enable a session in resumable mode:
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 60;
What is the impact of a timeout on the statements being suspended?
A. The statements remain suspended for at least 60 seconds.
B. The statements are suspended for 60 seconds and then they are executed.
C. The suspended statements error out if the problem is not rectified within 60 seconds.
D. The statements are automatically suspended 60 seconds after an error is received, and then attempt to execute normally again.
A
得试试
C
看错了
PDF教材13-7
5.
58. Exhibit: Using Database Control, you have scheduled a job to shrink the TRANS table residing on the TT tablespace. The job would run at 5:00 p.m. every Friday.When you examine the space usage of the table after the completion of the job, you find that the table has not been shrunk. What could have been the reason for this?
A. The tablespace that contains the TRANS table is online.
B. The tablespace that contains the TRANS table is permanent.
C. The tablespace that contains the TRANS table is locally managed.
D. The segment space management of the tablespace that contains the TRANS table is manual in nature.
D
D
PDF教材13-17
"Only segments in Automatic Segment Space Managed (ASSM) tablespaces can be shrunk"
6.
59. The warning and critical threshold values have been set to 85% and 97%, respectively, for one of the tablespaces. The current tablespace space usage is 54%. You modify the warning threshold to be 50% and critical threshold to be 53% in Database Control. Which statement is true?
A. The new setting would be applied but no alerts would be raised immediately.
B. The new setting would be applied and an alert would be raised immediately.
C. The new setting would be ignored because the tablespace space usage is more than the specified threshold value.
D. The new setting would cause an error because the tablespace space usage is more than the specified threshold value.
A
不知道
A
不会立即报错
7.
68. You noticed that the index tablespace in your database requires a recovery. However, instead of performing a media recovery, you decided to re-create the indexes in a new tablespace.
Which two options would you use to reduce the time it takes to re-create the indexes? (Choose two.)
A. ONLINE
B. REVERSE
C. PARALLEL
D. COMPRESS
E. NOLOGGING
F. COMPUTE STATISTICS
CE
CE
ALTER INDEX ... REBUILD [ONLINE]也比re-create快, 但是索引是坏的, 只能重建
E. 如果强制打开了日志记录, NOLOGGING也不管用
8.
74. While setting up the threshold for tablespace space usage metrics for one of your tablespaces, you define the critical threshold as 60% and warning threshold as 75%. What would be the result of this setting?
A. It would result in an error because the warning threshold cannot be more than the critical threshold.
B. The warning threshold is ignored and alerts would be generated when space usage exceeds the critical threshold.
C. The critical threshold is ignored and alerts would be generated when space usage exceeds the warning threshold.
D. Both threshold values are used and alerts are generated when space usage exceeds the respective threshold values.
B
A
不能这样设置
9.
84. Exhibit: View the Exhibit and examine the characteristic of the USERS tablespace. You observe that a large volume of inserts and deletes are happening on the TRANS table in the USERS tablespace and you suspect that the TRANS table is fragmented. Which advisory component would you refer to, in order to find information about table fragmentation?
A. Memory Advisor
B. Segment Advisor
C. SQL Tuning Advisor
D. SQL Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)
B
B
教材10-22
10.
97. Users in your production database complain that they are getting the following error message while trying to insert rows into the ORDERS table:
ERROR at line 1:
ORA-01654: unable to extend index USERS.ORDERS_IND by 8 in tablespace INDEXES.While investigating,
you find that the INDEXES tablespace has run out of space and there is no more free space on the disk where the data files are available. Which two actions could you perform to overcome this error without affecting the queries that are currently being executed? (Choose two)
A. Drop and re-create the index.
B. Coalesce the ORDERS_IND index.
C. Coalesce the INDEXES tablespace.
D. Drop and re-create the ORDERS table.
E. Rebuild the index online and move it to another tablespace.
BE
好像做过,OCA的题?
BE
做过
11.
102. While designing the database for one of your online transaction processing (OLTP) applications, you want to achieve the following:
a) high availability of data
b) faster primary key access to the table data
c) compact storage for the table
Which type of tables would you use to achieve these objectives?
A. heap tables
B. object tables
C. partitioned tables
D. index-organized tables (IOTs)
D
索引组织表的高可用性是什么来着?
D
教材10-10
"Have higher availability because table reorganization does not invalidate secondary indexes"
12.
106. Which type of PL/SQL construct would you use to automatically correct the error resulting from a statement that was suspended due to a space-related problem?
A. functions
B. package
C. procedure
D. database trigger
E. anonymous PL/SQL block
D
D
PDF教材13-10
"When a resumable statement encounters a correctable error, the system internally generates the AFTER SUSPEND system event."
13.
107. In an Oracle 10g database, the in-memory statistics are gathered at regular intervals and used to perform growth-trend analysis and capacity planning of the database. Which component stores these statistics?
A. Recovery catalog
B. Oracle Enterprise Manager Repository
C. Automatic Workload Repository (AWR)
D. Oracle 10g Enterprise Manager Grid Control
E. Automatic Database Diagnostic Monitor (ADDM)
C
刚做过
C
无需解释
14.
109. In one of your online transaction processing (OLTP) applications, most users frequently modify the values, including the key values, of the application tables. Some users generate application reports by using multiple application tables. What is the best table structure that you can use to gain optimal performance?
A. Heap table
B. Object table
C. External table
D. Clustered table
E. Global temporary table
F. Index-organized table (IOT)
A
A
B 这个好像没什么关系
C 外部表不能被修改
D,F 聚簇表和索引组织表都不适用于关键字段频繁修改的情况.
E 临时表的作用范围只对当前会话
15.
116. In one of your online transaction processing (OLTP) applications, users are manipulating and querying a database table simultaneously. From the Segment Advisor, you find that one of the tables is highly fragmented and you want to shrink the table immediately without affecting the currently active queries. Which option would you use with the ALTER TABLE command to achieve this objective?
A. REBUILD
B. CASCADE
C. TRUNCATE
D. ROW MOVEMENT
E. SHRINK SPACE COMPACT
F. SHRINK SPACE CASCADE
E
E
PDF教材13-22
"During the compaction phase, locks are held on individual rows containing the data. This causes concurrent DML operations such as updates and deletes to serialize on these locks."
16.
118. Users are performing a large volume of inserts and deletes on the application tables in the APPS tablespace. You observe that there are several warning alerts being generated for the APPS tablespace space usage metrics. Currently, the warning threshold for the tablespace usage metrics is set to 70%. To make the generated alerts more useful as a problem identification tool, you want to reduce the frequency of alert generation for the tablespace usage metrics for the APPS tablespace. What should you do?
A. Disable SQL tracing for the APPS tablespace.
B. Disable logging attributes for the APPS tablespace.
C. Modify the tablespace to be a dictionary-managed tablespace.
D. Increase the critical threshold value for the tablespace space usage metrics for the APPS tablespace.
E. Increase the warning threshold value for the tablespace space usage metric for the APPS tablespace.
E
E
太容易了反而觉得有什么蹊跷
17.
127. In your test database, you have created the ORDERS table as an index-organized table (IOT). To facilitate faster querying, you have created a mapping table and a bitmap index on the ORDER_FILLED column. You observe that the query performance degrees when users perform a large volume of transactions. While investigating the reason, you find that the mapping table segment is fragmented, leading to poor performance. Which option would you use to defragment the mapping table without affecting the original table data?
A. Export and import the mapping table.
B. Drop and re-create the mapping table.
C. Truncate the mapping table and reinsert the values.
D. Use the ALTER TABLE .. REBUILD command to defragment the mapping table.
B
不知道
A
答案错了,应该是B
PDF教材13-38
"To rebuild the mapping table, it must be dropped and re-created."
问题:
alter table xxx move nomapping;
alter table xxx move mapping table;
会锁表吗?
映射表也不能被收缩(shrinking), PDF教材13-17
18.
142. View the Exhibit and examine the characteristics of the USERS tablespace. You observe that a large volume of inserts and deletes are happening on the TRANS table in the USERS tablespace and you suspect that the TRANS table is fragmented. Which advisory component would you refer to, in order to find information about table fragmentation? Exhibit:
A. Segment Advisor
B. SQL Access Advisor
C. SQL Tuning Advisor
D. Memory Advisor
E. Automatic Database Diagnostic Monitor (ADDM)
A
怎么都是一样的题,一样的图?
A
重复了
19.
149. You lost the index tablespace in your database. You decided to re-create the index tablespace and the indexes in the tablespace. What methods can you use to re-create the indexes? (Choose all that apply.)
A. SQL scripts
B. Recovery Manager (RMAN) script
C. Data Pump
D. SQL*Loader
E. Flashback database
ABC
没看懂
AC
破题, B为什么不行
A 肯定行
B 可以恢复表空间, 包括了其中的索引
C 可以从导出文件中导出建索引的SQL语句
20.
154. Exhibit: View the Exhibit and examine the properties of the USERS tablespace. You execute the following statement to shrink the TRANS table existing on the USERS tablespace:
SQL> ALTER TABLE trans SHRINK SPACE CASCADE;
Which objects would be affected by this command? (Choose all that apply.)
A. the materialized views based on the TRANS table
B. the materialized views log of the TRANS table
C. the TRANS table
D. the B*Tree indexes on the TRANS table
E. the large object (LOB) segments of the TRANS table
ABCDE
不会做.图不对题.
CD
PDF教材13-21
"If CASCADE is specified, the shrink behavior is cascaded to all the dependent segments that support a shrink operation, except materialized views, LOB indexes, IOT mapping tables, and overflow segments."
21.
182. In which two conditions are resumable statements suspended? (Choose two.)
A. when a user exceeds the space quota
B. when a user session is terminated
C. when a table that is being accessed by the current transaction is not found
D. when a user manually suspends the statement
E. when the maximum extents of a segment is reached
AE
AE
PDF教材13-7
无法手工挂起一个会话
=====
22.
28. You create a new table, populate it, and build several indexes on the table. When you issue a query against the table, the optimizer does not choose to use the indexes. Why might this be?
A. The indexed columns are not used in the where clause of the query.
B. There are no statistics available for the table.
C. There is a small number of rows in the table.
D. The query contains a hint instructing the optimizer not to use the indexes.
E. All of the above.
E
28. E. Because you aren’t given enough information to determine the availability of statistics, nor are you privy to the query itself, you can’t rule out any valid possibilities. All of the choices listed are valid reasons why the optimizer might choose to not use an available index. See Chapter 8 for more information.
A. 没查询这个字段
B. 没有统计信息
C. 记录数很少, 无需使用索引
D. 提示强制改变了执行计划
23.
29. You are concerned about keeping statistics up-to-date for the NOODLE table, a table that is the target of frequent DML operations. In response, another DBA issues the command ALTER TABLE NOODLE MONITORING. What is the result of this action? (Choose the best answer.)
A. The action resolves the issue.
B. The action has no effect.
C. The action only partially resolves the issue.
D. The action has a detrimental effect regarding the issue.
E. None of the above.
D
29. B. The MONITORING option of the ALTER TABLE command is deprecated in Oracle Database 10g. While it will not produce an error, it is treated as no operation by Oracle. Automatic DML monitoring is used instead. See Chapter 8 for more information.
C 我记着我选C来着, 怎么成D了. 10G不用monitoring, 设不设置都一样
SQL> select monitoring from user_tables where table_name='T1';
MON
---
YES
SQL> alter table t1 nomonitoring;
Table altered.
SQL> select monitoring from user_tables where table_name='T1';
MON
---
YES
SQL>
见Automatically Collecting Statistics on Tables(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2270)
Formerly, you enabled DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. Starting with Oracle Database 10g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.
D detrimental是有害的意思
24.
47. Consider the index HR.IDX_PK_EMP on the table HR.EMPLOYEES and the following ALTER INDEX command:
ALTER INDEX HR.IDX_PK_EMP COALESCE;
Which of the following commands accomplishes the same task as this command? (Choose the best answer.)
A. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
B. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
C. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
D. ALTER INDEX HR.IDX_PK_EMP REBUILD;
D
A也行?
47. A. Using the CASCADE keyword in any segment shrink operation will shrink the free space in any dependent objects such as indexes. Chapter 13 discusses segment shrink functionality.
D为啥不对? 是因为不是online的吗?
25.
48. Which type of queue is supported by sorted hash clusters?
A. DEQUE
B. LIFO
C. FIFO
D. A queue represented by a two-way linked list
C
不会
48. C. Sorted hash clusters are similar to standard hash clusters, except that they store data sorted by non-primary key columns and make access by applications that use the rows in a first in, first out (FIFO) manner very efficiently; no sorting is required. Chapter 13 describes how sorted hash clusters are created and used.
教材10-18
Calls are stored as they are made and processed later in a “first-in, first-out” order when bills are generated for each originating telephone number.
26.
50. Which of the following statements is not true about segment shrink operations?
A. The compaction phase of segment shrink is done online.
B. During the compaction phase, the entire segment is locked but only for a very short period of time.
C. When the second phase of segment shrink occurs, the high watermark (HWM) is adjusted.
D. User DML can block the progress of the compaction phase until the DML is committed or rolled back.
E. Using the COMPACT keyword, the movement of the HWM can occur later during non-peak hours by running the command without the COMPACT keyword.
B
50. B. During the compaction phase, locks are held only on individual rows, causing some minor serialization with concurrent DML operations. For more information about segment shrink, see Chapter 13.
PDF教材13-22
27.
51. What is the purpose of the overflow area for an index-organized table (IOT)?
A. The overflow area helps to reduce row chaining in the IOT.
B. The overflow area allows you to store some or all of the non-primary key data in an IOT row in another tablespace, improving performance.
C. The overflow area is used when there is a duplicate value for the primary key in the IOT.
D. The overflow area stores the index information for indexed columns that are not part of the primary key.
B
51. B. If an IOT row’s data exceeds the threshold of available space in a block, the row’s data will be dynamically and automatically moved to the overflow area. For more information about index-organized tables, see Chapter 13.
PDF教材13-33
=====
28.
1. Which data dictionary view provides the recommended action, as a SQL statement, from the Segment Advisor?
A. DBA_ADVISOR_FINDINGS
B. DBA_ADVISOR_RECOMMENDATIONS
C. DBA_ADVISOR_ACTIONS
D. DBA_ADVISOR_RATIONALE
B
不确定, 或者是A?
1. C. The data dictionary view DBA_ADVISOR_ACTIONS contains the SQL statement(s) that the Segment Advisor supplies to implement its recommendation for segment maintenance. DBA_ADVISOR_FINDINGS contains the results of the analysis, but no SQL. DBA_ADVISOR_RECOMMENDATIONS presents one or more findings and the benefits for performing the recommendation. DBA_ADVISOR_RATIONALE provides a more detailed set of reasons why the recommendation should be implemented, along with the impact of not performing the recommendation.
29.
2. Which of the following statements is not true about sorted hash clusters?
A. The new access path is used regardless of the type of predicate in the WHERE clause.
B. You are allowed to create indexes on sorted hash clusters.
C. The cost-based optimizer must be used to take advantage of the new access path.
D. Additional sorts are not necessary if you access the cluster by one of the lists of hash key columns.
E. More than one table can be stored in a sorted hash cluster.
A
不懂
2. A. The new access path in a sorted hash cluster is used only if an equality predicate is used.
教材10-17
见
15.9 Using Hash Clusters for Performance(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/data_acc.htm#i7690)
30.
3. Consider the following scenario. The user SCOTT runs a query at 8:25 A.M. that receives an ORA-01555: Snapshot too old error after running for 15 minutes. An alert is sent to the DBA that the undo tablespace is incorrectly sized. At 10:15 A.M., the DBA checks the initialization parameter UNDO_RETENTION, and its value is 3600; the parameter is sized correctly. The DBA doubles the size of the undo tablespace by adding a second datafile. At 1:15 P.M., the user SCOTT runs the same query and once again receives an ORA-01555: Snapshot too old error. What happens next? (Choose the best answer.)
A. The DBA receives another alert indicating that the undo tablespace is still undersized.
B. The user SCOTT calls the DBA to report that the query is still failing.
C. The second datafile autoextends so that future queries will have enough undo space to complete when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds another datafile to the undo tablespace, and then the query runs to completion.
D
什么意思
3. B. Even if the size of the undo tablespace is adjusted after an undo space problem, only one alert is sent for each 24-hour period. Therefore, the only way that the problem will be resolved promptly is for SCOTT to call the DBA, because the DBA will not receive another alert until the next day when another query fails.
见
Viewing Information About Undo(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1510)
In addition to the proactive undo space alerts, Oracle Database also provides alerts if your system has long-running queries that cause SNAPSHOT TOO OLD errors. To prevent excessive alerts, the long query alert is issued at most once every 24 hours. When the alert is generated, you can check the Undo Advisor Page of Enterprise Manager to get more information about the undo tablespace.
31.
4. The background process __________ checks for tablespace threshold violation or clearance every __________ minutes.
A. MMON, 10
B. SMON, 10
C. TMON, 30
D. PMON, 15
E. MMON, 30
A
不知道
4. A. The new background process MMON checks for threshold violations every 10 minutes. An alert is triggered when the threshold is reached or is cleared.
32.
5. Which of the following initialization parameters influences the recommended redo log file size provided by the Redo Logfile Size Advisor?
A. LOG_CHECKPOINT_INTERVAL
B. OPTIMAL_LOGFILE_SIZE
C. FAST_START_IO_TARGET
D. FAST_START_MTTR_TARGET
E. None of the above
D
5. D. FAST_START_MTTR_TARGET specifies the desired time, in seconds, for instance recovery after a crash or an instance failure. Therefore, the Redo Logfile Size Advisor uses this value to determine the optimal log file size. OPTIMAL_LOGFILE_SIZE is not an initialization parameter but a column in the view V$INSTANCE_RECOVERY. The initialization parameter FAST_START_IO_TARGET specifies recovery at the I/O level, and LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of redo log file blocks used.
PDF教材13-5
This column shows the redo log file size (in megabytes) that is considered to be optimal based on the current FAST_START_MTTR_TARGET setting. It is recommended that you set all online redo log files to at least this value.
33.
6. Which of the following is not a benefit of segment shrink?
A. Full table scans will take less time.
B. Better index access takes place because of a smaller b-tree.
C. Space is freed up for other database objects.
D. All chained rows are fixed.
E. Space below the HWM is released, and the HWM is moved down.
B
不确定. D?
6. D. While some chained rows may be fixed with segment shrink functionality, it is not guaranteed that all chained rows will be fixed, because not all blocks may be read in a segment shrink operation.
B 也对. shrink索引
D 见Space Reclamation(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2240)
Although segment shrink reduces row chaining, and the Oracle Database recommends online redefinition to remove chained rows, the Segment Advisor actually detects certain chained rows that are above a threshold. For example, if a row size increases during an update such that it not longer fits into the block, then the Segment Advisor recommends that the segment be reorganized to improve I/O performance.
Note: The Segment Advisor does not detect chained rows created by inserts.
34.
7. The EM Database Control Segment Resource Estimation feature uses all the following characteristics of the proposed table except for which one?
A. Column datatypes
B. PCTUSED
C. PCTFREE
D. Column sizes
E. Estimated number of rows
B
7. B. Only PCTFREE is used in the calculation, because it is the amount of space to leave free in the block for updates to existing rows. PCTUSED is not needed unless the segment space management is not AUTO. In addition, extent sizes calculated by this feature help assess the impact on the tablespace where this segment will be stored.
教材10-21
For example, the estimation of a table size is based on the following data: column data types, column sizes, and PCTFREE. Extent information is also used to calculate the space allocation impact on the currently selected tablespace.
35.
8. Which of the following is not a benefit of sorted hash clusters? (Choose the best answer.)
A. Rows within a given cluster key value are sorted by the sort key(s).
B. The ORDER BY clause is not required to retrieve rows in ascending or descending order of the sort key(s).
C. Cluster key values are hashed.
D. Rows selected by a cluster key value using an equality operator are returned in ascending or descending order.
B
不知道
8. C. While cluster key values in a sorted hash cluster are hashed, this is also true of regular hash clusters, and therefore is not a benefit unique to sorted hash clusters.
B 教材10-17
Within each list, the rows are stored in the order specified by the sort key columns defined by the corresponding sorted hash cluster. This is also the default return order when querying the table by using the hash key columns in the predicate.
36.
9. In the following scenario, the DBA wants to reclaim a lot of wasted space in the HR.EMPLOYEES table by using the segment shrink functionality. Which of the following is the correct order of the steps?
1. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
2. ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
3. ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
4. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
5. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
A. 3, 4, 1, 5, 2
B. 4, 1, 3, 2, 5
C. 5, 2, 1, 3, 4
D. 4, 1, 2, 3, 5
A
第5步有疑问
9. A. While the segment shrink operation could combine steps 1 and 4, the impact to the users will most likely be lessened by performing two smaller operations instead of one by specifying SHRINK SPACE COMPACT before specifying SHRINK SPACE in a subsequent operation.
第5步cascade的时候会不会锁索引?
37.
10. Which of the following calls to DBMS_SERVER_ALERT.SET_THRESHOLD will set the thresholds for the UNDOTBS1 tablespace to 60 percent and 90 percent? (Choose the best answer.)
A. dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
null);
B. dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_le, 60,
dbms_server_alert.operator_le, 90,
1, 1, null,
dbms_server_alert.object_type_datafile,
'UNDOTBS1');
C. dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');
D. dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');
D
猜的
10. D. The call to DBMS_SERVER_ALERT.SET_THRESHOLD must specify the metric TABLESPACE_ CT_FULL, the two thresholds, an object type of tablespace, and the tablespace name itself. Specifying NULL for the tablespace name will set the threshold for all tablespaces, not just the UNDOTBS1 tablespace.
38.
11. Which of the following statements is not true about segment shrink operations? (Choose the best sanswer.)
A. Tables with ROWID-based materialized views are maintained.
B. Segment shrink is allowed only on segments whose space is automatically managed.
C. Heap-organized and index-organized tables can be shrunk.
D. ROW MOVEMENT must be enabled for heap-organized segments.
E. Chained rows may be repaired during a segment shrink operation.
F. Triggers are not fired during a segment shrink operation.
E
不知道
11. A. Because the ROWIDs are changed with a segment shrink operation, tables with ROWID-based materialized views cannot be shrunk unless the materialized views are dropped and re-created after the segment shrink operation.
PDF教材13-17
E 见Space Reclamation(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2240)
Although segment shrink reduces row chaining, and the Oracle Database recommends online redefinition to remove chained rows, the Segment Advisor actually detects certain chained rows that are above a threshold. For example, if a row size increases during an update such that it not longer fits into the block, then the Segment Advisor recommends that the segment be reorganized to improve I/O performance.
Note: The Segment Advisor does not detect chained rows created by inserts.
Using the Segment Advisor(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2102)
Note: The Segment Advisor flags only the type of row chaining that results from updates that increase row length.
39.
12. Which of the following is not a feature of the Segment Advisor within EM Database Control?
A. Growth trend analysis
B. Segment Resource Estimation
C. Finding candidates for segment shrink
D. Finding table segments with chained rows
D
估计是
12. D. The Segment Advisor is not used to find tables with chained rows, but instead is used for finding segments that are good candidates for segment shrink or may be growing too fast.
segment advisor可以发现chained rows呀, 见上一道题
40.
13. Which of the following conditions will trigger an additional sort on a sorted hash cluster? (Choose two.)
A. The ORDER BY clause specifies non-sort columns that are not indexed.
B. An ORDER BY clause is used in the query, although the sort may still fit in memory.
C. The cost-based optimizer is in effect.
D. The ORDER BY clause is omitted, and the WHERE clause does not reference the cluster key.
E. The ORDER BY clause specifies trailing sort columns.
A
不知道
13. A, E. If a query on a sorted hash cluster retrieves rows and an ORDER BY clause specifies either non-sort columns or a suffix of the sort columns, additional sorting is required, assuming that indexes are not defined on the columns in the ORDER BY clause.
E是说, ORDER BY字段的顺序跟sorted hash cluster指定的顺序不一样?
41.
14. Which of the following statements is not true about segment shrink operations in tablespaces with automatic segment space management?
A. Clustered tables cannot be shrunk.
B. LOB segments can be shrunk.
C. IOT mapping tables and overflow segments cannot be shrunk.
D. Tables with function-based indexes cannot be shrunk.
E. ROW MOVEMENT must be enabled for heap-based segments.
A
不知道
14. B. For segments in tablespaces with automatic segment space management, LOB segments cannot be shrunk. In addition, tables with LONG columns, on-commit materialized views, and ROWID-based materialized view cannot be shrunk. In all cases, shrink operations cannot be performed on segments managed by freelists.
PDF教材13-17
42.
15. Which of the following is a disadvantage of rebuilding an index instead of coalescing an index?
A. You temporarily need twice as much disk space to rebuild the index.
B. You cannot move the index to another tablespace.
C. The storage characteristics of the index cannot be changed.
D. The rebuild operation only coalesces index leaf blocks within each branch instead of re-creating the entire tree.
A
15. A. Whether you rebuild the index offline or online, you temporarily need twice as much disk space. If you rebuild the index online, you also need disk space to support a journal table to hold the intermediate changes to the index while the index is being rebuilt.
PDF教材13-27
43.
16. Which of the following commands adds a member /logs/redo22.log to redo log file group 2?
A. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2;
B. ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2;
C. ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2;
D. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’;
B
16. B. When adding log file members, specify the group number or specify all the existing group members.
嗯
44.
17. Which of the following is not a benefit of index clusters?
A. The tables in the cluster are always queried together.
B. Queries with an equivalence operator will perform better.
C. The tables in the cluster have little or no DML activity.
D. The child tables have roughly the same number of rows for each parent key in the parent table.
D
就这句最看不懂
17. B. Hash clusters, not index clusters, use a hashing function to find a row in a cluster and perform best for equivalence queries.
不懂. index cluster指的是cluster上建的索引?
都不懂
45.
18. Which of the following scenarios will never trigger Resumable Space Allocation? (Choose all that apply.)
A. SELECT * FROM HR.EMPLOYEES;
B. DELETE FROM HR.EMPLOYEES;
C. DROP TABLE HR.EMPLOYEES;
D. SELECT * FROM HR.DEPARTMENTS ORDER BY DEPARTMENT_NUMBER;
E. ALTER INDEX HR.EMP_NAME_IX REBUILD ONLINE;
F. ALTER INDEX HR.EMP_NAME_IX REBUILD;
AC
A应该不行吧
18. A, C. Unless an ORDER BY clause forces a sort operation and uses up temporary space, a SELECT command will not otherwise trigger Resumable Space Allocation. Dropping a table does not use any additional disk space in a tablespace; it frees up disk space. Therefore, it will not trigger Resumable Space Allocation.
A 查询可能发生ora-1555错误, 无法恢复
46.
19. An AFTER SUSPEND trigger can be defined at what level? (Choose all that apply.)
A. Instance
B. Schema
C. Table
D. Session
E. Database
E
19. B, E. The AFTER SUSPEND system-level trigger can be defined only at the database level or at the schema level.
多选
见Notifying Users: The AFTER SUSPEND System Event and Trigger(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2075)
When a resumable statement encounter a correctable error, the system internally generates the AFTER SUSPEND system event. Users can register triggers for this event at both the database and schema level. If a user registers a trigger to handle this system event, the trigger is executed after a SQL statement has been suspended.
47.
20. Which of the following statements is not true about index-organized tables?
A. An index-organized table can have additional indexes defined on other columns in the table.
B. An index-organized table has both physical and logical ROWIDs.
C. An index-organized table without secondary indexes is stored in a single segment.
D. The space requirements for an index-organized table are reduced in part because the data is stored in the same segment as the index, and therefore no physical ROWID is required.
C
20. B. An index-organized table (IOT) does not have a physical ROWID; instead, a logical ROWID is constructed based on the value of the primary key.
Posts
见STATISTICS_LEVEL(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#sthref640)