viewing DDL statements
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: viewing DDL statements

  1. #1
    Join Date
    Mar 2001
    Posts
    6
    I'm wondering if somebody knows of any method to view DDL statements running against the database?

    Thanks for help in advance

  2. #2
    Join Date
    Feb 2001
    Posts
    180
    Hi,
    This may give a first look of what is happening:

    select p.spid
    , s.sid
    , s.status
    , s.username
    , decode(s.command
    ,1, 'CREATE TABLE' ,33, 'DROP DATABASE LINK' ,65, 'CREATE PROFILE'
    ,2, 'INSERT' ,34, 'CREATE DATABASE' ,66, 'DROP PROFILE'
    ,3, 'SELECT' ,35, 'ALTER DATABASE' ,67, 'ALTER PROFILE'
    ,4, 'CREATE CLUSTER' ,36, 'CREATE ROLLBACK SEGMENT' ,68, 'DROP PROCEDURE'
    ,5, 'ALTER CLUSTER' ,37, 'ALTER ROLLBACK SEGMENT' ,69, 'DROP PROCEDURE'
    ,6, 'UPDATE' ,38, 'DROP ROLLBACK SEGMENT' ,70, 'ALTER RESOURCE COST'
    ,7, 'DELETE' ,39, 'CREATE TABLESPACE' ,71, 'CREATE SNAPSHOT LOG'
    ,8, 'DROP CLUSTER' ,40, 'ALTER TABLESPACE' ,72, 'ALTER SNAPSHOT LOG'
    ,9, 'CREATE INDEX' ,41, 'DROP TABLESPACE' ,73, 'DROP SNAPSHOT LOG'
    ,10, 'DROP INDEX' ,42, 'ALTER SESSION' ,74, 'CREATE SNAPSHOT'
    ,11, 'ALTER INDEX' ,43, 'ALTER USE' ,75, 'ALTER SNAPSHOT'
    ,12, 'DROP TABLE' ,44, 'COMMIT' ,76, 'DROP SNAPSHOT'
    ,13, 'CREATE SEQUENCE' ,45, 'ROLLBACK' ,79, 'ALTER ROLE'
    ,14, 'ALTER SEQUENCE' ,46, 'SAVEPOINT' ,85, 'TRUNCATE TABLE'
    ,15, 'ALTER TABLE' ,47, 'PL/SQL EXECUTE' ,86, 'TRUNCATE COUSTER'
    ,16, 'DROP SEQUENCE' ,48, 'SET TRANSACTION' ,88, 'ALTER VIEW'
    ,17, 'GRANT' ,49, 'ALTER SYSTEM SWITCH LOG' ,91, 'CREATE FUNCTION'
    ,18, 'REVOKE' ,50, 'EXPLAIN' ,92, 'ALTER FUNCTION'
    ,19, 'CREATE SYNONYM' ,51, 'CREATE USER' ,93, 'DROP FUNCTION'
    ,20, 'DROP SYNONYM' ,25, 'CREATE ROLE' ,94, 'CREATE PACKAGE'
    ,21, 'CREATE VIEW' ,53, 'DROP USER' ,95, 'ALTER PACKAGE'
    ,22, 'DROP VIEW' ,54, 'DROP ROLE' ,96, 'DROP PACKAGE'
    ,23, 'VALIDATE INDEX' ,55, 'SET ROLE' ,97, 'CREATE PACKAGE BODY'
    ,24, 'CREATE PROCEDURE' ,56, 'CREATE SCHEMA' ,98, 'ALTER PACKAGE BODY'
    ,25, 'ALTER PROCEDURE' ,57, 'CREATE CONTROL FILE' ,99, 'DROP PACKAGE BODY'
    ,26, 'LOCK TABLE' ,58, 'ALTER TRACING'
    ,27, 'NO OPERATION' ,59, 'CREATE TRIGGER'
    ,28, 'RENAME' ,60, 'ALTER TRIGGER'
    ,29, 'COMMENT' ,61, 'DROP TRIGGER'
    ,30, 'AUDIT' ,62, 'ANALYZE TABLE'
    ,31, 'NOAUDIT' ,63, 'ANALYZE INDEX'
    ,32, 'CREATE DATABASE LINK' ,64, 'ANALYZE CLUSTER'
    ,0 , 'NOTHING'
    , 'UNKNOWN') COMMAND
    , s.osuser
    , s.process
    , s.logon_time
    , s.program
    from sys.gv_$session s
    , sys.gv_$process p
    where s.paddr = p.addr
    Regards
    Ben de Boer

  3. #3
    Join Date
    Jul 2000
    Posts
    243
    Hi avarshney
    for this you can use v$sql, v$sqlarea, v$sqltext. if you join them with v$session you can get data on the user that initiated the ddl statment.

    for more reading look at
    [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76961/toc.htm[/url]

  4. #4
    Join Date
    Feb 2001
    Posts
    180
    For a closer look take the SID from the first query and:
    select t.sql_text
    from sys.gv_$sqltext t
    , sys.gv_$session s
    -- Adress in session_info.sql (sql_address)
    where t.address = s.sql_address
    and s.sid = ??
    ORDER BY piece
    Regards
    Ben de Boer

  5. #5
    Join Date
    Jan 2001
    Posts
    18
    Try a easy tool Chyfo (www.ispirer.com/chyfo.html)
    This is database migration and movement tool.

    But this command:

    chyfo.exe /t=schema1.table1 /ddl

    will generate CREATE TABLE and CREATE INDEX statements for a table.
    It's easy. Isn't it?

    Best regards, Dmitry.


  6. #6
    Join Date
    Mar 2001
    Posts
    6

    Arrow

    I'm sorry I did'nt phrased my requirements correctly.Actually what I'm looking at is extracting the entire sqltext for eg. (create table A as select * from B) which a user might be running against the database.

    select t.sql_text
    from sys.gv_$sqltext t
    , sys.gv_$session s
    -- Adress in session_info.sql (sql_address)
    where t.address = s.sql_address
    and s.sid = ??
    ORDER BY piece

    does not extract sql information if it is a DDL.Is there Any view that can be queried for extracting DDL statement text.
    Please let me know if it is possible.

    Thanks


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