FAQ
I have been attempting to find the modification dates of varios objects by
interrogating ALL_OBJECTS.LAST_DDL_TIME. However, I have noted that
granting object privileges causes the last DDL time to be changed (even
though granting an object priv is not actually DDL).

Example

15:00 HOURS:

SQL> ALTER TABLE schema_1.table_1 ADD col_1 VARCHAR2(100);

15:15 HOURS:

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON schema_1.table_1 TO schema_2;

15:30 HOURS:

SQL> select object_name, TO_CHAR(LAST_DDL_TIME, 'YYYYMMDD HH24:MI:SS')
last_ddl

from all_objects
where owner = 'SCHEMA_1'
and object_name = 'TABLE_1';

OBJECT_NAME LAST_DDL
------------------------------ -----------------
TABLE_1 20080701 15:15:00 <- NOTE, 15:15, the time

of the Obj Priv grant, not 15:00 when the table was modified by adding a
column

My question is: Which DD view can I interrogate to find the the time that
the column was added to the table.

Thank you

P Clark

Paul Clark
Tel: (+34)
Fax: (+34)
paul.clark_at_externos.aseval.com
ATENCIÓN: Esta cuenta de correo electrónico ha sido modificada. Rogamos
que lo tengan en cuenta y actualicen su lista de contactos de correo.

MENSAJE MEDIOAMBIENTAL **************************

Antes de imprimir este mensaje, por favor, compruebe que es realmente
necesario. Una tonelada de papel implica la tala de unos 15 árboles y el
consumo de 250.000 litros de agua. El Medio Ambiente es cuestión de TODOS.
LOOK AFTER THE ENVIRONMENT! *******************

Before you print, please think about whether it's really necessary. One
ton of paper means felling about 15 trees and consuming 250,000 litres of
water. Looking after the environment is EVERYONE'S job.

CLÁUSULA DE CONFIDENCIALIDAD *******************

Este correo electrónico y/o los documentos que lo acompañan pueden
contener información confidencial y/o reservada dirigida exclusivamente al
uso de los destinatarios. Si Usted no es destinatario (o ha recibido este
correo electrónico por error), no está autorizado a copiar o distribuir
esta comunicación a ninguna otra persona. Le rogamos nos la devuelva y la
elimine de su sistema.
CONFIDENTIALITY NOTICE *****************************

This e-mail and/or the attached documents may contain confidential and/or
sensitive information for the exclusive use of the addressees. If you are
not the intended recipient (or have received this e-mail in error) you are
not authorized to copy or distribute this message to any other person.
Please return the message to the sender and destroy this e-mail.

Paul Clark
Tel: (+34)
Fax: (+34)
paul.clark_at_externos.aseval.com
ATENCIÓN: Esta cuenta de correo electrónico ha sido modificada. Rogamos
que lo tengan en cuenta y actualicen su lista de contactos de correo.

MENSAJE MEDIOAMBIENTAL **************************

Antes de imprimir este mensaje, por favor, compruebe que es realmente
necesario. Una tonelada de papel implica la tala de unos 15 árboles y el
consumo de 250.000 litros de agua. El Medio Ambiente es cuestión de TODOS.
LOOK AFTER THE ENVIRONMENT! *******************

Before you print, please think about whether it's really necessary. One
ton of paper means felling about 15 trees and consuming 250,000 litres of
water. Looking after the environment is EVERYONE'S job.

CLÁUSULA DE CONFIDENCIALIDAD *******************

Este correo electrónico y/o los documentos que lo acompañan pueden
contener información confidencial y/o reservada dirigida exclusivamente al
uso de los destinatarios. Si Usted no es destinatario (o ha recibido este
correo electrónico por error), no está autorizado a copiar o distribuir
esta comunicación a ninguna otra persona. Le rogamos nos la devuelva y la
elimine de su sistema.
CONFIDENTIALITY NOTICE *****************************

This e-mail and/or the attached documents may contain confidential and/or
sensitive information for the exclusive use of the addressees. If you are
not the intended recipient (or have received this e-mail in error) you are
not authorized to copy or distribute this message to any other person.
Please return the message to the sender and destroy this e-mail.

Search Discussions

  • Asif Momen at Jul 2, 2008 at 11:22 am
    Hi Clark,
    Which DD view can I interrogate
    to find the the time that the column was added to the
    table.
    Unless you have enabled database auditing, you cannot view this information.

    Regards

    Asif Momen
    http://momendba.blogspot.com

    On Tue, 7/1/08, paul.clark_at_externos.aseval.com wrote:
    From: paul.clark_at_externos.aseval.com
    Subject: LAST_DDL_TIME from ALL_OBJECTS
    To: oracle-l_at_freelists.org
    Date: Tuesday, July 1, 2008, 11:13 PM

    I have been attempting to find the modification
    dates of varios objects by interrogating ALL_OBJECTS.LAST_DDL_TIME. However,
    I have noted that granting object privileges causes the last DDL time to
    be changed (even though granting an object priv is not actually DDL).

    Example

    15:00 HOURS:

    SQL> ALTER TABLE schema_1.table_1
    ADD col_1 VARCHAR2(100);

    15:15 HOURS:

    SQL> GRANT SELECT, INSERT, UPDATE,

    DELETE ON schema_1.table_1 TO schema_2;

    15:30 HOURS:

    SQL> select object_name, TO_CHAR(LAST_DDL_TIME,
    'YYYYMMDD HH24:MI:SS') last_ddl


    from all_objects


    where owner = 'SCHEMA_1'


    and object_name = 'TABLE_1';

    OBJECT_NAME

    LAST_DDL

    -----------------

    TABLE_1

    20080701 15:15:00
    <- NOTE, 15:15, the time of the Obj Priv grant, not 15:00 when

    the



    table was modified by adding a column

    My question is: Which DD view can I interrogate
    to find the the time that the column was added to the table.

    Thank you

    P Clark

    Paul Clark

    Tel: (+34)

    Fax: (+34)

    paul.clark_at_externos.aseval.com

    ATENCIÓN: Esta cuenta de correo electrónico ha sido modificada. Rogamos
    que lo tengan en cuenta y actualicen su lista de contactos de correo.

    MENSAJE MEDIOAMBIENTAL **************************

    Antes de imprimir este mensaje, por favor, compruebe que es realmente necesario.
    Una tonelada de papel implica la tala de unos 15 árboles y el consumo de
    250.000 litros de agua. El Medio Ambiente es cuestión de TODOS.

    LOOK AFTER THE ENVIRONMENT! *******************

    Before you print, please think about whether it's really necessary. One
    ton of paper means felling about 15 trees and consuming 250,000 litres
    of water. Looking after the environment is EVERYONE'S job.

    CLÁUSULA DE CONFIDENCIALIDAD *******************

    Este correo electrónico y/o los documentos que lo acompañan pueden contener
    información confidencial y/o reservada dirigida exclusivamente al uso de
    los destinatarios. Si Usted no es destinatario (o ha recibido este correo
    electrónico por error), no está autorizado a copiar o distribuir esta comunicación
    a ninguna otra persona. Le rogamos nos la devuelva y la elimine de su sistema.

    CONFIDENTIALITY NOTICE *****************************

    This e-mail and/or the attached documents may contain confidential and/or
    sensitive information for the exclusive use of the addressees. If you are
    not the intended recipient (or have received this e-mail in error) you
    are not authorized to copy or distribute this message to any other person.
    Please return the message to the sender and destroy this e-mail.

    Paul Clark

    Tel: (+34)

    Fax: (+34)

    paul.clark_at_externos.aseval.com

    ATENCIÓN: Esta cuenta de correo electrónico ha sido modificada. Rogamos
    que lo tengan en cuenta y actualicen su lista de contactos de correo.

    MENSAJE MEDIOAMBIENTAL **************************

    Antes de imprimir este mensaje, por favor, compruebe que es realmente necesario.
    Una tonelada de papel implica la tala de unos 15 árboles y el consumo de
    250.000 litros de agua. El Medio Ambiente es cuestión de TODOS.

    LOOK AFTER THE ENVIRONMENT! *******************

    Before you print, please think about whether it's really necessary. One
    ton of paper means felling about 15 trees and consuming 250,000 litres
    of water. Looking after the environment is EVERYONE'S job.

    CLÁUSULA DE CONFIDENCIALIDAD *******************

    Este correo electrónico y/o los documentos que lo acompañan pueden contener
    información confidencial y/o reservada dirigida exclusivamente al uso de
    los destinatarios. Si Usted no es destinatario (o ha recibido este correo
    electrónico por error), no está autorizado a copiar o distribuir esta comunicación
    a ninguna otra persona. Le rogamos nos la devuelva y la elimine de su sistema.

    CONFIDENTIALITY NOTICE *****************************

    This e-mail and/or the attached documents may contain confidential and/or
    sensitive information for the exclusive use of the addressees. If you are
    not the intended recipient (or have received this e-mail in error) you
    are not authorized to copy or distribute this message to any other person.
    Please return the message to the sender and destroy this e-mail.
  • Yong Huang at Jul 2, 2008 at 5:16 pm
    Paul,

    I'm sure others will point out too that GRANT *is* a DDL, because it modifies
    data dictionary (sysauth$, etc). Some shops do daily grant's until they find
    the impact of cursor reloads on library cache.

    To find the time a column was added to the table recently, check v$sqlstats if
    you're on 10g. It has the last_active_time column. Otherwise rely on redo log
    drilling, using log miner for instance.

    Last_ddl_time does not report all DDLs's times. Analyze is one. Compile trigger
    is another. There may be other cases.

    Yong Huang
    I have been attempting to find the modification dates of varios objects by
    interrogating ALL_OBJECTS.LAST_DDL_TIME. However, I have noted that
    granting object privileges causes the last DDL time to be changed (even
    though granting an object priv is not actually DDL).
    Example

    15:00 HOURS:
    SQL> ALTER TABLE schema_1.table_1 ADD col_1 VARCHAR2(100);

    15:15 HOURS:
    SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON schema_1.table_1 TO schema_2;

    15:30 HOURS:
    SQL> select object_name, TO_CHAR(LAST_DDL_TIME, 'YYYYMMDD HH24:MI:SS')
    last_ddl
    from all_objects
    where owner = 'SCHEMA_1'
    and object_name = 'TABLE_1';

    OBJECT_NAME LAST_DDL
    ------------------------------ -----------------
    TABLE_1 20080701 15:15:00 <- NOTE, 15:15, the time
    of the Obj Priv grant, not 15:00 when the table was modified by adding a
    column

    My question is: Which DD view can I interrogate to find the the time that
    the column was added to the table.

    Thank you

    P Clark


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 2, '08 at 6:13a
activeJul 2, '08 at 5:16p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase