I had enabled auditing with oracle 12.2C and mistakenly dropped the table sys.aud$ is there anyway to recreate the same with the same columns I tried to create it again but it is not the same as the previous so unable to write the audit logs into it please let me know the solution for this
The definition is stored in your installation. Head to:
$ORACLE_HOME/rdbms/admin/dsec.bsq
and you'll find the create table command that is needed. Extracting and re-running the relevant create table script is supported by Oracle (Doc 316452.1).
Here's the one from my 12.2 instance:
create table aud$ /* audit trail table */
( sessionid number not null, /* server session id */
entryid number not null, /* serial number to audit records */
statement number not null, /* sql statement number */
timestamp# date, /* OBSOLETE: 10iR1 and above: time of query */
userid varchar2("M_IDEN"), /* database username */
userhost varchar2("M_HOST"), /* client host machine name */
terminal varchar2("M_TERM"), /* client terminal id */
action# number not null, /* action responsible for auditing */
returncode number not null, /* return code for the action */
obj$creator varchar2("M_IDEN"), /* schema where object resides */
obj$name varchar2("M_XDBI"), /* name of the object */
auth$privileges varchar2(32), /* granted privileges */
auth$grantee varchar2("M_IDEN"), /* grantee username */
new$owner varchar2("M_IDEN"), /* schema of the dependent object */
new$name varchar2("M_XDBI"), /* name of the dependent object */
ses$actions varchar2("S_ACFL"), /* success/failure of each action */
ses$tid number, /* object id */
logoff$lread number, /* number of logical reads in the session */
logoff$pread number, /* number of physical reads in the session */
logoff$lwrite number, /* number of logical writes in the session */
logoff$dead number, /* number of deadlocks in the session */
logoff$time date, /* session duration */
comment$text varchar2("M_VCSZ"), /* type authentication/trigger/protocol */
clientid varchar2(128), /* user defined client identifier */
spare1 varchar2(255), /* OS user name */
spare2 number, /* whether this table (aud$) is modified */
obj$label raw(255), /* OBSOLETE: 8.0 and above */
ses$label raw(255), /* OBSOLETE: 8.0 and above */
priv$used number, /* system privlege used */
sessioncpu number, /* total cpu time for the session */
ntimestamp# timestamp, /* new timestamp (in UTC) of query */
proxy$sid number, /* proxy session serial number */
user$guid varchar2(32), /* global user identifier */
instance# number, /* instance number */
process# varchar2("M_PIDL"), /* OS process id */
xid raw(8), /* transaction identifier */
auditid varchar2(64), /* audit operation id */
scn number, /* SCN of the query */
dbid number, /* database identifier for source db */
sqlbind clob, /* bind variables for the query */
sqltext clob, /* sql text of the query */
obj$edition varchar2("M_IDEN"), /* Object edition name */
rls$info clob, /* RLS related info */
current_user varchar2("M_IDEN") /* Current User */
)
/