Category: Oracle

Job Execution Interval: ‘SYSDATE + 7’ Exactly seven days from the last execution ‘SYSDATE + 1/48’ Every half hour ‘NEXT_DAY(TRUNC(SYSDATE), ”MONDAY”) + 15/24’ Every Monday at 3PM ‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ”Q”), 3), ”THURSDAY”)’ First Thursday of each quarter Remobe the job: BEGIN DBMS_JOB.REMOVE(14144); END; / Change the job: In this example, job number 14144 is altered to…

Read More “DBMS_JOB all example” »

VARIABLE jobno NUMBER BEGIN DBMS_JOB.SUBMIT(:jobno, ‘DBMS_DDL.ANALYZE_OBJECT(”TABLE”, ”HR”, ”EMPLOYEES”, ”ESTIMATE”, NULL, 50);’, SYSDATE, ‘SYSDATE + 1’); COMMIT; END; / PRINT jobno JOBNO ———- 14144

col name format a50 col UNRECOVERABLE_CHANGE# format 999999999999999999999 col dt1 format a30 set lines 120 pages 200 SELECT NAME, UNRECOVERABLE_CHANGE#, TO_CHAR (UNRECOVERABLE_TIME,’DD-MON-YYYY HH:MI:SS’) dt1 FROM V$DATAFILE WHERE UNRECOVERABLE_CHANGE# != 0;

—————— CS Sequences maintenance——————- column last_number format 9999999999999999 select * from user_sequences where sequence_name in (‘UOC_ORDER_SEQ_ID’); select 2300000000 – CurrVAl – 20 LAST_NUMBER from dual; alter sequence UOC_ORDER_SEQ_ID increment by NOCACHE; Increment Once. select UOC_ORDER_SEQ_ID.nextval from dual; Confirm Value comes to 2300000000. After that alter sequence UOC_ORDER_SEQ_ID increment by 1 NOCACHE; alter sequence UOC_ORDER_SEQ_ID maxvalue…

Read More “Sequence Resetting” »

SQL> SQL> CREATE OR REPLACE PROCEDURE AlterSession( 2 p_SessionString IN VARCHAR2) AS 3 4 v_CursorID INTEGER; 5 v_Dummy INTEGER; 6 BEGIN 7 v_CursorID := DBMS_SQL.OPEN_CURSOR; 8 9 DBMS_SQL.PARSE(v_CursorID, p_SessionString, DBMS_SQL.NATIVE); 10 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID); 11 END AlterSession; 12 / Procedure created. SQL> SQL> column sysdate format a30 SQL> SELECT SYSDATE FROM dual; SYSDATE —————————— Jun…

Read More “DBMS_SQL for alter session.” »

CREATE TABLE STATSUSER.STATS$TOTALUSERS ( MEASURED_DATE DATE, TOTAL_USERS NUMBER, ACTIVE_USERS NUMBER, UOCWEBSPHERE_USERS NUMBER, UOCSCRIPTS_USERS NUMBER ); CREATE TABLE STATSUSER.STATS$USERSESSIONS ( START_DATE DATE, SYSTEM_SESSIONS NUMBER, ACTIVE_USERS NUMBER, INACTIVE_USERS NUMBER, DEDICATED_SERVERS NUMBER, USER_SESSIONS NUMBER, TRANSACTIONS NUMBER ); CREATE INDEX STATSUSER.STATS$USERSESSIONS_STDTE_I ON STATSUSER.STATS$USERSESSIONS (START_DATE); /* Following procedure and shell program is for Solaris */ #!/bin/ksh # This is the…

Read More “get_vmstat_linux” »

CREATE TABLE STATSUSER.STATS$TOTALUSERS ( MEASURED_DATE DATE, TOTAL_USERS NUMBER, ACTIVE_USERS NUMBER, UOCWEBSPHERE_USERS NUMBER, UOCSCRIPTS_USERS NUMBER ); CREATE TABLE STATSUSER.STATS$USERSESSIONS ( START_DATE DATE, SYSTEM_SESSIONS NUMBER, ACTIVE_USERS NUMBER, INACTIVE_USERS NUMBER, DEDICATED_SERVERS NUMBER, USER_SESSIONS NUMBER, TRANSACTIONS NUMBER ); CREATE INDEX STATSUSER.STATS$USERSESSIONS_STDTE_I ON STATSUSER.STATS$USERSESSIONS (START_DATE); /* Following procedure and shell program is for Solaris */ CREATE OR REPLACE PROCEDURE STATSUSER.vmstat3…

Read More “get_vmstat_solaris” »

set lines 132 set pagesize 3000 spool lob_mvmt select ‘alter table ‘ || table_name || ‘ move lob(‘|| column_name || ‘) store as ( tablespace pnet_data ) ;’ from user_tab_columns where data_type like ‘%LOB%’; spool off