Recovery_Methods

Oracle_Server_Architecture

DBMS_SCHEDULER

create table t(c1 number);

• Creating the job including program and schedule:

BEGIN

  DBMS_SCHEDULER.CREATE_JOB(

   job_name => 'JOB_NAME',

   job_type => 'PLSQL_BLOCK',

   job_action => 'BEGIN insert into t values(10); END;',

   start_date => SYSTIMESTAMP,

   repeat_interval=>'FREQUENCY=minutely;INTERVAL=1',

   enabled => TRUE);

END;

/

• Create Program Separately:

BEGIN

  DBMS_SCHEDULER.CREATE_PROGRAM(

   program_name => 'PROG1',

   program_type => 'PLSQL_BLOCK',

   program_action => 'BEGIN insert into t values(10); END;',

   enabled=>true

);

end;

/

•  Creating the scheduler separately

BEGIN

  DBMS_SCHEDULER.CREATE_SCHEDULE('Sc1',

   start_date => SYSTIMESTAMP,

   repeat_interval => 'FREQ=DAILY',

   end_date => SYSTIMESTAMP +15); 

END;

/

• Creating a job with the program and schedule:

BEGIN

  DBMS_SCHEDULER.CREATE_JOB('Job2',

   program_name => 'PROG1',

   schedule_name => 'SC1',

   enabled => TRUE);

END;

/

EXEC DBMS_SCHEDULER.DROP_JOB('SCOTT.JOB_NAME');

EXEC DBMS_SCHEDULER.RUN_JOB('SCHEMA.JOB_NAME');

EXEC DBMS_SCHEDULER.stop_JOB('SCHEMA.JOB_NAME');

12c Architecture Diagram

View the tablespace usage

• View the tablespace usage

The query below is useful for showing how full each tablespace in a database. 
(This works much like df on a filesystem. This query is just selecting the tablespace usage from a database).

SQL>set pages 999

col tablespace_name format a40

col "size MB" format 999,999,999

col "free MB" format 99,999,999

col "% Used" format 999

select    tsu.tablespace_name, ceil(tsu.used_mb) "size MB"

,   decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"

,   decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,

               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"

from   (select tablespace_name, sum(bytes)/1024/1024 used_mb

   from    dba_data_files group by tablespace_name union all

   select    tablespace_name || '  **TEMP**'

   ,   sum(bytes)/1024/1024 used_mb

   from    dba_temp_files group by tablespace_name) tsu

,   (select tablespace_name, sum(bytes)/1024/1024 free_mb

   from    dba_free_space group by tablespace_name) tsf

where   tsu.tablespace_name = tsf.tablespace_name (+)

order   by 4

/

If You prefer the output in gigabytes and doesn't bother about the TEMP tablespace:

SQL>

set pages 999

col tablespace_name format a40

col "size GB" format 999,999.99

col "free GB" format 999,999.99

col "% Used" format 999

SELECT

  tsu.tablespace_name,

  tsu.used_gb "size GB",

  tsf.free_gb "free GB",

  DECODE(tsu.used_gb,0,100,100 - (tsf.free_gb/tsu.used_gb*100)) "% used"

FROM (

  SELECT

    tablespace_name,

    cast (sum(bytes)/power(1024,3) AS decimal(15,9)) used_gb

  FROM

    dba_data_files

  GROUP BY

    tablespace_name

  ) tsu,

  (

  SELECT

    tablespace_name,

    cast (sum(bytes)/power(1024,3) AS decimal(15,9)) free_gb

  FROM

    dba_free_space

  GROUP BY

    tablespace_name

  ) tsf

WHERE

  tsu.tablespace_name = tsf.tablespace_name (+)

ORDER BY 4

/