-
My first question is:
I have a script to show me is there any DDL task happened in database in the last 2 days as below:
PROMPT ===== OBJECTS CHANGED in LAST 2 DAYS IN NU_MODEL
select SUBSTR(OWNER,1,15), SUBSTR(OBJECT_NAME,1,20), OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP
from DBA_OBJECTS
where (LAST_DDL_TIME > sysdate-2) or
(CREATED > sysdate-2)
ORDER BY LAST_DDL_TIME
/
But I actually need to know a little bit more! I need to know what was this DDL? For example it just show Table XY had changed but was it a new column, new table, ...? Same thing about Stored Procs? Was it only recompiled or change?
How can I do that?
Another question is I want to create a user and give him GRANT to be able to create ONLY tables with temp_ prefix not any other tables? ( This is user will be use by an application and application internally create and delete temp_XYZ table.
How can I do this?
An ounce of prevention is worth a pound of cure
-
Oracle only updates the time stamp of last DDL in the data dictionary table not the sql.
However, you may get the exact DDL from V$SQLAREA by running the following query, provided the statements are not aged out:
In SQL Plus,
set column sql_text format a66
select sql_text
from v$sqlarea
where sql_text like 'CREATE%' or
sql_text like 'ALTER%'
;
Another method is, use log miner. I have not used it to find out the last DDL statements. You can try.
-
But we shut down our DB every night... I think the v$sqlarea will be wipe out... doesn't it?
I don't know log mioner and we use 8.0.5
Any other way?
Also My second QQQ please ?
An ounce of prevention is worth a pound of cure
-
You can not restrict a user to create only tables beginning with only TEMP_.
To your first question,
set up a cronjob at a interval of 10 minutes that will execute a SQL and spool the output to file.
-
Try to use AUDIT for statmant level --> TABLE.
It will write to audit file or table information about all commands like:
create table .. alter table .. drop table .. delete from table and so on.
-
Originally posted by Shestakov
Try to use AUDIT for statmant level --> TABLE.
It will write to audit file or table information about all commands like:
create table .. alter table .. drop table .. delete from table and so on.
I could imagine auditing data in tables , in oradinary databases . Is AuditTrail Feature available for normal databases applications also also like Oracle Apps databse ??
could u provide with procedure ?
thanks
siva prakash
DBA
-
I wrote this post because i thing that is only one way on oracle 8.0.5 for do that.
The way that describe in tamilselvan's post doesn't work.
Oracle doesn't keep in sqlarea commands:
create table ..
drop table ..
and LogMiner not avaiable in this version of oracle.
Just check it --->
SQL> select sql_text
from v$sqlarea
where sql_text like 'CREATE%' or sql_text like 'create%' or
sql_text like 'DROP%' or sql_text like 'drop%'
;
no rows selected
SQL> drop table t_alter;
Table dropped.
SQL> create table t_alter(a char);
Table created.
SQL> select sql_text
from v$sqlarea
where sql_text like 'CREATE%' or sql_text like 'create%' or
sql_text like 'DROP%' or sql_text like 'drop%'
;
no rows selected
SQL>
========================================================================
and then:
========================================================================
SQL>
select sql_text from v$sqlarea
where sql_text like '%CREATE%' or sql_text like '%create%' or
sql_text like '%DROP%' or sql_text like '%drop%'
;
SQL_TEXT
--------------------------------------------------------------------------------
ALTER SESSION SET CREATE_STORED_OUTLINES=FALSE
BEGIN :RESULT := SYS.OUTLN_PKG.DROP_COLLISION_EXPACT; END;
BEGIN :RESULT := SYS.OUTLN_PKG.DROP_EXTRAS_EXPACT; END;
select 'drop '||object_type||' '||owner||'.'||object_name|| ' ;'
from all_objects where owner = 'MEDEDNEW' and object_type in ('TABLE','FUNCT
ION','PROCEDURE','PACKAGE')
...
select sql_text from v$sqlarea where sql_text like 'CREATE%' or sql_text like 'c
reate%'
[Edited by Shestakov on 09-13-2002 at 06:51 PM]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|