/*UTL FILE ukoly*/

create user utluser identified by utluser quota 100M on users
create role adbs;
grant resource,connect to adbs;

create directory INPUT as 'C:\app\utladdr' /*vytvri adresar*/

CREATE OR REPLACE PROCEDURE printutlfile(p_fName VARCHAR2) IS
 f utl_file.file_type;
 line VARCHAR2( 1024 );
BEGIN
  f := utl_file.fopen( 'INPUT' , p_fName , 'r' );
  LOOP
    UTL_FILE.GET_LINE( f , line );
    dbms_output.put_line ( 'create user ' || line || ' identified by changeme quota 100m on users' );
    execute immediate 'create user ' || line || ' identified by changeme quota 100m on users';
  END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN UTL_FILE .FCLOSE( f );
END;

set serveroutput on; /*presmerovani vystupu do okna*/

execute printutlfile('test.txt');

select * from dba_users where username = 'test1';

select * from dba_users;



/*FAST_START_MTTR_TARGET - udava dobu zotaveni - nastavuje dobu mezi kontrolními body*/
alter system set FAST_START_MTTR_TARGET = 120 scope = both;

select * from v$instance_recovery


select * from V$LOG

/*ARCHIVACE REDO LOG SOUBORU */
select log_mode from V$DATABASE

/* musi byt v mounted rezimu (shutdown immediate) + (startup mount)*/
ALTER DATABASE ARCHIVELOG;


/*flesh recovery */
alter system set db_recovery_file_dest_size = 2000m scope = both
alter system set db_recovery_file_dest = 'C:\app' scope = both



/*simulace blokovani*/

create user s1 identified by s1 quota 100M on users;
grant resource,connect to s1;
create user s2 identified by s2 quota 100M on users;
grant resource,connect to s2;


grant update,select on blocktable to s2;



select blocking session, sid, serial#, wait class,
seconds in wait from v$session where blocking session is not NULL;






create table stored_times (datetime date);

begin
dbms_scheduler.create_job (
job_name => 'timejob' ,
job_type => 'plsql_block' ,
job_action => 'insert into stored_times
values ( sysdate ) ' ,
start_date => sysdate ,
repeat_interval => 'freq=MINUTELY; interval=1' ,
enabled => true ,
auto_drop => false ) ;
end;

begin
dbms_scheduler.drop_job (job_name => 'timejob');
end;

select * from stored_times;