WCS Misc Query

-- Query records that have been stage propogated from staging to runtime database
select * from staglong where stgprocessed = 1

-- Query various adaptor properties
SELECT K3.DESCRIPTION,INTERSPECATTNAME,INTERSPECATTVALUE FROM iseditatt K1, PROFILE K2, MSGTYPES K3 
WHERE K1.PROFILE_ID = K2.PROFILE_ID AND K2.MSGTYPE_ID = K3.MSGTYPE_ID 
AND K1.PROFILE_ID IN (SELECT PROFILE_ID FROM PROFILE
WHERE MSGTYPE_ID IN (SELECT MSGTYPE_ID FROM msgtypes WHERE name LIKE '%%'));

-- Create ACP Policy entry for a new View, this is a quick and dirty approach
-- alternate option is to make use of acpload utility
-- replace MyNewView with the actual view name
insert into acaction values ((Select max(acaction_id) from acaction)+1, 'MyNewView', null);
insert into acactgrp values (10196, Select acaction_id from acaction where action='MyNewView', null, null);

-- Generate update sql
select concat(concat(concat('update keys set counter=',concat(concat(
concat(concat('(select max(',columnname),')+1 from ' ),tablename),') where tablename=''')),tablename),''' and counter <= '|| '(select max('||columnname||') from '|| tablename||')' ||';') from keys

-- Select Contract and store id by store name
select contract_id, store_id from storedef where store_id 
= (select storeent_id from storeent where identifier = '<STORE_NAME>');

-- Create backup of a table, we add parallel option for tables with large records
-- catentry_bk is the backup table name
create table catentry_bk parallel 4 nologging as select * from catentry;
-- Use following steps to oppulate the main table from backup table, truncate actual table, alter pctfree and then run the previous sql
-- to create main table from backup table
truncate table catentry;
alter table catentry pctfree 50;

-- Get current PctFree value for a list of tables
select table_name, pct_free from dba_tables where owner='<SCHEMA_OWNER>' and table_name in ('ORDADJUST', 'ORDIADJUST');