| Monitoring script required for Oracle 9.2.0.7.0 on Windows 2000 Server [message #450604] |
Thu, 08 April 2010 04:51  |
catchme55555@yahoo.co.in
Messages: 20
Registered: September 2009
|
Junior Member |
|
|
Hi all,
Greetings of the day.
My manager is asking to create a SQL scrip, which will provide following information. Can anyone help me ?
Our database is 9.2.0.7.0 on Windows 2000 Server
- How many records Insert / Update or Delete daily in my Oracle database?
- Check Archive log / Redo Log switching information?
- Check database growth ?
Expecting a prompt reply !
Regards,
Sanju
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Monitoring script required for Oracle 9.2.0.7.0 on Windows 2000 Server [message #451839 is a reply to message #451251] |
Sat, 17 April 2010 05:25  |
catchme55555@yahoo.co.in
Messages: 20
Registered: September 2009
|
Junior Member |
|
|
Hi Michel,
Greetings of the day ! Hope you are fine and well.
I have done the following implementation on my development database for finding out how many records Insert / Update or Delete
1)Create pfile from spfile.
2)Shutdown the database
3) Add the following parameter in your pfile
a)audit_trail='db'
b)audit_sys_operations='true'
c)optimizer_mode=FIRST_ROWS_1
4)Start the database with pfile
5)Then run the cataudit.sql script ( Login as SYSBDA)
Note: The auditing table will be installed in the SYSTEM tablespace. It is therefore important that you have enough space
available in this tablespace before you enable auditing.
6)Shutdown the database
7) Re start the database
 Now check the following view:
SQL>select name, value from v$parameter where name like 'audit%';
SQL>select value from v$parameter where name='optimizer_mode';
9)SQL> AUDIT ALL BY <user-name> BY ACCESS;
10)SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY <user-name>BY ACCESS;
11)SQL> AUDIT EXECUTE PROCEDURE BY <user-name>BY ACCESS;
12)SQL> SELECT username,timestamp,owner,obj_name,action_name FROM dba_audit_trail WHERE owner = '<user-name>' ORDER BY timestamp;
Example
=======
SQL> conn <user-name>/password
Connected.
SQL> create table test (name varchar(10));
Table created.
SQL> insert into test values('simi');
1 row created.
SQL> insert into test values('rimi');
1 row created.
SQL>commit;
SQL> select * from name;
test
====
simi
rimi
SQL> delete from name;
2 rows deleted.
SQL> drop table name;
Table dropped.
Run the following query to track the output.
SQL> SELECT username,timestamp,owner,obj_name,action_name FROM dba_audit_trail WHERE owner = '<user-name>' ORDER BY timestamp;
------------------
Regards,
Sanju
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
