Tuesday 14 April 2015

Create Usage tracking table in Oracle

DECLARE CURSOR ut_cursor is
SELECT * FROM &&OLDUTTABLE_NAME;
l_hash varchar2(32);
begin
FOR ut_rec in ut_cursor
LOOP
l_hash := ‘987654321’;
insert into &&NEWUTTABLE_NAME values (
ut_rec.user_name,
ut_rec.repository_name,
ut_rec.subject_area_name,
ut_rec.node_id,
ut_rec.start_ts,
ut_rec.start_dt,
ut_rec.start_hour_min,
ut_rec.end_ts,
ut_rec.end_dt,
ut_rec.end_hour_min,
ut_rec.query_text,
ut_rec.query_text,
l_hash,
ut_rec.success_flg,
ut_rec.row_count,
ut_rec.total_time_sec,
ut_rec.compile_time_sec,
ut_rec.num_db_query,
ut_rec.cum_db_time_sec,
ut_rec.cum_num_db_row,
ut_rec.cache_ind_flg,
ut_rec.query_src_cd,
ut_rec.saw_src_path,
ut_rec.saw_dashboard,
ut_rec.saw_dashboard_pg,
ut_rec.presentation_name,
ut_rec.error_text,
ut_rec.runas_user_name,
ut_rec.num_cache_inserted,
ut_rec.num_cache_hits );
END LOOP;
END;

No comments:

Post a Comment