Questions for DBAs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Questions for DBAs

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.


  3. #3
    Join Date
    Nov 2000
    Posts
    416
    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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  6. #6
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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
  •  



Click Here to Expand Forum to Full Width