viewing DDL statements

I'm wondering if somebody knows of any method to view DDL statements running against the database?

Thanks for help in advance

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

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
http://technet.oracle.com/docs/produ...a76961/toc.htm

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

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.

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