NuxOra – Linux and Oracle IT website

Un petite question posée par mail il y a peu:

Pour lister les jobs

SQL> connect system/xxx
SQL> select job, schema_user, next_date, what from dba_jobs;

JOB    SCHEMA_USER    NEXT_DATE     WHAT
------ -------------- ---------     --------------------
1      SYSMAN         19-OCT-08     EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
100    CRM            19-OCT-08     CRM_PROCS();
101    STAT           19-OCT-08     STAT_JOB_PROCS();

Pour supprimer un job

SQL> exec dbms_job.remove(215);
PL/SQL procedure successfully completed.


J’ai recu une demande pour savoir comment générer un rapport AWR sous Oracle.

Voici un petit exemple pour se connecter à la base et générer un AWR en mode test

[root@nuxora01 ~]# su - oracle

[oracle@nuxora01 ~]$ export ORACLE_SID=nuxodb

[oracle@nuxora01 ~]$ sqlplus system@nuxodb

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 31 08:40:22 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning and Data Mining options

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

  731622690 NUXODB              1 nuxodb

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: text

Type Specified:  text

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 731622690         1 NUXODB       nuxodb       nuxora01

Using  731622690 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

nuxodb       NUXODB            1510 29 Aug 2008 06:26      1

                               1511 29 Aug 2008 06:45      1

                               1512 29 Aug 2008 07:00      1

                               1513 29 Aug 2008 07:15      1

                               1514 29 Aug 2008 07:30      1

                               1515 29 Aug 2008 07:45      1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1514

Begin Snapshot Id specified: 1514

Enter value for end_snap: 1515

End   Snapshot Id specified: 1515

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_1514_1515.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_1514_1515.txt

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     RAC Host

------------ ----------- ------------ -------- ----------- --- ------------

NUXODB         731622690 nuxodb              1 10.2.0.1.0  NO  nuxora01

              Snap Id      Snap Time      Sessions Curs/Sess

            --------- ------------------- -------- ---------

Begin Snap:      1514 29-Aug-08 07:30:23        18       2.1

  End Snap:      1515 29-Aug-08 07:45:24        18       2.1

   Elapsed:               15.01 (mins)

   DB Time:                0.01 (mins)

Cache Sizes

~~~~~~~~~~~                       Begin        End

                             ---------- ----------

               Buffer Cache:       180M       180M  Std Block Size:         8K

           Shared Pool Size:        76M        76M      Log Buffer:     7,000K

Load Profile

~~~~~~~~~~~~                            Per Second       Per Transaction

                                   ---------------       ---------------

                  Redo size:                694.24              7,269.72

              Logical reads:                 27.06                283.31

              Block changes:                  2.05                 21.42

             Physical reads:                  0.07                  0.78

            Physical writes:                  0.48                  5.06

                 User calls:                  0.10                  1.05

                     Parses:                  1.88                 19.67

                Hard parses:                  0.35                  3.64

                      Sorts:                  1.50                 15.74

                     Logons:                  0.03                  0.35

                   Executes:                  4.53                 47.49

               Transactions:                  0.10

  % Blocks changed per Read:    7.56    Recursive Call %:    99.79

 Rollback per transaction %:    0.00       Rows per Sort:     7.74

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:  100.00       Redo NoWait %:  100.00

            Buffer  Hit   %:   99.73    In-memory Sort %:  100.00

            Library Hit   %:   83.14        Soft Parse %:   81.50

         Execute to Parse %:   58.57         Latch Hit %:  100.00

Parse CPU to Parse Elapsd %:   83.33     % Non-Parse CPU:   35.22

 Shared Pool Statistics        Begin    End

                              ------  ------

             Memory Usage %:   82.16   82.43

    % SQL with executions>1:   63.84   78.55

  % Memory for SQL w/exec>1:   84.04   90.26

Top 5 Timed Events                                         Avg %Total

~~~~~~~~~~~~~~~~~~                                        wait   Call

Event                                 Waits    Time (s)   (ms)   Time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

CPU time                                              1          92.7

os thread startup                        16           1     42   90.3 Concurrenc

control file parallel write             299           1      2   90.1 System I/O

log file parallel write                 111           0      2   27.2 System I/O

db file sequential read                  17           0      3    7.5   User I/O

          -------------------------------------------------------------

......

          -------------------------------------------------------------

End of Report

Report written to awrrpt_1_1514_1515.txt

SQL> quit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning and Data Mining options

[oracle@nuxora01 ~]$ ll
total 100
-rw-r--r--  1 oracle dba 95022 Aug 31 08:40 awrrpt_1_1514_1515.txt

[oracle@nuxora01 ~]$

If you’d like to log the result from a sql script, just prepare your sql script like that:spool result.log
SET SERVEROUTPUT ON
select * from dual;
spool off
exit

Then from a command line or a script shell execute the following command:
# sqlplus user/pwd@db_name @script.sql