viernes, 14 de febrero de 2014

GENERAR CSV ENVIANDO SQL

CREATE OR REPLACE PACKAGE csv AS
-- --------------------------------------------------------------------------
-- Name         : http://www.oracle-base.com/dba/miscellaneous/cvs.sql
-- Author       : Tim Hall
-- Description : Basic CSV API. For usage notes see:
--                  http://www.oracle-base.com/articles/9i/GeneratingCSVFiles.php
--
--                  CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';
--                  ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
--
--                  EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');
--
-- Requirements : UTL_FILE, DBMS_SQL
-- Ammedments   :
--   When         Who       What
--   =========== ======== =================================================
--   14-MAY-2005 Tim Hall Initial Creation
-- --------------------------------------------------------------------------

PROCEDURE generate (p_dir     IN VARCHAR2,
                    p_file    IN VARCHAR2,
                    p_query   IN VARCHAR2);
END csv;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY csv AS
-- --------------------------------------------------------------------------
-- Name         : http://www.oracle-base.com/dba/miscellaneous/cvs.sql
-- Author       : Tim Hall
-- Description : Basic CSV API. For usage notes see:
--                  http://www.oracle-base.com/articles/9i/GeneratingCSVFiles.php
--
--                  CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';
--                  ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
--
--                  EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');
--
-- Requirements : UTL_FILE, DBMS_SQL
-- Ammedments   :
--   When         Who       What
--   =========== ======== =================================================
--   14-MAY-2005 Tim Hall Initial Creation
-- --------------------------------------------------------------------------

g_sep         VARCHAR2(5) := ',';

PROCEDURE generate (p_dir     IN VARCHAR2,
                    p_file    IN VARCHAR2,
                    p_query   IN VARCHAR2) AS
l_cursor    PLS_INTEGER;
l_rows      PLS_INTEGER;
l_col_cnt   PLS_INTEGER;
l_desc_tab DBMS_SQL.desc_tab;
l_buffer    VARCHAR2(32767);

l_file      UTL_FILE.file_type;
BEGIN
l_cursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);

DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);

FOR i IN 1 .. l_col_cnt LOOP
    DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );
END LOOP;

l_rows := DBMS_SQL.execute(l_cursor);

l_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);

-- Output the column names.
FOR i IN 1 .. l_col_cnt LOOP
    IF i > 1 THEN
      UTL_FILE.put(l_file, g_sep);
    END IF;
    UTL_FILE.put(l_file, l_desc_tab(i).col_name);
END LOOP;
UTL_FILE.new_line(l_file);

-- Output the data.
LOOP
    EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;

    FOR i IN 1 .. l_col_cnt LOOP
      IF i > 1 THEN
        UTL_FILE.put(l_file, g_sep);
      END IF;

      DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);
      UTL_FILE.put(l_file, l_buffer);
    END LOOP;
    UTL_FILE.new_line(l_file);
END LOOP;

UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    IF DBMS_SQL.is_open(l_cursor) THEN
      DBMS_SQL.close_cursor(l_cursor);
    END IF;
    RAISE;
END generate;

END csv;
/
SHOW ERRORS

viernes, 7 de febrero de 2014

Identifying High CPU Users

Listing . Identifying High CPU Users

SELECT n.username, s.sid, s.value FROM v$sesstat s, v$statname t, v$session n

WHERE s.statistic# = t.statistic#
AND n.sid = s.sid
AND t.name='CPU used by this session'
ORDER BY s.value desc;

AUDITORIA UNIFICADA DBMS_AUDIT_MGMT CONFIGURACION Y MANTENIMIENTO COMPLETO

Auditing Enhancements (DBMS_AUDIT_MGMT) in Oracle Database 11g Release 2

Oracle 11g Release 1 turned on auditng by default for the first time. Oracle 11g Release 2 now allows better management of the audit trail using the DBMS_AUDIT_MGMT package.

Note. This package has also been backported to previous versions down to 10g Release 2. See Oracle Support Note 731908.1.
Related articles.

Moving the Database Audit Trail to a Different Tablespace

The SET_AUDIT_TRAIL_LOCATION procedure allows you to alter the location of the standard and/or fine-grained database audit trail. It does not currently allow the alteration of the OS audit trail, although the documentation suggests this may happen in future. The procedure accepts two parameters.

AUDIT_TRAIL_TYPE: They type of audit trail that is to be moved.
AUDIT_TRAIL_LOCATION_VALUE: The tablespace the audit trail tables should be moved to.

The AUDIT_TRAIL_TYPE parameter is specified using one of three constants.

DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.

Let's see this in action. First check the current location of the audit trail tables.

CONN / AS SYSDBA

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           SYSTEM
FGA_LOG$                       SYSTEM

SQL>

Next, create a new tablespace to hold the audit trail.

CREATE TABLESPACE audit_aux
  DATAFILE '/u01/app/oracle/oradata/DB11G/audit_aux01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

Then we move the standard audit trail to the new tablespace.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'AUDIT_AUX');
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUDITORIA');
END;
/

PL/SQL procedure successfully completed.

SQL>

-- Check locations.
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           AUDIT_AUX
FGA_LOG$                       SYSTEM

SQL>

Next we move the fine-grained audit trail.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_AUX');
END;
/

PL/SQL procedure successfully completed.

SQL>

-- Check locations.
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           AUDIT_AUX
FGA_LOG$                       AUDIT_AUX

SQL>

Finally, we move them both back to their original location in a single step.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    audit_trail_location_value => 'SYSTEM');
END;
/

PL/SQL procedure successfully completed.

SQL>

-- Check locations.
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           SYSTEM
FGA_LOG$                       SYSTEM

SQL>

The AUDIT_AUX tablespace is no longer used so we can drop it.

DROP TABLESPACE audit_aux;

The time it takes to move the audit trail tables depends on the amount of data currently in the audit trail tables, and the resources available on your system.

Controlling the Size and Age of the OS Audit Trail

The SET_AUDIT_TRAIL_PROPERTY procedure allows you to set the maximum size and/or age of the OS audit trail files. The procedure can set parameters for several purposes, but I will restrict the discussion to only those relevant to this section. A full list of the constants available can be found here.
The procedure accepts three parameters.

AUDIT_TRAIL_TYPE: The type of audit trail to be modified (AUDIT_TRAIL_OS, AUDIT_TRAIL_XML or AUDIT_TRAIL_FILES).
AUDIT_TRAIL_PROPERTY: The name of the property to be set (OS_FILE_MAX_SIZE or OS_FILE_MAX_AGE).
AUDIT_TRAIL_PROPERTY_VALUE: The required value for the property.

To check the current settings query the DBA_AUDIT_MGMT_CONFIG_PARAMS view.

COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20

SELECT *
FROM   dba_audit_mgmt_config_params
WHERE  parameter_name LIKE 'AUDIT FILE MAX%';

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL

SQL>

These defaults mean that OS and XML audit trail files will grow to a maximum of 10,000Kb, at which point a new file will be created. In addition, files older than 5 days will not be written to any more, even if they are below the maximum file size. Instead, a new file will be created and written to. Here are some examples of changing the settings.

-- Set the Maximum size of OS audit files to 15,000Kb.
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
    audit_trail_property_value => 15000);
END;
/

SELECT *
FROM   dba_audit_mgmt_config_params
WHERE  parameter_name LIKE 'AUDIT FILE MAX%';

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            15000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL

SQL>


-- Set the Maximum age of XML audit files to 10 days.
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
    audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
    audit_trail_property_value => 10);
END;
/

SELECT *
FROM   dba_audit_mgmt_config_params
WHERE  parameter_name LIKE 'AUDIT FILE MAX%';

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            15000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             10                   XML AUDIT TRAIL

SQL>

The CLEAR_AUDIT_TRAIL_PROPERTY procedure can be used to remove the size and age restrictions, or reset them to the default values. Setting the USE_DEFAULT_VALUES parameter value to FALSE removes the restrictions, while setting it to TRUE returns the restriction to the default value.

-- Reset the max size default values for both OS and XML audit file.
BEGIN
  DBMS_AUDIT_MGMT.clear_audit_trail_property(
   audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
   audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
   use_default_values   => TRUE );
END;
/

SELECT *
FROM   dba_audit_mgmt_config_params
WHERE  parameter_name LIKE 'AUDIT FILE MAX%';

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             10                   XML AUDIT TRAIL

SQL>

-- Remove the max age restriction for both OS and XML audit file.
BEGIN
  DBMS_AUDIT_MGMT.clear_audit_trail_property(
   audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
   audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
   use_default_values   => FALSE );
END;
/

SELECT *
FROM   dba_audit_mgmt_config_params
WHERE  parameter_name LIKE 'AUDIT FILE MAX%';

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             NOT SET              OS AUDIT TRAIL
AUDIT FILE MAX AGE             NOT SET              XML AUDIT TRAIL

SQL>

-- Reset the max age default values for both OS and XML audit file.
BEGIN
  DBMS_AUDIT_MGMT.clear_audit_trail_property(
   audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
   audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
   use_default_values   => TRUE );
END;
/

SELECT *
FROM   dba_audit_mgmt_config_params
WHERE  parameter_name LIKE 'AUDIT FILE MAX%';

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL

SQL>

Purging Audit Trail Records

As with previous versions, you can manually delete records from the AUD$ and FGA_LOG$ tables and manually delete OS audit files from the file system, but DBMS_AUDIT_MGMT package gives you some new and safer mechanisms for maintaining the audit trail.
Note. If you are using Oracle Audit Vault, use that to manage your audit trail, not this functionality.

Initializing the Management Infrastructure

Before you can purge the database audit trail you must perform a one-time initialization of the audit management infrastructure. This is done using the INIT_CLEANUP procedure. The procedure accepts two parameters.

AUDIT_TRAIL_TYPE: The audit trail to be initialized (Constants).
DEFAULT_CLEANUP_INTERVAL: The default interval in hours, after which the cleanup procedure should be called again (1-999).

Note. The documentation seems to be incorrect about 2 points.

It claims that initializing the database audit trails move the AUD$ and FGA_LOG$ tables from the SYSTEM tablespace to the SYSAUX tablespace, unless they have already been moved out of the SYSTEM tablespace. This doesn't seem to be the case as the example below will show. Even though it doesn't happen automatically, it makes sense to move the audit tables into the SYSAUX tablespace or their own dedicated tablespace.
It claims it is not necessary to initialize the OS audit trails, yet in the example below you can clearly see the default cleanup intervals being set by the initialization process.

The following code checks the current parameter settings, initializes the audit management infrastructure for all audit trails with a default interval of 12 hours and rechecks the settings.

COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20

SELECT * FROM dba_audit_mgmt_config_params;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT TABLESPACE            SYSTEM               STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            SYSTEM               FGA AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL

SQL>

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 12 /* hours */);
END;
/

PL/SQL procedure successfully completed.

SQL>

SELECT * FROM dba_audit_mgmt_config_params;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT TABLESPACE            SYSTEM               STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            SYSTEM               FGA AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      12                   OS AUDIT TRAIL

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DEFAULT CLEAN UP INTERVAL      12                   STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      12                   FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      12                   XML AUDIT TRAIL

Notice that the 'DB AUDIT TABLESPACE' for the database audit trails are unchanged and the 'DEFAULT CLEAN UP INTERVAL' for all four audit trails has been set.
The current initialization status of a specific audit trail can be checked using the IS_CLEANUP_INITIALIZED.

SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/
YES

PL/SQL procedure successfully completed.

SQL>

To deconfigure the audit management infrastructure run the DEINIT_CLEANUP procedure.

BEGIN
  DBMS_AUDIT_MGMT.deinit_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
END;
/

Timestamp Management

The next thing to consider before purging the audit trail is how much data you wish to purge. The DBMS_AUDIT_MGMT package allows us to purge all the records, or all the records older than a specific timestamp. The timestamp in question is specified individually for each audit trail using the SET_LAST_ARCHIVE_TIMESTAMP procedure, which accepts three parameters.

AUDIT_TRAIL_TYPE: The audit trail whose timestamp is to be set (Constants). Only individual audit trails are valid, not the constants that specify multiples.
LAST_ARCHIVE_TIME: Records or files older than this time will be deleted.
RAC_INSTANCE_NUMBER: Optionally specify the RAC node for OS audit trails. If unset it assumes the current instance.

The following code specifies a timestamp of 5 days ago for the standard database audit trail. The setting is then checked by querying the DBA_AUDIT_MGMT_LAST_ARCH_TS view.

BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-5);
END;
/

COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 13-DEC-09 01.57.54.000000 PM +00:00

SQL>

The timestamps for each audit trail can be cleared to allow a complete purge using the CLEAR_LAST_ARCHIVE_TIMESTAMP procedure.

BEGIN
  DBMS_AUDIT_MGMT.clear_last_archive_timestamp(
    audit_trail_type     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
/

Manual Purge

The CLEAN_AUDIT_TRAIL procedure is the basic mechanism for manually purging the audit trail. It accepts two parameters.

AUDIT_TRAIL_TYPE: The audit trail to be purged (Constants).
USE_LAST_ARCH_TIMESTAMP: Set to FALSE to purge all records/files, or TRUE to only purge records/files older than the timestamp specified for the audit trail.

The following code queries the last archive timestamp and total number of audit records, deletes standard database audit records older than the last archive timestamp, then returns the number of records again.

SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 13-DEC-09 01.57.54.000000 PM +00:00

SQL>

SELECT COUNT(*) FROM aud$;

  COUNT(*)
----------
      2438

SQL> 

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/

PL/SQL procedure successfully completed.

SELECT COUNT(*) FROM aud$;

  COUNT(*)
----------
        76

SQL>

Automated Purging

The CREATE_PURGE_JOB procedure allows you to schedule a job to call the CLEAN_AUDIT_TRAIL procedure. When creating a purge job you can specify 4 parameters.

AUDIT_TRAIL_TYPE: The audit trail to be purged by the scheduled job (Constants).
AUDIT_TRAIL_PURGE_INTERVAL: The interval in hours between purges.
AUDIT_TRAIL_PURGE_NAME: A name for the purge job.
USE_LAST_ARCH_TIMESTAMP: Set to FALSE to purge all records/files, or TRUE to only purge records/files older than the timestamp specified for the audit trail.

The following code schedules a purge of all audit trails every 24 hours. The resulting job is visible in the DBA_SCHEDULER_JOBS view.

BEGIN
  DBMS_AUDIT_MGMT.create_purge_job(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24 /* hours */,  
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp    => TRUE);
END;
/

PL/SQL procedure successfully completed.

SQL>

SELECT job_action
FROM   dba_scheduler_jobs
WHERE  job_name = 'PURGE_ALL_AUDIT_TRAILS';

JOB_ACTION
--------------------------------------------------------------------------------
BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE);  END;

SQL>

The job can be disabled and enabled using the SET_PURGE_JOB_STATUS procedure.

BEGIN
  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'PURGE_ALL_AUDIT_TRAILS',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE);

  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'PURGE_ALL_AUDIT_TRAILS',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/

The interval of the purge job can be altered using the SET_PURGE_JOB_INTERVAL procedure.

BEGIN
  DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    audit_trail_interval_value => 48);
END;
/

Purge jobs are removed using the DROP_PURGE_JOB procedure.

BEGIN
  DBMS_AUDIT_MGMT.drop_purge_job(
     audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS');
END;
/

There are two things to note about the automated functionality.

If purge jobs use the last archived timestamp and you do not manually move this timestamp forward, the job will run and have nothing to purge. You should reset the timestamp using DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP when you have made an archive of the audit information, that way your audit information is secure and the job can purge the excess data.
The purge job functionality is simply a wrapper over the DBMS_SCHEDULER package to make automating purge jobs easier.

If you want the purge job to maintain an audit trail of a specific number of days, the easiest way to accomplish this is to define a job to set the last archive time automatically. The following job resets the last archive time on a daily basis, keeping the last archive time 90 days in the past.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'audit_last_archive_time',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN 
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TRUNC(SYSTIMESTAMP)-90);
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Automatically set audit last archive time.');
END;
/

jueves, 6 de febrero de 2014

Capacidad Almacenamiento y Conversiones

En Noviembre de 2000 apareció un nuevo standard.

Name	Symbol	Before the standardization				After the standardization
bit	b	1	bit	=	1 bit	1	bit	=	1 bit
byte	B	1	B	=	8 bit	1	B	=	8 bit
kilobit	kbit / kb	1	kbit	=	1024 bit	1	kBit	=	1000 bit
Kibibit	KiBit					1	KiBit	=	1024 bit
kilobyte	kB	1	kB	=	1024 B = Byte	1	kB	=	1000 Byte
kibibyte	KiB					1	KiB	=	1024 Byte
megabit	MBit / Mb	1	MBit	=	1024 KBit	1	MBit	=	1000 kBit
mebibit	MiBit / Mib					1	Mib	=	1024 KiBit
megabyte	MB	1	MB	=	1024 kB	1	MB	=	1000 kB
Mebibyte	MiBit / MiB					1	MiB	=	1024 KiB
gigabit	GBit / Gb	1	GBit	=	1024 MBit	1	GBit	=	1000 MBit
gibibit	GiBit / Gib					1	Gib	=	1024 MiBit
gigabyte	GB	1	GB	=	1024 MB	1	GB	=	1000 MB
gibibyte	GiB					1	GiB	=	1024 MiB
terabyte	TB	1	TB	=	1024 GB	1	TB	=	1000 GB
tebibyte	TiB					1	TiB	=	1024 GiB
petabyte	PB	1	PB	=	1024 TB	1	PB	=	1000 TB
pebibyte	PiB					1	PiB	=	1024 TiB
exabyte	EB	1	EB	=	1024 PB	1	EB	=	1000 PB
exbibyte	EiB					1	EiB	=	1024 PiB
zettabyte	ZB	1	ZB	=	1024 EB	1	ZB	=	1000 EB
zebibyte	ZiB					1	ZiB	=	1024 EiB
yottabyte	YB	1	YB	=	1024 ZB	1	YB	=	1000 ZB
yobibyte	YiB					1	YiB	=	1024 ZiB

El nuevo Standard IEC
bit	bit	0 or 1
byte	B	8 bits
kibibit	Kibit	1024 bits
kilobit	kbit	1000 bits
kibibyte (binary)	KiB	1024 bytes
kilobyte (decimal)	kB	1000 bytes
megabit	Mbit	1000 kilobits
mebibyte (binary)	MiB	1024 kibibytes
megabyte (decimal)	MB	1000 kilobytes
gigabit	Gbit	1000 megabits
gibibyte (binary)	GiB	1024 mebibytes
gigabyte (decimal)	GB	1000 megabytes
terabit	Tbit	1000 gigabits
tebibyte (binary)	TiB	1024 gibibytes
terabyte (decimal)	TB	1000 gigabytes
petabit	Pbit	1000 terabits
pebibyte (binary)	PiB	1024 tebibytes
petabyte (decimal)	PB	1000 terabytes
exabit	Ebit	1000 petabits
exbibyte (binary)	EiB	1024 pebibytes
exabyte (decimal)	EB	1000 petabytes

El blog de Nelson Javier Martinez Acosta

MERCADOS FINANCIEROS

martes, 25 de febrero de 2014

Query Sumar con Alias

viernes, 14 de febrero de 2014

GENERAR CSV ENVIANDO SQL

viernes, 7 de febrero de 2014

Identifying High CPU Users

AUDITORIA UNIFICADA DBMS_AUDIT_MGMT CONFIGURACION Y MANTENIMIENTO COMPLETO

Auditing Enhancements (DBMS_AUDIT_MGMT) in Oracle Database 11g Release 2

Moving the Database Audit Trail to a Different Tablespace

Controlling the Size and Age of the OS Audit Trail

Purging Audit Trail Records

Initializing the Management Infrastructure

Timestamp Management

Manual Purge

Automated Purging

jueves, 6 de febrero de 2014

Capacidad Almacenamiento y Conversiones

Vistas de página en total

Buscar este blog

Seguidores

Datos personales

Archivo del blog

INSTRUCTOR WDP

Entradas populares

Oracle Seguridad

Etiquetas

Archivo del blog

OCP 11G

OCA11G

SQL

WEBLOGIC

OCA 10G

OCP 10G

Formulario de contacto