Bo's Oracle Station

查看: 2609|回复: 0

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

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2016-2-22 20:34:49 | 显示全部楼层 |阅读模式
本帖最后由 botang 于 2016-2-22 20:37 编辑

2016-02-20.sql:
  1. select *
  2.   from dba_lobs l
  3. where l.owner = 'SH'
  4.    and l.table_name = 'TAB1';

  5. select *
  6.   from dba_tab_partitions tp
  7. where tp.table_owner = 'SH'
  8.    and tp.table_name = 'TAB1';

  9. select *
  10.   from dba_profiles
  11. where resource_type = 'PASSWORD'
  12.    and profile = 'PROFILE1';

  13. alter profile profile1 limit FAILED_LOGIN_ATTEMPTS default PASSWORD_LOCK_TIME default;

  14. alter user hr profile profile1;

  15. select * from dba_users where username = 'HR';

  16. alter profile profile1 limit PASSWORD_LIFE_TIME 1 / 1440 PASSWORD_GRACE_TIME 1 / 1440;

  17. alter profile profile1 limit PASSWORD_REUSE_TIME 5 / 1440 PASSWORD_REUSE_MAX 1;

  18. --oracle_5U 11:44:09  change

  19. 11 :49 :09 ---->oracle_4U(bug's )

  20. alter profile profile1 limit password_verify_function verify_function_11g;

  21. ---

  22. select  * from  dict where table_name like '%AUDIT%';



  23. select  * from dba_stmt_audit_opts
  24. minus
  25. select  * from DBA_PRIV_AUDIT_OPTS ;

  26. select  * from dba_common_audit_trail   order by 6 desc;

  27. select * from v$xml_audit_trail ;

  28. noaudit create session;

  29. audit create session by hr ;

  30. alter table aud$ move tablespace users;

  31. alter user hr profile default;

  32. select  * from dba_obj_audit_opts  ;

  33. audit table by hr whenever  successful;
  34. ---

  35. select  * from dict where table_name like '%ENCRYP%';

  36. select  * from V$ENCRYPTED_TABLESPACES;

  37. create tablespace tbsen  datafile size 10M
  38. encryption default storage ( encrypt ) ;

  39. select  * from dba_encrypted_columns;

  40. alter system set encryption wallet close identified by "oracle_7U";

  41. select  * from v$encryption_wallet;

  42. alter system set encryption wallet open identified by "oracle_7U";

  43. ----

  44. select  * from dba_policies;

  45. select * from dba_audit_policies;

  46. select  * from dba_fga_audit_trail;

  47. select  * from tfga;

  48. -----
  49. create table tfga( a_username varchar2(30) ,
  50.                              a_ipaddress varchar2(50),
  51.                              a_time  date ,
  52.                              a_osname  varchar2(30),
  53.                              a_authen varchar2(30) ,
  54.                              a_policy  varchar2(30));

  55. create  or replace procedure procfga (p_1 varchar2,
  56.                                                                p_2 varchar2,
  57.                                                                p_3 varchar2 )
  58. is
  59. begin
  60.     insert into tfga values ( sys_context('userenv','session_user'),
  61.                                            sys_context('userenv','ip_address'),
  62.                                            sysdate,
  63.                                            sys_context('userenv','os_user'),
  64.                                            sys_context('userenv','authentication_type'),
  65.                                             p_3 );
  66. end;

  67. select  * from user_errors;



  68. begin
  69.     dbms_fga.add_policy(object_schema => 'HR',
  70.                                          object_name => 'EMPLOYEES',
  71.                                          policy_name => 'POLICY1',
  72.                                          audit_condition => 'employee_id=100',
  73.                                          audit_column => 'salary,commission_pct',
  74.                                          handler_schema => 'SYS',
  75.                                          handler_module => 'PROCFGA',
  76.                                          statement_types =>'SELECT,UPDATE',
  77.                                          audit_trail => dbms_fga.DB+dbms_fga.EXTENDED,
  78.                                          audit_column_opts => dbms_fga.ANY_COLUMNS);
  79.      end;
  80.                                           
  81.                                          
  82.       BEGIN
  83.           dbms_sqltune.create_sqlset(sqlset_name => 'STS_JFV', sqlset_owner =>'SYSTEM');
  84.         END;
  85.         
  86.       DECLARE
  87.          bf VARCHAR2(114);
  88.        BEGIN
  89.            bf := q'#UPPER(PARSING_SCHEMA_NAME) = 'APPS' AND UPPER(SQL_TEXT) NOT LIKE '%APPLICAT%' AND UPPER(MODULE) LIKE 'DWH_TEST%' #';
  90.      dbms_sqltune.capture_cursor_cache_sqlset( sqlset_name=>'STS_JFV', time_limit=>'360', repeat_interval=>'5', basic_filter=>bf, sqlset_owner=>'SYSTEM');
  91.             
  92.              end;
  93.                                    
  94.                                          
  95.                                          
  96.         


复制代码


[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

InformationCapture
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 NameValue% 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
    EventEvent Class% EventAvg Active Sessions
    rdbms ipc replyOther
    16.67
    0.05
    [url=]Back to Workload Not Captured - User Filtered[/url]
    [url=]Back to Top[/url]

    Top Service/Module Filtered Out
    ServiceModule% ActivityAction% Action
    SYS$USERSoraagent.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% ActivityEvent% EventSQL 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#% ActivityEvent% EventUserProgram# Samples ActiveXIDs
    135,    1
    16.67
    rdbms ipc reply
    16.67
    SYSoraagent.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)
    EventEvent Class% EventAvg Active Sessions
    CPU + Wait for CPUCPU
    33.33
    0.10
    ARCH wait for process start 3Other
    16.67
    0.05
    db file sequential readUser I/O
    16.67
    0.05
    resmgr:cpu quantumScheduler
    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)
    ServiceModule% ActivityAction% Action
    SYS$USERSDBMS_SCHEDULER
    66.67
    0000001 STARTED2001
    16.67
    ORA$AT_OS_OPT_SY_65
    16.67
    ORA$AT_SA_SPC_SY_66
    16.67
    SYS$BACKGROUNDUNNAMED
    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% ActivityEvent% EventSQL 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#% ActivityEvent% EventUserProgram# Samples ActiveXIDs
    19,    3
    16.67
    db file sequential read
    16.67
    SYSoracle@station...le.com (J004)
    1/20 [  5%]
    0
    24,    1
    16.67
    CPU + Wait for CPU
    16.67
    SYSoracle@station...le.com (J005)
    1/20 [  5%]
    0
    130,    1
    16.67
    ARCH wait for process start 3
    16.67
    SYSoracle@station...le.com (LGWR)
    1/20 [  5%]
    0
    136,    7
    16.67
    CPU + Wait for CPU
    16.67
    SYSoracle@station...le.com (J001)
    1/20 [  5%]
    0
    146,    1
    16.67
    resmgr:cpu quantum
    16.67
    SYSoracle@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 IdSQL Text
    1h50ks4ncswfn** SQL Text Not Available **
    2nszajb0qbyvpDECLARE 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;
    3nkd3g3ju5ph1select 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
    59v4zh1ac3v2aDECLARE 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;
    acxhd97hybcgwcall dbms_ir.reevaluateopenfailures ( :0, :1, :2, :3 )

    [url=]Back to Top[/url]


    Workload Filters
    #Filter NameType Attribute Value
    1
    ORACLE MANAGEMENT AGENT (DEFAULT)EXCLUDEPROGRAMemagent%
    2
    ORACLE MANAGEMENT SERVICE (DEFAULT)EXCLUDEPROGRAMOMS

    [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-2CAPTURE-ORCL-1
              Status        COMPLETEDCOMPLETED
              Database Name        ORCLORCL
              Database Version        11.2.0.1.011.2.0.1.0
              Start Time        21-02-16 10:10:0821-02-16 09:42:19
              End Time        21-02-16 10:12:3521-02-16 09:45:36
              Duration        2 minutes 27 seconds3 minutes 17 seconds
              Directory Object        DBREPLAY1DBREPLAY1
              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]

    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    QQ|手机版|Bo's Oracle Station   

    GMT+8, 2024-3-29 21:15 , Processed in 0.036687 second(s), 24 queries .

    快速回复 返回顶部 返回列表