{"id":248,"date":"2013-05-22T15:25:49","date_gmt":"2013-05-22T18:25:49","guid":{"rendered":"http:\/\/www.dbarj.com.br\/?p=248"},"modified":"2014-10-09T23:44:30","modified_gmt":"2014-10-10T02:44:30","slug":"alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g","status":"publish","type":"post","link":"https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/","title":{"rendered":"Alterando tablespace de AUDIT e criando uma pol\u00edtica de expurgo no 11g"},"content":{"rendered":"<p>O Oracle Database 11g v\u00eam com algumas op\u00e7\u00f5es de audit ligadas por default, o que pode ao longo do tempo esgotar a tablespace <strong>SYSTEM<\/strong> sem que o DBA perceba o porque. No entanto, esta mesma vers\u00e3o disponibiliza agora algumas packages DBMS que podem facilitar muito a vida do administrador que deseja migrar para outra tablespace ou alterar o per\u00edodo de reten\u00e7\u00e3o dos dados. At\u00e9 a vers\u00e3o 10gR2, era preciso fazer essa altera\u00e7\u00e3o de maneira mais arcaica, que pode ser verificado no <span style=\"color: #0000ff;\"><strong><em>Doc ID 1019377.6<\/em><\/strong><\/span> no suporte da Oracle.<\/p>\n<p>Para come\u00e7ar, verifique se as suas op\u00e7\u00f5es de auditoria est\u00e3o ligadas.<\/p>\n<p>No meu caso, eu ativei para todas as opera\u00e7\u00f5es do sistema e configurei para que fosse guardado tamb\u00e9m o SQL executado (DB_EXTENDED). Isso pode variar de caso a caso. Coloque os valores desejados e reinicie seu banco.<\/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>Vamos primeiro localizar em qual tablespace est\u00e3o as tabelas de auditoria.<\/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>Agora vamos verificar se a infraestrutura do processo autom\u00e1tico de expurgo do audit j\u00e1 foi inicializado.<\/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>Caso j\u00e1 tenha sido ativado, pule para a pr\u00f3xima etapa. Caso contr\u00e1rio, vamos ent\u00e3o iniciar a infraestrutura do expurgo do audit com o intervalo de execu\u00e7\u00e3o da limpeza (n\u00e3o reten\u00e7\u00e3o) para cada 1 dia (24 horas).<\/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 *\/); -- PARAMETRO NAO USADO NA 11GR2 (PARA USO FUTURO)\r\n  5  END;\r\n  6  \/\r\n\r\nPL\/SQL procedure successfully completed<\/pre>\n<p>Verificando novamente.<\/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 o aparecimento das 2 \u00faltimas linhas com o valor &#8220;<strong>DEFAULT CLEAN UP INTERVAL<\/strong>&#8221; de 24 horas.<br \/>\nO pr\u00f3ximo passo \u00e9 a cria\u00e7\u00e3o de uma tablespace exclusiva para guardar os dados de auditoria:<\/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>Agora vamos mover as tabelas <strong><span style=\"color: #0000ff;\">AUD$<\/span><\/strong> e <span style=\"color: #0000ff;\"><strong>FGA_LOG$<\/strong><\/span> para a nova tablespace criada. \u00c9 recomendado que estas tabelas estejam vazias para otimizar o processo. Fa\u00e7a um truncate se poss\u00edvel ou ent\u00e3o deixe os dados e aguarde.<\/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>Hora de verificar se as tabelas foram realmente movidas. Verifique se os valores do par\u00e2metro &#8220;<strong>DB AUDIT TABLESPACE<\/strong>&#8221; na primeira query foram alterados.<\/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>Agora vamos criar um novo Scheduler Job que ir\u00e1 marcar diariamente todos os registros de auditoria anteriores a <strong>90<\/strong> dias como arquivados. Desta forma, o processo de limpeza poder\u00e1 limp\u00e1-los.<\/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>Fazendo a verifica\u00e7\u00e3o se o job foi criado.<\/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>Por fim, vamos criar o processo para limpar diariamente os registros que foram marcados pelo job anterior.<\/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>Fazendo a verifica\u00e7\u00e3o se o job foi criado.<\/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>Para finalizar, recomendo remover todas as op\u00e7\u00f5es default da auditoria do Oracle e relig\u00e1-las apenas para execu\u00e7\u00f5es &#8220;<strong>Whenever Successful<\/strong>&#8220;, n\u00e3o catalogando comandos executados que falharam por falta de privil\u00e9gios.<\/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; -- Adiciona 27 op\u00e7\u00f5es de AUDIT\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>Verificando..<\/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>Pronto, agora voc\u00ea n\u00e3o precisa mais se preocupar com o crescimento descontralado da sua tablespace <strong>SYSTEM<\/strong>. A auditoria est\u00e1 isolada em uma tablespace dedicada.<\/p>\n<b>Gostou? N\u00e3o deixe de comentar ou deixar um \ud83d\udc4d!<\/b>\n<div class='watch-action'><div class='watch-position align-left'><div class='action-like'><a class='lbg-style2 like-248 jlk' href='javascript:void(0)' data-task='like' data-post_id='248' data-nonce='de4404f630' rel='nofollow'><img class='wti-pixel' src='https:\/\/www.dbarj.com.br\/wp-content\/plugins\/wti-like-post\/images\/pixel.gif' title='Like' \/><span class='lc-248 lc'>+14<\/span><\/a><\/div><\/div> <div class='status-248 status align-left'><\/div><\/div><div class='wti-clear'><\/div>","protected":false},"excerpt":{"rendered":"<p>O Oracle Database 11g v\u00eam com algumas op\u00e7\u00f5es de audit ligadas por default, o que pode ao longo do tempo esgotar a tablespace SYSTEM sem que o DBA perceba o porque. No entanto, esta mesma vers\u00e3o disponibiliza agora algumas packages DBMS que podem facilitar muito a vida do administrador que deseja migrar para outra tablespace &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/\">Continue lendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,2],"tags":[],"class_list":["post-248","post","type-post","status-publish","format-standard","hentry","category-security","category-database","item-wrap"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Alterando tablespace de AUDIT e criando uma pol\u00edtica de expurgo no 11g - DBA - Rodrigo Jorge - Oracle Tips and Guides<\/title>\n<meta name=\"description\" content=\"Como mover a tabela de AUDIT no Oracle 11g para outra tablespace.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/\" \/>\n<meta name=\"twitter:label1\" content=\"Escrito por\" \/>\n\t<meta name=\"twitter:data1\" content=\"DBA RJ\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. tempo de leitura\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutos\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2013\\\/05\\\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2013\\\/05\\\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\\\/\"},\"author\":{\"name\":\"DBA RJ\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"headline\":\"Alterando tablespace de AUDIT e criando uma pol\u00edtica de expurgo no 11g\",\"datePublished\":\"2013-05-22T18:25:49+00:00\",\"dateModified\":\"2014-10-10T02:44:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2013\\\/05\\\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\\\/\"},\"wordCount\":456,\"commentCount\":14,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"articleSection\":[\"Database Security\",\"Oracle Database General\"],\"inLanguage\":\"pt-BR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2013\\\/05\\\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2013\\\/05\\\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\\\/\",\"url\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2013\\\/05\\\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\\\/\",\"name\":\"Alterando tablespace de AUDIT e criando uma pol\u00edtica de expurgo no 11g - DBA - Rodrigo Jorge - Oracle Tips and Guides\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#website\"},\"datePublished\":\"2013-05-22T18:25:49+00:00\",\"dateModified\":\"2014-10-10T02:44:30+00:00\",\"description\":\"Como mover a tabela de AUDIT no Oracle 11g para outra tablespace.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2013\\\/05\\\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\\\/#breadcrumb\"},\"inLanguage\":\"pt-BR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2013\\\/05\\\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/2013\\\/05\\\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Alterando tablespace de AUDIT e criando uma pol\u00edtica de expurgo no 11g\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#website\",\"url\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/\",\"name\":\"DBA - Rodrigo Jorge - Oracle Tips and Guides\",\"description\":\"Blog about Databases, Security and High Availability\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"pt-BR\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/pt-br\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\",\"name\":\"DBA RJ\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-BR\",\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"url\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"contentUrl\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"width\":712,\"height\":712,\"caption\":\"DBA RJ\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Alterando tablespace de AUDIT e criando uma pol\u00edtica de expurgo no 11g - DBA - Rodrigo Jorge - Oracle Tips and Guides","description":"Como mover a tabela de AUDIT no Oracle 11g para outra tablespace.","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:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/","twitter_misc":{"Escrito por":"DBA RJ","Est. tempo de leitura":"11 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/#article","isPartOf":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/"},"author":{"name":"DBA RJ","@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"headline":"Alterando tablespace de AUDIT e criando uma pol\u00edtica de expurgo no 11g","datePublished":"2013-05-22T18:25:49+00:00","dateModified":"2014-10-10T02:44:30+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/"},"wordCount":456,"commentCount":14,"publisher":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"articleSection":["Database Security","Oracle Database General"],"inLanguage":"pt-BR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/","url":"https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/","name":"Alterando tablespace de AUDIT e criando uma pol\u00edtica de expurgo no 11g - DBA - Rodrigo Jorge - Oracle Tips and Guides","isPartOf":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#website"},"datePublished":"2013-05-22T18:25:49+00:00","dateModified":"2014-10-10T02:44:30+00:00","description":"Como mover a tabela de AUDIT no Oracle 11g para outra tablespace.","breadcrumb":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/#breadcrumb"},"inLanguage":"pt-BR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbarj.com.br\/pt-br\/2013\/05\/alterando-tablespace-de-audit-e-criando-uma-politica-de-expurgo-no-oracle-11g\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dbarj.com.br\/pt-br\/"},{"@type":"ListItem","position":2,"name":"Alterando tablespace de AUDIT e criando uma pol\u00edtica de expurgo no 11g"}]},{"@type":"WebSite","@id":"https:\/\/www.dbarj.com.br\/pt-br\/#website","url":"https:\/\/www.dbarj.com.br\/pt-br\/","name":"DBA - Rodrigo Jorge - Oracle Tips and Guides","description":"Blog about Databases, Security and High Availability","publisher":{"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbarj.com.br\/pt-br\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"pt-BR"},{"@type":["Person","Organization"],"@id":"https:\/\/www.dbarj.com.br\/pt-br\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9","name":"DBA RJ","image":{"@type":"ImageObject","inLanguage":"pt-BR","@id":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","url":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","contentUrl":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","width":712,"height":712,"caption":"DBA RJ"},"logo":{"@id":"https:\/\/www.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png"}}]}},"_links":{"self":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/posts\/248","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/comments?post=248"}],"version-history":[{"count":0,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/posts\/248\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/media?parent=248"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/categories?post=248"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbarj.com.br\/pt-br\/wp-json\/wp\/v2\/tags?post=248"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}