Skip to content

Instantly share code, notes, and snippets.

Avatar

Vikram Vasudevan VikramVasudevan

View GitHub Profile
View oracle-rowscn-demo-part4.sql
SELECT 'Inserting 3 rows into table...' STATUS_MSG FROM DUAL;
INSERT INTO T (x,y) VALUES(-1,'SOMENEWENTRY');
INSERT INTO T (x,y) VALUES(-2,'SOMENEWENTRY2');
INSERT INTO T (x,y) VALUES(-3,'SOMENEWENTRY3');
COMMIT;
SELECT 'Fetching changed data in table ...' STATUS_MSG FROM DUAL;
SELECT t.*
-- dbms_rowid.rowid_block_number (ROWID) blockno,
-- SCN_TO_TIMESTAMP (t.ORA_ROWSCN),
View oracle-rowscn-demo-part3.sql
SELECT 'Updating 1 record in table ...' STATUS_MSG FROM DUAL;
UPDATE t
SET y = 'DUAL123'
WHERE x = 1;
COMMIT;
SELECT 'Fetching changed data in table ...' STATUS_MSG FROM DUAL;
SELECT t.*
-- dbms_rowid.rowid_block_number (ROWID) blockno,
View oracle-rowscn-demo-part2.sql
var max_row_scn number;
SELECT 'Resetting max scn ...' STATUS_MSG FROM DUAL;
BEGIN
SELECT MAX(ORA_ROWSCN) max_row INTO :max_row_scn FROM T;
END;
/
print max_row_scn
View oracle-rowscn-demo-part1.sql
set echo off time on timing off linesize 9999 feedback off heading off
-- The following statement might fail if table t does not exist.
drop table t;
/* Create Table wiht ROWDEPENDENCIES enabled */
CREATE TABLE t
(
x,
y
View oracle-rowscn-demo-full.sql
set echo off time on timing off linesize 9999 feedback off heading off
drop table t;
/* Create Table wiht ROWDEPENDENCIES enabled */
CREATE TABLE t
(
x,
y
)
View oracle-audit-trigger-full.sql
WITH prm_input
AS (SELECT table_name,
'CREATE OR REPLACE TRIGGER "YOUR_DATA_OWNER"."TRG'
|| table_name
|| '"'
|| ' AFTER INSERT OR UPDATE OR DELETE '
|| ' ON '
|| table_name
|| ' FOR EACH ROW '
|| ' DECLARE '
View oracle-audit-trigger-004.sql
SELECT REPLACE (
REPLACE (trigger_sql,
'{INSERT_SET_CLAUSE}',
insert_column_list || ';'),
'{UPDATE_SET_CLAUSE}',
update_column_list || ';')
trigger_sql
FROM table_agg_stg
View oracle-audit-trigger-003.sql
table_agg_stg
AS (SELECT trigger_sql,
table_name,
listagg (
'audtab.' || column_name || ':= :NEW.' || column_name,
'; ')
WITHIN GROUP (ORDER BY column_id)
insert_column_list,
listagg (
'audtab.' || column_name || ':= :OLD.' || column_name,
View oracle-audit-trigger-002.sql
table_columns_stg AS (SELECT c.table_name,
t.trigger_sql,
c.column_name,
column_id
FROM prm_input t, user_tab_columns c
WHERE t.table_name = c.table_name),
View oracle-audit-trigger-001.sql
WITH prm_input
AS (SELECT table_name,
'CREATE OR REPLACE TRIGGER "YOUR_DATA_OWNER"."TRG'
|| table_name
|| '"'
|| ' AFTER INSERT OR UPDATE OR DELETE '
|| ' ON '
|| table_name
|| ' FOR EACH ROW '
|| ' DECLARE '