{"id":255,"date":"2013-05-22T15:56:23","date_gmt":"2013-05-22T18:56:23","guid":{"rendered":"http:\/\/dev.dbarj.com.br\/?p=255"},"modified":"2014-10-09T23:39:26","modified_gmt":"2014-10-10T02:39:26","slug":"changing-audit-tablespace-create-purge-job-11g","status":"publish","type":"post","link":"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/","title":{"rendered":"Changing AUDIT tablespace and create a purge job in 11g"},"content":{"rendered":"<p>Oracle Database 11g comes with some audit options turned on by default, which over time can exhaust the <strong>SYSTEM<\/strong> tablespace without the DBA knowledge. However, this same version of database now offers some DBMS packages that can greatly facilitate the life of the administrator who wants to migrate audit to another tablespace or change the retention period of the data. Until version 10gR2, it was necessary to make this change in a more archaic way, which can be checked at <span style=\"color: #0000ff;\"><strong>Doc ID 1019377.6<\/strong><\/span> on Oracle Support.<\/p>\n<p>To begin, make sure your audit options are turned on.<\/p>\n<p>In my case, I activated for all sys operations and configured to be saved also the run SQL (DB_EXTENDED). This can vary from case to case. Place the desired values \u200b\u200band restart your database.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; SHOW parameter audit\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\naudit_file_dest                      string      \/u02\/admin\/rmanbkp\/adump\r\naudit_syslog_level                   string\r\naudit_sys_operations                 boolean     TRUE\r\naudit_trail                          string      DB_EXTENDED<\/pre>\n<p>Let&#8217;s first find in which tablespace are the audit tables.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; SELECT OWNER,\r\n  2         SEGMENT_NAME,\r\n  3         TABLESPACE_NAME\r\n  4  FROM   DBA_SEGMENTS\r\n  5  WHERE  SEGMENT_NAME IN ('AUD$', 'FGA_LOG$');\r\n\r\nOWNER                          SEGMENT_NAME                                                                     TABLESPACE_NAME\r\n------------------------------ -------------------------------------------------------------------------------- ------------------------------\r\nSYS                            FGA_LOG$                                                                         SYSTEM\r\nSYS                            AUD$                                                                             SYSTEM<\/pre>\n<p>Now let&#8217;s check if the automatic purge infrastructure process of the audit data has already been initialized.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; SET SERVEROUTPUT ON\r\nSQL&gt; BEGIN\r\n  2    IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN\r\n  3      DBMS_OUTPUT.put_line('YES');\r\n  4    ELSE\r\n  5      DBMS_OUTPUT.put_line('NO');\r\n  6    END IF;\r\n  7  END;\r\n  8  \/\r\n\r\nNO\r\n\r\nSQL&gt; SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;\r\n\r\nPARAMETER_NAME                                                                   PARAMETER_VALUE                                                                  AUDIT_TRAIL\r\n-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------\r\nDB AUDIT TABLESPACE                                                              SYSAUX                                                                           STANDARD AUDIT TRAIL\r\nDB AUDIT TABLESPACE                                                              SYSAUX                                                                           FGA AUDIT TRAIL\r\nAUDIT FILE MAX SIZE                                                              10000                                                                            OS AUDIT TRAIL\r\nAUDIT FILE MAX SIZE                                                              10000                                                                            XML AUDIT TRAIL\r\nAUDIT FILE MAX AGE                                                               5                                                                                OS AUDIT TRAIL\r\nAUDIT FILE MAX AGE                                                               5                                                                                XML AUDIT TRAIL\r\nDB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            STANDARD AUDIT TRAIL\r\nDB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            FGA AUDIT TRAIL\r\nOS FILE CLEAN BATCH SIZE                                                         1000                                                                             OS AUDIT TRAIL\r\nOS FILE CLEAN BATCH SIZE                                                         1000                                                                             XML AUDIT TRAIL\r\n\r\n10 rows selected<\/pre>\n<p>If it is already enabled, skip to the next step. Otherwise, we will then initialize the infrastructure and set the cleaning interval (not the retention interval) for every 1 day (24 hours).<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; BEGIN\r\n  2    SYS.dbms_audit_mgmt.init_cleanup(\r\n  3      audit_trail_type         =&gt; SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,\r\n  4      default_cleanup_interval =&gt; 24 \/* hours *\/); -- PARAMETER NOT USED ON 11GR2 (FUTURE USE)\r\n  5  END;\r\n  6  \/\r\n\r\nPL\/SQL procedure successfully completed<\/pre>\n<p>Rechecking.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; SET SERVEROUTPUT ON\r\nSQL&gt; BEGIN\r\n  2    IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN\r\n  3      DBMS_OUTPUT.put_line('YES');\r\n  4    ELSE\r\n  5      DBMS_OUTPUT.put_line('NO');\r\n  6    END IF;\r\n  7  END;\r\n  8  \/\r\n\r\nYES\r\n\r\nSQL&gt; SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;\r\n\r\nPARAMETER_NAME                                                                   PARAMETER_VALUE                                                                  AUDIT_TRAIL\r\n-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------\r\nDB AUDIT TABLESPACE                                                              SYSAUX                                                                           STANDARD AUDIT TRAIL\r\nDB AUDIT TABLESPACE                                                              SYSAUX                                                                           FGA AUDIT TRAIL\r\nAUDIT FILE MAX SIZE                                                              10000                                                                            OS AUDIT TRAIL\r\nAUDIT FILE MAX SIZE                                                              10000                                                                            XML AUDIT TRAIL\r\nAUDIT FILE MAX AGE                                                               5                                                                                OS AUDIT TRAIL\r\nAUDIT FILE MAX AGE                                                               5                                                                                XML AUDIT TRAIL\r\nDB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            STANDARD AUDIT TRAIL\r\nDB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            FGA AUDIT TRAIL\r\nOS FILE CLEAN BATCH SIZE                                                         1000                                                                             OS AUDIT TRAIL\r\nOS FILE CLEAN BATCH SIZE                                                         1000                                                                             XML AUDIT TRAIL\r\nDEFAULT CLEAN UP INTERVAL                                                        24                                                                               STANDARD AUDIT TRAIL\r\nDEFAULT CLEAN UP INTERVAL                                                        24                                                                               FGA AUDIT TRAIL\r\n\r\n12 rows selected<\/pre>\n<p>Note the appearance of the last 2 rows with the value &#8220;<strong>DEFAULT CLEAN UP INTERVAL<\/strong>&#8221; set to 24 hours.<br \/>\nThe next step is the creation of a unique tablespace to store audit data:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; CREATE TABLESPACE AUDTBS DATAFILE '\/u02\/oradata\/ORACL\/audtbs01.dbf' SIZE 128M\r\n  2  AUTOEXTEND ON NEXT 64M MAXSIZE 2G\r\n  3  NOLOGGING default NOCOMPRESS ONLINE PERMANENT BLOCKSIZE 8K\r\n  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M SEGMENT SPACE MANAGEMENT AUTO;\r\n\r\nTablespace created<\/pre>\n<p>Now we will move the tables <span style=\"color: #0000ff;\"><strong>AUD$<\/strong><\/span> and <span style=\"color: #0000ff;\"><strong>FGA_LOG$<\/strong><\/span> to the new tablespace created. It is recommended that these tables are empty to optimize the process. Truncate if possible or leave your data and wait.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; BEGIN\r\n  2   SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(\r\n  3      audit_trail_type =&gt; SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,\r\n  4      audit_trail_location_value =&gt; 'AUDTBS');\r\n  5  END;\r\n  6  \/\r\n\r\nPL\/SQL procedure successfully completed\r\n\r\nSQL&gt;\r\nSQL&gt; BEGIN\r\n  2    SYS.DBMS_AUDIT_MGMT.set_audit_trail_location(\r\n  3      audit_trail_type           =&gt; SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,\r\n  4      audit_trail_location_value =&gt; 'AUDTBS');\r\n  5  END;\r\n  6  \/\r\n\r\nPL\/SQL procedure successfully completed<\/pre>\n<p>Time to see if the tables were really moved. Check if the values \u200b\u200bof the parameter &#8220;<strong>DB AUDIT TABLESPACE<\/strong>&#8221; in the first query has been changed.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;\r\n\r\nPARAMETER_NAME                                                                   PARAMETER_VALUE                                                                  AUDIT_TRAIL\r\n-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------\r\nDB AUDIT TABLESPACE                                                              AUDTBS                                                                           STANDARD AUDIT TRAIL\r\nDB AUDIT TABLESPACE                                                              AUDTBS                                                                           FGA AUDIT TRAIL\r\nAUDIT FILE MAX SIZE                                                              10000                                                                            OS AUDIT TRAIL\r\nAUDIT FILE MAX SIZE                                                              10000                                                                            XML AUDIT TRAIL\r\nAUDIT FILE MAX AGE                                                               5                                                                                OS AUDIT TRAIL\r\nAUDIT FILE MAX AGE                                                               5                                                                                XML AUDIT TRAIL\r\nDB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            STANDARD AUDIT TRAIL\r\nDB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            FGA AUDIT TRAIL\r\nOS FILE CLEAN BATCH SIZE                                                         1000                                                                             OS AUDIT TRAIL\r\nOS FILE CLEAN BATCH SIZE                                                         1000                                                                             XML AUDIT TRAIL\r\nDEFAULT CLEAN UP INTERVAL                                                        24                                                                               STANDARD AUDIT TRAIL\r\nDEFAULT CLEAN UP INTERVAL                                                        24                                                                               FGA AUDIT TRAIL\r\n\r\n12 rows selected\r\n\r\nSQL&gt;\r\nSQL&gt; SELECT OWNER,\r\n  2         SEGMENT_NAME,\r\n  3         TABLESPACE_NAME\r\n  4  FROM   DBA_SEGMENTS\r\n  5  WHERE  SEGMENT_NAME IN ('AUD$', 'FGA_LOG$');\r\n\r\nOWNER                          SEGMENT_NAME                                                                     TABLESPACE_NAME\r\n------------------------------ -------------------------------------------------------------------------------- ------------------------------\r\nSYS                            FGA_LOG$                                                                         AUDTBS\r\nSYS                            AUD$                                                                             AUDTBS<\/pre>\n<p>Now let&#8217;s create a new Scheduler that will daily mark all audit records prior to <strong>90<\/strong> days as filed. Thus, the cleaning process can clean them.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; BEGIN\r\n  2    DBMS_SCHEDULER.CREATE_JOB (\r\n  3      job_name   =&gt; 'SYS.DAILY_AUDIT_ARCHIVE_TIMESTAMP',\r\n  4      job_type   =&gt; 'PLSQL_BLOCK',\r\n  5      job_action =&gt; 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =&gt; DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME =&gt; SYSDATE-90); END;',\r\n  6      start_date =&gt; sysdate,\r\n  7      repeat_interval =&gt; 'FREQ=HOURLY;INTERVAL=24',\r\n  8      enabled    =&gt;  TRUE,\r\n  9      comments   =&gt; 'Create an archive timestamp'\r\n 10    );\r\n 11  END;\r\n 12  \/\r\n\r\nPL\/SQL procedure successfully completed<\/pre>\n<p>Checking if the job was created.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%';\r\n\r\nOWNER                          JOB_NAME                       JOB_STYLE   JOB_CREATOR                    JOB_TYPE         JOB_ACTION                                                                       START_DATE                                                                       REPEAT_INTERVAL                                                                  ENABLED STATE           SYSTEM NLS_ENV                                                                          COMMENTS\r\n------------------------------ ------------------------------ ----------- ------------------------------ ---------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- --------------- ------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------\r\nSYS                            DAILY_AUDIT_ARCHIVE_TIMESTAMP  REGULAR     SYS                            PLSQL_BLOCK      BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =&gt; DBMS_AUDIT_ 22\/05\/13 13:29:13,000000 -03:00                                                  FREQ=HOURLY;INTERVAL=24                                                          TRUE    SCHEDULED       TRUE   NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Create an archive timestamp<\/pre>\n<p>Finally, we will create the process to daily clean the records that were marked by the previous job.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; BEGIN\r\n  2    SYS.DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(\r\n  3      AUDIT_TRAIL_TYPE           =&gt; SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,\r\n  4      AUDIT_TRAIL_PURGE_INTERVAL =&gt; 24 \/* hours *\/,\r\n  5      AUDIT_TRAIL_PURGE_NAME     =&gt; 'Daily_Audit_Purge_Job',\r\n  6      USE_LAST_ARCH_TIMESTAMP    =&gt; TRUE\r\n  7    );\r\n  8  END;\r\n  9  \/\r\n\r\nPL\/SQL procedure successfully completed<\/pre>\n<p>Checking if the job was created.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;\r\n\r\nJOB_NAME                                                                         JOB_STATUS AUDIT_TRAIL                  JOB_FREQUENCY\r\n-------------------------------------------------------------------------------- ---------- ---------------------------- --------------------------------------------------------------------------------\r\nDAILY_AUDIT_PURGE_JOB                                                            ENABLED    STANDARD AUDIT TRAIL         FREQ=HOURLY;INTERVAL=24\r\n\r\nSQL&gt; SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%';\r\n\r\nOWNER                          JOB_NAME                       JOB_STYLE   JOB_CREATOR                    JOB_TYPE         JOB_ACTION                                                                       START_DATE                                                                       REPEAT_INTERVAL                                                                  ENABLED STATE           SYSTEM NLS_ENV                                                                          COMMENTS\r\n------------------------------ ------------------------------ ----------- ------------------------------ ---------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- --------------- ------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------\r\nSYS                            DAILY_AUDIT_ARCHIVE_TIMESTAMP  REGULAR     SYS                            PLSQL_BLOCK      BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =&gt; DBMS_AUDIT_ 22\/05\/13 13:29:13,000000 -03:00                                                  FREQ=HOURLY;INTERVAL=24                                                          TRUE    SCHEDULED       TRUE   NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Create an archive timestamp\r\nSYS                            DAILY_AUDIT_PURGE_JOB          REGULAR     SYS                            PLSQL_BLOCK      BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(1, TRUE);  END;                          22\/05\/13 13:30:13,003284 AMERICA\/RECIFE                                          FREQ=HOURLY;INTERVAL=24                                                          TRUE    SCHEDULED       TRUE   NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Audit clean job = 'Daily_Audit_Purge_Job'<\/pre>\n<p>Now, I recommend removing all of the default audit options of Oracle and reapplying them only for executions &#8220;<strong>Whenever Successful<\/strong>&#8220;, not commands that execution failed for lack of privileges.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; noaudit all;\r\nSQL&gt; noaudit all privileges;\r\nSQL&gt; noaudit exempt access policy;\r\n\r\nNoaudit succeeded\r\nNoaudit succeeded\r\nNoaudit succeeded\r\n\r\nsql&gt; audit all by access whenever successful; -- Add 27 AUDIT options\r\nsql&gt; audit alter any table by access whenever successful;\r\nsql&gt; audit create any table by access whenever successful;\r\nsql&gt; audit drop any table by access whenever successful;\r\nsql&gt; audit create any procedure by access whenever successful;\r\nsql&gt; audit drop any procedure by access whenever successful;\r\nsql&gt; audit alter any procedure by access whenever successful;\r\nsql&gt; audit grant any privilege by access whenever successful;\r\nsql&gt; audit grant any object privilege by access whenever successful;\r\nsql&gt; audit grant any role by access whenever successful;\r\nsql&gt; audit audit system by access whenever successful;\r\nsql&gt; audit create external job by access whenever successful;\r\nsql&gt; audit create any job by access whenever successful;\r\nsql&gt; audit create any library by access whenever successful;\r\nsql&gt; audit create public database link by access whenever successful;\r\nsql&gt; audit exempt access policy by access whenever successful;\r\nsql&gt; audit alter user by access whenever successful;\r\nsql&gt; audit create user by access whenever successful;\r\nsql&gt; audit role by access whenever successful;\r\nsql&gt; audit create session by access whenever successful;\r\nsql&gt; audit drop user by access whenever successful;\r\nsql&gt; audit alter database by access whenever successful;\r\nsql&gt; audit alter system by access whenever successful;\r\nsql&gt; audit alter profile by access whenever successful;\r\nsql&gt; audit drop profile by access whenever successful;\r\n\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded\r\nAudit succeeded<\/pre>\n<p>Verifying..<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">SQL&gt; SELECT  * FROM DBA_STMT_AUDIT_OPTS order by 3;\r\n\r\nUSER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    FAILURE\r\n------------------------------ ------------------------------ ---------------------------------------- ---------- ----------\r\n                                                              ALTER ANY PROCEDURE                      BY ACCESS  NOT SET\r\n                                                              ALTER ANY TABLE                          BY ACCESS  NOT SET\r\n                                                              ALTER DATABASE                           BY ACCESS  NOT SET\r\n                                                              ALTER PROFILE                            BY ACCESS  NOT SET\r\n                                                              ALTER SYSTEM                             BY ACCESS  NOT SET\r\n                                                              ALTER USER                               BY ACCESS  NOT SET\r\n                                                              CLUSTER                                  BY ACCESS  NOT SET\r\n                                                              CONTEXT                                  BY ACCESS  NOT SET\r\n                                                              CREATE ANY JOB                           BY ACCESS  NOT SET\r\n                                                              CREATE ANY LIBRARY                       BY ACCESS  NOT SET\r\n                                                              CREATE ANY PROCEDURE                     BY ACCESS  NOT SET\r\n                                                              CREATE ANY TABLE                         BY ACCESS  NOT SET\r\n                                                              CREATE EXTERNAL JOB                      BY ACCESS  NOT SET\r\n                                                              CREATE PUBLIC DATABASE LINK              BY ACCESS  NOT SET\r\n                                                              CREATE SESSION                           BY ACCESS  NOT SET\r\n                                                              CREATE USER                              BY ACCESS  NOT SET\r\n                                                              DATABASE LINK                            BY ACCESS  NOT SET\r\n                                                              DIMENSION                                BY ACCESS  NOT SET\r\n                                                              DIRECTORY                                BY ACCESS  NOT SET\r\n                                                              DROP ANY PROCEDURE                       BY ACCESS  NOT SET\r\n                                                              DROP ANY TABLE                           BY ACCESS  NOT SET\r\n                                                              DROP PROFILE                             BY ACCESS  NOT SET\r\n                                                              DROP USER                                BY ACCESS  NOT SET\r\n                                                              EXEMPT ACCESS POLICY                     BY ACCESS  NOT SET\r\n                                                              GRANT ANY OBJECT PRIVILEGE               BY ACCESS  NOT SET\r\n                                                              GRANT ANY PRIVILEGE                      BY ACCESS  NOT SET\r\n                                                              GRANT ANY ROLE                           BY ACCESS  NOT SET\r\n                                                              INDEX                                    BY ACCESS  NOT SET\r\n                                                              MATERIALIZED VIEW                        BY ACCESS  NOT SET\r\n                                                              MINING MODEL                             BY ACCESS  NOT SET\r\n                                                              NOT EXISTS                               BY ACCESS  NOT SET\r\n                                                              PROCEDURE                                BY ACCESS  NOT SET\r\n                                                              PROFILE                                  BY ACCESS  NOT SET\r\n                                                              PUBLIC DATABASE LINK                     BY ACCESS  NOT SET\r\n                                                              PUBLIC SYNONYM                           BY ACCESS  NOT SET\r\n                                                              ROLE                                     BY ACCESS  NOT SET\r\n                                                              ROLLBACK SEGMENT                         BY ACCESS  NOT SET\r\n                                                              SEQUENCE                                 BY ACCESS  NOT SET\r\n                                                              SYNONYM                                  BY ACCESS  NOT SET\r\n                                                              SYSTEM AUDIT                             BY ACCESS  NOT SET\r\n                                                              SYSTEM GRANT                             BY ACCESS  NOT SET\r\n                                                              TABLE                                    BY ACCESS  NOT SET\r\n                                                              TABLESPACE                               BY ACCESS  NOT SET\r\n                                                              TRIGGER                                  BY ACCESS  NOT SET\r\n                                                              TYPE                                     BY ACCESS  NOT SET\r\n                                                              USER                                     BY ACCESS  NOT SET\r\n                                                              VIEW                                     BY ACCESS  NOT SET\r\n\r\n47 rows selected\r\n\r\nSQL&gt;<\/pre>\n<p>Done, now you no longer need to worry about the uncontrolled growth of your <strong>SYSTEM<\/strong> tablespace. The audit is isolated in a dedicated tablespace.<\/p>\n<b>Have you enjoyed? Please leave a comment or give a ?!<\/b>\n<div class='watch-action'><div class='watch-position align-left'><div class='action-like'><a class='lbg-style2 like-255 jlk' href='javascript:void(0)' data-task='like' data-post_id='255' data-nonce='b7aaf4ff99' rel='nofollow'><img class='wti-pixel' src='https:\/\/dev.dbarj.com.br\/wp-content\/plugins\/wti-like-post\/images\/pixel.gif' title='Like' \/><span class='lc-255 lc'>+10<\/span><\/a><\/div><\/div> <div class='status-255 status align-left'><\/div><\/div><div class='wti-clear'><\/div>","protected":false},"excerpt":{"rendered":"<p>Oracle Database 11g comes with some audit options turned on by default, which over time can exhaust the SYSTEM tablespace without the DBA knowledge. However, this same version of database now offers some DBMS packages that can greatly facilitate the life of the administrator who wants to migrate audit to another tablespace or change the &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/\">Continue reading<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20,15],"tags":[],"class_list":["post-255","post","type-post","status-publish","format-standard","hentry","category-security-en","category-database-en","item-wrap"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.7 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Changing AUDIT tablespace and create a purge job in 11g - DBA - Rodrigo Jorge - Oracle Tips and Guides<\/title>\n<meta name=\"description\" content=\"Step by step of how to move AUDIT table to othe tablespace and setup a purge job.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DBA RJ\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2013\\\/05\\\/changing-audit-tablespace-create-purge-job-11g\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2013\\\/05\\\/changing-audit-tablespace-create-purge-job-11g\\\/\"},\"author\":{\"name\":\"DBA RJ\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"headline\":\"Changing AUDIT tablespace and create a purge job in 11g\",\"datePublished\":\"2013-05-22T18:56:23+00:00\",\"dateModified\":\"2014-10-10T02:39:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2013\\\/05\\\/changing-audit-tablespace-create-purge-job-11g\\\/\"},\"wordCount\":407,\"commentCount\":6,\"publisher\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"articleSection\":[\"Database Security\",\"Oracle Database General\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2013\\\/05\\\/changing-audit-tablespace-create-purge-job-11g\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2013\\\/05\\\/changing-audit-tablespace-create-purge-job-11g\\\/\",\"url\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2013\\\/05\\\/changing-audit-tablespace-create-purge-job-11g\\\/\",\"name\":\"Changing AUDIT tablespace and create a purge job in 11g - DBA - Rodrigo Jorge - Oracle Tips and Guides\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#website\"},\"datePublished\":\"2013-05-22T18:56:23+00:00\",\"dateModified\":\"2014-10-10T02:39:26+00:00\",\"description\":\"Step by step of how to move AUDIT table to othe tablespace and setup a purge job.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2013\\\/05\\\/changing-audit-tablespace-create-purge-job-11g\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2013\\\/05\\\/changing-audit-tablespace-create-purge-job-11g\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2013\\\/05\\\/changing-audit-tablespace-create-purge-job-11g\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Changing AUDIT tablespace and create a purge job in 11g\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/\",\"name\":\"DBA - Rodrigo Jorge - Oracle Tips and Guides\",\"description\":\"Blog about Databases, Security and High Availability\",\"publisher\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\",\"name\":\"DBA RJ\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"url\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"contentUrl\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"width\":712,\"height\":712,\"caption\":\"DBA RJ\"},\"logo\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Changing AUDIT tablespace and create a purge job in 11g - DBA - Rodrigo Jorge - Oracle Tips and Guides","description":"Step by step of how to move AUDIT table to othe tablespace and setup a purge job.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/","twitter_misc":{"Written by":"DBA RJ","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/#article","isPartOf":{"@id":"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/"},"author":{"name":"DBA RJ","@id":"https:\/\/dev.dbarj.com.br\/en\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"headline":"Changing AUDIT tablespace and create a purge job in 11g","datePublished":"2013-05-22T18:56:23+00:00","dateModified":"2014-10-10T02:39:26+00:00","mainEntityOfPage":{"@id":"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/"},"wordCount":407,"commentCount":6,"publisher":{"@id":"https:\/\/dev.dbarj.com.br\/en\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"articleSection":["Database Security","Oracle Database General"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/","url":"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/","name":"Changing AUDIT tablespace and create a purge job in 11g - DBA - Rodrigo Jorge - Oracle Tips and Guides","isPartOf":{"@id":"https:\/\/dev.dbarj.com.br\/en\/#website"},"datePublished":"2013-05-22T18:56:23+00:00","dateModified":"2014-10-10T02:39:26+00:00","description":"Step by step of how to move AUDIT table to othe tablespace and setup a purge job.","breadcrumb":{"@id":"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dev.dbarj.com.br\/en\/2013\/05\/changing-audit-tablespace-create-purge-job-11g\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dev.dbarj.com.br\/en\/"},{"@type":"ListItem","position":2,"name":"Changing AUDIT tablespace and create a purge job in 11g"}]},{"@type":"WebSite","@id":"https:\/\/dev.dbarj.com.br\/en\/#website","url":"https:\/\/dev.dbarj.com.br\/en\/","name":"DBA - Rodrigo Jorge - Oracle Tips and Guides","description":"Blog about Databases, Security and High Availability","publisher":{"@id":"https:\/\/dev.dbarj.com.br\/en\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dev.dbarj.com.br\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/dev.dbarj.com.br\/en\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9","name":"DBA RJ","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","url":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","contentUrl":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","width":712,"height":712,"caption":"DBA RJ"},"logo":{"@id":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png"}}]}},"_links":{"self":[{"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/posts\/255","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/comments?post=255"}],"version-history":[{"count":0,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/posts\/255\/revisions"}],"wp:attachment":[{"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/media?parent=255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/categories?post=255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/tags?post=255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}