第79/80/81/82次：2016-02-20/21双休日

botang

2016-02-20.sql：

1. select *
   
2.   from dba_lobs l
   
3. where l.owner = 'SH'
   
4.    and l.table_name = 'TAB1';
   
5. 
   
6. select *
   
7.   from dba_tab_partitions tp
   
8. where tp.table_owner = 'SH'
   
9.    and tp.table_name = 'TAB1';
   
10. 
    
11. select *
    
12.   from dba_profiles
    
13. where resource_type = 'PASSWORD'
    
14.    and profile = 'PROFILE1';
    
15. 
    
16. alter profile profile1 limit FAILED_LOGIN_ATTEMPTS default PASSWORD_LOCK_TIME default;
    
17. 
    
18. alter user hr profile profile1;
    
19. 
    
20. select * from dba_users where username = 'HR';
    
21. 
    
22. alter profile profile1 limit PASSWORD_LIFE_TIME 1 / 1440 PASSWORD_GRACE_TIME 1 / 1440;
    
23. 
    
24. alter profile profile1 limit PASSWORD_REUSE_TIME 5 / 1440 PASSWORD_REUSE_MAX 1;
    
25. 
    
26. --oracle_5U 11:44:09  change
    
27. 
    
28. 11 :49 :09 ---->oracle_4U(bug's )
    
29. 
    
30. alter profile profile1 limit password_verify_function verify_function_11g;
    
31. 
    
32. ---
    
33. 
    
34. select  * from  dict where table_name like '%AUDIT%';
    
35. 
    
36. 
    
37. 
    
38. select  * from dba_stmt_audit_opts
    
39. minus
    
40. select  * from DBA_PRIV_AUDIT_OPTS ;
    
41. 
    
42. select  * from dba_common_audit_trail   order by 6 desc;
    
43. 
    
44. select * from v$xml_audit_trail ;
    
45. 
    
46. noaudit create session;
    
47. 
    
48. audit create session by hr ;
    
49. 
    
50. alter table aud$ move tablespace users;
    
51. 
    
52. alter user hr profile default;
    
53. 
    
54. select  * from dba_obj_audit_opts  ;
    
55. 
    
56. audit table by hr whenever  successful;
    
57. ---
    
58. 
    
59. select  * from dict where table_name like '%ENCRYP%';
    
60. 
    
61. select  * from V$ENCRYPTED_TABLESPACES;
    
62. 
    
63. create tablespace tbsen  datafile size 10M
    
64. encryption default storage ( encrypt ) ;
    
65. 
    
66. select  * from dba_encrypted_columns;
    
67. 
    
68. alter system set encryption wallet close identified by "oracle_7U";
    
69. 
    
70. select  * from v$encryption_wallet;
    
71. 
    
72. alter system set encryption wallet open identified by "oracle_7U";
    
73. 
    
74. ----
    
75. 
    
76. select  * from dba_policies;
    
77. 
    
78. select * from dba_audit_policies;
    
79. 
    
80. select  * from dba_fga_audit_trail;
    
81. 
    
82. select  * from tfga;
    
83. 
    
84. -----
    
85. create table tfga( a_username varchar2(30) ,
    
86.                              a_ipaddress varchar2(50),
    
87.                              a_time  date ,
    
88.                              a_osname  varchar2(30),
    
89.                              a_authen varchar2(30) ,
    
90.                              a_policy  varchar2(30));
    
91. 
    
92. create  or replace procedure procfga (p_1 varchar2,
    
93.                                                                p_2 varchar2,
    
94.                                                                p_3 varchar2 )
    
95. is
    
96. begin
    
97.     insert into tfga values ( sys_context('userenv','session_user'),
    
98.                                            sys_context('userenv','ip_address'),
    
99.                                            sysdate,
    
100.                                            sys_context('userenv','os_user'),
     
101.                                            sys_context('userenv','authentication_type'),
     
102.                                             p_3 );
     
103. end;
     
104. 
     
105. select  * from user_errors;
     
106. 
     
107. 
     
108. 
     
109. begin
     
110.     dbms_fga.add_policy(object_schema => 'HR',
     
111.                                          object_name => 'EMPLOYEES',
     
112.                                          policy_name => 'POLICY1',
     
113.                                          audit_condition => 'employee_id=100',
     
114.                                          audit_column => 'salary,commission_pct',
     
115.                                          handler_schema => 'SYS',
     
116.                                          handler_module => 'PROCFGA',
     
117.                                          statement_types =>'SELECT,UPDATE',
     
118.                                          audit_trail => dbms_fga.DB+dbms_fga.EXTENDED,
     
119.                                          audit_column_opts => dbms_fga.ANY_COLUMNS);
     
120.      end;
     
121.                                           
     
122.                                          
     
123.       BEGIN
     
124.           dbms_sqltune.create_sqlset(sqlset_name => 'STS_JFV', sqlset_owner =>'SYSTEM');
     
125.         END;
     
126.         
     
127.       DECLARE
     
128.          bf VARCHAR2(114);
     
129.        BEGIN
     
130.            bf := q'#UPPER(PARSING_SCHEMA_NAME) = 'APPS' AND UPPER(SQL_TEXT) NOT LIKE '%APPLICAT%' AND UPPER(MODULE) LIKE 'DWH_TEST%' #';
     
131.      dbms_sqltune.capture_cursor_cache_sqlset( sqlset_name=>'STS_JFV', time_limit=>'360', repeat_interval=>'5', basic_filter=>bf, sqlset_owner=>'SYSTEM');
     
132.             
     
133.              end;
     
134.                                    
     
135.                                          
     
136.                                          
     
137.         
     
138. 
     
139. 
     

复制代码

[url=]file:///4/capture_files/bSave-Stf.gif[/url]file:///4/capture_files/t.gif[url=]file:///4/capture_files/bOK9n9n.gif[/url]

Database Capture Report For ORCL
[/table]

DB NameDB IdReleaseRACCapture NameStatus

ORCL 1423490332 11.2.0.1.0NOCAPTURE-ORCL-1COMPLETED

Information Capture Start time: 21-Feb-16 09:42:19 (SCN = 2032399) End time: 21-Feb-16 09:45:36 (SCN = 2038773) Duration: 3 minutes 17 seconds Capture size: 887.38 KB Directory object: DBREPLAY Directory path: /home/oracle/solutions/dbreplay Directory shared in RAC: TRUE Filters used: 2 EXCLUSION filters

DB Capture Report

[url=]Capture Statistics[/url]

[url=]Workload Captured[/url]

[url=]Workload Not Captured - Contains Unreplayable Calls[/url]

[url=]Workload Not Captured - User Filtered[/url]

[url=]Workload Not Captured - DB Scheduler Jobs and Background Activity[/url]

[url=]SQL Text[/url]

[url=]Workload Filters[/url]

[url=]Back to Top[/url]

Captured Workload Statistics

'Value' represents the corresponding statistic aggregated        across the entire captured database workload.

'% Total' is the percentage of 'Value' over the corresponding        system-wide aggregated total.

Statistic Name Value % Total DB time (secs) 4.91 86.14 Average Active Sessions 0.02 User calls captured 4275 92.73 User calls captured with Errors 0 Session logins 9 40.91 Transactions 2100 98.82

[url=]Back to Top[/url]

Workload Captured

[url=]Top Events Captured[/url]

[url=]Top Service/Module Captured[/url]

[url=]Top SQL Captured[/url]

[url=]Top Sessions Captured[/url]

[url=]Back to Top[/url]

Top Events Captured                  No data exists for this section of the report.

[url=]Back to Workload Captured[/url]

[url=]Back to Top[/url]

Top Service/Module Captured                  No data exists for this section of the report.

[url=]Back to Workload Captured[/url]

[url=]Back to Top[/url]

Top SQL Captured                  No data exists for this section of the report.

[url=]Back to Workload Captured[/url]

[url=]Back to Top[/url]

Top Sessions Captured                  No data exists for this section of the report.

[url=]Back to Workload Captured[/url]

[url=]Back to Top[/url]

Workload Not Captured - Contains Unreplayable Calls

[url=]Top Events containing Unreplayable Calls[/url]

[url=]Top Service/Module containing Unreplayable Calls[/url]

[url=]Top SQL containing Unreplayable Calls[/url]

[url=]Top Sessions containing Unreplayable Calls[/url]

[url=]Back to Top[/url]

Top Events containing Unreplayable Calls                  No data exists for this section of the report.

[url=]Back to Workload Not Captured - Contains Unreplayable Calls[/url]

[url=]Back to Top[/url]

Top Service/Module containing Unreplayable Calls                  No data exists for this section of the report.

[url=]Back to Workload Not Captured - Contains Unreplayable Calls[/url]

[url=]Back to Top[/url]

Top SQL containing Unreplayable Calls                  No data exists for this section of the report.

[url=]Back to Workload Not Captured - Contains Unreplayable Calls[/url]

[url=]Back to Top[/url]

Top Sessions containing Unreplayable Calls                  No data exists for this section of the report.

[url=]Back to Workload Not Captured - Contains Unreplayable Calls[/url]

[url=]Back to Top[/url]

Workload Not Captured - User Filtered

[url=]Top Events Filtered Out[/url]

[url=]Top Service/Module Filtered Out[/url]

[url=]Top SQL Filtered Out[/url]

[url=]Top Sessions Filtered Out[/url]

[url=]Back to Top[/url]

Top Events Filtered Out Event Event Class % Event Avg Active Sessions rdbms ipc reply Other 16.67 0.05 [url=]Back to Workload Not Captured - User Filtered[/url]

[url=]Back to Top[/url]

Top Service/Module Filtered Out Service Module % Activity Action % Action SYS$USERS oraagent.bin@station37.example.com (TNS V1-V3) 16.67 UNNAMED 16.67 [url=]Back to Workload Not Captured - User Filtered[/url]

[url=]Back to Top[/url]

Top SQL Filtered Out SQL ID % Activity Event % Event SQL Text [url=]1h50ks4ncswfn[/url] 16.67 rdbms ipc reply 16.67 ** SQL Text Not Available ** [url=]Back to Workload Not Captured - User Filtered[/url]

[url=]Back to Top[/url]

Top Sessions Filtered Out

'# Samples Active' shows the number of ASH samples in which the session        was found waiting for that particular event. The percentage shown       in this column is calculated with respect to wall clock time       and not total database activity.

'XIDs' shows the number of distinct transaction IDs sampled in ASH        when the session was waiting for that particular event

For sessions running Parallel Queries, this section will NOT aggregate        the PQ slave activity into the session issuing the PQ. Refer to        the 'Top Sessions running PQs' section for such statistics.

Sid, Serial# % Activity Event % Event User Program # Samples Active XIDs 135,    1 16.67 rdbms ipc reply 16.67 SYS oraagent.bin@s...m (TNS V1-V3) 1/20 [  5%] 0 [url=]Back to Workload Not Captured - User Filtered[/url]

[url=]Back to Top[/url]

Workload Not Captured - DB Scheduler Jobs and Background Activity

[url=]Top Events (Jobs and Background Activity)[/url]

[url=]Top Service/Module (Jobs and Background Activity)[/url]

[url=]Top SQL (Jobs and Background Activity)[/url]

[url=]Top Sessions (Jobs and Background Activity)[/url]

[url=]Back to Top[/url]

Top Events (Jobs and Background Activity) Event Event Class % Event Avg Active Sessions CPU + Wait for CPU CPU 33.33 0.10 ARCH wait for process start 3 Other 16.67 0.05 db file sequential read User I/O 16.67 0.05 resmgr:cpu quantum Scheduler 16.67 0.05 [url=]Back to Workload Not Captured - DB Scheduler Jobs and Background Activity[/url]

[url=]Back to Top[/url]

Top Service/Module (Jobs and Background Activity) Service Module % Activity Action % Action SYS$USERS DBMS_SCHEDULER 66.67 0000001 STARTED2001 16.67 ORA$AT_OS_OPT_SY_65 16.67 ORA$AT_SA_SPC_SY_66 16.67 SYS$BACKGROUND UNNAMED 16.67 UNNAMED 16.67 [url=]Back to Workload Not Captured - DB Scheduler Jobs and Background Activity[/url]

[url=]Back to Top[/url]

Top SQL (Jobs and Background Activity) SQL ID % Activity Event % Event SQL Text [url=]2nszajb0qbyvp[/url] 16.67 CPU + Wait for CPU 16.67 DECLARE job BINARY_INTEGER := ... [url=]3nkd3g3ju5ph1[/url] 16.67 db file sequential read 16.67 select obj#, type#, ctime, mti... [url=]59v4zh1ac3v2a[/url] 16.67 resmgr:cpu quantum 16.67 DECLARE job BINARY_INTEGER := ... [url=]acxhd97hybcgw[/url] 16.67 CPU + Wait for CPU 16.67 call dbms_ir.reevaluateopenfai... [url=]Back to Workload Not Captured - DB Scheduler Jobs and Background Activity[/url]

[url=]Back to Top[/url]

Top Sessions (Jobs and Background Activity)

'# Samples Active' shows the number of ASH samples in which the session        was found waiting for that particular event. The percentage shown       in this column is calculated with respect to wall clock time       and not total database activity.

'XIDs' shows the number of distinct transaction IDs sampled in ASH        when the session was waiting for that particular event

For sessions running Parallel Queries, this section will NOT aggregate        the PQ slave activity into the session issuing the PQ. Refer to        the 'Top Sessions running PQs' section for such statistics.

Sid, Serial# % Activity Event % Event User Program # Samples Active XIDs 19,    3 16.67 db file sequential read 16.67 SYS oracle@station...le.com (J004) 1/20 [  5%] 0 24,    1 16.67 CPU + Wait for CPU 16.67 SYS oracle@station...le.com (J005) 1/20 [  5%] 0 130,    1 16.67 ARCH wait for process start 3 16.67 SYS oracle@station...le.com (LGWR) 1/20 [  5%] 0 136,    7 16.67 CPU + Wait for CPU 16.67 SYS oracle@station...le.com (J001) 1/20 [  5%] 0 146,    1 16.67 resmgr:cpu quantum 16.67 SYS oracle@station...le.com (J003) 1/20 [  5%] 0 [url=]Back to Workload Not Captured - DB Scheduler Jobs and Background Activity[/url]

[url=]Back to Top[/url]

Complete List of SQL Text

SQL Id SQL Text 1h50ks4ncswfn ** SQL Text Not Available ** 2nszajb0qbyvp DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS'), wwv_flow_platform.get_preference('SMTP_HOST_PORT')); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 3nkd3g3ju5ph1 select obj#, type#, ctime, mtime, stime,  status,  dataobj#,  flags,  oid$,  spare1,  spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null 59v4zh1ac3v2a DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :mydate;  broken BOOLEAN := FALSE;  job_name VARCHAR2(30) := :job_name;  job_subname VARCHAR2(30) := :job_subname;  job_owner VARCHAR2(30) := :job_owner;  job_start TIMESTAMP WITH TIME ZONE := :job_start;  job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;  window_start TIMESTAMP WITH TIME ZONE := :window_start;  window_end TIMESTAMP WITH TIME ZONE := :window_end;  chain_id VARCHAR2(14) :=  :chainid;  credential_owner varchar2(30) := :credown;  credential_name  varchar2(30) := :crednam;  destination_owner varchar2(30) := :destown;  destination_name varchar2(30) := :destnam;  job_dest_id varchar2(14) := :jdestid;  BEGIN  DECLARE         ename VARCHAR2(30);       BEGIN         ename := dbms_sqltune.execute_tuning_task(                    'SYS_AUTO_SQL_TUNING_TASK');       END;  :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; acxhd97hybcgw call dbms_ir.reevaluateopenfailures ( :0, :1, :2, :3 )

[url=]Back to Top[/url]

Workload Filters # Filter Name Type Attribute Value 1 ORACLE MANAGEMENT AGENT (DEFAULT) EXCLUDE PROGRAM emagent% 2 ORACLE MANAGEMENT SERVICE (DEFAULT) EXCLUDE PROGRAM OMS

[url=]Back to Top[/url]

End of Report

[url=]file:///4/capture_files/bSave-Stf.gif[/url]file:///4/capture_files/t.gif[url=]file:///4/capture_files/bOK9n9n.gif[/url] ----------------------------

[url=]file:///4/replay_files/bSave-Stf.gif[/url]file:///4/replay_files/t.gif[url=]file:///4/replay_files/bOK9n9n.gif[/url]       DB Replay Report for       REPLAY-ORCL-2

[table]
DB Name  DB Id  Release  RAC  Replay Name  Replay Status
ORCL142349033211.2.0.1.0NOREPLAY-ORCL-2COMPLETED
      Replay Information   

Information	Replay	Capture
Name	REPLAY-ORCL-2	CAPTURE-ORCL-1
Status	COMPLETED	COMPLETED
Database Name	ORCL	ORCL
Database Version	11.2.0.1.0	11.2.0.1.0
Start Time	21-02-16 10:10:08	21-02-16 09:42:19
End Time	21-02-16 10:12:35	21-02-16 09:45:36
Duration	2 minutes 27 seconds	3 minutes 17 seconds
Directory Object	DBREPLAY1	DBREPLAY1
Directory Path	/home/oracle/solutions/dbreplay1	/home/oracle/solutions/dbreplay1

      Replay Options   

Option Name	Value
Synchronization	SCN
Connect Time	100%
Think Time	100%
Think Time Auto Correct	TRUE
Number of WRC Clients	1          (1 Completed,                          0                        Running          )

      Replay Statistics   

Statistic	Replay	Capture
DB Time	4.242 seconds	4.911 seconds
Average Active Sessions	.03	.02
User calls	4275	4275
Network Time	1.146 seconds	.
Think Time	430.006 seconds	.

      Replay Divergence Summary   

Divergence Type	Count	% Total
Session Failures During Replay	0	0.00
Errors No Longer Seen During Replay	0	0.00
New Errors Seen During Replay	0	0.00
Errors Mutated During Replay	0	0.00
DMLs with Different Number of Rows Modified	0	0.00
SELECTs with Different Number of Rows Fetched	0	0.00

---

       Workload Profile     
       Top Events     
         (-) Hide      

No data exists for this section of the report.

       Top Service/Module/Action     
         (-) Hide      

No data exists for this section of the report.

       Top SQL with Top Events     
         (-) Hide      

No data exists for this section of the report.

       Top Sessions with Top Events     
         (-) Hide      

No data exists for this section of the report.

       Replay Divergence     
       Session Failures      
       By Application     
         (-) Hide      

No data exists for this section of the report.

       Error Divergence     
       By Application     
         (-) Hide      

No data exists for this section of the report.

       By SQL     
         (-) Hide      

No data exists for this section of the report.

       By Session     
         (-) Hide      

No data exists for this section of the report.

       DML Data Divergence     
       By Application     
         (-) Hide      

No data exists for this section of the report.

       By SQL     
         (-) Hide      

No data exists for this section of the report.

       By Divergence magnitude     
         (-) Hide      

No data exists for this section of the report.

       SELECT Data Divergence     
       By Application     
         (-) Hide      

No data exists for this section of the report.

       By Divergence magnitude     
         (-) Hide      

No data exists for this section of the report.

       Replay Clients Alerts     
         (-) Hide      

No data exists for this section of the report.

       Replay Filters     
         (-) Hide      

No data exists for this section of the report.

End of Report.

[url=]file:///4/replay_files/bSave-Stf.gif[/url]file:///4/replay_files/t.gif[url=]file:///4/replay_files/bOK9n9n.gif[/url]