Sunday, October 18, 2009

How to check last ddl on a table in Oracle Database

drop table test;

CREATE TABLE test (
testcol VARCHAR2(20))
ROWDEPENDENCIES;

SELECT table_name, dependencies
FROM user_tables where table_name='TEST';

SELECT current_scn
FROM v$database;

INSERT INTO test VALUES ('ABC');

COMMIT;

INSERT INTO test VALUES ('ABC');

COMMIT;

INSERT INTO test VALUES ('ABC');

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

SELECT current_scn
FROM v$database;

UPDATE test
SET testcol = 'DEF'
WHERE rownum = 1;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

UPDATE test
SET testcol = 'XYZ';

SELECT ORA_ROWSCN, rowid, testcol FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

CREATE TABLE test2 AS
SELECT * FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test2;

INSERT INTO test VALUES ('ABC');

UPDATE test SET testcol = 'DEF' WHERE rownum = 1;

UPDATE test2 SET testcol = 'GHI' WHERE rownum = 1;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

SELECT scn_to_timestamp(ORA_ROWSCN), rowid, testcol FROM test2;

SELECT scn_to_timestamp(ORA_ROWSCN) FROM test2;

SELECT current_scn
FROM v$database;

Keep observing the output of the above select queries.

No comments: