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

i want to move the sys.aud$ files to another schema audit_logs.

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

  COUNT(*)
----------
    680427

i am unable to conenct to the database .

Please can anyone tell me this.this is oracle 11g version and platform is windows server 2003R2


 



 



 

LVL 2
Cha1tuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
0
Cha1tuAuthor Commented:
oh...i got those count yesterday.

now i want to move the sys.aud$ to schema AUDIT_LOGS
0
Cha1tuAuthor Commented:
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;
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Cha1tuAuthor Commented:
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?
0
sdstuberCommented:
do you "need" to delete after the copy?  no, there is no requirement that you do so

but you might want to.
0
sdstuberCommented:
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$

0
Cha1tuAuthor Commented:
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.
0
Cha1tuAuthor Commented:
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$


0
sdstuberCommented:
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.


0
sdstuberCommented:
>>>  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

0
Cha1tuAuthor Commented:
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
0
Cha1tuAuthor Commented:
what priviliges did i need to give ?
0
sdstuberCommented:
user AUDIT_LOGS needs to have the unlimited tablespace privilege or needs to have quota granted to it for the MASTER_DATA tablespace
0
sdstuberCommented:
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
0
Cha1tuAuthor Commented:
ya.but it is 11.1 version
0
sdstuberCommented:
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

0
Cha1tuAuthor Commented:

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
0
Cha1tuAuthor Commented:
did all the sys.aud$ files  move to audit_logs.audit_logs;
0
sdstuberCommented:
You must have done something wrong
SQL> create user audit_logs identified by pa55w0rd;

User created.

SQL> grant unlimited tablespace to audit_logs;

Grant succeeded.

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

Table created.

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

127 rows created.

SQL> commit;

Commit complete.

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

  COUNT(*)
----------
       127

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

  COUNT(*)
----------
       127

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cha1tuAuthor Commented:
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$;
0
sdstuberCommented:
yes
0
Cha1tuAuthor Commented:
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?

0
sdstuberCommented:
I think so, we do something similar
0
Cha1tuAuthor Commented:
@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?
0
Cha1tuAuthor Commented:
@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
0
sdstuberCommented:
insert into audit_logs.audit_logs  select * from sys.aud$ where timestamp# >= trunc(sysdate)-1 and timestamp# < trunc(sysdate)
0
Cha1tuAuthor Commented:
@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


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.