Re: 2 DBA problems

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 14 Oct 2002 16:15:13 +0100
Message-ID: <3daadf81$0$8514$ed9e5944@reading.news.pipex.net>

<Shameless steal from Norman>

If you are worried about people doing things to your database and you not knowing,
and you are running at 8i then try the following logged in as SYS :

create tablespace logging
datafile 'path to file' size 201m
extent management local
uniform size 64k;

create user logging identified by new_password default tablespace logging
temporary tablespace temp
quota 0 on system;

create table logging.ddl_log
( user_name varchar2(30),

   ddl_date         date,
   ddl_type         varchar2(30),
   object_type      varchar2(18),
   owner            varchar2(30),
   object_name      varchar2(128)

) tablespace logging;

create or replace trigger

   DDLTrigger
AFTER DDL ON DATABASE /* See below for changes to this line */ BEGIN
insert into logging.ddl_log
( user_name, ddl_date, ddl_type, object_type, owner, object_name)
VALUES ( ora_login_user, sysdate, ora_sysevent, ora_dict_obj_type, ora_dict_obj_owner, ora_dict_obj_name); END;
/

</Shameless steal from Norman>

requires 8i (and if a lot of dml goes on a lot of disk space)

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

e
"s Lehaire" <s.lehaire_at_meilleuregestion.com> wrote in message
news:aoelm5$7dt$1_at_reader1.imaginet.fr...
> I've got the response for the second question :
>
> I've made a script in a SQL file :
>
> set linesize 500
> set heading off
> set pagesize 0
> spool dropconstraints.sql
> select 'ALTER TABLE '||TABLE_NAME||' DROP CONSTRAINT
'||CONSTRAINT_NAME||';'
>  FROM USER_CONSTRAINTS;
> spool off
>
>
> "s Lehaire" <s.lehaire_at_meilleuregestion.com> a écrit dans le message de
> news: aoeja2$6me$1_at_reader1.imaginet.fr...
> > HI,
> > I've got 2 problems:
> >
> > 1 - I want to know when a structure of a table have changed and what was
> the
> > change done.
> > 2 - My DB is not correctly structure and I want to create the
constraints
> > but there's yet constraints existing so how can I drop all constraints
> (DROP
> > CONSTRAINT my_constraint is not working).
> >
> > Thx for response
> >
> >
>
>

Received on Mon Oct 14 2002 - 10:15:13 CDT