2014-08-02-6.sql

botang

select  * from dict where table_name like '%UNDO%' or table_name like '%ROLL%';

select  * from v$undostat;

select  * from V$ROLLNAME;

select * from V$ROLLSTAT;

select  * from dba_rollback_segs;

create undo tablespace undotbs2 datafile size 256k autoextend off;

drop tablespace undotbs1;

select  * from dba_rollback_segs;

select * from dba_alert_history;

select  * from v$sqltext where sql_id='b4ypsk068tpkv';

select * from v$transaction;

select  * from v$session where taddr='00000001E455FB38';

alter system kill session '12,1176' immediate;

SQL> select  salary  from employees as of timestamp to_timestamp('2014-08-02:17:15:00','YYYY-MM-DD:HH24:MI:SS')  
  2    where employee_id=100;

    SALARY
----------
     24001

SQL>

create undo tablespace undotbs3 datafile size 100M autoextend on maxsize 10G;

alter system set undo_tablespace=undotbs3;

drop tablespace undotbs1;

select  * from dba_rollback_segs;

alter system flush buffer_cache;


create tablespace tbsaudit datafile size 20M  autoextend on ;

drop table  tlogon ;

create table tlogon  (   audit_time  varchar2(100),
                                      audit_ipaddress  varchar2(60),
                                      audit_user  varchar2(30))   tablespace tbsaudit ;



create or replace trigger   trg_logon
after logon on database
when (user ='HR' )
begin
        insert into    tlogon    values(to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS'),
                                             sys_context('userenv' , 'ip_address'),
                                               sys_context('userenv' , 'session_user')             );
        commit;
end;

select  o.status  from dba_objects o where o.owner='SYS' and o.object_name='TRG_LOGON';


select  * from tlogon;














create or replace trigger log_logon
after logon on database
when (user ='SYS' or user like 'OPS$%')
begin
        insert into t2 values('Logon '||user||' at '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
        commit;
end;