第51/52/53次：2015-09-02到2015-09-03上下午星期三，星期四上下午

botang

上完1Z0-052第12章 （52-44)
上完1Z0-053第14章   (52-45)
上完1Z0-053第15章 （52-46）
上完1Z0-052第13章   (52-47)

2015-09-02.sql：
select  count(*)  from dba_tables
   where table_name like  'WRI$%';
   
select  count(*)  from dba_tables
   where table_name like  'WRR$%';  
   
   select  count(*)  from dba_tables
   where table_name like  'WRH$%';   
   
      select  count(*)  from dba_tables
   where table_name like  'WRM$%';   
   
   select   t.num_rows
    from dba_tables t where t.owner='HR' and t.table_name='T05212';
   
    select   i.last_analyzed
      from dba_indexes  i where i.owner='HR' and i.index_name='I05212';
      
      select  * from dba_tab_col_statistics tcs
         where tcs.owner='HR' and tcs.table_name='T05212';
         
     select  * from dba_tab_col_statistics tcs
         where tcs.owner='HR' and tcs.table_name='EMPLOYEES';      
         
         begin
         DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( 29, 30 ,'SET4') ;
         end;
      
2015-09-03.sql:


select  * from dba_sql_profiles;

select   t.last_analyzed   from dba_tables t where t.owner='SYSMAN'
   and t.table_name='MGMT_CURRENT_VIOLATION';
   
   
   
select * from dba_alert_history  t  order by t.time_suggested desc ;

select  * from dba_outstanding_alerts   o order by o.time_suggested desc ;

select   file_name from dba_data_files ;

select  * from v$waitstat;

select * from v$system_event;

select  * from v$session_wait_history;

select * from dict where table_name like 'DBA%WAIT%';


select  * from DBA_HIST_WAITSTAT;


select  * from DBA_HIST_ACTIVE_SESS_HISTORY;

  select  * from V$ACTIVE_SESSION_HISTORY;

select  * from  dict where table_name like '%CLASS%';

select  * from V$SYSTEM_WAIT_CLASS;

select  * from  v$event_name;

select   dbms_stats.get_prefs(pname => 'PUBLISH')   from dual;

select  * from dba_tab_stat_prefs  p where p.owner='HR' and p.table_name='T05212';

begin
   dbms_stats.set_table_prefs(ownname => 'HR',tabname => 'T05212',
                                                pname => 'PUBLISH',pvalue => 'FALSE');
         end;
   
select  * from dba_tab_stat_prefs  p where p.owner='HR' and p.table_name='T05212';

select  t.num_rows, t.last_analyzed
  from dba_tables t where t.owner='HR' and t.table_name='T05212';

select  * from DBA_TAB_PENDING_STATS  t where t.owner='HR' and t.table_name='T05212';




begin
   dbms_stats.gather_table_stats('HR','T05212');
end;

begin
   dbms_stats.publish_pending_stats(ownname => 'HR',tabname => 'T05212');
end;

alter system flush shared_pool;


2015-09-03b:



select   n.name , se.value
  from v_$session s, v_$sesstat se , v_$statname n
  where s.sid=se.sid and se.statistic#=n.STATISTIC#
  and n.name='redo size' and s.terminal='pts/24';
  
  select   n.name , se.value
  from v_$session s, v_$sesstat se , v_$statname n
  where s.sid=se.sid and se.statistic#=n.STATISTIC#
  and n.name='undo change vector size' and s.terminal='pts/24';
  
  select  * from v$statname where name like '%undo%' ;
  
  
  select * from dba_views v where v.view_name='DBA_WORKLOAD_CAPTURES';
  
  
  select t.num_rows,t.last_analyzed
    from dba_tables t where t.owner='HR' and t.table_name='T04209_UNAME';
   
  
  begin
     dbms_stats.gather_table_stats('HR','T04209_UNAME');
  end;
   
    begin
       dbms_advisor.quick_tune(advisor_name => 'SQL Access Advisor',
       task_name => 'QUICK9',
        attr1 =>'select count(*)  from hr.tbig  group by line  having count(*)   >=500',
       template => 'SQLACCESS_WAREHOUSE');
     end;
        
       CREATE MATERIALIZED VIEW LOG ON
"HR"."TBIG"
WITH ROWID, SEQUENCE("TEXT")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "SYS"."MV$$_006C0000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT HR.TBIG.TEXT C1, COUNT(*) M1 FROM HR.TBIG GROUP BY HR.TBIG.TEXT;
   
grant create materialized view to hr;



ALTER MATERIALIZED VIEW LOG FORCE ON
"HR"."TBIG"
ADD ROWID, SEQUENCE("LINE")
INCLUDING NEW VALUES;

drop MATERIALIZED VIEW "SYS"."MV$$_006E0000";

CREATE MATERIALIZED VIEW "HR"."MV2"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT HR.TBIG.LINE C1, COUNT(*) M1 FROM HR.TBIG GROUP BY HR.TBIG.LINE;


2015-09-03C：


select  * from dict where table_name like 'V$%ADVICE';

select  * from V$MEMORY_TARGET_ADVICE;

select  * from V$SGA_TARGET_ADVICE;

select  *  from V$PGA_TARGET_ADVICE;

select  * from V$DB_CACHE_ADVICE;

select  * from V$SHARED_POOL_ADVICE;