Solution:-
Assuming you have your audit table, indexes on a non-system tablespace and have already deleted the rows but the disk space is not released. The following is a guide to reorganize your audit table , indexes and release the disk space back to the system.
Table, indexes DDL extracted from TOAD Tool
DROP TABLE SYS.AUD$ CASCADE CONSTRAINTS;
CREATE TABLE SYS.AUD$
(
SESSIONID NUMBER NOT NULL,
ENTRYID NUMBER NOT NULL,
STATEMENT NUMBER NOT NULL,
TIMESTAMP# DATE,
USERID VARCHAR2(30 BYTE),
USERHOST VARCHAR2(128 BYTE),
TERMINAL VARCHAR2(255 BYTE),
ACTION# NUMBER NOT NULL,
RETURNCODE NUMBER NOT NULL,
OBJ$CREATOR VARCHAR2(30 BYTE),
OBJ$NAME VARCHAR2(128 BYTE),
AUTH$PRIVILEGES VARCHAR2(16 BYTE),
AUTH$GRANTEE VARCHAR2(30 BYTE),
NEW$OWNER VARCHAR2(30 BYTE),
NEW$NAME VARCHAR2(128 BYTE),
SES$ACTIONS VARCHAR2(19 BYTE),
SES$TID NUMBER,
LOGOFF$LREAD NUMBER,
LOGOFF$PREAD NUMBER,
LOGOFF$LWRITE NUMBER,
LOGOFF$DEAD NUMBER,
LOGOFF$TIME DATE,
COMMENT$TEXT VARCHAR2(4000 BYTE),
CLIENTID VARCHAR2(64 BYTE),
SPARE1 VARCHAR2(255 BYTE),
SPARE2 NUMBER,
OBJ$LABEL RAW(255),
SES$LABEL RAW(255),
PRIV$USED NUMBER,
SESSIONCPU NUMBER,
NTIMESTAMP# TIMESTAMP(6),
PROXY$SID NUMBER,
USER$GUID VARCHAR2(32 BYTE),
INSTANCE# NUMBER,
PROCESS# VARCHAR2(16 BYTE),
XID RAW(8),
AUDITID VARCHAR2(64 BYTE),
SCN NUMBER,
DBID NUMBER,
SQLBIND CLOB,
SQLTEXT CLOB
)
LOB (SQLBIND) STORE AS (
TABLESPACE HD_AUDIT
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
INDEX (
TABLESPACE HD_AUDIT
STORAGE (
INITIAL 104K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
STORAGE (
INITIAL 104K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
LOB (SQLTEXT) STORE AS (
TABLESPACE HD_AUDIT
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
INDEX (
TABLESPACE HD_AUDIT
STORAGE (
INITIAL 104K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
STORAGE (
INITIAL 104K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
TABLESPACE HD_AUDIT
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 56K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX SYS.I_AUD2 ON SYS.AUD$
(SESSIONID, SES$TID)
LOGGING
TABLESPACE HD_AUDIT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 56K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
GRANT SELECT ON SYS.AUD$ TO HD_MAINT;
DROP TABLE HD_MAINT.HD_AUD_HIST CASCADE CONSTRAINTS;
CREATE TABLE HD_MAINT.HD_AUD_HIST
(
SESSIONID NUMBER NOT NULL,
ENTRYID NUMBER NOT NULL,
STATEMENT NUMBER NOT NULL,
TIMESTAMP# DATE,
USERID VARCHAR2(30 BYTE),
USERHOST VARCHAR2(128 BYTE),
TERMINAL VARCHAR2(255 BYTE),
ACTION# NUMBER NOT NULL,
RETURNCODE NUMBER NOT NULL,
OBJ$CREATOR VARCHAR2(30 BYTE),
OBJ$NAME VARCHAR2(128 BYTE),
AUTH$PRIVILEGES VARCHAR2(16 BYTE),
AUTH$GRANTEE VARCHAR2(30 BYTE),
NEW$OWNER VARCHAR2(30 BYTE),
NEW$NAME VARCHAR2(128 BYTE),
SES$ACTIONS VARCHAR2(19 BYTE),
SES$TID NUMBER,
LOGOFF$LREAD NUMBER,
LOGOFF$PREAD NUMBER,
LOGOFF$LWRITE NUMBER,
LOGOFF$DEAD NUMBER,
LOGOFF$TIME DATE,
COMMENT$TEXT VARCHAR2(4000 BYTE),
CLIENTID VARCHAR2(64 BYTE),
SPARE1 VARCHAR2(255 BYTE),
SPARE2 NUMBER,
OBJ$LABEL RAW(255),
SES$LABEL RAW(255),
PRIV$USED NUMBER,
SESSIONCPU NUMBER,
NTIMESTAMP# TIMESTAMP(6),
PROXY$SID NUMBER,
USER$GUID VARCHAR2(32 BYTE),
INSTANCE# NUMBER,
PROCESS# VARCHAR2(16 BYTE),
XID RAW(8),
AUDITID VARCHAR2(64 BYTE),
SCN NUMBER,
DBID NUMBER,
SQLBIND CLOB,
SQLTEXT CLOB
)
LOB (SQLBIND) STORE AS (
TABLESPACE HD_AUDIT_ARC
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
INDEX (
TABLESPACE HD_AUDIT_ARC
STORAGE (
INITIAL 104K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
STORAGE (
INITIAL 104K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
LOB (SQLTEXT) STORE AS (
TABLESPACE HD_AUDIT_ARC
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
INDEX (
TABLESPACE HD_AUDIT_ARC
STORAGE (
INITIAL 104K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
STORAGE (
INITIAL 104K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
TABLESPACE HD_AUDIT_ARC
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 104K
NEXT 104K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
The Objects are found in two Tablespaces
SET PAGES 1000 LINES 150
COLUMN SEGMENT_NAME FORMAT A30
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);
Time Start: 09/09/2011 7:13:03 PM
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS TABLESPACE_NAME
—————————— —————————— —————— ———- ———- ——————————
SYS AUD$ TABLE 1693073408 15898 HD_AUDIT
HD_MAINT HD_AUD_HIST TABLE 858251264 8059 HD_AUDIT_ARC
SYS SYS_IL0000055838C00040$$ LOBINDEX 106496 1 HD_AUDIT
SYS SYS_IL0000055838C00041$$ LOBINDEX 106496 1 HD_AUDIT
SYS I_AUD2 INDEX 948240384 8904 HD_AUDIT
HD_MAINT SYS_IL0000093888C00040$$ LOBINDEX 106496 1 HD_AUDIT_ARC
HD_MAINT SYS_IL0000093888C00041$$ LOBINDEX 106496 1 HD_AUDIT_ARC
SYS SYS_LOB0000055838C00040$$ LOBSEGMENT 106496 1 HD_AUDIT
SYS SYS_LOB0000055838C00041$$ LOBSEGMENT 106496 1 HD_AUDIT
HD_MAINT SYS_LOB0000093888C00040$$ LOBSEGMENT 106496 1 HD_AUDIT_ARC
HD_MAINT SYS_LOB0000093888C00041$$ LOBSEGMENT 106496 1 HD_AUDIT_ARC
11 rows selected.
Time End: 09/09/2011 7:13:04 PM
Elapsed Time for Script Execution: 766 msecs
Shutdown the primary and standby database
Login to primary database server hd_primary
Sqlas
Shutdown immediate
Login to hd_standby
Sqlas
Shutdown immediate
House Keep AUD$ Table
1.Set the database in restricted mode and make sure that no auditable users
are connected. If necessary kill these sessions.
Login as oracle in hd_primary
connect / as sysdba
startup restrict
alter system enable restricted session;
Verify the column LOGINS in V$INSTANCE.
LOGINS = RESTRICTED indicates that the database is in restricted mode.
Select logins from v$instance;
Check if sessions are still connected :
select sid, serial#, username from v$session;
If necessary kill these sessions with:
alter system kill session ‘sid , serial#’;
make a backup of current aud$, hd_aud_hist tables in the same tablespace
create table sys.aud$_backup tablespace hd_audit as select * from sys.aud$;
create table hd_main.hd_aud_hist_backup tablespace hd_audit_arc as select * from hd_maint.hd_aud_hist;
2. Move SYS.AUD$ to a different tablespace
select count(*) from SYS.AUD$;
create tablespace HD_AUD datafile ‘/u02/oradata/zoeoa1/hd_aud_01.dbf’ size 500m autoextend on;
alter table sys.aud$ move tablespace hd_aud;
Alter table sys.aud$ move lob (SQLBIND) STORE as (tablespace hd_aud);
Alter table sys.aud$ move lob (SQLTEXT) STORE as (tablespace hd_aud);
3
Rebuild the index / move to new tablespace
Alter index sys.i_aud2 rebuild tablespace hd_aud;
All extents are now optimally filled again.
————–
House Keep HD_AUD_HIST Table
Select count(*) from HD_MAINT.HD_AUD_HIST;
create tablespace HD_AUD_ARC datafile ‘/u02/oradata/zoeoa1/hd_aud_arc01.dbf’ size 500m autoextend on;
alter user hd_maint quota unlimited on HD_AUD_ARC;
alter table hd_maint.hd_aud_hist move tablespace hd_aud_arc;
Alter table hd_maint.hd_aud_hist move lob (SQLBIND) STORE as (tablespace hd_aud_arc);
Alter table hd_maint.hd_aud_hist move lob (SQLTEXT) STORE as (tablespace hd_aud_arc);
All extents are now optimally filled again.
Ensure the tablespace is empty except the backedup objects by running the script below before dropping the objects
SET PAGES 1000 LINES 150
COLUMN SEGMENT_NAME FORMAT A30
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);
Now drop the backedup objects
Drop table sys.aud$_backup purge;
Drop table hd_maint.hd_aud_hist_backup purge;
And finally drop the old tablespaces hd_audit and hd_audit_arc
Drop tablespace hd_audit including contents and datafiles ;
Drop tablespace hd_audit_arc including contents and datafiles;
SET PAGES 1000 LINES 150
COLUMN SEGMENT_NAME FORMAT A30
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);
no rows selected.
Restart the database in normal mode
Shutdown immediate
Startup
Startup zoeoa1 dr database
Login as oracle in hd_standby
Sqlas
Startup mount
check data guard broker and Log Gap
dgmgrl sys/******
show configuration
Expected result: success
Run the SC01 job from hd_primary
Log Gap=0
Also check alert log and ensure for no errors