'1. IT Story'에 해당되는 글 397건

1. create_program 

BEGIN

  -- Shell Script.

  DBMS_SCHEDULER.create_program (

    program_name        => 'hotbackup_executable_prog',

    program_type        => 'EXECUTABLE',

    program_action      => '/backup/hotbackup/test.sql',

    number_of_arguments => 0,

    enabled             => TRUE,

    comments            => 'Program to gather SCOTT''s statistics us a shell script.');

        END;

/

2. create_schedule 

BEGIN

  DBMS_SCHEDULER.create_schedule (

    schedule_name   => 'test_min_schedule',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'freq=MINUTELY; INTERVAL=30',

    end_date        => NULL,

    comments        => 'Repeats hourly, on the hour, for ever.');

END;

/

3. create_job 

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name      => 'test_prog_sched_job_definition',

    program_name  => 'hotbackup_executable_prog',

    schedule_name => 'test_min_schedule',

    enabled       => TRUE,

    comments      => 'Job defined by an existing program and schedule.');

            END;

/

SELECT    'alter tablespace '

         || t.tablespace_name

         || ' begin backup;'

         || CHR (10)

         || '!cp -p '

         || d.file_name

         || ' /oradata_backup/HOT/ '

         || CHR (10)

         || 'alter tablespace '

         || t.tablespace_name

         || ' end backup;'

         || CHR (10)

    FROM dba_tablespaces t, dba_data_files d

   WHERE t.TABLESPACE_NAME = d.TABLESPACE_NAME

ORDER BY t.tablespace_name;

SELECT TABLESPACE_NAME,

       TOTAL_BYTES,

       USED_BYTES,

       FREE_BYTES,

       USED_RATE

FROM   (SELECT X.TABLESPACE_NAME,

               X.TOTAL_BYTES,

               X.USED_BYTES,

               X.FREE_BYTES,

               X.USED_RATE

        FROM   (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,

                       SUBSTR(To_Char(Round(A.TOTAL_BYTES/1024000), '999,999,999'),2,11)||' MB ('||

                       SUBSTR(To_Char(Round(A.TOTAL_BYTES/1024000000, 2), '990.99'),2,6) ||' GB)' TOTAL_BYTES,

                       SUBSTR(To_Char(Round((A.TOTAL_BYTES-B.FREE_BYTES)/1024000), '999,999,999'),2,11)||' MB ('||

                       SUBSTR(To_Char(Round((A.TOTAL_BYTES-B.FREE_BYTES)/1024000000,2), '990.99'),2,6) ||' GB)' USED_BYTES,

                       SUBSTR(To_Char(Round(B.FREE_BYTES/1024000), '999,999,999'),2,11)||' MB ('||

                       SUBSTR(To_Char(Round(B.FREE_BYTES/1024000000, 2), '990.99'),2,6) ||' GB)' FREE_BYTES,

                       SUBSTR(To_Char(Round((A.TOTAL_BYTES-B.FREE_BYTES)/A.TOTAL_BYTES*100,2), '90.99'),2,5)||'%' USED_RATE

                FROM   (SELECT TABLESPACE_NAME, Sum(BYTES) TOTAL_BYTES

                        FROM   DBA_DATA_FILES

                        WHERE  TABLESPACE_NAME LIKE UPPER(DECODE('%','','%', '%'))

                        GROUP  BY TABLESPACE_NAME

                       )    A,

                       (SELECT TABLESPACE_NAME, Sum(BYTES) FREE_BYTES

                        FROM   USER_FREE_SPACE

                        WHERE  TABLESPACE_NAME LIKE UPPER(DECODE('%','','%', '%'))

                        GROUP  BY TABLESPACE_NAME

                       )    B

                WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME

               )    X

        ORDER  BY DECODE('&TS_ORDER_BASE', '1', X.TABLESPACE_NAME, '', X.TABLESPACE_NAME, '2', X.USED_RATE) DESC

       )

       UNION ALL

       SELECT '------------------------------ ' TABLESPACE_NAME,

              '-------------------------- ' TOTAL_BYTES,

              '-------------------------- ' USED_BYTES,

              '-------------------------- ' FREE_BYTES,

              '----------' USED_RATE

       FROM   DUAL

       UNION ALL

       SELECT '전체' TABLESPACE_NAME,

              SUBSTR(To_Char(Round(A.TOTAL_BYTES/1024000), '999,999,999'),2,11)||' MB ('||

              SUBSTR(To_Char(Round(A.TOTAL_BYTES/1024000000, 2), '990.99'),2,6) ||' GB)' TOTAL_BYTES,

              SUBSTR(To_Char(Round((A.TOTAL_BYTES-B.FREE_BYTES)/1024000), '999,999,999'),2,11)||' MB ('||

              SUBSTR(To_Char(Round((A.TOTAL_BYTES-B.FREE_BYTES)/1024000000,2), '990.99'),2,6) ||' GB)' USED_BYTES,

              SUBSTR(To_Char(Round(B.FREE_BYTES/1024000), '999,999,999'),2,11)||' MB ('||

              SUBSTR(To_Char(Round(B.FREE_BYTES/1024000000, 2), '990.99'),2,6) ||' GB)' FREE_BYTES,

              SUBSTR(To_Char(Round((A.TOTAL_BYTES-B.FREE_BYTES)/A.TOTAL_BYTES*100,2), '90.99'),2,5)||'%' USED_RATE

       FROM   (SELECT Sum(BYTES) TOTAL_BYTES

               FROM   DBA_DATA_FILES

               WHERE  TABLESPACE_NAME LIKE UPPER(DECODE('%','','%', '%'))

              )     A,

              (SELECT Sum(BYTES) FREE_BYTES

               FROM   USER_FREE_SPACE

               WHERE  TABLESPACE_NAME LIKE UPPER(DECODE('%','','%', '%'))

              )     B

;

Select ts.tablespace_name, ts.status, ts.contents, ts.extent_management, ts.bigfile,

       size_info.megs_alloc, size_info.megs_free, size_info.megs_used,

       size_info.pct_free, size_info.pct_used, size_info.max

From

      (

      select  a.tablespace_name,

             round(a.bytes_alloc / 1024 / 1024) megs_alloc,

             round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,

             round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,

             round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,

            100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,

             round(maxbytes/1048576) Max

      from  ( select  f.tablespace_name,

                     sum(f.bytes) bytes_alloc,

                     sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes

              from dba_data_files f

              group by tablespace_name) a,

            ( select  f.tablespace_name,

                     sum(f.bytes)  bytes_free

              from dba_free_space f

              group by tablespace_name) b

      where a.tablespace_name = b.tablespace_name (+)

      union all

      select h.tablespace_name,

             round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,

             round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,

             round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,

             round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,

             100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,

             round(sum(f.maxbytes) / 1048576) max

      from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f

      where  p.file_id(+) = h.file_id

      and    p.tablespace_name(+) = h.tablespace_name

      and    f.file_id = h.file_id

      and    f.tablespace_name = h.tablespace_name

      group by h.tablespace_name

      ) size_info,

      sys.dba_tablespaces ts

where ts.tablespace_name = size_info.tablespace_name

;

set verify off

set linesize 120

set pagesize 60

column sort_id noprint

column priv_type format a35

column priv format a60

column grantable heading "ADM" format a3

column default_role heading "DEF" format a3

spool showAllPrivs.txt

 select

    1 as sort_id,

    'ROLE' as priv_type,

    a.granted_role as priv,

    a.admin_option as grantable,

    a.default_role as default_role

  from

    DBA_ROLE_PRIVS a

  where

    grantee = '&&enter_username'

union

 select

    2 as sort_id,

    'SYS PRIV' as priv_type,

    b.privilege as priv,

    b.admin_option as grantable,

    null as default_role

  from

    DBA_SYS_PRIVS b

  where

    grantee = '&&enter_username'

union

 select

    5 as sort_id,

    'TAB PRIV (ROLE "' || c.granted_role || '")' as priv_type,

    d.privilege || ' on "' || d.owner ||

       '"."' || d.table_name || '"'

       as priv,

    d.grantable as grantable,

    c.default_role as default_role

  from

    DBA_ROLE_PRIVS c,

    DBA_TAB_PRIVS d

  where

    c.grantee = '&&enter_username'

    and d.grantee = c.granted_role

union

 select

    7 as sort_id,

    'COL PRIV (ROLE "' || e.granted_role || '")' as priv_type,

    f.privilege || ' on "' || f.owner ||

       '"."' || f.table_name || '" ("' || f.column_name || '"

;)'

       as priv,

    f.grantable as grantable,

    e.default_role as default_role

  from

    DBA_ROLE_PRIVS e,

    DBA_COL_PRIVS f

  where

    e.grantee = '&&enter_username'

    and f.grantee = e.granted_role

union

 select

    4 as sort_id,

    'TAB PRIV' as priv_type,

    g.privilege || ' on "' || g.owner ||

       '"."' || g.table_name || '"'

       as priv,

    g.grantable as grantable,

    null as default_role

  from

    DBA_TAB_PRIVS g

  where

    g.grantee = '&&enter_username'

union

 select

    6 as sort_id,

    'COL PRIV' as priv_type,

    h.privilege || ' on "' || h.owner ||

       '"."' || h.table_name || '" ("' || h.column_name || '"

;)'

       as priv,

    h.grantable as grantable,

    null as default_role

  from

    DBA_COL_PRIVS h

  where

    h.grantee = '&&enter_username'

union

 select

    3 as sort_id,

    'SYS PRIV (ROLE "' || i.granted_role || '")' as priv_type,

    j.privilege as priv,

    j.admin_option as grantable,

    i.default_role as default_role

  from

    DBA_ROLE_PRIVS i,

    DBA_SYS_PRIVS j

  where

    i.grantee = '&&enter_username'

    and j.grantee = i.granted_role

order by 1, 2, 3 ;

undefine enter_username

clear columns

spool off

set linesize 80

set verify on