how to move sys.aud$ files to another schema AUDIT_LOGS

how can you count if you can't connect?

the actual copy should be easy

insert into audit_logs select * from sys.aud$

this assumes audit_logs has the same structure, if not, you'll need to specify the columns which is good practice anyway

oh...i got those count yesterday.

now i want to move the sys.aud$ to schema AUDIT_LOGS

i am trying to create schema like this

CREATE USER AUDIT_LOGS
  IDENTIFIED BY "......"
  DEFAULT TABLESPACE  MASTER_DATA
  TEMPORARY TABLESPACE TEMPORARY
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

Then i need to insert these

insert into audit_logs select * from sys.aud$

i have one doubt.once i inserted into audit_logs schema ,did i need to delete the sys.aud$ files?

do you "need" to delete after the copy?  no, there is no requirement that you do so

but you might want to.

in your example you have created a user called audit_logs

but your insert is for a table called audit_logs.

it is permissible to create a table with the same name as the schema if that's what you're trying to do


      insert into audit_logs.audit_logs  select * from sys.aud$

if your target table isn't called "audit_logs" then change it below

insert into audit_logs.YOUR_LOG_TABLE  select * from sys.aud$

why i am saying delete is after inserting the sys.aud$ files to audit_logs schema .both will conatin the sys.aud$ files right.so it will occupy the space in sys table right

my sys tablespace is filling very frequently .
 
let me know know which way i better to put sys.aud$.


and after insertion toAUDT_LOGS.all the audit files will directly got this schema.or did i need to insert the files everyday.

can i do like this

CREATE USER AUDIT_LOGS
  IDENTIFIED BY "......"
  DEFAULT TABLESPACE  MASTER_DATA
  TEMPORARY TABLESPACE TEMPORARY
  PROFILE DEFAULT
  ACCOUNT UNLOCK;


SQL>insert into audit_logs.audit_logs  select * from sys.aud$

there are no "files"

after you copy the rows with the insert you can delete to free up space
you will need to copy new rows periodically.  Daily should be fine.

>>>  insert into audit_logs.audit_logs  select * from sys.aud$

yes, you can do that provided you create the audit_logs table with the same structure as sys.aud$

easiest way to do that is...


create table audit_logs.audit_logs as select * from sys.aud$ where 1=0;

that will create an empty table with the same columns and types as the original

SQL> CREATE USER AUDIT_LOGS
  2    IDENTIFIED BY "SpringT!m3"
  3    DEFAULT TABLESPACE  MASTER_DATA
  4    TEMPORARY TABLESPACE TEMPORARY
  5    PROFILE DEFAULT
  6    ACCOUNT UNLOCK;

User created.

SQL> create table audit_logs.audit_logs as select * from sys.aud$ where 1=0;
create table audit_logs.audit_logs as select * from sys.aud$ where 1=0
                                                        *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'MASTER_DATA'

Thse is the error i am getting

what priviliges did i need to give ?

user AUDIT_LOGS needs to have the unlimited tablespace privilege or needs to have quota granted to it for the MASTER_DATA tablespace

also note,  if you have version 11.2    you can use dbms_audit_mgmt to move sys.aud$ to another tablespace as well as other maintenance on it

ya.but it is 11.1 version

if you can't upgrade, you can check with oracle support for a patch.  There are some backports available, but you'll need to check with oracle support for your specific platform and version

SQL> Select count (*) from sys.aud$;

  COUNT(*)
----------
    702650

SQL> Select count (*) from audit_logs.audit_logs;

  COUNT(*)
----------
         0
After insertion it showing the count 0?
After

did all the sys.aud$ files  move to audit_logs.audit_logs;

i did everything correct expect the commit...:):)

now i want the space in the sys.aud$

so can i do like this TRUNCATE TABLE sys.aud$;

yes

I would like a nightly process developed that will move the audit data from System to another database.  Does it make sense to create a “central” audit database that all audit (from every server) is moved to?

I think so, we do something similar

@sdstuber: if i want insert only the yesterday(just like sysdate-1) the sys.aud$ files into audit_logs


what is the query we can write,is it possible tell me

insert into audit_logs.audit_logs  select * from sys.aud$ where sysdate-1;

is it correct?

@sdstuber:
if i want insert the sys.aud$ files into audit_logs  only upto privous day.like sysdate-1( 3/31/2011)

can you tell the query for that

insert into audit_logs.audit_logs  select * from sys.aud$ where timestamp# >= trunc(sysdate)-1 and timestamp# < trunc(sysdate)

@sdstuber:

can you help me out this

SQL> Select count (*) from sys.aud$;

  COUNT(*)
----------
   1024316

sql> insert into audit_logs.audit_logs  select * from sys.aud$ where timestamp# >= trunc(sysdate)-1 and timestamp# < trunc(sysdate)

0 rows created.

SQL>commit;
SQL> Select count (*) from audit_logs.audit_logs;

  COUNT(*)
----------
         0


its not inserting rows in sys.aud$ to audit_logs.audit_logs