tag:blogger.com,1999:blog-7204411147499359498.post6005555491416683981..comments2022-02-25T11:39:10.722-05:00Comments on TRIRIGA Development: System Usage ScriptRob Zombronhttp://www.blogger.com/profile/14515979904589988419noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-7204411147499359498.post-24183859711852715342014-03-05T19:32:03.268-05:002014-03-05T19:32:03.268-05:00jennyB:
To delete data from a table, the record ne...jennyB:<br />To delete data from a table, the record needs to be transitioned to the NULL state. The safest way to do this is through the app, but there is a way to do it via SQL. Once a record is in a NULL state, the cleanup agent will remove it. You do not want to directly delete data from tables. Rob Zombronhttps://www.blogger.com/profile/14515979904589988419noreply@blogger.comtag:blogger.com,1999:blog-7204411147499359498.post-40103892740171912172014-03-04T10:34:43.558-05:002014-03-04T10:34:43.558-05:00How is table cleaned up? How do you delete unneces...How is table cleaned up? How do you delete unnecessary data from the table? Is it an update script for cleanup agent or just delete from the table?jennyBhttp://www.aboutweb.comnoreply@blogger.comtag:blogger.com,1999:blog-7204411147499359498.post-59085029628370701862010-07-28T09:17:39.189-04:002010-07-28T09:17:39.189-04:00Warren,
A while back, a coworker of mine was asked...Warren,<br />A while back, a coworker of mine was asked to do a similar task. He wrote a script to go out and query ACTIVE_SESSION_USER joined to T_LICENCEDETAILS every five minutes and dump it to a csv file. He used a batch script that runs the the sql script with sqlplus and another batch script to concatenate the results. He then dumped the output into Excel and slapped a pivot table on it to produce a chart of concurrent license usage.<br /><br />The script he used is:<br />column dcol new_value mydate noprint<br />select to_char(sysdate,'YYYYMMDDHH24MISS') dcol from dual;<br />set echo off<br />set feedback off<br />set linesize 100<br />set pagesize 0<br />set sqlprompt ''<br />set trimspool on<br />spool c:\temp\concurrency\&mydate._output.csv<br />select to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS')||','||b.username||','||b.peoplefullname||','||c.userlicence<br />from tridata9.active_session_user a, tridata9.t_myprofile b, tridata9.ibs_spec_assignments isa, tridata9.t_licencedetails c<br />where a.user_id = b.spec_id<br />and b.spec_id = isa.spec_id<br />and isa.ass_spec_id = c.spec_id;<br />spool off<br />exitRob Zombronhttps://www.blogger.com/profile/14515979904589988419noreply@blogger.comtag:blogger.com,1999:blog-7204411147499359498.post-37673108414221954052010-07-21T09:39:52.548-04:002010-07-21T09:39:52.548-04:00Hello Rob,
is there a way to show concurrency thr...Hello Rob,<br /><br />is there a way to show concurrency through the session history table? Here are two queries that I'm using to look for this information:<br /><br />select distinct (count(T3.usergroup)) as Total_Logins, T3.USERGROUP<br /><br />from session_history as T1, T_MYPROFILE as T2, T_GROUPDETAILS as T3 <br /><br />where T1.USER_ID = T2.SPEC_ID <br />and T2.SPEC_ID = T3.PAR_SPEC_ID <br />and T1.USER_ID != '10943449' <br />and T3.USERGROUP not like 'Portfolio 2%' <br />and T1.START_TIME > '2010-07-01 00:00:00' and T1.START_TIME < '2010-07-21 00:00:00'<br />group by T3.usergroup<br />order by Total_Logins asc<br /><br />OR<br /><br />select T3.USERGROUP, T1.START_TIME, T1.END_TIME, T1.USER_ID, T2.trinametx<br /><br />from session_history as T1, T_MYPROFILE as T2, T_GROUPDETAILS as T3 <br /><br />where T1.USER_ID = T2.SPEC_ID <br />and T2.SPEC_ID = T3.PAR_SPEC_ID <br />and T1.USER_ID != '10943449' <br />and T3.USERGROUP not like 'Portfolio 2%' <br />and T1.START_TIME > '2010-04-01 00:00:00' <br />order by T1.START_TIME asc<br /><br />The userid 10943449 is our integration user and I filter out all Porfolio groups because of duplication.<br /><br />thanks<br />Warrenmasmixhttps://www.blogger.com/profile/11756577069720025995noreply@blogger.com