第93/94次：2016-03-27星期日

botang

1. select  * from dict
   
2. where table_name like '%MODIFICATION%';
   
3. 
   
4. select  * from DBA_TAB_MODIFICATIONS m
   
5.   where m.table_owner <> 'SYS';
   
6.   
   
7.   
   
8. select  * from  DBA_STAT_EXTENSIONS e
   
9.   where e.owner='HR'
   
10.    and e.table_name='EMPLOYEES';
    
11. 
    
12. 
    
13.   select
    
14.     dbms_stats.create_extended_stats('HR','EMPLOYEES','(salary,commission_pct)') from dual;
    
15.    
    
16.     begin
    
17.        dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'EMPLOYEES',
    
18.        method_opt => 'for all columns size auto for columns (salary,commission_pct) size 254');
    
19.      end;
    
20.       
    
21.      
    
22.      select  * from dba_tab_col_statistics s where s.owner='HR'
    
23.         and s.table_name='EMPLOYEES';     
    
24.         
    
25.      select  * from dba_histograms h
    
26.       where h.owner='HR' and h.table_name='EMPLOYEES';
    
27.       
    
28.       begin
    
29.           dbms_stats.create_stat_table(ownname => 'SYSTEM',stattab => 'MYSTAT');
    
30.         end;
    
31.                
    
32.         select  * from system.mystat;
    
33.         
    
34.         begin
    
35.            dbms_stats.export_table_stats(ownname => 'HR',tabname => 'EMPLOYEES',
    
36.            statown => 'SYSTEM',
    
37.            
    
38.              stattab =>  'MYSTAT');
    
39.            end;  
    
40.            
    
41.            
    
42.            begin
    
43.               dbms_stats.delete_table_stats(ownname => 'HR',tabname => 'EMPLOYEES');
    
44.              end;
    
45. 
    
46. select  t.last_analyzed
    
47. from dba_tables t where t.owner='HR' and t.table_name='EMPLOYEES';
    
48. 
    
49. begin
    
50.            dbms_stats.import_table_stats(ownname => 'HR',tabname => 'EMPLOYEES',
    
51.            statown => 'SYSTEM',
    
52.            
    
53.              stattab =>  'MYSTAT');
    
54.            end;  
    
55. 
    
56. ---
    
57. 
    
58. select  * from v$sql_monitor;
    
59. select  * from v$sql_plan_monitor;
    
60. 
    
61. select  *
    
62. from v$sql where sql_id='05s9358mm6vrr';
    
63. 
    
64. 
    
65. select  * from dict
    
66.   where table_name like 'DBA%ACTIVE%';
    
67.   
    
68.   
    
69.   select * from DBA_HIST_ACTIVE_SESS_HISTORY;

复制代码

utlpwdmg.sql:

1. Rem
   
2. Rem $Header: utlpwdmg.sql 02-aug-2006.08:18:05 asurpur Exp $
   
3. Rem
   
4. Rem utlpwdmg.sql
   
5. Rem
   
6. Rem Copyright (c) 2006, Oracle. All rights reserved.  
   
7. Rem
   
8. Rem    NAME
   
9. Rem      utlpwdmg.sql - script for Default Password Resource Limits
   
10. Rem
    
11. Rem    DESCRIPTION
    
12. Rem      This is a script for enabling the password management features
    
13. Rem      by setting the default password resource limits.
    
14. Rem
    
15. Rem    NOTES
    
16. Rem      This file contains a function for minimum checking of password
    
17. Rem      complexity. This is more of a sample function that the customer
    
18. Rem      can use to develop the function for actual complexity checks that the
    
19. Rem      customer wants to make on the new password.
    
20. Rem
    
21. Rem    MODIFIED   (MM/DD/YY)
    
22. Rem    asurpur     05/30/06 - fix - 5246666 beef up password complexity check
    
23. Rem    nireland    08/31/00 - Improve check for username=password. #1390553
    
24. Rem    nireland    06/28/00 - Fix null old password test. #1341892
    
25. Rem    asurpur     04/17/97 - Fix for bug479763
    
26. Rem    asurpur     12/12/96 - Changing the name of password_verify_function
    
27. Rem    asurpur     05/30/96 - New script for default password management
    
28. Rem    asurpur     05/30/96 - Created
    
29. Rem
    
30. 
    
31. 
    
32. -- This script sets the default password resource parameters
    
33. -- This script needs to be run to enable the password features.
    
34. -- However the default resource parameters can be changed based
    
35. -- on the need.
    
36. -- A default password complexity function is also provided.
    
37. -- This function makes the minimum complexity checks like
    
38. -- the minimum length of the password, password not same as the
    
39. -- username, etc. The user may enhance this function according to
    
40. -- the need.
    
41. -- This function must be created in SYS schema.
    
42. -- connect sys/<password> as sysdba before running the script
    
43. 
    
44. CREATE OR REPLACE FUNCTION verify_function_11G
    
45. (username varchar2,
    
46.   password varchar2,
    
47.   old_password varchar2)
    
48.   RETURN boolean IS
    
49.    n boolean;
    
50.    m integer;
    
51.    differ integer;
    
52.    isdigit boolean;
    
53.    ischar  boolean;
    
54.    ispunct boolean;
    
55.    db_name varchar2(40);
    
56.    digitarray varchar2(20);
    
57.    punctarray varchar2(25);
    
58.    chararray varchar2(52);
    
59.    i_char varchar2(10);
    
60.    simple_password varchar2(10);
    
61.    reverse_user varchar2(32);
    
62.    part1  varchar2(30);
    
63.    part2 varchar2(30);
    
64. 
    
65. BEGIN
    
66.    digitarray:= '0123456789';
    
67.    chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    
68. 
    
69.    -- Check for the minimum length of the password
    
70.    IF length(password) < 8  or length(password)  > 30  THEN
    
71.       raise_application_error(-20001, 'Password length less than 8 or more than 30');
    
72.    END IF;
    
73. 
    
74. 
    
75.    -- Check if the password is same as the username or username(1-100)
    
76.    IF NLS_LOWER(password) = NLS_LOWER(username) THEN
    
77.      raise_application_error(-20002, 'Password same as or similar to user');
    
78.    END IF;
    
79.    FOR i IN 1..100 LOOP
    
80.       i_char := to_char(i);
    
81.       if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
    
82.         raise_application_error(-20005, 'Password same as or similar to user name ');
    
83.       END IF;
    
84.     END LOOP;
    
85. 
    
86.    -- Check if the password is same as the username reversed
    
87.    
    
88.    FOR i in REVERSE 1..length(username) LOOP
    
89.      reverse_user := reverse_user || substr(username, i, 1);
    
90.    END LOOP;
    
91.    IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
    
92.      raise_application_error(-20003, 'Password same as username reversed');
    
93.    END IF;
    
94. 
    
95.    
    
96.   
    
97. 
    
98. 
    
99. 
    
100. 
     
101. 
     
102.    IF length(password)>length(username) THEN
     
103. 
     
104.   part1 := SUBSTR(password, 1, length(username));
     
105. 
     
106.   part2 := SUBSTR(password, length(username)+1);
     
107. 
     
108.   IF part1=username THEN
     
109.    
     
110.       isdigit:=FALSE;
     
111.       m := length(part2);
     
112.       FOR i IN 1..10 LOOP
     
113.        FOR j IN 1..m LOOP
     
114.          IF substr(part2,j,1) = substr(digitarray,i,1) THEN
     
115.             isdigit:=TRUE;
     
116.          END IF;
     
117.       END LOOP;
     
118.    END LOOP;
     
119. 
     
120.      IF isdigit=true THEN
     
121. 
     
122.        raise_application_error(-20013,'Password same as or similar to username');                 
     
123. 
     
124.      END IF;
     
125. 
     
126. 
     
127. 
     
128. 
     
129. 
     
130.   END IF;
     
131. 
     
132.   END IF;
     
133. 
     
134. 
     
135. 
     
136. 
     
137.    -- Check if the password is the same as server name and or servername(1-100)
     
138.    select name into db_name from sys.v$database;
     
139.    if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
     
140.       raise_application_error(-20004, 'Password same as or similar to server name');
     
141.    END IF;
     
142.    FOR i IN 1..100 LOOP
     
143.       i_char := to_char(i);
     
144.       if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
     
145.         raise_application_error(-20005, 'Password same as or similar to server name ');
     
146.       END IF;
     
147.     END LOOP;
     
148. 
     
149.    -- Check if the password is too simple. A dictionary of words may be
     
150.    -- maintained and a check may be made so as not to allow the words
     
151.    -- that are too simple for the password.
     
152.    IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
     
153.       raise_application_error(-20006, 'Password too simple');
     
154.    END IF;
     
155. 
     
156.    -- Check if the password is the same as oracle (1-100)
     
157.     simple_password := 'oracle';
     
158.     FOR i IN 1..100 LOOP
     
159.       i_char := to_char(i);
     
160.       if simple_password || i_char = NLS_LOWER(password) THEN
     
161.         raise_application_error(-20007, 'Password too simple ');
     
162.       END IF;
     
163.     END LOOP;
     
164. 
     
165.    -- Check if the password contains at least one letter, one digit
     
166.    -- 1. Check for the digit
     
167.    isdigit:=FALSE;
     
168.    m := length(password);
     
169.    FOR i IN 1..10 LOOP
     
170.       FOR j IN 1..m LOOP
     
171.          IF substr(password,j,1) = substr(digitarray,i,1) THEN
     
172.             isdigit:=TRUE;
     
173.              GOTO findchar;
     
174.          END IF;
     
175.       END LOOP;
     
176.    END LOOP;
     
177. 
     
178.    IF isdigit = FALSE THEN
     
179.       raise_application_error(-20008, 'Password must contain at least one digit, one character');
     
180.    END IF;
     
181.    -- 2. Check for the character
     
182.    <<findchar>>
     
183.    ischar:=FALSE;
     
184.    FOR i IN 1..length(chararray) LOOP
     
185.       FOR j IN 1..m LOOP
     
186.          IF substr(password,j,1) = substr(chararray,i,1) THEN
     
187.             ischar:=TRUE;
     
188.              GOTO endsearch;
     
189.          END IF;
     
190.       END LOOP;
     
191.    END LOOP;
     
192.    IF ischar = FALSE THEN
     
193.       raise_application_error(-20009, 'Password must contain at least one \
     
194.               digit, and one character');
     
195.    END IF;
     
196. 
     
197. 
     
198.    <<endsearch>>
     
199.    -- Check if the password differs from the previous password by at least
     
200.    -- 3 letters
     
201.    IF old_password IS NOT NULL THEN
     
202.      differ := length(old_password) - length(password);
     
203. 
     
204.      differ := abs(differ);
     
205.      IF differ < 3 THEN
     
206.        IF length(password) < length(old_password) THEN
     
207.          m := length(password);
     
208.        ELSE
     
209.          m := length(old_password);
     
210.        END IF;
     
211. 
     
212.        FOR i IN 1..m LOOP
     
213.          IF substr(password,i,1) != substr(old_password,i,1) THEN
     
214.            differ := differ + 1;
     
215.          END IF;
     
216.        END LOOP;
     
217. 
     
218.        IF differ < 3 THEN
     
219.          raise_application_error(-20011, 'Password should differ from the \
     
220.             old password by at least 3 characters');
     
221.        END IF;
     
222.      END IF;
     
223.    END IF;
     
224.    -- Everything is fine; return TRUE ;   
     
225.    RETURN(TRUE);
     
226. END;
     
227. /
     
228. 
     
229. -- This script alters the default parameters for Password Management
     
230. -- This means that all the users on the system have Password Management
     
231. -- enabled and set to the following values unless another profile is
     
232. -- created with parameter values set to different value or UNLIMITED
     
233. -- is created and assigned to the user.
     
234. 
     
235. ALTER PROFILE DEFAULT LIMIT
     
236. PASSWORD_LIFE_TIME 180
     
237. PASSWORD_GRACE_TIME 7
     
238. PASSWORD_REUSE_TIME UNLIMITED
     
239. PASSWORD_REUSE_MAX UNLIMITED
     
240. FAILED_LOGIN_ATTEMPTS 10
     
241. PASSWORD_LOCK_TIME 1
     
242. PASSWORD_VERIFY_FUNCTION verify_function_11G;
     
243. 
     
244. 
     
245. 
     
246. -- Below is the older version of the script
     
247. 
     
248. -- This script sets the default password resource parameters
     
249. -- This script needs to be run to enable the password features.
     
250. -- However the default resource parameters can be changed based
     
251. -- on the need.
     
252. -- A default password complexity function is also provided.
     
253. -- This function makes the minimum complexity checks like
     
254. -- the minimum length of the password, password not same as the
     
255. -- username, etc. The user may enhance this function according to
     
256. -- the need.
     
257. -- This function must be created in SYS schema.
     
258. -- connect sys/<password> as sysdba before running the script
     

复制代码

OCM参考答案：

1. **********************************************************
   
2. Skillset 1: Database, RMAN, EM, and Network Configuration *
   
3. **********************************************************
   
4. Section 1: Network Configuration
   
5. 配置链接串 对prod1/prod2进行验证。创建EMREP
   
6. ---archive log list;
   
7. ---show parameter db_recovery_
   
8. ---enable flashback database
   
9. ---check sys/system password
   
10. ---check dedicate server
    
11. ---both node alter user dbsnmp identified by oracle;
    
12. 
    
13. 
    
14. Section 2: Collecting Statistics
    
15. 
    
16. connect sys/oracle@prod1 as sysdba
    
17. exec dbms_stats.gather_schema_stats('HR');
    
18. 
    
19. 
    
20. Section 3: Authentication Method
    
21. CONNECT sys/oracle@prod1 as SYSDBA
    
22. ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=true;
    
23. orapwd file=orapwPROD1 password entries=5 ignorecase=n
    
24. 
    
25. CONNECT sys/oracle@prod1 as SYSDBA
    
26. --add code
    
27. vi utlpwdmg.sql
    
28. part1 varchar2(30);
    
29. part2 varchar2(30);
    
30. function is_number(i_str varchar2)
    
31. return boolean
    
32. is
    
33.   v_number number;
    
34. begin
    
35.   select to_number(i_str) into v_number from dual;
    
36.   if v_number is not null then
    
37.     return true;
    
38.   else
    
39.     return false;
    
40.   end if;
    
41. EXCEPTION
    
42.   WHEN OTHERS THEN
    
43.     return false;
    
44. end;
    
45. 
    
46. IF length(password)>30 then
    
47.   raise_application_error(-20012,'Password length more then 30');
    
48. END IF;
    
49. 
    
50. IF length(password)>length(username) THEN
    
51.   part1 := SUBSTR(password, 1, length(username));
    
52.   part2 := SUBSTR(password, length(username)+1, length(password)-length(username));
    
53.   IF part1=username THEN
    
54.      IF is_number(part2) THEN
    
55.        raise_application_error(-20013,'Password same as or similar to username');                 
    
56.      END IF;
    
57.   END IF;
    
58. END IF;
    
59. 
    
60. 
    
61. @?/rdbms/admin/utlpwdmg.sql
    
62. ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11g;
    
63. 
    
64. 
    
65. Section 4: Storing Diagnostic Information
    
66. CONNECT sys/oracle@prod1 as SYSDBA
    
67. alter system set DIAGNOSTIC_DEST='/u01/app/oracle/product/11.1.0/db_1'
    
68. 
    
69. 
    
70. Section 5: Improving Access to the Network File Directories
    
71. find this file in directory
    
72. 　　(1)$ORACLE_HOME/dbs/oranfstab
    
73. 　　(2)/etc/oranfstab
    
74. 　　(3)/etc/mtab
    
75. 
    
76. cd $ORACLE_HOME/dbs
    
77. vi oranfstab
    
78. server: MyDataServer1
    
79. path: 132.34.35.12
    
80. export: /vol/oradata mount: /u02/oradata/prod1
    
81. 
    
82. cd $ORACLE_HOME/lib
    
83. mv libodm11.so libodm11.so_stub
    
84. ln -s libnfsodm11.so libodm11.so
    
85. 
    
86. conn sys/oracle@prod1 as sysdba
    
87. shutdown immediate
    
88. startup
    
89. create tablespace SHARED_DATA datafile '/u02/oradata/prod1/shared_data.tbs.dbf' size 10m;
    
90. --check
    
91. select * from v$dnfs_servers;
    
92. 
    
93. Section 6: Applying a Patch
    
94. export PATH=$ORACLE_HOME/OPatch
    
95. cd /home/oracle/scripts
    
96. unzip p8342329_111070_linux-x86.zip
    
97. cd 8342329
    
98. $ORACLE_HOME/OPatch/opatch query -is_online_patch `pwd`
    
99. 
    
100. --prod1/prod2 should be shutdown
     
101. 
     
102. $ORACLE_HOME/OPatch/opatch apply
     
103. $ORACLE_HOME/OPatch/opatch lsinventory
     
104. 
     
105. Section 7: Enabling Grid Control to Manage Targets on the Database Machine
     
106. http://oms:4889/agent_download/10.2.0.1.0/linux
     
107. http://oms:4889/agent_download/10.2.0.5.0/linux
     
108. download agentDownload.linux
     
109. chmod a+x agentDownload.linux
     
110. export PATH=$ORACLE_HOME/jdk/bin:$PATH
     
111. java -version
     
112. which java
     
113. --10.2.0.5
     
114. 1
     
115. ./agentDownload.linux -b  /u01/app/oracle/product/11.1.0/ -m oms.us.oracle.com -r 4889
     
116. 
     
117. ssh oms-->tar -cvf agent10g.tar agent10g/-->scp agent10.tar to dbsever-->tar -xvf agent10g.tar
     
118. cd oui/bin
     
119. runInstaller -clone -forceClone ORACLE_HOME=/u01/app/oracle/product/11.1.0/agent10g ORACLE_HOME_NAME=agent10ghome -noconfig -silent
     
120. 
     
121. agentca -f
     
122. 
     
123. ./root.sh
     
124. 
     
125. emctl secure agent
     
126. 
     
127. 
     
128. --10.2.0.1
     
129. ./agentDownload.linux -b  /u01/app/oracle/product/11.1.0/
     
130. 
     
131. 
     
132. 
     
133. Section 8: Creating a Recovery Catalog
     
134. conn sys/oracle@emrep as sysdba
     
135. create bigfile tablespace rc_data datafile '/u01/app/oracle/oradata/PROD2/rc_data.dbf' size 100m;
     
136. ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=true;
     
137. create user rc_admin identified by RC_ADMIN default tablespace rc_data;
     
138. grant resource,connect,recovery_catalog_owner to rc_admin;
     
139. 
     
140. rman catalog rc_admin/RC_ADMIN@emrep
     
141. create catalog tablespace rc_data;
     
142. --from catalog register
     
143. rman target sys/oracle@prod1 catalog rc_admin/RC_ADMIN@emrep
     
144. register database;
     
145. rman target sys/oracle@prod2 catalog rc_admin/RC_ADMIN@emrep
     
146. register database;
     
147. 
     
148. 
     
149. Section 9: Backups
     
150. --prod1 and prod2
     
151. rman target sys/oracle@prod1 catalog rc_admin/RC_ADMIN@emrep
     
152. configure controlfile autobackup on;
     
153. run {
     
154.    backup database tag='Q107' format '/home/oracle/files/%U'  plus archivelog tag='Q107' format '/home/oracle/files/%U' KEEP FOREVER;}
     
155. 
     
156. select bytes/1024/1024/30 from v$datafile a, v$tablespace b where b.tablespace_name='EXAMPLE' and b.ts#=a.ts#;
     
157. 
     
158. run{
     
159. allocate channel c1 device type disk;
     
160. allocate channel c2 device type disk;
     
161. allocate channel c3 device type disk;
     
162. allocate channel c4 device type disk;
     
163. backup tablespace example section size 30m format '/home/oracle/files/%U';}
     
164. 
     
165. 
     
166. rman target sys/oracle@prod2 catalog rc_admin/RC_ADMIN@emrep
     
167. configure controlfile autobackup on;
     
168. backup database tag='prod2_full' format '/home/oracle/files/%U' plus archivelog tag='prod2_full' format '/home/oracle/files/%U' delete all input
     
169. 
     
170. 
     
171. ************************
     
172. Skillset 2: Data Guard*
     
173. ************************
     
174. Section 1: Creating a Physical Standby Database
     
175.    conn sys/oracle@prod1 as sysdba
     
176.    alter database force logging;
     
177.    alter database add standby logfile '/u01/app/oracle/oradata/PROD1/stdby01.log' size 50m;
     
178.    alter database add standby logfile '/u01/app/oracle/oradata/PROD1/stdby02.log' size 50m;
     
179.    alter database add standby logfile '/u01/app/oracle/oradata/PROD1/stdby03.log' size 50m;
     
180.    alter database add standby logfile '/u01/app/oracle/oradata/PROD1/stdby04.log' size 50m;
     
181. ---primary role parameter setting   
     
182.    alter system set log_archive_config='dg_config=(PROD1,SBDB1)';
     
183.    alter system set log_archive_dest_1='LOCATION=/home/oracle/files/arch/prod1 VALID_FOR=(online_logfiles,primary_role) db_unique_name=PROD1';
     
184.    alter system set log_archive_dest_2='service=SBDB1 SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=SBDB1';
     
185.    
     
186. ---standby role parameter setting make a just parameter db_name=SBDB1  vi initSBDB1 in /home/oracle/files   
     
187. ---configure tnsnames.ora on primary and standby
     
188. ---make static register in listener on standby
     
189. ---copy password file to standby host ,and rename it
     
190. ---make directory on standby host
     
191.    mkdir -p /u01/app/oracle/admin/SBDB1/adump
     
192.    mkdir -p /u01/app/oracle/flash_recovery_area
     
193.    mkdir -p /u01/app/oracle/oradata/SBDB1
     
194. ---duplicate standby
     
195.    conn sys/oracle@sbdb1 as sysdba
     
196.    startup nomount pfile=/home/oracle/file/initSBDB1;
     
197.    rman target sys/oracle@prod1 auxiliary sys/oracle@sbdb1 catalog rc_admin/RC_ADMIN@emrep;
     
198. run {
     
199.      allocate channel c1 device type disk;
     
200.      allocate channel c2 device type disk;
     
201.      allocate channel c3 device type disk;
     
202.      allocate channel c4 device type disk;
     
203.      allocate auxiliary channel stby type disk;
     
204.      duplicate target database for standby from active database
     
205.      spfile parameter_value_convert '/PROD1','/SBDB1'
     
206.      set db_unique_name='SBDB1'
     
207.      set db_file_name_convert='/u01/app/oracle/oradata/PROD1/','/u01/app/oracle/oradata/SBDB1/'
     
208.      set log_file_name_convert='/u01/app/oracle/oradata/PROD1/','/u01/app/oracle/oradata/SBDB1/'
     
209.      set standby_file_management='auto'
     
210.      set FAL_CLIENT='SBDB1'
     
211.      set FAL_SERVER='PROD1'
     
212.      set log_archive_dest_1='location=/home/oracle/files/arch/sbdb1 valid_for=(online_logfile,primary_role) db_unique_name=SBDB1'
     
213.      set log_archive_dest_3='location=/home/oracle/files/arch/prod1 valid_for=(standby_logfile,standby_role) db_unique_name=SBDB1'
     
214.      set log_archive_dest_2='service=PROD1 SYNC AFFIRM valid_for=(online_logfile,primary_role) db_unique_name=PROD1';}
     
215. 
     
216.      
     
217. ---enable flashback database
     
218. ---enable realtime-apply
     
219.    alter database flashback on;
     
220.    alter database recover managed standby database using current logfile disconnect from session;
     
221.    
     
222. ----create dataguard broker
     
223. ----make static register in listener.ora, global name must be PROD1_DGMGRL and SBDB1_DGMGRL/ make tnsnames.ora connect PROD1_DGMGRL and SBDB1_DGMGRL
     
224.    conn sys/oracle@prod1 as sysdba
     
225.    alter system set dg_broker_start=true;   
     
226.    conn sys/oracle@sbdb1 as sysdba
     
227.    alter system set dg_broker_start=true;
     
228.    
     
229.    dgmgrl sys/oracle@prod1
     
230.    CREATE CONFIGURATION 'dgconfig' AS PRIMARY DATABASE IS PROD1 CONNECT IDENTIFIER IS PROD1;
     
231.    add database SBDB1 as connect identifier is sbdb1 MAINTAINED AS physical;
     
232.    ENABLE CONFIGURATION;
     
233.    
     
234.       
     
235. Section 2: Testing the Standby Database
     
236. --convert database from physical standby to snapshot standby
     
237.   convert database SBDB1 to snapshot standby;
     
238. 
     
239. Section 3: Restoring the Standby Database
     
240. --convert database from snapshot standby to phyical standby
     
241.   convert database SBDB1 to physical standby;
     
242.   
     
243. --active dataguard
     
244.   edit database SBDB1 set state='apply-off';
     
245.   alter database open;
     
246.   edit database SBDB1 set state='apply-on';
     
247. 
     
248. 4、  
     
249. ---ENABLE BLOCK CHANGE TRACKING ON STANDBY
     
250.    sqlplus /nolog
     
251.    conn sys/oracle@sbdb1 as sysdba
     
252.    alter database enable block change tracking using file '/home/oracle/files/block_change_tracking.f';
     
253.    
     
254. ---PROTECT ARCHIVELOG
     
255.   rman target / catalog rc_admin/RC_ADMIN@emrep
     
256.   CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
     
257.   
     
258. ---ENABLE FAST-START FAILOVER
     
259.    1、edit database PROD1 set property FastStartFailoverTarget="SBDB1";
     
260.       edit database SBDB1 set property FastStartFailoverTarget="PROD1";
     
261.       
     
262.    2、enable fast_start failover;
     
263.    3、start observer
     
264.   
     
265. 
     
266. 
     
267. **********************************************************
     
268. Skillset 3: Data and Data Warehouse Management           *
     
269. **********************************************************
     
270. Section 1: Fast Refreshable Materialized View
     
271. 
     
272. alter user hr identified by hr account unlock;
     
273. alter user sh identified by sh account unlock;
     
274. alter user bi identified by bi account unlock;
     
275. alter user oe identified by OE account unlock;
     
276. 
     
277. create directory dump_dir as '/home/oracle/files';
     
278. grant dba,advisor to sh;
     
279. conn sh/sh@prod1
     
280. 
     
281. var adv_name varchar2(20)
     
282. begin
     
283.   dbms_advisor.tune_mview
     
284.       (
     
285.           :adv_name,
     
286.           'CREATE MATERIALIZED VIEW sh.prod_mv AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM products GROUP BY prod_category');
     
287. end;
     
288. 
     
289. /
     
290. 
     
291. begin
     
292.    dbms_advisor.create_file (
     
293.       dbms_advisor.get_task_script(:adv_name),  
     
294.    'DUMP_DIR',
     
295.    'mvtune_script.sql');
     
296. end;
     
297. /
     
298. 
     
299. Answer :
     
300.     CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS"  
     
301.     WITH ROWID, SEQUENCE ("PROD_CATEGORY","PROD_LIST_PRICE","PROD_MIN_PRICE")  
     
302.     INCLUDING NEW VALUES
     
303.     /
     
304.    
     
305.     CREATE MATERIALIZED VIEW SH.PROD_MV
     
306.     REFRESH FAST WITH ROWID
     
307.     ENABLE QUERY REWRITE
     
308.     AS SELECT SH.PRODUCTS.PROD_CATEGORY C1, COUNT("SH"."PRODUCTS"."PROD_CATEGORY") M1,
     
309.        SUM("SH"."PRODUCTS"."PROD_LIST_PRICE"-"SH"."PRODUCTS"."PROD_MIN_PRICE")
     
310.        M2, COUNT("SH"."PRODUCTS"."PROD_LIST_PRICE"-"SH"."PRODUCTS"."PROD_MIN_PRICE")
     
311.        M3, COUNT(*) M4 FROM SH.PRODUCTS GROUP BY SH.PRODUCTS.PROD_CATEGORY;
     
312. 
     
313. 
     
314. Section 2: Creating a Database Link
     
315.     CREATE PUBLIC DATABASE LINK "PROD_LINK" USING 'PROD1';
     
316. 
     
317. Section 3: Creating a Plug-in Tablespace Using the Transportable Tablespace Feature
     
318. 
     
319. create user sst identified by Sst1234;
     
320. grant connect,resource to sst;
     
321. rman target sys/oracle@prod1 catalog rc_admin/RC_ADMIN
     
322. convert datafile '/home/oracle/scripts/TRPDATA_6' from platform 'Solaris[tm] OE (64-bit)' format='/home/oracle/files/TRPDATA_6.dbf';
     
323. impdp system/oracle dumpfile=dump_dir:trans3_2.dmp transport_datafiles=/u01/app/oracle/oradata/PROD1/TRPDATA_6.dbf
     
324. alter tablespace trpdata read write;
     
325. 
     
326. Section 4: Optimizing Star Queries
     
327. alter system set star_transformation_enabled=true;
     
328. 
     
329. create bitmap index sales_channel_bix on sh.sales(channel_id);
     
330. create bitmap index sales_time_bix on sh.sales(time_id);
     
331. create bitmap index sales_cust_bix on sh.sales(cust_id);
     
332. 
     
333. alter session set star_transformation_enabled=true;
     
334. /
     
335. SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
     
336.    SUM(s.amount_sold) sales_amount
     
337. FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
     
338. WHERE s.time_id = t.time_id
     
339. AND   s.cust_id = c.cust_id
     
340. AND   s.channel_id = ch.channel_id
     
341. AND   c.cust_state_province = 'CA'
     
342. AND   ch.channel_desc in ('Internet','Catalog')
     
343. AND   t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
     
344. GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
     
345. /
     
346. 
     
347. 
     
348. Section 5: Configuring Parallel Execution
     
349. 1、
     
350. alter system set parallel_max_servers=100;
     
351. alter system set parallel_min_servers=10;
     
352. 
     
353. 2、???????????
     
354. alter system set PARALLEL_MIN_PERCENT=60 scope=spfile;
     
355. 
     
356. 3、
     
357. ALTER TABLE "SH"."SALES" PARALLEL 8;
     
358. 
     
359. 4、????????????????????
     
360. @utlxplan.sql
     
361. alter session enable parallel dml
     
362. /
     
363. TRUNCATE TABLE plan_table
     
364. /
     
365. EXPLAIN PLAN
     
366.     SET STATEMENT_ID = 'Raise quantity'
     
367.     INTO plan_table
     
368.     FOR UPDATE  sh.sales SET quantity_sold=quantity_sold+100
     
369. /
     
370. INSERT INTO sh.par_tab VALUES('Raise quantity')
     
371. /
     
372. COMMIT
     
373. /
     
374. SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())
     
375. /
     
376. 
     
377. 
     
378. Section 6: Optimize the space usage for LOB Data
     
379. 1、
     
380.   create tablespace sf_data datafile '/u01/app/oracle/oradata/PROD1/sf_data.dbf' size 150m         
     
381.   autoextend on extent management local uniform size 64M;
     
382. 
     
383. 2、5
     
384.   create table oe.CUSTOMER_PROFILES(
     
385.         CUST_ID  NUMBER,
     
386.         FIRST_NAME  VARCHAR2(20),
     
387.         LAST_NAME  VARCHAR2(30),
     
388.         PROFILE_INFO  BLOB
     
389. )
     
390. lob (profile_info) store as securefile
     
391. (
     
392.         disable storage in row
     
393.         deduplicate
     
394.         compress high
     
395.         tablespace sf_data
     
396. )
     
397. tablespace sf_data
     
398. /
     
399. 
     
400. 3、
     
401.   create directory cust_files as '/home/oracle/scripts/cust_files';
     
402.   grant read,write on directory cust_files to oe;
     
403.   conn oe/OE
     
404. 4、  @3_4_6.sql
     
405.   
     
406. 
     
407. 
     
408. Section 7: Creating a Partitioned Table to save storing unnecessary columns
     
409. conn sh/sh
     
410. 
     
411. create table sh.new_order_items(
     
412.         order_id number(12) not null,
     
413.         product_id number not null,
     
414.         quantity number not null,
     
415.         sales_amount number not null,
     
416.         constraint fk_order_id foreign key (order_id)
     
417.         references sh.new_orders(order_id)
     
418. )
     
419. partition by reference (fk_order_id);
     
420. /
     
421. 
     
422. @3_7_1.sql
     
423. 
     
424. 
     
425. Section 8: Using Partitioning to store non-existent partitions
     
426. conn sys/oracle@prod1 as sysdba
     
427. @3_8_1.sql
     
428. 
     
429. create table sh.sales_history_2006(
     
430. PROD_ID          NUMBER NOT NULL,
     
431. CUST_ID         NUMBER NOT NULL,
     
432. TIME_ID         DATE NOT NULL,
     
433. CHANNEL_ID         NUMBER NOT NULL,
     
434. PROMO_ID         NUMBER NOT NULL,
     
435. QUANTITY_SOLD         NUMBER(10,2) NOT NULL,
     
436. AMOUNT_SOLD         NUMBER(10,2) NOT NULL
     
437. )
     
438. partition by range (time_id)
     
439. interval (interval '1' month)
     
440. (
     
441.          partition sal1 values less than (to_date('2003-01-01','YYYY-MM-DD')) tablespace SALES_TBS1,
     
442.          partition sal2 values less than (to_date('2004-01-01','YYYY-MM-DD')) tablespace SALES_TBS2,
     
443.          partition sal3 values less than (to_date('2005-01-01','YYYY-MM-DD')) tablespace SALES_TBS3,
     
444.          partition sal4 values less than (to_date('2006-01-01','YYYY-MM-DD')) tablespace SALES_TBS4
     
445. )
     
446. /
     
447. @3_8_2.sql
     
448. 
     
449. Section 9: Configuring the Database to Retrieve All Previous Versions of the Table Rows
     
450. 
     
451. create tablespace fratbs datafile '/u01/app/oracle/oradata/PROD1/fratbs.dbf' size 50m;
     
452. create user fra_admin identified by abc123456;
     
453. grant FLASHBACK ARCHIVE ADMINISTER,connect,resource to fra_admin;
     
454. connect fra_admin/abc123456
     
455. create flashback archive fra1 tablespace fratbs retention 1 year;
     
456. GRANT FLASHBACK ARCHIVE ON FRA1 TO SH;
     
457. conn sh/sh
     
458. alter table sh.promotions flashback archive fra1;
     
459. 
     
460. 
     
461. Section 10: Capturing and Propagating Streams
     
462. ---on emrep/prod1
     
463. ---archive log list;
     
464. ---check hr user on target
     
465. 
     
466. alter system set global_names=true;
     
467. create directory src_dir as '/home/oracle/files';
     
468. create directory dst_dir as '/home/oracle/files';
     
469. 
     
470. create tablespace streams_tbs datafile '/u01/app/oracle/oradata/PROD1/streams_tbs.dbf' size 100m
     
471. /
     
472. create user strmadmin identified by streams_123 default tablespace streams_tbs
     
473. /
     
474. grant dba,select_catalog_role,select any dictionary to strmadmin
     
475. /
     
476. begin
     
477.         dbms_streams_auth.grant_admin_privilege('STRMADMIN',true);
     
478. end;
     
479. /
     
480. 
     
481. conn strmadmin/streams_123@prod1
     
482. create database link emrep
     
483. connect to strmadmin identified by streams_123
     
484. using 'emrep'
     
485. /
     
486. 
     
487. conn strmadmin/streams_123@emrep
     
488. create database link prod1
     
489. connect to strmadmin identified by streams_123
     
490. using 'prod1'
     
491. /
     
492. 
     
493. conn strmadmin/streams_123@prod1
     
494. 
     
495. begin
     
496.         dbms_streams_adm.maintain_tables(
     
497.                 table_names=>'hr.employees,hr.departments',
     
498.                 source_directory_object=>'SRC_DIR',
     
499.                 destination_directory_object=>'DST_DIR',
     
500.                 source_database=>'PROD1',
     
501.                 destination_database=>'EMREP',
     
502.                 capture_name => 'PROD1_CAPTURE',
     
503.                 propagation_name => 'PROD1_PROPAGATION',
     
504.                 perform_actions=>true,
     
505.                 bi_directional=>false,
     
506.                 include_ddl=>true,
     
507.                 instantiation=>dbms_streams_adm.instantiation_table_network
     
508.         );
     
509. end;
     
510. /
     
511. 
     
512. 
     
513. 
     
514. select STREAMS_TYPE,rule_name,RULE_TYPE from DBA_STREAMS_TABLE_RULES
     
515. 
     
516. conn hr/hr
     
517. @3_10_4.sql
     
518. 
     
519. BEGIN
     
520. DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
     
521. rule_name => 'departments4',
     
522. transform_function => 'hr.zero_sal');
     
523. END;
     
524. /
     
525. 
     
526. ---if error  see
     
527. EXECUTE DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
     
528. execute dbms_streams_adm.RECOVER_OPERATION(operation_mode = 'FORWARD'/operation_mode = 'ROLLBACK'/operation_mode = 'PURGE');
     
529. DBA_RECOVERABLE_SCRIPT_ERRORS
     
530. DBA_RECOVERABLE_SCRIPT
     
531. DBA_RECOVERABLE_SCRIPT_PARAMS
     
532. DBA_RECOVERABLE_SCRIPT_BLOCKS
     
533. 
     
534. **********************************************************
     
535. Skillset 4: Performance Management                       *
     
536. **********************************************************
     
537. Section 1: Configuring the Resource Manager
     
538. 
     
539. alter user hr identified by hr account unlock;
     
540. alter user sh identified by sh account unlock;
     
541. alter user bi identified by bi account unlock;
     
542. alter user oe identified by OE account unlock;
     
543. 
     
544. BEGIN
     
545. dbms_resource_manager.clear_pending_area();
     
546. dbms_resource_manager.create_pending_area();
     
547. dbms_resource_manager.create_consumer_group(consumer_group => 'OLTP',comment=>'');
     
548. dbms_resource_manager.submit_pending_area();
     
549. BEGIN
     
550. dbms_resource_manager_privs.grant_switch_consumer_group(?,?,case ? when 'false' then false when 'true' then true else false end);
     
551. END;
     
552. END;
     
553. 
     
554. BEGIN
     
555. dbms_resource_manager.clear_pending_area();
     
556. dbms_resource_manager.create_pending_area();
     
557. dbms_resource_manager.create_plan( plan => 'DAYTIME',COMMENT=>'');
     
558. dbms_resource_manager.create_plan_directive(
     
559.     plan =>'DAYTIME',
     
560.     group_or_subplan => 'LOW_GROUP',
     
561.     COMMENT=>''
     
562. );
     
563. 
     
564. dbms_resource_manager.create_plan_directive(
     
565.     plan =>'DAYTIME',
     
566.     group_or_subplan => 'OLTP',
     
567.     comment => '',
     
568.     switch_io_reqs =>10000 ,
     
569.     switch_io_megabytes =>2500,
     
570.     switch_group =>'LOW_GROUP',
     
571.     switch_for_call=> TRUE
     
572. );
     
573. dbms_resource_manager.create_plan_directive(
     
574.     plan =>'DAYTIME',
     
575.     group_or_subplan => 'OTHER_GROUPS',
     
576.     comment => '',
     
577.     switch_io_reqs =>10000 ,
     
578.     switch_io_megabytes =>2500,
     
579.     switch_group =>'LOW_GROUP',
     
580.     switch_for_call => TRUE
     
581. );
     
582. dbms_resource_manager.submit_pending_area();
     
583. END;
     
584. 
     
585. alter system set resource_manager_plan='DAYTIME';
     
586. 
     
587. 
     
588. Section 2: Tuning SQL Statements
     
589. 2.1
     
590.    alter system set RESULT_CACHE_MAX_SIZE=15m;
     
591.    alter system set CLIENT_RESULT_CACHE_SIZE = 128m;
     
592.    
     
593. set feedback on
     
594. set echo on
     
595. pause Press enter to continue
     
596. set autot on explain stat
     
597. 
     
598. SELECT /*+RESULT_CACHE*/ department_id, AVG(salary)
     
599. FROM employees
     
600. GROUP BY department_id
     
601. /
     
602. 
     
603. set autot off
     
604. pause Press enter to continue
     
605. 
     
606. Update employees set salary=salary*.1+salary
     
607. where department_id=20
     
608. /
     
609. 
     
610. commit
     
611. /
     
612. set autot on explain stat
     
613. pause Press enter to continue
     
614. 
     
615. SELECT /*+RESULT_CACHE*/ department_id, AVG(salary)
     
616. FROM employees
     
617. GROUP BY department_id
     
618. /
     
619. 
     
620. set autot off
     
621. Update employees set salary=salary*.1+salary
     
622. where job_id='SA_REP'
     
623. /
     
624. 
     
625. commit
     
626. /
     
627. 
     
628. pause Press enter to continue
     
629. set autot on explain stat
     
630. 
     
631. SELECT /*+RESULT_CACHE*/ department_id, AVG(salary)
     
632. FROM employees
     
633. GROUP BY department_id
     
634. /
     
635. 
     
636. set autot off
     
637. 
     
638. 2.2
     
639.    select dbms_stats.create_extended_stats('SH','CUSTOMERS','(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)') from dual;
     
640.    exec dbms_stats.gather_table_stats('SH','CUSTOMERS',method_opt=>'for all columns size 1 for columns (CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID) size 3');
     
641.    select * from USER_STAT_EXTENSIONS
     
642.    select dbms_stats.show_extended_stats_name('SH','CUSTOMERS','(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)') from dual;
     
643.    
     
644. 2.3
     
645.    exec dbms_stats.set_table_prefs('SH','SALES','PUBLISH','false');
     
646.    exec dbms_stats.gather_table_stats('SH','SALES',NO_INVALIDATE=>TRUE);
     
647.    alter session set optimizer_use_pending_statistics = true;
     
648.    
     
649. 
     
650. Section 3: Index Tuning
     
651. CREATE INDEX PROD_IDX on sh.PRODUCT_INFORMATION_PART (PRODUCT_ID) GLOBAL PARTITION BY HASH (PRODUCT_ID) PARTITIONS 4 STORE IN (TBS1);
     
652. (PARTITION p1  TABLESPACE tbs_1,PARTITION p2  TABLESPACE tbs_2,PARTITION p3  TABLESPACE tbs_3,PARTITION p4  TABLESPACE tbs_4);
     
653. 
     
654. Section 4: Real Application Testing
     
655. 
     
656. 4.1/4.2
     
657. impdp apps/Apps1234 dumpfile=dump_dir:appsstg.dmp full=y logfile=dump_dir:impdp.log
     
658. conn apps/Apps1234
     
659. @?/rdbms/admin/utlxplan.sql
     
660. grant advisor,dba to apps;
     
661. 
     
662. begin
     
663.         dbms_sqltune.unpack_stgtab_sqlset(
     
664.                 replace=>true,
     
665.                 staging_table_name=>'STS_PS_TAB',
     
666.                 staging_schema_owner=>'APPS'
     
667.         );
     
668. end;
     
669. /
     
670. 
     
671. dbms_sqltune.create_tuning_task
     
672. dbms_sqltune.execute_tuning_task('sql_tuning_test');
     
673. USER_ADVISOR_TASKS
     
674. dbms_sqltune.report_tning_task
     
675. 
     
676. --SQL Performance Analyzer
     
677. alter system set optimizer_features_enable='10.2.0.1';
     
678. var tname varchar2(30);
     
679. exec :tname:= dbms_sqlpa.create_analysis_task(sqlset_name => 'STS_PS', task_name => 'MYSPA');
     
680. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'TEST EXECUTE', execution_name => 'before');
     
681. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
     
682. alter system set optimizer_features_enable='11.1.0.7';
     
683. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'TEST EXECUTE', execution_name => 'after');
     
684. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
     
685. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'COMPARE PERFORMANCE');
     
686. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
     
687. 
     
688. EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>:tname,execution_type => 'compare performance', execution_params =>
     
689. dbms_advisor.arglist( 'execution_name1', 'before','execution_name2', 'after', 'comparison_metric', 'buffer_gets'));
     
690. 
     
691. insert into plan_table select statement_id,plan_id,timestamp,remarks,OPERATION,OPTIONS,OBJECT_NODE,OBJECT_OWNER,OBJECT_NAME,OBJECT_ALIAS,OBJECT_INSTANCE,OBJECT_TYPE,OPTIMIZER,SEARCH_COLUMNS,ID,PARENT_ID,DEPTH,POSITION,COST,CARDINALITY,BYTES,OTHER_TAG,PARTITION_START,PARTITION_STOP,PARTITION_ID,OTHER,DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES,PROJECTION,TIME,QBLOCK_NAME,OTHER_XML
     
692. from DBA_ADVISOR_SQLPLANS where task_name='MYSPA' and execution_name='SECOND_SQL_TRIAL' and sql_id in('',''
     
693. 
     
694. 
     
695. 4.3
     
696. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
     
697. 
     
698. create directory capture_dir '/home/oracle/scripts/capture';
     
699. 
     
700. BEGIN
     
701.   DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE ('CAPTURE_DIR');
     
702. END;
     
703. /
     
704. 
     
705. BEGIN
     
706.   DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => 'REPLAY1',replay_dir => 'CAPTURE_DIR');
     
707. END;
     
708. /
     
709. 
     
710. select * from DBA_WORKLOAD_CONNECTION_MAP=>connection_id
     
711. 
     
712. BEGIN
     
713.   DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 1,
     
714.                            replay_connection => 'hostname:1521/PROD1');
     
715. END;
     
716. /
     
717. 
     
718. BEGIN
     
719.   DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 2,
     
720.                            replay_connection => 'hostname:1521/PROD1');
     
721. END;
     
722. /
     
723. 
     
724. BEGIN
     
725.   DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE);
     
726. END;
     
727. /
     
728. 
     
729. wrc REPLAYDIR=/home/oracle/scripts/capture USERID=system PASSWORD=oracle
     
730. wrc system/oracle@test mode=replay replaydir=/home/oracle/scripts/capture
     
731. 
     
732. begin
     
733. dbms_workload_replay.start_replay;
     
734. end;
     
735. /
     
736. 
     
737. 
     
738. ----export to AWR
     
739. BEGIN
     
740.   DBMS_WORKLOAD_REPLAY.EXPORT_AWR (replay_id => 1);
     
741. END;
     
742. /
     
743. 
     
744. -----interpruted replay
     
745. BEGIN
     
746.   DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
     
747. END;
     
748. /
     
749. 
     
750. BEGIN
     
751. DBMS_WORKLOAD_REPLAY.DELETE_REPLAY_INFO(REPLAY_ID=>?);
     
752. END;
     
753. /
     
754. 
     
755. DBMS_WORKLOAD_CAPTURE
     
756. 
     
757. select * from DBA_WORKLOAD_CONNECTION_MAP
     
758. 
     
759. select * from DBA_WORKLOAD_REPLAYS
     
760. 
     
761. 
     
762. Section 5: Evolving SQL Plan Baselines
     
763. 5.1
     
764. alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
     
765. 
     
766. @4_5_1.sql
     
767. 
     
768. select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
     
769. 
     
770. declare
     
771. test binary_integer;
     
772. begin
     
773. test := dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'')
     
774. end;
     
775. /
     
776. 
     
777. variable ret clob
     
778. declare
     
779. begin
     
780.         :ret :=
     
781.                 dbms_spm.evolve_sql_plan_baseline(
     
782.                         'SYS_SQL_f6cb7f742ef93547',
     
783.                         'SYS_SQL_PLAN_2ef9354754bc8843'
     
784.                 );
     
785. end;
     
786. /
     
787. print ret
     
788. 
     
789. 
     
790. 
     
791. select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
     
792. 
     
793. Section 6: Capturing Performance Statistics
     
794. BEGIN
     
795.     DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
     
796.                    start_time => '2010-07-31 18:00:00',
     
797.                    end_time => '2010-08-01 18:00:00',
     
798.                    baseline_name => 'WEEKEND',
     
799.                    template_name => 'WEEKEND');
     
800. END;
     
801. /
     
802. 
     
803. PERFORMANCE TUNNING GUIDE 5
     
804. 
     

复制代码